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好很多。