SQL Server中的索引結構與疑惑

標籤：

　　說實話我從沒有在實際項目中使用過索引，僅知道索引是一個相當重要的技術點，因此我也看了不少文章知道了索引的區別、分類、優缺點以及如何使用索引。但關於索引它最本質的是什麼筆者一直沒明白，本文是筆者帶著這些問題研究msdn的一點小結以及一大堆疑惑。

1.表結構

　　當開發人員在資料庫中建立一個表時，此時預設為這個表建立了一個分區，注意是一個分區。分區是一種資料群組織單元，在這個分區中可存在2種結構，分別是堆結構或B樹結構(索引結構)，也就是說一個分區裡要麼是堆結構要麼是B樹結構。為了在某些方面提高效能以及便於管理, 我們可以自己建立分區，將資料以水平方式，也就是以行為單位進行資料行的分區移動。雖然進行分區將資料行組劃分到不同的地方，但是進行查詢或其他動作時仍將這個表看成是單個的邏輯實體。分區中包含由頁組成的堆結構或B樹結構，可能剛開始你會對堆和B樹結構有點懵，不過沒關係先瞭解整個表的結構然後再深入細節。對於堆結構或B樹結構中的資料頁，為了更高效的儲存它們，又分了3種儲存單元，本質其實就是頁的類型，它們也是一種資料群組織單元。這三種頁類型分別是IN_ROW_DATA、LOB_DATA、ROW_OVERLLOW_DATA。頁類型的官方叫法是配置單位，它之所以存在，是因為資料頁中存在占空間小的資料類型和占空間比較大的資料類型，對於像varchar(max)這樣的資料列就要分配大型儲存單元，對於普通的資料列當然就要分配小型儲存單元，就好比int類型為4位元組，bit類型為1位元組一樣。到這裡關於一張表的構成也就介紹完了，如所示。

 2.頁類型

　　現在你或許很好奇這3種頁類型到底是怎樣的類型，從上面為什麼要分類的介紹中可以看出它們分類的依據是佔用空間的大小。IN_ROW_DATA是最基本的頁類型，包含除LOB資料以外的所有資料行和索引行，頁的類型為Data或Index。LOB_DATA頁類型，用來儲存大型資料對象，包括以下資料列text、ntext、varchar(max)、nvarchar(max)、image、varbinary(max)、xml、CLR UDT，頁的類型是Text或Image。ROW_OVERFLOW_DATA頁類型，當IN_ROW_DATA頁類型中的這一行資料超過8060時，將把這個頁上佔用空間最大的列移動到溢出頁中，原始頁上將維護一個指向溢出頁的指標。溢出頁類型和大對象頁類型管理頁的方式相同，都是使用IAM頁鏈來進行管理，注意又出現了一個新名詞IAM頁鏈，用一句話來描述IAM的話就是IAM用來跟蹤表的指定配置單位。現在我們仍繼續深入這3種頁類型，更深刻的理解還是需要sql執行個體。如下我建立了student表。

use testDbcreate table student( studentId int, studentName nvarchar(3600), studentAddress nvarchar(3600), studentDescription nvarchar(max))    --查看建立的頁資訊,最後一個1表示顯示所有分頁的資訊，包括IAM分頁,資料分頁,所有存在的LOB分頁和行溢出頁,索引分頁dbcc ind(‘testDb‘,‘student‘,1)    --此時什麼都沒有insert into student values(1,‘李四‘,‘湖北‘,‘學生‘)dbcc ind(‘testDb‘,‘student‘,1)

在插入一條資料後再執行dbcc ind，它可得到各個類型的頁面分布和它們的所在的檔案號和頁號，此時不再什麼都沒有而是出現了下面的結果。

PageFID和PagePID分別指頁面Id和頁面編號，IAMFID指管理該頁面的IAM頁所在的檔案ID，IAMPID指管理該頁面的IAM頁所在的檔案編號。ObjectID指的是student這個表的對象Id，PartitionNumber表示表或索引所在的分區號，PartitionId表示分區id。接下來要介紹的這4個列要格外注意了。

