mysql优化 - 关于 MySQL 分页查询的优化

Question

使用的 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 a
INNER JOIN (SELECT film_id FROM film ORDER BY title LIMIT 50,5) b ON a.`film_id`=b.`film_id`;

但是在 id 为 2 的查询过程中，虽然 type 为 index，但是实际上仍然扫描了全表这是为什么呢？

Answer 1

事实上，你改写成 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: ALL
possible_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_ref
possible_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: index
possible_keys: NULL
          key: idx_title
      key_len: 767
          ref: NULL
         rows: 989
        Extra: Using index

你问这里为什么还是扫描了全表？其实应该不是，如果要扫面全表，访问类型应该是 ALL 而不是 index。所以不用担心。

你的这个优化写法，正好也出现在 High Performance MySQL 第六章，我正好看到。

Answer 2

INNER JOIN用法就是把符合要求的元组取出来，肯定要扫描全表。
id 为 2 的查询过程中。。。你为什么没有在后面加and a.film_id=2；

Answer 3

你这样写有什么用？