SQL语句实现查询并自动创建Missing Index

什么是Missing Index？

Missing Index的意思是数据库在处理查询语句时，发现某些列没有索引，或者已有的索引不够好，会提示数据库管理员建议创建新的Missing Index来优化查询效率。Missing Index可以让数据库更快地定位到所需要的数据，提高查询效率。

SQL语句实现查询Missing Index

在SQL Server中，我们可以使用如下的查询语句来查找Missing Index：

SELECT TOP 25

    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
    'CREATE INDEX [missing_index_' +
    CONVERT (varchar, mig.index_group_handle) + '_' +
    CONVERT (varchar, mid.index_handle) + '_' +
    LEFT (PARSENAME(mid.statement, 1), 32) + '
    ON ' + mid.statement + '
    (' + ISNULL (mid.equality_columns,'')
        + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
        + ISNULL (mid.inequality_columns, '')
    + ')'
    + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
    migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC;

上面这段代码可以查询出当前数据库中的Missing Index，并给出了创建Missing Index的语句。

代码解析

以最重要的“SELECT”语句为例进行分析：

SELECT TOP 25

    migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
    'CREATE INDEX [missing_index_' +
    CONVERT (varchar, mig.index_group_handle) + '_' +
    CONVERT (varchar, mid.index_handle) + '_' +
    LEFT (PARSENAME(mid.statement, 1), 32) + '
    ON ' + mid.statement + '
    (' + ISNULL (mid.equality_columns,'')
        + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
        + ISNULL (mid.inequality_columns, '')
    + ')'
    + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
    migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC;

查询Missing Index的三张系统表

我们可以看到，这个查询语句使用了SQL Server三张系统表（sys.dm_db_missing_index_groups、sys.dm_db_missing_index_group_stats、sys.dm_db_missing_index_details）来获取Missing Index的信息。下面是这三张表的详细说明：

sys.dm_db_missing_index_groups：包含了每个索引缺失的相关列、对象ID和索引建议的名称和创建语句。

sys.dm_db_missing_index_group_stats：包含了统计索引缺失的信息，如索引缺失的次数、索引广告和最后重建的时间。

sys.dm_db_missing_index_details：包含了造成索引缺失的 SQL 语句。

计算每个Missing Index的优化度量值

这段代码的主要部分是计算每个Missing Index的优化度量值，即下面这段代码：

migs.avg_total_user_cost *

    (migs.avg_user_impact / 100.0) *
    (migs.user_seeks + migs.user_scans) AS improvement_measure

这里的优化度量值是根据查询后的结果进行计算得到的，包含了三个因素：查询的总成本，每次查询使用的次数（user_seeks + user_scans），以及索引对于查询的重要度（avg_user_impact）。这些信息对于选择是否创建Missing Index是非常重要的。

生成Missing Index的创建语句

最后一部分是生成Missing Index的创建语句，即下面这段代码：

'CREATE INDEX [missing_index_' +

    CONVERT (varchar, mig.index_group_handle) + '_' +
    CONVERT (varchar, mid.index_handle) + '_' +
    LEFT (PARSENAME(mid.statement, 1), 32) + '
    ON ' + mid.statement + '
    (' + ISNULL (mid.equality_columns,'')
        + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
        + ISNULL (mid.inequality_columns, '')
    + ')'
    + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement

这段代码生成了一个CREATE INDEX语句，并使用一些元数据，如数据库的ID、表的ID、列、语句等等，生成了适合当前缺失的索引的创建语句。

SQL语句实现自动创建Missing Index

在SQL Server中，我们可以使用如下的查询语句来自动创建Missing Index：

DECLARE @Database VARCHAR(50)='DatabaseName'

DECLARE @TableName VARCHAR(200)='TableName'
IF OBJECT_ID('TempDB.dbo.#MissingIndexes') IS NOT NULL
    DROP TABLE #MissingIndexes
CREATE TABLE #MissingIndexes
(
    IndexName NVARCHAR(200), 
    DatabaseName SYSNAME,
    TableName SYSNAME, 
    EqualityColumns NVARCHAR(4000), 
    InequalityColumns NVARCHAR(4000), 
    IncludedColumns NVARCHAR(4000),
    [Statement] NVARCHAR(MAX)
)
INSERT INTO #MissingIndexes (IndexName, EqualityColumns, InequalityColumns, IncludedColumns, [Statement])
SELECT DISTINCT 
    CONVERT(NVARCHAR, migs.user_seeks + migs.user_scans + migs.last_user_seek + migs.last_user_scan) + '_' 
    + mid.[statement] 
    + ISNULL (mid.equality_columns, '') 
    + ISNULL(mid.inequality_columns, '') 
    + ISNULL(mid.included_columns, ''),
    DB_NAME(mid.database_id) AS DatabaseName,
    OBJECT_NAME(mid.[object_id], mid.database_id) AS TableName, 
    mid.equality_columns, 
    mid.inequality_columns,
    mid.included_columns,
    mid.[statement]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle 
        AND mid.[statement] LIKE '%'+@TableName+'%' 
WHERE DB_NAME(mid.database_id) = @Database 
DECLARE @IndexName NVARCHAR(200), @DatabaseName SYSNAME, @TableName SYSNAME, 
    @EqualityColumns NVARCHAR(4000), @InequalityColumns NVARCHAR(4000), @IncludedColumns NVARCHAR(4000),
    @SchemaName SYSNAME, @ObjectName SYSNAME, @SQL NVARCHAR(MAX)=NULL,
    @TotalIndexScript NVARCHAR(MAX)=NULL
DECLARE @IndexCreationTemplate NVARCHAR(MAX)=N'CREATE INDEX {IndexName} ON {SchemaName}.{TableName} ({KeyColumns})
{IncludedColumns}
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON, FILLFACTOR = {FillFactor}, MAXDOP = {MaxDOP}) ON {FileGroupName}'
DECLARE @XMLData XML
SELECT  @XMLData=CONVERT(xml,''+REPLACE(REPLACE([Statement],CHAR(10),''),CHAR(13),'')+'')
FROM    #MissingIndexes
SELECT  item.value('./SQL[1]', 'nvarchar(max)') AS SQLScript, item.value('./QueryPlan[1]/MissingIndexes[1]/MissingIndexGroup[1]/Impact[1]', 'float') AS Impact
INTO    #Temp 
FROM    @XMLData.nodes('//root') AS A(item)
SELECT  @TotalIndexScript=COALESCE(@TotalIndexScript+CHAR(10)+CHAR(10),'')+REPLACE(REPLACE(@IndexCreationTemplate, '{IndexName}', [IndexName]), '{SchemaName}', @SchemaName), 
        @IndexName=[IndexName],
        @DatabaseName=DatabaseName,
        @TableName=TableName,
        @EqualityColumns=EqualityColumns,
        @InequalityColumns=InequalityColumns,
        @IncludedColumns=IncludedColumns
FROM    #MissingIndexes
WHERE   CHARINDEX('[', @TableName) = 0
    AND Impact > 10
    AND CHARINDEX('#', @TableName) = 0  --ignore temp tables 
    AND CHARINDEX('@', @TableName) = 0  --ignore table variables 
GROUP BY    [IndexName],
            DatabaseName, 
            TableName,  
            EqualityColumns, 
            InequalityColumns, 
            IncludedColumns, 
            [Statement]
ORDER BY    Impact DESC
SELECT  DISTINCT        @TableName = TableName, 
            @SchemaName = SchemaName 
FROM    INFORMATION_SCHEMA.TABLES
WHERE   TABLE_NAME = @TableName
IF @SchemaName IS NULL
BEGIN
    SELECT  @SchemaName = SCHEMA_NAME(schema_id)
    FROM    sys.objects
    WHERE   object_id = OBJECT_ID(@TableName)
END
SELECT  @SQL =REPLACE(REPLACE(@IndexCreationTemplate, '{IndexName}', @IndexName),'{SchemaName}', @SchemaName)
IF CHARINDEX('[', @TableName) <> 0 
BEGIN
    SELECT  @SQL=STUFF(@SQL,CHARINDEX('.', @SQL),1,'') --remove the extra period if the table is a system object
END
SELECT  @SQL=REPLACE(@SQL,'{TableName}',@TableName),
        @SQL=REPLACE(@SQL,'{KeyColumns}',COALESCE(@EqualityColumns+CASE WHEN @InequalityColumns IS NULL THEN '' ELSE ', '+@InequalityColumns END,'')),
        @SQL=REPLACE(@SQL,'{IncludedColumns}',CASE WHEN @IncludedColumns IS NOT NULL THEN CHAR(10)+'INCLUDE ('+@IncludedColumns+')' ELSE '' END),
        @SQL=REPLACE(@SQL,'{FillFactor}',90),
        @SQL=REPLACE(@SQL,'{MaxDOP}',0),
        @SQL=REPLACE(@SQL,'{FileGroupName}',NULL)
--Run the command
SELECT  @SQL
EXECUTE sp_executesql @SQL

代码解析

这段代码需要先创建一个临时表#MissingIndexes，然后将查询得到的Missing Index信息插入到这个表中。

过滤出当前表的Missing Index

我们可以看到，这个查询语句使用了参数@Database和@TableName来过滤出当前表的Missing Index信息，即下面这段代码：

DECLARE @Database VARCHAR(50)='DatabaseName'

DECLARE @TableName VARCHAR(200)='TableName'
...
WHERE DB_NAME(mid.database_id) = @Database

计算每个Missing Index的优化度量值

这段代码也使用了计算优化度量值的方法，即下面这段代码：

CONVERT(NVARCHAR, migs.user_seeks + migs.user_scans + migs.last_user_seek + migs.last_user_scan) + '_' 

    + mid.[statement] 
    + ISNULL (mid.equality_columns, '') 
    + ISNULL(mid.inequality_columns, '') 
    + ISNULL(mid.included_columns, '')

生成Missing Index的创建语句

最后一部分是生成Missing Index的创建语句，即和查询语句一样使用一些元数据生成适合当前缺失的索引的创建语句，然后执行SQL语句来创建Missing Index。

总结

通过以上两节的内容，我们可以知道SQL Server中如何查询和自动创建Missing Index，大大提高了查询效率。不过，仅仅是在监测到了Missing Index之后，我们才知道需要创建索引，而且只有在查询大量数据时才能看到明显的效果。所以，我们平时需要学会如何设计合适的索引，以提高整体的性能。