iam_chain_type表示的正是頁類型，現在我們可以看到全IN_ROW_DATA類型的，此外還可以是溢出類型和大對象資料類型。

PageType也相當重要，它表示分頁類型，1表示資料頁，2是索引頁面，3是Lob_mixed_page，4是Lob_tree_page，10是IAM頁面。

IndexID表示索引ID，0 代表堆, 1 代表叢集索引, 2-250 代表非叢集索引，大於250的是text或image欄位。

IndexLevel表示索引層級，0 代表分葉層級分頁 ，大於0 代表非分葉層級層次，NULL 代表IAM分頁。

從結果中我們看到當插入這條資料後新增了一個資料頁和IAM頁，那IAM頁和資料頁到底是什麼關係呢？帶著這個問題繼續新增資料。

--新增資料以致溢出,並且插入大資料對象insert into student values(2,REPLICATE(‘小王‘,1700),REPLICATE(‘湖北‘,1700),REPLICATE(‘哈‘,80000))dbcc ind(‘testDb‘,‘student‘,1)

現在又新增了5個頁，資料頁又增加了一個114，溢出頁為93和其相應的IAM頁，大對象資料增加了109和IAM頁。產生溢出資料的原因正是我新增的這一行除大對象資料外佔用空間超過了8060位元組，大資料對象則是因為插入了80000個字元‘哈‘，另外這些資料全都使用的是堆結構。現在再來理解IAM，前面說了IAM用來跟蹤每一個配置單位，這個跟蹤的意思其實就是記錄這些頁的順序以便將這些頁連結起來。如果一個IAM頁面儲存的這個配置單位頁資訊超過了它本身的大小，那麼會再建立新的IAM頁，這樣IAM頁和IAM頁一起來記錄配置單位頁的資訊，將這些IAM頁稱為IAM頁鏈。

3.堆結構與B樹結構

　　有了上面的一個鋪墊，現在要來探索的便是堆和B樹了。堆結構是不含叢集索引的資料結構，每個堆中的每個分區至少有一個 IN_ROW_DATA 配置單位。如果堆包含大型物件 (LOB) 列，則該堆的每個分區還將有一個 LOB_DATA 配置單位。如果堆包含超過 8,060 位元組行大小限制的可變長度列，則該堆的每個分區還將有一個 ROW_OVERFLOW_DATA 配置單位。堆中的資料頁和行沒有任何特定的順序且沒有連結在一起，IAM頁內的資訊記錄了資料頁之間唯一的邏輯連結。sys.system_internals_allocation_units系統檢視表中的列 first_iam_page指向管理特定分區中堆的分配空間的一系列 IAM 頁的第一頁，具體的結構圖如。

接下來就是本文的重點，叢集索引和非叢集索引的結構。關於叢集索引與非叢集索引區別的經典說法是索引就像字典的目錄，叢集索引好比按拼音尋找，非叢集索引好比按偏旁來尋找。索引是按B樹結構組織的，所以我們現在要將這種具體的執行個體與B樹關聯起來。對於叢集索引，其B樹結構的頂點稱為根節點，根節點與分葉節點之間的任何索引層級都被認為是中間級，其中最核心的是分葉節點包含基礎資料表的資料頁，其餘節點(根節點和中間級節點)包含的是索引頁，索引頁中存在索引行，每一個索引行包含一個索引值對和一個指標。索引值對的內容是索引順序和該行所連結的下級節點中最小的值。比如2個索引值對5-100和6-200，第一個表示這個索引行是第5個，那它的下級節點中最小的就是100，範圍是100~200，索引行中的指標則是指向了下一個索引頁或分葉節點中的資料行。再看看我從msdn上截的叢集索引結構圖就能夠很清楚地理解使用索引加快速度的本質原因了。

