Какие поля лучше использовать для индекса при соединении таблиц
Что проверяют
Понимаете ли вы, как база выполняет соединение и в каком случае индекс ей вообще пригодится. Для data-специальности следом обычно спрашивают, что делать с джойном на сотнях миллионов строк в хранилище, где классических индексов нет.
Как отвечать
Базовое правило: индексируют колонку соединения на той стороне, где база ищет совпадения. Первичный ключ проиндексирован всегда, а внешний ключ (orders.customer_id) в PostgreSQL сам индекс не получает, его создают отдельно. Дальше привяжите ответ к алгоритмам соединения:
- Nested Loop: для каждой строки внешней выборки ищет пару во внутренней таблице. Выигрывает от индекса на внутренней стороне, когда внешняя выборка маленькая;
- Hash Join: строит хэш-таблицу по меньшей стороне и прогоняет через неё большую. Индекс по ключу соединения ему не нужен, помогает только индекс под фильтр
WHERE; - Merge Join: сливает два отсортированных потока. Индекс в порядке ключа избавляет от отдельной сортировки.
Если соединение идёт по нескольким колонкам, нужен составной индекс в том же составе. Колонки из SELECT, добавленные в индекс через INCLUDE, позволяют вообще не читать таблицу.
В аналитическом стеке рычаги другие. В Spark индексов нет: маленькую таблицу рассылают на все узлы через broadcast join, а большие заранее бакетируют по ключу соединения, чтобы избежать shuffle. В ClickHouse при хэш-джойне в памяти строится правая таблица, поэтому меньшую ставят справа, а ключ сортировки ORDER BY помогает фильтрации, а не самому джойну. Проверяется всё по плану: EXPLAIN ANALYZE или Spark UI.
Где спотыкаются
Считают, что внешний ключ индексируется автоматически. В PostgreSQL это не так, и джойн вместе с каскадным удалением идут полным сканом дочерней таблицы.
Соединяют по колонкам разных типов, например int и text. Неявное приведение не даёт использовать индекс.
Добавляют индекс и не смотрят план. На большой выборке база всё равно выберет Hash Join, и новый индекс только замедлит запись.