mysql把表分页_MySql大表分页如何做？

问题背景

MySql(InnoDB)中的订单表需要按时间顺序分页查询，且主键不是时间维度递增，订单表在百万以上规模，此时如何高效地实现该需求？

方案1

一般情况下,大家的分页都会采用 MySql里边的 limit offset, pageSize的用法来实现分页查询

select * from order where user_id = xxx order by created_time, id limit offset, pageSize

因为created_time可能重复，所以order by时应加上id，保证顺序的确定性

说明：该方案在表规模较小的时候，不会暴露出问题，当order表增长到十万级，并且查询后面几页的时候，执行速度明显变慢，可能降到100ms的量级，如果数据量增长到百万级，则耗时达到秒级，如果增长到千万级，那耗时就变得完全不可接受了

分析：方案1为啥在大表中表现这么差呢？

假设我们在user_id，created_time，以及【其它业务条件】建立了联合索引，当我要查找第100000条到100049条的记录时，因为MySql的索引是b+ tree结构，不像数组可以随机定位到第N条记录，它需要花不小的成本去找到N的位置，N越大，成本越大

抛开b+ tree的细节不讲，我们还可以借助统计表记录总数的SQL来理解

select count(1) from order

如果能非常高效地定位第N条记录，那么上述统计也能非常高效的执行，但实际上，在大表中统计记录总条数，也是非常慢的(本文是在InnoDB的场景下)

方案1低效的根本原因在于：定位到offset的成本过高，未能充分利用索引的有序性

方案2

索引(b+ tree)的特点在于，数据是有序的，虽然找到第N条记录的效率比较低，但找到某一条数据在索引中的位置，其效率是很高的(索引本来就是解决这个问题的)

我们换一种思路，每次取50条记录，第一次取的时候，指定从上次结束的位置继续往后取50条，这样，我们便可以利用上索引的有序性了

我们先看一个以id为序，进行分页查询的例子

select * from order where id > 'pre max id' order by id limit 50

第一次查询不用带条件，后续查询则传入前一次查询的最大id，简单分析可知，MySql在执行时，先定位到pre max id的位置(id是有序的，定位非常快)，然后从这往后取50条记录即可，整个过程非常高效

说明:上述方法确实可以解决漏掉数据或重复的问题，并且也有着不错的性能，但缺点也比较明显，查询过于复杂，得分情况执行不同的SQL，并且分页不稳定，中间查询出来的记录数可能小于pageSize(如果没有重复项，那会多出一倍的结果为空的查询)，实际上后面还有数据

方案3

由于有了a>x or (a=x and b>y)这种等价于组合比较的语法，且能正确地使用索引，所以可以写出高效且还算简洁的SQL

select * from order

where user_id = xxx

and 【其它业务条件】

and (created_time > 'created_time of latest recode'

or (created_time = 'created_time of latest recode' and id > 'id of latest recode')

)

order by created_time, id limit pageSize

注意：

这里也不允许created_time为null，因为null值参与>和=运算，结果一律为null，即条件不成立，相应结果查不出来。

如果存在为null的情况，则要作一些调整，如果前一批数据的最后一条记录的created_time为null(null在索引中被视作极小值)，则可以这样改：

(created_time is not null or (created_time is null and id > 'id of latest recode'))

仍旧可以走索引，实现高效分页查询。

总结

方案1在小表的情况下，简单方便，只用传页码和页大小即可，还可以随机跳到指定页，具有一定优势

方案2和方案3在大表的情况下，有着优异的性能，以及稳定性，缺点是不能随机地跳转页面，需要传入上一页的排序字段。这个弊端在一定程度上可以规避，比如现在很多分页都是一页一页地往下翻，比如微博、朋友圈动态等，或者是分批处理全表数据，不需要随机跳转