• Статья

Индекс с включенными столбцами

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

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

  • Они не учитываются компонентом Database Engine при расчете числа ключевых столбцов индекса и размера ключа индекса.

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

ПримечаниеПримечание

Если индекс содержит все столбцы, ссылаемые в запросе, это называется покрытием запроса.

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

Использование включенных столбцов для обхода ограничений по размеру

Можно включать неключевые столбцы в некластеризованный индекс, чтобы избежать превышения текущих ограничений на размер индекса (16 ключевых столбцов) и размер ключа индекса (900 байт). Компонент Database Engine не учитывает неключевые столбцы при расчете количества ключевых столбцов индекса и размера ключа индекса.

Например, нужно индексировать следующие столбцы в таблице Document в образце базы данных AdventureWorks2008R2:

     Title nvarchar(50)

     Revision nchar(5)

     FileName nvarchar(400)

Поскольку для типа данных nchar и nvarchar необходимо 2 байта для каждого символа, индекс, содержащий эти три столбца, превысит на 10 байт ограничение на размер в 900 байт (455 * 2). Использование предложения INCLUDE в инструкции CREATE INDEX позволит определить ключ индекса как (Title, Revision), а FileName определить как неключевой столбец. Таким образом, размер ключа индекса составит 110 байт (55 * 2), при этом индекс будет по-прежнему содержать все нужные столбцы. Следующая инструкция создает такой индекс:

USE AdventureWorks2008R2;
GO
CREATE INDEX IX_Document_Title 
ON Production.Document (Title, Revision) 
INCLUDE (FileName);

Правила для индекса с включенными столбцами

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

  • Неключевые столбцы определяются предложением INCLUDE инструкции CREATE INDEX.

  • Неключевые столбцы можно определять только для некластеризованных индексов по таблицам или индексированным представлениям.

  • Допускаются данные всех типов, за исключением text, ntext и image.

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

  • Как и ключевые столбцы, вычисляемые столбцы, полученные на основе типов данных image, ntext и text, могут быть неключевыми (включенными) столбцами до тех пор, пока тип данных этого вычисляемого столбца допустим в качестве неключевого индексного столбца.

  • Имена столбцов нельзя указывать ни в списке INCLUDE, ни в списке ключевых столбцов.

  • Имена столбцов в списке INCLUDE нельзя повторять.

Требования к размеру столбцов

  • Должен быть определен как минимум один ключевой столбец. Максимальное количество неключевых столбцов равно 1023. Это на 1 меньше, чем максимальное количество столбцов таблицы.

  • Ключевые столбцы индекса, в отличие от неключевых, должны удовлетворять текущим ограничениям на максимальное количество столбцов (16) и общий размер ключа индекса (900 байт).

  • Общий размер всех неключевых столбцов ограничен только размером столбцов, указанных в предложении INCLUDE, например столбцы varchar(max) могут иметь размер до 2 ГБ.

Правила изменения столбца

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

  • Неключевые столбцы нельзя удалять из таблицы до удаления соответствующего индекса.

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

    • изменение поведения столбца в отношении значения NULL с NOT NULL на NULL;

    • увеличение длины столбцов типов varchar, nvarchar и varbinary.

      ПримечаниеПримечание

      Эти ограничения на изменение столбца также применяются к ключевым столбцам индекса.

Рекомендации по проектированию

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

Например, нужно спроектировать индекс, покрывающий следующий запрос:

USE AdventureWorks2008R2;
GO
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';

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

Следующая инструкция создает индекс с включенными столбцами, покрывающий данный запрос.

USE AdventureWorks2008R2;
GO
CREATE INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);

Вопросы производительности

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

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

  • Для хранения индекса потребуется больше места на диске. В частности, добавление типов данных varchar(max), nvarchar(max), varbinary(max) и xml в качестве неключевых индексных столбцов может значительно повысить требования к месту на диске. Это обусловлено тем, что значения столбцов копируются на конечный уровень индекса. Поэтому они находятся и в индексе, и в базовой таблице.

  • Обслуживание индекса может увеличить время выполнения операций изменения, вставки, обновления и удаления в базовой таблице или индексированном представлении.

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