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

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

Индекс не заменяет сам внешний ключ и не влияет на корректность данных напрямую. Он ускоряет проверку ссылочной целостности, особенно при больших таблицах и действиях ON DELETE или ON UPDATE.

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

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

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

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

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

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

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

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

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

Минимальная иллюстрация:

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE INDEX orders_customer_id_idx
    ON orders (customer_id);

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

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

Также необходимо учитывать составные индексы. Индекс по столбцам (customer_id, status) может помочь искать строки по customer_id, тогда как индекс только по (status, customer_id) не всегда столь же эффективен для поиска по одному идентификатору клиента.

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

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

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

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

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

Обязан ли индекс на внешнем ключе существовать для корректности ссылочной целостности?

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

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

Почему индекс на родительском ключе не решает проблему поиска дочерних строк?

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

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

Всегда ли каскадное удаление делает индекс внешнего ключа обязательным?

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

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