Какие знаешь правила для эффективной работы составного индекса?

Правила эффективного использования составных индексов

Составные (композитные, многоколеночные) индексы — мощный инструмент оптимизации запросов, но их эффективность зависит от соблюдения ключевых принципов. Вот основные правила:

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

Наиболее важное правило: индекс (col1, col2, col3) может быть использован для запросов с:

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

Но НЕ для:

• col2 или col3 отдельно
• col2, col3 без col1

-- Индекс (user_id, status, created_at)
-- Работает: WHERE user_id = 5
-- Работает: WHERE user_id = 5 AND status = 'active'
-- Работает: WHERE user_id = 5 AND status = 'active' AND created_at > '2024-01-01'
-- НЕ РАБОТАЕТ: WHERE status = 'active' (пропущен user_id)

2. Порядок колонок имеет критическое значение

Располагайте колонки в порядке:

1. Колонки для равенства (WHERE col = value)
2. Колонки для диапазонов (WHERE col > value, BETWEEN)
3. Колонки для сортировки (ORDER BY)
4. Колонки для покрытия запроса (INCLUDE в некоторых СУБД)

-- Оптимальный порядок для запроса:
-- WHERE category_id = 5 AND price > 100 ORDER BY created_at DESC
-- Индекс: (category_id, price, created_at DESC)

3. Селективность и кардинальность

Располагайте более селективные колонки (с большим количеством уникальных значений) левее:

• Колонка с 1000 уникальных значений → более селективная
• Колонка с 2 значения (типа status) → менее селективная

-- Плохо: (status, user_id) -- status имеет мало уникальных значений
-- Лучше: (user_id, status) -- user_id более селективен

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

Индекс, содержащий ВСЕ необходимые для запроса колонки, позволяет избежать обращений к таблице:

-- Индекс (user_id, status, created_at) покрывает запрос:
SELECT user_id, status FROM orders 
WHERE user_id = 5 AND status = 'active'
-- Данные есть в индексе, таблица не читается

5. Учет операторов неравенства

Колонки после диапазонного сравнения (>, <, BETWEEN, LIKE с % в начале) не могут использоваться для фильтрации:

-- Индекс (status, created_at, user_id)
-- WHERE status = 'active' AND created_at > '2024-01-01' AND user_id = 5
-- user_id НЕ используется для фильтрации (после диапазона created_at)
-- Лучше: (status, user_id, created_at)

6. Сортировка и группировка

Индекс может устранить операции сортировки, если ORDER BY соответствует порядку индекса:

-- Индекс (category_id, price)
-- Устраняет сортировку для:
SELECT * FROM products 
WHERE category_id = 5 
ORDER BY price -- сортировка по price уже есть в индексе

7. Ограничение длины индекса

• Слишком много колонок увеличивает размер индекса и снижает производительность записи
• Рекомендуется 3-5 колонок максимум для большинства сценариев
• Используйте частичные индексы или индексы по выражению при необходимости

8. Анализ конкретных запросов

Создавайте индексы под конкретные медленные запросы, а не наугад:

-- Анализируем EXPLAIN для запроса:
EXPLAIN ANALYZE 
SELECT * FROM orders 
WHERE user_id = 100 AND status = 'shipped' 
ORDER BY created_at DESC LIMIT 10;

9. Компромиссы при проектировании

Учитывайте баланс:

• Чтение vs запись — каждый индекс замедляет INSERT/UPDATE/DELETE
• Дисковое пространство — составные индексы занимают больше места
• Поддержка разных запросов — иногда нужны несколько индексов с разным порядком колонок

10. Особенности СУБД

Правила могут отличаться для разных систем:

• MySQL/InnoDB: строгий leftmost prefix, ограниченное использование DESC в индексах
• PostgreSQL: более гибкие индексы (INCLUDE, выражения, DESC), bitmap index scan
• SQL Server: included columns, filtered indexes

Практический пример оптимизации

-- Исходная таблица заказов
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    status VARCHAR(20),
    created_at DATETIME,
    amount DECIMAL(10,2),
    category_id INT
);

-- Частые запросы:
-- 1. WHERE user_id = ? AND status = ? ORDER BY created_at DESC
-- 2. WHERE category_id = ? AND created_at > ? 
-- 3. WHERE status = ? AND created_at BETWEEN ? AND ?

-- Оптимальные индексы:
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at DESC);
CREATE INDEX idx_category_created ON orders(category_id, created_at);
CREATE INDEX idx_status_created ON orders(status, created_at);

-- Для покрывающего запроса:
CREATE INDEX idx_covering ON orders(user_id, status) INCLUDE (amount, created_at);

Ключевой вывод: эффективные составные индексы требуют анализа конкретных запросов, понимания принципа левого префикса и правильного порядка колонок. Всегда тестируйте планы выполнения с EXPLAIN и учитывайте специфику вашей СУБД.