Рекомендации по проектированию отфильтрованных индексов

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

Например, база данных AdventureWorks2008R2 содержит таблицу Production.BillOfMaterials с 2679 строками. Столбец EndDate содержит только 199 строк, содержащих значения, отличные от NULL, и 2480 строк, содержащих значение NULL. Следующий отфильтрованный индекс может перекрыть запросы, которые возвращают столбцы, указанные в индексе, и выбирают только строки, содержащие значения EndDate, отличные от NULL.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate'
    AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL ;
GO

Отфильтрованный индекс FIBillOfMaterialsWithEndDate допустим для следующего запроса. Можно отобразить план выполнения запроса для проверки того, использует ли оптимизатор запросов отфильтрованный индекс. Сведения об отображении плана выполнения запроса см. в разделе Анализ запроса.

SELECT ProductAssemblyID, ComponentID, StartDate 
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL 
    AND ComponentID = 5 
    AND StartDate > '01/01/2008' ;
GO

Дополнительные сведения о создании отфильтрованного индекса и об определении выражения предиката отфильтрованного индекса см. в разделе CREATE INDEX (Transact-SQL).

Если таблица содержит строки с разнородными данными, можно создать отфильтрованный индекс для одной или более категорий данных.

Например, продукты, содержащиеся в таблице Production.Product, связаны с идентификатором ProductSubcategoryID, который, в свою очередь, связан с категориями продуктов, такими как велосипеды, запчасти, одежда или аксессуары. Эти категории являются разнородными, так как значения их столбцов в таблице Production.Product не являются близко коррелирующими. Например, Color, ReorderPoint, ListPrice, Weight, Class и Style имеют уникальные характеристики для каждой категории продукта. Предположим, что существуют частые запросы на аксессуары, имеющие подкатегории с 27 по 36. Можно повысить результативность запросов на аксессуары, создав отфильтрованный индекс по подкатегориям аксессуаров.

В следующем примере создается отфильтрованный индекс по всем продуктам подкатегории Accessories в таблице Production.Product.

USE AdventureWorks2008R2;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIProductAccessories'
    AND object_id = OBJECT_ID ('Production.Product'))
DROP INDEX FIProductAccessories
    ON Production.Product;
GO
CREATE NONCLUSTERED INDEX FIProductAccessories
    ON Production.Product (ProductSubcategoryID, ListPrice) 
        Include (Name)
WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;
GO

Отфильтрованный индекс FIProductAccessories включает следующий запрос, поскольку результаты

запроса содержатся в индексе, а план запроса не включает в себя поиск в базовых таблицах. Например, выражение предиката запроса ProductSubcategoryID = 33 — это подмножество предиката отфильтрованного индекса ProductSubcategoryID >= 27 и ProductSubcategoryID <= 36, а столбцы ProductSubcategoryID и ListPrice в предикате запроса являются ключевыми столбцами в индексе. Имя сохраняется на конечном уровне индекса в качестве включенного столбца.

SELECT Name, ProductSubcategoryID, ListPrice
FROM Production.Product
WHERE ProductSubcategoryID = 33 AND ListPrice > 25.00 ;
GO

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

*Описание простой логики в предикате см. в синтаксисе предложения WHERE в разделе CREATE INDEX.

**Описание сложной логики в предикате см. в синтаксисе предложения WHERE в разделе SELECT.

Невозможно создать отфильтрованный индекс для представления. Однако оптимизатор запросов может извлечь выгоду из отфильтрованного индекса, определенного в таблице, на которую имеется ссылка в представлении. Оптимизатор запросов рассматривает отфильтрованный индекс для запроса, выбирающего данные из представления, если результат запроса будет корректен. В следующем примере создается представление с датой начала 01.04.00 и отфильтрованный индекс с датой начала 01.08.00.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('ViewOnBillOfMaterials') IS NOT NULL
DROP VIEW ViewOnBillOfMaterials;
GO
CREATE VIEW ViewOnBillOfMaterials AS 
SELECT ComponentID, StartDate, EndDate, StartDate + 2 AS ShipDate
FROM Production.BillOfMaterials
WHERE StartDate > '20000401';
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsByStartDate'
    AND object_ID = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsByStartDate 
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsByStartDate
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
WHERE StartDate > '20000801';
GO

В следующем примере запрос выбирает данные с датами начала позже 1 сентября 2004 г., содержащиеся в отфильтрованном индексе и фильтруемом представлении. Оптимизатор запросов рассматривает отфильтрованный индекс FIBillOfMaterialsByStartDate, поскольку он содержит верные результаты запроса.

SELECT StartDate, ComponentID FROM ViewOnBillOfMaterials
WHERE StartDate > '20040901';
GO

