smalldatetime列非聚集索引查询Message字段时失效原因咨询

首先明确SQL Server非聚集索引的存储规则：非聚集索引的叶子节点默认只存储索引键值 + 聚集索引键，你表中的聚集索引键是ID列，所以Datestamp的非聚集索引仅存储了Datestamp和ID两个字段的内容。

第一个查询走索引的原因 #

你执行的select ID from [Log] where [Datestamp] > '2020-01-01' and [Datestamp] < '2021-09-01' order by datestamp语句，需要的所有字段（ID、Datestamp）都已经包含在Datestamp的非聚集索引里，这个索引属于该查询的覆盖索引：

• 可以直接用索引完成Datestamp的范围过滤
• 索引本身是按Datestamp有序存储的，不需要额外Sort排序
• 不需要回表查询其他数据，整体IO成本极低，所以优化器选择走该非聚集索引。

第二个查询不走索引的核心原因 #

当你查询Message字段时，Datestamp的非聚集索引里没有存储Message的内容，此时如果选择走该非聚集索引，需要额外执行**键查找（Key Lookup）**操作：对每一条符合过滤条件的记录，拿着ID去聚集索引里查找对应的Message值。
如果你的过滤条件返回的行数占总表比例较高（你这个语句过滤了近20个月的日志，通常返回量都很大），优化器会判定：大量随机IO的键查找操作，成本远高于直接扫描全表（聚集索引扫描）后做排序的成本，因此会选择不走非聚集索引，转而走全表扫描+Sort的执行计划，就出现了你遇到的性能问题。

如果要让该查询也能走非聚集索引，可以创建带包含列的覆盖索引，把查询需要的Message字段加入索引的包含列中：

CREATE NONCLUSTERED INDEX IX_Log_Datestamp_INCLUDE_Message 
ON [Log] (Datestamp)
INCLUDE (Message);

创建完成后，该索引存储了Datestamp、ID、Message三个字段的全部内容，不需要回表即可完成查询，同时索引本身按Datestamp有序，不需要额外Sort操作，性能会大幅提升。

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