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

Порядок полей в составном индексе (B-tree) определяет, для каких условий WHERE и ORDER BY этот индекс будет эффективен. Я руководствуюсь правилом: от наиболее селективного и часто используемого в равенствах — к наименее селективному и используемому в диапазонах или сортировке.

Алгоритм выбора порядка:

1. Поля с условиями = (равенство) идут первыми. Их порядок между собой менее критичен, но лучше начать с самого селективного.
2. Поля с условиями диапазона (>, <, BETWEEN, LIKE 'prefix%') или IN (который СУБД часто преобразует в диапазоны) идут после полей равенства.
3. Поля из ORDER BY или GROUP BY идут в конце, если они не используются в фильтрации диапазонами.

Пример из практики: У нас есть таблица orders и частые запросы вида:

SELECT * FROM orders 
WHERE status = 'shipped' 
  AND user_id = 123 
  AND created_at > '2024-01-01'
ORDER BY created_at DESC;

• status = 'shipped' — условие равенства, но низкая селективность (мало уникальных статусов).
• user_id = 123 — условие равенства, высокая селективность.
• created_at > '2024-01-01' — условие диапазона, также используется в ORDER BY.

Плохой индекс: (status, created_at, user_id) Индекс будет фильтровать по status, затем по диапазону created_at, и только потом сможет применить условие к user_id. Условие равенства user_id «запоздает».

Хороший индекс: (user_id, status, created_at)

1. Сначала находится точное совпадение по высокоселективному user_id.
2. Внутри этого подмножества фильтруется по status.
3. Для получившихся строк индекс уже отсортирован по created_at, что позволяет эффективно применить диапазонный фильтр > и сортировку ORDER BY created_at DESC (можно прочитать индекс в обратном порядке).

Как проверить селективность?

-- Примерная оценка селективности поля
SELECT 
    COUNT(DISTINCT status) / COUNT(*) as selectivity_status,
    COUNT(DISTINCT user_id) / COUNT(*) as selectivity_user_id
FROM orders;

Чем ближе значение к 1, тем выше селективность. Поле с большей селективностью выгоднее ставить раньше.

Важное ограничение: Составной индекс работает слева-направо. Если в запросе нет условия на первое поле индекса, СУБД, скорее всего, не сможет его использовать для фильтрации (хотя может для покрывающего индекса — INDEX ONLY SCAN).