mysql 基於規則的執行計劃(二)

上篇文章說mysql還在用基於規則的優化器，是我的誤解，裡面涉及到Covering Index，獲取資料的方式，Cost的計算等等，總之，剛從oracle來搞mysql，兩者差別還是挺大的：

 

The STRAIGHT_JOIN keyword forces the join to proceed in the order specified
in the query. Here’s the EXPLAIN output for the revised query：

 

mysql> explain
    -> SELECT STRAIGHT_JOIN film.film_id, film.title, film.release_year, actor.actor_id,
    -> actor.first_name, actor.last_name
    -> FROM sakila.film
    -> INNER JOIN sakila.film_actor USING(film_id)
    -> INNER JOIN sakila.actor USING(actor_id);
+----+-------------+------------+--------+------------------------+----------------+---------+----------------------------+------+-------------+
| id | select_type | table      | type   | possible_keys          | key            | key_len | ref                        | rows | Extra       |
+----+-------------+------------+--------+------------------------+----------------+---------+----------------------------+------+-------------+
|  1 | SIMPLE      | film       | ALL    | PRIMARY                | NULL           | NULL    | NULL                       |  994 |             |
|  1 | SIMPLE      | film_actor | ref    | PRIMARY,idx_fk_film_id | idx_fk_film_id | 2       | sakila.film.film_id        |    1 | Using index |
|  1 | SIMPLE      | actor      | eq_ref | PRIMARY                | PRIMARY        | 2       | sakila.film_actor.actor_id |    1 |             |
+----+-------------+------------+--------+------------------------+----------------+---------+----------------------------+------+-------------+
3 rows in set (0.00 sec)

mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 2197.800000 |
+-----------------+-------------+
1 row in set (0.01 sec)

mysql> explain
    -> SELECT  film.film_id, film.title, film.release_year, actor.actor_id,
    -> actor.first_name, actor.last_name
    -> FROM sakila.film
    -> INNER JOIN sakila.film_actor USING(film_id)
    -> INNER JOIN sakila.actor USING(actor_id);
+----+-------------+------------+--------+------------------------+---------+---------+---------------------------+------+-------------+
| id | select_type | table      | type   | possible_keys          | key     | key_len | ref                       | rows | Extra       |
+----+-------------+------------+--------+------------------------+---------+---------+---------------------------+------+-------------+
|  1 | SIMPLE      | actor      | ALL    | PRIMARY                | NULL    | NULL    | NULL                      |  200 |             |
|  1 | SIMPLE      | film_actor | ref    | PRIMARY,idx_fk_film_id | PRIMARY | 2       | sakila.actor.actor_id     |    1 | Using index |
|  1 | SIMPLE      | film       | eq_ref | PRIMARY                | PRIMARY | 2       | sakila.film_actor.film_id |    1 |             |
+----+-------------+------------+--------+------------------------+---------+---------+---------------------------+------+-------------+
3 rows in set (0.00 sec)

mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| Last_query_cost | 440.999000 |
+-----------------+------------+
1 row in set (0.00 sec)