Покрывающие индексы (covering index) и include-столбцы

Введение

Обычный индекс ускоряет поиск нужных строк, но после нахождения строки СУБД обычно всё равно обращается к самой таблице за дополнительными данными, не входящими в индекс (так называемый «поиск по закладке», bookmark lookup) — покрывающий индекс устраняет этот дополнительный шаг, если индекс содержит абсолютно все столбцы, нужные для конкретного запроса, позволяя получить весь результат, читая исключительно сам индекс.

Концепция

Покрывающий индекс — индекс, который включает (либо как часть ключа индекса, статья 414, либо как дополнительные, не индексированные «включённые» (include) столбцы, поддерживаемые некоторыми СУБД) все столбцы, нужные конкретному запросу — и в условии WHERE/JOIN, и в самом SELECT — что позволяет СУБД полностью обойтись данными из индекса, не обращаясь к основной таблице вовсе. Это даёт существенный выигрыш в производительности для запросов, читающих относительно немного столбцов из таблиц с большим количеством столбцов, особенно при высокой селективности условия фильтрации.

Пример кода: Покрывающие индексы (covering

-- Обычный индекс — после нахождения строки по customer_id СУБД обращается к таблице
-- за дополнительными столбцами (order_date, total_amount), не входящими в индекс
CREATE INDEX idx_orders_customer ON orders(customer_id);

SELECT order_date, total_amount FROM orders WHERE customer_id = 5;
-- ^ выполняется в два шага: поиск по индексу, затем обращение к таблице за остальными данными

-- Покрывающий индекс — включение нужных столбцов прямо в составной индекс (статья 414)
CREATE INDEX idx_orders_covering ON orders(customer_id, order_date, total_amount);

SELECT order_date, total_amount FROM orders WHERE customer_id = 5;
-- ^ теперь может выполниться ИСКЛЮЧИТЕЛЬНО через индекс, без обращения к самой таблице

-- INCLUDE-столбцы (PostgreSQL 11+) — отдельный способ добиться покрывающего индекса
-- без расширения самого ключа индекса (что иногда выгоднее для производительности записи)
CREATE INDEX idx_orders_covering_include ON orders(customer_id) INCLUDE (order_date, total_amount);
-- customer_id - часть ключа индекса (используется для поиска/сортировки),
-- order_date и total_amount - просто "довеском" хранятся рядом, для покрытия запроса,
-- но не участвуют в сортировке/поиске самого индекса

Пояснения к коду

Сравнение обычного и покрывающего индекса для одного и того же запроса показывает разницу в количестве физических обращений к данным — обычный индекс требует «прыжка» от найденной позиции в индексе к соответствующей строке основной таблицы за дополнительными данными (что для каждой отдельной строки результата — дополнительная операция чтения, потенциально с диска, а не из памяти), тогда как покрывающий индекс предоставляет абсолютно все нужные данные непосредственно из самого индекса, минуя этот дополнительный шаг. Синтаксис INCLUDE (order_date, total_amount) показывает более тонкий механизм — эти столбцы физически хранятся в индексе (что и даёт эффект покрытия запроса), но не участвуют в самой структуре B-tree как часть ключа сортировки/поиска, что может быть эффективнее по объёму и производительности обновления индекса по сравнению с включением тех же столбцов прямо в ключ составного индекса, если порядок/поиск по ним отдельно не нужен.

Подводные камни

• Чрезмерное расширение индекса дополнительными столбцами «на всякий случай» для покрытия всех возможных будущих запросов — каждый дополнительный столбец в индексе (будь то часть ключа или INCLUDE) увеличивает физический размер индекса и стоимость его обновления при каждой модификации данных; покрывающие индексы стоит создавать целенаправленно, для конкретных, реально часто выполняемых и критичных по производительности запросов, а не превентивно расширять каждый индекс всеми мыслимыми столбцами.
• Забывание, что покрывающий индекс должен покрывать ВСЕ столбцы, использованные в запросе (включая столбцы условия WHERE, не только SELECT), для получения полного эффекта избежания обращения к основной таблице — частичное покрытие (когда индекс содержит лишь часть необходимых столбцов) всё равно потребует обращения к таблице за оставшимися, не покрытыми столбцами, что лишь частично снижает количество таких обращений, а не устраняет их полностью.
• Игнорирование того, что добавление столбца в таблицу через ALTER TABLE не автоматически «переделывает» существующие покрывающие индексы, чтобы включить новый столбец, даже если новый столбец логически связан с уже покрытыми запросами — индексы должны явно поддерживаться и обновляться (через DROP/CREATE или эквивалентный механизм изменения) при значимых изменениях схемы или паттернов запросов, а не предполагается, что они автоматически адаптируются к изменениям схемы.
• Создание покрывающего индекса для запроса, выполняющегося редко, при значительном объёме данных, изменяемых часто (что означает частое обновление этого широкого, дорогого по поддержанию индекса при каждой модификации данных) — стоимость поддержания покрывающего индекса должна быть оправдана реальной частотой и критичностью выполнения запроса, который он оптимизирует, а не создаваться без учёта этого практического компромисса между ускорением чтения и замедлением записи.