两条相似SQL语句为何一条使用索引另一条不使用？

问题背景 #

有两条结构几乎完全一致的关联查询SQL，仅WHERE条件不同：

• 语句1：过滤省级表DICT_REGION的code字段，值为'110000000000'
• 语句2：过滤居委会表DICT_REGION_NEIGHBOR_COMMITTEE的code字段，值为'620524108210'

所有涉及表的code字段均已创建索引，但一条SQL使用索引，另一条未使用，核心原因在于数据库优化器基于数据分布和查询成本评估选择了不同的执行路径。

具体原因分析 #

1. 过滤条件的匹配数据量差异

   • 语句1的过滤条件针对省级表DICT_REGION，'110000000000'对应唯一的省级行政区，匹配行数仅1行。优化器判断通过f.code的索引快速定位该数据后，再通过关联链（省→市→区→街道→居委会）逐层查询的IO成本极低，因此选择使用索引。
   • 语句2的过滤条件针对居委会表DICT_REGION_NEIGHBOR_COMMITTEE，即便单个居委会编码仅匹配1行数据，但优化器会评估后续关联路径的成本：若关联时依赖的parent_code字段未创建索引（仅code字段有索引），从居委会表出发关联街道、区等表时无法利用索引，优化器可能认为直接扫描关联表的成本比走索引更低，最终选择放弃索引。

2. 关联顺序的选择逻辑
   数据库优化器会自动挑选最优驱动表和关联顺序：

   • 语句1中，优化器选择数据量极小的省级表作为驱动表，后续每一步关联都能借助父表code的唯一索引快速匹配子表数据，整体执行成本可控。
   • 语句2中，若居委会表作为驱动表，后续关联街道表时需用到c.parent_code匹配t.code，但c.parent_code无索引的话，关联步骤会变成全表扫描，优化器可能转而选择从其他数据量更大的表开始关联，此时原有的c.code索引就无法被利用。

3. 统计信息的准确性影响
   如果数据库表的统计信息过时，优化器会错误评估数据分布（比如误判居委会表code字段的基数），进而做出不使用索引的决策。

验证与解决建议 #

• 查看两条SQL的执行计划，明确驱动表、关联顺序及各表的访问方式（索引扫描/全表扫描）；
• 检查关联字段parent_code是否创建索引，若未创建，可考虑为该字段添加索引以优化关联性能；
• 更新表的统计信息（如MySQL执行ANALYZE TABLE，Oracle执行DBMS_STATS.GATHER_TABLE_STATS），让优化器获得准确的数据分布；
• 可尝试强制使用索引验证性能变化，例如：

  SELECT 
      f.name AS "省", e.name AS "市", d.name AS "区", 
      t.name AS "街道", c.name AS "居委会"
  FROM 
      DICT_REGION_TOWNSHIP t
  JOIN 
      DICT_REGION_NEIGHBOR_COMMITTEE c FORCE INDEX (idx_code) ON t.code = c.parent_code
  JOIN 
      DICT_REGION_COUNTY d ON t.parent_code = d.code
  JOIN 
      DICT_REGION_CITY e ON d.parent_code = e.code
  JOIN 
      DICT_REGION f ON e.parent_code = f.code
  WHERE 
      c.code = '620524108210'
  

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