mysql 服务器性能不是很好， 如果执行如下的 sql 语句
update table set status = 1 where order_id = "xxxxx"
order_id 上有索引，一次更新的数据量有 60w 条，需要耗时 60 秒左右，数据库 cpu 会到 80%以上。
排除升级机器，有啥好办法优化这种场景吗
› MySQL 5.5 Community Server
› MySQL 5.6 Community Server
› Percona Configuration Wizard
› XtraBackup 搭建主从复制
Great Sites on MySQL
› Percona
› MySQL Performance Blog
› Severalnines
推荐管理工具
› Sequel Pro
› phpMyAdmin
推荐书目
› MySQL Cookbook
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
MySQL 相关项目
› MariaDB
› Drizzle
参考文档
› http://mysql-python.sourceforge.net/MySQLdb.html
This topic created in 2091 days ago, the information mentioned may be changed or developed.
Supplement 1 · Jan 16, 2021
谢谢大家的回复，
现在我的做法是这样的, 需求将状态为 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 Jan 15, 2021
分批更新
|
 |
2
owenliang Jan 15, 2021
select 一批出来，再 update 这批回去。
|
 |
3
love Jan 15, 2021
以前看过 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 Jan 15, 2021
关注下大佬们如何解决 ///
|
 |
7
cheng6563 Jan 15, 2021 via Android
@git00ll 没啥办法的，耗时就是有那么久。不 limt 他就跑最高 CPU 快速跑完，加 limit 分批跑其实就是把 CPU 时间让给其他程序。
|
 |
8
AngryPanda Jan 15, 2021
如果不影响业务，可以适当 sleep
|
 |
9
guxingke Jan 15, 2021
业务上看看能否不更新这么多数据，比如把状态放到单独一张表管理
order_id status === 每次更新一条即可 |
 |
10
unbright Jan 15, 2021
innodb_write_io_threads
|
 |
11
cway Jan 15, 2021
加钱提高性能
|
 |
13
zhengfuchao2008 Jan 15, 2021
定时任务，每天跑的数量 > 每天新插入的数量 即可
删历史数据也是这么玩 |
 |
14
xx6412223 Jan 15, 2021 via Android
把 orderid 和 status 单独建变，这样每次只更新一条数据就行了
|
 |
15
mchl Jan 16, 2021 via iPhone  1
start transaction;
update... commit; 有时开事务有奇效哦 |
 |
16
Rocketer Jan 16, 2021 via iPhone
难道不是应该先定位问题，然后头痛医头，脚痛医脚吗？
大量更新本就应该速度慢、cpu 占用高，但如果这台服务器是独占的，那就不是问题，也不用优化。 所以你打算怎么优化，得看这个更新如何影响了你。比如你这服务器还有别的高优先级写入请求，那就弄个优先级队列，让服务器在没有高优先级请求的时候慢慢更新。要是你这个大更新有强一致性要求，必须一次完成，那其实你就没有什么技术解决的办法，只能换个不忙的时间来做。 |
 |
17
longchen888 Jan 16, 2021
分页筛选数据出来，为了避免重复，可以根据 ID 正序排列(id > xxx 的方式)，进行批量更新，每页条数找个合理的数值;反正是定时任务，放到凌晨慢慢跑
|
 |
18
mostkia Jan 16, 2021
定时任务吧，吧事务放到凌晨这类低负载的时间段更新，可以适当将整张表分段更新，减少瞬时 cpu 负载，反正凌晨服务器一般有大把 cpu 空闲时间。
|
 |
19
zch693922 Jan 16, 2021
《高性能 MySQL 》 第六章 查询性能优化 ---> 一个复杂查询还是多个简单查询（书不在身边，忘记标题了...） 的前面一页的例子和你情况差不多。
分解这个 SQL，分批执行就可以了。 |
 |
20
xiaomu8 Jan 16, 2021 via iPad
又想要性能，又想要速度…
这不就是又想马跑的快，又不给马吃草，哪有这么好的事。 有个稍微麻烦点的思路加个缓存层，跑定是任务将缓存数据慢慢更新至 sql 就好了，类似队列削峰。读取状态时以缓存加 mysql 做判断。这样既保持了数据的实时性，有减缓了 cpu 。 |
 |
21
winglight2016 Jan 16, 2021
一分钟更新 60w 条记录，这个速度很慢吗？总共有多少记录需要更新？更新频次是多少？希望达到什么样的更新速度？及时性有什么要求？
lz 想做优化就先把约束条件都写下来，自然就有优化方向了，现在啥都没有，换高性能机器是最靠谱的办法。 |
 |
22
siyemiaokube Jan 17, 2021 via iPhone
一个可能的方法是把表拆分一下
不过 sql 其实本身不太适合大批量更新，如果你们只是需要根据 order_id 修改确定的属性、而几乎不需要根据其他的属性进行修改操作，那么，可以考虑更换别的数据结构。 不过在我的知识中，出现这种情况是因为设计的不好…… |
Select Language