小編帶你深入解析SQL Server索引的原理

實際上，您可以把索引理解為一種特殊的目錄，下面這篇文章主要給大家介紹了關於SQL Server索引原理的相關資料，文中通過範例程式碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧

前言

此文是我之前的筆記整理而來，以索引為入口進行探討相關資料庫知識（又做了修改以讓人更好消化）。SQL Server接觸不久的朋友可以只看以下藍色字型字，簡單有用節省時間；如果是資料庫基礎不錯的朋友，可以全看，歡迎探討。

索引的概念

索引的用途：我們對資料查詢及處理速度已成為衡量應用系統成敗的標準，而採用索引來加快資料處理速度通常是最普遍採用的最佳化方法。

索引是什麼：資料庫中的索引類似於一本書的目錄，在一本書中使用目錄可以快速找到你想要的資訊，而不需要讀完全書。在資料庫中，資料庫程式使用索引可以重啊到表中的資料，而不必掃描整個表。書中的目錄是一個字詞以及各字詞所在的頁碼列表，資料庫中的索引是表中的值以及各值儲存位置的列表。

索引的利弊：查詢執行的大部分開銷是I/O，使用索引提高效能的一個主要目標是避免全表掃描，因為全表掃描需要從磁碟上讀取表的每一個資料頁，如果有索引指向資料值，則查詢只需要讀少數次的磁碟就行啦。所以合理的使用索引能加速資料的查詢。但是索引並不總是提高系統的效能，帶索引的表需要在資料庫中佔用更多的儲存空間，同樣用來增刪資料的命令已耗用時間以及維護索引所需的處理時間會更長。所以我們要合理使用索引，及時更新去除次優索引。

1.叢集索引和非叢集索引

索引分為叢集索引和非叢集索引

1.1 叢集索引

表的資料是儲存在資料頁中（資料頁的PageType標記為1），SqlServer一頁是8k，存滿一頁就開闢下一頁儲存。如果表有叢集索引，那麼一筆一筆物理資料就是按叢集索引欄位的大小升/降排序儲存在頁中。當對叢集索引欄位更新或中間插入/刪除資料時，都會導致表資料移動（造成效能一定影響），因為它要保持升/降排序。

注意，主鍵只是預設是叢集索引，它也可以設定為非叢集索引，也可以在非主鍵欄位上設定為叢集索引，全表只能有一個叢集索引。

一個優秀的叢集索引欄位一般包含以下4個特性：

(A).自增長

總是在末尾增加記錄，減少分頁和索引片段。

(B).不被更改

減少資料移動。

(C).唯一性

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

更重要的是，索引鍵指唯一的話，它在每條記錄裡才可以正確指向來源資料行RID。如果叢集索引索引值不唯一，SqlServer就需要內部產生uniquifier 列組合當作聚集鍵保證“索引值”唯一性；如果非叢集索引索引值不唯一，就會增加RID列（叢集索引鍵或者堆表中的行指標）保證“索引值”唯一性。

思考（可略過）：索引“索引值”在非葉子節點也有保證唯一性，原因應該是為了明確索引記錄在非葉子節點中的位置。比如有個非叢集索引欄位Name2，表中有很多Name2='a'的記錄，導致Name2='a'在非葉子節點上有多條索引記錄（節點），這時候再insert一筆Name2=‘a'的記錄時，就可以根據非葉子節點的RID和新增記錄的RID很快確定要insert到哪個索引記錄（節點）上，如果沒有非葉子節點的RID，那得遍曆到所有Name2='a'的葉子節點才能確定位置。另外，當我們select * from Table1 where Name2<='a'時，返回的資料是按非叢集索引Name2和RID排序的，很好理解返回的資料就是按這邊索引儲存的順序排序的。這是這條sql查詢時有用到Name2索引的結果，如果資料庫查詢計劃因“臨界點”問題選擇直接表資料掃描，那返回的資料預設就是按表資料的順序排序的。

