mysql的子查詢

子查詢的執行方式不同於oracle，它不會首先得到一個in 列表，然後通過這些值去進行過濾：

改進方法，重寫查詢或者先取出列表，然後再查詢。

 

MySQL tries to “help” the subquery by
pushing a correlation into it from the outer table, which it thinks will let the subquery
find rows more efficiently. It rewrites the query as follows:

 

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      | type   | possible_keys          | key     | key_len | ref        | rows | Extra       |
+----+--------------------+------------+--------+------------------------+---------+---------+------------+------+-------------+
|  1 | PRIMARY            | film       | ALL    | NULL                   | NULL    | NULL    | NULL       |  952 | Using where |
|  2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id | PRIMARY | 4       | const,func |    1 | Using index |
+----+--------------------+------------+--------+------------------------+---------+---------+------------+------+-------------+
2 rows in set (0.60 sec)

 

mysql> explain
    -> SELECT * FROM sakila.film
    -> WHERE EXISTS (
    -> SELECT * FROM sakila.film_actor WHERE actor_id = 1
    -> AND film_actor.film_id = film.film_id);
+----+--------------------+------------+--------+------------------------+---------+---------+---------------------------+------+-------------+
| id | select_type        | table      | type   | possible_keys          | key     | key_len | ref                       | rows | Extra       |
+----+--------------------+------------+--------+------------------------+---------+---------+---------------------------+------+-------------+
|  1 | PRIMARY            | film       | ALL    | NULL                   | NULL    | NULL    | NULL                      |  952 | Using where |
|  2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id | PRIMARY | 4       | const,sakila.film.film_id |    1 | Using index |
+----+--------------------+------------+--------+------------------------+---------+---------+---------------------------+------+-------------+
2 rows in set (0.02 sec)

 

 

According to the EXPLAIN output, MySQL will table-scan the film table and execute
the subquery for each row it finds. This won’t cause a noticeable performance hit on
small tables, but if the outer table is very large, the performance will be extremely
bad. Fortunately, it’s easy to rewrite such a query as a JOIN:
mysql> SELECT film.* FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id)
-> WHERE actor_id = 1;
Another good optimization is to manually generate the IN( ) list by executing the
subquery as a separate query with GROUP_CONCAT( ). Sometimes this can be faster than
a JOIN.
MySQL has been criticized thoroughly for this particular type of subquery execution
plan. Although it definitely needs to be fixed, the criticism often confuses two different
issues: execution order and caching. Executing the query from the inside out is
one way to optimize it; caching the inner query’s result is another. Rewriting the
query yourself lets you take control over both aspects. Future versions of MySQL
should be able to optimize this type of query much better, although this is no easy
task. There are very bad worst cases for any execution plan, including the inside-out
execution plan that some people think would be simple to optimize.