V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
lry
V2EX  ›  数据库

mysql 插入 1000 万条数据需要多久

  •  
  •   lry · 2018-05-21 22:42:21 +08:00 · 5700 次点击
    这是一个创建于 2138 天前的主题,其中的信息可能已经有所发展或是发生改变。

    数据分了两张表,一张表 500 万,表包含两个 varchar 类型字段,除主键外还建了一个 unique 索引

    使用 mybatis,开了 8 个线程,每张表分配 4 个线程,数据库连接池设置 maximum-pool-size = 32

    花了 53 分钟...

    想问下插入大量数据应该怎么优化

    22 条回复    2018-05-23 09:06:08 +08:00
    SimbaPeng
        1
    SimbaPeng  
       2018-05-21 22:44:09 +08:00
    load data 了解一下?
    lry
        2
    lry  
    OP
       2018-05-21 22:45:24 +08:00
    @SimbaPeng 插入的数据要先计算出来。。这个也能 load data ?
    SimbaPeng
        3
    SimbaPeng  
       2018-05-21 22:46:09 +08:00
    @lry 先计算出来写入一个文件,然后 load data 也比你这个快的多
    SimbaPeng
        4
    SimbaPeng  
       2018-05-21 22:46:59 +08:00
    @lry 一条 sql 写一条记录肯定慢阿,要么一条 sql 写 1000 条数据还差不多
    lry
        5
    lry  
    OP
       2018-05-21 22:47:17 +08:00
    @SimbaPeng 这个没玩过哈 感谢回复
    codespots
        6
    codespots  
       2018-05-21 22:52:15 +08:00
    insert 可以插入多条数据,了解一下,预计你这个量级也只是在几分钟内就可以跑完
    gamexg
        7
    gamexg  
       2018-05-21 22:54:58 +08:00
    加事务了吗?
    把操作包到事务里面。
    niubee1
        8
    niubee1  
       2018-05-21 22:56:31 +08:00
    batch insert
    了解一下
    dobelee
        9
    dobelee  
       2018-05-21 23:04:13 +08:00 via Android
    看你數據大小吧,單條不大的話,multi-insert 幾分鐘搞定。我經常生產數據做測試,都是 1000w 左右。
    lry
        10
    lry  
    OP
       2018-05-21 23:29:00 +08:00
    @gamexg 没有事务...
    lry
        11
    lry  
    OP
       2018-05-21 23:29:24 +08:00
    @dobelee 想问下批量插入的话 一次插多少条数据比较合适
    pathbox
        12
    pathbox  
       2018-05-21 23:38:10 +08:00 via iPhone
    2000 条一个批量,批量插入速度会提升很多的。我之前做过 1000-2000 条这样的批量插入 没什么问题
    gamexg
        13
    gamexg  
       2018-05-21 23:55:39 +08:00
    @lry #10 我的意思是把插入操作包到一个事务里面可以提高性能。

    我这里没有 mysql 的数据,前段时间刚测试过 mssql 批量插入,记得性能是这样:
    bulkcopy > 单条语句插入多行 = 单事务单语句插入单行 > 无事务单语句插入单行
    印象最好的结果是 bulkcopy xW 行 /秒,可惜这个是 ms 专有的...
    lry
        14
    lry  
    OP
       2018-05-22 00:04:17 +08:00
    @gamexg 没用过事务插入 不懂。。。
    刚刚试了一下一次批量插入 2000 条,总共耗时 16 分钟
    woodface2233
        15
    woodface2233  
       2018-05-22 08:30:33 +08:00 via iPhone
    很快并发写入
    abccccabc
        16
    abccccabc  
       2018-05-22 09:43:01 +08:00
    楼主,你在另外一张新表插入数据前,先不要建立索引,然后启用事务,插入数据。数据插完后,再添加索引。试下
    qnwx1
        17
    qnwx1  
       2018-05-22 10:19:35 +08:00
    @lry 老大是你吗?
    opengps
        18
    opengps  
       2018-05-22 10:26:09 +08:00
    为什么没人说说硬盘环境是啥?
    为啥不说明下单行数据多大?
    以我测试过的 gps 轨迹为例,普通 5400 转机械盘,单行 1k 大小,顺序写入,iops 可达 3000,也就是说一秒添加 3000 行
    另外,你的索引需要每次重新编排,或导致你随着数据变多,插入速度越来越慢,这时候,就别只看平均值了
    opengps
        19
    opengps  
       2018-05-22 10:31:04 +08:00
    硬盘速度接近极限之后,自然就没法进一步提速了
    楼主可能就是机械硬盘,平均算了下 3144iops,跟我的测试结果接近,可能已经达到了 5400 转机械盘的写入速度和 iops 值。
    这种情况,还可以稍微改进下,就是大块写入,避免单行写入。
    有意义的其他改进,就是 :多块硬盘,换 ssd,上多硬盘磁盘阵列,做多个数据库负载 等几个方向
    woscaizi
        20
    woscaizi  
       2018-05-22 10:39:50 +08:00 via iPhone
    @abccccabc unique 索引如何处理?
    hooopo
        21
    hooopo  
       2018-05-22 16:04:55 +08:00
    abccccabc
        22
    abccccabc  
       2018-05-23 09:06:08 +08:00
    @woscaizi 插入数据之后,建普通索引,将重复的数据删掉,再将普通索引改为唯一索引。要么就导出 csv 格式,再用命令导入到 mysql。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   1112 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 22:50 · PVG 06:50 · LAX 15:50 · JFK 18:50
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.