SQL Server索引的示例分析

小編給大家分享一下SQL Server索引的示例分析，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

前言

此文是我之前的筆記整理而來(lái)，以索引為入口進(jìn)行探討相關(guān)數(shù)據(jù)庫(kù)知識(shí)（又做了修改以讓人更好消化）。SQL Server接觸不久的朋友可以只看以下藍(lán)色字體字，簡(jiǎn)單有用節(jié)省時(shí)間；如果是數(shù)據(jù)庫(kù)基礎(chǔ)不錯(cuò)的朋友，可以全看，歡迎探討。

索引的概念

索引的用途：我們對(duì)數(shù)據(jù)查詢及處理速度已成為衡量應(yīng)用系統(tǒng)成敗的標(biāo)準(zhǔn)，而采用索引來(lái)加快數(shù)據(jù)處理速度通常是最普遍采用的優(yōu)化方法。

索引是什么：數(shù)據(jù)庫(kù)中的索引類似于一本書(shū)的目錄，在一本書(shū)中使用目錄可以快速找到你想要的信息，而不需要讀完全書(shū)。在數(shù)據(jù)庫(kù)中，數(shù)據(jù)庫(kù)程序使用索引可以重啊到表中的數(shù)據(jù)，而不必掃描整個(gè)表。書(shū)中的目錄是一個(gè)字詞以及各字詞所在的頁(yè)碼列表，數(shù)據(jù)庫(kù)中的索引是表中的值以及各值存儲(chǔ)位置的列表。

索引的利弊：查詢執(zhí)行的大部分開(kāi)銷是I/O，使用索引提高性能的一個(gè)主要目標(biāo)是避免全表掃描，因?yàn)槿頀呙栊枰獜拇疟P(pán)上讀取表的每一個(gè)數(shù)據(jù)頁(yè)，如果有索引指向數(shù)據(jù)值，則查詢只需要讀少數(shù)次的磁盤(pán)就行啦。所以合理的使用索引能加速數(shù)據(jù)的查詢。但是索引并不總是提高系統(tǒng)的性能，帶索引的表需要在數(shù)據(jù)庫(kù)中占用更多的存儲(chǔ)空間，同樣用來(lái)增刪數(shù)據(jù)的命令運(yùn)行時(shí)間以及維護(hù)索引所需的處理時(shí)間會(huì)更長(zhǎng)。所以我們要合理使用索引，及時(shí)更新去除次優(yōu)索引。

1.聚集索引和非聚集索引

索引分為聚集索引和非聚集索引

1.1 聚集索引

表的數(shù)據(jù)是存儲(chǔ)在數(shù)據(jù)頁(yè)中（數(shù)據(jù)頁(yè)的PageType標(biāo)記為1），SqlServer一頁(yè)是8k，存滿一頁(yè)就開(kāi)辟下一頁(yè)存儲(chǔ)。如果表有聚集索引，那么一筆一筆物理數(shù)據(jù)就是按聚集索引字段的大小升/降排序存儲(chǔ)在頁(yè)中。當(dāng)對(duì)聚集索引字段更新或中間插入/刪除數(shù)據(jù)時(shí)，都會(huì)導(dǎo)致表數(shù)據(jù)移動(dòng)（造成性能一定影響），因?yàn)樗３稚?降排序。

注意，主鍵只是默認(rèn)是聚集索引，它也可以設(shè)置為非聚集索引，也可以在非主鍵字段上設(shè)置為聚集索引，全表只能有一個(gè)聚集索引。

一個(gè)優(yōu)秀的聚集索引字段一般包含以下4個(gè)特性：

(A).自增長(zhǎng)

總是在末尾增加記錄，減少分頁(yè)和索引碎片。

(B).不被更改

減少數(shù)據(jù)移動(dòng)。

(C).唯一性

唯一性是任何索引最理想的特性，可以明確索引鍵值在排序中的位置。

更重要的是，索引鍵指唯一的話，它在每條記錄里才可以正確指向源數(shù)據(jù)行RID。如果聚集索引鍵值不唯一，SqlServer就需要內(nèi)部生成uniquifier 列組合當(dāng)作聚集鍵保證“鍵值”唯一性；如果非聚集索引鍵值不唯一，就會(huì)增加RID列（聚集索引鍵或者堆表中的行指針）保證“鍵值”唯一性。