為了“索引值”唯一性，對於叢集索引，uniquifier 列只在索引值重複時增加。對於非叢集索引，如果建立索引時沒定義唯一，RID會在所有記錄增加，就算索引值是唯一的；如果建立索引時定義唯一，RID只在葉子層增加，用於尋找來源資料行，即書籤尋找操作。

(D).欄位長度小

叢集索引鍵長度越小，一頁索引頁就可以容納更多索引記錄，進而減少索引B樹結構的深度。例如，一個百萬記錄的表有一個int叢集索引，可能只需要3層的B樹結構。如果把叢集索引定義在更寬的列（比如uniqueidentifier列需要16 位元組），那麼索引的深度會增加到4層。任何叢集索引尋找需要4個I/O操作（確切的說是4個邏輯讀），原先只要3個I/O操作。
同樣，非叢集索引裡會包含叢集索引索引值，叢集索引鍵長度越小非叢集索引記錄也就越小，一頁索引頁就可以容納更多索引記錄。

1.2 非叢集索引

也是儲存在頁中（PageType標記為2的頁，叫索引頁)。比如表T建立了一個非叢集索引Index_A，那麼表T有100條資料的話，那麼索引Index_A也就有100條資料（準確的說是100條葉子節點資料，索引是B樹結構，如果樹的高度大於0，那麼就有根節點頁或中間節點頁資料，這時索引資料就超過100條），如果表T還有非叢集索引Index_B，那麼Index_B也是至少100條資料，所以索引建越多開銷越大。

更新索引欄位、插入一條資料、刪除一條資料都會造成索引的維護從而造成效能的一定影響。在不同情況下，效能影響是不同的。比如當你有一個叢集索引，插入的資料又都是在末尾，這樣幾乎是不會造成資料移動，影響較小；如果插入的資料在中間位置，一般會導致資料移動，而且可能產生分頁和頁片段，影響就會稍大一點（如果插入到的中間頁有足夠的剩餘空間容納插入的資料，而且位置是在頁末，也是不會造成資料移動）

2.索引的結構

都說SqlServer的索引是B樹結構（這邊假定你對B樹結構有一定瞭解），那它到底長什麼個模樣呢，可以用Sql語句來查看它的邏輯呈現。

建立查詢執行文法： DBCC IND(Test,OrderBo,-1) --其中Test庫的OrderBo表有1萬筆資料，有叢集索引Id主鍵欄位
（不妨自己動手建個表，有叢集索引欄位，插入1萬表資料，然後執行這個文法看看，會收穫很多，百聞不如一見）

執行結果：

如，看到一個IndexLevel=2的索引頁2112（這邊它就是B樹的根節點，IndexLevel最大的就是根節點，往下就是子級、子子級...只有一個根頁作為B樹結構的訪問進入點），說明一定還有IndexLevel=1的索引頁和IndexLevel=0的葉子頁。由於這邊是叢集索引，因此當IndexLevel=0的葉子頁就是資料頁，儲存的是一筆一筆的物理資料。如也可以看到，IndexLevel=0的行的PageType等於1，就是代表資料頁，上面1.1章節講到叢集索引時，也有提到PageType=1；而如果是非叢集索引，IndexLevel=0的葉子頁，PageType是等於 2，仍然是索引頁。

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

-- DBCC TRACEON(3604,-1) DBCC PAGE(Test,1,2112,3)  --根節點2112，可以查出它的兩個子節點2280和2448，然後對這兩個子節點再作DBCC PAGE查詢DBCC PAGE(Test,1,2280,3) DBCC PAGE(Test,1,2448,3)

如，IndexLevel=2的2112頁有兩個IndexLevel=1的子節點2280和2448，子節點下又有子節點，每個節點負責不同的索引索引值的區間（即的“Id(key)”欄位，第一行值是Null，表示最小值或倒序時的最大值）。這樣的層級關係是不是就是一棵B樹結構，其中IndexLevel其實就是B樹結構中的高度Height。

