【譯】SQL Server索引進(jìn)階第二篇：深入非聚集索引

SQL Server索引進(jìn)階第二篇：深入非聚集索引

    索引設(shè)計(jì)是數(shù)據(jù)庫(kù)設(shè)計(jì)中比較重要的一個(gè)環(huán)節(jié)，對(duì)數(shù)據(jù)庫(kù)的性能其中至關(guān)重要的作用，但是索引的設(shè)計(jì)卻又不是那么容易的事情，性能也不是那么輕易就獲取到的，很多的技術(shù)人員因?yàn)椴磺‘?dāng)?shù)膭?chuàng)建索引，最后使得其效果適得其反，可以說(shuō)“成也索引，敗也索引”。

    本系列文章來(lái)自Stairway to SQL Server Indexes

    本系列文章的第一篇介紹了SQL Server的索引，尤其重點(diǎn)介紹了非聚集索引，在我們的第一個(gè)例子中展示了使用非聚集索引從一個(gè)表中取得一行數(shù)據(jù)所帶來(lái)的潛在好處。在本篇文章中，我們繼續(xù)研究非聚集索引，本篇文章所研究的內(nèi)容就要比使用非聚集索引在單表中查詢一行所帶來(lái)的性能提升更深一步了。

       本系列文章將要列舉的一些例子中介紹的部分理論是關(guān)于是非聚集索引的理論，并通過(guò)探究索引的內(nèi)部結(jié)構(gòu)來(lái)幫助更好的理解這些理論，在此基礎(chǔ)上，我們分別在存在索引和不存在索引的情況下分別執(zhí)行相同的查詢并通過(guò)統(tǒng)計(jì)數(shù)據(jù)來(lái)比較性能。因此我們就可以體會(huì)到索引帶來(lái)的影響了。

       我們繼續(xù)使用在第一篇文章中曾使用過(guò)的AdventureWorks內(nèi)的部分?jǐn)?shù)據(jù)。尤其是Contact表，我們僅僅使用一個(gè)我們?cè)谏掀闹兄惺褂眠^(guò)的FullName索引。為了更好的測(cè)試非聚集索引帶來(lái)的影響，我將建兩個(gè)Contact表，其中一個(gè)存在FullName非聚集索引，而另一個(gè)不存在。總之就是，兩個(gè)相同的表，一個(gè)表中存在非聚集索引，另一個(gè)不存在非聚集索引。

      注意:本篇文章中的T-SQL代碼都可以在文章底部找到下載鏈接

      列表1所示代碼創(chuàng)建了Person.Contact表的副本,如果你想恢復(fù)到初始測(cè)試狀態(tài)，你可以隨時(shí)運(yùn)行這段代碼。

IF EXISTS ( SELECT  * 
            FROM    sys.tables 
            WHERE   OBJECT_ID = OBJECT_ID('dbo.Contacts_index') ) 
    DROP TABLE dbo.Contacts_index ; 
        GO 
IF EXISTS ( SELECT  * 
            FROM    sys.tables 
            WHERE   OBJECT_ID = OBJECT_ID('dbo.Contacts_noindex') ) 
    DROP TABLE dbo.Contacts_noindex ; 
    GO 
SELECT  * 
INTO    dbo.Contacts_index 
FROM    Person.Contact ; 
SELECT  * 
INTO    dbo.Contacts_noindex 
FROM    Person.Contact ;

代碼2.1:制作Person.Contact 表的副本

Contacts表內(nèi)的部分?jǐn)?shù)據(jù)如下所示：

非聚集索引條目

    如下代碼段在Contacts_index表上創(chuàng)建名為FullName的非聚集索引.

