Как нужно отсортировать колонки в индексе для быстрого поиска по нескольким колонкам?
Что проверяют
Знаете ли вы правило левого префикса и понимаете ли, что порядок колонок определяют запросы, а не одна селективность. Следом обычно дают конкретный запрос, например WHERE status = ? AND created_at > ? ORDER BY created_at, и просят составить индекс.
Как отвечать
B-tree индекс по (a, b, c) отсортирован сначала по a, внутри по b, затем по c. Из этого следует всё остальное:
- левый префикс: индекс помогает условиям по
a, поa, bи поa, b, c. Запрос только поbего толком не использует. В MySQL 8 и PostgreSQL 18 есть skip scan, но он выгоден, лишь когда уaмало различных значений; - равенство впереди, диапазон в конце: после первой колонки с диапазоном (
>,BETWEEN,LIKE 'abc%') следующие уже не сужают поиск. Для примера выше нужен(status, created_at), а не наоборот; - сортировка: тот же индекс отдаёт строки уже упорядоченными, и база обходится без узла
Sort. При смешанныхASCиDESCнаправление колонок в индексе важно; - покрывающий индекс: выбираемые колонки в конце ключа или в
INCLUDE(PostgreSQL, SQL Server) дают index-only scan без похода в таблицу.
Селективность решает, когда несколько колонок стоят в равенстве и есть выбор. Тогда первой разумно поставить ту, по которой фильтруют чаще и отдельно, чтобы один индекс закрывал больше запросов.
Проверка всегда через EXPLAIN ANALYZE: какой индекс выбран, условие попало в Index Cond или в Filter, сколько строк отброшено. В Java-проекте смотрите на реальный SQL, который генерирует Hibernate, а не на то, что ожидаете от репозитория Spring Data.
Где спотыкаются
Повторяют «самую селективную колонку первой» как закон. Колонка с диапазоном, поставленная первой, делает остальные бесполезными, какой бы селективной она ни была.
Заводят отдельный индекс на каждую колонку и рассчитывают, что база их сложит. Bitmap-объединение бывает, но обычно проигрывает одному составному индексу.
Не замечают, что индекс (a, b) делает лишним отдельный индекс по a. Держат оба и платят за запись дважды.