Left Outer Join关联表扫描问题排查及优化方法咨询

场景背景 #

查询一个仅3万行的小表时，IO指标显示逻辑读异常偏高：Scan count 1, logical reads 21745, physical reads 20, read-ahead reads 154, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0。对应的SQL语句如下：

SELECT a.id
     , a.name
     , a.start_dt
     , a.end_dt
     , a.title
     , b.contents
     , b.status
     , b.img
     , b.type
FROM            dbo.a WITH(NOLOCK)
LEFT OUTER JOIN (SELECT c.contents
                      , c.status
                      , c.img
                      , c.type
                 FROM dbo.c WITH(NOLOCK)
                 JOIN dbo.d WITH(NOLOCK) ON c.id = d.id
                 WHERE c.onoff='on' AND c.type!='S') b 
             ON a.code = b.id AND b.type='A'
LEFT OUTER JOIN dbo.e WITH(NOLOCK) 
             ON a.name = e.name AND e.type ='E'

已知c.id是表c的主键，添加WITH(NOLOCK, FORCESEEK)后，查询改用聚集索引查找，性能恢复正常。

问题解答 #

1. 这是查询优化器的正常失误吗？ #

这属于优化器常见的成本估算偏差，算不上Bug。优化器是基于统计信息和成本模型做决策的：

• 对于3万行的小表，优化器可能认为全表扫描的IO开销比走索引查找更低——毕竟小表扫描的单次IO能读取更多数据，而索引查找可能需要额外的键查找/书签查找操作，优化器估算时可能判定扫描成本更低。
• 子查询的多层关联逻辑（和表d的JOIN、后续和表a的关联）可能让优化器误判过滤后的行数，最终选择了扫描而非索引查找。这种情况在小表+复杂关联的场景下很常见，属于优化器的“合理决策偏差”。

2. 这是唯一的性能优化方式吗？ #

当然不是。FORCESEEK是强制干预优化器决策的“硬手段”，还有很多更温和、更可持续的优化方法，不需要依赖强制提示。

3. 还有哪些其他优化方法？ #

• 更新统计信息：优化器的决策依赖准确的统计数据，先更新表c和表d的统计信息，让优化器能精准估算过滤后的行数：

  UPDATE STATISTICS dbo.c WITH FULLSCAN;
  UPDATE STATISTICS dbo.d WITH FULLSCAN;
  

  很多时候，统计信息过时是优化器决策失误的核心原因，更新后优化器可能自动选择索引查找。

• 创建覆盖索引：针对表c的查询场景，创建包含过滤条件、关联列和返回列的覆盖索引，让优化器无需回表就能获取所有数据：

  CREATE NONCLUSTERED INDEX IX_c_onoff_type_id 
  ON dbo.c (onoff, type, id)
  INCLUDE (contents, status, img);
  

  这个索引覆盖了WHERE子句的onoff、type，关联用的id，以及需要返回的列，优化器自然会优先选择索引查找，避免全表扫描。

• 提前过滤数据：把子查询的type!='S'和外层关联的b.type='A'合并，提前过滤掉无关数据，减少子查询返回的行数：
  修改子查询的WHERE条件为c.onoff='on' AND c.type='A'，这样子查询返回的数据量大幅减少，优化器的成本估算会更准确，更倾向于选择索引查找。修改后的子查询：

  SELECT c.contents
       , c.status
       , c.img
       , c.type
  FROM dbo.c WITH(NOLOCK)
  JOIN dbo.d WITH(NOLOCK) ON c.id = d.id
  WHERE c.onoff='on' AND c.type='A'
  

• 优化表d的索引：表d和表c关联的条件是c.id = d.id，如果表d的id列没有索引，会导致表d的全表扫描，进而影响优化器对整个关联的成本判断。确保表d的id列有主键或非聚集索引，这样关联时能快速匹配，优化器更可能选择表c的索引查找。

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