技術文章 » SQLSERVER 的複合索引和包含索引到底有啥區別？

SQLSERVER 的複合索引和包含索引到底有啥區別？

2023-01-06 12:00:44

一：背景

1. 講故事

在 SQLSERVER 中有非常多的索引，比如：聚集索引，非聚集索引，唯一索引，複合索引，Include索引，交叉索引，連線索引，奇葩索引等等，當索引多了之後很容易傻傻的分不清，比如：複合索引 和 Include索引，但又在真實場景中用的特別多，本篇我們就從底層資料頁層面釐清一下。

二：到底有什麼區別

1. 這些索引解決了什麼問題

說區別之前，一定要知道它們大概解決了什麼問題？這裡我就從 索引覆蓋 角度來展開吧，為了方便講述，先上一個測試 sql：


IF(OBJECT_ID('t') IS NOT NULL) DROP TABLE t;

CREATE TABLE t(a INT IDENTITY, b CHAR(6), c CHAR(10) DEFAULT 'aaaaaaaaaa')

SET NOCOUNT ON
DECLARE @num INT 
SET @num =10000
WHILE (@num <90000)
BEGIN
	INSERT INTO t(b) VALUES ('b'+CAST(@num AS CHAR(5)))
	SET @num=@num+1
END

CREATE CLUSTERED INDEX idx_a ON t(a)
CREATE INDEX idx_b ON t(b)

SELECT * FROM t;

程式碼非常簡單，在 t 表中建立三個列，插入 8w 條資料，然後建立兩個索引，接下來做一個查詢獲取 b,c 列。


SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT b,c FROM t WHERE b IN  ('b10000','b20000','b30000','b40000','b50000','b70000','b80000','b90000')
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

輸出如下：


表「t」。掃描計數 8，邏輯讀取次數 30，物理讀取次數 0，頁面伺服器讀取次數 0，預讀讀取次數 0，頁面伺服器預讀讀取次數 0，LOb 邏輯讀取次數 0，LOB 邏輯讀取次數 0，LOB 頁面伺服器讀取次數 0，LOB 預讀讀取次數 0，LOB 頁面伺服器預讀讀取次數 0。

 SQL Server 執行時間:
   CPU 時間 = 0 毫秒，佔用時間 = 134 毫秒。

 SQL Server 執行時間:
   CPU 時間 = 0 毫秒，佔用時間 = 0 毫秒。

Completion time: 2023-01-06T08:47:45.2364473+08:00

從執行計劃看，這是一個經典的 書籤查詢，這種查詢返回的行數越多效能越差，在索引優化時一般都會規避掉這種情況，我們也看到了邏輯讀取次數有 30 次，那能不能再小一點呢？

為了解決這個問題，乾脆把 c 列也放到索引中去達到索引覆蓋的效果，這就需要用到 複合索引 了，參考sql如下：


CREATE INDEX idx_complex ON t (b,c)

再次查詢輸出如下：


SQL Server 分析和編譯時間: 
   CPU 時間 = 0 毫秒，佔用時間 = 0 毫秒。
表「t」。掃描計數 8，邏輯讀取次數 24，物理讀取次數 0，頁面伺服器讀取次數 0，預讀讀取次數 0，頁面伺服器預讀讀取次數 0，LOb 邏輯讀取次數 0，LOB 邏輯讀取次數 0，LOB 頁面伺服器讀取次數 0，LOB 預讀讀取次數 0，LOB 頁面伺服器預讀讀取次數 0。

 SQL Server 執行時間:
   CPU 時間 = 0 毫秒，佔用時間 = 96 毫秒。

 SQL Server 執行時間:
   CPU 時間 = 0 毫秒，佔用時間 = 0 毫秒。

Completion time: 2023-01-06T08:53:56.9688921+08:00

從執行計劃來看，這次沒有走 書籤查詢 而是 索引查詢，並且邏輯讀也降到了 24 次，這是一個好的優化。

相信有些朋友也知道用 Include索引 也能達到這個效果，接下來試著把複合索引給刪了增加一個 Include索引，程式碼如下：


