Важен ли порядок полей в составном индексе

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

Знаете ли вы правило левого префикса и понимаете ли, почему оно вытекает из устройства индекса, а не из прихоти планировщика.

Как отвечать

Да, порядок критичен. Составной индекс хранит записи отсортированными сначала по первой колонке, затем внутри неё по второй и так далее. Это как телефонный справочник по фамилии и имени: найти всех Ивановых легко, найти всех Сергеев нельзя.

Отсюда правило: индекс работает, пока запрос использует непрерывный левый префикс его колонок. Индекс (a, b, c) помогает запросам по a, по a и b, по всем трём. Запросу только по b или по b и c он практически бесполезен.

Как выбирать порядок:

• колонки с равенством идут первыми, колонка с диапазоном последней. После условия вида > или BETWEEN следующие колонки индекса для отбора уже не работают, только для сортировки;
• если запрос сортирует результат, порядок и направление колонок в индексе стоит согласовать с ORDER BY, тогда отдельная сортировка не понадобится;
• селективность важна, но вторична по отношению к форме запросов: колонка, по которой почти никогда не фильтруют, впереди бесполезна, какой бы селективной она ни была.

create index on events (user_id, created_at desc);   -- равенство, потом диапазон

Полезно добавить два уточнения. Первое: отдельный индекс по первой колонке при наличии составного обычно лишний. Второе: некоторые базы умеют пропускающее сканирование, когда первая колонка низкоселективна, но полагаться на это не стоит, и проверять всё равно нужно планом через EXPLAIN.

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

Ставят диапазонное поле первым и теряют вторую колонку.

Заводят по индексу на каждую колонку вместо одного составного и получают просадку на записи.

Не проверяют планом, что индекс реально выбран: наличие подходящего индекса ещё не значит, что планировщик его взял.