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

#mysql #sql #база_данных

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

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

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

Ответы

Ответ 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) - в этих
случаях нам тоже нужен быстрый (индексированный) доступ ко вторичным ключам.