优化SQL查询性能求助：关联MKPF与MSEG大表查询耗时过长

执行以下查询时遇到性能瓶颈，耗时超2分钟，目标是将耗时控制在30秒以内（最多不超过1分钟）：

SELECT TOP 5000 
    mseg.werks
FROM
    mkpf
INNER JOIN 
    mseg ON mkpf.mandt = mseg.mandt
         AND mkpf.mblnr = mseg.mblnr
GROUP BY 
    mseg.werks 

现有索引与数据规模 #

索引信息 #

• MKPF：聚集索引，列顺序：mandt, mblnr, abc
• MSEG：非聚集索引，仅包含列：werks
• MSEG：聚集索引，列顺序：mandt, mblnr, abc, xyz

表行数 #

• MKPF：5000万行
• MSEG：1.5亿行

补充背景 #

该查询是更大查询的核心部分，其他场景需从两表提取任意列，因此查询改写受限，必须保证可访问两表所有列。

1. 创建覆盖JOIN与查询需求的非聚集索引 #

当前MSEG上仅werks的非聚集索引无法支撑高效JOIN（JOIN依赖mandt和mblnr），建议创建包含连接列的覆盖索引：

CREATE NONCLUSTERED INDEX IX_MSEG_MANDT_MBLNR_WERKS 
ON MSEG (mandt, mblnr)
INCLUDE (werks);

该索引可让数据库直接通过索引完成JOIN和GROUP BY操作，无需回表查询聚集索引，大幅降低IO开销。

2. 优先触发合并连接 #

针对超大规模表的JOIN，合并连接是最高效的方式之一，但要求两张表在JOIN列上有序。MKPF的聚集索引已按mandt, mblnr排序，上述新索引会让MSEG在mandt, mblnr上也有序，数据库会自动选择合并连接，避免哈希连接的大量内存开销或嵌套循环的反复查找。

3. 移除冗余的TOP 5000 #

werks作为工厂代码，唯一值数量通常远小于5000，TOP 5000属于冗余限制，直接移除可减少数据库不必要的计算：

SELECT 
    mseg.werks
FROM
    mkpf
INNER JOIN 
    mseg ON mkpf.mandt = mseg.mandt
         AND mkpf.mblnr = mseg.mblnr
GROUP BY 
    mseg.werks 

4. 更新统计信息 #

若表数据有大量新增/修改，过时的统计信息会导致数据库生成低效执行计划，执行以下语句更新：

UPDATE STATISTICS MKPF WITH FULLSCAN;
UPDATE STATISTICS MSEG WITH FULLSCAN;

兼容大查询的长期优化 #

由于核心查询是更大查询的一部分，需支持访问两表所有列：

• 若有高频访问的其他列，可在上述索引的INCLUDE子句中添加这些列，平衡索引维护成本与查询性能；
• 避免创建过于宽泛的索引，优先针对高频查询场景定制覆盖索引。

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