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
mawerss1
V2EX  ›  MySQL

请教一个 mysql 批量插入的问题

  •  
  •   mawerss1 · 2020-02-21 14:41:32 +08:00 · 4577 次点击
    这是一个创建于 1498 天前的主题,其中的信息可能已经有所发展或是发生改变。

    背景:

    数据库是 mysql 5.7,
    表结构:
    id
    data
    modified_time
    
    data 是一个很长的 json 串,需求是批量插入每次最多要几千条,插入前比较 modified_time 和插入的数据比较,时间戳比较新的插入,否则失败
    
    现在我的做法是用 insert into on ON DUPLICATE KEY UPDATE,但是如何比较这个时间就不会了,试了用触发器来写,但是批量插入如果有一条时间对不上的话,整个 sql 就不会再执行了,没办法跳过,请教大家有更好的做法吗
    
    23 条回复    2020-02-24 19:34:01 +08:00
    encro
        1
    encro  
       2020-02-21 14:58:24 +08:00
    一条一条插入,然后采用事务批量提交看看,性能也可以的。
    mawerss1
        2
    mawerss1  
    OP
       2020-02-21 15:36:30 +08:00
    @encro 试了一下,速度差距还是很大的
    hhyvs111
        3
    hhyvs111  
       2020-02-21 15:46:17 +08:00 via iPhone
    不要在数据库里弄触发器之类的,后期很难维护,建议在逻辑层去判断这个时间。
    encro
        4
    encro  
       2020-02-21 15:57:24 +08:00
    采用事务提交后,应该几秒钟可以插入几千条吧,你 modified_time 没有索引?

    其实这个方案不好,因为 modified_time 可能存在同一秒有多条数据吧。

    不知道你具体的需求场景是什么,脱离需求场景谈 SQL 优化很不靠谱。
    zjq123
        5
    zjq123  
       2020-02-21 16:04:29 +08:00 via iPad
    弄张临时内存表 insert into xx select from a join b ... where
    littlewing
        6
    littlewing  
       2020-02-21 16:10:25 +08:00
    multi query
    jswh
        7
    jswh  
       2020-02-21 16:12:51 +08:00
    一次几千应该也不多吧,先把 id 对应的 time 一次都拿出来,在代码里比较确定要不要插入就好了。
    akira
        8
    akira  
       2020-02-21 18:07:45 +08:00
    先插入到一个表,然后再把数据更新过来
    mawerss1
        9
    mawerss1  
    OP
       2020-02-21 19:02:14 +08:00
    @encro 你说的对,场景是同步订单表,商家业务
    mawerss1
        10
    mawerss1  
    OP
       2020-02-21 19:03:23 +08:00
    @encro 同步过来的数据查询也很频繁
    mawerss1
        11
    mawerss1  
    OP
       2020-02-21 19:06:19 +08:00
    @jswh 主要还考虑延迟问题,这个业务场景要求延迟越低越好
    encro
        12
    encro  
       2020-02-21 20:49:29 +08:00
    采用事务后,如果几千条数据,插入更新下应该几秒钟搞定了。前提是建立了合理的索引。当然如果你 data 非常大可能久一点。

    你这个需求还是不明确,INSERT ... ON DUPLICATE KEY UPDATE Statement 的意思如果唯一索引不存在就插入否则更新。并不能指定其他条件。这篇文章讲得很清楚了 https://blog.csdn.net/analogous_love/article/details/71085001。官方语法也没有提供扩展 https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html。

    所以除非你是根据 ID 来的,或者定了一个唯一索引。且实际不是你说的 “比较新的插入,否则失败”。

    我理解你的需求应该是“如果数据有更新,那么执行更新,否则忽略这个数据更新。”
    如果是我理解的,那么方案是:
    update order set data=new_data where id=xxx and modified_time < new_time; (只需要 id 主键即可,性能最好)
    比如你的目的表数据不全部来自来源表,那么可能需要加一个唯一索引字段 from_id
    update order set data=new_data where from_id=xxx and modified_time < new_time;(需要对 from_id 建立唯一索引)

    你首先要做的是,确定性能问题出在查找数据时的读取磁盘速度慢还是写入磁盘慢。
    MoYi123
        13
    MoYi123  
       2020-02-21 22:14:08 +08:00
    直接不检查直接插入,查找时候找时间戳最大的那条,定时删除过期数据,这样的话需要经常重建索引。如果量特别大可以考虑时序数据库。
    mawerss1
        14
    mawerss1  
    OP
       2020-02-21 23:04:30 +08:00 via iPhone
    @encro 还是我没有说清楚,场景是一个接受数据的推送库,这个库是没有任何操作权限,只能查询,要查询推送库里的数据到业务库,这两个库表结构是不同的要对数据做些处理,另外还有别的应用对业务库插入,主要还要新增插入,只靠 update 不行,所以用了 insert on dup key,看来还是要在程序里查询 modified 时间进行比较,然后决定是 insert 还是 update
    mawerss1
        15
    mawerss1  
    OP
       2020-02-21 23:11:30 +08:00 via iPhone
    @MoYi123 这样数据量太大了吧,现在的数据量单库预计一千万到两千万,每天还要新增
    ASpiral
        16
    ASpiral  
       2020-02-21 23:21:05 +08:00
    建个中间表,把数据都插进去;写个存储过程,把中间表的数据有选择性地插入目标表,再清空中间表;不知这样会不会比较快…
    wind3110991
        17
    wind3110991  
       2020-02-22 01:00:57 +08:00
    碰什么都不要碰触发器,坑死你,而且你的 mysql 版本也太低了,是大厂吗
    llussy
        18
    llussy  
       2020-02-22 07:49:49 +08:00 via iPhone
    @wind3110991 大厂都是 8.0 吗?我们还是 5.6 呢
    polymerdg
        19
    polymerdg  
       2020-02-22 08:35:14 +08:00
    1000 條不多 事務 查出來 在代碼里比较后插入即可
    encro
        20
    encro  
       2020-02-22 09:50:47 +08:00
    @mawerss1
    执行两次,
    第一次找出已有的 update,
    第二次不存在的 insert。
    记得采用事务。
    anjuyiyu
        21
    anjuyiyu  
       2020-02-22 12:42:38 +08:00
    id + 时间做一个临时表?
    另:这里是否可以考虑下时间分片做日结表?
    Aresxue
        22
    Aresxue  
       2020-02-24 16:54:01 +08:00
    1.别用触发器;
    2.考虑做分表;
    3.在代码里做好数据的清洗,最好是先删除数据库中较老时间的数据然后再插入,能不用 update 就尽量规避;
    4.注意调整 max_allowed_packet 的限制。
    mawerss1
        23
    mawerss1  
    OP
       2020-02-24 19:34:01 +08:00
    @Aresxue update 有什么问题吗
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2767 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 32ms · UTC 12:02 · PVG 20:02 · LAX 05:02 · JFK 08:02
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.