mysql in 子查询原理_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=)

at sql_select.cc:11384 【s表】

#2 0x00000000005e3f5a in evaluate_join_record (join=0x6fe0f10,

join_tab=0x6fe28d0, error=

out>) at sql_select.cc:11511

#3 0x00000000005e41e8 in sub_select

(join=0x6fe0f10, join_tab=0x6fe28d0,

end_of_records=)

at

sql_select.cc:11384 【e表】

#4 0x00000000005e3f5a in evaluate_join_record (join=0x6fe0f10,

join_tab=0x6fe2678, error=

out>) at sql_select.cc:11511

#5 0x00000000005e4215 in sub_select

(join=0x6fe0f10, join_tab=0x6fe2678,

end_of_records=)

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