透過空間佔用和執行計劃瞭解SQL Server的行儲存索引

十畝菠蘿地發表於2023-05-11
SQLServer索引

1 索引介紹

索引是一種幫助查詢語句能夠快速定位到資料的一種技術。索引的儲存方式有行儲存索引、列儲存索引和記憶體最佳化三種儲存方式：

  1. 行儲存索引，使用B+樹結構，行儲存指的是資料儲存格式為堆、聚集索引和記憶體最佳化表的表，用於OLTP場景。行儲存索引按順序排列的值列表，每個值都有指向其所在的資料頁面的指標。
    • 聚集索引
    • 非聚集索引
    • 唯一索引
    • 篩選索引
  2. 列儲存索引，使用列結構儲存，列儲存指的是在邏輯上整理為包含行和列的表，實際上以列式資料格式儲存的資料，用於OLAP場景。使用基於列的資料儲存和查詢處理。
    • 聚集列儲存
    • 非聚集列儲存
  3. 記憶體最佳化索引，使用Bw樹儲存，Bw樹使用一種“旋轉”技術，更適合處理處理範圍查詢和隨機插入/刪除操作，適用於各種場景下的資料儲存和查詢。
    本文中我們討論的索引就是行儲存索引中的聚集索引和非聚集索引，不涉及其它索引。

Bw樹使用一組新的旋轉技術，支援更加高效的範圍查詢操作。而B+樹則使用葉節點連結串列來處理範圍查詢。在B+樹中，如果您需要範圍查詢，您需要遍歷整個連結串列，這會增加查詢的時間成本。相比之下，Bw樹透過一些特殊的旋轉操作，能夠使得範圍查詢操作更加高效，從而顯著提高查詢效能。
假設需要查詢數字在100到200之間的資料，那麼B+樹需要遍歷相應的葉節點連結串列，而Bw樹則可以使用一些特殊的旋轉操作，跳過某些節點，快速定位到相應的資料範圍，從而減少了查詢的時間成本。
總體來說，Bw樹在範圍查詢和隨機操作等特殊情況下比B+樹更加高效。但是對於其他型別的查詢操作，它們的效能並沒有很大的區別，具體的效果需要根據應用場景來進行具體分析。

2 行儲存索引的資料組織結構

聚集索引和非聚集索引都是使用B+樹結構組織的，最頂層稱為根節點，中間層稱為中間節點，最底層稱為葉節點。在聚集索引中，葉節點包含了基礎表的資料頁，根節點和中間節點包含了索引行的索引頁，每個索引行包含一個鍵值和一個指標，透過指標來找到某個葉節點的資料行。而在非聚集索引中，葉節點只包含了索引行的索引頁，沒有資料頁，它的索引行中只有指標，透過指標來找到對應的堆表的RID或者聚集索引的資料頁。
聚集索引和非聚集索引的資料組織結構
聚集索引決定了表中資料行的儲存順序（升序/降序），所以每張表只能有1個聚集索引，可以使用CREATE CLUSTERED INDEX來手動建立聚集索引，也可以是在建表時指定主鍵的方式來自動建立。
每張表可以有多個非聚集索引，可以針對不同的查詢語句和業務場景來建立非聚集索引，只能是使用CREATE NONCLUSTERED INDEX來手動建立非聚集索引。

3 兩種索引的空間佔用對比

由於聚集索引的葉節點儲存了是資料頁，由中間節點存放了指標，而非聚集索引的葉節點存放了指標（行定位器），那透過B+樹的構造，可以大概判斷是非聚集索引要消耗的空間更多，因為非聚集索引要存放更多的指標資訊（葉節點的數量肯定會比中間節點的數量多）。

3.1 使用sp_spaceused檢視索引大小

  1. 檢視基礎表order_line，目前行數1232537行，資料大小約80MB，未建立索引。
    使用exec sp_spaceused order_line命令檢視。
  2. 在order_line表的ol_w_id、ol_d_id、ol_o_id和ol_number列上建立聚簇索引 order_line_i1_clustered
    CREATE UNIQUE CLUSTERED INDEX [order_line_i1_clustered] ON [dbo].[order_line]
