mysql子查询(in)的实现

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  0x00000000005e41e8 in sub_select (join=0x6fe0f10, join_tab=0x6fe2b28, end_of_records=<value optimized out>) at sql_select.cc:11384   【s表】 #2  0x00000000005e3f5a in evaluate_join_record (join=0x6fe0f10, join_tab=0x6fe28d0, error=<value optimized out>) at sql_select.cc:11511 #3  0x00000000005e41e8 in sub_select (join=0x6fe0f10, join_tab=0x6fe28d0, end_of_records=<value optimized out>) at sql_select.cc:11384       【e表】 #4  0x00000000005e3f5a in evaluate_join_record (join=0x6fe0f10, join_tab=0x6fe2678, error=<value optimized out>) at sql_select.cc:11511 #5  0x00000000005e4215 in sub_select (join=0x6fe0f10, join_tab=0x6fe2678, end_of_records=<value optimized out>) at sql_select.cc:11391       【t表】 #6  0x0000000000601d30 in do_select (join=0x6fe0f10, fields=0x6f819a0, table=0x0, procedure=0x0) at sql_select.cc:11140 #7  0x000000000060a479 in JOIN::exec (this=0x6fe0f10) at sql_select.cc:2314 #8  0x000000000060ae0f in mysql_select (thd=0x6f7f980, rref_pointer_array=0x6f81a68, tables=0x6fd5198, wild_num=0, fields=@0x6f819a0, conds=0x6fdd218,    og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x6fdd398, unit=0x6f81470, select_lex=0x6f81898)    at sql_select.cc:2509 #9  0x000000000060b481 in handle_select (thd=0x6f7f980, lex=0x6f813d0, result=0x6fdd398, setup_tables_done_option=0) at sql_select.cc:269 #10 0x000000000054c71a in execute_sqlcom_select (thd=0x6f7f980, all_tables=0x6fd5198) at sql_parse.cc:5075 #11 0x000000000055538c in mysql_execute_command (thd=0x6f7f980) at sql_parse.cc:2271 #12 0x000000000055ebd3 in mysql_parse (thd=0x6f7f980,

表1 join方式的bt

通过该bt我们也可以清楚的看到三层nest-loop的过程；注：通过在每一层的sub_select处查看join_tab->table->alias变量我们可以此时具体操作的表。