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

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL AzureнетХранилище данных SQL AzureнетParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

В этом разделе описывается создание фильтруемого индекса в SQL Server 2017SQL Server 2017 с помощью Среда SQL Server Management StudioSQL Server Management Studio или Transact-SQLTransact-SQL.This topic describes how to create a filtered index in SQL Server 2017SQL Server 2017 by using Среда SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. Фильтруемый индекс — это оптимизированный некластеризованный индекс, особенно подходящий для запросов, осуществляющих выборку из хорошо определенного подмножества данных.A filtered index is an optimized nonclustered index especially suited to cover queries that select from a well-defined subset of data. Он использует предикат фильтра для индексирования части строк в таблице.It uses a filter predicate to index a portion of rows in the table. Хорошо спроектированный фильтруемый индекс позволяет повысить производительность запросов, а также снизить затраты на обслуживание и хранение индексов по сравнению с полнотабличными индексами.A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes.

Отфильтрованные индексы могут предоставить следующие преимущества по сравнению с индексами, построенными на всей таблице.Filtered indexes can provide the following advantages over full-table indexes:

  • Улучшение производительности запроса и качества планаImproved query performance and plan quality

    Хорошо спроектированный отфильтрованный индекс повышает производительность запроса и качество плана выполнения, поскольку он меньше, чем полнотабличный некластеризованный индекс, и содержит отфильтрованную статистику.A well-designed filtered index improves query performance and execution plan quality because it is smaller than a full-table nonclustered index and has filtered statistics. Отфильтрованная статистика точнее полнотабличной статистики, так как содержит только строки отфильтрованного индекса.The filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index.

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

    Индекс обслуживается только в случае, если инструкции языка обработки данных (DML) затрагивают данные в индексе.An index is maintained only when data manipulation language (DML) statements affect the data in the index. Фильтруемый индекс уменьшает затраты на обслуживание индекса по сравнению с полнотабличным некластеризованным индексом, поскольку он меньше и обслуживается только при изменении данных в индексе.A filtered index reduces index maintenance costs compared with a full-table nonclustered index because it is smaller and is only maintained when the data in the index is changed. Возможно наличие большого числа фильтруемых индексов, особенно если они содержат редко изменяющиеся данные.It is possible to have a large number of filtered indexes, especially when they contain data that is changed infrequently. Аналогично, если фильтруемый индекс содержит только часто изменяемые данные, меньший размер индекса уменьшает затраты на обновление статистики.Similarly, if a filtered index contains only the frequently modified data, the smaller size of the index reduces the cost of updating the statistics.

  • Снижение затрат на хранение индексаReduced index storage costs

    Создание отфильтрованного индекса может уменьшить место на диске для некластеризованных индексов, если нет необходимости в полнотабличном индексе.Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary. Полнотабличный некластеризованный индекс можно заменить несколькими отфильтрованными индексами без значительного увеличения требований к хранилищу.You can replace a full-table nonclustered index with multiple filtered indexes without significantly increasing the storage requirements.

    В этом разделеIn This Topic

  • Перед началом работыBefore you begin:

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

    ОграниченияLimitations and Restrictions

    безопасностьSecurity

  • Создание фильтруемого индекса с использованием следующих средств:To create a filtered index, using:

    Среда SQL Server Management StudioSQL Server Management Studio

    Transact-SQLTransact-SQL

Перед началомBefore You Begin

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

  • Если столбец содержит только небольшое число удовлетворяющих запросу значений, можно создать отфильтрованный индекс на этом подмножестве значений.When a column only has a small number of relevant values for queries, you can create a filtered index on the subset of values. Например, если столбец содержит в основном значения NULL, а запрос выбирает только из значений, отличных от NULL, можно создать отфильтрованный индекс для строк данных, отличных от NULL.For example, when the values in a column are mostly NULL and the query selects only from the non-NULL values, you can create a filtered index for the non-NULL data rows. В результате индекс уменьшится и затраты на его обслуживание будут значительно меньше, чем для полнотабличного некластеризованного индекса на тех же ключевых столбцах.The resulting index will be smaller and cost less to maintain than a full-table nonclustered index defined on the same key columns.

  • Если таблица содержит строки с разнородными данными, можно создать отфильтрованный индекс для одной или более категорий данных.When a table has heterogeneous data rows, you can create a filtered index for one or more categories of data. Это может позволить повысить производительность запросов этих строк данных с помощью ограничения области запроса до определенной области таблицы.This can improve the performance of queries on these data rows by narrowing the focus of a query to a specific area of the table. В результате индекс уменьшится и затраты на его обслуживание будут значительно меньше, чем для полнотабличного некластеризованного индекса.Again, the resulting index will be smaller and cost less to maintain than a full-table nonclustered index.

