Покрывающие индексы SQL Server: ключ, INCLUDE и lookup

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

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

Расположить ключи по способу доступа

В примере клиент ограничен равенством, дата задает диапазон, а результат нужен от новых продаж к старым. Поэтому ключ начинается с CustomerId, продолжается OrderedAt и SaleId для однозначного порядка при совпадении дат. Total и Status нужны только для выдачи и помещены в INCLUDE.

CREATE TABLE #Sales
(
    SaleId bigint NOT NULL PRIMARY KEY,
    CustomerId int NOT NULL,
    OrderedAt datetime2(0) NOT NULL,
    Total decimal(12,2) NOT NULL,
    Status tinyint NOT NULL
);
INSERT #Sales VALUES
(1,7,'2021-10-01',90,1),(2,7,'2021-10-02',40,0),
(3,8,'2021-10-02',20,1),(4,7,'2021-10-03',70,1);

CREATE INDEX IX_Sales_Customer_Date
ON #Sales(CustomerId, OrderedAt DESC, SaleId DESC)
INCLUDE(Total, Status);

DECLARE @CustomerId int=7;
DECLARE @From datetime2(0)='2021-10-01';
SELECT TOP (2) SaleId, OrderedAt, Total, Status
FROM #Sales
WHERE CustomerId=@CustomerId AND OrderedAt>=@From
ORDER BY OrderedAt DESC, SaleId DESC;

SET STATISTICS IO, TIME ON;
SELECT SaleId, OrderedAt, Total, Status
FROM #Sales
WHERE CustomerId=@CustomerId AND OrderedAt>=@From;
SET STATISTICS IO, TIME OFF;
DROP TABLE #Sales;

Для клиента 7 первая выборка возвращает продажи 4 и 2. Индекс поддерживает и ограничение клиента, и порядок. Если первой поставить дату, сервер сможет пройти временной диапазон, но, возможно, прочитает множество чужих клиентов. Поэтому совет всегда начинать с глобально наиболее избирательного столбца недостаточен.

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

Использовать INCLUDE для доказанной проблемы

Включенные столбцы хранятся на листовом уровне некластерного индекса и не задают порядок поиска. Они позволяют убрать lookup, но делают страницы шире, занимают буферный пул, увеличивают объем хранения и резервных копий. Изменение их значений также требует обслуживания. Часто обновляемый Status способен заметно повысить стоимость записи.

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

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

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

Измерить весь рабочий процесс

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

Включите вставки, корректировки сумм, изменения статуса и удаление старых записей. Если индекс устраняет сортировку, сравните выделения памяти и сбросы в tempdb. Если большой запрос продолжает выбирать scan, это может быть разумно для объема результата. Принудительный seek с сотнями тысяч lookup способен ухудшить ситуацию.

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

Техническая документация: Microsoft Learn: Included columns · Microsoft Learn: Index design.