對於非叢集索引，它與叢集索引最本質的兩個區別是"表中的資料行不按非聚集鍵的順序排序和儲存"和"非叢集索引的葉子節點不是資料頁而是索引頁"。也就是說非叢集索引並沒有真正的對資料在物理上進行排序，只是在資料表外建立了一個索引B樹結構，它根據非叢集索引列進行了排序並在每個索引節點中有指向資料表中資料行的指標。非叢集索引中的索引行包含非聚集索引值對和行定位器，其實也就是指標，但是有時候是在存在叢集索引的情況下去建立非叢集索引，有時候是在堆結構中建立非叢集索引，因此這個行定位器即可能是指向叢集索引中的索引鍵，又可能是指向堆中的資料行。這裡還有一個問題要注意，索引的目的是進行排序，而在叢集索引的基礎上建立非叢集索引時，有可能這個叢集索引是非唯一叢集索引，這樣將有可能造成排序錯誤，因此SQL Server 將在內部產生一個唯一鍵以使所有重複鍵唯一，此四位元組的值對於使用者是不可見的，僅當需要使聚集鍵唯一以用於非叢集索引中時，才添加該值，SQL Server 通過使用儲存在非叢集索引的葉行內的叢集索引鍵搜尋叢集索引來檢索資料行。

　　

4.索引疑惑

　　以前閱讀索引的文章時知道索引這個工具適用於查詢量很大的列，不適用於需要很多更新的列，那為什麼會這樣呢？當在一個列上建立叢集索引時，由於叢集索引中直接讓索引頁指向了資料頁，因此叢集索引的查詢速度幾乎總是比非叢集索引的查詢速度快。對於更新操作，它降低索引查詢速度的最本質原因就是索引片段。索引片段可分為外部片段和內部片段，部落格園上有很多關於這方面的文章。筆者學習的過程可以用理解-不理解-理解-不理解這樣的感覺來形容, 我最初的理解是這樣的。

當建立好叢集索引後，此時是一個B樹結構，假如每個索引頁(8060位元組)中有5條資料。那如果進行一個刪除操作造成很多頁中的索引條數不再是5條，這樣索引頁中不就有很多剩餘部分了嗎，這就是內部片段。我查閱的資料裡出現內部片段大概是2種情況，一是新增資料導致分頁從而出現剩餘空間，一是刪除原有資料頁中的部分資料導致出現剩餘空間。

如果進行一個新增操作，假如有3頁資料，現在有一個新增一條資料的操作。按照順序應該放在第一頁，可是現在第一頁空間無法容納。這會導致建立一個新的頁，但是，這個頁並不像邏輯順序那樣在第一頁和第二頁之間，最終結果是在物理上與第一頁不連續，這就是外部片段。出現外部片段最關鍵的是要在物理上造成分離而不是連續。

　　本來我以為我理解了索引片段，可是我又發現有些隨筆裡關於索引片段舉例中，內部片段是新增資料導致索引分頁，外部片段則是新增資料導致分頁。如下面這2張圖，左邊是內部片段樣本圖，右邊是外部片段樣本圖。這又讓我感到不理解了，從表面上看內部片段和外部片段都是新增資料導致分頁，難道內部片段與外部片段之間僅僅只是描述的角度不同，實際上本質是一樣的。後來仔細閱讀前輩的文章才知道還是有區別的，不僅分頁且在物理上導致新增頁與原來的頁不連續才是外部片段。很快我的理解又變成不理解了，或許是我有點鑽牛角尖，但是這個地方好多隨筆都很模糊。我不明白到底這個新增的頁，是如何分配物理空間的。什麼時候會與原來的頁連續造成內部片段，什麼時候會與原來的頁不連續造成外部片段？

         

