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.descriptionFROM sakila.filmINNER 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.filmWHERE position BETWEEN 50 AND 54 ORDER BY position;

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

SELECT * FROM sakila.rentalORDER BY rental_id DESC LIMIT 20;

SELECT * FROM sakila.rentalWHERE rental_id < 16030 ORDER BY rental_id DESC LIMIT 20;

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

更多相关文章

随机推荐