Просмотр исходного текста страницы Отключение внешних ключей

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

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

Для анализа индексов можно изучать логи, выдаваемые утилитой [[gstat]], или воспользоваться утилитой [http://www.ib-aid.com/products/firebird_interbase/monitoring/IBAnalyst IBAnalyst]. 

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

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

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

http://gsbelarus.com/gs/images/gs/2009/gd_ref_constraints.jpg

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

{| class="wikitable" border="1"
|-
| 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 || Количество записей в таблице на которую идет ссылка.
|-
|}

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

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

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

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

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

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

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

http://gsbelarus.com/gs/images/gs/2009/gd_ref_constraints_dlg.jpg

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

{| class="wikitable" border="1"
|-
| Записей во внешнем ключе || Справа от кнопки выводится общее количество записей во внешнем ключе. Нажав на кнопку, можно выполнить запрос для просмотра содержимого таблицы с внешним ключем.
|-
| Уникальных значений во внешнем ключе  || Справа от кнопки выводится количество уникальных значений во внешнем ключе. Нажав на кнопку можно выполнить запрос и просмотреть распределение этих значений.
|-
| Записей в таблице справочнике  || Справа от кнопки выводится общее количество записей в таблице, на которую ссылается внешний ключ. Нажав на кнопку, можно выполнить запрос для просмотра содержимого этой таблицы.
|-
| Показать текущие значения  || Кнопка доступна только для внешнего ключа в состоянии TRIGGER и позволяет просмотреть значения в таблице [[GD_REF_CONSTRAINT_DATA]].
|-
|}

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

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

# Открыть окно Менеджера внешних ключей.
# Пересчитать статистику индексов с помощью кнопки http://gsbelarus.com/gs/images/gs/2009/recalc_stat.jpg
# Отобрать индексы для конвертации. На базах с большим количеством внешних ключей удобно использовать фильтры для отбора нужных записей.
# Открыть на редактирование отобранные индексы и установить значение TRIGGER в поле Конвертировать. Сохранить изменения.
# Запустить процесс конвертации с помощью кнопки http://gsbelarus.com/gs/images/gs/2009/start_process.jpg
# Выполнить переподключение к базе данных.

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

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

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

Проиллюстрируем работу псевдо ключа на следующем примере. Дано: таблица GD_DOCUMENT и поле EDITORKEY, которое ссылается на справочник контактов:
<syntaxhighlight lang="SQL">
ALTER TABLE GD_DOCUMENT ADD CONSTRAINT GD_FK_DOCUMENT_EDITORKEY 
FOREIGN KEY (EDITORKEY) REFERENCES GD_PEOPLE (CONTACTKEY) 
ON UPDATE CASCADE
</syntaxhighlight>

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

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

<syntaxhighlight lang="SQL">
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
</syntaxhighlight>

<syntaxhighlight lang="SQL">
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
</syntaxhighlight>

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

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

<syntaxhighlight lang="SQL">
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
</syntaxhighlight>

<syntaxhighlight lang="SQL">
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
</syntaxhighlight>

<syntaxhighlight lang="SQL">
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
</syntaxhighlight>

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

# Программный код, который получает информацию о структуре БД через глобальный объект [[Gedemin TLB.IGedeminApplication.atDatabase|atDatabase]], будет работать корректно в неизменном виде. 
# Отличить сконвертированный внешний ключ от оригинального можно по значению атрибута '''IndexName''' класса [[At_Classes.TatForeignKey|TatForeignKey]]. У сконвертированного ключа оно равно пустой строке.
# Если информация о ссылках извлекается напрямую запросом из RDB$ таблиц, как показано ниже:

<syntaxhighlight lang="SQL">
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 
</syntaxhighlight>

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

<syntaxhighlight lang="SQL">

...
       
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 
</syntaxhighlight>

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

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

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

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

<syntaxhighlight lang="SQL">
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

</syntaxhighlight>

=== См. также ===

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

[[Category:Школа системного администратора]]

__NOTOC__