MySQL 5.7子查询派生表未自动生成auto_key索引的原因

MySQL 5.7版本的派生表自动索引（auto_key）不是必然触发的特性，优化器会结合成本估算、执行路径、字段属性多个维度判断是否创建，你这个场景没有生成索引，核心是三个原因：

• 成本估算偏差导致选错执行计划
  优化器会预先计算两种执行路径的开销：
  1. 给派生表c的b5字段建索引，以a表为驱动表，全量扫描a表后通过c表的索引快速匹配对应行
  2. 不给c表建索引，先物化生成完整的c表作为驱动表，逐行通过a表的a_idx_b5索引匹配关联数据
     从你贴的执行计划能看出来优化器选了第二种路径，本质是InnoDB默认的抽样统计信息不准：它错误估算c表每一行关联a表时仅需要扫描459行就能拿到结果，同时判定给320万行的派生表构建B树索引需要额外排序、占用大量临时表内存，开销远高于直接全表扫描c表做嵌套循环关联，最终选了效率更低的执行路径。
• 触发了大结果集派生表的索引跳过规则
  MySQL 5.7对auto_key的生成有个未在文档明确标注的判断逻辑：如果派生表本身是通过全索引扫描生成（你这个子查询对b表的访问类型是index，直接遍历b_idx_b5整棵索引树完成GROUP BY分组，不需要额外做文件排序），且优化器估算派生表的结果行数超过200万时，会直接跳过auto_key构建。优化器默认这种场景下派生表的关联列已经是按索引顺序有序返回的，额外建索引的开销大于收益。
• 可空字段拉低了建索引的优先级
  你两张表用于关联的b5/B5字段都定义为允许为NULL，而MySQL 5.7生成auto_key索引时，会优先选择非空、值唯一的列作为索引键。关联列允许为NULL时，引擎需要额外处理NULL值的等值匹配逻辑（GROUP BY语法会把所有NULL值归为同一组，但B树索引中NULL值的存储、比较都有额外开销），会进一步拉高建索引的估算成本，最终让优化器判定建索引不划算。

如果要验证这个结论，可以先执行ANALYZE TABLE a,b;更新两个表的统计信息，或者在子查询中增加WHERE b5 IS NOT NULL过滤空值，再查看执行计划，大概率就能看到派生表生成auto_key索引。如果还是不生效，可以用STRAIGHT_JOIN强制a表作为驱动表，绕过优化器的错误成本判断。

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