复合索引、INCLUDE关键字的作用及索引性能差异解析

针对高频查询SELECT a, b FROM MyTable WHERE c = @val1 AND d = @val2，ix1（CREATE INDEX ix1 ON MyTable (c, d, a, b)）和ix2（CREATE INDEX ix2 ON MyTable (c, d) INCLUDE (a, b)）是性能更优的覆盖索引，其中ix2最优。以下是对两个疑问的解答：

1. ix1中列的顺序是否重要？原因是什么？ #

列的顺序非常重要，核心原因和B树索引的排序特性直接相关：

• 过滤效率的影响：B树索引是按列顺序逐层排序的，查询的过滤条件是c = @val1 AND d = @val2，把c放在最前面，数据库可以快速定位到所有c等于目标值的索引分支；再在这个分支里找d等于目标值的行，大幅缩小扫描范围。如果把列顺序换成d, c, a, b，查找时需要遍历所有d的分支才能匹配c的值，效率会大幅下降。
• 索引体积与维护成本：a、b作为键列会参与B树的全节点排序，这会让索引的非叶节点和叶节点都包含这两列，导致索引体积更大；同时插入、更新、删除数据时，数据库需要重新调整a、b的排序，增加维护开销。而如果只是为了覆盖查询返回的列，完全不需要让它们参与索引排序。

2. ix1和ix2的区别是什么？INCLUDE关键字的作用是什么？ #

两者的核心差异在于a、b在索引中的存储方式，INCLUDE关键字的作用也由此体现：

• 索引结构差异：
  • ix1里的a、b是索引键列，会参与B树所有节点（非叶节点+叶节点）的排序，整个索引的每个节点都包含c, d, a, b四列；
  • ix2里的a、b是包含列，仅存在于索引的叶节点，非叶节点只存储c、d。这会让非叶节点的体积更小，单个节点能容纳更多索引项，减少磁盘IO次数，这也是ix2性能更优的关键原因。
• 维护成本差异：
  • ix1因为键列数量多，数据变更时需要重新调整四列的排序，维护开销更大；
  • ix2只需要维护c、d的排序，a、b不需要参与排序，维护成本更低。
• INCLUDE关键字的作用：它能在不增加索引键列的前提下，将查询需要返回的列添加到索引叶节点，形成覆盖索引，避免回表查询。这样既保证了过滤条件的高效查找，又提供了查询所需的数据，同时有效控制了索引的体积和维护成本。

内容的提问来源于stack exchange，提问作者emrebener