概述

學數據庫一定要對其中索引是怎麼設計去做個探討，由一個點逐步深入學習，下面以索引為入口探討下sqlserver數據庫。

01

索引概念

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

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

02

聚集索引和非聚集索引

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

1、 聚集索引

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

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

一個優秀的聚集索引字段一般包含以下4個特性：

(1)自增長

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

(2)不被更改

減少數據移動。

(3)唯一性

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

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

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

(4)字段長度小

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

同樣，非聚集索引裡會包含聚集索引鍵值，聚集索引鍵長度越小非聚集索引記錄也就越小，一頁索引頁就可以容納更多索引記錄。

2、 非聚集索引

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

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

03索引的結構

很多人認為SqlServer的索引是B樹結構，那它到底長什麼個模樣呢，可以用Sql語句來查看它的邏輯呈現。

新建查詢執行語法： DBCC IND(Test,OrderBo,-1) --其中Test庫的OrderBo表有1萬條數據，有聚集索引Id主鍵字段。

執行結果：

如上圖，看到一個IndexLevel=2的索引頁2112（這邊它就是B樹的根節點，IndexLevel最大的就是根節點，往下就是子級、子子級...只有一個根頁作為B樹結構的訪問入口點），說明一定還有IndexLevel=1的索引頁和IndexLevel=0的葉子頁。由於這邊是聚集索引，因此當IndexLevel=0的葉子頁就是數據頁，存儲的是一筆一筆的物理數據。如上圖也可以看到，IndexLevel=0的行的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不是這麼做）。

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

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

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

問題來了，Oracle、mysql、sqlserver在索引設計上有什麼區別呢？哪種方式更優，大家有什麼好的想法可以在下方留言一起探討，後面會分享更多devops和DBA方面的內容，感興趣的朋友可以關注一下~