索引前導鍵(Leading-column)的選擇

如果有A、B、C三個欄位要據以建立一個索引，那麼建立的方式共有六種：

create index idx1 on TABLE(A,B,C)

create index idx1 on TABLE(A,C,B)

create index idx1 on TABLE(B,A,C)

create index idx1 on TABLE(B,C,A)

create index idx1 on TABLE(C,A,B)

create index idx1 on TABLE(C,B,A)

~~你會用哪一種？

~~索引鍵的順序不同，對搜尋效率有影響嗎？

~~如果有影響，那麼你的考量原則是什麼？

早期的我，由於不知道有什麼原則可以遵循，因此當時我會將每個索引都建一次，然後執行相應的SQL語句，觀察所耗用的時間及執行計畫的成本，最後選擇耗用時間或成本最少的索引。

~~我想大多數的DBA應該跟我一樣，曾經面臨這樣的問題~~

「索引」是排序過的資料，透過索引可以加快資料的搜尋速度。當索引建立的正確並有適當的維護時，它可以顯著的提高SQL語法執行的效能。索引可以想像成一般書籍的目錄，有這種目錄，你就可以快速的找到你要找的主題。如果沒有，那麼你可能要翻遍整本書才能找到你所需要的訊息。

一般會考量匹配前端AP常用SQL語法來據以建立索引，以加快SQL語法的執行速度。如果SQL語法的where子句只有一個欄位查詢條件，那麼就沒有鍵值順序的問題，只有這個欄位適不適合單獨建立索引的問題。

如果SQL語法的where子句有多個欄位查詢條件，通常我們會用這些欄位來建立一個「複合式索引」。

複合式索引，由多個欄位組成，這些欄位在索引中的先後順序，常見的建議是以每個欄位的「選擇性(Selectivity)」來決定其排列的順序。選擇性越高的，排在越前面。這樣的原則在B-Tree的搜尋上，可以最大程度上的過濾掉不需要的分支。加快葉層級掃瞄定位的速度。

「選擇性」可以定義為：「不同紀錄與總紀錄的百分比」。例如：table共有1000筆資料，其中有個性別欄位[Gender]，它的欄位值只有兩種「男、女」，所以Gender欄位的選擇性等於2/1000=0.002。

選擇性越高，表示資料重覆的比例越低。

一個欄位的選擇性，可以簡單地使用下列SQL語法得知

select convert(float,count(distinct 欄位名))/count(*) from TABLE

舉個簡單的例子，假設有一句經常執行的SQL如下

select count(*) from TABLE where LoginDate='2017-03-17' and LoginName='Phil'

如果要匹配這句SQL語法建立index，有兩種建法

(1)  Create index idx1 on TABLE(LoginDate,LoginName)

(2)  Create index idx1 on TABLE(LoginName,LoginDate)

假設這兩個欄位的選擇性：

LoginDate：0.02

LoginName：0.0004

LoginDate的選擇性高於LoginName，因此(1)，會是我們建立索引的較佳選擇。

在複合式索引中，除了欄位的排列順序外，對於索引前導鍵（leading-column）的選擇尤為重要。那是因為在一個多欄位組成的索引中，只有第一個欄位是有序的。

索引的前導鍵必需出現在where條件子句中，否則這個索引是無法被搜尋的。

例如上面的索引：

Create index idx1 on TABLE(LoginDate,LoginName)

如果查詢語句為：

select count(*) from TABLE where LoginName='Phil'

索引前導鍵沒有出現在where條件中，這句查詢將無法使用到這個索引的搜尋。

~~然而，僅只考慮欄位的「選擇性」，並不足以決定最佳的索引策略

where條件子句，是由一些「比較運算符」組成，如「=、<>、between、like」等等，我們可把這些比較運算符歸類為兩種

(1) 等式（equality）：即「=」

(2) 非等式（inequality）：凡不是「=」者，皆為非等式。

