Краткий ответ

Индекс на внешнем ключе позволяет быстро найти дочерние строки, которые ссылаются на удаляемую или изменяемую родительскую строку. Без него СУБД может выполнить полное сканирование дочерней таблицы, чтобы убедиться, что ограничение внешнего ключа не нарушается либо определить строки для каскадного действия.

Индекс не гарантирует отсутствие блокировок и не всегда устраняет дорогостоящую работу: если дочерних строк много, их всё равно потребуется обработать. Но он резко сокращает стоимость поиска таких строк, особенно когда родительская операция затрагивает немного ключей.

Исторический контекст

Внешние ключи появились как механизм декларативной ссылочной целостности: СУБД сама контролирует согласованность связанных таблиц, вместо того чтобы полностью перекладывать эту обязанность на прикладной код.

Проверка связи требует поиска по дочернему столбцу внешнего ключа. Поэтому индексы на таких столбцах стали естественным дополнением к ограничениям: они не нужны для логической корректности, но нужны для предсказуемой стоимости проверок и каскадных операций.

Постановка проблемы

Рассмотрим связь между родителями и дочерними объектами. При удалении родительской строки СУБД должна проверить, существуют ли дочерние строки с соответствующим внешним ключом. В зависимости от действия ограничения она либо отклоняет удаление, либо удаляет или изменяет найденные дочерние строки.

Если на дочернем внешнем ключе нет индекса, поиск может потребовать чтения всей дочерней таблицы. Это особенно опасно при удалении нескольких родителей, массовом обновлении ключей и каскадных операциях: растёт объём чтения, продолжительность блокировок и вероятность конфликтов с параллельными транзакциями.

Индекс на родительском первичном или уникальном ключе обычно создаётся вместе с самим ограничением. Однако индекс на дочернем внешнем ключе во многих СУБД автоматически не создаётся, поэтому его наличие нужно проверять отдельно.

Подробное решение

Для внешнего ключа важен индекс, начинающийся с его столбца или подходящего префикса составного индекса. Тогда СУБД может выполнить индексный поиск по значению родительского ключа и быстро определить наличие связанных строк.

При ограничении без каскада найденная строка может немедленно привести к отказу операции. При каскадном удалении индекс помогает найти именно подлежащие удалению дочерние строки, после чего СУБД всё равно должна изменить данные и поддержать все индексы дочерней таблицы.

Индекс также полезен при обновлении ключа родительской строки, если такое обновление разрешено: СУБД должна проверить или изменить дочерние ссылки. Для обычных запросов приложения этот индекс тоже может ускорять соединения и фильтрацию по внешнему ключу, но его основная роль в данном сценарии — ускорить внутреннюю проверку ограничения.

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

Точный набор блокировок, способ проверки и поведение каскадов зависят от СУБД и уровня изоляции. Поэтому индекс не следует рассматривать как средство устранения блокировок: он уменьшает время и объём чтения, но массовая операция всё равно может затронуть много строк.

Минимальная схема обычно выглядит так:

CREATE TABLE parent (
    id bigint PRIMARY KEY
);

CREATE TABLE child (
    id bigint PRIMARY KEY,
    parent_id bigint NOT NULL,
    FOREIGN KEY (parent_id) REFERENCES parent(id)
);

CREATE INDEX ix_child_parent_id ON child(parent_id);

В этом примере последняя строка не меняет правила целостности, но даёт СУБД эффективный путь к дочерним строкам при удалении или изменении parent.id.

Ситуация из практики

В системе заказов удаление клиента стало периодически выполнять полное сканирование таблицы заказов. Клиентские записи удалялись редко, но таблица заказов содержала десятки миллионов строк, поэтому операция долго удерживала блокировки и конкурировала с рабочими запросами.

Рассматривались три варианта. Перенос проверки в приложение не устранял гонки между транзакциями и ослаблял гарантию целостности. Принудительное удаление через отдельную фоновую обработку уменьшало влияние на пользовательский запрос, но усложняло модель данных. Индекс на столбце внешнего ключа сохранял декларативное ограничение и уменьшал объём поиска.

Выбрали третий вариант, предварительно проверив план и фактическое распределение ключей. После создания индекса поиск связанных заказов стал адресным; каскадное удаление всё равно оставалось дорогим для клиентов с огромной историей, поэтому для таких клиентов дополнительно применили управляемое пакетное удаление. Это разделило две задачи: индекс ускорил поиск, а пакетная обработка ограничила длительность транзакций.

Что кандидаты часто упускают

1. Достаточно ли индекса только на первичном ключе родительской таблицы?

Нет. Индекс родительского ключа ускоряет поиск самой родительской строки, но не поиск дочерних строк, ссылающихся на неё. Для проверки внешнего ключа при удалении родителя нужен доступ к столбцу внешнего ключа в дочерней таблице.

2. Всегда ли индекс внешнего ключа делает удаление быстрым?

Нет. Он ускоряет обнаружение связанных строк, но не стоимость обработки самих строк. При каскадном удалении большого числа дочерних записей СУБД должна удалить их, обновить вторичные индексы, записать изменения в журнал и соблюдать блокировки. Индекс уменьшает стоимость поиска, но не отменяет эти операции.

3. Почему индекс на внешнем ключе может быть полезен даже без удалений родителей?

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