Зачем индексировать внешний / вторичный (foreign key) ключ в MySQL?

216

04 августа 2018, 08:50

Есть понятие "ограничитель" в MySQL. Например, это первичный ключ (ключ должен быть уникален, поэтому каждый раз производится поиск "а не было ли этого ключа в предыдущих строках?" для ускорения которого автоматом столбец с первичным ключом индексируется) и ключ-кандидат (та же логика, что и с первичным ключом). То есть, есть логика: будет поиск каждый раз поиск, значит, надо проиндексировать.

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

ВОПРОС: зачем индексировать вторичный ключ? Это же, например, столбец с номерами преподавателей (а в таблице-списке расшифровывается какой препод какому номеру соответствует). Зачем что-то искать в столбце вида "1,2,5,1,3,10..."?

Answer 1

Основная причина - возможность быстрой проверки на "осиротелые вторичные ключи" (orphaned rows) в случае удаления (DELETE) или смены (UPDATE - лучше так не делать) значений первичного ключа.

Пример:

table master:

id (PK)   val
1         11
2         22
3         33

table slave:

id (PK)  master_id (FK)  val
1           1            111
2           1            112
3           2            311

при удалении:

delete from master where id = 3;

СУБД должна проверить - можно ли удалить PK == 3 для этого проверяются все таблицы содержащие вторичные ключи референцирующие столбец master.id и только если значение 3 не встречается ни в одном из вторичных ключей, тогда удаление разрешается.

Также существует возможность каскадного удаления (ON DELETE CASCADE) или перезаписи соответствующих значений вторичных ключей значением NULL (ON DELETE SET NULL) - в этих случаях нам тоже нужен быстрый (индексированный) доступ ко вторичным ключам.