搜尋本站文章

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 倍。



示範:刪除 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




參考資料

FOREIGN KEY 條件約束
https://technet.microsoft.com/zh-tw/library/ms175464(v=sql.105).aspx

SQL Server Performance Tuning Tip - Index Foreign Key Columns
https://www.mssqltips.com/sqlservertip/4305/sql-server-performance-tuning-tip--index-foreign-key-columns/

The Benefits of Indexing Foreign Keys
https://sqlperformance.com/2012/11/t-sql-queries/benefits-indexing-foreign-keys

SQLskills SQL101: Indexes on Foreign Keys
https://www.sqlskills.com/blogs/kimberly/sqlskills-sql101-indexes-foreign-keys/

When did SQL Server stop putting indexes on Foreign Key columns?
https://www.sqlskills.com/blogs/kimberly/when-did-sql-server-stop-putting-indexes-on-foreign-key-columns/

2017-10-15

[SQL Server] WHERE search condition with date: Use a range of dates and don't use CONVERT/CAST or BETWEEN (2)


延續前一篇的討論
[SQL Server] WHERE search condition with date: Use a range of dates and don't use CONVERT/CAST or BETWEEN (1)


建議 使用 比較運算子 + 邏輯運算子

  1. 使用 比較運算子 + 邏輯運算子,符合 SARG,使用到索引
  2. 無須額外耗用 CONVERT/CAST 轉換資料類型
  3. 直觀的語法、語意,通用性高

WHERE TransactionDate >= '20140805' AND TransactionDate < '20140806'



為何不建議使用  BETWEEN ?

因為 BETWEEN 在處理 DATE/TIME 資料類型時,容易造成誤解,導致查詢結果不正確。

問題:以下  WHERE 搜尋條件 是找出什麼資料?

WHERE TransactionDate BETWEEN '20140805' AND '20140806'

答案會是?

  1. 20140805 當日的全部資料
  2. 20140805 ~ 20140806 兩日的全部資料
  3. 20140805 全日 加上 20140806 00:00:00:000 時間點的資料


用以下範例來討論

-- 01_檢視資料特性_Datetime



WHERE TransactionDate BETWEEN '20140805' AND '20140806'

卻是篩選出 20140805 當日 加上 20140806 00:00:00:000 時間點的資料
回傳:4 筆資料列,Cost:0.150749

-- 02_BETWEEN_僅回傳4_rows


預期

應該回傳 5 筆資料列,卻僅回傳 4 筆資料列,缺少 1 筆資料列:2014-08-06 07:00:00.000

以條件式 BETWEEN '20140805' AND '20140806' 為例
多半寫出這類的語句,應該要找的是包含這 兩天 的全部資料
預期應該回傳 5 筆資料列。

卻是

但實際上
BETWEEN '20140805 00:00:00.000' AND '20140806 00:00:00.000'

也就是:20140805 全日 加上 20140806 00:00:00:000 時間點的資料。並未包含 20140806 當日的全部資料。

解決方案

該如何有效解決問題呢?

  • 改用 比較運算子 + 邏輯運算子 來篩選出明確的日期時間範圍
    • 例如:>= (大於或等於),  <= (小於或等於), < (小於)。


WHERE TransactionDate >= '20140805' AND TransactionDate < '20140807'

-- 03_改用比較運算子_正確回傳5_rows





對照執行計畫

近一步觀察執行計畫,以 BETWEEN 比較運算子 +邏輯運算子 來對照,BETWEEN 是耗用更多的資源。

-- 10_對照兩者執行計畫


-- 11_BETWEEN_搜尋述詞


觀察

  • 搜尋述詞-起點
    • [tempdb].[dbo].[Transactions02].TransactionDate >= 純量運算子('2014-08-05 00:00:00.000')
  • 搜尋述詞-終點 
    • [tempdb].[dbo].[Transactions02].TransactionDate <= 純量運算子('2014-08-06 00:00:00.000')

留意

BETWEEN 的終點,卻是被解譯為:
<= 純量運算子('2014-08-06 00:00:00.000')

-- 12_BETWEEN_搜尋述詞_2


-- 13_BETWEEN_搜尋述詞_3



使用  比較運算子 + 邏輯運算子

如預期的結果正確回傳。
語意沒有被誤解。

-- 14_比較運算子_搜尋述詞



-- 15_比較運算子_搜尋述詞_2


-- 16_比較運算子_搜尋述詞_3




範例程式碼

20171014_WHERE_DATE_Compare_Operator
https://drive.google.com/drive/folders/0B9PQZW3M2F40MDNoMGF4Wml6M00?usp=sharing



參考資料

[SQL Server] WHERE search condition with date: Use a range of dates and don't use CONVERT/CAST or BETWEEN (1)
http://sharedderrick.blogspot.tw/2017/10/sql-server-where-search-condition-with.html

What do BETWEEN and the devil have in common?
http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/19/what-do-between-and-the-devil-have-in-common.aspx

It's Time for Time
http://www.kimballgroup.com/1997/07/its-time-for-time/