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

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

Можно включать неключевые столбцы в некластеризованный индекс, чтобы избежать превышения текущих ограничений на размер индекса (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 в качестве неключевых индексных столбцов может значительно повысить требования к месту на диске. Это обусловлено тем, что значения столбцов копируются на конечный уровень индекса. Поэтому они находятся и в индексе, и в базовой таблице.

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

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

См. также

Основные понятия