V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
qq2549112
V2EX  ›  问与答

请问 mysql 大数据量 limit 分页, 时间戳 排序 ,如何优化

  •  
  •   qq2549112 · 2015-07-05 16:07:53 +08:00 · 7159 次点击
    这是一个创建于 2708 天前的主题,其中的信息可能已经有所发展或是发生改变。

    数据库:mysql
    开发语言:python

    ORM:sqlalchemy 或者 原生sql

    需求是 把mysql里面的数据 实时增量 同步到 postgresql里面

    [mysql to postgresql]

    [首次同步,拉取所有数据, 而后,每次只拉取最近更新的数据]

    mysql 表结构:

    uid <varchar 32> 主键,并非自增长,随机生成的
    user <varchar 32>
    pwd <varchar 32>
    url <varchar 32>
    created <datetime> 加入了索引
    modified <datetime> 加入了索引 [该记录最后更改时间,若这个记录变动,该字段也会改变]


    上面就是mysql的结构,

    [并且我无权修改mysql的表结构,这是别人提供给我的帐号密码]

    目前想到的 最妥当的方案,也只有 根据 modified 来同步数据

    我的sql如下:

    SELECT * FROM table ORDER BY modified ASC LIMIT 100,100


    当offset小的时候查询起来,速度上没有任何问题,但当我查询到5w左右的时候,非常慢,慢到无法接受,几分钟都没有反映

    sql语句如下:

    SELECT * FROM table ORDER BY modified ASC LIMIT 50000,100


    网上的文章,我也参考了
    如: http://www.fienda.com/archives/110

    我这个 没有id字段
    只有时间戳,
    所以无法用文章里的优化方法
    我也在谷歌上找了好久,但是没有找到好的方案

    请问各位前辈

    可有好的方案,

    非常感谢

    7 条回复    2020-10-20 19:10:28 +08:00
    wy315700
        1
    wy315700  
       2015-07-05 16:10:39 +08:00
    那就保存上次查询的时间戳

    遇到时间戳一样的话,那就把上次的时间戳 - 1作为下次查询的时间
    qq2549112
        2
    qq2549112  
    OP
       2015-07-05 16:33:02 +08:00
    @wy315700 谢谢
    您的意思是:

    先 SELECT * FROM table ORDER BY modified ASC LIMIT 100

    就会得到100个记录,我得出最后一个 记录的 modified

    下次就
    SELECT * FROM table WHERE modified >=上次的时间戳 ORDER BY modified ASC LIMIT 100


    再下次就
    SELECT * FROM table WHERE modified >=上次的时间戳 ORDER BY modified ASC LIMIT 100


    是这样吗
    wy315700
        3
    wy315700  
       2015-07-05 16:36:05 +08:00
    @qq2549112 就是这样,只要保证时间戳是递增的就行,如果怕漏掉就用
    上次的时间戳 - 1
    qq2549112
        4
    qq2549112  
    OP
       2015-07-05 16:39:20 +08:00
    @wy315700 谢谢
    lucky2touch
        5
    lucky2touch  
       2015-07-05 17:22:50 +08:00
    5w应该不算多大数据吧......分析下执行计划呢。
    iyangyuan
        6
    iyangyuan  
       2015-07-06 08:30:03 +08:00 via iPhone
    limit优化最简单的办法就是保存上一页的最后一条记录id,查询的时候直接用这个id过滤掉之前的数据,加快查询速度
    sunqb
        7
    sunqb  
       2020-10-20 19:10:28 +08:00
    @wy315700 你这个方法没用啊,举例:

    我有 1000 条数据的时间戳都是一样的,假设时间戳为 12345 。但是我的 limit 为 20 。

    第一次查询:
    获取到 20 条数据的时间戳都是 12345

    第二次查询:
    时间戳>= 12345-1
    获取到的仍然是时间戳为 12345 的前 20 条数据。

    会死循环。
    关于   ·   帮助文档   ·   API   ·   FAQ   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   2867 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 42ms · UTC 08:06 · PVG 16:06 · LAX 00:06 · JFK 03:06
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.