SQL Server中INCLUDE与WHERE在索引中的正确用法及最优索引构建

一、索引键列与INCLUDE子句的判断标准 #

键列的适用场景 #

• 用于WHERE子句的等值/范围过滤条件，依赖索引的有序性快速定位数据范围。
• 参与JOIN、ORDER BY、GROUP BY的字段，利用索引有序性减少额外排序/分组操作。
• 需要作为索引筛选逻辑的核心字段，缩小索引扫描的数据集。

INCLUDE子句的适用场景 #

• 仅出现在SELECT列表中，不参与过滤、排序、分组的字段（即“覆盖列”），避免增大索引体积。
• 大体积字段（如VARCHAR(MAX)、TEXT），这类字段加入键列会大幅降低索引性能。
• 不需要利用其有序性，仅为了避免回表查询的字段。

二、针对当前查询的优化建议 #

当前查询需求：筛选未软删除数据，指定客户ID和日期范围，求主键ID的最小/最大值。结合你的表数据分布（1000个客户、6年数据），给出以下优化方案：

1. 重构筛选索引结构 #

你的现有索引键列顺序不合理，等值条件字段应放在范围条件字段之前，调整后的索引如下：

CREATE NONCLUSTERED INDEX [IX_MyBigTable_Customer_Date] ON [MyBigTable] (
    [CustomerID] ASC,
    [NumericDate] ASC
)
INCLUDE ([PrimaryKeyId])
WHERE [SoftDeleted] = 0

优化逻辑：

• SoftDeleted=0作为筛选条件，直接在索引层面过滤已删除数据，减少索引总行数。
• CustomerID是等值查询，放在键列首位，快速锁定单个客户的数据集。
• NumericDate是范围查询，放在键列第二位，在客户数据范围内快速筛选日期区间。
• PrimaryKeyId仅用于聚合计算，放在INCLUDE子句即可，无需加入键列，避免索引体积膨胀。
• 无需INCLUDESoftDeleted，筛选索引已限定该条件，查询的WHERE判断会被索引自动覆盖。

2. 验证新索引性能（强制指定索引） #

若优化器仍选择旧索引，可临时用FORCE INDEX验证新索引的性能：

declare @date1 int = 45442;    -- 对应'2024-06-01'
declare @date2 int = 45471;    -- 对应'2024-06-30'
declare @customerID int = 123;

select min(PrimaryKeyId), max(PrimaryKeyId)
from MyBigTable WITH (FORCE INDEX (IX_MyBigTable_Customer_Date))
where 
   NumericDate >= @date1  
   and NumericDate <= @date2 
   and CustomerID = @customerID
   and SoftDeleted = 0

若新索引性能更优，执行全量统计更新，帮助优化器做出正确选择：

UPDATE STATISTICS MyBigTable WITH FULLSCAN;

3. 清理冗余旧索引 #

检查被选中的大型旧索引，若其仅因“覆盖查询”被选中但体积过大，在确认新索引满足所有依赖后，可禁用或删除该旧索引，避免优化器误选。

4. 关于列存储索引的说明 #

非聚集列存储索引适合大规模批量分析，当前查询是单客户小范围聚合，行存储筛选索引的性能更适配。若要尝试列存储索引，需确保其包含所有必要字段，并更新统计信息帮助优化器识别。

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