MySQL中无关联列的user与milestone表为何执行Nested Loop Inner Join？

问题背景 #

原始SQL查询 #

select distinct issue.id from issue 
inner join user on issue.user_id=user.id 
left outer join milestone on issue.milestone_id=milestone.id 
where issue.is_opened=true and user.name='aaa' and milestone.name='bbb';

执行计划（Explain Analyze结果） #

1.-> Nested loop inner join  (cost=5484.47 rows=1004) (actual time=7.410..7.819 rows=17 loops=1)
   2.-> Nested loop inner join  (cost=0.70 rows=1) (actual time=0.887..0.897 rows=1 loops=1)
      3.-> Covering index lookup on user1_ using idx_name (name='aaa')  (cost=0.35 rows=1) (actual time=0.854..0.858 rows=1 loops=1)
      4.-> Covering index lookup on milestone2_ using idx_m_name (name='bbb')  (cost=0.35 rows=1) (actual time=0.027..0.034 rows=1 loops=1)
   5.-> Filter: ((issue0_.milestone_id = milestone2_.id) and (issue0_.is_opened = true))  (cost=1567.42 rows=1004) (actual time=6.522..6.919 rows=17 loops=1)
      6.-> Index lookup on issue0_ using fk_issue_user_idx (user_id=user1_.id)  (cost=1567.42 rows=40168) (actual time=6.509..6.892 rows=200 loops=1)

用户疑问 #

为何无关联列的user表与milestone表会执行Nested Loop Inner Join（第2行）？如果是外连接还能理解，但内连接的情况实在搞不懂。

解答 #

这是数据库优化器做的逻辑等价转换，核心原因有两个：

1. 左连接被隐式转为内连接
   你写的是issue左连milestone，但where条件里加了milestone.name='bbb'——这个条件会直接过滤掉所有milestone字段为NULL的行（因为NULL和任何值比较结果都是false），相当于把左连接变成了内连接，最终issue必须同时匹配到符合条件的user和milestone。

2. 优化器选择了更高效的执行顺序
   优化器通过索引快速定位到：

   • user.name='aaa'仅返回1条记录
   • milestone.name='bbb'也仅返回1条记录

这时候把两个单条结果做嵌套循环内连接，本质就是做一次笛卡尔积（结果只有1行），代价极低。之后再用这行里的user.id和milestone.id去关联issue表，相当于同时用两个精准条件过滤issue，比按你写的顺序先连issue和user、再连milestone要高效得多。

简单说，优化器重新组合了查询逻辑：先找到符合条件的user和milestone，再用这两个条件查对应的issue，逻辑完全等价，但性能更好。

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