MySQL自连接时无主键表速度比有主键表快3倍的原因求解

性能差异核心原因 #

两个查询使用了完全不同的连接算法，IO开销差距极大：

• 有主键的table_indexed使用嵌套循环连接（Nested Loop Join）
  执行计划显示驱动表t1全扫描主键索引，每读取一条t1的idx值，就到t2的主键索引执行1次精准匹配（eq_ref），总共有300万次随机索引查找。
  你插入的idx是随机排列的，主键作为聚簇索引，随机查找会产生大量随机IO开销，单次查找虽然成本低，但300万次累加的总开销远高于顺序IO。
• 无主键的table_not_indexed使用哈希连接（Hash Join）
  执行计划的Extra字段明确标注了Using join buffer (hash join)，整个查询只有两次全表顺序扫描：
  1. 先扫描t1的所有idx值，在内存的join buffer中构建哈希表
  2. 再顺序扫描t2的所有行，拿每行的idx到内存哈希表中做O(1)复杂度的匹配，匹配成功就计数
     整个过程没有随机IO，全是顺序读+内存操作，300万行的数据量完全可以放入join buffer，所以总耗时远低于嵌套循环连接。

补充说明 #

MySQL优化器选择嵌套循环连接是因为成本估算时认为走主键索引的嵌套循环成本更低，但估算模型没有完全覆盖随机IO的实际开销，尤其是当索引键值随机分布时，实际执行成本远高于估算值，才会出现有索引反而更慢的反常识结果。

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