如何减少自连接查询中WNOMELIG表的逻辑读取量？

问题背景 #

我有如下SQL查询语句：

SELECT DISTINCT LEFT(REPLACE(PEREWNOMETET.WNT_CODEARTICLE, 'P', ''), 5) AS CI, Reference 
FROM WNOMETET AS PEREWNOMETET 
JOIN WNOMELIG AS PEREWNOMELIG ON PEREWNOMELIG.WNL_NATURETRAVAIL = PEREWNOMETET.WNT_NATURETRAVAIL AND PEREWNOMELIG.WNL_ARTICLE = PEREWNOMETET.WNT_ARTICLE AND PEREWNOMELIG.WNL_MAJEUR = PEREWNOMETET.WNT_MAJEUR 
JOIN WNOMELIG AS FILSWNOMELIG ON FILSWNOMELIG.WNL_ARTICLE=PEREWNOMELIG.WNL_COMPOSANT 
JOIN ARTICLE AS COMPOSANT ON COMPOSANT.GA_ARTICLE=FILSWNOMELIG.WNL_COMPOSANT 
JOIN APP_PIECES_RECHANGE ON Reference=FILSWNOMELIG.WNL_CODECOMPOSANT 
WHERE COMPOSANT.GA_LIBREART1 = 'COM' AND PEREWNOMETET.WNT_CODITI = 'STA'

执行后统计信息显示：

(74944 ligne(s) affectée(s))
Table 'Worktable'. Nombre d'analyses 0, lectures logiques 0, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'APP_PIECES_RECHANGE'. Nombre d'analyses 1, lectures logiques 26, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'WNOMELIG'. Nombre d'analyses 10, lectures logiques 243780, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'ARTICLE'. Nombre d'analyses 5, lectures logiques 1062, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'WNOMETET'. Nombre d'analyses 5, lectures logiques 419, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'Worktable'. Nombre d'analyses 0, lectures logiques 0, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.

当前核心问题是WNOMELIG表的逻辑读取量过高：该表大小仅1.67GB，但查询的逻辑读取量达到了1.86GB，远超表本身容量。

WNOMELIG表现有索引：

• 聚集索引：WNL_NATURETRAVAIL (ASC), WNL_ARTICLE (ASC), WNL_MAJEUR(ASC), WNL_GUID (ASC)
• 非聚集索引：WNL_NATURETRAVAIL (ASC), WNL_COMPOSANT (ASC)
• 非聚集索引：WNL_ARTICLE (ASC)

优化建议 #

1. 为第一次WNOMELIG关联创建覆盖索引 #

查询中第一次关联WNOMELIG（别名PEREWNOMELIG）时，使用的JOIN条件是WNL_NATURETRAVAIL = PEREWNOMETET.WNT_NATURETRAVAIL AND WNL_ARTICLE = PEREWNOMETET.WNT_ARTICLE AND WNL_MAJEUR = PEREWNOMETET.WNT_MAJEUR，并且需要取出WNL_COMPOSANT字段用于后续关联。

现有聚集索引包含这些JOIN键，但聚集索引会存储所有列，逻辑读取成本高。建议创建一个覆盖非聚集索引，只包含查询需要的字段：

CREATE NONCLUSTERED INDEX IX_WNOMELIG_Join1_Covering
ON WNOMELIG (WNL_NATURETRAVAIL, WNL_ARTICLE, WNL_MAJEUR)
INCLUDE (WNL_COMPOSANT);

这个索引可以让SQL Server直接从索引中获取所需数据，避免回表访问聚集索引，大幅减少逻辑读取量。

2. 为第二次WNOMELIG关联创建覆盖索引 #

第二次关联WNOMELIG（别名FILSWNOMELIG）时，JOIN条件是WNL_ARTICLE=PEREWNOMELIG.WNL_COMPOSANT，同时需要取出WNL_COMPOSANT（关联ARTICLE）和WNL_CODECOMPOSANT（关联APP_PIECES_RECHANGE）。

现有非聚集索引WNL_ARTICLE (ASC)仅包含单个键，需要回表获取其他字段。建议创建另一个覆盖索引：

CREATE NONCLUSTERED INDEX IX_WNOMELIG_Join2_Covering
ON WNOMELIG (WNL_ARTICLE)
INCLUDE (WNL_COMPOSANT, WNL_CODECOMPOSANT);

这个索引可以让SQL Server直接从索引中提取所有需要的字段，无需访问聚集索引，进一步降低逻辑读取开销。

3. 验证DISTINCT是否必要 #

查询中使用了DISTINCT去重，但如果你的数据模型本身不会产生重复的CI和Reference组合，或者可以通过调整JOIN逻辑避免重复，那么去掉DISTINCT可以减少排序/去重的额外开销，间接降低整体读取压力。

你可以先移除DISTINCT执行查询，对比结果集是否和原查询一致，如果一致，建议删除该关键字。

4. 更新表统计信息 #

过时的统计信息可能导致SQL Server选择低效的执行计划。确保所有涉及表的统计信息是最新的，执行以下命令：

UPDATE STATISTICS WNOMELIG WITH FULLSCAN;
UPDATE STATISTICS WNOMETET WITH FULLSCAN;
UPDATE STATISTICS ARTICLE WITH FULLSCAN;
UPDATE STATISTICS APP_PIECES_RECHANGE WITH FULLSCAN;

完整扫描（FULLSCAN）会生成最准确的统计信息，帮助优化器做出更优的索引选择。

5. 检查执行计划中的索引使用情况 #

从逻辑读取量来看，SQL Server可能没有使用最优的索引。你可以查看执行计划：

• 确认PEREWNOMELIG是否使用了针对WNL_NATURETRAVAIL, WNL_ARTICLE, WNL_MAJEUR的索引
• 确认FILSWNOMELIG是否使用了针对WNL_ARTICLE的索引
  如果存在索引扫描（而非索引查找），说明现有索引无法满足查询需求，上述创建的覆盖索引应该能解决这个问题。

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