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

  • Статья

Применяется к:SQL ServerAzure SQL DatabaseAzure, управляемому экземпляру SQL Azure

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

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

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

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

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

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

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

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

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

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

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

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

ограничения

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

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

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

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

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

    • Перестроение индексов в сети. Отфильтрованные индексы можно перестраивать, если они доступны для запросов. Перестроения индексов в Сети не поддерживаются для индексированных представлений. Дополнительные сведения см. в параметре REBUILDALTER 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. В диалоговом окне Выбор столбцов изимя_таблицы установите флажки для столбцов таблицы, добавляемых к индексу.

  8. Нажмите ОК.

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

  10. Нажмите ОК.

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

В этой статье требуется AdventureWorks2022 пример базы данных, которую можно скачать на домашней странице примеров и проектов сообщества Microsoft SQL Server.

  1. В обозревателе объектов подключитесь к экземпляру ядра СУБД.

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

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

USE AdventureWorks2022;
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 AdventureWorks2022;
GO

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

Далее

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