SQL Server 深入解析索引儲存(堆)

pursuer.chen發表於2015-03-25
SQLServer索引

標籤：SQL SERVER/MSSQL SERVER/資料庫/DBA/索引體系結構/堆

概述

     本篇文章是關於堆的儲存結構。堆是不含聚集索引的表（所以只有非聚集索引的表也是堆）。堆的 sys.partitions 中具有一行，對於堆使用的每個分割槽，都有 index_id = 0。預設情況下，一個堆有一個分割槽。當堆有多個分割槽時，每個分割槽有一個堆結構，其中包含該特定分割槽的資料。例如，如果一個堆有四個分割槽，則有四個堆結構；每個分割槽有一個堆結構。根據堆中的資料型別，每個堆結構將有一個或多個分配單元來儲存和管理特定分割槽的資料。每個堆中的每個分割槽至少有一個 IN_ROW_DATA 分配單元。如果堆包含大型物件 (LOB) 列，則該堆的每個分割槽還將有一個 LOB_DATA 分配單元。如果堆包含超過 8,060 位元組行大小限制的可變長度列，則該堆的每個分割槽還將有一個 ROW_OVERFLOW_DATA 分配單元。有關分配單元的詳細資訊，

sys.system_internals_allocation_units 系統檢視中的列 first_iam_page 指向管理特定分割槽中堆的分配空間的一系列 IAM 頁的第一頁。SQL Server 使用 IAM 頁在堆中移動。堆內的資料頁和行沒有任何特定的順序，也不連結在一起。資料頁之間唯一的邏輯連線是記錄在 IAM 頁內的資訊。

 

正文

堆結構

可以通過掃描 IAM 頁對堆進行表掃描或序列讀操作來找到容納該堆的頁的擴充套件盤區。因為 IAM 按擴充套件盤區在資料檔案記憶體在的順序表示它們，所以這意味著序列堆掃描連續沿每個檔案進行。使用 IAM 頁設定掃描順序還意味著堆中的行一般不按照插入的順序返回。

 

 頁面的組成

 

一個SQL資料頁面=標頭+資料行+剩餘空間+行偏移表（如果表中存在大資料型別欄位）+溢位表（如果存在） 

行偏移

---測試資料
CREATE TABLE Theap
(ID INT IDENTITY(1,1) NOT NULL,
NAME NVARCHAR(MAX) NOT NULL,
IDATE DATETIME DEFAULT(GETDATE()) NOT NULL
)
GO
---插入1000條測試資料
DECLARE @ID INT=1
WHILE(@ID<=1000)
BEGIN
INSERT INTO Theap(NAME)VALUES((@ID))
SET @ID=@ID+1 
END
GO
SELECT * FROM Theap

---開啟跟蹤標誌
DBCC TRACEON(3604,2588)
--DBCC TRACEOFF(3604,2588)
---獲取物件的資料頁，結構：資料庫、物件、顯示
DBCC IND(Ixdata,Theap,-1)

SELECT * FROM sys.system_internals_allocation_units WHERE container_id=72057594039566336

分析114頁

DBCC page(Ixdata,1,114,3)

整個資料頁有四部分組成

1.頁面在記憶體中的對映資訊（BUFFER:）

2. 頁頭部分（PAGE HEADER）：記錄了頁號、頁型別、記錄數，LSN及其他資訊，在上一章已經講過

3. 資料部分（DATA）：以16進位制格式儲存行記錄（從第96個位元組開始）

4. 行偏移部分(OFFSET TABLE)：以倒序的順序記錄了行記錄的指標位置，這個使用2的顯示方式比較明顯看出

看看一行記錄在頁面中是怎樣記錄的

 

 

 

00000000: 30001000 01000000 76ff7401 64a40000 †0.......v.t.d...
00000010: 0300b801 00190031 00†††††††††††††††††.......1.

1位元組：30>00110000 ；右邊第一位開始是0位，第4位和第5位是1，由於在2008中null bit map總是存在的，所以只考慮第五位，即存在變長欄位。 

1位元組：00；狀態位B在SQLServer2005/2008中未啟用，所以為00

2位元組：1000;這兩個位元組是表示定長列的位元組數，反過來排0010=1*16=16個位元組，表中的定長列ID(4個位元組)+IDATE(8個位元組)+4個位元組（預設加的）=16個位元組

N個位元組：01000000 76ff7401 64a40000；這N個位元組是定長欄位的內容，總共12個位元組