CREATE INDEX FullName 
ON Contacts_index 
( LastName, FirstName ) ; 

    代碼段2.2 -創(chuàng)建非聚集索引

    不要忘了非聚集索引按順序存儲(chǔ)索引鍵。就像書簽可以用于直接訪問(wèn)表中的數(shù)據(jù)那樣，你也可以把書簽想象成一種指針，在接下來(lái)的文章中我們將更詳細(xì)的討論書簽的組成和用法。

     FullName索引的部分?jǐn)?shù)據(jù)如下，由LastName和FirstName作為索引鍵外加一個(gè)書簽組成。

     每一個(gè)條目都由索引鍵列和書簽值組成。除此之外，SQL Server中非聚集索引條目還包含了一些可選的頭數(shù)據(jù)，用于SQL Server內(nèi)部使用。這兩個(gè)非聚集索引條目的組成部分對(duì)于理解非聚集索引基礎(chǔ)并不重要，所以安排在后續(xù)系列文章中進(jìn)行講解。

索引條目有序帶來(lái)的好處

    索引條目按照索引鍵的值有序排列，這樣SQL Server就能快速有序的遍歷索引條目。對(duì)于有序的條目的掃描操作既可以從頭到尾，也可以從尾到頭，甚至是從任意條目開(kāi)始。

    因此，如果一個(gè)查詢請(qǐng)求想要得到contacts表中l(wèi)astname以字母s開(kāi)頭的數(shù)據(jù)（WHERE LastName LIKE 'S%'），SQL Server會(huì)直接找到第一條s開(kāi)頭的條目(“Sabella, Deanna”)并以此為出發(fā)點(diǎn)進(jìn)行掃描，直到掃描到第一條以T開(kāi)頭的條目，這時(shí)SQL Server就知道s開(kāi)頭的條目全部掃描完成，然后使用書簽訪問(wèn)數(shù)據(jù)。

    上面的請(qǐng)求的列中如果僅僅包含在非聚集索引中，那么這個(gè)查詢會(huì)執(zhí)行的更快

SELECT  FirstName , 
        LastName 

FROM    Contact 
WHERE   LastName LIKE 'S%' ;

    SQL Server可以快速的導(dǎo)航到第一條S開(kāi)頭的條目，然后在無(wú)視書簽的情況下遍歷索引條目并直接從索引中取得數(shù)據(jù)，直到遇到T開(kāi)頭的索引條目時(shí)掃描結(jié)束。在關(guān)系數(shù)據(jù)庫(kù)的術(shù)語(yǔ)中，這個(gè)索引“覆蓋”了查詢請(qǐng)求。

    任何由順序數(shù)據(jù)給SQL操作帶來(lái)的好處也可以同樣由索引帶來(lái)，這些操作包括 ORDER BY, GROUP BY, DISTINCT, UNION (不是UNION ALL), 以及JOIN…ON。

    比如，SQL Server使用聚合函數(shù)Count根據(jù)LastName列來(lái)查詢Contact表有多少個(gè)聯(lián)系人。就像前面的例子一樣，這是一個(gè)覆蓋索引，SQL Server無(wú)視了Contact表，僅僅從索引取得所需數(shù)據(jù)。

    值得注意的是鍵列的順序是由左到右的。也就是說(shuō)前面所建的FullName索引如果按照非聚集索引鍵第一列LastName列作為查詢條件時(shí)將會(huì)非常有用，而如果以FirstName作為查詢條件或許起的作用就不是那么大了。

測(cè)試示例查詢

    如果你想執(zhí)行下面的查詢語(yǔ)句，請(qǐng)確保你首先按照前面的代碼創(chuàng)建了dbo.Contacts_index 和dbo.Contacts_noindex這兩個(gè)表,并創(chuàng)建了順序?yàn)長(zhǎng)astName, FirstName 的非聚集索引dbo.Contacts_index.

    為了證實(shí)我前面的理論，我通過(guò)下面代碼開(kāi)啟統(tǒng)計(jì)數(shù)據(jù)并在有非聚集索引和沒(méi)有非聚集索引存在的情況下執(zhí)行相同的數(shù)據(jù)。

