我們都知道,為 WHERE 條件用到的欄位建立索引可改善查詢效能,假設有 SQL 查詢如下,如果要為它建立索引,請問索引 Key 應包含哪些欄位?

SELECT LastName,FirstName,Address,City,State,Zip,Phone
FROM Employee
WHERE (City = 'Madison' OR MiddleInitial = 'A') AND Status = 1

如果你也覺得是 City, MiddleInitial, Status,歡迎跟我一起補充知識。

【先修知識】

索引該怎麼建,聽 SQL Server 的建議是不錯的點子。遇到效率不佳的查詢,SSMS 會在Execution Plan 貼心附上索引建議,故要知道 WHERE (Col1 = '...' OR Col2 = '...') AND Col3 = '...' 該加什麼索引,實際用 SSMS 跑一下便知。

我在 2019-2022 Conference/Session Demos and Sample Database找到有八萬筆模擬員工資料的 2012 Employee Case Study Sample Database,用它來做實驗。

當 WHERE 條件是 City = 'Madison' AND MiddleInitial = 'A' AND Status = 1,SQL 建議為 MiddleInitial、City 與 Status 建立索引,跟我過去所知概念一致,其餘 SELECT 用到的欄位則設為 INCLUDE :

當比對條件為 WHERE (City = 'Madison' OR MiddleInitial = 'A') AND Status = 1,將 City 與 MiddleInitial 設成索引 Key 並無幫助,建議的索引 Key 欄位只有 Status:

實際測試「沒有索引」、「索引 Key 包含 City,MiddleInitial,Status」 以及「索引 Key 只有 Status」三種情境,我加了一個 ORDERY BY 比較三者效率。Execution Plan 中 ORDER BY 的 Cost % 愈低,表示 WHERE 查詢的成本愈高,效能愈差。

沒有索引時,使用 Clustered Index Scan,成本佔 95%:

索引 Key 包含 City,MiddleInitial,Status,會用到索引,但採用 Index Scan,成本佔 95%:
(SSMS 會認定未最佳化,繼續提出 Missing Index 建議)

索引 Key 只有 Status 時,會使用效率最好的 Index Seek,成本下降到 89%:

再看兩個案例:

WHERE (City = 'Madison' OR City = 'San Diego') 
AND (MiddleInitial = 'A' OR MiddleInitial = 'L' )
AND Status = 1
-- 建議索引 Key: Status,MiddleInitial,City

WHERE (City = 'Madison' OR MiddleInitial = 'L') 
AND (MiddleInitial = 'A' OR City = 'San Diego' )
AND Status = 1
-- 建議索引 Key: Status

由以上大致可歸納出原則,如果 OR 會導致從頭再查一遍,無法靠前一個查詢縮小範圍,就沒必要設成 Key。例如:City = 'Madison' OR MiddleInitial = 'A',無法在 City 查詢結果用 MiddleInitial 繼續查,得從頭查起。而 (City = 'Madison' OR City = 'San Diego') AND (MiddleInitial = 'A' OR MiddleInitial = 'L') 可以從 「City 為 'Madison' 或 San Diego」的結果找「MiddleInitial 為 'A' 或 'L'」,範圍有縮小,索引用 City、MiddleIntial 當 Key 有助於加速查詢,故它們被列在建議中。

最後提醒一點,大家可能有注意到,以 WHERE (City = 'Madison' OR City = 'San Diego') AND (MiddleInitial = 'A' OR MiddleInitial = 'L' ) AND Status = 1 為例,最佳的索引 Key 順序應該是 City,MiddleInitial,Status,但 Missing Index 給的建議卻是 Status,MiddleInitial,City,在微軟文件 Missing Index 建議的已知限制有提到這點:

Key columns are suggested, but the suggestion doesn't specify an order for those columns. For information on ordering columns, see the Apply missing index suggestions section of this article.

註:至於 Missing Index 如何決定欄位順序可參考這篇 Stack Exchange 討論

故在決定索引 Key 以及 WHERE 條件順序時請回歸一般設計原則,較常查詢以及基數高(例如:生日比性別基數高)的欄位放左邉。(延伸閱讀:資料庫層的優化 - 索引設計與雷區)

【小結】

建立索引並非 WHERE 出現什麼欄位就加什麼,OR / AND 組合方式很重要,原則上要能產生「靠前次篩選結果縮小範圍」效果,加為 Key 欄位才有意義。而 Include SELECT 用到的欄位可省去 Key Lookup 原資料列的步驟,也能加快查詢。設計索引時,SSMS 提供的 Missing Index 建議是很不錯的參考,唯必須注意建議裡的 Key 欄位順序未最佳化,請自行調整別照抄。

Demostration of using missing index suggestion to find proper index key columns for WHERE OR condition.


Comments

# by Anonymous

這跟資料量也有闗係 如果 Status = 1 的資料列極小,那 Status 為第一個索引項目也沒有錯 資料庫太複雜了,就交給 DBA 處理了 我們就袖手旁觀了吧(遠目

# by None

一個 index 不夠可以用兩個三個 https://dev.mysql.com/doc/refman/8.0/en/index-merge-optimization.html https://www.postgresql.org/docs/current/indexes-bitmap-scans.html

Post a comment