MySQL IN子查询

近日通过《mysql技术内幕:SQL编程》学习mysql子查询,书中介绍了IN子查询存在的问题,并且提供了一些优化方法。我在此书的基础上进行了深入研究,总结几点。

MySql优化器对IN子查询优化时存在一个问题,MySql优化器对于IN语句的优化是“LAZY”的。对于IN子句,如果不显式的列表定义,如IN(‘a’,’b’,’c’),那么IN子句都会被转换为EXISTS的相关子查询。如下面这条独立子查询:

select … from t1 where t1.a in (select b from t2)

优化器会将该语句重写为如下的相关子查询:

select … from t1 where exists (select 1 from t2 wheret1.a=t2.b)

如果子查询和外部查询分别返回M和N行,那么该子查询被扫描为O(N+M*N)而不是O(M+N)。

这一点在mysql官方文档中有参考,E.4.Restrictions on Subqueries。于是我通过mysql官方的示例数据库employees进行了测试。

要求:查询dept_no为d001的所有员工。

按照要求,我只需要使用子查询在dept_emp表中选择dept_no为d001的emp_no,然后使用IN关键字从employees表中选择emp_no所在的记录就可以,于是有:

select * from employees where emp_no in (select emp_nofrom dept_emp where dept_no='d001');

执行这条sql语句,总共查出20211条记录,耗时2.078s。感觉是很慢的,但是也不知道到底多快算是合理。

使用EXPLAIN来查看它的查询计划:

由上图可知,子查询的执行方式为相关子查询,但是从sql语句来看,我写的明明是独立子查询。看来正如mysql文档所说,优化器对sql进行了改写。

按照mysql参考文档的说法,可以将该查询改为联结的方式。如下所示:

select * from employees emp,dept_emp de wheredept_no='d001' and emp.emp_no=de.emp_no;

执行结果相同,但是查询时间变为了0.290s,速度提高了将近10倍,再EXPLAIN一下:

发现两个查询选择类型都为SIMPLE。而且type列为eq_ref,比unique_subquery好很多。

 

MySQL中的IN子查询是一种常用的查询方式,它允许将一个查询语句嵌套在另一个查询语句的IN关键字后面。通过使用IN子查询,我们可以在查询语句中使用结果集中的数据,或者与同一张表中计算得出的数据结果进行比较。 IN子查询的优化也是一个重要的问题。在MySQL中,IN子查询的执行方式是将外表和内表进行哈希连接,而EXISTS子查询则是对外表进行循环遍历,每次循环再对内表进行查询。在查询的两个表大小相当的情况下,IN和EXISTS的效率差别不大。但如果一个表较小,另一个表较大,那么使用EXISTS子查询的效率更高。此外,使用NOT IN时,内外表都要进行全表扫描,没有使用到索引;而使用NOT EXISTS的子查询则可以使用表上的索引,因此无论表大小如何,使用NOT EXISTS都比NOT IN要快。 除了IN子查询,还有其他的子查询方式可以实现类似的功能。例如,在FROM子句中使用子查询,可以将子查询作为临时的虚拟表来使用。通过给子查询取别名,并将其作为FROM子句的一部分,可以方便地在查询中使用子查询的结果,以及与其他表进行连接和比较。 综上所述,MySQL中的IN子查询是一种强大的查询工具,它可以增强SELECT查询的能力。通过了解和合理使用不同的子查询方式,我们可以更好地优化查询性能,并实现复杂的查询需求。<span class="em">1</span><span class="em">2</span><span class="em">3</span><span class="em">4</span>
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值