SQL查詢優(yōu)化詳解SQL Server非聚集索引

只看樓主\o"倒序閱讀"倒序閱讀使用道具0

發(fā)表于:2011-12-29在SQL

SERVER中，非聚集索引其實可以看作是一個含有聚集索引的表.但相比實際的表而言.非聚集索引中所存儲的表的列數(shù)要窄很多，因為非聚集索引僅僅包含原表中非聚集索引的列和指向?qū)嶋H物理表的指針。

并且，對于非聚集索引表來說，其中所存放的列是按照聚集索引來進行存放的.所以查找速度要快了很多。但是對于性能的榨取來說，SQLSERVER總是竭盡所能，假如僅僅是通過索引就可以在B樹的葉子節(jié)點獲取所需數(shù)據(jù)，而不再用通過葉子節(jié)點上的指針去查找實際的物理表,那性能的提升將會更勝一籌.

下面我們來看下實現(xiàn)這一點的幾種方式.

非聚集索引的覆蓋

正如前面簡介所說。非聚集索引其實可以看作一個聚集索引表.當這個非聚集索引中包含了查詢所需要的所有信息時，則查詢不再需要去查詢基本表，而僅僅是從非聚集索引就能得到數(shù)據(jù):

下面來看非聚集索引如何覆蓋的:

在adventureWorks的SalesOrderHeader表中，現(xiàn)在只有CustomerID列有非聚集索引，而BillToAddressID沒有索引，我們的查詢計劃會是這樣:

查詢會根據(jù)CustomerID列上的非聚集索引找到相應的指針后，去基本表上查找數(shù)據(jù).從執(zhí)行計劃可以想象，這個效率并不快。

下面我們來看覆蓋索引，通過在CustomerID和BillToAddressID上建立非聚集索引，我們覆蓋到了上面查詢語句的所有數(shù)據(jù):

通過覆蓋索引，可以看到執(zhí)行計劃簡單到不能再簡單，直接從非聚集索引的葉子節(jié)點提取到數(shù)據(jù)，無需再掃描基本表!

這個性能的提升可以從IO統(tǒng)計看出來,下面我們來看有覆蓋索引和沒有覆蓋索引的IO對比:

索引的覆蓋不僅僅帶來的是效率的提升，還有并發(fā)的提升，因為減少了對基本表的依賴，所以提升了并發(fā)，從而減少了死鎖!

理解INCLUDE的魔力

上面的索引覆蓋所帶來的效率提升就像魔術一樣，但別著急，正如我通篇強調(diào)的一樣，everythinghasprice.如果一個索引包含了太多的鍵的話，也會帶來很多副作用。INCLUDE的作用使得非聚集索引中可以包含更多的列，但不作為“鍵”使用。

比如:假設我們上面的那個查詢需要增加一列，則原來建立的索引無法進行覆蓋，從而還需要查找基本表:

但是如果要包含SubTotal這個總金額，則索引顯得太寬，因為我們的業(yè)務很少根據(jù)訂單價格作為查詢條件,則使用INCLUDE建立索引:

理解INCLUDE包含的列和索引建立的列可以這樣理解,把上述建立的含有INCLUDE的非聚集索引想像成:

使用INCLUDE可以減少葉子“鍵”的大小!

非聚集索引的交叉

非聚集索引的交叉看以看作是覆蓋索引的擴展!

由于很多原因，比如：

在生產(chǎn)環(huán)境中，我們往往不能像上面建立覆蓋索引那樣隨意改動現(xiàn)有索引，這可能導致的結(jié)果是你會更頻繁的被客戶打電話“關照”

現(xiàn)有的非聚集索引已經(jīng)很“寬”，你如果繼續(xù)拓寬則增改查帶來的性能下降的成本會高過提高查詢帶來的好處

這時候，你可以通過額外建立索引。正如我前面提到的，非聚集索引的本質(zhì)是表，通過額外建立表使得幾個非聚集索引之間進行像表一樣的Join,從而使非聚集索引之間可以進行Join來在不訪問基本表的情況下給查詢優(yōu)化器提供所需要的數(shù)據(jù):

比如還是上面的那個例子.我們需要查取SalesOrderHeader表,通過BillToAddressID,CustomerID作為選擇條件，可以通過建立兩個索引進行覆蓋,下面我們來看執(zhí)行計劃:

非聚集索引的連接

非聚集索引的連接實際上是非聚集索引的交叉的一種特例。使得多個非聚集索引交叉后可以覆蓋所要查詢的數(shù)據(jù)，從而使得從減少查詢基本表變成了完全不用查詢基本表:

比如還是上面那兩個索引，這時我只查詢非聚集索引中包含的數(shù)據(jù)，則完全不再需要查詢基本表:

看起來這樣的查詢意義不大?但當你把查詢條件變?yōu)?lt;號時呢?或者給定范圍時。還是有一定實際意義的。

非聚集索引的過濾

很多時候，我們并不需要將基本表中索引列的所有數(shù)據(jù)全部索引，比如說含有NULL的值不希望被索引，或者根據(jù)具體的業(yè)務場景，有一些數(shù)據(jù)我們不想索引。這樣可以:

減少索引的大小

索引減少了，從而使得對索引的查詢得到了加速

小索引對于增刪改的維護性能會更高

比如說,如下語句:

我們?yōu)槠浣⒕奂饕?

這時我們?yōu)槠浼由线^濾條件，形成過濾索引:

由上面我們可以看出，使用過濾索引的場景要和具體的業(yè)務場景相關，對于為大量相同的查詢條件建立過濾索引使得性能進一步提升!

總結(jié)

本文從介紹了SQL

SERVER中非聚集索引的覆蓋，連接，交叉和過濾。對于我們每一點從SQLSERVE