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

2015-07-05 16:07:53 +08:00
 qq2549112

数据库: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字段
只有时间戳,
所以无法用文章里的优化方法
我也在谷歌上找了好久,但是没有找到好的方案

请问各位前辈

可有好的方案,

非常感谢

7681 次点击
所在节点    问与答
7 条回复
wy315700
2015-07-05 16:10:39 +08:00
那就保存上次查询的时间戳

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

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

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

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

会死循环。

这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/203463

V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX