IT日常- SQL索引最佳化

前言

因近期工作內容偏向SQL查詢效能調校以此篇作為調整紀錄(以下SQL系統為MSSQL)

系統查詢功能隨著資料的增多(約9500萬筆資料)導致原本設計的資料表查詢效能日漸緩慢，近期針對目前常用的查詢語法做了梳理以下幾個調校方法做整理，以下是針對已經有設定Index的情況下進一步調整。

服用Index前注意事項

優點:加速查詢的效率從原本的全表搜尋(Table Scan)機制改善為索引查詢(Index Seek)

缺點:建立的Index越多，在對資料進行新增/刪除/修改時，就會花越多時間來完成。 盡量避免對設定索引的欄位經常性的改動操作，會導致原先排續好的索引順序越零碎而影響查詢效能，必要時需定期使用重組排序讓原本零碎的索引重新排序以改刪查詢效能。

索引最佳化

1.覆蓋索引搜尋(Covering Index) :

意思是讓 Select 的欄位 = Index的欄位 當查詢被執行的時候，若需要的欄位全都在索引當中，則會直接將索引查到的內容回傳，減少回主表查詢缺少欄位所需要的I/O時間。

2.複合索引(Multiple Column Index) :

若設定索引時是用多個欄位組成，建議把「頻繁被作為查詢條件的欄位越左方越好」 因為在下Where查詢條件時會有索引的順序性才能吃到此索引 例如: 當初建立的複合索引是用欄位 A/B/C組合，在下Where條件是只能是以下的查詢組合才能吃到複合索引的效能

Select * from Table where A = 0 and B = 0 and C = 0 
Select * from Table where A = 0 and B = 0
Select * from Table where A = 0

以下順序無法吃到此索引功能

Select * from Table where B = 0 and C = 0 
Select * from Table where A = 0 and C = 0
Select * from Table where C = 0

3.索引涵蓋查詢欄位(Include Columns) :

在建立索引的語法中加入涵蓋的查詢欄位 例如以下查詢語法中，在LastName欄位新增Index

SELECT LastName, FirstName, Email 
FROM Student WHERE LastName = 'Dave'

改善前:

Index新增語法

CREATE NONCLUSTERED INDEX idx_nonclustered ON Student(LastName)

因為新增的索引僅包含 LastName 欄位的資訊但查詢的欄位還有FirstName, Email， SQL Server 必須通過查詢的回溯操作（或二次查詢）來檢索 FirstName 和 Email 的值。

意思是因為索引中只有加入LastName 所以用索引找到LastName的RowId時，會再用RowId回去取 FirstName 和 Email的值

改善後:

原本的Index內容改為

CREATE NONCLUSTERED INDEX idx_nonclustered ON Student(LastName) 
INCLUDE (FirstName, Email)

這時的查詢語法執行的邏輯就會變成一樣用LastName的索引找到資料後會一起把FirstName, Email值取出來，不需再用RowId做二次查詢回去撈FirstName, Email的值。

參考資料

SQL Worker

SQL Server 索引筆記

5個概念拯救龜速的SQL查詢(Query)

INCLUDE 關鍵字以提高查詢效能