mysql 服务器性能不是很好, 如果执行如下的 sql 语句
update table set status = 1 where order_id = "xxxxx"
order_id 上有索引,一次更新的数据量有 60w 条,需要耗时 60 秒左右,数据库 cpu 会到 80%以上。
排除升级机器,有啥好办法优化这种场景吗
现在我的做法是这样的, 需求将状态为 2 的记录改为状态 1 ,我使用定时任务执行下面语句
update table set status = 1 where order_id = "xxxxx" and status = 2 limit 1000
每几秒运行一次,直到全部更新完,大概需要20分钟左右,cpu占用由原来的最高80%+ 到现在的60%+,时间上由原来的60秒变成了现在的20分钟,不知道是亏了还是赚了🤣。
这个表字段更改不是很频繁,每天只需要做几次就行,但是比较集中,不可以分散到一天中来做, 有朋友说放在夜里跑,其实在我这个场景下不太行,我这个需求是要和前台交互的,领导在页面上点击按钮,这边的状态就要很快的变化,不可能白天领导点完,晚上才开始执行任务。
有点悖论的意思,要么拿cpu换时间,要么拿时间换cpu。我原来的第一想法是能否从索引入手,加上索引或移除索引,我试过去掉此字段上的所有索引,一次更新能快10秒左右。 数据库表中的记录有五千万条左右,不知道为啥会这么慢
1
vindac 2021-01-15 14:58:57 +08:00
分批更新
|
2
owenliang 2021-01-15 15:33:06 +08:00
select 一批出来,再 update 这批回去。
|
3
love 2021-01-15 15:50:57 +08:00
以前看过 django 源码的内置级联删除也是分批删除的,不是靠直接走 mysql 一条语句
|
4
git00ll OP @owenliang 尝试过这样操作,其实效果也不好
下面是想要把 status 为 2 的改为 1 先选择一批数据 select id from table where order_id = "xxxxx" and status = 2 limit 1000 再更新这一批数据 update table set status = 1 where id in (上面 select 到的数据) 放在循环里更新,同样导致数据库压力 |
5
git00ll OP @love
目前我们的做法是 update table set status = 1 where order_id = "xxxxx" and status = 2 limit 1000 然后定时执行,问题是,如果定时频率快了 cpu 扛不住,定时频率慢了,速度太慢,60w 要更新好几十分钟 |
6
cryboy007 2021-01-15 17:37:32 +08:00
关注下大佬们如何解决 ///
|
7
cheng6563 2021-01-15 17:40:35 +08:00 via Android
@git00ll 没啥办法的,耗时就是有那么久。不 limt 他就跑最高 CPU 快速跑完,加 limit 分批跑其实就是把 CPU 时间让给其他程序。
|
8
AngryPanda 2021-01-15 17:43:04 +08:00
如果不影响业务,可以适当 sleep
|
9
guxingke 2021-01-15 17:45:21 +08:00
业务上看看能否不更新这么多数据,比如把状态放到单独一张表管理
order_id status === 每次更新一条即可 |
10
unbright 2021-01-15 17:48:05 +08:00
innodb_write_io_threads
|
11
cway 2021-01-15 18:01:24 +08:00
加钱提高性能
|
13
zhengfuchao2008 2021-01-15 18:46:44 +08:00
定时任务,每天跑的数量 > 每天新插入的数量 即可
删历史数据也是这么玩 |
14
xx6412223 2021-01-15 22:33:31 +08:00 via Android
把 orderid 和 status 单独建变,这样每次只更新一条数据就行了
|
15
mchl 2021-01-16 07:58:55 +08:00 via iPhone 1
start transaction;
update... commit; 有时开事务有奇效哦 |
16
Rocketer 2021-01-16 08:59:34 +08:00 via iPhone
难道不是应该先定位问题,然后头痛医头,脚痛医脚吗?
大量更新本就应该速度慢、cpu 占用高,但如果这台服务器是独占的,那就不是问题,也不用优化。 所以你打算怎么优化,得看这个更新如何影响了你。比如你这服务器还有别的高优先级写入请求,那就弄个优先级队列,让服务器在没有高优先级请求的时候慢慢更新。要是你这个大更新有强一致性要求,必须一次完成,那其实你就没有什么技术解决的办法,只能换个不忙的时间来做。 |
17
longchen888 2021-01-16 09:05:02 +08:00
分页筛选数据出来,为了避免重复,可以根据 ID 正序排列(id > xxx 的方式),进行批量更新,每页条数找个合理的数值;反正是定时任务,放到凌晨慢慢跑
|
18
mostkia 2021-01-16 10:25:42 +08:00
定时任务吧,吧事务放到凌晨这类低负载的时间段更新,可以适当将整张表分段更新,减少瞬时 cpu 负载,反正凌晨服务器一般有大把 cpu 空闲时间。
|
19
zch693922 2021-01-16 10:32:53 +08:00
《高性能 MySQL 》 第六章 查询性能优化 ---> 一个复杂查询还是多个简单查询(书不在身边,忘记标题了...) 的前面一页的例子和你情况差不多。
分解这个 SQL,分批执行就可以了。 |
20
xiaomu8 2021-01-16 12:13:06 +08:00 via iPad
又想要性能,又想要速度…
这不就是又想马跑的快,又不给马吃草,哪有这么好的事。 有个稍微麻烦点的思路加个缓存层,跑定是任务将缓存数据慢慢更新至 sql 就好了,类似队列削峰。读取状态时以缓存加 mysql 做判断。这样既保持了数据的实时性,有减缓了 cpu 。 |
21
winglight2016 2021-01-16 13:58:27 +08:00
一分钟更新 60w 条记录,这个速度很慢吗?总共有多少记录需要更新?更新频次是多少?希望达到什么样的更新速度?及时性有什么要求?
lz 想做优化就先把约束条件都写下来,自然就有优化方向了,现在啥都没有,换高性能机器是最靠谱的办法。 |
22
siyemiaokube 2021-01-17 11:11:40 +08:00 via iPhone
一个可能的方法是把表拆分一下
不过 sql 其实本身不太适合大批量更新,如果你们只是需要根据 order_id 修改确定的属性、而几乎不需要根据其他的属性进行修改操作,那么,可以考虑更换别的数据结构。 不过在我的知识中,出现这种情况是因为设计的不好…… |