ОграниченияLimitations and Restrictions

  • Невозможно создать отфильтрованный индекс для представления.You cannot create a filtered index on a view. Однако оптимизатор запросов может извлечь выгоду из отфильтрованного индекса, определенного в таблице, на которую имеется ссылка в представлении.However, the query optimizer can benefit from a filtered index defined on a table that is referenced in a view. Оптимизатор запросов рассматривает отфильтрованный индекс для запроса, выбирающего данные из представления, если результат запроса будет корректен.The query optimizer considers a filtered index for a query that selects from a view if the query results will be correct.

  • В таблице нельзя создать фильтруемый индекс, если столбец в критерии фильтра имеет тип данных CLR.You cannot create a filtered index on a table when the column accessed in the filter expression is of a CLR data type.

  • Отфильтрованные индексы имеют следующие преимущества по сравнению с индексированными представлениями.Filtered indexes have the following advantages over indexed views:

    • Снижение расходов на обслуживание индексаReduced index maintenance costs. Например, для обновления отфильтрованного индекса обработчик запросов использует меньшее количество ресурсов ЦП, чем для индексированного представления.For example, the query processor uses fewer CPU resources to update a filtered index than an indexed view.

    • Повышение качества планов.Improved plan quality. Например, во время компиляции запроса оптимизатор запросов рассматривает использование отфильтрованного индекса в большем количестве ситуаций, чем для эквивалентного индексированного представления.For example, during query compilation, the query optimizer considers using a filtered index in more situations than the equivalent indexed view.

    • Перестроение индексов в сети.Online index rebuilds. Отфильтрованные индексы можно перестраивать, если они доступны для запросов.You can rebuild filtered indexes while they are available for queries. Для индексированных представлений перестроение индексов в сети не поддерживается.Online index rebuilds are not supported for indexed views. Дополнительные сведения см. в описании параметра REBUILD для ALTER INDEX (Transact-SQL).For more information, see the REBUILD option for ALTER INDEX (Transact-SQL).

    • Неуникальные индексы.Non-unique indexes. Отфильтрованные индексы могут быть неуникальными, тогда как индексированные представления должны быть уникальными.Filtered indexes can be non-unique, whereas indexed views must be unique.

  • Отфильтрованные индексы определены в одной таблице и поддерживают только простые операторы сравнения.Filtered indexes are defined on one table and only support simple comparison operators. Если необходим критерий фильтра, который ссылается на множество таблиц или имеет сложную логику, нужно создать представление.If you need a filter expression that references multiple tables or has complex logic, you should create a view.

  • Столбец в выражении отфильтрованного индекса не обязательно должен быть ключевым или включенным столбцом в определении отфильтрованного индекса, если выражение отфильтрованного индекса эквивалентно предикату запроса, а запрос не возвращает столбец с результатами запроса в выражение отфильтрованного индекса.A column in the filtered index expression does not need to be a key or included column in the filtered index definition if the filtered index expression is equivalent to the query predicate and the query does not return the column in the filtered index expression with the query results.

  • Столбец в выражении отфильтрованного индекса должен быть ключевым или включенным столбцом в определении отфильтрованного индекса, если предикат запроса использует в сравнении столбец, который не эквивалентен выражению отфильтрованного индекса.A column in the filtered index expression should be a key or included column in the filtered index definition if the query predicate uses the column in a comparison that is not equivalent to the filtered index expression.

  • Столбец в выражении отфильтрованного индекса должен быть ключевым или включенным столбцом в определении отфильтрованного индекса, если этот столбец содержится в результирующем наборе запроса.A column in the filtered index expression should be a key or included column in the filtered index definition if the column is in the query result set.

  • Ключ кластеризованного индекса таблицы необязательно должен быть ключевым или включенным столбцом в определении отфильтрованного индекса.The clustered index key of the table does not need to be a key or included column in the filtered index definition. Ключ кластеризованного индекса автоматически включается во все некластеризованные индексы, в том числе отфильтрованные индексы.The clustered index key is automatically included in all nonclustered indexes, including filtered indexes.

  • Если оператор сравнения определен в выражении отфильтрованного индекса результатов отфильтрованного индекса в неявном или явном преобразовании данных, произойдет ошибка, если преобразование выполняется в левой части оператора сравнения.If the comparison operator specified in the filtered index expression of the filtered index results in an implicit or explicit data conversion, an error will occur if the conversion occurs on the left side of a comparison operator. Решением является применение выражения отфильтрованного индекса с оператором преобразования данных (CAST или CONVERT) в правой части оператора сравнения.A solution is to write the filtered index expression with the data conversion operator (CAST or CONVERT) on the right side of the comparison operator.

  • Просмотрите обязательные параметры SET для отфильтрованных индексов в синтаксисе CREATE INDEX (Transact-SQL).Review the required SET options for filtered index creation in CREATE INDEX (Transact-SQL) syntax

