【譯】索引列，列選擇率和等式謂詞

    本篇文章也可以叫做”建立索引時那一列應該放到最前面”。

    通常對于索引列的選擇的通常準則都是把最高選擇率(譯者注：所謂選擇率指的是在where子句中作為選擇條件使用次數的比例來說的)的列放在最前面，我接下來并不是要說這個準則不對，因為這個準則本身是正確的。但通常在給出這個準則的同時并沒有同時給出為什么要把最高選擇率的列作為索引列以及索引列的順序。

    綜上原因，這很有可能導致對索引列選擇的誤解。比如，在極端情況下，某個人風聞了上述建議后，把所有非聚集索引的的索引鍵都設置成聚集索引的鍵（因為這列有很高的選擇率）,然后開始糾結為什么數據庫的性能開始慘不忍睹。

    出現上面那種極端情況是因為SQL Server為每一個索引存儲統計信息，但這個統計信息僅僅記錄索引索引第一列的統計分布，這意味著索引僅僅知道第一列的數據分布，如果第一列不作為謂詞使用，可能這個索引依然會被使用，但這并不是全部。

    除了統計分布圖之外，SQL Server還為索引列的所有子集存儲密度。對于3列作為組合索引鍵，SQL Server就會存儲第一列的密度，第一列和第二列的組合密度以及整個三列的組合密度。密度這個詞表示列中所存的數據所不同的概率，公式為1/唯一值。每個索引上的這個值都可以通過使用DBCC Show_Statistics加上DENSITY_VECTOR選項進行查看。

    這也同時意味著，雖然SQL Server知道了第一列的數據分布，也同時知道索引列中其它鍵組合包含數據的平均值。

     那么，對于索引列的先后順序該怎么做呢？要把最高選擇率的列放在第一個，剩下的列先后順序就無所謂了。

     下面通過下表來看這究竟是什么意思。

CREATE TABLE ConsideringIndexOrder (
ID INT IDENTITY,
SomeString VARCHAR (100),
SomeDate DATETIME DEFAULT GETDATE()
);

    假設上面的表有10000行，沒有聚集索引所以是基于堆存儲的，然后SomeString列包含100個不同的值，SomeDate列包含5000個不同的值，而ID列是自增，所以唯一。

    建立一個非聚集索引包含上述散列，順序為ID,SomeDate,SomeString.

    上面建立的索引只能在謂詞是如下時被使用：

…  WHERE ID = @ID AND SomeDate = @dt AND SomeString = @str
…  WHERE ID = @ID AND SomeDate = @dt
…  WHERE ID = @ID

    換句話說，這三列的子集只有按從左到右的順序包含在謂詞中才能被where和join使用。

     如果你僅僅在where子句之后使用SomeDate列作為過濾條件，則不能使用索引進行查找。這就像是你想通過電話本按照人的名字而不是姓查找電話號碼一樣，想找到這個人是不能使用目錄的，而只能翻遍整個電話版。

    此外，把具有最高選擇率的列放在最左邊，但這一列很少在謂詞中使用。而大量的where過濾的是其它列的話，只能進行索引掃描，而掃描的代價非常高。

    由此得出結論如何選擇放在索引第一列的列的標準并不唯一，而是基于數據庫中使用最多的查詢，如果這幾列在where等子句之后使用等于號進行過濾的話，那么毫無疑問，選擇具有最高選擇率的列放到第一位，這樣SQL Server就有更多的幾率知道這個索引有用，如果不是這樣的話，將在where等子句之后使用最多的列放到第一列，這樣這個索引就可以適用于更多的查詢。

    下面基于文章前面創建的表來做一些查詢。

    場景1：用ID作為謂詞使用等于號進行過濾

    這是最簡單的一個場景，因為這個情況直接匹配索引的第一列，索引僅僅使用查找方式找到數據。

    場景2 :  用ID和SomeDate列作為謂詞使用等于號進行過濾

     這個場景同樣非常簡單，使用和非聚集索引順序相同的子集作為過濾條件，因此也使用查找方式找到數據。

    場景3 :用ID和SomeString列作為謂詞使用等于號進行過濾

     這個場景就有點意思了。只能使用部分使用ID作為索引查找條件，因為SomeString列并不是索引的第二列。這個索引的第二列是SomeDate但查詢沒有按照SomeDate進行過濾。因此這個查詢首先使用ID進行過濾，然后過濾后的列進行字符串比較來找到匹配的行。雖然這個操作是通過查找實現，但SQL Server僅僅使用查找找到ID，然后再將過濾后的行進行字符串比較。

    場景4：用SomeDate和SomeString列作為謂詞使用等于號進行過濾

    在這個場景中，SQL Server就不能使用查找了。索引的第一列并不包含在這個查詢的謂詞之內。只能通過掃描來滿足這個查詢。實際上，SQL Server需要將表中這兩列的每一行都和給定值進行比較來找到所需的行。

    我覺得上述知識已經基本涵蓋了有關索引的等式謂詞。或許我這篇文章讓你更迷惑了。但是至少我希望你更好的了解索引以及等號匹配。

原文鏈接:http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/

Translated by CareySon

本文PDF可以點擊這里下載