Индекс с включенными столбцами
В SQL Server 2005 можно расширить функциональность некластеризованных индексов с помощью добавления неключевых столбцов к конечному уровню некластеризованного индекса. Добавление неключевых столбцов позволяет создавать некластеризованные индексы, покрывающие больше запросов. Это обусловлено следующими преимуществами неключевых столбцов.
- Они могут содержать типы данных, не разрешенные для ключевых столбцов индекса.
- Они не учитываются компонентом Database Engine при расчете числа ключевых столбцов индекса и размера ключа индекса.
Индекс с включенными неключевыми столбцами может значительно повысить производительность запроса, когда все столбцы запроса включены в индекс как ключевые или неключевые. Производительность повышается благодаря тому, что оптимизатор запросов может найти все значения столбцов в этом индексе; при этом нет обращения к данным таблиц или кластеризованных индексов, что приводит к меньшему количеству дисковых операций ввода-вывода.
Примечание. |
---|
Если индекс содержит все столбцы, ссылаемые в запросе, это называется покрытием запроса. |
В то время как ключевые столбцы сохраняются на всех уровнях индекса, неключевые столбцы сохраняются только на конечном уровне. Дополнительные сведения об уровнях индекса см. в разделе Организация таблиц и индексов.
Использование включенных столбцов для обхода ограничений по размеру
Можно включать неключевые столбцы в некластеризованный индекс, чтобы избежать превышения текущих ограничений на размер индекса (16 ключевых столбцов) и размер ключа индекса (900 байт). Компонент Database Engine не учитывает неключевые столбцы при расчете количества ключевых столбцов индекса и размера ключа индекса.
Например, нужно индексировать следующие столбцы в таблице Document
в образце базы данных AdventureWorks
:
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 AdventureWorks;
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 AdventureWorks;
GO
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';
Для покрытия запроса необходимо включить в индекс все его столбцы. Хотя можно определить все столбцы как ключевые, размер ключа составит 334 байт. Поскольку в качестве критерия поиска реально используется только столбец PostalCode
, имеющий длину 30 байт, более эффективный индекс определит PostalCode
в качестве ключевого столбца, а все остальные столбцы включит как неключевые.
Следующая инструкция создает индекс с включенными столбцами, покрывающий данный запрос.
USE AdventureWorks;
GO
CREATE INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
Вопросы производительности
Избегайте добавления неиспользуемых столбцов. Добавление слишком большого количества столбцов, ключевых или неключевых, может оказать следующее влияние на производительность.
- На странице будет помещаться меньше строк индекса. Это может привести к увеличению количества операций ввода-вывода и снизить эффективность кэша.
- Для хранения индекса потребуется больше места на диске. В частности, добавление типов данных varchar(max), nvarchar(max), varbinary(max) и xml в качестве неключевых индексных столбцов может значительно повысить требования к месту на диске. Это обусловлено тем, что значения столбцов копируются на конечный уровень индекса. Поэтому они находятся и в индексе, и в базовой таблице.
- Обслуживание индекса может увеличить время выполнения операций изменения, вставки, обновления и удаления в исходной таблице или индексированном представлении.
Необходимо определить, что важнее — повышение производительности запросов или производительность при изменении данных и дополнительные требования к месту на диске. Дополнительные сведения об оценке производительности при выполнении запросов см. в разделе Настройка запроса.
См. также
Основные понятия
Создание индексов (компонент Database Engine)
Создание индексов с включенными столбцами
Общие рекомендации по проектированию индексов
Основы проектирования индексов
Максимальный размер ключей индекса
Просмотр сведений об индексах