MySQL——常用SQL优化（五）

一、分页优化查询。

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题：“limit 10000,10”，此时MySQL排序出10020条记录后仅仅返回第10001到10020条记录，前10000条记录都会被抛弃，查询和排序的代价非常高。

1、第一种优化思路。

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。例如：对电影表film根据标题title排序后取得某一页的数据，直接查询的时候，可以从explain的输出结果中看到优化器实际上做了全表扫描，效率不高。

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

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

2、第二种优化思路。

把limit查询转换为某个位置的查询，例如：假设每页10条记录，查询支付表payment中按照租凭编号rental_id逆序排序的第42页记录，能够看到执行计划走了全表扫描。

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

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

注意：这样把limit m,n转换为limit n的查询，只适合在排序字段不会出现重复值的特定环境，能够减轻分页翻页的压力；如果排序字段出现大量重复值，仍然使用这样的优化分页，就会造成记录丢失，不该使用这样的方式优化分页。