Краткий ответ

Покрывающий индекс ускоряет запрос тем, что содержит не только ключ фильтрации, но и все столбцы, нужные для выдачи или дополнительных операций. СУБД может получить результат из индекса, не обращаясь к основной таблице за каждой найденной строкой, поэтому сокращаются операции чтения и случайный доступ.

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

Исторический контекст

Обычный индекс решает прежде всего задачу быстрого поиска подходящих ключей. После этого СУБД часто выполняет дополнительные обращения к таблице, чтобы прочитать столбцы, отсутствующие в индексе.

Покрывающие индексы появились как развитие этой идеи: хранить в структуре индекса необходимые для частых запросов данные и тем самым уменьшать стоимость перехода от найденного ключа к строке таблицы. В разных СУБД это может называться covering index, index-only scan или реализовываться через включаемые столбцы.

Постановка проблемы

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

Если у клиента много заказов или строки таблицы физически разбросаны по страницам, такие обращения становятся дорогими. Ошибочное добавление всех столбцов в ключ индекса тоже опасно: индекс увеличивается, замедляет вставки и обновления и потребляет больше места.

Подробное решение

Покрывающий индекс хранит ключевые столбцы, используемые для поиска, и дополнительные столбцы, которые нужны запросу. Например, в PostgreSQL дополнительные данные можно хранить через INCLUDE:

CREATE INDEX orders_customer_idx
    ON orders (customer_id)
    INCLUDE (status, total);

SELECT status, total
FROM orders
WHERE customer_id = 42;

В этом примере customer_id остаётся ключом поиска, а status и total позволяют сформировать результат без чтения соответствующих строк таблицы. В плане выполнения это может привести к индексному сканированию без обращения к таблице.

Однако «индекс содержит все столбцы» не означает автоматическое отсутствие чтения таблицы. Например, PostgreSQL дополнительно проверяет карту видимости страниц, чтобы убедиться, что данные можно безопасно прочитать только из индекса. Если нужные страницы не отмечены подходящим образом, возможны дополнительные проверки таблицы.

Покрытие определяется конкретным запросом: нужны все столбцы фильтрации, соединений, сортировки и выдачи, которые оптимизатор сможет использовать из индекса. Слишком широкий индекс увеличивает стоимость записи, обновления статистики и хранения; слишком узкий не устраняет обращения к таблице. Поэтому индекс проектируют под устойчивый важный шаблон запросов, а не под единичный пример.

Ситуация из практики

В сервисе список заказов фильтровался по клиенту и возвращал только статус и сумму. План показывал поиск по индексу клиента, после которого выполнялось множество обращений к таблице; при клиентах с большим числом заказов задержка становилась заметной.

Рассматривались три варианта. Увеличить размер кэша было бы полезно лишь при повторном чтении тех же страниц и не устраняло бы стоимость холодного доступа. Переписать запрос не помогало, поскольку проблема находилась не в выражении, а в необходимости читать отсутствующие в индексе столбцы. Добавить статус и сумму в покрывающую часть индекса увеличивало стоимость записей, но непосредственно устраняло основной источник чтений.

Выбрали третий вариант после проверки плана и нагрузки на запись. Для этого запроса число обращений к таблице уменьшилось, но индекс добавили только на нужный рабочий шаблон, не включая туда редко используемые столбцы.

Что кандидаты часто упускают

Вопрос: Всегда ли покрывающий индекс полностью устраняет чтение основной таблицы?

Ответ: Нет. СУБД может обращаться к таблице для проверки видимости строк, чтения столбцов, не вошедших в индекс, или выполнения условий, которые нельзя надежно вычислить по индексным данным. Кроме того, оптимизатор может выбрать другой план, если последовательное чтение таблицы дешевле.

Вопрос: Почему добавление столбца в покрывающую часть обычно предпочтительнее добавления его в ключ индекса?

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

Вопрос: В каком случае покрывающий индекс может ухудшить общую производительность системы?

Ответ: Он увеличивает объём индекса и количество работы при вставках, удалениях и обновлениях включённых столбцов. Дополнительные страницы могут вытеснять из памяти более полезные данные, а обновление индекса повышает нагрузку на журналирование и обслуживание. Поэтому выигрыш чтения нужно сопоставлять с долей запросов, их задержкой и стоимостью записи.