SQL-Ex blog

Включенные столбцы могут использоваться для создания покрывающих индексов без включения всех данных в столбцы ключа. Такой покрывающий индекс имеет то преимущество, что содержит все столбцы, необходимые для запроса. Это подразумевает включение как ключевых, так и неключевых столбцов, поэтому нет необходимости использовать операции lookup (поиск закладки) или доступ к физической таблице. Тем самым сокращается число операций ввода/вывода для извлечения данных.

Создание включенных столбцов возможно только для некластеризованных индексов. Включенными столбцами в некластеризованном индексе могут быть только неключевые столбцы. Хранение значений включенных столбцов возможно только на листовом уровне индекса, в отличие от ключевого столбца индекса. Ключевой столбец индекса сохраняется на всех уровнях.

Неключевые столбцы не имеют тех ограничений на типы данных, которые имеют ключевые столбцы. Не допускаются только унаследованные типы данных, например, TEXT, NTEXT и IMAGE. Даже типы данных больших объектов (LOB) допускаются для неключевых столбцов. Такими типами данных являются varchar(max), nvarchar(max), varbinary(max) и XML.

Проблема использования типов данных LOB заключается в возможном падении производительности. Значения столбцов копируются на листовой уровень некластеризованного индекса. По этой причине индексу может потребоваться большое место на диске. Кроме того, снижается эффективность буферного кэша, поскольку меньшее число строк поместится в память, что может привести к увеличению нагрузки на ввод/вывод. Допускается использовать до 16 индексных ключей в некластеризованном индексе. На число включенных столбцов не накладывается ограничений, однако не рекомендуется использовать больше столбцов, чем это необходимо.

Чтобы принять решение об использовании включенных столбцов в ваших индексах, необходимо знать особенности базы данных. Недостатками использования включенных столбцов является снижение производительности при операциях модификации данных и, как уже отмечалось, уменьшение эффективности буферного кэша и увеличение нагрузки на ввод/вывод в случае использования типов данных LOB. Убедитесь, что недостатки включенных столбцов не перевешивают предполагаемые преимущества в производительности запросов.

В качестве примера давайте проверим таблицу Users из базы данных Stack Overflow. Вы можете ввести ваш запрос здесь:

SELECT Id,

DisplayName,

Location

FROM Users

WHERE Views > 100

AND UpVotes = 3

Теперь мы создаем индекс для этого запроса:

CREATE INDEX idx_Users_ViewsUpVotes

ON Users (Views, UpVotes)

Этот индекс содержит столбцы, которые необходимы для предложения WHERE, но он не покрывает 3-х столбцов в предложении SELECT. Для получения этих трех столбцов SQL Server потребуется выполнить поиск в кластеризованном индексе. Чтобы создать покрывающий индекс, нам необходимо добавить в него эти три столбца. В этом случаен имеет смысл использовать включенные столбцы. Мы не добавляем эти столбцы как ключевые, поскольку это сделает индекс шире, чем необходимо. Кроме того, мы не используем эти столбцы для фильтрации или индексирования, следовательно нам нет необходимости делать их ключевыми.

Оптимальный индекс в этом случае выглядит так:

CREATE INDEX idx_Users_ViewsUpVotes

ON Users (Views, UpVotes)

Include (Id, DisplayName, Location)

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

Заключение

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

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

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