Индексы, покрывающие весь запрос без обращения к таблице, index condition pushdown
Покрывающий индекс — это индекс, который содержит все столбцы, необходимые для выполнения запроса. MySQL может ответить на запрос, читая только индекс, без обращения к данным таблицы.
В обычном сценарии поиск по вторичному индексу в InnoDB требует двух шагов:
Покрывающий индекс исключает шаг 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(*) без условий на конкретные столбцы может выполняться по любому вторичному индексу:
-- Индекс на status
CREATE INDEX idx_status ON users(status);
-- COUNT(*) использует idx_status как покрывающий
SELECT COUNT(*) FROM users WHERE status = 'active';
-- MySQL считает записи в индексе, не читая строки таблицыЭто работает, потому что COUNT(*) не требует значений конкретных столбцов — достаточно посчитать количество записей в индексе. Вторичный индекс меньше кластерного, поэтому этот запрос быстрее full table scan.
Не путайте 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 conditionUsing index — MySQL читает ТОЛЬКО индекс. Using index condition — MySQL фильтрует по индексу в storage engine, но затем всё равно обращается к таблице за недостающими столбцами.
Идентифицируйте частые запросы с малым набором столбцов. API-эндпоинты, возвращающие 2-3 поля — идеальные кандидаты для покрывающего индекса.
Мониторьте через EXPLAIN. Если видите Using index — отлично. Если нет и запрос критичен — рассмотрите добавление недостающих столбцов в индекс.
Измеряйте. Добавление столбца в индекс может ускорить SELECT на 20%, но замедлить INSERT на 10%. Решайте на основе профиля нагрузки.
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.