为何执行计划出现SELECT未包含列？SQL Server聚集键查找疑问

问题背景 #

执行以下查询时，已创建对应的非聚集覆盖索引，但SQL Server执行计划仍出现聚集键查找操作：

查询语句 #

SELECT top 100 RCLNT, RBUKRS, RYEAR, RACCT, DRCRK, SUM(ISNULL([000],0.00)) AS CSLVT,SUM(ISNULL([001],0.00)) AS CSL01,SUM(ISNULL([002],0.00)) AS CSL02,SUM(ISNULL([003],0.00)) AS CSL03,SUM(ISNULL([004],0.00)) AS CSL04,SUM(ISNULL([005],0.00)) AS CSL05,SUM(ISNULL([006],0.00)) AS CSL06,SUM(ISNULL([007],0.00)) AS CSL07,SUM(ISNULL([008],0.00)) AS CSL08,SUM(ISNULL([009],0.00)) AS CSL09,SUM(ISNULL([010],0.00)) AS CSL10,SUM(ISNULL([011],0.00)) AS CSL11,SUM(ISNULL([012],0.00)) AS CSL12,SUM(ISNULL([013],0.00)) AS CSL13,SUM(ISNULL([014],0.00)) AS CSL14,SUM(ISNULL([015],0.00)) AS CSL15,SUM(ISNULL([016],0.00)) AS CSL16
FROM dbo.ACDOCA WITH (NOLOCK)

PIVOT
(
MAX(ACDOCA.CSL) FOR ACDOCA.POPER IN ([000],[001],[002],[003],[004],[005],[006],[007],[008],[009],[010],[011],[012],[013],[014],[015],[016])
) AS ACDOC_PIV
GROUP BY RCLNT, RBUKRS, RYEAR, RACCT, DRCRK

已创建的非聚集索引 #

