Как работает join индекс и когда его использовать
Что проверяют
Знаете ли вы, что «join-индекс» в строгом смысле есть не во всех СУБД, и умеете ли ускорить соединение таблиц в той базе, с которой работаете. Для Python-разработчика следом обычно спрашивают, как это выглядит в Django или SQLAlchemy и как вы проверяете, что индекс реально используется.
Как отвечать
Сначала разведите два понятия:
- join-индекс как отдельный объект: есть в Oracle (bitmap join index) и Teradata. Он заранее хранит результат соединения, например по колонке таблицы измерения указывает прямо на строки таблицы фактов. Нужен в хранилищах данных со схемой «звезда», где много чтения и мало записи;
- индексы под соединение в обычной базе: в PostgreSQL и MySQL отдельного join-индекса нет, соединение ускоряет обычный B-tree на колонке, по которой соединяют, чаще всего на внешнем ключе.
Дальше механика. Планировщик выбирает способ соединения: nested loop с поиском по индексу во внутренней таблице, hash join или merge join. Индекс по orders.user_id делает nested loop дешёвым, когда из внешней таблицы берут немного строк. Если соединяются почти целые таблицы, база возьмёт hash join, и индекс там не нужен.
Важная деталь: PostgreSQL не создаёт индекс на внешний ключ автоматически, MySQL (InnoDB) создаёт. Django сам ставит индекс на ForeignKey (db_index=True по умолчанию), а в SQLAlchemy про index=True на такой колонке нужно помнить самому.
Если нужен именно заранее посчитанный результат соединения, в PostgreSQL для этого есть материализованное представление: CREATE MATERIALIZED VIEW, которое обновляется только по команде REFRESH MATERIALIZED VIEW. Проверку показывайте через EXPLAIN ANALYZE.
Где спотыкаются
Пересказывают определение из учебника и не могут сказать, в какой СУБД это существует. Интервьюер спрашивает про ваш PostgreSQL, а слышит про Oracle.
Считают, что индекс ускоряет любое соединение. На больших выборках hash join без всяких индексов бывает быстрее.
Лечат индексами проблему N+1 в ORM. Сотня запросов из цикла по order.user останется сотней запросов, тут нужны select_related или prefetch_related в Django и joinedload или selectinload в SQLAlchemy.