Отключение внешних ключей

Материал из GedeminWiki
Перейти к: навигация, поиск

Индекс занимает место в файле базе данных и требует времени для перестроения и обновления при каждой операции изменения данных. В большинстве случаев преимущества от использования индекса перевешивают его недостатки. В оставшихся -- индекс ничем не может нам помочь и только ухудшает производительность сервера. Можно выделить три категории "вредных" индексов:

Бесполезный индекс
содержит только одно уникальное значение. Не используется оптимизатором для извлечения данных. Однозначно должен быть удален для экономии места.
Плохой индекс
количество уникальных значений которого много меньше общего количества записей. Такой индекс может быть удален, но для принятия окончательного решения требуется дополнительная информация о распределении значений и об использовании этого индекса в запросах. Например, индекс по аналитическому признаку в таблице с проводками может иметь низкую селективность в целом, однако быть полезным при использовании в коомбинации с конкретным счетом.
Неиспользуемый (редкоиспользуемый) индекс
такой индекс может обладать приемлемой селективностью, однако никогда не использоваться вследствии логической организации задачи. Например, очень редко извлекаются данные в разрезе полей CREATORKEY, EDITORKEY.

Для анализа индексов можно изучать логи, выдаваемые утилитой gstat, или воспользоваться утилитой IBAnalyst.

К сожалению, Firebird не позволяет создавать внешние ключи (FOREIGN KEY) без индекса. В Гедымине мы пошли на определенные ухищрения для создания псевдо внешних ключей. Такой "внешний ключ" контролирует ссылочную целостность с помощью системы триггеров и не создает индекс.

Управление внешними ключами осуществляется с помощью Менеджера внешних ключей. Откроем его окно: 

 Исследователь -> Сервис -> Атрибуты -> Внешние ключи

gd_ref_constraints.jpg

Ниже приведено описание колонок таблицы GD_REF_CONSTRAINTS:

constraint_name Имя внешнего ключа.
const_name_uq Имя первичного ключа или уникального индекса, на который идет ссылка.
match_option
update_rule Правило при изменении данных.
delete_rule Правило при удалении данных.
constraint_rel Таблица, которой принадлежит внешний ключ.
constraint_field Поле внешнего ключа.
ref_rel Таблица на которую идет ссылка.
ref_field Поле первичного ключа в таблице на которую идет ссылка.
ref_state Состояние внешнего ключа (см. ниже).
ref_next_state Состояние после конвертации.
constraint_rec_count Количество записей в таблице с внешним ключом.
constraint_uq_count Количество уникальных значений в поле с внешним ключом.
ref_rec_count Количество записей в таблице на которую идет ссылка.

Колонки с количеством записей и уникальных значений являются вычисляемыми. Соответствующие значения выводятся на основании имеющейся статистики индексов и могут отличаться от реального количества записей и уникальных значений в таблице. Запустить фоновый процесс обновления статистики можно с помощью кнопки recalc_stat.jpg на панели инструментов.

Состояние внешнего ключа: ORIGINAL и TRIGGER

Текущее состояние внешнего ключа записано в поле ref_state, которое может принимать два значения:

ORIGINAL
Внешняя ссылка, созданная стандартным образом через ограничение FOREIGN KEY.
TRIGGER
Внешняя ссылка без индекса. Целостность данных поддерживается с помощью системы триггеров.

Для прямой или обратной конвертации внешних ключей необходимо изменить значение поля ref_next_state в диалоговом окне и запустить процесс конвертации с помощью кнопки start_process.jpg на панели инструментов.

Диалоговое окно внешнего ключа

Окно предназначено для изменения состояния внешнего ключа и для просмотра его параметров.

gd_ref_constraints_dlg.jpg

Предназначение кнопок в разделе Статистика:

Записей во внешнем ключе Справа от кнопки выводится общее количество записей во внешнем ключе. Нажав на кнопку, можно выполнить запрос для просмотра содержимого таблицы с внешним ключем.
Уникальных значений во внешнем ключе Справа от кнопки выводится количество уникальных значений во внешнем ключе. Нажав на кнопку можно выполнить запрос и просмотреть распределение этих значений.
Записей в таблице справочнике Справа от кнопки выводится общее количество записей в таблице, на которую ссылается внешний ключ. Нажав на кнопку, можно выполнить запрос для просмотра содержимого этой таблицы.
Показать текущие значения Кнопка доступна только для внешнего ключа в состоянии TRIGGER и позволяет просмотреть значения в таблице GD_REF_CONSTRAINT_DATA.

