mysql 关联子查询优化

-- 如下两种写法等效，一般，通过 exists 的写法更高效mysql> explain select * from sakila.film where film_id in (select film_id from sakila.film_actor where actor_id = 1);
+----+-------------+------------+------------+--------+------------------------+---------+---------+---------------------------+------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys          | key     | key_len | ref                       | rows | filtered | Extra       |
+----+-------------+------------+------------+--------+------------------------+---------+---------+---------------------------+------+----------+-------------+
|  1 | SIMPLE      | film_actor | NULL       | ref    | PRIMARY,idx_fk_film_id | PRIMARY | 2       | const                     |   19 |   100.00 | Using index |
|  1 | SIMPLE      | film       | NULL       | eq_ref | PRIMARY                | PRIMARY | 2       | sakila.film_actor.film_id |    1 |   100.00 | NULL        |
+----+-------------+------------+------------+--------+------------------------+---------+---------+---------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)


mysql> explain select * from sakila.film where exists (select * from sakila.film_actor where actor_id = 1 and film.film_id = film_actor.film_id);
+----+--------------------+------------+------------+--------+------------------------+---------+---------+---------------------------+------+----------+-------------+
| id | select_type        | table      | partitions | type   | possible_keys          | key     | key_len | ref                       | rows | filtered | Extra       |
+----+--------------------+------------+------------+--------+------------------------+---------+---------+---------------------------+------+----------+-------------+
|  1 | PRIMARY            | film       | NULL       | ALL    | NULL                   | NULL    | NULL    | NULL                      | 1000 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | film_actor | NULL       | eq_ref | PRIMARY,idx_fk_film_id | PRIMARY | 4       | const,sakila.film.film_id |    1 |   100.00 | Using index |
+----+--------------------+------------+------------+--------+------------------------+---------+---------+---------------------------+------+----------+-------------+

mysql> explain select distinct film.film_id from sakila.film inner join sakila.film_actor using(film_id);
+----+-------------+------------+------------+-------+------------------------------------------------------------------+--------------------+---------+---------------------+------+----------+------------------------------+
| id | select_type | table      | partitions | type  | possible_keys                                                    | key                | key_len | ref                 | rows | filtered | Extra                        |
+----+-------------+------------+------------+-------+------------------------------------------------------------------+--------------------+---------+---------------------+------+----------+------------------------------+
|  1 | SIMPLE      | film       | NULL       | index | PRIMARY,idx_title,idx_fk_language_id,idx_fk_original_language_id | idx_fk_language_id | 1       | NULL                | 1000 |   100.00 | Using index; Using temporary |
|  1 | SIMPLE      | film_actor | NULL       | ref   | idx_fk_film_id                                                   | idx_fk_film_id     | 2       | sakila.film.film_id |    5 |   100.00 | Using index; Distinct        |
+----+-------------+------------+------------+-------+------------------------------------------------------------------+--------------------+---------+---------------------+------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> explain select film_id from sakila.film where exists (select * from sakila.film_actor where film.film_id = film_actor.film_id);
+----+--------------------+------------+------------+-------+----------------+--------------------+---------+---------------------+------+----------+--------------------------+
| id | select_type        | table      | partitions | type  | possible_keys  | key                | key_len | ref                 | rows | filtered | Extra                    |
+----+--------------------+------------+------------+-------+----------------+--------------------+---------+---------------------+------+----------+--------------------------+
|  1 | PRIMARY            | film       | NULL       | index | NULL           | idx_fk_language_id | 1       | NULL                | 1000 |   100.00 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | film_actor | NULL       | ref   | idx_fk_film_id | idx_fk_film_id     | 2       | sakila.film.film_id |    5 |   100.00 | Using index              |
+----+--------------------+------------+------------+-------+----------------+--------------------+---------+---------------------+------+----------+--------------------------+
2 rows in set, 2 warnings (0.00 sec)

一般推荐使用 exists 用法，因为一旦使用了 distinct 和 group by ，在执行过程中需要创建临时中间表。