我們知道，合理的索引能大幅提升性能，但冗余的索引也會(huì )降低數據庫性能。隨著(zhù)我們業(yè)務(wù)的發(fā)展，數據庫的中的表、表結構、查詢(xún)的內容都有可能發(fā)生變化。這樣，有的索引就可能不再使用了，需要刪除（因為維護索引即浪費存儲，又耗費性能）；而有的表則需要修改或者增加索引。本文主要給出快速確定不再使用的索引的查找方式之一，動(dòng)態(tài)視圖（DMV）查詢(xún)。

無(wú)用索引

首先我們來(lái)看一下如何查詢(xún)無(wú)用的索引。sys.dm_db_index_usage_stats 記錄自上次重啟或數據庫離線(xiàn)或重置統計信息后使用到的索引，sys.indexes 記錄數據中所有表的索引，排除掉最近使用的索引，即為最近沒(méi)有使用的索引，具體腳本如下：

--查詢(xún)數據庫中沒(méi)有使用過(guò)到索引
USE WideWorldImporters;
GO
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
       o.name tableName,i.name indexName
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]>0;

因為我們只考察用戶(hù)創(chuàng )建的表或者索引視圖，最后我們只篩選出sys.objects 中type為“U”（用戶(hù)創(chuàng )建的表）和“V”（用戶(hù)創(chuàng )建的視圖索引）。sys.indexes 中type=0是堆，所以也排除。下面給出產(chǎn)生刪除索引的腳本：

--刪除沒(méi)有使用過(guò)索引腳本產(chǎn)生
USE WideWorldImporters;
GO
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.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]>0;

上面的腳本每條對應一個(gè)表的一個(gè)索引的刪除語(yǔ)句，當然也可以使用如下腳本產(chǎn)生一條語(yǔ)句。

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]>0
FOR XML PATH(''),type).value('.','NVARCHAR(MAX)');
--exec sp_executesql @sql

細心的讀者會(huì )發(fā)現，上面最后一條語(yǔ)句（exec sp_executesql @sql）是注釋掉的，直接這樣執行是可以最快速的刪除所有無(wú)用索引。但是，正如我們上面所說(shuō)的，sys.dm_db_index_usage_stats 記錄自上次重啟或數據庫離線(xiàn)或重置統計信息后使用到的索引，所以其記錄的用到的索引可能是不全的（如果我們最近剛重啟過(guò)數據庫服務(wù)、數據庫所在的服務(wù)器或者重置了動(dòng)態(tài)視圖），這樣可能導致部分有用的索引也被刪除掉，切記、切記、切記，生成的腳本不能直接執行。保險的做法是，至少，在數據庫服務(wù)運行一個(gè)月做這樣的事情，如果有經(jīng)常重啟維護的數據庫服務(wù)，可以在數據庫重啟維護之前收集記錄已經(jīng)使用的索引。經(jīng)過(guò)幾個(gè)月或一年的記錄，最終確定不需要的索引，再進(jìn)行刪除。