　　再來思考出現片段的這種情況下，為什麼會變慢。在內部片段中，索引頁裡一個頁有很多剩餘空間，這是讓我認為變慢的現象。我在網上查閱資料來解釋為什麼變慢發現大概都是這樣的說法：明明只有10頁的資料結果卻要在20頁中查詢，這增加了I/O而且還導致頁命中率下降，儲存上也必須消耗更多的空間。假設現在是這樣一種情況，我有10頁資料，進行刪除操作後每頁中的資料減少了，這樣造成了內部片段。按照一般的想法現在速度變慢了，的確這些資料可能已經不需要10頁來儲存，可能5頁就足夠。可是我原來是有10頁資料，現在依舊是10頁資料，為什麼相對於以前完整的10頁資料來說現在變慢了？我越來越想知道到底這種內部的操作的是什麼樣子的，如果有大牛閱讀到這裡還請您指點一下。我也不知道我為什麼會出現這種奇怪的疑問，現在我可以做的就是在進行刪除操作導致頁資料減少，從而產生內部片段的情況下查詢速度是否真的變慢了呢？sql如下所示，在測試過程中又出現了3個疑惑。

use testDbcreate table student( studentId int, studentName char(500),studentAddress char(500))    declare @i intset @i=0while(@i<3200)begin    insert into student values(@i,‘wangwu‘,‘hubei‘)    set @i=@i+1endselect index_type_desc,               --索引類型說明:HEAP、CLUSTERED INDEX、NONCLUSTERED INDEX、XML INDEX、PRIMARY XML INDEX、SPATIAL INDEXindex_depth,                          --索引層級數：堆 或 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位page_count,                           --索引或資料頁的總數，對於索引表示IN_ROW_DATA中B樹的當前層級中索引頁總數，對於堆表示IN_ROW_DATA配置單位中的資料頁總數record_count,                         --總記錄數fragment_count,                       --IN_ROW_DATA 配置單位的分葉層級中的片段數。avg_record_size_in_bytes,             --平均記錄大小（位元組）avg_fragment_size_in_pages,           --IN_ROW_DATA 配置單位的分葉層級中的一個片段的平均頁數。avg_fragmentation_in_percent,         --索引的邏輯片段，或 IN_ROW_DATA 配置單位中堆的區片段。avg_page_space_used_in_percent        --所有頁中使用的可用資料存放區空間的平均百分比。 from sys.dm_db_index_physical_stats(DB_ID(‘testDb‘),OBJECT_ID(‘student‘),NULL,NULL,‘sampled‘)

create clustered index index_studentId on student(studentId)select index_type_desc,               --索引類型說明:HEAP、CLUSTERED INDEX、NONCLUSTERED INDEX、XML INDEX、PRIMARY XML INDEX、SPATIAL INDEXindex_depth,                          --索引層級數：1是堆 或 LOB_DATA 或 ROW_OVERFLOW_DATA 配置單位page_count,                           --索引或資料頁的總數，對於索引表示IN_ROW_DATA中B樹的當前層級中索引頁總數，對於堆表示IN_ROW_DATA配置單位中的資料頁總數record_count,                         --總記錄數fragment_count,                       --IN_ROW_DATA 配置單位的分葉層級中的片段數。avg_record_size_in_bytes,             --平均記錄大小（位元組）avg_fragment_size_in_pages,           --IN_ROW_DATA 配置單位的分葉層級中的一個片段的平均頁數。avg_fragmentation_in_percent,         --索引的邏輯片段，或 IN_ROW_DATA 配置單位中堆的區片段。avg_page_space_used_in_percent        --所有頁中使用的可用資料存放區空間的平均百分比。 from sys.dm_db_index_physical_stats(DB_ID(‘testDb‘),OBJECT_ID(‘student‘),NULL,NULL,‘sampled‘)

 (1)按理說一行資料為1004個位元組，一頁可以存放8條資料，3200條應該是400行，但結果卻是464頁，這是第一個讓我很疑惑的地方。

 (2)在建立索引後頁數page_count為458，頁數相比原來的464反而還變小了，這是第二個讓我很疑惑的地方。

declare @i intset @i=6while(@i<3200)begin    delete from student where studentId=@i     set @i=@i+8endgo

(3)結果中avg_fragmentation_in_persent為0，這說明根本就沒有產生片段，我不知道是我誤解這篇隨筆的意思還是這篇隨筆關於內部片段的地方寫錯了。

本人資料庫菜鳥一枚，如您有想法歡迎交流。

SQL Server中的索引結構與疑惑