思考（可略過(guò)）：索引“鍵值”在非葉子節(jié)點(diǎn)也有保證唯一性，原因應(yīng)該是為了明確索引記錄在非葉子節(jié)點(diǎn)中的位置。比如有個(gè)非聚集索引字段Name2，表中有很多Name2='a'的記錄，導(dǎo)致Name2='a'在非葉子節(jié)點(diǎn)上有多條索引記錄（節(jié)點(diǎn)），這時(shí)候再insert一筆Name2=‘a(chǎn)'的記錄時(shí)，就可以根據(jù)非葉子節(jié)點(diǎn)的RID和新增記錄的RID很快確定要insert到哪個(gè)索引記錄（節(jié)點(diǎn)）上，如果沒(méi)有非葉子節(jié)點(diǎn)的RID，那得遍歷到所有Name2='a'的葉子節(jié)點(diǎn)才能確定位置。另外，當(dāng)我們select * from Table1 where Name2<='a'時(shí)，返回的數(shù)據(jù)是按非聚集索引Name2和RID排序的，很好理解返回的數(shù)據(jù)就是按這邊索引存儲(chǔ)的順序排序的。這是這條sql查詢時(shí)有用到Name2索引的結(jié)果，如果數(shù)據(jù)庫(kù)查詢計(jì)劃因“臨界點(diǎn)”問(wèn)題選擇直接表數(shù)據(jù)掃描，那返回的數(shù)據(jù)默認(rèn)就是按表數(shù)據(jù)的順序排序的。

為了“鍵值”唯一性，對(duì)于聚集索引，uniquifier 列只在索引值重復(fù)時(shí)增加。對(duì)于非聚集索引，如果創(chuàng)建索引時(shí)沒(méi)定義唯一，RID會(huì)在所有記錄增加，就算索引值是唯一的；如果創(chuàng)建索引時(shí)定義唯一，RID只在葉子層增加，用于查找源數(shù)據(jù)行，即書(shū)簽查找操作。

(D).字段長(zhǎng)度小

聚集索引鍵長(zhǎng)度越小，一頁(yè)索引頁(yè)就可以容納更多索引記錄，進(jìn)而減少索引B樹(shù)結(jié)構(gòu)的深度。例如，一個(gè)百萬(wàn)記錄的表有一個(gè)int聚集索引，可能只需要3層的B樹(shù)結(jié)構(gòu)。如果把聚集索引定義在更寬的列（比如uniqueidentifier列需要16 字節(jié)），那么索引的深度會(huì)增加到4層。任何聚集索引查找需要4個(gè)I/O操作（確切的說(shuō)是4個(gè)邏輯讀），原先只要3個(gè)I/O操作。
同樣，非聚集索引里會(huì)包含聚集索引鍵值，聚集索引鍵長(zhǎng)度越小非聚集索引記錄也就越小，一頁(yè)索引頁(yè)就可以容納更多索引記錄。

1.2 非聚集索引

也是存儲(chǔ)在頁(yè)中（PageType標(biāo)記為2的頁(yè)，叫索引頁(yè))。比如表T建立了一個(gè)非聚集索引Index_A，那么表T有100條數(shù)據(jù)的話，那么索引Index_A也就有100條數(shù)據(jù)（準(zhǔn)確的說(shuō)是100條葉子節(jié)點(diǎn)數(shù)據(jù)，索引是B樹(shù)結(jié)構(gòu)，如果樹(shù)的高度大于0，那么就有根節(jié)點(diǎn)頁(yè)或中間節(jié)點(diǎn)頁(yè)數(shù)據(jù)，這時(shí)索引數(shù)據(jù)就超過(guò)100條），如果表T還有非聚集索引Index_B，那么Index_B也是至少100條數(shù)據(jù)，所以索引建越多開(kāi)銷越大。

更新索引字段、插入一條數(shù)據(jù)、刪除一條數(shù)據(jù)都會(huì)造成索引的維護(hù)從而造成性能的一定影響。在不同情況下，性能影響是不同的。比如當(dāng)你有一個(gè)聚集索引，插入的數(shù)據(jù)又都是在末尾，這樣幾乎是不會(huì)造成數(shù)據(jù)移動(dòng)，影響較小；如果插入的數(shù)據(jù)在中間位置，一般會(huì)導(dǎo)致數(shù)據(jù)移動(dòng)，而且可能產(chǎn)生分頁(yè)和頁(yè)碎片，影響就會(huì)稍大一點(diǎn)（如果插入到的中間頁(yè)有足夠的剩余空間容納插入的數(shù)據(jù)，而且位置是在頁(yè)末，也是不會(huì)造成數(shù)據(jù)移動(dòng)）

2.索引的結(jié)構(gòu)

都說(shuō)SqlServer的索引是B樹(shù)結(jié)構(gòu)（這邊假定你對(duì)B樹(shù)結(jié)構(gòu)有一定了解），那它到底長(zhǎng)什么個(gè)模樣呢，可以用Sql語(yǔ)句來(lái)查看它的邏輯呈現(xiàn)。

