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 
这是一个创建于 1730 天前的主题，其中的信息可能已经有所发展或是发生改变。
mysql 服务器性能不是很好， 如果执行如下的 sql 语句
update table set status = 1 where order_id = "xxxxx" order_id 上有索引，一次更新的数据量有 60w 条，需要耗时 60 秒左右，数据库 cpu 会到 80%以上。
排除升级机器，有啥好办法优化这种场景吗
第 1 条附言 2021-01-16 16:15:58 +08:00
谢谢大家的回复，
现在我的做法是这样的, 需求将状态为 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 修改确定的属性、而几乎不需要根据其他的属性进行修改操作，那么，可以考虑更换别的数据结构。 不过在我的知识中，出现这种情况是因为设计的不好…… |
Select Language