优化mysql大数据表的分页性能

工作中实现了一个时间统计功能：数据必须按照指定的顺序从源表中提取出来，经过分组合并后插入到目标表中。

源表数据量相当大，几千万行，显然不适合一次性检索（如果是一次性脚本，也可以考虑在一台机器上内存大，但是定时任务每次启动都会占用数据（10GB内存也太大了）并且需要分页。

但是最初的实现使用了封装的分页库，简单的全表查询，仅仅基于limit子句来限制结果集窗口，SQL语句类似这样：

select * from A order by x, y limit 30000, 10000

Where field x and field y 是普通索引，每页返回10000个元素。

结果很糟糕。每个页面查询需要40秒才能返回，这样的查询要循环数千次，需要半天时间才能完成。

解决办法也很简单。使用自定义分页机制，根据字段过滤实现分页 写下来，作为“x > ?”的评估条件在下一页。

python + sqlalchemy 代码示例如下：

PAGE_SIZE = 10000

last_x = 0    # 这里假设 x 永远是大于零的整数，如果不是，初始化一个最小值
while last_x == 0 or len(records > 0):
    # last_x == 0 这个条件，相当于判断是否第一次循环，这里其实有 do...while 语句更好，可惜 python 没有
    records = A.query.filter(A.x > last_x).order_by(A.x, A.y).limit(PAGE_SIZE)
    last_x = records[-1].x
    # do something