В следующем примере запрос выбирает данные с датами начала позже 1 июня 2004 г., которые содержатся в представлении, но не в отфильтрованном индексе. Оптимизатор запросов не рассматривает отфильтрованный индекс FIBillOfMaterialsByStartDate, поскольку запрос может вернуть различные результаты при использовании отфильтрованного индекса в сравнении с правильными результатами, получаемыми, когда запрос выбирает данные из представления.

SELECT StartDate, ComponentID FROM ViewOnBillOfMaterials
WHERE StartDate > '20040601';
GO

Отфильтрованные индексы имеют следующие преимущества по сравнению с индексированными представлениями.

• Уменьшение ресурсозатрат на обслуживание индекса. Например, для обновления отфильтрованного индекса обработчик запросов использует меньшее количество ресурсов ЦП, чем для индексированного представления.

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

• Перестроение индексов в сети. Отфильтрованные индексы можно перестраивать, если они доступны для запросов. Для индексированных представлений перестроение индексов в сети не поддерживается. Дополнительные сведения см. в описании параметра REBUILD для ALTER INDEX (Transact-SQL).

• Неуникальные индексы. Отфильтрованные индексы могут быть неуникальными, тогда как индексированные представления должны быть уникальными.

По указанным выше причинам рекомендуется (по мере возможности) использовать отфильтрованные индексы вместо индексированных представлений. Отфильтрованные индексы можно использовать вместо индексированных представлений, если выполняются следующие условия: представления ссылаются только на одну таблицу, запросы не возвращают вычисляемые столбцы и предикат представления использует простую логику сравнения. Например, следующее выражение предиката разрешено в определении представления, но не в отфильтрованных индексах, поскольку оно содержит оператор LIKE.

WHERE StartDate > '20040701' AND ModifiedDate LIKE 'E%'

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

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

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

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO

Столбец в выражении отфильтрованного индекса должен быть ключевым или включенным столбцом в определении отфильтрованного индекса, если предикат запроса использует в сравнении столбец, который не эквивалентен выражению отфильтрованного индекса. Например, отфильтрованный индекс FIBillOfMaterialsWithEndDate допустим для следующего запроса, поскольку этот запрос выбирает подмножество строк из отфильтрованного индекса. Однако он не перекрывает следующий запрос, поскольку столбец EndDate используется в сравнении EndDate > '20040101', не эквивалентном выражению отфильтрованного индекса. Обработчик запросов не может выполнить запрос без поиска значений EndDate. Поэтому EndDate должен быть ключом или включенным столбцом в определении отфильтрованного индекса.

SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate > '20040101';
GO

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

SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO

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

Если оператор сравнения определен в выражении отфильтрованного индекса результатов отфильтрованного индекса в неявном или явном преобразовании данных, произойдет ошибка, если преобразование выполняется в левой части оператора сравнения. Решением является применение выражения отфильтрованного индекса с оператором преобразования данных (CAST или CONVERT) в правой части оператора сравнения.

В следующем примере создается таблица с различными типами данных.

USE AdventureWorks2008R2;
GO
IF OBJECT_ID ('dbo.TestTable') IS NOT NULL
DROP TABLE dbo.TestTable;
GO
CREATE TABLE TestTable (a int, b varbinary(4));
GO

В следующем определении отфильтрованного индекса столбец b неявно преобразуется в тип данных integer для сравнения с константой 1. Это вызывает сообщение об ошибке 10611, поскольку преобразование выполняется в левой части оператора в отфильтрованном предикате.

USE AdventureWorks2008R2;
GO
IF EXISTS ( SELECT name from sys.indexes 
    WHERE name = N'TestTabIndex'
    AND object_id = OBJECT_ID (N'dbo.TestTable'))
DROP INDEX TestTabIndex on dbo.TestTable
GO
CREATE NONCLUSTERED INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = 1;
GO

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

CREATE INDEX TestTabIndex ON dbo.TestTable(a,b)
WHERE b = CONVERT(Varbinary(4), 1);
GO

Перемещение преобразования данных из левой части оператора сравнения в правую может изменить значение преобразования. В приведенном выше примере при добавлении оператора CONVERT в правую часть преобразование изменяется с преобразования типа integer на преобразование типа varbinary.

Представление каталога sys.sql_expression_dependencies содержит каждый столбец из выражения отфильтрованного индекса в качестве ссылаемой зависимости. Можно удалить, переименовать или изменить определение столбца таблицы, определенного в выражении отфильтрованного индекса.

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

• Разреженные столбцы, содержащие небольшое количество значений, отличных от NULL.

• Разнородные столбцы, содержащие категории данных.

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