DROP INDEX idx_complex ON dbo.t;
CREATE INDEX idx_include ON  t(b) INCLUDE (c)

再次查詢輸出如下：


表「t」。掃描計數 8，邏輯讀取次數 16，物理讀取次數 0，頁面伺服器讀取次數 0，預讀讀取次數 0，頁面伺服器預讀讀取次數 0，LOb 邏輯讀取次數 0，LOB 邏輯讀取次數 0，LOB 頁面伺服器讀取次數 0，LOB 預讀讀取次數 0，LOB 頁面伺服器預讀讀取次數 0。

 SQL Server 執行時間:
   CPU 時間 = 0 毫秒，佔用時間 = 73 毫秒。

 SQL Server 執行時間:
   CPU 時間 = 0 毫秒，佔用時間 = 0 毫秒。

Completion time: 2023-01-06T08:58:18.1122561+08:00

從執行計劃來看也是走的 非聚集索引，而且邏輯讀再次降到了 16 次，相比原始的書籤查詢已經優化了 50%，這是一個巨大的效能提升不是。

到這裡其實有一個問題，兩種優化走的都是 非聚集索引，從邏輯讀次數看貌似 Include索引 更好一些，為什麼會這樣呢？這就涉及到了底層儲存，接下來一起扒一下。

2. 儲存原理研究

研究它們的不同點，最徹底的方式就是從底層儲存出發，首先我們觀察下 複合索引 的底層儲存是什麼樣的，可以用 DBCC 命令。


DBCC TRACEON(3604)
DBCC IND(MyTestDB,t,-1)

從 IndexLevel=2 來看這個複合索引構成的B樹已經達到了二層，接下來我們查一下 368 號資料頁內容。


DBCC PAGE(MyTestDB,1,368,2)

輸出如下：


PAGE: (1:368)

Memory Dump @0x000000F555578000