我們再看下面的例子

select * from TABLE where ID between 'Axxxxx' and 'Bxxxx' and Gender='M'

ID：身份證號

Gender：性別

查詢身份證號在某一範圍內的男性。其中ID欄位為「非等式(inequality)」，而Gender欄位為「等式(equality)」

若要匹配此語法建立叢集索引，有兩種建法

create unique clustered index idx1 on TABLE(ID,Gender)  →身份證欄位在前

VS

create unique clustered index idx1 on TABLE(Gender, ID)  →性別欄位在前

假設TABLE有20萬筆紀錄，身份證號在此TABLE中是唯一的，那麼這兩個欄位的選擇性如下：

(1) ID：1

(2) Gender：2/200000=0.00001

在建立索引時，很自然地，我們會選擇ID做為前導鍵：

create unique clustered index idx1 on TABLE(ID,Gender) →身份證欄位在前

但是，只考慮選擇性，而不考慮where條件是「等式(equality)」或「非等式(inequality)」，這樣的索引真的會比較有效率嗎？

我們透過少量的資料，來解釋一下….

Table「TestIndex」，假設有10筆資料，我們對它執行一個查詢：

select count(*) from TestIndex where ID between 2 and 6 and Gender='M'

分兩種情況來看

一、以ID,Gender來排序

如下表所示，在B-Tree葉層級(leaf level)共需掃瞄5筆資料後，可得到確定的結果

二、以Gender,ID來排序

如下表所示，在B-Tree葉層級(leaf level)共需搜尋比對3筆資料後，即可得到結果

~~這代表著什麼意思呢？

select count(*) from TestIndex where ID between 2 and 6 and Gender='M'     

首先在這句查詢條件中，ID是「非等式(inequality)」，Gender是「等式（equality）」

SQLSERVER的索引結構，使用「查詢條件值」，與B-Tree的根節點及中間節點內的鍵值進行比對，逐層確定要掃瞄範圍。當到達葉層級（leaf level）後，即開始進行掃瞄，若一頁掃瞄完成尚未超過條件值的範圍，則根據頁鏈再往下一頁掃瞄，直到發現第一筆超過範圍的紀錄為止。

多重欄位的複合式索引，一般的建議是將「選擇性」最高的欄位放在最前面做為前導鍵，這個主要是為了在根節點及中間節點的比對上，消除更多的分支，這會節省一些時間在確定掃瞄範圍上。

但一旦到達leaf level開始掃瞄，如果前導鍵是「非等式inequality」，可能導致在葉層級的掃瞄範圍擴大（如上面少量資料的說明）。

使用選擇性高的「非等式inequality」欄位做為索引的前導鍵，雖然可以加快「掃瞄範圍定位」的時間，但也會使得在葉層級的掃瞄頁數擴大許多。

掃瞄範圍的定位在根節點及中間節點中確定，即使是在大型的索引，可能也只有6~7個層級，因此它定位節省的成本有限。

反而是在葉層級的資料掃瞄上，使用「非等式inequality」做為索引的前導鍵，有可能擴大掃瞄的範圍成百上千頁。因此在索引建立時，關於葉層級的掃瞄成本應列為優先考量。

以下截錄一段SQLCAT Team的說明，請參考

However, once the leaf level is reached and scanning begins on the requested range, placing the inequality column first can result in the reading of hundreds or even thousands of extra pages to satisfy the same query. Clearly, the tradeoff at the leaf level can quickly overwhelm the advantages gained at the root and intermediate levels on queries that return ranges of values.

Google英翻中（有修飾）：

一旦達到葉級並且開始掃描所需要的範圍，則不等式欄位(inequality column)為前導鍵，可以導致讀取數百甚至數千個額外的頁面，以滿足相同的查詢。 顯然，在葉級別上的權衡可以很快地壓倒根節點和中間層節點對返回符合範圍的查詢所獲得的優勢。

