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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/758322/viewspace-680925/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/758322/viewspace-680925/