优化分页查询

在系统中需要进行分页操作的时候，我们通常会使用 LIMIT 加上偏移量的办法实现，同时加上合适的 ORDER BY 子句。如果有对应的索引，通常效率会不错，否则，MySQL 需要做大量的文件排序操作。

一个非常常见又令人头疼的问题是，在偏移量非常大的时候，例如，可能是 LIMIT 10000, 20 这样的查询，这时 MySQL 需要查询 10020 条记录然后只返回最后 20 条，前面 10 000 条记录都将被抛弃，这样的代价非常高。如果所有的页面被访问的频率都相同，那么这样的查询平均需要访问半个表的数据。要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能。

第一种优化思路（延迟联接）

在索引上完成排序分页的操作，最后根据主键关联回原表查询所需要的其他列的内容。这种方法允许服务器在不访问行的情况下检查索引中尽可能少的数据，一旦找到所需的行，就将它们与整个表联接，以从该行中检索其他列。

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           |1         |1        |2          |
+-------------+--------------------------------+
|select_type  |PRIMARY   |PRIMARY  |DERIVED    |
+-------------+--------------------------------+
|table        |<derived2>|a        |film       |
+-------------+--------------------------------+
|partitions   |NULL      |NULL     |NULL       |
+-------------+--------------------------------+
|type         |ALL       |eq_ref   |index      |
+-------------+--------------------------------+
|possible_keys|NULL      |PRIMARY  |NULL       |
+-------------+--------------------------------+
|key          |NULL      |PRIMARY  |idx_title  |
+-------------+--------------------------------+
|key_len      |NULL      |2        |514        |
+-------------+--------------------------------+
|ref          |NULL      |b.film_id|NULL       |
+-------------+--------------------------------+
|rows         |55        |1        |55         |
+-------------+--------------------------------+
|filtered     |100       |100      |100        |
+-------------+--------------------------------+
|Extra        |NULL      |NULL     |Using index|
+-------------+--------------------------------+

第二种优化思路（把 LIMIIT 查询转换成某个位置的查询）

例如，可以和开发人员协商一下，翻页的过程中增加一个参数 last_page_record，用来记录上一页最后一行的租赁编号 rental_id。

假如第 41 页最后一行的租赁编号 rental_id=15640，当想要获取 42 页的内容时，可以使用下面的 SQL 语句进行查询：

SELECT *
FROM rental
WHERE rental_id < 15640
ORDER BY rental_id DESC
LIMIT 10;