безопасностьSecurity

PermissionsPermissions

Необходимо разрешение ALTER для таблицы или представления.Requires ALTER permission on the table or view. Пользователь должен быть членом предопределенной роли сервера sysadmin или предопределенных ролей базы данных db_ddladmin и db_owner.User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles. Для изменения выражения отфильтрованного индекса используйте инструкцию CREATE INDEX WITH DROP_EXISTING.To modify the filtered index expression, use CREATE INDEX WITH DROP_EXISTING.

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

Создание фильтруемого индексаTo create a filtered index

  1. В обозревателе объектов щелкните знак «плюс», чтобы развернуть базу данных, содержащую таблицу, в которой необходимо создать отфильтрованный индекс.In Object Explorer, click the plus sign to expand the database that contains the table on which you want to create a filtered index.

  2. Чтобы развернуть папку Таблицы , щелкните значок «плюс».Click the plus sign to expand the Tables folder.

  3. Щелкните знак «плюс», чтобы развернуть таблицу, в которой необходимо создать отфильтрованный индекс.Click the plus sign to expand the table on which you want to create a filtered index.

  4. Щелкните правой кнопкой мыши папку Индексы , выберите Создать индекси Некластеризованный индекс…Right-click the Indexes folder, point to New Index, and select Non-Clustered Index….

  5. В диалоговом окне Создание индекса на странице Общие введите имя нового индекса в поле Имя индекса .In the New Index dialog box, on the General page, enter the name of the new index in the Index name box.

  6. В разделе Ключевые столбцы индексащелкните Добавить…Under Index key columns, click Add….

  7. В диалоговом окне Выбор столбцов изимя_таблицы установите флажки для столбцов таблицы, добавляемых к уникальному индексу.In the Select Columns fromtable_name dialog box, select the check box or check boxes of the table column or columns to be added to the unique index.

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

  9. На странице Фильтр в поле Критерий фильтравведите выражение SQL, которое будет использоваться для создания фильтруемого индекса.On the Filter page, under Filter Expression, enter SQL expression that you’ll use to create the filtered index.

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

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

Создание фильтруемого индексаTo create a filtered index

  1. В обозревателе объектов подключитесь к экземпляру компонента Компонент Database EngineDatabase Engine.In Object Explorer, connect to an instance of Компонент Database EngineDatabase Engine.

  2. На стандартной панели выберите пункт Создать запрос.On the Standard bar, click New Query.

  3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.Copy and paste the following example into the query window and click Execute.

    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  
    

    Фильтруемый индекс выше является действительным для следующего запроса.The filtered index above is valid for the following query. Можно отобразить план выполнения запроса для проверки того, использует ли оптимизатор запросов отфильтрованный индекс.You can display the query execution plan to determine if the query optimizer used the filtered index.

    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-запросеTo ensure that a filtered index is used in a SQL query

  1. В обозревателе объектов подключитесь к экземпляру компонента Компонент Database EngineDatabase Engine.In Object Explorer, connect to an instance of Компонент Database EngineDatabase Engine.

  2. На стандартной панели выберите пункт Создать запрос.On the Standard bar, click New Query.

  3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.Copy and paste the following example into the query window and click Execute.

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

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