Конвертация внешних ключей

Последовательность действий включает следующие шаги:

  1. Открыть окно Менеджера внешних ключей.
  2. Пересчитать статистику индексов с помощью кнопки recalc_stat.jpg
  3. Отобрать индексы для конвертации. На базах с большим количеством внешних ключей удобно использовать фильтры для отбора нужных записей.
  4. Открыть на редактирование отобранные индексы и установить значение TRIGGER в поле Конвертировать. Сохранить изменения.
  5. Запустить процесс конвертации с помощью кнопки start_process.jpg
  6. Выполнить переподключение к базе данных.

Обратная конвертация

Восстановление исходного состояния внешних ключей осуществляется аналогичным образом. В поле Конвертировать диалогового следует выбрать значение ORIGINAL.

Как это работает

Проиллюстрируем работу псевдо ключа на следующем примере. Дано: таблица GD_DOCUMENT и поле EDITORKEY, которое ссылается на справочник контактов: 

ALTER TABLE GD_DOCUMENT ADD CONSTRAINT GD_FK_DOCUMENT_EDITORKEY 
FOREIGN KEY (EDITORKEY) REFERENCES GD_PEOPLE (CONTACTKEY) 
ON UPDATE CASCADE

Открываем окно Менеджера внешних ключей, находим наш внешний ключ и задаем ему значение TRIGGER в поле Конвертировать. Выполняем конвертацию и переподключаемся к базе данных.

В процессе конвертации ограничение GD_FK_DOCUMENT_EDITORKEY будет удалено из базы данных, а для контроля за целостностью будут созданы следующие триггеры: 

CREATE OR ALTER TRIGGER gd_ai_constraint_rel_148740481 FOR gd_document
  active 
  after INSERT 
  POSITION 32000
AS 
BEGIN 
  IF (NEW.EDITORKEY IS NOT NULL) THEN 
  BEGIN 
    IF (NOT EXISTS (SELECT * FROM gd_ref_constraint_data WHERE value_data = NEW.EDITORKEY 
      AND constraintkey = 148740481)) THEN 
    BEGIN
      IF (NOT EXISTS (SELECT CONTACTKEY FROM GD_PEOPLE WHERE CONTACTKEY = NEW.EDITORKEY)) THEN
        EXCEPTION gd_e_fkmanager 'Нарушение ссылочной целостности по полю EDITORKEY в таблице GD_DOCUMENT.' 
          || ' Значение: ' || NEW.EDITORKEY;
      RDB$SET_CONTEXT('USER_TRANSACTION', 'REF_CONSTRAINT_UNLOCK', '1');
      INSERT INTO gd_ref_constraint_data (constraintkey, value_data, value_count)
        VALUES (148740481, NEW.EDITORKEY, 1);
      RDB$SET_CONTEXT('USER_TRANSACTION', 'REF_CONSTRAINT_UNLOCK', '0');
    END
  END
END
CREATE OR ALTER TRIGGER gd_au_constraint_rel_148740481 FOR gd_document
  active 
  after UPDATE 
  POSITION 32000
AS 
BEGIN 
  IF (NEW.EDITORKEY IS NOT DISTINCT FROM OLD.EDITORKEY) THEN
    EXIT;
  IF (NEW.EDITORKEY IS NOT NULL) THEN 
  BEGIN 
    IF (NOT EXISTS (SELECT id FROM gd_ref_constraint_data WHERE value_data = NEW.EDITORKEY 
      AND constraintkey = 148740481)) THEN 
    BEGIN
      IF (NOT EXISTS (SELECT CONTACTKEY FROM GD_PEOPLE WHERE CONTACTKEY = NEW.EDITORKEY)) THEN
        EXCEPTION gd_e_fkmanager 'Нарушение ссылочной целостности по полю EDITORKEY в таблице GD_DOCUMENT.' 
          || ' Значение: ' || NEW.EDITORKEY;
      RDB$SET_CONTEXT('USER_TRANSACTION', 'REF_CONSTRAINT_UNLOCK', '1');
      INSERT INTO gd_ref_constraint_data (constraintkey, value_data, value_count)
        VALUES (148740481, NEW.EDITORKEY, 1);
      RDB$SET_CONTEXT('USER_TRANSACTION', 'REF_CONSTRAINT_UNLOCK', '0');
    END
  END
