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

Применимо к: SQL Server (все поддерживаемые версии) Azure SQL database Управляемый экземпляр SQL Azure Azure Synapse Analytics Analytics Platform System (PDW)

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

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

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

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

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

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

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

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

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

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

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

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

ограничения

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

  • В таблице нельзя создать фильтруемый индекс, если столбец в критерии фильтра имеет тип данных CLR.

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

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

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

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

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

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

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

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

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

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

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

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

  • Фильтры невозможно применить к первичному ключу или уникальным ограничениям, но можно применить к индексам со свойством UNIQUE.

Разрешения

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

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

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

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

  3. Чтобы развернуть таблицу, в которой нужно создать отфильтрованный индекс, выберите значок "плюс".

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

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

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

  7. В диалоговом окне "Выбор столбцов" изtable_name установите флажки или флажки столбцов таблицы, добавляемых в индекс.

  8. Щелкните ОК.

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

  10. Щелкните ОК.

Создание отфильтрованного индекса с помощью Transact-SQL

В этом примере используется база данных AdventureWorks2019, которую можно скачать в примерах баз данных AdventureWorks.

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

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

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

USE AdventureWorks2019;  
GO

DROP INDEX IF EXISTS FIBillOfMaterialsWithEndDate  
    ON Production.BillOfMaterials  
GO  

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate  
    ON Production.BillOfMaterials (ComponentID, StartDate)  
    WHERE EndDate IS NOT NULL ;  
GO  

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

USE AdventureWorks2019;  
GO  

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

Дальнейшие действия

Дополнительные сведения о создании индексов и связанных с этим понятиях см. в следующих статьях: