SQL Server 索引优化——无用索引和索引缺失

SQL Server 索引优化

——无用索引和索引缺失

我们知道，合理的索引能大幅提升性能，但冗余的索引也会降低数据库性能。随着我们业务的发展，数据库的中的表、表结构、查询的内容都有可能发生变化。这样，有的索引就可能不再使用了，需要删除（因为维护索引即浪费存储，又耗费性能）；而有的表则需要修改或者增加索引。本文主要给出快速确定不再使用的索引的查找方式之一，动态视图（DMV）查询。

无用索引

首先我们来看一下如何查询无用的索引。sys.dm_db_index_usage_stats 记录自上次重启或数据库离线或重置统计信息后使用到的索引，sys.indexes 记录数据中所有表的索引，排除掉最近使用的索引，即为最近没有使用的索引，具体脚本如下：

--查询数据库中没有使用过的索引USE WideWorldImporters;GODECLARE @dbid INT=DB_ID('WideWorldImporters');WITH cte AS(       SELECT              [object_id],index_id       FROM sys.indexes       EXCEPT       SELECT              [object_id],index_id       FROM sys.dm_db_index_usage_stats       WHERE database_id=@dbid)SELECT       o.name tableName,i.name indexNameFROM sys.indexes iINNER JOIN cte ON cte.index_id=i.index_id AND cte.[object_id]=i.[object_id]INNER JOIN sys.objects o ON i.[object_id]=o.[object_id]WHERE o.[type] IN ('U','V')       AND i.[type]>1       AND is_primary_key=0       AND i.name NOT LIKE '[_]%';

因为我们只考察用户创建的表或者索引视图，最后我们只筛选出sys.objects 中type为“U”（用户创建的表）和“V”（用户创建的视图索引）。sys.indexes 中type=0是堆，type=1是聚集索引，所以也排除，同时我们排除主键索引，以及自动创建的统计信息。下面给出生成删除索引的脚本：

DECLARE @dbid INT=DB_ID('WideWorldImporters');WITH cte AS(       SELECT              [object_id],index_id       FROM sys.indexes       EXCEPT       SELECT              [object_id],index_id       FROM sys.dm_db_index_usage_stats       WHERE database_id=@dbid)SELECT     'DROP INDEX '+i.name+' ON '+ o.nameFROM sys.indexes iINNER JOIN cte ON cte.index_id=i.index_id AND cte.[object_id]=i.[object_id]INNER JOIN sys.objects o ON i.[object_id]=o.[object_id]WHERE o.[type] IN ('U','V')      AND i.[type]>1      AND is_primary_key=0     AND i.name NOT LIKE '[_]%';

上面的脚本每条对应一个表的一个索引的删除语句，当然也可以使用如下脚本产生一条语句。

DECLARE @dbid INT=DB_ID('WideWorldImporters');DECLARE @sql VARCHAR(MAX);WITH cte AS(       SELECT              [object_id],index_id       FROM sys.indexes       EXCEPT       SELECT              [object_id],index_id       FROM sys.dm_db_index_usage_stats       WHERE database_id=@dbid)SELECT @sql=(       SELECT            'DROP INDEX '+i.name+' ON '+ o.name       FROM sys.indexes i       INNER JOIN cte ON cte.index_id=i.index_id AND cte.[object_id]=i.[object_id]       INNER JOIN sys.objects o ON i.[object_id]=o.[object_id]       WHERE o.[type] IN ('U','V')             AND i.[type]>1             AND is_primary_key=0            AND i.name NOT LIKE '[_]%'FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)');--exec sp_executesql @sql

细心的读者会发现，上面最后一条语句（exec sp_executesql @sql）是注释掉的，直接这样执行是可以最快速的删除所有无用索引。但是，正如我们上面所说的，sys.dm_db_index_usage_stats 记录自上次重启或数据库离线或重置统计信息后使用到的索引，所以其记录的用到的索引可能是不全的（如果我们最近刚重启过数据库服务、数据库所在的服务器或者重置了动态视图），这样可能导致部分有用的索引也被删除掉，切记、切记、切记，生成的脚本不能直接执行。保险的做法是，至少，在数据库服务运行一个月做这样的事情，如果有经常重启维护的数据库服务，可以在数据库重启维护之前收集记录已经使用的索引。经过几个月或一年的记录，最终确定不需要的索引，再进行删除。