mysql子查询(in)的实现

本文探讨了MySQL中子查询使用IN操作符与直接JOIN在性能上的差异。通过对两种方法的执行计划和过程分析,揭示了IN子查询在全表扫描时会多次执行JOIN构造和析构,导致性能下降。主要原因是子查询的执行涉及到JOIN结构的重复构造和初始化,特别是在外部查询为全表扫描时,这个过程会被放大,增加了额外的性能开销。
摘要由CSDN通过智能技术生成

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变量我们可以此时具体操作的表。

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值