2個位元組：0300；表中的欄位數，由於表中只有3個欄位所以用0300表示

1個位元組：b8>10111000;這個位元組表示主要是判斷對應的欄位內容是否有空值，1代表允許為空，前三個欄位都不允許為空，而且表只有三個欄位所以不用看後面。

2個位元組：01 00；這個欄位表示變長列的個數，根據剛才說的方法倒過來00 01=1個欄位，表中頁只有NAME欄位是變長欄位。

2個位元組*變長欄位的個數：1900；由於表中只有一個變長欄位，所以只有兩個位元組，表示第一個變長列的終止位置=25

N個位元組：變長欄位的內容，3100轉換成字元剛好是‘1’

線上16進位制轉字元： http://www.bejson.com/tools/0x/

 

查詢

SELECT [ID]
      ,[NAME]
      ,[IDATE]
  FROM [Ixdata].[dbo].[Theap]
  WHERE NAME='1'
    
  SELECT [ID]
      ,[NAME]
      ,[IDATE]
  FROM [Ixdata].[dbo].[Theap]
  WHERE NAME='900'

分析查詢可以看出無論你查詢的是'1'還是'900',都是掃描一次，邏輯讀取4次，因為存在4個頁，用ID去查也是一樣.

 

行溢位

CREATE TABLE Theapover
(ID INT IDENTITY(1,1) NOT NULL,
NAME VARCHAR(5000) NOT NULL,
NAME1 VARCHAR(5000) NOT NULL,
IDATE DATETIME DEFAULT(GETDATE()) NOT NULL
)
GO
---插入1000條測試資料
DECLARE @ID INT=1
WHILE(@ID<=1000)
BEGIN
INSERT INTO Theapover(NAME,NAME1)VALUES(REPLICATE(1,5000),REPLICATE(2,5000))
SET @ID=@ID+1 
END
GO
SELECT * FROM Theapover
ORDER BY ID
GO

DBCC IND(Ixdata,Theapover,-1)

SELECT * FROM sys.system_internals_allocation_units WHERE container_id=72057594039828480

 

總共插入了1000條記錄，一行佔一頁再加上兩個IAM頁剛好2002頁，

存在兩個IAM頁，分別是3281和3283頁，還有一個比較特殊的頁3280頁，3280頁是溢位資料裡面的根頁，等一下看一下這頁的資料。

 

分析IAM頁

DBCC page(Ixdata,1,3283,3)

分析溢位頁

DBCC page(Ixdata,1,3282,3)

 

注意:不是堆頁和溢位頁就只能一一對應，由於當前表中堆頁容納不下兩條記錄所以就導致了堆頁和溢位頁一樣，當堆頁可以存多條記錄的時候就會出現一個堆頁對應多個溢位頁。

測試查詢

  SELECT  [ID]
      ,[NAME]
      ,[NAME1]
      ,[IDATE]
  FROM [Ixdata].[dbo].[Theapover]
  where ID=500

當我繼續往堆表裡插入資料直到表超過4G的時候會有新的IAM頁生成，而且IAM頁之間存在鏈關係（資料頁）。

查詢發現新生成的3135IAM頁種的資料頁的行溢位指向的是新生成的511256IAM頁的溢位頁，這樣的話IAM頁之間的鏈關係對查詢效率貌似沒有什麼改善的好處。

1. IAM用於查詢分配給heap的所有資料頁資訊，IAM頁中記錄了所有的頁面的頁id。

2. 對於大多數較小的heap表來說，僅需要一個IAM頁就可以管理其頁面。

3. 若heap表大於4GB或包含LOB資料型別的話，則會包含多個IAM頁面。

4. 當查詢要獲取heap表的所有記錄時，SQL Server使用IAM頁來掃描heap表

總結

  堆表的頁是沒有規律的不存在頁鏈，所以導致堆表的查詢效率很差,當查詢一個10萬條記錄的堆表邏輯讀取就需要10萬次，如果堆表的資料量很大需要多次進行物理讀獲取頁面的時候對於IO的消耗是非常大的，建議表都應該建聚集索引。

 

備註：

    作者：pursuer.chen

    部落格：http://www.cnblogs.com/chenmh

本站點所有隨筆都是原創，歡迎大家轉載；但轉載時必須註明文章來源，且在文章開頭明顯處給明連結，否則保留追究責任的權利。

《歡迎交流討論》

相關文章