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

mysql 批量插入数据越来越慢 如何解决?

  •  
  •   madaima · 2018-02-26 11:50:53 +08:00 · 6842 次点击
    这是一个创建于 2222 天前的主题,其中的信息可能已经有所发展或是发生改变。

    表中有两个唯一索引;

    使用 php 对一个数据表进行批量插入时,开始插入 1w 条数据只要 2s,插入 50w 条数据过后,再次插入 1w 条数据需要 18s ;

    sql 使用的是拼接方式,3000 条数据拼接成一条 sql 执行;

    这种问题如何解决?

    18 条回复    2018-02-26 23:47:55 +08:00
    yidinghe
        1
    yidinghe  
       2018-02-26 11:54:08 +08:00
    首先拼接 SQL 执行并不是真正的批量插入,最好使用标准的批量插入方式;其次,表的索引要尽可能的少,索引越多插入效率越低。
    realpg
        2
    realpg  
       2018-02-26 12:37:53 +08:00
    igonre delayed 循环插吧 不用拼接成一个
    msg7086
        3
    msg7086  
       2018-02-26 12:55:13 +08:00
    禁用索引,换 SSD。
    alvinbone88
        4
    alvinbone88  
       2018-02-26 12:58:13 +08:00   ❤️ 1
    akira
        5
    akira  
       2018-02-26 13:02:08 +08:00
    把索引删了,数据导完以后再加回来
    male110
        6
    male110  
       2018-02-26 13:29:47 +08:00
    把索引删了,数据导完以后再加回来
    lastpass
        7
    lastpass  
       2018-02-26 13:56:52 +08:00
    可以试着先插入到一个空的中间表,再复制到的需要插入的表里。我原来在 oracle 上实验过这种方式最快。MySQL 应该也是同理。
    lianxiaoyi
        8
    lianxiaoyi  
       2018-02-26 14:11:00 +08:00
    最简单的你可以试试 1 万个一条 SQL。降低内部刷 IO
    est
        9
    est  
       2018-02-26 14:15:30 +08:00
    把 @alvinbone88 那篇文章一定挨个看完。绝对不会慢。
    fakepoet
        10
    fakepoet  
       2018-02-26 14:15:57 +08:00
    mysql 的单表 IO 放大问题,如果数据量百万级接近千万,建议分表。
    madaima
        11
    madaima  
    OP
       2018-02-26 14:38:12 +08:00
    @msg7086 不用索引怎么确定数据唯一呢
    madaima
        12
    madaima  
    OP
       2018-02-26 14:54:44 +08:00
    @alvinbone88 谢谢,我测试了一下 1000 条数据批量插入时,临时关索引 所消耗的时间基本是 不关索引插入 的一半,但是批量插入 1 万条时 两个方法都超过 30s 了。我在仔细看看文档。
    madaima
        13
    madaima  
    OP
       2018-02-26 14:55:31 +08:00
    谢谢 各位朋友的指导 我就不一个个 @了
    madaima
        14
    madaima  
    OP
       2018-02-26 14:56:09 +08:00
    @fakepoet 后期打算分表了。
    madaima
        15
    madaima  
    OP
       2018-02-26 15:21:24 +08:00
    @lianxiaoyi 1 万条数据拼接成的 sql 字符串长度会超过 mysql 限制的 65535 个字符长度,虽然能修改配置,但是不方便。
    beginor
        16
    beginor  
       2018-02-26 18:42:41 +08:00 via Android
    分表会增加不少逻辑,如果能做表分区的话最好了
    runntuu
        17
    runntuu  
       2018-02-26 19:08:56 +08:00 via iPhone   ❤️ 1
    看一下执行时间都花在哪了,
    https://www.cnblogs.com/happySmily/p/5943311.html
    opengps
        18
    opengps  
       2018-02-26 23:47:55 +08:00 via Android
    看下你的聚集索引是什么字段,尽量采用时间等字段当聚集索引,这样追加数据物理上是尾部追加,而不是中间填充。数据量大了相差很明显,我曾做过实验,10 亿都没问题
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   3198 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 00:38 · PVG 08:38 · LAX 17:38 · JFK 20:38
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.