V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
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
git00ll
V2EX  ›  MySQL

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

  •  
  •   git00ll · 2021-01-15 14:48:23 +08:00 · 5624 次点击
    这是一个创建于 1409 天前的主题,其中的信息可能已经有所发展或是发生改变。

    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秒左右。 数据库表中的记录有五千万条左右,不知道为啥会这么慢

    22 条回复    2021-01-17 11:11:40 +08:00
    vindac
        1
    vindac  
       2021-01-15 14:58:57 +08:00
    分批更新
    owenliang
        2
    owenliang  
       2021-01-15 15:33:06 +08:00
    select 一批出来,再 update 这批回去。
    love
        3
    love  
       2021-01-15 15:50:57 +08:00
    以前看过 django 源码的内置级联删除也是分批删除的,不是靠直接走 mysql 一条语句
    git00ll
        4
    git00ll  
    OP
       2021-01-15 17:34:23 +08:00
    @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
        5
    git00ll  
    OP
       2021-01-15 17:36:47 +08:00
    @love
    目前我们的做法是
    update table set status = 1 where order_id = "xxxxx" and status = 2 limit 1000
    然后定时执行,问题是,如果定时频率快了 cpu 扛不住,定时频率慢了,速度太慢,60w 要更新好几十分钟
    cryboy007
        6
    cryboy007  
       2021-01-15 17:37:32 +08:00
    关注下大佬们如何解决 ///
    cheng6563
        7
    cheng6563  
       2021-01-15 17:40:35 +08:00 via Android
    @git00ll 没啥办法的,耗时就是有那么久。不 limt 他就跑最高 CPU 快速跑完,加 limit 分批跑其实就是把 CPU 时间让给其他程序。
    AngryPanda
        8
    AngryPanda  
       2021-01-15 17:43:04 +08:00
    如果不影响业务,可以适当 sleep
    guxingke
        9
    guxingke  
       2021-01-15 17:45:21 +08:00
    业务上看看能否不更新这么多数据,比如把状态放到单独一张表管理

    order_id
    status

    ===
    每次更新一条即可
    unbright
        10
    unbright  
       2021-01-15 17:48:05 +08:00
    innodb_write_io_threads
    cway
        11
    cway  
       2021-01-15 18:01:24 +08:00
    加钱提高性能
    owenliang
        12
    owenliang  
       2021-01-15 18:38:50 +08:00
    @git00ll 压力在所难免,这样是为了让你压力可控。
    zhengfuchao2008
        13
    zhengfuchao2008  
       2021-01-15 18:46:44 +08:00
    定时任务,每天跑的数量 > 每天新插入的数量 即可

    删历史数据也是这么玩
    xx6412223
        14
    xx6412223  
       2021-01-15 22:33:31 +08:00 via Android
    把 orderid 和 status 单独建变,这样每次只更新一条数据就行了
    mchl
        15
    mchl  
       2021-01-16 07:58:55 +08:00 via iPhone   ❤️ 1
    start transaction;
    update...
    commit;

    有时开事务有奇效哦
    Rocketer
        16
    Rocketer  
       2021-01-16 08:59:34 +08:00 via iPhone
    难道不是应该先定位问题,然后头痛医头,脚痛医脚吗?

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

    所以你打算怎么优化,得看这个更新如何影响了你。比如你这服务器还有别的高优先级写入请求,那就弄个优先级队列,让服务器在没有高优先级请求的时候慢慢更新。要是你这个大更新有强一致性要求,必须一次完成,那其实你就没有什么技术解决的办法,只能换个不忙的时间来做。
    longchen888
        17
    longchen888  
       2021-01-16 09:05:02 +08:00
    分页筛选数据出来,为了避免重复,可以根据 ID 正序排列(id > xxx 的方式),进行批量更新,每页条数找个合理的数值;反正是定时任务,放到凌晨慢慢跑
    mostkia
        18
    mostkia  
       2021-01-16 10:25:42 +08:00
    定时任务吧,吧事务放到凌晨这类低负载的时间段更新,可以适当将整张表分段更新,减少瞬时 cpu 负载,反正凌晨服务器一般有大把 cpu 空闲时间。
    zch693922
        19
    zch693922  
       2021-01-16 10:32:53 +08:00
    《高性能 MySQL 》 第六章 查询性能优化 ---> 一个复杂查询还是多个简单查询(书不在身边,忘记标题了...) 的前面一页的例子和你情况差不多。
    分解这个 SQL,分批执行就可以了。
    xiaomu8
        20
    xiaomu8  
       2021-01-16 12:13:06 +08:00 via iPad
    又想要性能,又想要速度…
    这不就是又想马跑的快,又不给马吃草,哪有这么好的事。
    有个稍微麻烦点的思路加个缓存层,跑定是任务将缓存数据慢慢更新至 sql 就好了,类似队列削峰。读取状态时以缓存加 mysql 做判断。这样既保持了数据的实时性,有减缓了 cpu 。
    winglight2016
        21
    winglight2016  
       2021-01-16 13:58:27 +08:00
    一分钟更新 60w 条记录,这个速度很慢吗?总共有多少记录需要更新?更新频次是多少?希望达到什么样的更新速度?及时性有什么要求?

    lz 想做优化就先把约束条件都写下来,自然就有优化方向了,现在啥都没有,换高性能机器是最靠谱的办法。
    siyemiaokube
        22
    siyemiaokube  
       2021-01-17 11:11:40 +08:00 via iPhone
    一个可能的方法是把表拆分一下

    不过 sql 其实本身不太适合大批量更新,如果你们只是需要根据 order_id 修改确定的属性、而几乎不需要根据其他的属性进行修改操作,那么,可以考虑更换别的数据结构。

    不过在我的知识中,出现这种情况是因为设计的不好……
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2966 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 13:19 · PVG 21:19 · LAX 05:19 · JFK 08:19
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.