本文探讨了MySQL中子查询使用IN操作符与直接JOIN在性能上的差异。通过对两种方法的执行计划和过程分析，揭示了IN子查询在全表扫描时会多次执行JOIN构造和析构，导致性能下降。主要原因是子查询的执行涉及到JOIN结构的重复构造和初始化，特别是在外部查询为全表扫描时，这个过程会被放大，增加了额外的性能开销。
In子查询的原理
1. in原理
此调研的背景是同样的select结果为什么使用子查询会比不使用子查询慢。我们使用的数据库还是mysql官方的employees。进行的实验操作为下面两个语句：
方法一：explain select sql_no_cache t.emp_no,t.title,t.from_date,t.to_datefrom titles t straight_join employees e on (e.emp_no=t.emp_no) straight_joinsalaries s on (s.emp_no=e.emp_no) where e.gender='F' and s.salary=90930;
图1直接使用join
方法二：explain select sql_no_cache * from titles t where t.emp_no in (select s.emp_no from salaries s, employees e where s.emp_no=e.emp_no ande.gender='F' and s.salary=90930);
图2使用in的子查询
在下面的讨论中我们直接使用直接join和in的称呼来代表两种不同的情况。（注：在我们的实验中第一种情况use 3.5s;第二种情况use 5.7s）
首先我们来解释一下图2的dependent subquery是什么意思：手册上的解释是，子查询中的第一个select，取决于外面的查询。就这么一句话，其实它表达的意思是（以我们图2的表来说明）子查询（e join t）的第一个表（e）的查询方式依赖于外部（t表）的查询。换句话说就是e表的检索方式依赖于t表的数据，如这里t表得到的记录t.emp_no（where t.emp_no in）刚好可以被e表作为eq_ref方式来获得它的相应的记录；换种写法如果此时t表扫描第一条记录得到的t.emp_no为10001的话，那么后面子查询的语句就类似于这样的语句：
select s.emp_no from salaries s, employeese where s.emp_no=e.emp_no and e.gender='F' and s.salary=90930 ands.emp_no=10001。此时这个语句就会被优化拿来优化，变成了上面的子查询的执行计划。
通过这个解释我们可以知道：对于上面的两种方式，它们使用的索引及读取数据的过程及方法是一样的，全表扫描t表，将t的每条记录传递给e表，e表通过eq_ref索引方式来获得记录判断自身的条件，然后再传递给s给，s表使用ref方式来获得记录，再判断自身的条件是否也得到满足，如果也满足的话，则找到一个满足此查询的语句。那么为什么这两种情况会有性能上的差距了？
首先我们通过bt上看一下，两的具体执行流程，看它们的区别在哪里？
| #0 evaluate_join_record (join=0x6fe0f10, join_tab=0x6fe2b28, error=0) at sql_select.cc:11414 |
表1 join方式的bt
通过该bt我们也可以清楚的看到三层nest-loop的过程；注：通过在每一层的sub_select处查看join_tab->table->alias变量我们可以此时具体操作的表。
最低0.47元/天 解锁文章
扫一扫
2852
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
