MySQL查询优化（九）：分页查询优化技巧

在有分页查询的应用中，包括 LIMIT 和 OFFSET 的查询十分常见，而且几乎每个都会有一个 ORDER BY 子句。如果使用索引排序的话将对性能优化十分有帮助，否则服务端需要做很多文件排序。

一个高频的问题是 offset 的值过大。如果查询类似 LIMIT 10000, 20，将会产生10020行，并将之前的10000行丢弃，这样的代价很高。假设所有的页使用相同的频次访问，这样的查询将平均扫描一半数据表。为了优化他们，你可以在分页视图中限制最多可访问的页数，或者让大便宜的查询更有效。

一个改善性能简单的技巧是在覆盖索引上进行查询操作而不是整行数据。你可以将结果与完整的行做一次联合然后再获取额外需要的列。这样的效率会更高，例如下面的查询：

SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;
复制代码

如果数据表很大的话，则可以按下面的方式进行优化：

SELECT film.film_id, film.description
FROM sakila.film
	INNER JOIN (
    SELECT film_id FROM sakila.film
    ORDER BY title LIMIT 50, 5)
  ) as lim USING(film_id);
复制代码

这种“推断联合查询”能够有效工作是因为它使用了索引减少了服务端尽可能少地访问数据行去检查数据。一旦复核要求的行查到了，将他们与对应的数据表的行进行联合查询以获取对应行的其他列。

有些时候也可以将 limit 转换为固定位置的查询，这种方式可以对索引进行范围扫描完成。例如，如果你预先计算一个固定位置的列 称之为 position，可以重写查询如下：

SELECT film_id, description FROM sakila.film
WHERE position BETWEEN 50 AND 54 ORDER BY position;
复制代码

排序的数据也可以使用类似的方式解决，但是通常会被 GROUP BY操作影响。大部分情况下需要提前计算和存储排序值。

LIMIT 和 OFFSET 真正的问题是在OFFSET，这意味着服务端会把很多数据行丢弃。如果使用一个有序书签来记录下次获取行的位置的话，则可以从上次的位置开始访问接下来的数据。例如，如果你需要对出租记录进行分页，从最新的出租记录开始往回查询，则可以依赖于记录的主键是一直增加的，因此可以对第一页数据这样查询：

SELECT * FROM sakila.rental
ORDER BY rental_id DESC LIMIT 20;
复制代码

这个查询返回16049到16030之间的数据。接下来的查询可以从之前结束位置开始：

SELECT * FROM sakila.rental
WHERE rental_id < 16030 
ORDER BY rental_id DESC LIMIT 20;
复制代码

这个技巧不管你从多远的偏移值开始查询都是很有效的。

其他的一些技巧包括使用预先计算的统计值，或者通过联合冗余了主键和排序列的数据表进行查询，这两种方式都是通过空间换取时间的方式提高查询效率。