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

無(wú)用索引

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

--查詢數(shù)據(jù)庫(kù)中沒(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;

因?yàn)槲覀冎豢疾煊脩魟?chuàng)建的表或者索引視圖，最后我們只篩選出sys.objects 中type為“U”（用戶創(chuàng)建的表）和“V”（用戶創(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;

上面的腳本每條對(duì)應(yīng)一個(gè)表的一個(gè)索引的刪除語(yǔ)句，當(dāng)然也可以使用如下腳本產(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

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