新建查詢執(zhí)行語(yǔ)法： DBCC IND(Test,OrderBo,-1) --其中Test庫(kù)的OrderBo表有1萬(wàn)筆數(shù)據(jù)，有聚集索引Id主鍵字段
（不妨自己動(dòng)手建個(gè)表，有聚集索引字段，插入1萬(wàn)表數(shù)據(jù)，然后執(zhí)行這個(gè)語(yǔ)法看看，會(huì)收獲很多，百聞不如一見(jiàn)）

執(zhí)行結(jié)果：

如上圖，看到一個(gè)IndexLevel=2的索引頁(yè)2112（這邊它就是B樹(shù)的根節(jié)點(diǎn)，IndexLevel最大的就是根節(jié)點(diǎn)，往下就是子級(jí)、子子級(jí)...只有一個(gè)根頁(yè)作為B樹(shù)結(jié)構(gòu)的訪問(wèn)入口點(diǎn)），說(shuō)明一定還有IndexLevel=1的索引頁(yè)和IndexLevel=0的葉子頁(yè)。由于這邊是聚集索引，因此當(dāng)IndexLevel=0的葉子頁(yè)就是數(shù)據(jù)頁(yè)，存儲(chǔ)的是一筆一筆的物理數(shù)據(jù)。如上圖也可以看到，IndexLevel=0的行的PageType等于1，就是代表數(shù)據(jù)頁(yè)，上面1.1章節(jié)講到聚集索引時(shí)，也有提到PageType=1；而如果是非聚集索引，IndexLevel=0的葉子頁(yè)，PageType是等于 2，仍然是索引頁(yè)。

同樣，我們用Sql命令DBCC PAGE看一看

-- DBCC TRACEON(3604,-1) 
DBCC PAGE(Test,1,2112,3) 
 --根節(jié)點(diǎn)2112，可以查出它的兩個(gè)子節(jié)點(diǎn)2280和2448，然后對(duì)這兩個(gè)子節(jié)點(diǎn)再作DBCC PAGE查詢
DBCC PAGE(Test,1,2280,3) 
DBCC PAGE(Test,1,2448,3)

如上圖，IndexLevel=2的2112頁(yè)有兩個(gè)IndexLevel=1的子節(jié)點(diǎn)2280和2448，子節(jié)點(diǎn)下又有子節(jié)點(diǎn)，每個(gè)節(jié)點(diǎn)負(fù)責(zé)不同的索引鍵值的區(qū)間（即上圖的“Id(key)”欄位，第一行值是Null，表示最小值或倒序時(shí)的最大值）。這樣的層級(jí)關(guān)系是不是就是一棵B樹(shù)結(jié)構(gòu)，其中IndexLevel其實(shí)就是B樹(shù)結(jié)構(gòu)中的高度Height。

SqlServer在索引中查找某一筆記錄時(shí)，是從根節(jié)點(diǎn)往下找到葉子節(jié)點(diǎn)，因?yàn)樗袛?shù)據(jù)地址都有存在葉子節(jié)點(diǎn)，這其實(shí)是B+樹(shù)的特點(diǎn)之一（B樹(shù)特點(diǎn)是如果查找的值在非葉子節(jié)點(diǎn)就找到，則就能直接返回，顯然SqlServer不是這么做，要驗(yàn)證這一點(diǎn)你可以set statistics io on把統(tǒng)計(jì)開(kāi)起來(lái)，然后select看下邏輯讀的次數(shù)）。

既然一定會(huì)找到葉子節(jié)點(diǎn)，那么索引包含列只要在葉子節(jié)點(diǎn)記錄就可以了，即非葉子節(jié)點(diǎn)沒(méi)有記錄包含列，“索引包含列”見(jiàn)下文第3章節(jié)。

B+樹(shù)這個(gè)特點(diǎn)（所有數(shù)據(jù)地址都有存在葉子節(jié)點(diǎn)）也利于between value1 and value2 區(qū)間查詢，只要找到value1和value2（在葉子節(jié)點(diǎn)），然后把中間串起來(lái)就是要的結(jié)果了。

SqlServer索引結(jié)構(gòu)更像是B+樹(shù)，最終是B樹(shù)和B+樹(shù)的混合版，數(shù)據(jù)結(jié)構(gòu)都是人定的，不一定就是純粹的B樹(shù)或者單純的B+樹(shù)。

3.索引包含列和書(shū)簽查找

談到索引，這邊再講一個(gè)SqlServer2005開(kāi)始增加的“索引包含列”功能，很實(shí)用。