CREATE NONCLUSTERED INDEX [IX_ACDOCA_RCLNT_RBUKRS_RYEAR_RACCT_DRCRK_INCL_CSL_POPER] ON [dbo].[ACDOCA]
(
    [RCLNT] ASC,
    [RBUKRS] ASC,
    [RYEAR] ASC,
    [RACCT] ASC,
    [DRCRK] ASC
)
INCLUDE([CSL],[POPER]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

原因分析 #

1. 统计信息过时：SQL Server优化器依赖准确的统计信息判断执行计划。如果表的统计信息未更新，优化器可能错误估算数据分布，认为聚集索引扫描+查找的成本低于使用非聚集覆盖索引。
2. PIVOT运算符的执行逻辑限制：SQL Server的PIVOT语法在底层执行时，可能先对基础表进行全聚集索引扫描，再进行透视和分组操作，无法有效利用非聚集索引的有序性（分组列已作为索引键排序），导致优化器忽略覆盖索引。
3. 优化器对PIVOT的解析局限性：PIVOT的写法可能让优化器无法识别到非聚集索引已包含所有所需列，进而触发聚集键查找来获取无关列（实际不需要，但优化器误判）。

优化方案 #

1. 更新统计信息 #

先更新表的统计信息，帮助优化器做出正确的执行计划选择：

UPDATE STATISTICS dbo.ACDOCA WITH FULLSCAN;

2. 改写查询为条件聚合（推荐） #

放弃PIVOT语法，改用条件聚合实现相同逻辑。这种写法更直观，且优化器更容易识别并利用覆盖索引：

SELECT TOP 100 
    RCLNT, RBUKRS, RYEAR, RACCT, DRCRK,
    SUM(ISNULL(CASE WHEN POPER = '000' THEN CSL ELSE 0.00 END, 0.00)) AS CSLVT,
    SUM(ISNULL(CASE WHEN POPER = '001' THEN CSL ELSE 0.00 END, 0.00)) AS CSL01,
    SUM(ISNULL(CASE WHEN POPER = '002' THEN CSL ELSE 0.00 END, 0.00)) AS CSL02,
    SUM(ISNULL(CASE WHEN POPER = '003' THEN CSL ELSE 0.00 END, 0.00)) AS CSL03,
    SUM(ISNULL(CASE WHEN POPER = '004' THEN CSL ELSE 0.00 END, 0.00)) AS CSL04,
    SUM(ISNULL(CASE WHEN POPER = '005' THEN CSL ELSE 0.00 END, 0.00)) AS CSL05,
    SUM(ISNULL(CASE WHEN POPER = '006' THEN CSL ELSE 0.00 END, 0.00)) AS CSL06,
    SUM(ISNULL(CASE WHEN POPER = '007' THEN CSL ELSE 0.00 END, 0.00)) AS CSL07,
    SUM(ISNULL(CASE WHEN POPER = '008' THEN CSL ELSE 0.00 END, 0.00)) AS CSL08,
    SUM(ISNULL(CASE WHEN POPER = '009' THEN CSL ELSE 0.00 END, 0.00)) AS CSL09,
    SUM(ISNULL(CASE WHEN POPER = '010' THEN CSL ELSE 0.00 END, 0.00)) AS CSL10,
    SUM(ISNULL(CASE WHEN POPER = '011' THEN CSL ELSE 0.00 END, 0.00)) AS CSL11,
    SUM(ISNULL(CASE WHEN POPER = '012' THEN CSL ELSE 0.00 END, 0.00)) AS CSL12,
    SUM(ISNULL(CASE WHEN POPER = '013' THEN CSL ELSE 0.00 END, 0.00)) AS CSL13,
    SUM(ISNULL(CASE WHEN POPER = '014' THEN CSL ELSE 0.00 END, 0.00)) AS CSL14,
    SUM(ISNULL(CASE WHEN POPER = '015' THEN CSL ELSE 0.00 END, 0.00)) AS CSL15,
    SUM(ISNULL(CASE WHEN POPER = '016' THEN CSL ELSE 0.00 END, 0.00)) AS CSL16
FROM dbo.ACDOCA WITH (NOLOCK)
GROUP BY RCLNT, RBUKRS, RYEAR, RACCT, DRCRK;

3. 强制使用非聚集索引（备选） #

如果更新统计信息和改写查询无效，可以强制优化器使用目标索引，但不建议长期依赖该方法（可能因数据变化导致性能下降）：

SELECT top 100 RCLNT, RBUKRS, RYEAR, RACCT, DRCRK, SUM(ISNULL([000],0.00)) AS CSLVT,SUM(ISNULL([001],0.00)) AS CSL01,SUM(ISNULL([002],0.00)) AS CSL02,SUM(ISNULL([003],0.00)) AS CSL03,SUM(ISNULL([004],0.00)) AS CSL04,SUM(ISNULL([005],0.00)) AS CSL05,SUM(ISNULL([006],0.00)) AS CSL06,SUM(ISNULL([007],0.00)) AS CSL07,SUM(ISNULL([008],0.00)) AS CSL08,SUM(ISNULL([009],0.00)) AS CSL09,SUM(ISNULL([010],0.00)) AS CSL10,SUM(ISNULL([011],0.00)) AS CSL11,SUM(ISNULL([012],0.00)) AS CSL12,SUM(ISNULL([013],0.00)) AS CSL13,SUM(ISNULL([014],0.00)) AS CSL14,SUM(ISNULL([015],0.00)) AS CSL15,SUM(ISNULL([016],0.00)) AS CSL16
FROM dbo.ACDOCA WITH (NOLOCK, INDEX(IX_ACDOCA_RCLNT_RBUKRS_RYEAR_RACCT_DRCRK_INCL_CSL_POPER))

PIVOT
(
MAX(ACDOCA.CSL) FOR ACDOCA.POPER IN ([000],[001],[002],[003],[004],[005],[006],[007],[008],[009],[010],[011],[012],[013],[014],[015],[016])
) AS ACDOC_PIV
GROUP BY RCLNT, RBUKRS, RYEAR, RACCT, DRCRK

4. 验证索引覆盖性 #

确认索引确实包含所有查询所需列，可通过以下查询检查索引列信息：

SELECT 
    col.name AS column_name,
    ic.index_column_id,
    ic.is_included_column
FROM sys.indexes AS i
JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns AS col ON ic.object_id = col.object_id AND ic.column_id = col.column_id
WHERE i.object_id = OBJECT_ID('dbo.ACDOCA')
AND i.name = 'IX_ACDOCA_RCLNT_RBUKRS_RYEAR_RACCT_DRCRK_INCL_CSL_POPER';

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