解析索引中數(shù)據(jù)列順序的選擇問(wèn)題
在多個(gè)列上面建立索引的時(shí)候，我們常常會(huì)遇到這樣的一個(gè)問(wèn)題“需要把哪個(gè)列放在前面”，因?yàn)樗饕辛许樞虻牟煌?，?huì)對(duì)索引的使用，以至性能產(chǎn)生很大的影響。我們本篇就來(lái)分析這個(gè)問(wèn)題。
對(duì)于上面的問(wèn)題，一個(gè)常見(jiàn)的回答就是“把選擇性***列放在前面”，這里為了使得后面的講述順序進(jìn)行，我們先來(lái)解釋一下選擇性的含義。選擇性是用來(lái)描述數(shù)據(jù)的差異情況的，例如，如果一個(gè)表中有1000條數(shù)據(jù)，其中的某個(gè)字段，如ID，如果每一條數(shù)據(jù)的ID值都不一樣，那么ID的選擇性就是1；如果其中有300百個(gè)ID是一樣的，那么就是說(shuō)，有700個(gè)ID不同，那么選擇性就是70%。很顯然，數(shù)據(jù)的選擇性越高，那么在上面建立索引效果就越好。
下面，我們就來(lái)解釋一下為什么在多個(gè)列上面建立索引的時(shí)候需要把選擇性高的列放在最前面。
也許有朋友聽(tīng)到上面的建議之后，在建立任何基于多個(gè)列的索引的時(shí)候，都會(huì)把表的聚集索引所在的列作為這個(gè)多列索引的***個(gè)字段。例如，假設(shè)現(xiàn)在表中有4個(gè)字段，ID,Name,Age，BirthDate，其中ID是主鍵，也是聚集索引，現(xiàn)在我們需要在Name,BirthDate上面建立索引，這個(gè)時(shí)候，有朋友發(fā)現(xiàn)：ID的選擇性***，那么把ID放在新的索引中，勢(shì)必會(huì)更好，于是一個(gè)名字為IX_Index的索引就包含了三個(gè)列：ID,Name，BirthDate。到后來(lái)，可能就發(fā)現(xiàn)，如果冒冒然的這樣做，使得這個(gè)新建的索引沒(méi)有發(fā)揮作用，反而導(dǎo)致性能問(wèn)題。
對(duì)于數(shù)據(jù)庫(kù)中的每一個(gè)索引，都會(huì)有相應(yīng)的統(tǒng)計(jì)數(shù)據(jù)信息，這個(gè)統(tǒng)計(jì)數(shù)據(jù)顯示了數(shù)據(jù)的分布情況，統(tǒng)計(jì)信息以一個(gè)類似柱形的形式表現(xiàn)了數(shù)據(jù)的分布。數(shù)據(jù)庫(kù)只把索引中的***個(gè)列的數(shù)據(jù)分布情況放在柱形圖中，換句話說(shuō)，這個(gè)統(tǒng)計(jì)信息顯示的就是索引中的***個(gè)數(shù)據(jù)列的數(shù)據(jù)分布情況（這里面涉及到的內(nèi)容有點(diǎn)深，大家可以關(guān)注本站點(diǎn)的“查詢優(yōu)化器內(nèi)核系列”，里面會(huì)講述到）。
我給大家看個(gè)例子吧，假設(shè)在SalesOrderDetail表上面有一個(gè)索引：X_SalesOrderDetail_ProductID，運(yùn)行下面的語(yǔ)句：
這個(gè)索引包含的列有：ProductID，SalesOrderID和SalesOrderDetailID。我們查看它的數(shù)據(jù)的柱形分布圖，如下：
我們發(fā)現(xiàn)，其中的RANGE_HI_KEY列出的就是ProductID的值，通過(guò)圖中，我們可以知道：ProductID值為826的數(shù)據(jù)有305條，值為831的數(shù)據(jù)有198條。ProductID的值在826到831之間的數(shù)據(jù)有110條。查詢優(yōu)化器就是根據(jù)這個(gè)來(lái)估算數(shù)據(jù)的條數(shù)的。
通過(guò)上面可以知道：把索引中的哪個(gè)列放在前面至關(guān)重要，如果把一個(gè)選擇性很低的列放在前面，那么就導(dǎo)致索引的統(tǒng)計(jì)數(shù)據(jù)顯示的數(shù)據(jù)分布完全改變，可能導(dǎo)致查詢優(yōu)化器選擇比較低效的執(zhí)行計(jì)劃。
下面，我們就通過(guò)一個(gè)例子來(lái)進(jìn)一步的看看這個(gè)問(wèn)題。
首先，建立一個(gè)測(cè)試的表，如下：
這個(gè)表中有10000條數(shù)據(jù)，并且這個(gè)表是一個(gè)堆表，即沒(méi)有聚集索引的表。并且在這個(gè)表中有100個(gè)不同的SomeString值，有5000個(gè)不同的SomeDate值，而ID是唯一的，全部都不同。
那么，上面的值的選擇性如下：
|
字段名 |
選擇性 |
|
ID |
100% |
|
SomeString |
100/10000*100%=1% |
|
SomeDate |
5000/10000*100%=50% |
在表中，有一個(gè)非聚集索引，假設(shè)名字為Idx_test，包含了表中的三個(gè)值，三個(gè)列在索引中的順序?yàn)椋篒D,SomeDate，SomeString，按照選擇性排序，確實(shí)不錯(cuò)！
- … WHERE ID = @ID AND SomeDate = @dt AND SomeString = @str
- … WHERE ID = @ID AND SomeDate = @dt
- … WHERE ID = @ID
換句話說(shuō)，就是這個(gè)索引只在查詢中的Where/Join的列按照索引中的列的順序使用的時(shí)候才有效。如果查詢是這樣的，如下：
對(duì)于上面的索引，只有在類似下面的查詢結(jié)構(gòu)中發(fā)揮作用，如下：
- … WHERE SomeDate = @dt或者… SomeDate = @dt AND SomeString = @str
那么，這個(gè)索引就不會(huì)上面的查詢中使用了，那么查詢?cè)趫?zhí)行的時(shí)候就會(huì)掃描整表了。
我們通過(guò)執(zhí)行計(jì)劃來(lái)看看是不是這樣的。
對(duì)于，WHERE ID = @ID的查詢，執(zhí)行計(jì)劃如下：
很顯然，執(zhí)行了Seek操作，是很快的。
對(duì)于WHERE ID = @ID AND SomeDate = @dt的查詢，執(zhí)行計(jì)劃如下：
還是進(jìn)行了Seek操作。
那么對(duì)于… SomeDate = @dt AND SomeString = @str的查詢，如下：
大家可以看到，這個(gè)時(shí)候已經(jīng)開(kāi)始進(jìn)行全表掃描了。
我們本篇講述了在索引的進(jìn)行列的相等操作時(shí)候，列的順序問(wèn)題，我們下一篇就講述如果是在列上進(jìn)行不等操作，例如ID>1，那么索引中的列的順序還是這樣進(jìn)行嗎？
原文鏈接：http://www.cnblogs.com/yanyangtian/archive/2012/05/03/2480052.html
【編輯推薦】