比如，在大報(bào)表查詢數(shù)據(jù)時(shí)，where條件用到索引字段Name2，但是要select的字段是Name1，這時(shí)候可以使用“索引包含列”把Name1包含在索引字段Name2中，大大提高查詢性能。

語(yǔ)法： Create [UNIQUE]  Nonclustered/Clustered Index IndexName On dbo.Table1(Name2) Include(Name1);

接下來(lái)分析為什么索引包含列可以大大提高性能。仍然使用DBCC PAGE命令，查看一個(gè)非聚集索引并有包含列的索引數(shù)據(jù)情況：

由上圖可知，包含列Name1也存儲(chǔ)在索引數(shù)據(jù)中。因此，當(dāng)數(shù)據(jù)庫(kù)用索引字段Name2定位到要查找的某一行時(shí)，就可以直接把Name1的值返回了，而不用再根據(jù)RID（上圖是【HEAP RID(Key)】列）定位到數(shù)據(jù)頁(yè)中去取值，即減少了書(shū)簽查找。當(dāng)查詢只返回一條數(shù)據(jù)，只有一次書(shū)簽查找時(shí)當(dāng)然沒(méi)什么，如果查詢返回的數(shù)據(jù)很大，每一筆都要去數(shù)據(jù)頁(yè)找數(shù)據(jù)取出來(lái)，1000筆就是1000次書(shū)簽查找，可想而知性能消耗很大，這時(shí)候“索引包含列”價(jià)值就大大體現(xiàn)出來(lái)了。

關(guān)于一次書(shū)簽查找，表有聚集索引（比如Id）時(shí)就是類似執(zhí)行了一次 select Name1 from Table1 where Id=1 ，利用聚集索引鍵Id查找（查找方式就是索引Id的B樹(shù)結(jié)構(gòu)查找），而如果表沒(méi)有聚集索引，則是根據(jù)數(shù)據(jù)行指針（由“文件號(hào)2byte：頁(yè)號(hào)4byte：槽號(hào)2byte”組成）查找。聚集索引鍵和行指針一般統(tǒng)稱為RID（Row ID）指針。從這里我們可以想到，如果你的表沒(méi)有很好的聚集索引字段，建議自增長(zhǎng)的Id字段做聚集索引主鍵（冗余出Id字段也行），它符合自增長(zhǎng)、不被更改、唯一性、長(zhǎng)度小的特性，是聚集索引的很好選擇。

自增長(zhǎng)Id絕大部分情況下是適用的，特殊的情況看具體需求而定吧。還有自增長(zhǎng)Id要考慮一個(gè)缺陷，當(dāng)對(duì)表大數(shù)據(jù)量的并發(fā)insert記錄時(shí)，可以想象每個(gè)線程都是要insert到末尾那個(gè)頁(yè)，就會(huì)發(fā)生競(jìng)爭(zhēng)和等待。解決這種情況你可以用uniqueidentifier類型字段（16字節(jié)，我是不建議使用）或者哈希分區(qū)（就是一個(gè)表分成多個(gè)表，大數(shù)據(jù)處理中分庫(kù)分表是正常的）等。但是我建議先優(yōu)化你的insert效率（insert性能本身是很快的），測(cè)試每秒并發(fā)insert數(shù)是否滿足生產(chǎn)環(huán)境，以保留簡(jiǎn)單穩(wěn)定高效的自增長(zhǎng)Id作法。

自增長(zhǎng)Id不一定就是用數(shù)據(jù)庫(kù)提供的自增長(zhǎng)，你也可以自己寫(xiě)算法生成一個(gè)并發(fā)情況下也能唯一的Id（這時(shí)候一般長(zhǎng)度是bitint，8字節(jié)整形），這種情況適合場(chǎng)景是分布式數(shù)據(jù)庫(kù)中主從復(fù)制時(shí)Id欄位是要求一定不能出錯(cuò)的情況（主從復(fù)制的一般模式下，主庫(kù)的Id是按主庫(kù)增長(zhǎng)，從庫(kù)Id也是按從庫(kù)自己的增長(zhǎng)，如果遇到死鎖等原因?qū)е轮鲝膹?fù)制不同步時(shí)，那從庫(kù)的Id就和主庫(kù)的Id自增長(zhǎng)就對(duì)不上號(hào)了）。如果自增長(zhǎng)Id是冗余出的主鍵，那主從庫(kù)Id對(duì)不上號(hào)也就無(wú)影響。

另外，上圖最后一列【Row Size】還告訴我們，索引列或索引包含列的size不要太長(zhǎng)，否則一頁(yè)容不了幾筆記錄，這樣大大增加了索引頁(yè)數(shù)量，而且索引數(shù)據(jù)所占的空間也大大增加了。

以上是“SQL Server索引的示例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！