MS SQL巡檢系列——檢查外鍵欄位是否缺少索引

前言感想：一時興起，突然想寫一個關於MS SQL的巡檢系列方面的文章，因為我覺得這方面的知識分享是有價值，也是非常有意義的。一方面，很多經驗不足的人，對於巡檢有點茫然，不知道要從哪些方面巡檢，另外一方面，網上關於MS SQL巡檢方面的資料好像也不是特別多。寫這個系列只是一個分享，自己的初衷是一個知識梳理、總結提煉過程，有些知識和腳本也不是原創，文章很多地方融入了自己的一些想法和見解的，不足和膚淺之處肯定也非常多，拋磚引玉，也希望大家提意見和建議、補充，指正其中的不足之處。Stay Hungry Stay Foolish！

MS SQL巡檢系列——檢查重覆索引

MS SQL巡檢系列——檢查外鍵欄位是否缺少索引

MS SQL巡檢系列——檢查資料庫上一次DBCC CHECKDB的時間

對於絕大部分情況，外鍵欄位都有必要建立對應的索引（註意，外鍵約束並不會自動建立索引），關於外鍵欄位為什麼要建立索引？下麵從幾個簡單的例子來分析一下。我們先準備測試環境數據。

CREATE TABLE PRIMARY_TB

(

    PRODUCT_CD        VARCHAR(12)      ,

    PRODUCT_DATE    DATE             ,

    PRODUCT_DESC    VARCHAR(120)     ,

    CONSTRAINT PK_PRIMARY_TB  PRIMARY KEY CLUSTERED (PRODUCT_CD)

);

SET NOCOUNT ON;

GO

DECLARE @Index INT=1;

BEGIN TRAN

WHILE @Index <= 3000

BEGIN

    INSERT INTO dbo.PRIMARY_TB

    SELECT 'Prd' + CAST(@Index AS VARCHAR(4)), GETDATE() - CEILING(RAND()*200), 'production description' + CAST(@Index AS VARCHAR(4));

    SET @Index +=1;

END

COMMIT;

CREATE TABLE FK_TB

(

    FK_ID            INT IDENTITY(1,1),

    SALES_REGION    VARCHAR(32),

    SALES_CITY        VARCHAR(32),

    PRODUCT_CD        VARCHAR(12),

    SALIES_SUM        INT,

    CONSTRAINT PK_FK_TB PRIMARY KEY CLUSTERED (FK_ID)

)

GO

ALTER TABLE [dbo].[FK_TB]  WITH CHECK ADD  CONSTRAINT [FK_PRIMARY_TB_PRODUCT_CD] FOREIGN KEY([PRODUCT_CD])

REFERENCES [dbo].[PRIMARY_TB] ([PRODUCT_CD]) ON  DELETE CASCADE;

GO

SET NOCOUNT ON;

GO

DECLARE @Index INT=1;

BEGIN TRAN

WHILE @Index <=1000000

BEGIN

    INSERT INTO FK_TB

    SELECT 'REGION'+CAST(CEILING(RAND()*20) AS VARCHAR(2)), CAST(CEILING(RAND()*300) AS VARCHAR(3)),'Prd'+ CAST(CEILING(RAND()*3000) AS VARCHAR(8)),CEILING(RAND()*100000);

    SET @Index +=1;

END

COMMIT;

UPDATE STATISTICS dbo.PRIMARY_TB WITH FULLSCAN;

UPDATE STATISTICS dbo.FK_TB WITH FULLSCAN;

GO

1： 外鍵欄位建立索引，在主表與子表JOIN操作時能提高性能，減少IO操作。

DBCC DROPCLEANBUFFERS;

GO

DBCC FREEPROCCACHE;

GO

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

SELECT  p.PRODUCT_CD ,

        p.PRODUCT_DATE ,

        f.SALES_REGION ,

        f.SALES_CITY ,

        f.SALIES_SUM

FROM    dbo.PRIMARY_TB p

        INNER JOIN dbo.FK_TB f ON p.PRODUCT_CD = f.PRODUCT_CD

WHERE p.PRODUCT_CD ='Prd131';

SET STATISTICS IO OFF;

SET STATISTICS TIME OFF;

如下截圖所示，如果外鍵欄位缺少索引，這兩個表關聯查詢時，子表就會走掃描（此處測試是聚集索引掃描），如果子表非常大（例如此處案例所示），IO開銷就比較大。

我們對外鍵約束欄位PRODUCT_CD建立下麵非聚集索引IDX_FK_TB，然後對比兩者的執行計劃和IO開銷

CREATE INDEX IDX_FK_TB ON dbo.FK_TB(PRODUCT_CD);

DBCC DROPCLEANBUFFERS;

GO

DBCC FREEPROCCACHE;

GO

SET STATISTICS IO ON;

SET STATISTICS TIME ON;

SELECT  p.PRODUCT_CD ,

        p.PRODUCT_DATE ,

        f.SALES_REGION ,

        f.SALES_CITY ,

        f.SALIES_SUM

FROM    dbo.PRIMARY_TB p

        INNER JOIN dbo.FK_TB f ON p.PRODUCT_CD = f.PRODUCT_CD

WHERE p.PRODUCT_CD ='Prd131'

SET STATISTICS IO OFF;

SET STATISTICS TIME OFF;

你會發現執行計劃從原來的聚集索引掃描（Clustered Index Scan）變成了索引查找（Index Seek），IO的減少也是非常明顯的。因為這裡僅僅是測試數據，複雜的生產環境，性能的提升有可能比這更加明顯。

2：如果外鍵約束為CASCADE（on update/delete）時，則當修改被引用行（referenced row）時，所有引用行（referencing rows ）也必須修改（更新或級聯刪除）。外鍵列上的索引能減小鎖的粒度和範圍，從而提高效率和性能。如下所示：

我們先看看缺少索引的情況。

DROP INDEX IDX_FK_TB ON dbo.FK_TB;

DBCC DROPCLEANBUFFERS;

GO

DBCC FREEPROCCACHE;

GO

SET STATISTICS IO ON;

SET STATISTICS TIME ON;