使用的 MySQL 官方示例表 sakila.film。
索引情况如下所示,均为 BTREE 索引:
需要对film 根据标题 title 排序后取某一页的数据,直接查询
EXPLAIN SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;
可以看到,优化器执行了全表扫描,我想通过按照索引分页后回表的方式改写 SQL,所以重写 SQL 如下:
#按照索引分页后回表查询EXPLAIN SELECT a.`film_id`,a.`description` FROM film aINNER JOIN (SELECT film_id FROM film ORDER BY title LIMIT 50,5) b ON a.`film_id`=b.`film_id`;
但是在 id 为 2 的查询过程中,虽然 type 为 index,但是实际上仍然扫描了全表这是为什么呢?
解决方案
事实上,你改写成 INNER JOIN
后,性能已经得到了改进。
第一条查询的 cost 为:
mysql> select film_id, description from film order by title limit 50,5;mysql> show status like 'Last_query_cost';+-----------------+------------+| Variable_name | Value |+-----------------+------------+| Last_query_cost | 209.799000 |+-----------------+------------+
第二条查询的 cost 为:
mysql> select a.film_id, a.description from film a inner join (select film_id from film c order by title limit 50,5) b on a.film_id = b.film_id;mysql> show status like 'Last_query_cost'; +-----------------+----------+| Variable_name | Value |+-----------------+----------+| Last_query_cost | 0.000000 |+-----------------+----------+
所以你的目的已经达到了。
改进的原因是,使用 INNER JOIN
后,覆盖索引(covering index)发挥了作用,整个匹配过程只需要 film_id 和 title,而它们都有索引。description
字段只在匹配完后才会去读取,所以节省了大量 IO。
下面来简单分析一下第二条查询。
MySQL 执行 JOIN 的算法,本质上就是简单的嵌套循环,最外层循环总是线性遍历,不会用索引的(这里是临时表,也没有索引),内层循环有索引则会使用索引。所以外层循环的大小,直接决定了 JOIN 的性能。
从 explain 的结果来看,MySQL 把 SELECT
子句得到的表 b
放在了外层循环,这个表只有 55 行记录,所以 MySQL 决定先从它开始,访问类型为 ALL
,意即扫描全表,也就是 55 行记录。
*************************** 1. row *************************** id: 1 select_type: PRIMARY table: <derived2> type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 55 Extra: NULL
第二层循环基于表 a
,这里只需要查找 film_id
,用上了主键索引,非常快。记住,匹配之后才会有第三层循环。
*************************** 2. row *************************** id: 1 select_type: PRIMARY table: a type: eq_refpossible_keys: PRIMARY key: PRIMARY key_len: 2 ref: b.film_id rows: 1 Extra: NULL
第三层循环基于 SELECT
子句里的表 film
,匹配的是 title
,也用上了索引,非常快。
*************************** 3. row *************************** id: 2 select_type: DERIVED table: c type: indexpossible_keys: NULL key: idx_title key_len: 767 ref: NULL rows: 989 Extra: Using index
你问这里为什么还是扫描了全表?其实应该不是,如果要扫面全表,访问类型应该是 ALL 而不是 index。所以不用担心。
你的这个优化写法,正好也出现在 High Performance MySQL 第六章,我正好看到。