星期三, 4月 08, 2020

[SQL] 複合索引 - 欄位順序影響

在改寫一個查詢時跳出 missing index 建議,發現該建議影響重點在於更換複合索引順序,剛好最近有看過國外大神介紹複合索引,很快就抓到這個點。

以往複合索引都只把重心放在第一個欄位的選擇,畢竟第一個欄位會影響到 index seek 是否會被使用,就沒有注意到剩下欄位順序,該情況是複合索引順序為 ADCB,TSQL 語法大略為
SELECT
    ...........
FROM TableName
WHERE 欄位A = 條件A
    AND 欄位B = 條件B
    AND 非Index欄位1 > 條件
WHERE 條件,用到複合索引的第一個和第四個欄位,missing index 建議複合索引順序以 AB 為首,簡單說就是把複合索引的前兩個欄位順序符合 WHERE 條件,修正複合索引順序後,statistics 因為都是跑 index seek 所以差異不大,但執行計畫從 0.74559 變成 0.33610,大概改善 45% 左右,該查詢剛好是該 Table 的主查詢語法,就直接把複合索引順序修正

以 AdventureWorks2017 來模擬的話,情況大概如下語法
USE AdventureWorks2017
GO

DROP INDEX IF EXISTS [IX_LastName_FirstName_MiddleName] ON Person.Person
DROP INDEX IF EXISTS [IX_LastName_MiddleName_FirstName] ON Person.Person
GO 

CREATE INDEX [IX_LastName_FirstName_MiddleName] ON Person.Person (LastName,FirstName,MiddleName);  
CREATE INDEX [IX_LastName_MiddleName_FirstName] ON Person.Person (LastName,MiddleName,FirstName);  
GO

-- WHERE 條件為複合索引的第一、二欄位
SELECT *
FROM Person.Person WITH (INDEX(IX_LastName_FirstName_MiddleName))
WHERE LastName = 'Reed'
  AND FirstName = 'Gabriella'

-- WHERE 條件為複合索引的第一、三欄位
SELECT *
FROM Person.Person WITH (INDEX(IX_LastName_MiddleName_FirstName))
WHERE LastName = 'Reed'
  AND FirstName = 'Gabriella'
[SQL] 複合索引欄位順序影響 - 2

2 則留言:

  1. 可是調完之後其他用的AD的不會影響嗎

    回覆刪除
  2. 調整 index 一定會影響其他 Query,該 case 剛好是該商業邏輯的主 Query 語法

    回覆刪除