多重欄位索引，使用「等式(equality)」欄位為前導鍵，會比用「非等式(inequality)」欄位做為前導鍵更有效率。即使等式的欄位選擇性非常的低，即使它是像「性別」這種只有兩種資料的欄位。

多重欄位索引鍵的考量優先順序應為：

(1)等式(equality)欄位

(2)非等式(inequality)欄位

(3)若等式（或非等式）欄位有多個，則使用這些欄位的選擇性由高到低順序排列。

因此，在上面的例子中

select count(*) from TestIndex where ID between 2 and 6 and Gender='M'  

若要建立複合式(叢集或非叢集)索引以匹配這樣的查詢，應該以等式欄位Gender為前導鍵：

create unique clustered index idx1 on TestIndex(Gender,ID)

==========================================================

我們來做一個實驗，以實做來瞭解上面的說法並確認其是否正確…

首先建立一個 Table

身份證號ID，用identity來模擬，Gender為性別，另加一個固定長度的住址欄位，目地是讓一筆資料夠長，令整個Table多一些pages，以利評估。

create table TestIndex(ID int identity ,Gender char(1),Addr char(200))

新增男女各10萬筆資料

declare @x int =1

while (@x<=100000)

  begin

    insert into TestIndex values('M' , 'This is a Address...')

    insert into TestIndex values('F' , 'This is a Address...')

       set @x=@x+1

  end

首先執行一次全表掃瞄查詢

set statistics io,time on

select count(*) from TestIndex

資料表 'testindex'。掃描計數 1，邏輯讀取 5406，實體讀取 0，讀取前讀取 0，LOB 邏輯讀取 0，LOB 實體讀取 0，LOB 讀取前讀取 0。

SQL Server 執行次數:

，CPU 時間 = 94 ms，經過時間 = 113 ms。

上面執行後的統計結果意思是，SQLSERVER完成此次查詢，總共讀取了5406頁，花費CPU時間94 ms，總花費時間（含IO等）113 ms。

首先我們建立叢集索引，使用選擇性高但條件是inequality的欄位「ID」做為前導鍵

create unique clustered index C_idx_ID on TestIndex(ID,Gender)

執行條件式查詢

select count(*) from TestIndex where ID between 100 and 300 and Gender='M'

從結果中可以看到，索引使用ID做為前導鍵，邏輯讀取為10。

接著我們再來測試，使用性別做為前導鍵

建立叢集索引，使用選擇性低，但條件是equality欄位「Gender」做為前導鍵

drop index TestIndex. C_idx_ID

create unique clustered index C_idx_Gender on TestIndex(Gender,ID)

執行同樣的查詢

dbcc freeproccache

select count(*) from TestIndex where ID between 100 and 300 and Gender='M'

從結果來看，反而是選擇性低的「性別」欄位在前的索引，效能更好。邏輯讀取由10降到了7。

註：

通常我們使用「邏輯讀取」來衡量SQL的效能，它越小越好。我們不會使用CPU Time或elapsed time（經過時間），因為這兩個時間值有可能是「不可測量的小(immeasurably small)」，例如你可能經常看到CPU time = 0 ms。

另外一個elapsed time還包含了I/O子系統所花費的時間，而I/O子系統有可能因為其它因素忙碌，使得elapsed time是會變動的，故不適合做為評估基準。

使用「邏輯讀取」數，可以衡量同一句SQL走不同index的成本以評估哪個index更佳。或者用在索引碎片重整前後或統計值更新前後的效能指標。

從上面的實驗，我們初步證明了：

多重欄位索引，使用「等式(equality)」欄位為前導鍵，會比用「非等式(inequality)」欄位做為前導鍵更有效率。

~~然而SQLSERVER的優化器，它知道這個原則嗎？

我們再來測試

刪除叢集索引

drop index TestIndex.C_idx_Gender

分別使用ID及Gneder做為前導鍵，建立兩個非叢集索引

