Какие поля лучше использовать для индекса при соединении таблиц

Что проверяют

Для аналитика это вопрос о том, понимаете ли вы, почему отчётный запрос работает десять минут и что попросить у разработчиков или DBA. Следом часто дают запрос с тремя джойнами и просят показать, где нужны индексы.

Как отвечать

Главные кандидаты:

• ключи соединения: колонки из условия ON. Первичный ключ уже проиндексирован, а внешний ключ (orders.client_id) в PostgreSQL индекс сам не получает; в MySQL InnoDB такой индекс создаётся автоматически;
• колонки фильтра рядом с ключом: если отчёт всегда берёт «заказы клиента за месяц», полезен составной индекс (client_id, created_at). Сначала поле с равенством, потом диапазон;
• селективные поля: client_id отсекает почти всё, а status с тремя значениями сам по себе от индекса почти не выигрывает.

Дальше скажите, как вы это проверяете, а не угадываете. План смотрят через EXPLAIN (в PostgreSQL EXPLAIN ANALYZE, в MS SQL фактический план выполнения): видно, какой тип соединения выбран, где идёт полное чтение таблицы и сколько строк ожидалось против фактических. Полный скан маленького справочника нормален, проблема в скане большой таблицы фактов внутри вложенного цикла.

Запрос часто ускоряется и без новых индексов: отфильтровать данные до соединения, не соединять по вычисляемому выражению, не тащить SELECT *. Если отчёт строится по хранилищу вроде ClickHouse или Greenplum, там решает ключ сортировки или распределения, об этом достаточно одной фразы.

В требованиях к разработке это оформляется так: какие запросы, как часто, какой объём данных и какое время ответа допустимо. Конкретный индекс выбирают вместе с командой, потому что каждый индекс замедляет запись.

Где спотыкаются

Соединяют по выражению: ON lower(a.email) = lower(b.email) или ON cast(a.id as text) = b.ext_id. Обычный индекс при этом не используется.

Предлагают индекс на каждую колонку отчёта и не думают о таблице, в которую постоянно идут вставки.

Ставят в составном индексе диапазон по дате первым. Тогда поиск по конкретному клиенту пользуется индексом плохо.