Отключение внешних ключей
Индекс занимает место в файле базе данных и требует времени для перестроения и обновления при каждой операции изменения данных. В большинстве случаев преимущества от использования индекса перевешивают его недостатки. В оставшихся -- индекс ничем не может нам помочь и только ухудшает производительность сервера. Можно выделить три категории "вредных" индексов:
- Бесполезный индекс -- содержит только одно уникальное значение. Не используется оптимизатором для извлечения данных. Однозначно должен быть удален для экономии места.
- Плохой индекс -- количество уникальных значений которого много меньше общего количества записей. Такой индекс может быть удален, но для принятия окончательного решения требуется дополнительная информация о распределении значений и об использовании этого индекса в запросах. Например, индекс по аналитическому признаку в таблице с проводками может иметь низкую селективность в целом, однако быть полезным при использовании в коомбинации с конкретным счетом.
- Неиспользуемый (редкоиспользуемый) индекс -- такой индекс может обладать приемлемой селективностью, однако никогда не использоваться вследствии логической организации задачи. Например, очень редко извлекаются данные в разрезе полей CREATORKEY, EDITORKEY.
Для анализа индексов можно изучать логи, выдаваемые утилитой gstat, или воспользоваться утилитой IBAnalyst.
К сожалению, Firebird не позволяет создавать внешние ключи (FOREIGN KEY) без индекса. В Гедымине мы пошли на определенные ухищрения для создания псевдо внешних ключей. Такой "внешний ключ" контролирует ссылочную целостность с помощью системы тригеров и не создает индекс.
Управление внешними ключами осуществляется с помощью Менеджера внешних ключей. Откроем его окно:
Исследователь -> Сервис -> Атрибуты -> Внешние ключи
Ниже приведено описание колонок таблицы 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 | Количество записей в таблице на которую идет ссылка. |
Колонки с количеством записей и уникальных значений являются вычисляемыми. Соответствующие значения выводятся на основании имеющейся статистики индексов и могут отличаться от реального количества записей и уникальных значений в таблице. Запустить фоновый процесс обновления статистики можно с помощью кнопки 
на панели инструментов.
Состояние внешнего ключа: ORIGINAL и TRIGGER
Текущее состояние внешнего ключа записано в поле ref_state, которое может принимать два значения:
- ORIGINAL
- Внешняя ссылка, созданная стандартным образом через ограничение FOREIGN KEY.
- TRIGGER
- Внешняя ссылка без индекса. Целостность данных поддерживается с помощью системы триггеров.
Для прямой или обратной конвертации внешних ключей необходимо изменить значение поля ref_next_state в диалоговом окне и запустить процесс конвертации.
Диалоговое окно внешнего ключа
Предназначение кнопок в разделе Статистика:
| Записей во внешнем ключе | Справа от кнопки выводится общее количество записей во внешнем ключе. Нажав на кнопку, можно выполнить запрос для просмотра содержимого таблицы с внешним ключем. |
| Уникальных значений во внешнем ключе | Справа от кнопки выводится количество уникальных значений во внешнем ключе. Нажав на кнопку можно выполнить запрос и просмотреть распределение этих значений. |
| Записей в таблице справочнике | Справа от кнопки выводится общее количество записей в таблице, на которую ссылается внешний ключ. Нажав на кнопку, можно выполнить запрос для просмотра содержимого этой таблицы. |
| Показать текущие значения | Кнопка доступна только для внешнего ключа в состоянии TRIGGER и позволяет просмотреть значения в таблице GD_REF_CONSTRAINT_DATA. |
Конвертация внешних ключей
Последовательность действий включает следующие шаги:
- Открыть окно Менеджера внешних ключей.
- Пересчитать статистику индексов с помощью кнопки
- Отобрать индексы для конвертации.
- Открыть на редактирование отобранные индексы и установить значение TRIGGER в поле Конвертировать.
- Запустить процесс конвертации с помощью кнопки
- Выполнить переподключение к базе данных.
Как это работает
Проиллюстрируем работу псевдо ключа на следующем примере. Дано: таблица GD_DOCUMENT и поле EDITORKEY, которое ссылается на справочник контактов:
ALTER TABLE GD_DOCUMENT ADD CONSTRAINT GD_FK_DOCUMENT_EDITORKEY FOREIGN KEY (EDITORKEY) REFERENCES GD_PEOPLE (CONTACTKEY) ON UPDATE CASCADE
Открываем окно Менеджера внешних ключей и задаем сконвертировать ключ по полю EDITORKEY. Запускаем конвертацию и переподключаемся к базе данных.
В процессе конвертации ограничение 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. Если указан недопустимый ключ, то сигнализируем об этом исключением.
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; 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 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
Совместимость с существующим кодом
Программный код, который получает информацию о структуре БД через глобальный объект atDatabase, будет работать корректно в неизменном виде. Отличить сконвертированный внешний ключ от оригинального можно по значению атрибута IndexName класса TatForeignKey. У сконвертированного ключа оно равно пустой строке.
Если информация о ссылках получается напрямую запросом из 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:
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 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.
