单列非聚集索引查询耗时更长、加INCLUDE后性能恢复原因咨询

现象原因解析 #

你观察到的性能差异是SQL Server非聚集索引工作机制的典型表现，核心和「回表开销」「覆盖索引优化」两个特性相关，具体拆解如下：

• 无额外非聚集索引时（551ms）
  测试前你已经删除了表的聚集主键，此时TestTable属于堆表。查询WHERE Continent = 'Asia'没有可用索引，数据库直接执行全表扫描，逐行匹配筛选条件后直接返回行数据，只有顺序读IO开销。
• 仅创建Continent列非聚集索引时（1083ms）
  仅含Continent的非聚集索引叶子节点仅存储两部分数据：索引键Continent的值，以及对应堆表行的RID（行标识符）。你的查询要求返回*，需要读取Location列，但该列不在非聚集索引中，因此数据库每匹配到一条符合条件的索引记录，都需要执行一次RID查找（回表操作），到堆表中读取对应的Location值。
  当筛选出的Asia相关行数较多时，大量随机IO的回表操作总开销远高于全表扫描的顺序IO开销，最终导致耗时直接翻倍。
• 添加INCLUDE (Location)后（530ms）
  INCLUDE关键字会将指定的非键列存储在非聚集索引的叶子节点上，此时这个非聚集索引已经覆盖了你查询需要的所有字段：Continent用于筛选，Location用于结果返回，也就是常说的覆盖索引。
  查询时仅需要扫描非聚集索引就能拿到全部需要的数据，完全不需要回表操作，性能自然比全表扫描更优。

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