mysql 多张表 分页_MySql大表分页(附独门秘技)

问题背景

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

注：本文并非主要讲解如何建立索引，以下的分析均建立在有合适的索引的前提下

初步方案1

众所周知，MySql中，有一个limit offset, pageSize的用法，可以实现分页查询

select * from order where user_id = xxx and 【其它业务条件】 order by created_time, id limit offset, pageSize

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

点评

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

深入分析

方案1为啥在大表中表现这么差呢？我们可以来揣测一下MySql是怎么执行这个查询的

假设我们在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为序，进行分