END

С помощью показанных выше триггеров осуществляется проверка значения поля EDITORKEY после вставки или изменения записи. Сначала значение ищется в таблице gd_ref_constraint_data. Если его там нет, то сверяемся с таблицей справочником. Если значение найдено, то заносим его в gd_ref_constraint_data. Если указан недопустимый ключ, то сигнализируем об этом исключением.

Следующие три триггера создаются для таблицы справочника и, во-первых, синхронизирует изменения в ней с содержимым таблицы gd_ref_constraint_data, а, во-вторых, выполняют правила ON UPDATE и ON DELETE для внешнего ключа. 

CREATE OR ALTER TRIGGER gd_bi_ref_rel_148740481 FOR gd_people
  active 
  BEFORE INSERT 
  POSITION 32000
AS
BEGIN
  RDB$SET_CONTEXT('USER_TRANSACTION', 'REF_CONSTRAINT_UNLOCK', '1');
  INSERT INTO gd_ref_constraint_data (constraintkey, value_data, value_count) 
    VALUES (148740481, NEW.CONTACTKEY, 1);
  RDB$SET_CONTEXT('USER_TRANSACTION', 'REF_CONSTRAINT_UNLOCK', '0');
END
CREATE OR ALTER TRIGGER gd_au_ref_rel_148740481 FOR gd_people
  active 
  after UPDATE 
  POSITION 32000
AS
BEGIN
  IF (NEW.CONTACTKEY = OLD.CONTACTKEY) THEN
    EXIT;
 
  /* ON UPDATE CASCADE */
  UPDATE GD_DOCUMENT SET EDITORKEY = NEW.CONTACTKEY WHERE EDITORKEY = OLD.CONTACTKEY;
 
  RDB$SET_CONTEXT('USER_TRANSACTION', 'REF_CONSTRAINT_UNLOCK', '1');
  UPDATE gd_ref_constraint_data SET value_data = NEW.CONTACTKEY WHERE value_data = OLD.CONTACTKEY
    AND constraintkey = 148740481;
  RDB$SET_CONTEXT('USER_TRANSACTION', 'REF_CONSTRAINT_UNLOCK', '0');
END
CREATE OR ALTER TRIGGER gd_ad_ref_rel_148740481 FOR gd_people
  active 
  after DELETE 
  POSITION 32000
AS
BEGIN
  /* ON DELETE RESTRICT */
  IF (EXISTS (SELECT * FROM GD_DOCUMENT WHERE EDITORKEY = OLD.CONTACTKEY)) THEN 
    EXCEPTION gd_e_fkmanager 'Нарушение ссылочной целостности по полю EDITORKEY в таблице GD_DOCUMENT.';
 
  RDB$SET_CONTEXT('USER_TRANSACTION', 'REF_CONSTRAINT_UNLOCK', '1');
  DELETE FROM gd_ref_constraint_data WHERE value_data = OLD.CONTACTKEY
    AND constraintkey = 148740481;
  RDB$SET_CONTEXT('USER_TRANSACTION', 'REF_CONSTRAINT_UNLOCK', '0');
END

Совместимость с существующим кодом

  1. Программный код, который получает информацию о структуре БД через глобальный объект atDatabase, будет работать корректно в неизменном виде.
  2. Отличить сконвертированный внешний ключ от оригинального можно по значению атрибута IndexName класса TatForeignKey. У сконвертированного ключа оно равно пустой строке.
  3. Если информация о ссылках извлекается напрямую запросом из RDB$ таблиц, как показано ниже: 
SELECT 
  RC.RDB$RELATION_NAME AS RELATIONNAME,
  RC.RDB$CONSTRAINT_NAME AS CONSTRAINTNAME,
  RC.RDB$INDEX_NAME AS INDEXNAME,
  INDSEG.RDB$FIELD_NAME AS FIELDNAME,
  INDSEG.RDB$FIELD_POSITION AS FIELDPOS,
  REFC.RDB$CONST_NAME_UQ AS FCONSTRAINT,
  RC2.RDB$RELATION_NAME AS FRELATIONNAME,
  RC2.RDB$INDEX_NAME AS FINDEXNAME,
  INDSEG2.RDB$FIELD_NAME AS FFIELDNAME,
  REFC.RDB$UPDATE_RULE AS UPDATE_RULE,
  REFC.RDB$DELETE_RULE AS DELETE_RULE
