Как создать индекс в базе данных по трем полям с разной вариативностью и в каком порядке их располагать?

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

Знаете ли вы, как B-tree использует составной индекс, и не повторяете ли заученное «самое селективное поле первым». Следом дают конкретный запрос и просят выбрать порядок колонок под него.

Как отвечать

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

Рабочий алгоритм:

• сначала колонки с равенством: те, что стоят в WHERE через = или IN. Среди них первой ставьте ту, что встречается в большинстве запросов, тогда индекс обслужит больше сценариев;
• потом колонка с диапазоном: >, <, BETWEEN. После диапазона следующие колонки уже не сужают поиск по дереву, по ним только фильтруются записи при просмотре;
• потом сортировка: если запрос с ORDER BY ... LIMIT и порядок совпадает с индексом, база отдаёт строки без отдельной сортировки.

Селективность решает меньше, чем кажется. Если все три условия на равенство, точка поиска в дереве одна и та же при любом порядке колонок. Селективность важна, когда вы решаете, нужна ли колонка в индексе вообще и какой запрос обслужит его префикс. Чтобы читать без обращения к таблице, недостающие поля добавляют через INCLUDE (PostgreSQL 11+).

В Java-проекте индекс описывают в миграции Flyway или Liquibase, а аннотация @Index в @Table пригодится для генерации схемы в тестах. Проверка всегда одна: EXPLAIN ANALYZE на реальном запросе и реальном объёме данных.

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

Ставят первым поле с диапазоном, потому что оно самое вариативное, например дату. Тогда колонки после неё почти не помогают поиску.

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

Считают, что индекс (a, b, c) бесполезен без условия на a. Чаще так и есть, но в свежих версиях PostgreSQL и в MySQL 8 есть skip scan, который выручает при малом числе значений a.