一般分页查询时,通过创建覆盖索引能够比较好地提高性能。一个常见又非常头痛的分页场景是“limit 1000,20”,此时 MySQL排序出前 1020条记录后仅仅需要返回第 1001到 1020条记录,前1000条记录都会被抛弃,查询和排序的代价非常高。
1.第一种优化思路
在索引上完成排序分页的操作,最后根据主键关联回原表查询所需要的其他列内容
EXPLAIN SELECTfilm_id, descriptionFROMfilmORDER BYtitle
LIMIT50, 5
直接查询的时候,能够从explain的输出结果中看到优化器实际上做了全表扫描,处理效率不高
EXPLAIN SELECTa.film_id, a.descriptionFROMfilm aINNER JOIN(SELECTfilm_idFROMfilmORDER BYtitle
LIMIT50, 5) bON a.film_id = b.film_id
按照索引分页后回表方式改写SQL后,从explain的输出结果中已经看不到全表扫描了
这种方式让MySQL扫描尽可能少的页面来提高分页效率。
2.第二种优化思路
把LIMIIT查询转换成某个位置的查询
EXPLAIN SELECT *
FROMpaymentORDER BY rental_id DESCLIMIT410, 10
翻页的过程中通过增加一个参数last_page_record,用来记录上一页最后一行的租赁编号rental_id,例如第41页最后一行的租赁编号rental_id=15640
那么在翻页到第42页时,可以根据第41页最后一条记录向后追溯,相应的SQL可以改写为
EXPLAIN SELECT *
FROMpaymentWHERE rental_id < 15640
ORDER BY rental_id DESCLIMIT10
注意,这样把LIMIT m,n 转换成LIMIT n的查询,只适合在排序字段不会出现重复值的特定环境,能够减轻分页翻页的压力;如果排序字段出现大量重复值,而仍进行这种优化,那么分页结果可能会丢失部分记录,不适用这种方式进行优化。