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

  • Статья

В этом разделе описывается создание фильтруемого индекса в SQL Server 2012 с помощью Среда 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).

Значок стрелки, используемый со ссылкой «В начало»[В начало]