Индекс для внешнего ключа таблицы БД

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

Сперва обратимся к документации PostgreSQL:

A foreign key must reference columns that either are a primary key or form a unique constraint, or are columns from a non-partial unique index. This means that the referenced columns always have an index to allow efficient lookups on whether a referencing row has a match. Since a DELETE of a row from the referenced table or an UPDATE of a referenced column will require a scan of the referencing table for rows matching the old value, it is often a good idea to index the referencing columns too. Because this is not always needed, and there are many choices available on how to index, the declaration of a foreign key constraint does not automatically create an index on the referencing columns.

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

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

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

Добавим индекс на внешний ключ и проведем повторный эксперимент.

После добавления индекса на внешний ключ и повторного выполнения запроса:

• Без индекса на внешний ключ операция удаления заняла 690 мс.
• С индексом — всего 0.101 мс.

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

Мета информация

Область:: 00 Базы Данных
Родитель:: Оптимизация SQL запросов
Источник::
Создана:: 2024-10-11
Автор::

Дополнительные материалы

Дочерние заметки