Что важно при размещении колонок при создании составного индекса?

Критерии размещения колонок в составном индексе

При создании составного индекса (composite index) в SQL-базах данных порядок столбцов является ключевым фактором, определяющим эффективность индекса для различных запросов. Вот основные принципы:

1. Принцип левого префикса (Leftmost Prefix)

Большинство СУБД (MySQL, PostgreSQL, SQL Server) используют индекс по левому префиксу. Это означает, что индекс (col1, col2, col3) может быть использован для запросов, фильтрующих по:

• col1
• col1, col2
• col1, col2, col3

Но НЕ для запросов с фильтрацией только по col2 или col3.

-- Индекс (country, city, age) будет использован:
SELECT * FROM users WHERE country = 'RU';
SELECT * FROM users WHERE country = 'RU' AND city = 'Moscow';
SELECT * FROM users WHERE country = 'RU' AND city = 'Moscow' AND age > 25;

-- НЕ будет использован (или будет использован неэффективно):
SELECT * FROM users WHERE city = 'Moscow';
SELECT * FROM users WHERE age > 25;

2. Селективность столбцов

Располагайте более селективные столбцы (с большим количеством уникальных значений) левее, если это не противоречит другим правилам. Это помогает быстрее отфильтровать данные на ранних этапах.

НО: Селективность не всегда является главным критерием — важен конкретный паттерн запросов.

3. Учет частых запросов

Анализируйте WHERE-условия типичных запросов:

• Столбцы с операторами = должны идти перед столбцами с диапазонными операторами (>, <, BETWEEN, LIKE).
• Индекс (country, city, age) подходит для:

WHERE country = 'RU' AND city = 'Moscow' AND age > 25  -- все условия
WHERE country = 'RU' AND city LIKE 'M%'               -- LIKE после =
WHERE country = 'RU' AND age > 25                     -- пропущен city (используется только country)

4. Использование для сортировки (ORDER BY)

Если запросы часто используют сортировку, учитывайте:

• Для ORDER BY col2, col3 индекс (col1, col2, col3) НЕ поможет сортировке, если col1 не в WHERE.
• Для ORDER BY col2 индекс (col1, col2) позволит избежать сортировки, только если запрос фильтрует по col1.

-- Индекс (department, salary)
SELECT * FROM employees 
WHERE department = 'IT' 
ORDER BY salary;  -- Сортировка будет выполнена с использованием индекса

SELECT * FROM employees 
ORDER BY salary;  -- Полная сортировка, индекс не поможет

5. Покрывающие индексы (Covering Index)

Если индекс содержит ВСЕ столбцы, запрашиваемые в SELECT, запрос может быть выполнен полностью по индексу без обращения к таблице (index-only scan).

-- Для запроса:
SELECT id, name FROM products WHERE category = 'books' AND price > 500;

-- Оптимальный покрывающий индекс:
CREATE INDEX idx_cover ON products(category, price, id, name);
-- Порядок: категория (фильтр), цена (диапазон), затем включаемые столбцы

6. Ограничения на длину индекса

Учитывайте максимальную длину индекса (например, 3072 байта в InnoDB MySQL). Длинные текстовые столбцы лучше не включать в начало индекса или использовать префиксы.

7. Практические рекомендации

1. Анализируйте реальные запросы из логов или мониторинга
2. Тестируйте разные варианты с EXPLAIN ANALYZE
3. Располагайте столбцы в порядке от наиболее часто используемых в WHERE к менее используемым
4. Для равенства → диапазон → сортировка типичный паттерн:
   (equality_columns, range_columns, sorting_columns, including_columns)
5. Учитывайте кардинальность в контексте запросов, а не изолированно

Пример оптимизации

Для частого запроса:

SELECT * FROM orders 
WHERE user_id = 123 
  AND status = 'processed' 
  AND created_at BETWEEN '2024-01-01' AND '2024-06-01'
ORDER BY total_amount DESC;

Оптимальный индекс:

CREATE INDEX idx_optimized ON orders(user_id, status, created_at, total_amount);
-- user_id, status: равенства (идут первыми)
-- created_at: диапазон (после равенств)
-- total_amount: сортировка (после фильтрации, хотя DESC может требовать отдельного рассмотрения)

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