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

2022年11月4日19:40:52数据库教程评论72 views字数 697阅读模式

工作中实现了一个定时统计功能:需要按指定顺序,从源表中取出数据,经过分组合并,插入目标表。

源表数据量相当大,有几千万行,显然不适合一次性取出(如果是一次性的脚本,在大内存的机器上也是可以考虑的,但定时任务每次启动都占用数十GB内存就太夸张了),需要分页查询。

但最初的实现中,采用了一个封装好的分页库,单纯的全表查询,纯粹依赖limit子句限制结果集窗口,构成的SQL语句类似这样:

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

其中字段 x 和字段 y 是有联合索引的,每页返回 10000 条。

结果惨不忍睹,每页查询需要40秒才能返回,而这样的查询需要循环几千次,整整半天时间都没执行完。


解决方案也很简单,使用自定义的分页机制,基于字段 x 筛选实现分页:

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

注意:这里的 30000,只是示例,每次要把上一页最后一条的 x 值记下来,当做下一页"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

  • 本站内容整理自互联网,仅提供信息存储空间服务,以方便学习之用。如对文章、图片、字体等版权有疑问,请在下方留言,管理员看到后,将第一时间进行处理。
  • 转载请务必保留本文链接:https://www.cainiaoxueyuan.com/sjk/30022.html
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定