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

Покрывающий индекс — это индекс, который содержит все столбцы, необходимые для выполнения запроса. MySQL может ответить на запрос, читая только индекс, без обращения к данным таблицы.

#Как это работает

В обычном сценарии поиск по вторичному индексу в InnoDB требует двух шагов:

Поиск по вторичному индексу → находим значение PK
Lookup по кластерному индексу (PK) → получаем остальные столбцы строки

Покрывающий индекс исключает шаг 2:

-- Индекс содержит все столбцы запроса
CREATE INDEX idx_covering ON orders(user_id, status, created_at);

-- Запрос использует ТОЛЬКО столбцы из индекса
SELECT user_id, status FROM orders WHERE user_id = 42;
-- EXPLAIN: Extra = 'Using index' — покрывающий индекс!

В выводе EXPLAIN покрывающий индекс отмечается как Using index в столбце Extra. Это не путать с Using index condition (ICP) — это другое.

#Почему это быстрее

Покрывающий индекс быстрее по трём причинам:

1. Меньше I/O. Индекс обычно значительно меньше таблицы. Чтение 100 МБ индекса быстрее, чем 10 ГБ таблицы.

2. Больше данных в buffer pool. Маленький индекс лучше помещается в оперативную память, снижая дисковые чтения.

3. Нет случайного I/O. Lookup по кластерному индексу — это случайный доступ к данным. Покрывающий индекс читает последовательно из B-Tree индекса.

#Какие столбцы включаются в покрывающий индекс

Покрывающий индекс должен содержать все столбцы, используемые в запросе:

-- Запрос
SELECT email, phone FROM users
WHERE status = 'active' AND department = 'engineering'
ORDER BY email;

-- Покрывающий индекс
CREATE INDEX idx_cover ON users(status, department, email, phone);
-- status, department — для WHERE
-- email — для WHERE и ORDER BY
-- phone — для SELECT

Если запрос делает SELECT *, покрывающий индекс невозможен (нужны ВСЕ столбцы таблицы). Если запрос включает JOIN — нужны столбцы из всех участвующих таблиц.

#Компромиссы: почему не делать все индексы покрывающими

Широкий покрывающий индекс создаёт серьёзные проблемы:

-- Плохо: слишком широкий индекс
CREATE INDEX idx_wide ON orders(
    customer_id, status, created_at, updated_at,
    total_amount, shipping_address, notes, tracking_code
);
-- Индекс может быть почти таким же большим, как сама таблица

Проблемы широких индексов:

Замедление записи. При каждом INSERT/UPDATE/DELETE нужно обновлять ВСЕ индексы таблицы. Широкий индекс = больше данных для записи = медленнее модификация.
Расход места. Большой индекс занимает место в buffer pool, вытесняя другие полезные данные.
Увеличение блокировок. Модификация широкого индекса требует больше времени и удерживает latch'и дольше.

Правило: включайте в покрывающий индекс только те столбцы, которые реально нужны запросу. Если запрос читает SELECT email, не добавляйте phone «на всякий случай».

#COUNT(*) и покрывающий индекс

COUNT(*) без условий на конкретные столбцы может выполняться по любому вторичному индексу:

-- Индекс на status
CREATE INDEX idx_status ON users(status);

-- COUNT(*) использует idx_status как покрывающий
SELECT COUNT(*) FROM users WHERE status = 'active';
-- MySQL считает записи в индексе, не читая строки таблицы

Это работает, потому что COUNT(*) не требует значений конкретных столбцов — достаточно посчитать количество записей в индексе. Вторичный индекс меньше кластерного, поэтому этот запрос быстрее full table scan.

#Index Condition Pushdown vs Covering Index

Не путайте Using index (покрывающий) и Using index condition (ICP):

-- Покрывающий индекс: Using index
-- Все столбцы запроса есть в индексе
SELECT user_id FROM orders WHERE user_id = 42;
-- Extra: Using index

-- ICP: Using index condition
-- Не все столбцы в индексе, но фильтр частично применяется в storage engine
SELECT user_id, total FROM orders
WHERE user_id = 42 AND status = 'pending';
-- Индекс (user_id, status); столбец total НЕ в индексе
-- Extra: Using index condition

Using index — MySQL читает ТОЛЬКО индекс. Using index condition — MySQL фильтрует по индексу в storage engine, но затем всё равно обращается к таблице за недостающими столбцами.

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

Идентифицируйте частые запросы с малым набором столбцов. API-эндпоинты, возвращающие 2-3 поля — идеальные кандидаты для покрывающего индекса.

Мониторьте через EXPLAIN. Если видите Using index — отлично. Если нет и запрос критичен — рассмотрите добавление недостающих столбцов в индекс.

Измеряйте. Добавление столбца в индекс может ускорить SELECT на 20%, но замедлить INSERT на 10%. Решайте на основе профиля нагрузки.