000000F555578000:   01020002 00800001 00000000 00001b00 00000000  ....................
000000F555578014:   00000200 3e010000 601f9c00 70010000 01000000  ....>...`...p.......
000000F555578028:   f8000000 e0680000 f5010000 00000000 00000000  .....h..............
000000F55557803C:   00000000 01000000 00000000 00000000 00000000  ....................
000000F555578050:   00000000 00000000 00000000 00000000 16623130  .................b10
000000F555578064:   30303061 61616161 61616161 61010000 00380500  000aaaaaaaaaa....8..
000000F555578078:   00010004 00001662 38333631 36616161 61616161  .......b83616aaaaaaa
000000F55557808C:   61616191 1f010070 05000001 00040000 00006231  aaa....p..........b1

OFFSET TABLE:

Row - Offset                        
1 (0x1) - 126 (0x7e)                
0 (0x0) - 96 (0x60)                 


DBCC 執行完畢。如果 DBCC 輸出了錯誤資訊，請與系統管理員聯絡。

根據下面的 Slot 個數可以知道這個分支節點資料頁只有 2 條記錄，分別為：(b10000,aaaaaaaaaa,0x01) , (b83616,aaaaaaaaaa,0x011f91)，這裡說明一下最後的 01 和 0x011f91 是主鍵key，接下來找個葉子節點，比如：1632 號索引頁。


PAGE: (1:1632)


Memory Dump @0x000000F555578000

...
000000F555578050:   00000000 00000000 00000000 00000000 16623135  .................b15
000000F555578064:   32383761 61616161 61616161 61a81400 00040000  287aaaaaaaaaa.......
000000F555578078:   16623135 32383861 61616161 61616161 61a91400  .b15288aaaaaaaaaa...
000000F55557808C:   00040000 16623135 32383961 61616161 61616161  .....b15289aaaaaaaaa
000000F5555780A0:   61aa1400 00040000 16623135 32393061 61616161  a........b15290aaaaa
000000F5555780B4:   61616161 61ab1400 00040000 16623135 32393161  aaaaa........b15291a
000000F5555780C8:   61616161 61616161 61ac1400 00040000 16623135  aaaaaaaaa........b15
000000F5555780DC:   32393261 61616161 61616161 61ad1400 00040000  292aaaaaaaaaa.......
000000F5555780F0:   16623135 32393361 61616161 61616161 61ae1400  .b15293aaaaaaaaaa...
000000F555578104:   00040000 16623135 32393461 61616161 61616161  .....b15294aaaaaaaaa
000000F555578118:   61af1400 00040000 16623135 32393561 61616161  a........b15295aaaaa
000000F55557812C:   61616161 61b01400 00040000 16623135 32393661  aaaaa........b15296a
000000F555578140:   61616161 61616161 61b11400 00040000 16623135  aaaaaaaaa........b15
...

從葉子節點上看，也是 (b,c,key) 的佈局模式，這時候腦子裡就有了一張圖。

用同樣的方式觀察下 Include索引，發現 IndexLevel=1，說明只有一層。

再用 DBCC 觀察下分支節點的佈局。


PAGE: (1:1696)

Memory Dump @0x000000F554F78000

000000F554F78000:   01020001 00820001 00000000 00001100 00000000  ....................
000000F554F78014:   00000601 42010000 1c09d814 a0060000 01000000  ....B....	..........
000000F554F78028:   0f010000 78310000 39010000 00000000 00000000  ....x1..9...........
000000F554F7803C:   f01efa04 00000000 00000000 00000000 00000000  ....................
000000F554F78050:   00000000 00000000 00000000 00000000 16623130  .................b10
000000F554F78064:   30303001 00000088 03000001 00030000 16623130  000..............b10
000000F554F78078:   33313138 010000b0 03000001 00030000 16623130  3118.............b10
000000F554F7808C:   3632326f 020000b1 03000001 00030000 16623130  622o.............b10
000000F554F780A0:   393333a6 030000b2 03000001 00030000 16623131  933..............b11
...

從輸出看並沒有記錄 列c 的值，就是那煩人的 aaaaaaaaaa，然後再抽個葉子節點看看，比如：1218號索引頁。


PAGE: (1:1218)
Memory Dump @0x000000F554F78000

000000F554F78000:   01020000 04020001 c1040000 01001500 c3040000  ....................
000000F554F78014:   01003701 42010000 0a00881d c2040000 01000000  ..7.B...............
000000F554F78028:   0f010000 00310000 03000000 00000000 00000000  .....1..............
000000F554F7803C:   e7351886 00000000 00000000 00000000 00000000  .5..................
000000F554F78050:   00000000 00000000 00000000 00000000 16623833  .................b83
000000F554F78064:   313235a6 1d010061 61616161 61616161 61040000  125....aaaaaaaaaa...
000000F554F78078:   16623833 313236a7 1d010061 61616161 61616161  .b83126....aaaaaaaaa
000000F554F7808C:   61040000 16623833 313237a8 1d010061 61616161  a....b83127....aaaaa
000000F554F780A0:   61616161 61040000 16623833 313238a9 1d010061  aaaaa....b83128....a
000000F554F780B4:   61616161 61616161 61040000 16623833 313239aa  aaaaaaaaa....b83129.
000000F554F780C8:   1d010061 61616161 61616161 61040000 16623833  ...aaaaaaaaaa....b83
000000F554F780DC:   313330ab 1d010061 61616161 61616161 61040000  130....aaaaaaaaaa...
...

在葉子節點中我們終於看到了 aaaaaaaaaa ，其實想一想肯定是有的，不然怎麼做索引覆蓋呢？有了這些資訊，腦子中又有了一張圖。

從圖中可以看出，Include索引 的分支節點是不包含 c 列的，這個列只會儲存在 葉子節點 中，再結合樹的高度來看就能解釋為什麼 Include索引 的邏輯讀要少於 複合索引。

三：總結

總的來說 複合索引 和 Include索引 各有利弊吧，前者會讓索引頁的行資料更大，導致索引頁更多，也就會佔用更多的儲存空間，更多的邏輯讀，索引維護開銷也更大，而後者只會將 Include 列 儲存在葉子節點，不參與索引計算，相對來說佔用的索引頁空間更小。

在查詢方面，複合索引能達到的索引覆蓋場景遠大於單列索引，而且在過濾，排序場景下也能發揮奇效，所以還是根據你的讀寫比例做一個取捨吧。