• 项目

使用缺失索引信息编写 CREATE INDEX 语句

本主题包含使用由缺失索引功能组件返回的信息，针对缺失索引编写 CREATE INDEX 语句的指南和示例。

排列 CREATE INDEX 语句中的列

缺失索引功能的组件在其输出中列出相等列、不等列以及包含列。

例如，XML 显示计划 MissingIndexes 元素指示索引键列在 Transact-SQL 语句谓词中是用于相等 (=) 或不等（<、> 等），还是只被包含在其中以包含查询。它将针对 ColumnGroup 子元素的 Usage 属性，将此信息显示为下列值之一：

<ColumnGroup Usage="EQUALITY">

<ColumnGroup Usage="INEQUALITY">

<ColumnGroup Usage="INCLUDE">

动态管理对象 sys.dm_db_missing_index_details 和 sys.dm_db_missing_index_columns 返回指示索引键列是相等列、不等列，还是包含列的结果。sys.dm_db_missing_index_details 的结果集在 equality_columns、inequality_columns 和 included_columns 列中返回此信息。sys.dm_db_missing_index_columns 返回的结果集在其 column_usage 列中返回此信息。

请使用下列指南排列通过缺失索引功能组件输出编写的 CREATE INDEX 语句中的列：

  • 首先列出相等列（在列列表的最左侧）。

  • 在相等列之后列出不等列（位于列出的相等列的右侧）。

  • 在 CREATE INDEX 语句的 INCLUDE 子句中列出包含列。

  • 若要确定相等列的有效顺序，请根据其选择性排序；即，首先列出最具选择性的列。

示例

使用 XML 显示计划 MissingIndexes 元素的输出

缺失索引功能利用查询优化器在优化查询时自动生成的信息。但是，必须首先在 SQL Server 实例上执行查询，以便优化器能够生成此缺失索引信息。

下面的示例显示了如何通过 MissingIndexes 元素返回的信息创建数据定义语言 (DDL) 语句：

  1. 使用 SET STATISTICS XML ON 选项打开 XML 显示计划功能，并对 AdventureWorks 示例数据库执行以下查询：

    USE AdventureWorks;
GO
SET STATISTICS XML ON;
GO
SELECT CustomerID, SalesOrderNumber, SubTotal
FROM Sales.SalesOrderHeader
WHERE ShipMethodID > 2
AND SubTotal > 500.00
AND Freight < 15.00
AND TerritoryID = 5;
GO

  2. 查看生成的显示计划的 MissingIndexes 元素中返回的输出：

    <MissingIndexes>

      <MissingIndexGroup Impact="95.8296">

        <MissingIndex Database="[AdventureWorks]" Schema="[Sales]" Table="[SalesOrderHeader]">

          <ColumnGroup Usage="EQUALITY">

            <Column Name="[TerritoryID]" ColumnId="14" />

          </ColumnGroup>

          <ColumnGroup Usage="INEQUALITY">

            <Column Name="[ShipMethodID]" ColumnId="17" />

            <Column Name="[SubTotal]" ColumnId="21" />

            <Column Name="[Freight]" ColumnId="23" />

          </ColumnGroup>

          <ColumnGroup Usage="INCLUDE">

            <Column Name="[SalesOrderNumber]" ColumnId="8" />

            <Column Name="[CustomerID]" ColumnId="11" />

          </ColumnGroup>

        </MissingIndex>

      </MissingIndexGroup>

    </MissingIndexes>

  3. 使用 MissingIndex 和 ColumnGroup 元素中返回的信息创建缺失索引，以按如下方式编写 CREATE INDEX DDL 语句：

    USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
           WHERE name = N'IX_SalesOrderHeader_TerritoryID')
     DROP INDEX IX_SalesOrderHeader_TerritoryID ON Sales.SalesOrderHeader;
GO
CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_TerritoryID
     ON Sales.SalesOrderHeader (TerritoryID, ShipMethodID, SubTotal, Freight)
     INCLUDE (SalesOrderNumber, CustomerID);
GO

    此 CREATE INDEX 语句使用 MissingIndex 元素中列出的数据库 (USE AdventureWorks)、架构和表名 (ON Sales.SalesOrderHeader)。同时还使用为键列 (TerritoryID, ShipMethodID, SubTotal, Freight) 和非键列 (INCLUDE (SalesOrderNumber, CustomerID)) 的每个 ColumnGroup 子元素列出的列。

使用动态管理对象返回的结果

在检索缺失索引信息之前，必须在 SQL Server 实例上执行查询，以便查询优化器能够生成缺失索引信息。

下面的示例显示了如何通过 sys.dm_db_missing_index_details 动态管理视图返回的信息创建 DDL 语句。

  1. 对 AdventureWorks 示例数据库执行以下查询：

    USE AdventureWorks;
GO
SELECT City, StateProvinceID, PostalCode
FROM Person.Address
WHERE StateProvinceID = 9;
GO

  2. 对 sys.dm_db_missing_index_details 动态管理视图执行以下查询：

    SELECT *
FROM sys.dm_db_missing_index_details

    查询此动态管理视图将返回下列结果：

index_handle

database_id

object_id

equality_columns

inequality_columns

included_columns

statement

1

6

53575229

[StateProvince]

NULL

[City], [PostalCode]

[AdventureWorks].[Person].[Address]

  1. 查询 sys.dm_db_missing_index_details 动态管理视图之后，您可以使用 equality_columns、included_columns 和 statement 列中返回的信息按如下方式创建缺失索引：

    USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
           WHERE name = N'IX_PersonAddress_StateProvinceID')
     DROP INDEX IX_PersonAddress_StateProvinceID ON Person.Address;
GO
CREATE NONCLUSTERED INDEX IX_PersonAddress_StateProvinceID
     ON Person.Address (StateProvinceID)
     INCLUDE (City, PostalCode);
GO

在此示例中，sys.dm_db_missing_index_details 结果集中不返回任何 inequality_columns。如果返回了不等列，则可以在 equality_columns 之后列出这些列。included_columns 中返回的列始终在 CREATE INDEX 语句的 INCLUDE 子句中列出。