采用Distinct字段

执行以下语句

1
2
3
4
5

explain SELECT DISTINCT film.film_id
FROM sakila.film
INNER JOIN sakila.film_actor USING(film_id);

show profiles;

采用Exist

执行以下语句

1
2
3
4
5
6
7

explain SELECT film_id
FROM sakila.film
WHERE EXISTS(
SELECT * FROM sakila.film_actor
WHERE film.film_id = film_actor.film_id);

show profiles;

区别

• Select type : distinct字段是两个普通子查询，exist是一个主查询以及子查询中的第一个select查询。
• type：均是一个index和ref
• extra：
  • Distinct: Using index(只使用索引树的信息), Using temporary(临时表), Using index, Distinct
  • Exist: Using where(用where子句进行限制), Using index, Using index
• 执行时间：第二种是第一种的执行时间的两倍左右。

分析原因

他们都在film.id 以及language.id上做了索引，查询时都会用到。

对于distinct，用到了临时表，先把select film.film_id的结果存入临时表，然后再扫描film_actor，用索引查找并和join_buffer中的内容做对比，符合join条件的，作为结果集返回。最后再做Distinct操作。这里的distinct没有用到Using filesort操作。

对于exist只负责查询select是否在子查询。核心表是film表，子查询和外层表关系紧密，外层查询对film表进行遍历，然后做exist存在性测试，如果非空，那么film_id就是需要的结果，否则剔除。只要满足符合条件的一条即可满足。

对执行时间做进一步探索。

上图是第一个查询的详细用时，可以看到主要时间用在Opening tables和Sending data上，其次还有一些创建tmp table的时间。

上图是查询2的详细用时，不过只截取了一部分，其余的行都是executing - sending data - end此类。清理也用了比查询1更多的时间。

综上所述，查询一尽管创建临时表花费一些时间，但通过优化器，利用内存中的临时表等操作做了较少的查询工作，而查询二由于大量的传输数据时间以及查询时间，导致最后花费时间比较长。