V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
The Go Programming Language
http://golang.org/
Go Playground
Go Projects
Revel Web Framework
billion
V2EX  ›  Go 编程语言

Go 语言 MySQL Update 200 万条数据的正确姿势是什么?

  •  
  •   billion ·
    kingname · 2017-07-20 16:53:38 +08:00 · 5945 次点击
    这是一个创建于 2444 天前的主题,其中的信息可能已经有所发展或是发生改变。

    根据主键来更新每一条数据。

    stmt, err := db.Prepare("update xxx set age=? where primaryid = ?")
    panic(err)
    _, err2 := stmt.Exec(age, id)
    

    由于主键是唯一的,所以需要一条一条的更新。这种情况下,开了 2000 个 goroute 速度还是非常慢。请问有什么好办法吗?

    46 条回复    2017-07-21 11:06:56 +08:00
    sunchen
        1
    sunchen  
       2017-07-20 17:01:16 +08:00
    大表更新一般选择建新表然后插入合并后的新数据,然后替换老表的方式比较快
    caotian
        2
    caotian  
       2017-07-20 17:03:18 +08:00
    一次拼 N 个 update 语句,一起发给 mysql 试试.
    jarlyyn
        3
    jarlyyn  
       2017-07-20 17:04:32 +08:00
    说实话,这个取决与 mysql 的效率。是否用 go 对效率提升作用不大。
    cxbig
        4
    cxbig  
       2017-07-20 17:07:24 +08:00
    可以批量处理的,比方说一次 1 千条数据。
    另一个性能上的共识是 INSERT ... ON DUPLICATE KEY UPDATE 速度快过 UPDATE,可以试试
    monsterxx03
        5
    monsterxx03  
       2017-07-20 17:08:30 +08:00
    这个用 goroutine 只会更慢啊,更新主键是有锁的 (gap lock), 你应该把 autocommit 关掉,每个 transaction 更新 1000 条,再 commit, 才 200w, 很快的
    mringg
        6
    mringg  
       2017-07-20 17:09:48 +08:00 via iPhone
    如果 age 上没索引,一会就跑完了
    cxbig
        7
    cxbig  
       2017-07-20 17:10:21 +08:00
    另一个点,如果你的表是 InnoDB,那么更新会锁表,Go 这边的并行处理不会直接提升速度
    GTim
        8
    GTim  
       2017-07-20 17:10:51 +08:00
    @monsterxx03 恩,这个可以试一试
    gouchaoer
        9
    gouchaoer  
       2017-07-20 17:12:25 +08:00
    我只想说这种简单的 update 语句 mysql 在 32 核上的性能都是上万 qps 了,区区 200w 数据一个小时内搞定吧
    liprais
        10
    liprais  
       2017-07-20 17:12:47 +08:00 via iPhone
    开一个事务做
    gouchaoer
        11
    gouchaoer  
       2017-07-20 17:13:28 +08:00
    Zzzzzzzzz
        12
    Zzzzzzzzz  
       2017-07-20 17:16:12 +08:00
    这种操作协程 /线程开的越多越慢
    billion
        13
    billion  
    OP
       2017-07-20 17:23:13 +08:00
    @caotian Go 语言拼接 Update 的语句有点麻烦。
    billion
        14
    billion  
    OP
       2017-07-20 17:26:52 +08:00
    @cxbig Go 语言拼接 Update 有点麻烦
    billion
        15
    billion  
    OP
       2017-07-20 17:27:52 +08:00
    @monsterxx03 用 Python 确实可以。但是 GO 语言没有 commit。。。。。
    mooncakejs
        16
    mooncakejs  
       2017-07-20 17:28:37 +08:00
    上事务会快一点, 如果是一次性操作
    导出 csv,用 sed 或者程序改,再导入,这样时间可控
    fitmewell
        17
    fitmewell  
       2017-07-20 17:28:56 +08:00
    @billion 有 commit 仔细看文档
    rrfeng
        18
    rrfeng  
       2017-07-20 17:29:21 +08:00
    @billion commit 是 MySQL 的概念,跟 go 有什么关系……
    utopia5719
        19
    utopia5719  
       2017-07-20 17:35:45 +08:00
    @cxbig 你的理解并不对,Innodb 主键更新并不会锁表,innodb 本身就是行锁; insert ... on duplicate 比 update 快的依据是什么?怎么看也不会比 update 快啊,而且 insert on duplicate 这种语句如果真实执行是 update 的话在 binlog 中记录的也是 update 语句,实际执行的也是 update。
    honeycomb
        20
    honeycomb  
       2017-07-20 17:38:30 +08:00   ❤️ 1
    @rrfeng
    commit 肯定在 go 有对应的封装

    随手搜索了一下(关键词: go commit mysql ),看上去里面有你要的
    http://studygolang.com/articles/3022
    billion
        21
    billion  
    OP
       2017-07-20 17:43:44 +08:00
    @honeycomb 这正是我想要的东西,非常感谢你的回答。
    realfreesky
        22
    realfreesky  
       2017-07-20 17:44:28 +08:00
    @sunchen 有更具体的操作方法不
    DCjanus
        23
    DCjanus  
       2017-07-20 18:42:40 +08:00
    生成 csv 然后用 MySQL 的 load data infile 功能
    使用这个是单个事务且所有数据导入后才重建索引,比其他方法快很多。
    注意处理唯一键冲突,选择 ignore 或者 replace
    0x8C
        24
    0x8C  
       2017-07-20 18:46:16 +08:00
    tide
    jarlyyn
        25
    jarlyyn  
       2017-07-20 18:50:22 +08:00
    @billion

    觉得 go 拼接 sql 语句麻烦是因为你不知道 sqlx 吧?
    fuxkcsdn
        26
    fuxkcsdn  
       2017-07-20 19:04:14 +08:00 via iPhone
    用 set case when 进行批量更新
    akrf
        27
    akrf  
       2017-07-20 19:28:35 +08:00 via Android
    上面那个关于 innoDB 的言论说反了
    orvice
        28
    orvice  
       2017-07-20 19:53:55 +08:00
    拼接语句,一次生成 1000 条语句在执行
    janxin
        29
    janxin  
       2017-07-20 20:35:44 +08:00
    用个 Golang ORM,告别手拼比如 jinzhu 的 gorm
    mchl
        30
    mchl  
       2017-07-20 21:04:35 +08:00 via Android
    transaction
    cxbig
        31
    cxbig  
       2017-07-20 21:04:47 +08:00
    @utopia5719 我说的锁表概念是错的
    关于 insert ... key update 快是看情况的,处理单条数据 update 当然快,但是 insert ... key update 可以放多条记录在一个 SQL 里的。根据 LZ 的情况,一次处理 1 千条数据,应该是比处理一千条 update 快。除非 age 是同一个值。
    sagaxu
        32
    sagaxu  
       2017-07-20 21:13:43 +08:00 via Android
    @cxbig UPDATE a
    SET fruit = (CASE id WHEN 1 THEN 'apple'
    WHEN 2 THEN 'orange'
    WHEN 3 THEN 'peach'
    END)
    WHERE id IN(1,2 ,3);

    很多 orm 支持这种批量 update,不需要用 insert
    cxbig
        33
    cxbig  
       2017-07-20 21:21:04 +08:00
    @sagaxu 这种我没试过,回头验证一下。
    cxh116
        34
    cxh116  
       2017-07-20 21:29:00 +08:00 via Android
    @utopia5719 因为一行 insert on duplicate 可以一次更新一千条记录,每条记录更新为不同值。

    而用 update set 要更新不同值,只能写 1000 条语句。
    mengskysama
        35
    mengskysama  
       2017-07-20 21:39:05 +08:00 via iPhone
    关了 auto commit 一个 query 还是一个 round trip,还是 batch 快,比如 32l 的
    mingyun
        36
    mingyun  
       2017-07-20 23:53:25 +08:00
    @sagaxu 上百个 id 这个 sql 得有多长
    msg7086
        37
    msg7086  
       2017-07-21 04:38:23 +08:00
    @mingyun 越长越快啊,没毛病。大量短语句才会变慢。

    这里 INSERT ON DUPLICATE 其实比较好,因为只更新一两个值,SQL 语句并不会很臃肿。
    jiazhoulvke
        38
    jiazhoulvke  
       2017-07-21 06:52:35 +08:00
    这和用什么语言没太大关系,主要是这种提交方式的问题,用事务会快很多。
    fuxkcsdn
        39
    fuxkcsdn  
       2017-07-21 08:27:57 +08:00 via iPhone   ❤️ 1
    @cxbig 我 26 楼就说了,这方法批量更新很快

    我昨天特地在我 16 年的 MacBook Air 128G SSD 上测试,200 万条简单数据的情况下(单条 SQL 更新 2000 条数据),60 秒左右更新完(用 PHP 7.0 PDO mysqli 驱动)
    预期测试
    age 有(无)索引,有(无)事务
    单条更新(不打算测试,肯定慢得一逼)
    set case when 批量更新(已测无索引,无事务)
    insert on dup
    fuxkcsdn
        40
    fuxkcsdn  
       2017-07-21 08:36:58 +08:00 via iPhone
    对了,按理说,楼主举的这例子,先根据 new_age (在程序或 sql 中)进行 group,然后用
    update set age=new_age where id in (1, 2...)
    更新应该是最快的
    nullen
        41
    nullen  
       2017-07-21 09:11:54 +08:00
    跟语言没多大关系,2 个方法:
    1、拼 SQL 批量执行;
    2、批量事务:
    START
    UPDATE xxx SET age=? WHERE primaryid = ?
    UPDATE xxx SET age=? WHERE primaryid = ?
    UPDATE xxx SET age=? WHERE primaryid = ?
    ....
    COMMIT
    Clarencep
        42
    Clarencep  
       2017-07-21 09:39:06 +08:00
    INSERT ON DUPLICATE 👍+1
    CryMeatel
        43
    CryMeatel  
       2017-07-21 09:46:23 +08:00
    @nullen 👍+1
    kofj
        44
    kofj  
       2017-07-21 09:54:03 +08:00
    这么多回答,也就俩人说到了事务
    billion
        45
    billion  
    OP
       2017-07-21 11:02:40 +08:00
    我发现使用了事务以后,速度反而慢下来了。
    我用 100 个 goroute 一条一条更新,基本上可以实现 1 秒 1000 行。

    使用了事务以后,还是 100 个 goroute,每个事物 1000 条,一个事务执行完成需要 1 分钟左右。
    @nullen @honeycomb
    nullen
        46
    nullen  
       2017-07-21 11:06:56 +08:00   ❤️ 3
    说到这里推荐大家一个帖子,几年前看到的,当时受益匪浅:
    https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   4546 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 32ms · UTC 09:57 · PVG 17:57 · LAX 02:57 · JFK 05:57
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.