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 ，在执行过程中需要创建临时中间表。

 

mysql 关联子查询优化

标签：type   nal   key   pos   ssi   用法   cto   font   使用