(
	[ol_w_id] ASC,
	[ol_d_id] ASC,
	[ol_o_id] ASC,
	[ol_number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
  3. 檢視錶的索引大小，約232KB，說明聚簇索引order_line_i1_clustered的大小為232KB-24KB=208KB。
    使用exec sp_spaceused order_line命令檢視。
  4. 在order_line表的ol_w_id、ol_d_id、ol_o_id和ol_number列上建立非聚簇索引order_line_i1_nonclustered
    CREATE UNIQUE CLUSTERED INDEX [order_line_i1_clustered] ON [dbo].[order_line]
(
	[ol_w_id] ASC,
	[ol_d_id] ASC,
	[ol_o_id] ASC,
	[ol_number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
  5. 檢視錶的索引大小，約19MB，說明非聚簇索引order_line_i1_clustered的大小為18MB~19MB。
    使用exec sp_spaceused order_line命令檢視。

3.2 使用DBCC檢視索引大小

我們也可以透過另外一種方式來證明，透過查詢索引ID，再使用dbcc ind將索引的所有頁返回，然後再計算索引頁的結果

  1. 首先檢視兩個表的查詢索引ID
     SELECT t.name AS TableName,i.name AS IndexName,i.index_id,i.type_desc
FROM sys.dm_db_partition_stats AS s
INNER JOIN sys.indexes AS i 
  ON s.object_id = i.object_id 
  AND s.index_id = i.index_id
INNER JOIN sys.tables AS t 
  ON t.object_id = i.object_id
WHERE t.name='order_line'
  2. 將兩個索引的DBCC IND結果輸出到dbcc_ind_result表中，然後計算索引的大小
    CREATE TABLE dbcc_ind_result (
    PageFID int,
    PagePID int,
    IAMFID int,
    IAMPID int,
    ObjectID int,
    IndexID int,
    PartitionNumber int,
    PartitionID bigint,
    iam_chain_type varchar(30),
    PageType int,
    IndexLevel int,
    NextPageFID int,
    NextPagePID int,
    PrevPageFID int,
    PrevPagePID int
);
GO
INSERT INTO dbcc_ind_result exec('DBCC IND(0,order_line,1)');
GO
INSERT INTO dbcc_ind_result exec('DBCC IND(0,order_line,5)');
GO
SELECT d.IndexID,i.name,COUNT(*)  AS PageCount,COUNT(*)*8 AS SizeKB
FROM dbcc_ind_result d 
INNER JOIN sys.indexes AS i 
ON d.ObjectID = i.object_id 
AND d.IndexID = i.index_id
WHERE d.PageType=2 
GROUP BY d.IndexID,i.name
GO

    實驗證明，在相同的列上，非聚集索引比聚集索引需要更多的空間來存放指標資訊（行定位器），消耗更多的空間。

4 兩種索引讀取資料的方式

前文提到聚集索引的葉節點存放的是資料頁，而非聚集索引葉節點存放的是指標來指向資料的位置，資料的位置可以是堆(head)的RID，也可以時聚集索引的葉節點。下面建立一張測試表來驗證。

4.1 未建立索引時

  1. 建立測試表，生產10000行測試資料
    DROP TABLE IF EXISTS dbo.Test1;
CREATE TABLE dbo.Test1 (
    C1 INT,
    C2 INT);
WITH Nums
AS (SELECT TOP (10000)
        ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS n
    FROM master.sys.all_columns AS ac1
        CROSS JOIN master.sys.all_columns AS ac2)
INSERT INTO dbo.Test1 (
    C1,
    C2)
SELECT n,
       2
FROM Nums;
  2. 開啟統計資訊和執行計劃功能， 從10000行中查詢1行資料，例如查詢C1列為1000的資料。
    SET STATISTICS TIME;
SET STATISTICS IO;
SELECT t.C1,t.C2
FROM dbo.Test1 AS t
WHERE C1 = 1000;
    執行後可以看到統計資訊項，發生了22個邏輯讀：
    • 表 'Test1'。掃描計數 1，邏輯讀取 22 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
    • 並且執行計劃中使用了全表掃描，需要讀取10000行資料。

4.2 建立非聚集索引後

在C1列建立1個非聚集索引後，再觀察統計資訊和執行計劃是否發生變化

  1. 建立非聚集索引
    CREATE NONCLUSTERED INDEX incl ON dbo.Test1(C1);
    建立非聚集索引的過程中，消耗了和前一個查詢相同的資源，統計資訊一樣：
    • 表 'Worktable'。掃描計數 0，邏輯讀取 0 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
    • 表 'Test1'。掃描計數 1，邏輯讀取 22 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
  2. 執行相同的查詢語句，觀察統計資訊和執行計劃
    這一次統計資訊發生了變化，比沒有索引的情況下消耗的邏輯讀更少，只發生了3個邏輯讀：
    • 表 'Test1'。掃描計數 1，邏輯讀取 3 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
    • 而執行計劃則由Table SCAN變為了Index Seek和RID，先是掃描非聚集索引中特定範圍的行，該行的指標資訊為Bmk1000，再將該指標資訊到堆中的RID，再返回資料，這個過程在表中只需要讀取1行資料。

4.3 建立聚集索引後

在非聚集索引的基礎上，我們再建立一個聚集索引，透過語句的執行計劃來了解讀取資料的方式。

  1. 建立聚集索引
    CREATE CLUSTERED INDEX icl ON dbo.Test1(C1);
    建立聚集索引的過程中，產生的統計資訊要比非聚集要多，消耗資源也要更多：
    • 表 'Test1'。掃描計數 1，邏輯讀取 22 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
    • 表 'Test1'。掃描計數 1，邏輯讀取 24 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
      再來看看執行計劃，由於再4.2中建立了非聚集索引，執行計劃裡將建立聚集索引的操作拆成了兩條語句，並且還是INSERT語句：
    • 查詢1：首先還是對錶進行了一次全表掃描，並且按照升序的方式進行了排序後，再將資料插入到聚集索引裡面。這裡對應的就是邏輯讀取22次這條統計資訊，完成了整個聚集索引的建立。
    • 查詢2：然後對整個聚集索引掃描，並將非聚集索引的指標資訊更新為聚集索引的葉節點。這裡對應的就是邏輯讀取24次這條統計資訊，完成了整個非聚集索引的指標資訊更新。
  2. 再次執行相同的查詢語句，消耗的邏輯讀比非聚集索引要少，只需要2次邏輯讀
    • 表 'Test1'。掃描計數 1，邏輯讀取 2 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
    • 執行計劃也不再需要使用非聚集索引和堆的RID返回資料
  3. 繼續驗證非聚集索引是否會透過聚集索引來返回資料，需要使用提示語法來固定語句使用非聚集索引。
    SELECT t.C1,t.C2
FROM dbo.Test1 AS t WITH(INDEX = incl)
WHERE C1 = 1000;
    發現這種讀取資料的方式要消耗更多的邏輯讀，比RID多了1次邏輯讀，比聚集索引多了2次邏輯讀：
    • 表 'Test1'。掃描計數 1，邏輯讀取 4 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
    • 執行計劃中先到非聚集索引查詢C1=1000所在的行，然後再將輸出的指標資訊Uniq1001到聚集索引中執行鍵值查詢，返回資料。

5 行儲存索引的基礎總結

行儲存索引的聚集索引和非聚集索引在生產環境上普遍都會使用到，在本文的基礎上，我們進行簡單總結。

  1. 在資料組織結構上
    聚集索引的葉節點儲存的是資料頁，決定了表資料的排序方式；非聚集索引的葉節點儲存的是指標（行定位器），有可能是堆的RID，也有可能是聚集索引的指標。
  2. 在空間佔用上
    聚集索引只需要很小的空間來儲存資料頁的資訊和順序；非聚集索引需要儲存資料的指標，佔用空間大。
  3. 在讀取資料的方式上
    聚集索引直接透過葉節點讀取資料頁；非聚集索引需要透過指標找到RID或者聚集索引的指標，再透過聚集索引查詢鍵值。
  4. 在邏輯讀的次數上
    直接讀聚集索引，邏輯讀最小，測試邏輯讀次數為2
    透過非聚集索引+RID，邏輯讀居中，測試邏輯讀次數為3
    透過聚集索引+非聚集索引，邏輯讀最大，測試邏輯讀次數為4
  5. 在建立方式上
    聚集索引：建立主鍵時自動使用主鍵列為聚集索引，沒有主鍵時可以透過CRAETE CLUSTERED INDEX 建立，可以指定多個列；每張表只能有1個聚集索引。
    非聚集索引：手動建立，透過CRAETE NONCLUSTERED INDEX 建立；每張表可以有多個非聚集索引。

本次僅對索引的基本知識進行介紹，後續再根據不同的使用場景來驗證和說明。

相關文章