5.6版本是mysql一个重大改进版,优化了很多内容,查询优化器也有改进。因此有必要做个测试
对于最常见的子查询问题,in和exists,如何优化,做个简单测试。
首先建三张表:film,actor,film_actor
电影,演员,电影-演员关联表,很简单的一个多对多关联。
给关联表添加外键,如果去掉外键会影响mysql的执行计划。
执行如下语句,查询演员1演出的所有电影。
1 EXPLAIN select film.* from film where EXISTS( select film_id from film_actor where actor_id = 1 ); 2 3 EXPLAIN select film.* from film where film_id 4 in (select film_id from film_actor where actor_id = 1); 5 6 EXPLAIN select film.* from film where film_id 7 in ( select GROUP_CONCAT(film_id) from film_actor where actor_id = 1); 8 9 EXPLAIN select film.* from film inner join film_actor 10 using(film_id) where actor_id = 1 ;
4个语句结果依次如下:
看来对in的子查询做了优化,和exists已经没什么区别,当然表的统计信息也会对优化器有很大影响。这里测试数据比较少
4的结果应该是最理想的,先查演员表,然后检索电影表。因为是select *所以要回表,如果索引覆盖返回列,可以不回表了。效率最高。
其他几个都是先获取外层所有数据,然后遍历子查询比较。如果电影表很大,显然不如4的先检索演员表效率高。
看来inner join是更优选择。实际场景因为表的统计信息不同,建议具体对待。不能一概而论。