德瑞克：SQL Server 學習筆記: [SQL Server] Suggestion: create Index on Foreign Keys

2017-10-21

[SQL Server] Suggestion: create Index on Foreign Keys - 建議在 FK 資料行上建立索引

為了確保資料一致性(Data Integrity)，在SQL Server上可以使用：
PRIMARY KEY 條件約束、FOREIGN KEY 條件約束、UNIQUE 條件約束、CHECK 條件約束、DEFAULT 定義、允許 Null 值等。

其中，FOREIGN KEY 條件約束，依據預設值：

在 Primary key 資料行上建立索引。
未在 Foreign Keys 資料行上建立索引。

若在 PK 資料表上，刪除資料列、更新 PK 值時，系統必須額外再到 FK 資料表上做資料查詢比對，或是執行相對應的刪除或更新作業。
例如：
在 PK 資料表上刪除資料列，系統當下必須額外再到 FK 資料表上做查詢比對，執行刪除作業。

建議

在 FK 資料表上建立索引，可優化效能於：刪除、更新或查詢等作業上。

	刪除 PK 資料-子樹成本(Subtree Cost)	索引使用方式
有建立 FK， NO ACTION，沒有索引	28.779146	Clustered Index Scan/Table Scan
有建立 FK， NO ACTION，有建立索引	8.4662016	NonClustered Index Seek
有建立 FK，啟用 CASCADE，沒有索引	45.5311	Clustered Index Scan/Table Scan
有建立 FK，啟用 CASCADE，有建立索引	9.15951	NonClustered Index Seek
沒有 FK，沒有索引	0.0132841

對照起來，在 FK 上建立索引，可節省耗用的子樹成本達 5 倍。

可使用以下指令碼
Find Foreign keys without index - 找出 FK 資料表缺少的索引
http://sharedderrick.blogspot.tw/2017/10/find-foreign-keys-without-index-fk.html

示範：刪除 PK 資料列的效能分析

示範環境：SQL Server 2017

-- 01_ 檢視資料表的資料

(1) DELETE rows on Product_PK table and without FK Constraints

-- 02_刪除資料_沒有FK

(2) DELETE rows on Product_PK table and FK Constraints with NO ACTION

-- 03_建立FK

-- 04_List_All_FK_in_DB

-- 05_資料庫關聯圖

-- 06_資料庫關聯圖

-- 101_DELETE rows on Product_PK table and FK Constraints with NO ACTION and without Index

-- 102_DELETE rows on Product_PK table and FK Constraints with NO ACTION and without Index_2

-- 103_DELETE rows on Product_PK table and FK Constraints with NO ACTION and without Index_3

(3) DELETE rows on Product_PK table and FK Constraints with NO ACTION and with Index

-- 104_0_Create index on FK constraints tables

-- 104_DELETE rows on Product_PK table and FK Constraints with NO ACTION and with Index

-- 105_DELETE rows on Product_PK table and FK Constraints with NO ACTION and with Index_2

-- 106_DELETE rows on Product_PK table and FK Constraints with NO ACTION and with Index_3

(4) DELETE rows on Product_PK table and FK Constraints with DELETE CASCADE and without Index

-- 10_FK_ with DELETE CASCADE

-- 11_List_All_FK_in_DB

-- 12_資料庫關聯圖

-- 13_檢視執行計畫_叢集索引掃描_CASCADE

-- 14_檢視執行計畫_叢集索引掃描_CASCADE_2

-- 15_檢視執行計畫_叢集索引掃描_CASCADE_3

-- 16_檢視執行計畫_叢集索引掃描_CASCADE_4

(5) DELETE rows on Product_PK table and FK constraints tables with index and DELETE CASCADE

-- 17_在FK上建立索引

-- 18_檢視執行計畫_CASCADE_索引搜尋

-- 19_檢視執行計畫_CASCADE_索引搜尋_2

-- 20_檢視執行計畫_CASCADE_索引搜尋_3

-- 21_檢視執行計畫_CASCADE_索引搜尋_4

有建立 FK，啟用 CASCADE，沒有索引

-- 有建立 FK，啟用 CASCADE，沒有索引

-- 99_遺漏索引

檢索 FOREIGN KEY 條件約束

基於下列原因，在外部索引鍵上建立索引通常很有幫助：

(1) 變更 PRIMARY KEY 條件約束將根據關聯資料表內的 FOREIGN KEY 條件約束來做檢查。

(2) 當關聯資料表的資料藉著將資料表的 FOREIGN KEY 條件約束和另一個資料表的主要或唯一索引鍵資料行進行比對，而合併於查詢中時，通常會使用外部索引鍵資料行來聯結準則。
索引可讓 Database Engine 在外部索引鍵資料表中快速尋找相關資料。

不過，建立此索引並非必要。即使於資料表之間沒有定義 PRIMARY KEY 或 FOREIGN KEY 條件約束，仍可合併兩個關聯資料表的資料，不過兩個資料表之間的外部索引鍵關聯性代表這兩個資料表已經過最佳化，可合併於使用該索引鍵作為準則的查詢之中。

範例程式碼

20171021_Suggestion create Index on Foreign Key
https://drive.google.com/drive/folders/0B9PQZW3M2F40TGxfdUVWbVNkVmc?usp=sharing