SqlServer在索引中尋找某一筆記錄時，是從根節點往下找到葉子節點，因為所有資料地址都有存在葉子節點，這其實是B+樹的特點之一（B樹特點是如果尋找的值在非葉子節點就找到，則就能直接返回，顯然SqlServer不是這麼做，要驗證這一點你可以set statistics io on把統計開起來，然後select看下邏輯讀的次數）。

既然一定會找到葉子節點，那麼索引包含列只要在葉子節點記錄就可以了，即非葉子節點沒有記錄包含列，“索引包含列”見下文第3章節。

B+樹這個特點（所有資料地址都有存在葉子節點）也利於between value1 and value2 區間查詢，只要找到value1和value2（在葉子節點），然後把中間串起來就是要的結果了。

SqlServer索引結構更像是B+樹，最終是B樹和B+樹的混合版，資料結構都是人定的，不一定就是純粹的B樹或者單純的B+樹。

3.索引包含列和書籤尋找

談到索引，這邊再講一個SqlServer2005開始增加的“索引包含列”功能，很實用。

比如，在大報表查詢資料時，where條件用到索引欄位Name2，但是要select的欄位是Name1，這時候可以使用“索引包含列”把Name1包含在索引欄位Name2中，大大提高查詢效能。

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

接下來分析為什麼索引包含列可以大大提高效能。仍然使用DBCC PAGE命令，查看一個非叢集索引並有內含資料行索引資料情況：

由可知，包含列Name1也儲存在索引資料中。因此，當資料庫用索引欄位Name2定位到要尋找的某一行時，就可以直接把Name1的值返回了，而不用再根據RID（是【HEAP RID(Key)】列）定位到資料頁中去取值，即減少了書籤尋找。當查詢只返回一條資料，只有一次書籤尋找時當然沒什麼，如果查詢返回的資料很大，每一筆都要去資料頁找資料取出來，1000筆就是1000次書籤尋找，可想而知效能消耗很大，這時候“索引包含列”價值就大大體現出來了。

關於一次書籤尋找，表有叢集索引（比如Id）時就是類似執行了一次 select Name1 from Table1 where Id=1 ，利用叢集索引鍵Id尋找（尋找方式就是索引Id的B樹結構尋找），而如果表沒有叢集索引，則是根據資料行指標（由“檔案號2byte：頁號4byte：槽號2byte”組成）尋找。叢集索引鍵和行指標一般統稱為RID（Row ID）指標。從這裡我們可以想到，如果你的表沒有很好的叢集索引欄位，建議自增長的Id欄位做叢集索引主鍵（冗餘出Id欄位也行），它符合自增長、不被更改、唯一性、長度小的特性，是叢集索引的很好選擇。

自增長Id絕大部分情況下是適用的，特殊的情況看具體需求而定吧。還有自增長Id要考慮一個缺陷，當對錶大資料量的並發insert記錄時，可以想象每個線程都是要insert到末尾那個頁，就會發生競爭和等待。解決這種情況你可以用uniqueidentifier類型欄位（16位元組，我是不建議使用）或者雜湊分割（就是一個表分成多個表，大資料處理中分庫分表是正常的）等。但是我建議先最佳化你的insert效率（insert效能本身是很快的），測試每秒並發insert數是否滿足生產環境，以保留簡單穩定高效的自增長Id作法。

自增長Id不一定就是用資料庫提供的自增長，你也可以自己寫演算法產生一個並發情況下也能唯一的Id（這時候一般長度是bitint，8位元組整形），這種情況適合情境是分散式資料庫中主從複製時Id欄位是要求一定不能出錯的情況（主從複製的一般模式下，主庫的Id是按主庫增長，從庫Id也是按從庫自己的增長，如果遇到死結等原因導致主從複製不同步時，那從庫的Id就和主庫的Id自增長就對不上號了）。如果自增長Id是冗餘出的主鍵，那主從庫Id對不上號也就無影響。

另外，最後一列【Row Size】還告訴我們，索引列或索引包含列的size不要太長，否則一頁容不了幾筆記錄，這樣大大增加了索引頁數量，而且索引資料所佔的空間也大大增加了。