​一、复合背景

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 ONDECLARE @num INTSET @num =10000WHILE (@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;1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.

代码非常简单，在 t 表中创建三个列，插入 8w 条数据，然后创建两个索引，接下来做一个查询获取 ​​b,c​​ 列。

复制

SET STATISTICS IO ONSET STATISTICS TIME ONSELECT b,c FROM t WHERE b IN (b10000,b20000,b30000,b40000,b50000,b70000,b80000,b90000)SET

STATISTICS IO OFF

SET STATISTICS TIME

OFF

1.2.3.4.5.6.

输出如下：

复制

表“t”。扫描计数 8，逻辑读取次数 30，物理读取次数 0，页面服务器读取次数 0，预读读取次数 0，页面服务器预读读取次数 0，LOb 逻辑读取次数 0，b2b供应网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:001.2.3.4.5.6.7.8.9.10.

从执行计划看，这是一个经典的 书签查找​，这种查找返回的行数越多性能越差，在索引优化时一般都会规避掉这种情况，我们也看到了逻辑读取次数有 30 次，那能不能再小一点呢？

为了解决这个问题，干脆把 c 列也放到索引中去达到索引覆盖的效果，这就需要用到 复合索引 了，参考sql如下：

复制

CREATE INDEX idx_complex ON t (b,c)1.2.

再次查询输出如下：

复制

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:001.2.3.4.5.6.7.8.9.10.11.12.

从执行计划来看，这次没有走 书签查找​ 而是 索引查找​，并且逻辑读也降到了 24 次，这是一个好的优化。

相信有些朋友也知道用 Include索引 也能达到这个效果，接下来试着把复合索引给删了增加一个 Include索引，代码如下：

复制

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

再次查询输出如下：

复制

表“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:001.2.3.4.5.6.7.8.9.10.

从执行计划来看也是走的 非聚集索引​，高防服务器而且逻辑读再次降到了 16​ 次，相比原始的书签查找已经优化了 50%，这是一个巨大的性能提升不是。

到这里其实有一个问题，两种优化走的都是 非聚集索引​，从逻辑读次数看貌似 Include索引 更好一些，为什么会这样呢？这就涉及到了底层存储，接下来一起扒一下。

2. 存储原理研究

研究它们的不同点，最彻底的方式就是从底层存储出发，首先我们观察下 复合索引​ 的底层存储是什么样的，可以用 DBCC 命令。

复制

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

从 IndexLevel=2​ 来看这个复合索引​构成的B树已经达到了二层，接下来我们查一下 368 号数据页内容。

复制

DBCC PAGE(MyTestDB,1,368,2)1.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 输出了错误信息，请与系统管理员联系。

1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.

根据下面的 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 287

aaaaaaaaaa.......

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 292

aaaaaaaaaa.......

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

...

1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.

从叶子节点上看，也是 (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 622

o.............b10

000000F554F780A0: 393333a6 030000b2 03000001 00030000 16623131 933

..............b11

...

1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.

从输出看并没有记录 列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 1

d010061 aaaaa....b83128....a

000000F554F780B4: 61616161 61616161 61040000 16623833 313239

aa aaaaaaaaa....b83129.

000000F554F780C8: 1d010061 61616161 61616161 61040000 16623833

...aaaaaaaaaa....b83

000000F554F780DC: 313330ab 1d010061 61616161 61616161 61040000 130

....aaaaaaaaaa...

...

1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.

在叶子节点中我们终于看到了 aaaaaaaaaa ，其实想一想肯定是有的，不然怎么做索引覆盖呢？有了这些信息，脑子中又有了一张图。

从图中可以看出，Include索引​ 的分支节点是不包含 c​ 列的，这个列只会保存在 叶子节点​ 中，再结合树的高度来看就能解释为什么 Include索引​ 的逻辑读要少于 复合索引。

三、总结

总的来说 复合索引​ 和 Include索引​ 各有利弊吧，前者会让索引页的行数据更大，导致索引页更多，也就会占用更多的存储空间，更多的逻辑读，索引维护开销也更大，而后者只会将 Include 列 保存在叶子节点，不参与索引计算，相对来说占用的索引页空间更小。

在查询方面，复合索引能达到的索引覆盖场景远大于单列索引，而且在过滤，排序场景下也能发挥奇效，所以还是根据你的读写比例做一个取舍吧。