create index idx_ID on TestIndex(ID,Gender)

create index idx_Gender on TestIndex(Gender,ID)

執行同樣的查詢

dbcc freeproccache

select count(*) from TestIndex where ID between 100 and 300 and Gender='M'

從結果的執行計畫中可以看到，系統優化器選擇走「idx_Gender」這個索引，而這索引是用equality、選擇性低的性別(Gender)欄位為前導鍵的。這表明SQLSEVER優化器知道equality欄位為前導鍵的索引是更好的選擇。

~~接下來，我們感興趣的是，SQLSERVER是如何地判斷哪個index是更好的選擇?

反對使用低選擇性的欄位做為索引前導鍵的主要理由是：「統計資料只會針對索引的第一個欄位建立統計訊息」，如果前導鍵只有男、女兩種選擇，那麼據以建立的統計訊息，是不太具有參考價值的。

對於「直方圖(Histogram)」而言，這種說法是正確的。

但在多where條件下，優化器評估要走哪個index，直方圖並不是唯一的參考因子。如「身份證號」跟「性別」的例子，這個索引無論哪個欄位當做前導鍵，SQLSERVER仍然會在產生執行計畫時，評估第二個欄位的統計訊息。

~~我們以上面的範例，繼續說明

首先刪除相關的index及該table的所有統計訊息，並重建相同的兩個索引

create index idx_ID on TestIndex(ID,Gender)

create index idx_Gender on TestIndex(Gender,ID)

查看TABLE目前有幾個統計訊息

select * from sys.stats where object_id = OBJECT_ID('testindex')

上圖可以看到目前textindex這個Table，有兩組統計資料。

執行同樣的查詢

dbcc freeproccache

select count(*) from TestIndex where ID between 100 and 300 and Gender='M'

優化器選擇走idx_Gender，而且有正確估計到資料列數目為100。

檢查索引的使用狀況

檢查索引的使用狀況，我們確認只有idx_Gender被seek過1次，另一索引idx_ID並沒有被使用過。

再次查看TABLE目前有幾個統計訊息

select * from sys.stats where object_id = OBJECT_ID('testindex')

執行完查詢後，還是一樣，有兩組統計資料。

  

我們檢查一下idx_Gender的統計資料

dbcc show_statistics(TestIndex,idx_Gender)

看紅框處，系統確實僅使用前導鍵「Gender/性別」做為直方圖。在這個直方圖中，只有F跟M兩種資料。每種資料各有10萬筆。

~~~那麼，問題來了~~

系統走idx_Gender這個索引，依照這索引的統計資料，系統是憑什麼「估計」出符合條件的有100筆 .！？?

我們先刪除掉另一個索引

drop index testindex.idx_ID

查看TABLE目前有幾個統計訊息

select * from sys.stats where object_id = OBJECT_ID('testindex')

確定只剩下一組統計資料

  

執行同樣的查詢

dbcc freeproccache

select count(*) from TestIndex where ID between 100 and 300 and Gender='M'

我們可以發現，系統仍然選擇使用相同的索引idx_Gender，但「估計」的資料列數目，卻由原本的「100」變成了「1」。這個估計值跟實際的資料列數目，誤差頗大。

執行計畫所使用的index沒變，但估計的資料列數目變了。這表示某個用於產生執行計畫的統計資料發生了改變。而這個改變了的「統計資料」，必然不是idx_Gender這個索引的統計值。而是另有其它。因為idx_Gender並沒有任何的變動。

查看TABLE目前有幾個統計訊息

select * from sys.stats where object_id = OBJECT_ID('testindex')

我們可以看到，在執行查詢後，系統竟然自動增加了一組統計資料「auto_created = 1」。

我們檢查一下，看看這個新增的統計資料，它到底統計了什麼

dbcc show_statistics(TestIndex,_WA_Sys_00000001_744F2D60)

由上圖可以發現，系統自動產生了「ID」這個欄位的統計資訊。

