Создание отфильтрованных индексов

В этом разделе описывается создание отфильтрованного индекса в SQL Server 2014 с помощью SQL Server Management Studio или Transact-SQL. Фильтруемый индекс — это оптимизированный некластеризованный индекс, особенно подходящий для запросов, осуществляющих выборку из хорошо определенного подмножества данных. Он использует предикат фильтра для индексирования части строк в таблице. Хорошо спроектированный фильтруемый индекс позволяет повысить производительность запросов, а также снизить затраты на обслуживание и хранение индексов по сравнению с полнотабличными индексами.

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

• Улучшение производительности запроса и качества плана

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

• Снижение расходов на обслуживание индекса

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

• Снижение затрат на хранение индекса

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

В этом разделе

• Перед началом работы

  Вопросы проектирования

  Ограничения

  Безопасность

• Создание фильтруемого индекса с использованием следующих средств:

  Среда SQL Server Management Studio

  Transact-SQL

Перед началом

Вопросы проектирования

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

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

Ограничения

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

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

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

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

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

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

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

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

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

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

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

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

Безопасность

Разрешения

Необходимо разрешение ALTER для таблицы или представления. Пользователь должен быть членом предопределенной роли сервера sysadmin или предопределенных ролей базы данных db_ddladmin и db_owner. Для изменения выражения отфильтрованного индекса используйте инструкцию CREATE INDEX WITH DROP_EXISTING.

Использование среды SQL Server Management Studio

Создание фильтруемого индекса

1. В обозревателе объектов щелкните знак «плюс», чтобы развернуть базу данных, содержащую таблицу, в которой необходимо создать отфильтрованный индекс.

2. Чтобы развернуть папку Таблицы , щелкните значок «плюс».

3. Щелкните знак «плюс», чтобы развернуть таблицу, в которой необходимо создать отфильтрованный индекс.

4. Щелкните правой кнопкой мыши папку Индексы, выберите Создать индекс и Некластеризованный индекс...

5. В диалоговом окне Создание индекса на странице Общие введите имя нового индекса в поле Имя индекса .

6. В разделе Ключевые столбцы индекса щелкните Добавить… .

7. В диалоговом окне Выбор столбцов изtable_name выберите поле проверка или проверка столбцов таблицы, которые нужно добавить в уникальный индекс.

8. Нажмите кнопку ОК.

9. На странице Фильтр в поле Критерий фильтра введите выражение SQL, которое будет использоваться для создания фильтруемого индекса.

10. Нажмите кнопку ОК.

Использование Transact-SQL

Создание фильтруемого индекса

1. В обозревателе объектовподключитесь к экземпляру компонента Компонент Database Engine.

2. На стандартной панели выберите пункт Создать запрос.

3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.

   USE AdventureWorks2012;  
   GO  
   -- Looks for an existing filtered index named "FIBillOfMaterialsWithEndDate"  
   -- and deletes it from the table Production.BillOfMaterials if found.   
   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  
   -- Creates a filtered index "FIBillOfMaterialsWithEndDate"  
   -- on the table Production.BillOfMaterials   
   -- using the columms ComponentID and StartDate.  
   
   CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate  
       ON Production.BillOfMaterials (ComponentID, StartDate)  
       WHERE EndDate IS NOT NULL ;  
   GO  
   

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

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

Обеспечение использования фильтруемого индекса в SQL-запросе

1. В обозревателе объектовподключитесь к экземпляру компонента Компонент Database Engine.

2. На стандартной панели выберите пункт Создать запрос.

3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.

   USE AdventureWorks2012;  
   GO  
   SELECT ComponentID, StartDate FROM Production.BillOfMaterials  
       WITH ( INDEX ( FIBillOfMaterialsWithEndDate ) )   
   WHERE EndDate IN ('20000825', '20000908', '20000918');   
   GO  
   

Дополнительные сведения см. в статье CREATE INDEX (Transact-SQL).