分页查询优化

优化概述

分页查询优化可以从如下2个维度来做：
1.设计层面
2.SQL层面

设计层面

产品设计时，界面上不要显示总数，只显示页码，如：“上一页 1 2 3 下一页”。

将具体的页数换成“下一页”按钮，假设每页显示20条记录，那么每次查询时都是用LIMIT返回21条记录并只显示20条，如果第21条存在，那么就显示“下一页”按钮。
先获取并缓存较多的数据（例如1000条），然后每次分页都从缓存中获取。这样做可以让应用程序根据结果集的大小采取不同策略，如果结果集少于1000，就可以在页面上显示所有的分页连接；如果结果集大于1000，则可以在页面上设计一个额外的“找到的结果多于1000条”之类的按钮。

SQL层面

优化思路：

(1)尽可能使用索引覆盖扫描，而不是查询所有的列，然后根据需要做一次关联操作再返回所需的列（延迟关联）
(2)将limit查询转换为已知位置的查询，让mysql通过范围扫描获得对应的结果（范围扫描）

具体实践：

(1)不要使用简单的分页查询方式直接到数据库查询，如：SELECT * FROM order LIMIT 100000, 10
(2)如果主键ID连续递增（没有数据记录被物理删除的情况下可以保证），根据主键ID按范围查询优化：

SELECT * FROM order WHERE id > 100000 AND id <= 100010 ORDER BY id;

或者

SELECT * FROM order WHERE id > 100000 LIMIT 10 ORDER BY id;

或者

SELECT * FROM order WHERE id >= (SELECT id FROM order ORDER BY id limit 100000,1) limit 0,10;

(3)如果主键ID不连续，可以使用如下优化方式

• 子查询优化

SELECT * FROM WHERE id IN (SELECT id FROM order ORDER BY id LIMIT 100000, 10) ORDER BY id;

• 延迟关联优化

SELECT * FROM order INNER JOIN (SELECT id FROM order LIMIT 100000,10) AS lim USING(id);