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

MySQL 导入大批量数据 这些优化不生效吗?

  •  
  •   815979670 · 2022-02-14 09:16:04 +08:00 · 2997 次点击
    这是一个创建于 998 天前的主题,其中的信息可能已经有所发展或是发生改变。

    最近在看《 MySQL 深入浅出》这本书,里面提到了几个 InnoDB 表批量导入数据时的一些优化方案,按照书上的演示来看确实有效,但我实际测试发现,几乎没有效果,不知道是哪里出了问题。

    测试数据是导入 102w 条数据,表结构、存储引擎与例子一致,MySQL 版本是 5.7.30

    方法 1:导入数据前执行 SET UNIQUE_CHECKS = 0 ,关闭唯一性校验;在导入数据后执行 SET UNIQUE_CHECKS = 1 ,恢复唯一性校验,可以提高导入的效率。

    书中效果:关闭前耗时 22.92s 关闭后降低为 19.92s
    测试效果:关闭前耗时 26.07s 关闭后耗时:25.70s

    多次测试,耗时的差异几乎都差不多,并没有书中 3 秒这么大的差异

    方法 2:在导入前执行 SET AUTOCOMMIT = 0,来关闭自动提交,导入结束后再打开自动提交,也可以提高导入效率

    书中效果:关闭前耗时 22.92s 关闭后降低为 20.87s
    测试效果:关闭前耗时 25.18s 关闭后耗时:25.04s

    也是经过多次测试的效果,书中的例子也是 5.7 的版本。 不太清楚导致这个结果的原因是 MySQL 版本差异还是其他什么原因?

    17 条回复    2022-02-15 11:21:13 +08:00
    GM
        1
    GM  
       2022-02-14 09:26:55 +08:00   ❤️ 2
    有没有可能作者测试的时候是 HDD ,你是 SSD ?
    markgor
        2
    markgor  
       2022-02-14 10:02:13 +08:00   ❤️ 4
    这个是相对的,而不是绝对的。
    比如
    SET UNIQUE_CHECKS = 0 ,关闭唯一性校验;
    如果作者测试的时候瓶颈大部分是 CPU 进行检验,而你测试的时候 CPU 并非瓶颈,那就会出现 你测试的效率和作者测试的效率不一样。

    这就是调优存在的意义,并不是把配置复制黏贴就完事,而是根据实际情况找到瓶颈所在,再进行优化。
    maocat
        3
    maocat  
       2022-02-14 10:27:38 +08:00 via iPhone
    数据量再大一点,再试试
    eW91IHNlZSBtZQ
        4
    eW91IHNlZSBtZQ  
       2022-02-14 10:33:23 +08:00
    @GM 从他的测试结果看,不太可能
    815979670
        5
    815979670  
    OP
       2022-02-14 11:00:28 +08:00
    @GM 我确实是 SSD 不过我认为这个差距是一个相对的差距,我的两次测试都是 SSD 作者两次测试都是 HDD ,变量一致的情况下 感觉不应该是这个问题。
    @maocat 作者演示的时候数据是 52w ,而我测试的时候 数据翻了一倍 102w ,感觉已经到了可以测出差距的地步了。
    @markgor 目前看来 这个原因的可能性很大,你的总结对我也有一些启发,调优更重要的是综合判断给出调优方案,而不是一股脑地照前人总结的方案去做。
    DinnyXu
        6
    DinnyXu  
       2022-02-14 11:02:01 +08:00
    要考虑一个问题,你的 MySQL 部署在哪?比如云服务器 1 核 2G 的 MySQL 性能如何? 你电脑 4 核 8G MySQL 性能又如何?
    815979670
        7
    815979670  
    OP
       2022-02-14 11:06:15 +08:00
    @DinnyXu 没有在云服务器上测试过,只是在本地测试的,我认为在 同一台机器上 如果配置项是唯一的变量,其他不变的情况下 前后还是有对比性的
    yulgang
        8
    yulgang  
       2022-02-14 13:10:43 +08:00
    感觉你也是有效果的,书中的也不那么明显。
    DinnyXu
        9
    DinnyXu  
       2022-02-14 16:30:51 +08:00
    @815979670 这个也不一定,因为看你测试的数据和书上的数据秒数相差也就是 2-3 秒内。2-3 秒的波动哪怕是你自己的电脑,同样的数据操作 2 次波动也会不一样,就好比一段 select 语句每次查询返回的时间都不一样,要考虑时间和空间的复杂度
    Chinsung
        10
    Chinsung  
       2022-02-14 17:29:19 +08:00
    个人感觉 begin;
    insert……
    Chinsung
        11
    Chinsung  
       2022-02-14 17:29:41 +08:00
    @Chinsung #10 commit;
    这种最明显,别的都有点玄学
    keke88168
        12
    keke88168  
       2022-02-14 17:32:04 +08:00
    非要方案,那:
    sync_binlog
    innodb_flush_log_at_trx_commit
    调成双 0 。
    opengps
        13
    opengps  
       2022-02-14 20:34:10 +08:00
    有些环境因素会导致差异比如磁盘分区时候的块大小
    littlewing
        14
    littlewing  
       2022-02-14 21:11:00 +08:00
    表结构、存储引擎与例子一致,MySQL 版本是 5.7.30
    MySQL 版本一致吗?
    配置一致吗?
    815979670
        15
    815979670  
    OP
       2022-02-15 10:18:25 +08:00
    @littlewing 表结构 、存储引擎 MySQL 版本(都是 5.7 ,第三位不一致),电脑配置与书中的例子不一致
    littlewing
        16
    littlewing  
       2022-02-15 10:58:09 +08:00
    @815979670 不是电脑配置,是 MySQL 配置
    815979670
        17
    815979670  
    OP
       2022-02-15 11:21:13 +08:00
    @littlewing 其他更深入的配置都是保持默认,在测试的这几个配置项上,与例子保持一致
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   3715 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 23ms · UTC 10:20 · PVG 18:20 · LAX 02:20 · JFK 05:20
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.