?一、背景
1. 講故事
在 SQLSERVER 中有非常多的索引，比如：聚集索引，非聚集索引，唯一索引，復(fù)合索引，Include索引，交叉索引，連接索引，奇葩索引等等，當(dāng)索引多了之后很容易傻傻的分不清，比如：??復(fù)合索引??? 和 ??Include索引??，但又在真實(shí)場(chǎng)景中用的特別多，本篇我們就從底層數(shù)據(jù)頁(yè)層面厘清一下。
二、到底有什么區(qū)別
1. 這些索引解決了什么問(wèn)題
說(shuō)區(qū)別之前，一定要知道它們大概解決了什么問(wèn)題？這里我就從 ??索引覆蓋?? 角度來(lái)展開(kāi)吧，為了方便講述，先上一個(gè)測(cè)試 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;
代碼非常簡(jiǎn)單，在 t 表中創(chuàng)建三個(gè)列，插入 8w 條數(shù)據(jù)，然后創(chuàng)建兩個(gè)索引，接下來(lái)做一個(gè)查詢獲取 ??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”。掃描計(jì)數(shù) 8，邏輯讀取次數(shù) 30，物理讀取次數(shù) 0，頁(yè)面服務(wù)器讀取次數(shù) 0，預(yù)讀讀取次數(shù) 0，頁(yè)面服務(wù)器預(yù)讀讀取次數(shù) 0，LOb 邏輯讀取次數(shù) 0，LOB 邏輯讀取次數(shù) 0，LOB 頁(yè)面服務(wù)器讀取次數(shù) 0，LOB 預(yù)讀讀取次數(shù) 0，LOB 頁(yè)面服務(wù)器預(yù)讀讀取次數(shù) 0。
SQL Server 執(zhí)行時(shí)間:
CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 134 毫秒。
SQL Server 執(zhí)行時(shí)間:
CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 0 毫秒。
Completion time: 2023-01-06T08:47:45.2364473+08:00
從執(zhí)行計(jì)劃看，這是一個(gè)經(jīng)典的 書(shū)簽查找?，這種查找返回的行數(shù)越多性能越差，在索引優(yōu)化時(shí)一般都會(huì)規(guī)避掉這種情況，我們也看到了邏輯讀取次數(shù)有 30 次，那能不能再小一點(diǎn)呢？
為了解決這個(gè)問(wèn)題，干脆把 c 列也放到索引中去達(dá)到索引覆蓋的效果，這就需要用到 復(fù)合索引 了，參考sql如下：
CREATE INDEX idx_complex ON t (b,c)
再次查詢輸出如下：
SQL Server 分析和編譯時(shí)間:
CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 0 毫秒。
表“t”。掃描計(jì)數(shù) 8，邏輯讀取次數(shù) 24，物理讀取次數(shù) 0，頁(yè)面服務(wù)器讀取次數(shù) 0，預(yù)讀讀取次數(shù) 0，頁(yè)面服務(wù)器預(yù)讀讀取次數(shù) 0，LOb 邏輯讀取次數(shù) 0，LOB 邏輯讀取次數(shù) 0，LOB 頁(yè)面服務(wù)器讀取次數(shù) 0，LOB 預(yù)讀讀取次數(shù) 0，LOB 頁(yè)面服務(wù)器預(yù)讀讀取次數(shù) 0。
SQL Server 執(zhí)行時(shí)間:
CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 96 毫秒。
SQL Server 執(zhí)行時(shí)間:
CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 0 毫秒。
Completion time: 2023-01-06T08:53:56.9688921+08:00
從執(zhí)行計(jì)劃來(lái)看，這次沒(méi)有走 書(shū)簽查找? 而是 索引查找?，并且邏輯讀也降到了 24 次，這是一個(gè)好的優(yōu)化。
相信有些朋友也知道用 Include索引 也能達(dá)到這個(gè)效果，接下來(lái)試著把復(fù)合索引給刪了增加一個(gè) Include索引，代碼如下：
DROP INDEX idx_complex ON dbo.t;
CREATE INDEX idx_include ON t(b) INCLUDE (c)
再次查詢輸出如下：
表“t”。掃描計(jì)數(shù) 8，邏輯讀取次數(shù) 16，物理讀取次數(shù) 0，頁(yè)面服務(wù)器讀取次數(shù) 0，預(yù)讀讀取次數(shù) 0，頁(yè)面服務(wù)器預(yù)讀讀取次數(shù) 0，LOb 邏輯讀取次數(shù) 0，LOB 邏輯讀取次數(shù) 0，LOB 頁(yè)面服務(wù)器讀取次數(shù) 0，LOB 預(yù)讀讀取次數(shù) 0，LOB 頁(yè)面服務(wù)器預(yù)讀讀取次數(shù) 0。
SQL Server 執(zhí)行時(shí)間:
CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 73 毫秒。
SQL Server 執(zhí)行時(shí)間:
CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 0 毫秒。
Completion time: 2023-01-06T08:58:18.1122561+08:00
從執(zhí)行計(jì)劃來(lái)看也是走的 非聚集索引?，而且邏輯讀再次降到了 16? 次，相比原始的書(shū)簽查找已經(jīng)優(yōu)化了 50%，這是一個(gè)巨大的性能提升不是。
到這里其實(shí)有一個(gè)問(wèn)題，兩種優(yōu)化走的都是 非聚集索引?，從邏輯讀次數(shù)看貌似 Include索引 更好一些，為什么會(huì)這樣呢？這就涉及到了底層存儲(chǔ)，接下來(lái)一起扒一下。
2. 存儲(chǔ)原理研究
研究它們的不同點(diǎn)，最徹底的方式就是從底層存儲(chǔ)出發(fā)，首先我們觀察下 復(fù)合索引? 的底層存儲(chǔ)是什么樣的，可以用 DBCC 命令。
DBCC TRACEON(3604)
DBCC IND(MyTestDB,t,-1)
從 IndexLevel=2? 來(lái)看這個(gè)復(fù)合索引?構(gòu)成的B樹(shù)已經(jīng)達(dá)到了二層，接下來(lái)我們查一下 368 號(hào)數(shù)據(jù)頁(yè)內(nèi)容。
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 執(zhí)行完畢。如果 DBCC 輸出了錯(cuò)誤信息，請(qǐng)與系統(tǒng)管理員聯(lián)系。
根據(jù)下面的 Slot 個(gè)數(shù)可以知道這個(gè)分支節(jié)點(diǎn)數(shù)據(jù)頁(yè)只有 2 條記錄，分別為：(b10000,aaaaaaaaaa,0x01) , (b83616,aaaaaaaaaa,0x011f91)?，這里說(shuō)明一下最后的 01 和 0x011f91 是主鍵key，接下來(lái)找個(gè)葉子節(jié)點(diǎn)，比如：1632 號(hào)索引頁(yè)。
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
...
從葉子節(jié)點(diǎn)上看，也是 (b,c,key) 的布局模式，這時(shí)候腦子里就有了一張圖。
用同樣的方式觀察下 Include索引?，發(fā)現(xiàn) IndexLevel=1，說(shuō)明只有一層。
再用 DBCC 觀察下分支節(jié)點(diǎn)的布局。
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
...
從輸出看并沒(méi)有記錄 列c? 的值，就是那煩人的 aaaaaaaaaa，然后再抽個(gè)葉子節(jié)點(diǎn)看看，比如：1218號(hào)索引頁(yè)。
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...
...
在葉子節(jié)點(diǎn)中我們終于看到了 aaaaaaaaaa ，其實(shí)想一想肯定是有的，不然怎么做索引覆蓋呢？有了這些信息，腦子中又有了一張圖。
從圖中可以看出，Include索引? 的分支節(jié)點(diǎn)是不包含 c? 列的，這個(gè)列只會(huì)保存在 葉子節(jié)點(diǎn)? 中，再結(jié)合樹(shù)的高度來(lái)看就能解釋為什么 Include索引? 的邏輯讀要少于 復(fù)合索引。
三、總結(jié)
總的來(lái)說(shuō) 復(fù)合索引? 和 Include索引? 各有利弊吧，前者會(huì)讓索引頁(yè)的行數(shù)據(jù)更大，導(dǎo)致索引頁(yè)更多，也就會(huì)占用更多的存儲(chǔ)空間，更多的邏輯讀，索引維護(hù)開(kāi)銷也更大，而后者只會(huì)將 Include 列 保存在葉子節(jié)點(diǎn)，不參與索引計(jì)算，相對(duì)來(lái)說(shuō)占用的索引頁(yè)空間更小。
在查詢方面，復(fù)合索引能達(dá)到的索引覆蓋場(chǎng)景遠(yuǎn)大于單列索引，而且在過(guò)濾，排序場(chǎng)景下也能發(fā)揮奇效，所以還是根據(jù)你的讀寫(xiě)比例做一個(gè)取舍吧。
