1。背景一、讲故事SQLServer中有很多索引，例如：聚集索引、非聚集索引、唯一索引、复合索引、Include索引、交叉索引、连接索引、wonderful索引等。当索引很容易太多的时候容易混淆，比如：Compositeindex和Includeindex，但是在实际场景中用的很多。在本文中，我们将从底层数据页面层面进行一次梳理。2.有什么区别？1、这些指标解决了什么问题？在谈差异之前，你必须知道它们解决的是什么问题？这里我从索引覆盖的角度来展开。为了说的方便，先测试sql:IF(OBJECT_ID('t')ISNOTNULL)DROPTABLEt;CREATETABLEt(aINTIDENTITY,bCHAR(6),cCHAR(10)DEFAULT'aaaaaaaaaa')SETNOCOUNTONDECLARE@numINTSET@num=10000WHILE(@num<90000)BEGININSERTINTOt(b)VALUES('b'+CAST(@numASCHAR(5)))SET@num=@num+1ENDCREATECLUSTEREDINDEXidx_aONt(a)CREATEINDEXidx_bONt(b)SELECT*FROMt;代码很简单，在t表中创建三列，插入8w条数据，然后创建两个索引，然后查询得到b,c列。设置统计数据IOOFFSETSTATISTICSTIMEOFF的输出如下：表“t”。扫描计数8，逻辑读取30，物理读取0，页面服务器读取0，预读读取0，页面服务器预读读取0，LOb逻辑读取0，LOB逻辑读取次数0，LOB页面服务器读取次数0，LOB读取-提前读取次数0，LOB页面服务器提前读取次数0。SQLServer执行时间：CPU时间=0毫秒，运行时间=134毫秒。SQLServer执行时间：CPU时间=0毫秒，运行时间=0毫秒。完成时间：2023-01-06T08:47:45.2364473+08:00从执行计划来看，这是一次经典的书签查找。这种查找返回的行越多，性能就越差，一般在索引优化时会避免。在本例中，我们也看到了逻辑读的数量是30，那能不能再小一点呢？为了解决这个问题，干脆把c列放到索引中，达到索引覆盖的效果。这就需要使用复合索引。参考如下sql：CREATEINDEXidx_complexONt(b,c)然后查询输出如下：SQLServer分析编译时间：CPUtime=0ms,elapsedtime=0ms。药片”。扫描计数8，逻辑读取24，物理读取0，页面服务器读取0，预读读取0，页面服务器预读读取0，LOb逻辑读取0，LOB逻辑读取次数0，LOB页面服务器读取次数0，LOB读取-提前读取次数0，LOB页面服务器提前读取次数0。SQLServer执行时间：CPU时间=0毫秒，运行时间=96毫秒。SQLServer执行时间：CPU时间=0毫秒，运行时间=0毫秒。完成时间：2023-01-06T08:53:56.9688921+08:00从执行计划来看，这次没有书签查找而是索引查找，逻辑读减少到24次，不错优化。相信有的朋友也知道使用Include索引也可以达到这样的效果。接下来尝试删除复合索引并添加一个Include索引。代码如下：DROPINDEXidx_complexONdbo.t;CREATEINDEXidx_includeONt(b)INCLUDE(c)再次查询输出如下：table"t"。扫描计数8，逻辑读取16，物理读取0，页面服务器读取0，预读读取0，页面服务器预读读取0，LOb逻辑读取0，LOB逻辑读取次数0，LOB页面服务器读取次数0，LOB读取-提前读取次数0，LOB页面服务器提前读取次数0。SQLServer执行时间：CPU时间=0毫秒，运行时间=73毫秒。SQLServer执行时间：CPU时间=0毫秒，运行时间=0毫秒。完成时间：2023-01-06T08:58:18.1122561+08:00从执行计划来看，也是非聚集索引，逻辑读又降到了16，优化了50%与原始书签查找相比，这并不是一个巨大的性能提升。这里其实有一个问题。两种优化都使用非聚集索引。从逻辑读取次数来看，似乎Includeindexes更好一些。为什么会这样？这就涉及到底层存储了，我们一起来看看吧。2.存储原理研究研究它们的区别最彻底的方法就是从底层存储入手。首先我们观察复合索引的底层存储是什么样子的，可以使用DBCC命令。DBCCTRACEON(3604)DBCCIND(MyTestDB,t,-1)从IndexLevel=2来看，这个复合索引形成的B-tree已经到了二级。接下来我们查看368号数据页的内容。DBCC页面的输出（MyTestDB，1,368,2）如下：PAGE：（1：368）存储器转储@0x00000000F5555557800000000000000000000000000000000000000000000000000000000000000000000000000005578000：010200020080000001000000100000000000000000000000000000000000000000000000000000000000000000000000000000000来...`...p....000000F555578028：f8000000e0680000f50100000000000000000000.....h............000000F55557803C：000000000100000000000000...00000000000000.....000000F555578050:0000000000000000000000000000000016623.......13....b10000000F555578064:3030306161616161616161616101000000380500000aaaaaaaaaa....8..000000F555578078:0001000400001662383336313661616161616161.......b83616aaaaaaa000000F55557808C:616161911f010070050000010004000000006231aaa....p.....b1OFFSET表：行-偏移量1(0x1)-126(0x7e)0(0x0)6-96DBCC执行完成。如果DBCC输出错误消息，请联系您的系统管理员。根据下面的槽数，我们可以知道这个分支节点数据页中只有2条记录，分别是：(b10000,aaaaaaaaaa,0x01),(b83616,aaaaaaaaaa,0x011f91)。这里说明一下，最后的01和0x011f91是主键。接下来，找到一个叶子节点，例如：indexpage1632。页面：（1：1632）内存转储@0x000000F5555578000...000000F5555578050：00000000000000000000000000000000000000000000000000000000000000000000000000000000近报16623135............................................................................................................................................................................................................................................................................................................................................................................................................000000F555578078:1662313532383861616161616161616161a91400.b15288aaaaaaaaaa...000000F55557808C:0004000016623135323839616161616161616161.....b15289aaaaaaaaa000000F5555780A0:61aa140000040000166231353239306161616161a........b15290aaaaa000000F5555780B4:6161616161ab1400000400001662313532393161aaaaa........b15291a000000F5555780C8:616161616161616161ac14000004000016623135aaaaaaaaa........b15000000F5555780DC:32393261616161616161616161ad140000040000292aaaaaaaaaa.......000000F5555780F0:1662313532393361616161616161616161ae1400.b15293aaaaaaaaaa...000000F555578104：0004000016623135323934616161616161616161.....b15294aaaaaaaaaaaaaaaaaaaaaaaaaa000000F555578118：61af1400000400001662313556161616161a........b15295aaaaa000000F55557812C:6161616161b01400000400001662313532393661aaaaa........b15296a000000F555578140:616161616161616161b114000004000016623135aaaaaaaaa........b15...从叶子节点从上面看，也是(b,c,key)的布局方式。这时，脑海里浮现出一幅画面。同理观察Include索引，发现IndexLevel=1，说明只有一层。然后用DBCC观察分支节点的布局。PAGE：（1：1696）内存转储@0x000000F554F7800000000000F5554F78000：0102000100000000000000000000000000000000000000000000000000000000000000000000000000000000000000来......000000F554F7803C：F01EFA0400000000000000000000000000000000000000000000000000000000000000000....................................................000000F554F78050：00000000000000000000000000000000000000000000000000000000000000000000000000000000来03000001000300301.30616.....................................................................................................................................................33313138：33313138010000B00300000100030000100300001662313031130003000016623131933........b11...从输出来看，没有记录c列的值，就是烦人的aaaaaaaaaa，再看一个叶子节点，例如：index第1218页。PAGE：（1：1218）内存转储@0x000000F554F7800000000000000000F554F78000：0102000004020001C104000001001500C3040000....................................000000F554F78028：0f01000000310000030000000000000000000000......1.....................000000F554F7803C：e735188600000000000000000000000000050...................000000F554F78050：0000000000000000000000000000000016623833.........b83000000F554F78064：313235a61d010061616161616161614616161612016....aaaaaaaaaa...000000F554F78078:16623833313236a71d0100616161616161616161.b83126....aaaaaaaaa000000F554F7808C:6104000016623833313237a81d01006161616161a....b83127....aaaaa000000F554F780A0:616161616104000016623833313238a91d010061aaaaa....b83128....a000000F554F780B4：61616161616161616104000016623833313239aaaaaaaaaaa....b83129.000000F554F780C8：1d010061616161601616161616161616623833...aaaaaaaaaa....b83000000F554F780DC:313330ab1d010061616161616161616161040000130....aaaaaaaaaa...在叶子节点howtodo中我们终于看到了aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa关于索引覆盖率？有了这些信息，我的脑海里又多了一个画面。从图中可以看出Include索引的分支节点不包含c列。该列只会存储在叶子节点中，结合树的高度来看可以解释为什么Include索引的逻辑读小于复合索引的逻辑读。3.小结总的来说，复合索引和Include索引各有优缺点。前者会使索引页的行数据变大，导致索引页变多，后者会占用更多的存储空间和更多的逻辑。read，索引维护开销也更大，而且后者只保存叶子节点中的Include列，不参与索引计算，相对来说索引页占用的空间更小。在查询方面，复合索引所能达到的索引覆盖场景比单列索引要大很多，而且在过滤排序场景也能起到奇效，还是取舍比较好根据你的读写比例。