FROM 
  RDB$RELATION_CONSTRAINTS RC 
    JOIN 
      RDB$INDEX_SEGMENTS INDSEG 
    ON 
      RC.RDB$INDEX_NAME = INDSEG.RDB$INDEX_NAME 
    JOIN 
      RDB$REF_CONSTRAINTS REFC 
    ON 
      REFC.RDB$CONSTRAINT_NAME = RC.RDB$CONSTRAINT_NAME, 
 
  RDB$RELATION_CONSTRAINTS RC2 
    JOIN 
      RDB$INDEX_SEGMENTS INDSEG2 
    ON 
      RC2.RDB$INDEX_NAME = INDSEG2.RDB$INDEX_NAME 
 
WHERE 
  RC2.RDB$CONSTRAINT_NAME = REFC.RDB$CONST_NAME_UQ 
    AND 
  INDSEG.RDB$FIELD_POSITION = INDSEG2.RDB$FIELD_POSITION 
 
ORDER BY 
  1, 2, 5

то в запрос следует через UNION ALL добавить извлечение информации о псевдо ключах из таблицы gd_ref_constraints: 

...
 
UNION ALL
 
SELECT 
  CONSTRAINT_REL AS RELATIONNAME, 
  CONSTRAINT_NAME AS CONSTRAINTNAME, 
  '' AS INDEXNAME, 
  CONSTRAINT_FIELD AS FIELDNAME, 
  0 AS FIELDPOS,
  CONST_NAME_UQ AS FCONSTRAINT, 
  REF_REL AS FRELATIONNAME, 
  '' AS FINDEXNAME, 
  REF_FIELD AS FFIELDNAME, 
  UPDATE_RULE AS UPDATE_RULE, 
  DELETE_RULE AS DELETE_RULE 
FROM 
  gd_ref_constraints
WHERE 
  ref_state <> 'ORIGINAL' 
ORDER BY 
  1, 2, 5

Известные ограничения

  • Отключение внешних ключей невозможно на базе данных с настроенной репликацией, так как репликатор полагается на информацию о связях, полученную из RDB$ таблиц.
  • Правила ON DELETE SET DEFAULT и ON UPDATE SET DEFAULT не поддерживаются. Внешние ключи с такими правилами не будут доступны для конвертации.
  • Следует понимать, что удаление внешнего ключа означает и удаление индекса. В связи с этим операции удаления записей из справочников, на которые указывают сконвертированные внешние ключи с правилом ON DELETE CASCADE могут выполняться длительное время, если таблица с внешней ссылкой содержит большое число записей. То же самое можно сказать и об операции изменения первичного ключа и правила ON UPDATE CASCADE.

Очистка таблицы gd_ref_constraint_data

От неиспользуемых значений ключей. 

EXECUTE BLOCK
AS
  DECLARE VARIABLE id dintkey;
  DECLARE VARIABLE constraint_rel dname;
  DECLARE VARIABLE constraint_field dname;
BEGIN
  FOR
    SELECT id FROM gd_ref_constraints
    WHERE ref_state = 'ORIGINAL'
    INTO :id
  DO BEGIN
    EXECUTE STATEMENT
    'DELETE FROM gd_ref_constraint_data WHERE constraintkey = ' || :id
    WITH AUTONOMOUS TRANSACTION;
  END
 
  FOR
    SELECT id, constraint_rel, constraint_field FROM gd_ref_constraints
    WHERE ref_state = 'TRIGGER'
    INTO :id, :constraint_rel, :constraint_field
  DO BEGIN
    EXECUTE STATEMENT
    'DELETE FROM gd_ref_constraint_data WHERE constraintkey = ' || :id ||
    'AND NOT value_data IN (SELECT ' || :constraint_field || ' FROM ' || :constraint_rel || ')'
    WITH AUTONOMOUS TRANSACTION;
  END
END

См. также

Замена FOREIGN KEY (постановка)

Источник — «https://www.gsbelarus.com/gs/wiki/index.php?title=%D0%9E%D1%82%D0%BA%D0%BB%D1%8E%D1%87%D0%B5%D0%BD%D0%B8%D0%B5_%D0%B2%D0%BD%D0%B5%D1%88%D0%BD%D0%B8%D1%85_%D0%BA%D0%BB%D1%8E%D1%87%D0%B5%D0%B9&oldid=7820»
Персональные инструменты
Пространства имён

Варианты
Действия
Навигация
Инструменты