mysql5.6 in or exists

5.6版本是mysql一个重大改进版，优化了很多内容，查询优化器也有改进。因此有必要做个测试

对于最常见的子查询问题，in和exists，如何优化，做个简单测试。

首先建三张表：film,actor,film_actor

电影，演员，电影-演员关联表，很简单的一个多对多关联。

给关联表添加外键，如果去掉外键会影响mysql的执行计划。

 

执行如下语句，查询演员1演出的所有电影。

EXPLAIN  select film.* from film where EXISTS(  select film_id from film_actor where actor_id = 1 );

EXPLAIN   select film.* from film where film_id 
in (select film_id from film_actor where actor_id = 1);

EXPLAIN   select film.* from film where film_id 
in ( select GROUP_CONCAT(film_id) from film_actor where actor_id = 1);

EXPLAIN  select film.* from film inner join  film_actor 
using(film_id) where actor_id = 1 ;

4个语句结果依次如下：

 

 

 

 看来对in的子查询做了优化，和exists已经没什么区别，当然表的统计信息也会对优化器有很大影响。这里测试数据比较少

4的结果应该是最理想的，先查演员表，然后检索电影表。因为是select *所以要回表，如果索引覆盖返回列，可以不回表了。效率最高。

其他几个都是先获取外层所有数据，然后遍历子查询比较。如果电影表很大，显然不如4的先检索演员表效率高。

看来inner join是更优选择。实际场景因为表的统计信息不同，建议具体对待。不能一概而论。