SQL Server未使用data_doc索引，如何优化索引提升大表查询效率？

核心原因 #

你的查询中使用YEAR(data_doc) = 2023 AND MONTH(data_doc) = 10的写法，会导致SQL Server无法直接利用data_doc上的索引。因为函数运算会破坏列的有序性，优化器无法通过索引快速定位目标数据，最终判断全表扫描的成本更低，因此跳过了索引。

具体解决方案 #

1. 改写查询条件（优先推荐）

将函数筛选改为直接的日期范围匹配，让优化器可以利用data_doc的有序性做范围扫描：

SELECT * 
FROM [Table] 
WHERE data_doc >= '2023-10-01' 
  AND data_doc < '2023-11-01';

这种写法完全保留了data_doc的索引可用性，是最直接有效的优化方式。

2. 创建适配的索引

如果无法修改查询语句（比如视图定义固定），可以通过以下两种索引方式解决：

方式一：计算列索引

针对YEAR(data_doc)和MONTH(data_doc)创建计算列索引，让优化器可以直接匹配函数筛选条件：

-- 先添加持久化计算列
ALTER TABLE [Table] 
ADD DocYear AS YEAR(data_doc) PERSISTED,
    DocMonth AS MONTH(data_doc) PERSISTED;

-- 创建包含所有返回列的覆盖索引
CREATE INDEX ix_Data_Doc_YearMonth_Covering 
ON [Table](DocYear, DocMonth)
INCLUDE (data_doc, OperationDetail_Id, OperationTraceOut_Id, OperationTraceIn_Id, [其他所有需要返回的列]);

如果不想添加持久化计算列，也可以直接在索引中使用表达式（SQL Server 2012+支持）：

CREATE INDEX ix_Data_Doc_YearMonth_Covering 
ON [Table](YEAR(data_doc), MONTH(data_doc))
INCLUDE (data_doc, OperationDetail_Id, OperationTraceOut_Id, OperationTraceIn_Id, [其他所有需要返回的列]);

方式二：覆盖范围索引

如果采用了改写后的日期范围查询，创建以data_doc为键列的覆盖索引，包含所有需要返回的列（因为你用了SELECT *，需要覆盖视图涉及的全部57列）：

CREATE INDEX ix_Data_Doc_Covering 
ON [Table](data_doc)
INCLUDE (OperationDetail_Id, OperationTraceOut_Id, OperationTraceIn_Id, [其他所有需要返回的列]);

覆盖索引的优势是优化器可以直接从索引中获取所有需要的数据，无需回表查询聚集索引，大幅提升效率。

3. 辅助优化步骤

• 更新统计信息：确保表的统计信息是最新的，优化器才能准确判断索引成本：

  UPDATE STATISTICS [Table];
  

• 检查视图定义：如果视图包含多表关联或额外计算，需要确保关联条件也有对应的索引，避免关联过程中的性能瓶颈。

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