自動產生的統計資料跟建立索引時產生的統計資料，兩者最大的不同點在於自動產生的統計資訊係採取抽樣方式建立，所以統計資料會有較大誤差（上圖共有200000筆資料，抽樣數為54954）。

而建立索引時所產生的統計資訊則是fullscan，故不會有誤差。

之所以會自動產生統計資料，是因為查詢的語句中，有用到ID欄位為條件，但優化器搜遍TABLE所有的索引，找不到ID欄位的直方圖(histogram)統計值，系統便會去檢查資料庫的「自動建立統計資料」的選項是否為true（如下圖），若是true，則自動產生ID的統計資料。

我們手動更新一下自動建立的統計訊息

update statistics testindex _WA_Sys_00000001_744F2D60 with fullscan

執行同樣的查詢

dbcc freeproccache

select count(*) from TestIndex where ID between 100 and 300 and Gender='M'

更新自動建立的統計資料後，可以發現估計的資料列數目回到正常。

綜上所述，多重欄位索引中無論哪個欄位做為前導鍵，SQL Server在確定使用哪個索引之前，會以SQL語法的where條件，去評估每個欄位的統計資料。僅僅只有前導鍵的統計訊息並不足夠。會在產生查詢計劃時搜尋其它欄位的統計資訊。

SQL Server首先依where條件的欄位，查看有沒有索引是用這些欄位當前導鍵的。如果有，SQL Server將會使用該索引的統計資料。接著搜尋該索引有無下一個where條件欄位的統計訊息。如果沒有，則檢查其它索引是否包含這欄位的統計訊息，如果也找不到，且該資料庫已啟用了AUTO_CREATE_STATISTICS。就會自動建立該欄位的統計訊息，並且在產生查詢計劃時使用這些自動創建的統計資料。

  

結論

在「自動建立統計資料」及「自動更新統計資料」兩選項為true的前提下。

多重欄位索引的建立，僅僅檢查欄位的基數或選擇性，並不足以決定最佳的索引策略。

當SQL語法的查詢條件，包含等式(equality)和非等式（inequality）的欄位時，應首先考慮等式(equality)欄位 - 即使這個欄位的選擇性非常的低。即使這個欄位像是性別欄位（只有兩種型態資料）也是如此。

請注意，將非等式（inequality）欄位做為前導鍵，永遠不會比等式(equality)欄位更有效。

索引鍵欄位的排列順序，考量因素應為：

(1)等式(equality)欄位

(2)非等式(inequality)欄位

(3)若等式（或非等式）欄位有多個，則使用這些欄位的選擇性由高到低順序排列。

寫在最後

我記得有人說過，DBA依工作性質，可以分成兩類：

一、AP-DBA：主要是與AP開發人員協同工作，經常與開發人員接觸。

二、DB-DBA：專職負責維持並保障DB的穩定服務，較少與AP接觸。

如果你是一位所謂的AP-DBA，那麼理解及掌握索引的知識，將非常有助於工作的推展。

實務上，AP所執行的SQL語法，除了where條件外，還經常會包含有group by/order by..等條件。而本篇，主要著重在索引「前導鍵」的選擇，對於當where / group /order 同時出現時的索引策略，以及clustered vs non-clustered index或key column vs include column等議題，則不在討論範圍。

索引是影響資料庫效能的重要關鍵因素，但卻少有書本或文章去談論它建立時應考量的原則及策略。這篇文章主要在說明複合式索引的欄位順序該如何決定，特別著墨在前導鍵的選擇上。文中做了比較多的實驗，或許讀起來比較凌亂，但其中包含了一些重要的觀念。

我自己跌跌撞撞的摸索多年，才算是對它有初步的瞭解。

希望這篇文章有助於跟我一樣正在DBA這條路上前行的朋友們，在面對多重欄位索引時，不會再猶疑不定，也可以少走一些彎路。