mysql 一次更新大量数据，速度慢， cpu 占用高，有啥优化思路吗

[git00ll]

mysql 服务器性能不是很好， 如果执行如下的 sql 语句

update table set status = 1 where order_id = "xxxxx"

order_id 上有索引，一次更新的数据量有 60w 条，需要耗时 60 秒左右，数据库 cpu 会到 80%以上。

排除升级机器，有啥好办法优化这种场景吗

[vindac]

分批更新

[owenliang]

select 一批出来，再 update 这批回去。

[love]

以前看过 django 源码的内置级联删除也是分批删除的，不是靠直接走 mysql 一条语句

[git00ll]

@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 到的数据)

放在循环里更新，同样导致数据库压力

[git00ll]

@love
目前我们的做法是
update table set status = 1 where order_id = "xxxxx" and status = 2 limit 1000
然后定时执行，问题是，如果定时频率快了 cpu 扛不住，定时频率慢了，速度太慢，60w 要更新好几十分钟

[cryboy007]

关注下大佬们如何解决 ///

[cheng6563]

@git00ll 没啥办法的，耗时就是有那么久。不 limt 他就跑最高 CPU 快速跑完，加 limit 分批跑其实就是把 CPU 时间让给其他程序。

[AngryPanda]

如果不影响业务，可以适当 sleep

[guxingke]

业务上看看能否不更新这么多数据，比如把状态放到单独一张表管理

order_id
status

===
每次更新一条即可

[unbright]

innodb_write_io_threads

[cway]

加钱提高性能

[owenliang]

@git00ll 压力在所难免，这样是为了让你压力可控。

[zhengfuchao2008]

定时任务，每天跑的数量 > 每天新插入的数量 即可

删历史数据也是这么玩

[xx6412223]

把 orderid 和 status 单独建变，这样每次只更新一条数据就行了

[mchl]

start transaction;
update...
commit;

有时开事务有奇效哦

[Rocketer]

难道不是应该先定位问题，然后头痛医头，脚痛医脚吗？

大量更新本就应该速度慢、cpu 占用高，但如果这台服务器是独占的，那就不是问题，也不用优化。

所以你打算怎么优化，得看这个更新如何影响了你。比如你这服务器还有别的高优先级写入请求，那就弄个优先级队列，让服务器在没有高优先级请求的时候慢慢更新。要是你这个大更新有强一致性要求，必须一次完成，那其实你就没有什么技术解决的办法，只能换个不忙的时间来做。

[longchen888]

分页筛选数据出来，为了避免重复，可以根据 ID 正序排列(id > xxx 的方式)，进行批量更新，每页条数找个合理的数值;反正是定时任务，放到凌晨慢慢跑

[mostkia]

定时任务吧，吧事务放到凌晨这类低负载的时间段更新，可以适当将整张表分段更新，减少瞬时 cpu 负载，反正凌晨服务器一般有大把 cpu 空闲时间。

[zch693922]

《高性能 MySQL 》 第六章 查询性能优化 ---> 一个复杂查询还是多个简单查询（书不在身边，忘记标题了...） 的前面一页的例子和你情况差不多。
分解这个 SQL，分批执行就可以了。

[xiaomu8]

又想要性能，又想要速度…
这不就是又想马跑的快，又不给马吃草，哪有这么好的事。
有个稍微麻烦点的思路加个缓存层，跑定是任务将缓存数据慢慢更新至 sql 就好了，类似队列削峰。读取状态时以缓存加 mysql 做判断。这样既保持了数据的实时性，有减缓了 cpu 。