MySQL 优化分页思路

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头痛的分页场景是“limit 1000,20”，此时 MySQL排序出前 1020条记录后仅仅需要返回第 1001到 1020条记录，前1000条记录都会被抛弃，查询和排序的代价非常高。

 

1．第一种优化思路

在索引上完成排序分页的操作，最后根据主键关联回原表查询所需要的其他列内容

 

EXPLAIN SELECT film_id, description
FROM film
ORDER BY title
LIMIT 50, 5

 

 直接查询的时候，能够从explain的输出结果中看到优化器实际上做了全表扫描，处理效率不高

 

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

 

 按照索引分页后回表方式改写SQL后，从explain的输出结果中已经看不到全表扫描了

这种方式让MySQL扫描尽可能少的页面来提高分页效率。

 

2．第二种优化思路

把LIMIIT查询转换成某个位置的查询

EXPLAIN SELECT *
FROM payment
ORDER BY rental_id DESC
LIMIT 410, 10

翻页的过程中通过增加一个参数last_page_record，用来记录上一页最后一行的租赁编号rental_id，例如第41页最后一行的租赁编号rental_id=15640

那么在翻页到第42页时，可以根据第41页最后一条记录向后追溯，相应的SQL可以改写为

EXPLAIN SELECT *
FROM payment
WHERE rental_id < 15640
ORDER BY rental_id DESC
LIMIT 10

注意，这样把LIMIT m,n 转换成LIMIT n的查询，只适合在排序字段不会出现重复值的特定环境，能够减轻分页翻页的压力；如果排序字段出现大量重复值，而仍进行这种优化，那么分页结果可能会丢失部分记录，不适用这种方式进行优化。