如何选择最优索引提升指定SQL查询的执行性能

慢查询核心原因 #

当前仅在date字段创建非聚集索引，即使触发索引查找，索引未覆盖查询需要的CustomerId、DocumentNumber、IsCash字段，每次命中日期范围的行都需要回表读取数据；同时原写法对同一份日期范围数据做两次自关联扫描，IO和计算开销都很高。

索引优化建议 #

不需要为bit类型的IsCash字段创建单列索引：bit类型字段基数极低（只有0/1两个取值），单列索引筛选度极差，数据库优化器几乎不会选择这类索引，创建后没有实际收益。
你需要创建适配查询的覆盖组合索引，避免回表开销：

CREATE NONCLUSTERED INDEX IX_Table1_Date_CoverQuery
ON Table1 (date)
INCLUDE (DocumentNumber, CustomerId, IsCash)

如果单月数据量超过百万行，可将索引键调整为(date, DocumentNumber)，包含列保留CustomerId, IsCash，能进一步优化同单据号匹配的计算效率。

查询逻辑改写 #

原CTE写法需要两次扫描日期范围内的数据做自关联，可以用窗口函数改写为单次扫描，逻辑完全等价，性能提升明显：

SELECT DISTINCT CustomerId
FROM (
    SELECT
        CustomerId,
        IsCash,
        SUM(CASE WHEN IsCash = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY DocumentNumber) AS CashRecordCount
    FROM Table1
    WHERE date >= '2022-05-21' AND date < '2022-06-21'
) t
WHERE IsCash = 0 AND CashRecordCount > 0

改写逻辑：按DocumentNumber分区统计每个单据号下是否存在IsCash=1的记录，直接筛选出非现金支付、且同单据存在现金支付记录的客户，不需要做自关联匹配。

额外优化点 #

• 如果业务上单个DocumentNumber仅归属一个CustomerId，可以去掉DISTINCT，通过分区取第一条的方式减少排序去重开销，进一步降低计算量。
• 日期条件建议将date > '2022-05-21'调整为date >= '2022-05-21'，避免漏掉5月21日0点的边界数据，同时不影响索引查找效率。

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