SET STATISTICS io ON 
SET STATISTICS time ON

    因?yàn)锳dventureWorks數(shù)據(jù)庫(kù)的Contact表中僅有19972行數(shù)據(jù)，因此很難從時(shí)間統(tǒng)計(jì)中看出倪端，測(cè)試的大部分執(zhí)行語(yǔ)句CPU時(shí)間都是0，因此就不再顯示CPU時(shí)間了。僅僅顯示能反映出可能讀取的頁(yè)數(shù)的IO統(tǒng)計(jì)。這個(gè)值可以幫助我們對(duì)比同樣查詢?cè)诖嬖诜蔷奂饕筒淮嬖诜蔷奂饕那闆r下比較同樣查詢語(yǔ)句的性能。如果你想做一些更接近實(shí)際的CPU時(shí)間測(cè)試，在文章的末尾可以找到百萬(wàn)級(jí)別的Contact表的創(chuàng)建代碼。下面的結(jié)論僅僅是針對(duì)標(biāo)準(zhǔn)的19972行的Contact表。

測(cè)試查詢覆蓋

    第一條查詢是取出Contact表中LastName列以S開(kāi)頭的數(shù)據(jù)，僅僅取出FirstName列和LastName列，因此可以被索引覆蓋，如表2.1所示。

表2.1 運(yùn)行覆蓋查詢后的執(zhí)行結(jié)果

測(cè)試非覆蓋查詢

接下來(lái)，和上面語(yǔ)句類似，只是執(zhí)行語(yǔ)句中包含的列更多了，查詢的執(zhí)行信息如表2.2所示

表2.1 運(yùn)行非覆蓋查詢后的執(zhí)行結(jié)果

測(cè)試非覆蓋索引，但是選擇更少的列

表2.3 執(zhí)行返回更少的數(shù)據(jù)的非覆蓋查詢后的執(zhí)行結(jié)果

測(cè)試聚合覆蓋查詢

    我們最后的例子是聚合查詢，也就是查詢中使用了聚合。下面的查詢根據(jù)LastName和FirstName進(jìn)行分組來(lái)找到姓名完全相同的人.

部分查詢結(jié)果如下:

Steel         Merrill      1
Steele       Joan         1
Steele       Laura        2
Steelman Shanay     1
Steen        Heidi        2
Stefani      Stefano    1
Steiner      Alan         1

查詢執(zhí)行的詳細(xì)信息見(jiàn)表2.4

表2.4 覆蓋聚集查詢的執(zhí)行結(jié)果

測(cè)試非覆蓋聚合查詢

我們?cè)谏厦娌樵兊幕A(chǔ)上加入索引中不包含的列，得到了表2.5中的測(cè)試數(shù)據(jù)

表2.5  運(yùn)行非覆蓋聚合查詢后的執(zhí)行結(jié)果

總結(jié)

    我們知道非聚集索引有如下特點(diǎn)，非聚集索引是

•     實(shí)體的有序集合
•     每一行都有一個(gè)對(duì)應(yīng)所在表的入口指針
•     包含索引鍵和書簽
•     由用戶創(chuàng)建
•     由SQL Server維護(hù)
•     被SQL Server用來(lái)減少查詢所付出的代價(jià)

    目前為止，文中所演示的查詢既可以僅僅索引獲取數(shù)據(jù)，也可以僅僅通過(guò)表獲取，又或者是二者結(jié)合。

    當(dāng)一個(gè)查詢被傳到數(shù)據(jù)引擎時(shí)，SQL Server可以通過(guò)三種路徑獲取數(shù)據(jù)來(lái)滿足這個(gè)查詢。

•     不需要訪問(wèn)表僅需要訪問(wèn)索引本身，這種情況必須是索引覆蓋了請(qǐng)求所包含的列
•     使用索引鍵值去訪問(wèn)非聚集索引，然后使用書簽訪問(wèn)非聚集索引所在表
•     無(wú)視非聚集索引掃描基本表來(lái)獲取數(shù)據(jù)

     通常情況下，第一種方式是最理想的，第二種方式好于第三種。在接下的文章中我會(huì)講述如何提高索引覆蓋的概率以及如何知道指定非覆蓋查詢?nèi)绾螐姆蔷奂饕@益。但是這需要對(duì)索引的內(nèi)部的結(jié)構(gòu)有更深入的了解，這已經(jīng)超越了本篇文章的內(nèi)容。

   在更深入的了解索引的內(nèi)部結(jié)構(gòu)之前，讓我們首先介紹另一種SQL Server的索引，也就是聚集索引，這將會(huì)在本系列的第三篇文章中進(jìn)行介紹。