理解SQL SERVER中非聚集索引的覆盖，连接，交叉和过滤
寫在前面:這是第一篇T-SQL查詢高級(jí)系列文章.但是T-SQL查詢進(jìn)階系列還遠(yuǎn)遠(yuǎn)沒有寫完。這個(gè)主題放到高級(jí)我想是因?yàn)檫@個(gè)主題需要一些進(jìn)階的知識(shí)作為基礎(chǔ)..如果文章中有錯(cuò)誤的地方請(qǐng)不吝指正.本篇文章的內(nèi)容需要索引的知識(shí)作為基礎(chǔ)。
簡(jiǎn)介
??? 在SQL SERVER中，非聚集索引其實(shí)可以看作是一個(gè)含有聚集索引的表.但相比實(shí)際的表而言.非聚集索引中所存儲(chǔ)的表的列數(shù)要窄很多，因?yàn)榉蔷奂饕齼H僅包含原表中非聚集索引的列和指向?qū)嶋H物理表的指針。
???
??? 并且，對(duì)于非聚集索引表來(lái)說(shuō)，其中所存放的列是按照聚集索引來(lái)進(jìn)行存放的.所以查找速度要快了很多。但是對(duì)于性能的榨取來(lái)說(shuō)，SQL SERVER總是竭盡所能，假如僅僅是通過(guò)索引就可以在B樹的葉子節(jié)點(diǎn)獲取所需數(shù)據(jù)，而不再用通過(guò)葉子節(jié)點(diǎn)上的指針去查找實(shí)際的物理表,那性能的提升將會(huì)更勝一籌.
??? 下面我們來(lái)看下實(shí)現(xiàn)這一點(diǎn)的幾種方式.
非聚集索引的覆蓋
??? 正如前面簡(jiǎn)介所說(shuō)。非聚集索引其實(shí)可以看作一個(gè)聚集索引表.當(dāng)這個(gè)非聚集索引中包含了查詢所需要的所有信息時(shí)，則查詢不再需要去查詢基本表，而僅僅是從非聚集索引就能得到數(shù)據(jù):
??
?? 下面來(lái)看非聚集索引如何覆蓋的:
?? 在adventureWorks的SalesOrderHeader表中，現(xiàn)在只有CustomerID列有非聚集索引，而BillToAddressID沒有索引，我們的查詢計(jì)劃會(huì)是這樣:
??
?? 查詢會(huì)根據(jù)CustomerID列上的非聚集索引找到相應(yīng)的指針后，去基本表上查找數(shù)據(jù).從執(zhí)行計(jì)劃可以想象，這個(gè)效率并不快。
?? 下面我們來(lái)看覆蓋索引，通過(guò)在CustomerID和BillToAddressID上建立非聚集索引，我們覆蓋到了上面查詢語(yǔ)句的所有數(shù)據(jù):
???
??? 通過(guò)覆蓋索引，可以看到執(zhí)行計(jì)劃簡(jiǎn)單到不能再簡(jiǎn)單，直接從非聚集索引的葉子節(jié)點(diǎn)提取到數(shù)據(jù)，無(wú)需再查找基本表！
??? 這個(gè)性能的提升可以從IO統(tǒng)計(jì)看出來(lái),下面我們來(lái)看有覆蓋索引和沒有覆蓋索引的IO對(duì)比:
???
??? 索引的覆蓋不僅僅帶來(lái)的是效率的提升，還有并發(fā)的提升，因?yàn)闇p少了對(duì)基本表的依賴，所以提升了并發(fā)，從而減少了死鎖！
理解INCLUDE的魔力
??? 上面的索引覆蓋所帶來(lái)的效率提升就像魔術(shù)一樣，但別著急，正如我通篇強(qiáng)調(diào)的一樣，everything has price.如果一個(gè)索引包含了太多的鍵的話，也會(huì)帶來(lái)很多副作用。INCLUDE的作用使得非聚集索引中可以包含更多的列，但不作為“鍵”使用。
??? 比如:假設(shè)我們上面的那個(gè)查詢需要增加一列，則原來(lái)建立的索引無(wú)法進(jìn)行覆蓋，從而還需要查找基本表:
???
??? 但是如果要包含SubTotal這個(gè)總金額，則索引顯得太寬，因?yàn)槲覀兊臉I(yè)務(wù)很少根據(jù)訂單價(jià)格作為查詢條件,則使用INCLUDE建立索引:
???
??? 理解INCLUDE包含的列和索引建立的列可以這樣理解,把上述建立的含有INCLUDE的非聚集索引想像成:
????
???? 使用INCLUDE可以減少葉子“鍵”的大小!
非聚集索引的交叉
??? 非聚集索引的交叉看以看作是覆蓋索引的擴(kuò)展！
???? 由于很多原因，比如：
- ??? 在生產(chǎn)環(huán)境中，我們往往不能像上面建立覆蓋索引那樣隨意改動(dòng)現(xiàn)有索引，這可能導(dǎo)致的結(jié)果是你會(huì)更頻繁的被客戶打電話“關(guān)照”
- ??? 現(xiàn)有的非聚集索引已經(jīng)很“寬”，你如果繼續(xù)拓寬則增改查帶來(lái)的性能下降的成本會(huì)高過(guò)提高查詢帶來(lái)的好處
???? 這時(shí)候，你可以通過(guò)額外建立索引。正如我前面提到的，非聚集索引的本質(zhì)是表，通過(guò)額外建立表使得幾個(gè)非聚集索引之間進(jìn)行像表一樣的Join,從而使非聚集索引之間可以進(jìn)行Join來(lái)在不訪問基本表的情況下給查詢優(yōu)化器提供所需要的數(shù)據(jù):
??? 比如還是上面的那個(gè)例子.我們需要查取SalesOrderHeader表,通過(guò)BillToAddressID,CustomerID作為選擇條件，可以通過(guò)建立兩個(gè)索引進(jìn)行覆蓋,下面我們來(lái)看執(zhí)行計(jì)劃:
??
非聚集索引的連接
???? 非聚集索引的連接實(shí)際上是非聚集索引的交叉的一種特例。使得多個(gè)非聚集索引交叉后可以覆蓋所要查詢的數(shù)據(jù)，從而使得從減少查詢基本表變成了完全不用查詢基本表:
???? 比如還是上面那兩個(gè)索引，這時(shí)我只查詢非聚集索引中包含的數(shù)據(jù)，則完全不再需要查詢基本表:
????
非聚集索引的過(guò)濾
??? 很多時(shí)候，我們并不需要將基本表中索引列的所有數(shù)據(jù)全部索引，比如說(shuō)含有NULL的值不希望被索引，或者根據(jù)具體的業(yè)務(wù)場(chǎng)景，有一些數(shù)據(jù)我們不想索引。這樣可以:
- ??? 減少索引的大小
- ??? 索引減少了，從而使得對(duì)索引的查詢得到了加速
- ??? 小索引對(duì)于增刪改的維護(hù)性能會(huì)更高
??? 比如說(shuō),如下語(yǔ)句:
???
??? 我們?yōu)槠浣⒕奂饕?
???
??? 這時(shí)我們?yōu)槠浼由线^(guò)濾條件，形成過(guò)濾索引:
???
??? 由上面我們可以看出，使用過(guò)濾索引的場(chǎng)景要和具體的業(yè)務(wù)場(chǎng)景相關(guān)，對(duì)于為大量相同的查詢條件建立過(guò)濾索引使得性能進(jìn)一步提升！
總結(jié)
??? 本文從介紹了SQL SERVER中非聚集索引的覆蓋，連接，交叉和過(guò)濾。對(duì)于我們每一點(diǎn)從SQL SERVER榨取的性能的提升往往會(huì)伴隨著另一方面的犧牲。作為數(shù)據(jù)庫(kù)的開發(fā)人員或者管理人員來(lái)說(shuō)，以全面的知識(shí)來(lái)做好權(quán)衡將會(huì)是非常重要.系統(tǒng)的學(xué)習(xí)數(shù)據(jù)庫(kù)的知識(shí)不但能大量減少邏輯讀的數(shù)據(jù)，也能減少客戶打電話"關(guān)照”的次數(shù):-)
總結(jié)
以上是生活随笔為你收集整理的理解SQL SERVER中非聚集索引的覆盖，连接，交叉和过滤的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 理解SQL SERVER中的分区表
- 下一篇： 天上的街市原文朗诵（天上的街市原文）