• Секции таблицы, определенные логикой простого сравнения для значений столбцов.

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

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

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

• Инструкция ALTER INDEX поддерживает отфильтрованные индексы. Для изменения выражения отфильтрованного индекса используйте инструкцию CREATE INDEX WITH DROP_EXISTING.

• Функция информирования об отсутствующих индексах не предлагает создавать отфильтрованные индексы.

• Помощник по настройке компонента Database Engine принимает во внимание отфильтрованные индексы при рекомендации настройки индексов и может порекомендовать отфильтрованный индекс is not null.

• Операции с индексами в сети поддерживают отфильтрованные индексы.

• Табличные подсказки поддерживают отфильтрованные индексы, но имеют некоторые ограничения, неприменимые к неотфильтрованным индексам. Это описано в следующем разделе.

В некоторых случаях параметризованный запрос не содержит достаточно информации во время компиляции, для того чтобы оптимизатор запросов мог выбрать отфильтрованный индекс. Можно переписать запрос для предоставления отсутствующей информации. В следующем примере оптимизатор запросов не принимает во внимание отфильтрованный индекс FIBillOfMaterialsWithComponentID для инструкции SELECT, поскольку значение параметров @p и @q во время компиляции неизвестно. Ниже приведен пример запроса с параметром SHOWPLAN_XML, установленным в состояние ON, что позволяет просмотреть отфильтрованные индексы, не соответствующие параметризованным запросам, на выходе SHOWPLAN_XML.

USE AdventureWorks2008R2;
GO
IF EXISTS ( SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithComponentID'
    AND object_id = OBJECT_ID (N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithComponentID
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithComponentID
    ON Production.BillOfMaterials (ComponentID, StartDate, EndDate)
WHERE ComponentID IN (533, 324, 753);
GO
SET SHOWPLAN_XML ON;
GO
DECLARE @p AS INT, @q AS INT;
SET @p = 533;
SET @q = 324;
SELECT StartDate, ComponentID from Production.BillOfMaterials 
WHERE ComponentID = @p OR ComponentID = @q;
GO
SET SHOWPLAN_XML OFF;
GO

Элемент UnmatchedIndexes и его вложенный элемент Parameterization на выходе SHOWPLAN_XML указывают, что отфильтрованный индекс не соответствовал запросу. Сведения о том, как просмотреть выходные данные SHOWPLAN_XML, см. в разделе Инструкции Showplan XML.

Решением является изменение запроса таким образом, чтобы результаты запроса были пустыми, если параметризованное выражение не является подмножеством предиката фильтра. Следующий запрос демонстрирует данное изменение. С помощью добавления выражения ComponentID in (533, 324, 753) в предложение WHERE результаты запроса гарантированно будут подмножеством выражения предиката фильтра. После этого изменения оптимизатор запросов может рассматривать отфильтрованный индекс FIBillOfMaterialsWithComponentID для следующей инструкции SELECT.

USE AdventureWorks2008R2;
GO
SET SHOWPLAN_XML ON;
GO
DECLARE @p AS INT, @q AS INT;
SET @p = 533;
SET @q = 324;
SELECT StartDate, ComponentID FROM Production.BillOfMaterials
WHERE ComponentID in (533, 324, 753)
    AND (ComponentID = @p OR ComponentID = @q);
GO
SET SHOWPLAN_XML OFF;
GO

Простая параметризация

Оптимизатор запросов может использовать отфильтрованный индекс, даже если тот не перекрывает запрос, выполняя поиск ключа для получения столбцов, не перекрываемых отфильтрованным индексом. Дополнительные сведения о поиске ключа см. в разделе Оператор Key Lookup инструкции Showplan. Оптимизатор запросов может выбрать этот подход, если количество операторов Key Lookup невелико. Следующий запрос использует подсказку индекса для принудительного использования обработчиком запросов отфильтрованного индекса FIBillOfMaterialsWithEndDate с уточняющим запросом закладок для EndDate. Поиск ключа выполняется при сравнении EndDate > @date в предикате запроса.

USE AdventureWorks2008R2;
GO
DECLARE @date AS DATE;
SET @date = '20040825'
SELECT ComponentID, StartDate, EndDate FROM Production.BillOfMaterials
WITH ( INDEX (FIBillOfMaterialsWithEndDate) )
WHERE EndDate > @date;
GO

Обратите внимание, что точного соответствия между условием EndDate > @Date и выражением отфильтрованного индекса EndDate IS NOT NULL нет. Отфильтрованный индекс по-прежнему допустим для параметризованного запроса, поскольку тот возвращает подмножество строк, определенное выражением отфильтрованного индекса.