V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
Sponsored by
ShowMeBug
[福利] V2EXer 专属!在线代码笔面试 20 场
ShowMeBug,专业的在线代码面试平台,助力你快速识别神队友,高效面试不加班。

为了感谢 V2EX 小伙伴们的支持,特地大家提供了福利:ShowMeBug 在线笔面试场次 20 场,限时活动,快邀请你的小伙伴来薅羊毛吧!
Promoted by ShowMeBug
Dreamerwwr
V2EX  ›  程序员

mysql 导入超大 sql 文件有什么办法

  •  1
     
  •   Dreamerwwr · 81 天前 · 2749 次点击
    这是一个创建于 81 天前的主题,其中的信息可能已经有所发展或是发生改变。

    我这里的 sql 文件,大概 120GB,如何能导入到 mysql 呢?有什么优化呢? 我这里使用的是 source xxx.sql 进行导入,但是好像阻塞了,非常慢。例如这样:

    Query OK, 1727 rows affected (18.47 sec)
    Records: 1727  Duplicates: 0  Warnings: 0
    
    Query OK, 1703 rows affected (14.58 sec)
    Records: 1703  Duplicates: 0  Warnings: 0
    
    Query OK, 1717 rows affected (12.20 sec)
    Records: 1717  Duplicates: 0  Warnings: 0
    
    Query OK, 1699 rows affected (21.12 sec)
    Records: 1699  Duplicates: 0  Warnings: 0
    
    Query OK, 1706 rows affected (13.85 sec)
    Records: 1706  Duplicates: 0  Warnings: 0
    
    Query OK, 1704 rows affected (19.86 sec)
    Records: 1704  Duplicates: 0  Warnings: 0
    
    

    而且我查询数据时, 特别耗时:

    select count(*) from tabale;
    +-----------+
    | count(*)  |
    +-----------+
    | 180189257 |
    +-----------+
    1 row in set (19 min 51.76 sec)
    

    请教各位,有什么优化方案和优化吗?感谢不吝点拨:

    第 1 条附言  ·  80 天前
    此处的这个 sql 文件,里面就是 insert,一个文件大概 120GB:
    各位,这个 sql 文件拆分多个的话,怎么拆分?
    我程序导的话,我该怎么做,一个 sql 文件就 120GB,我程序去读吗?
    27 条回复    2021-02-02 17:14:06 +08:00
    codingadog
        1
    codingadog   81 天前 via Android
    单表一亿行,count 怎么都快不起来吧。
    另外如果 sql 文件是逐行 insert 的,瓶颈在硬盘上。
    如果 sql 文件是批量的,不确定 source 和直接 mysql<file.sql 效率会不会有区别,可以试试。
    eason1874
        2
    eason1874   81 天前
    120GB 是肯定慢的,就看慢到什么程度了,按套路云 140MB 的硬盘 I/O 速度,按顶格算光写入都得 15 分钟
    zhengxiaowai
        3
    zhengxiaowai   81 天前
    6 年前亲测,你把数据导出成 csv,文件太大可以分多个,速度至少快 100 被以上。
    wuwukai007
        4
    wuwukai007   81 天前 via Android
    直接把 data 文件拷贝过去
    kifile
        5
    kifile   81 天前
    我觉得可以从几个地方优化一下:
    1. 关闭 binlog,降低磁盘 io
    2. 因为数据量超大,数据表开启 partition, 将不同的数据写入到不同的 partition 中
    3. 上 ssd
    liprais
        6
    liprais   81 天前
    load infile 完事
    zzzmh
        7
    zzzmh   81 天前
    我用程序控制批量导入 例如每次 1W 条,然后在固态硬盘的机器上,能稍微快点。。。。。如果你是服务器就 24 小时一直导着呗,以前 dba 上班不是说一条命令跑一周么 doge
    msg7086
        8
    msg7086   81 天前
    SSD 。
    如果解决不了,买更快的 SSD 。
    talen666
        9
    talen666   81 天前
    拆成多个文件试试
    livepps
        10
    livepps   81 天前 via Android
    项目里面的数据,5g source 写入本地花了 10 分钟,固态硬盘
    340244120w
        11
    340244120w   81 天前 via iPhone
    还有就是把索引 外键 触发器啥的先去掉
    love
        12
    love   81 天前
    看进度也不能用 count,用了会更慢。select max(id)这种就瞬间出来。
    DarkCat123
        13
    DarkCat123   81 天前
    先 disable key,倒入完了再 打开 key 。
    varrily
        14
    varrily   81 天前
    mysqldump
    bthulu
        15
    bthulu   81 天前
    source 是一条一条执行的, 相当慢. 想办法搞成批处理, 一批导入 1000 条, 每 10000 条再提交一次. 或者在配置文件中改 innodb_flush_log_at_trx_commit=2, 再重启 mysql, 插入性能提高至少 20 倍以上.
    wowbaby
        16
    wowbaby   81 天前
    我当年 SB,在机房使用 source 方式导入,在机房导入一夜数据都导入不完,而且中途出错还得重导,后来用 navicat,就两个小时,如果有源数据库,使用数据传输估计会更快。
    gam2046
        17
    gam2046   81 天前
    如果是插入操作,可以先把索引、约束等等都删掉,全部导入以后,再重建索引,插入速度会快很多
    wapzjn
        18
    wapzjn   81 天前
    1 、文件分成几份试一下
    2 、去掉索引什么的,等导入完毕之后统一加

    另外,如果想要看倒入了多少条的话可以 explain select count(*) from xxx,这样不会阻塞,但是获得的是一个粗略值,用来看大概的条数是没问题的
    Lee2019
        19
    Lee2019   81 天前
    如果可以停库的话,把数据目录直接 copy 过去最快
    weizhen199
        20
    weizhen199   81 天前
    你这说的我以为是 120G 的 insert sql 。。
    jzmws
        21
    jzmws   81 天前
    直接导出文件 xtrabackup 用这个迁移
    zxbutton
        22
    zxbutton   81 天前
    切分文件,去掉索引啥的,用 load data infile
    cheng6563
        23
    cheng6563   81 天前
    @wowbaby 为啥 navicat 会比较快?是 navicat 会自动合并 Insert 吗?
    bthulu
        24
    bthulu   81 天前
    @cheng6563 navicat 是批处理
    cveoy
        25
    cveoy   81 天前
    这是裤子吗?
    janssenkm
        26
    janssenkm   80 天前
    @cveoy 我猜就是裤子。
    Dreamerwwr
        27
    Dreamerwwr   80 天前
    @weizhen199 是的
    关于   ·   帮助文档   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   1907 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 105ms · UTC 16:24 · PVG 00:24 · LAX 09:24 · JFK 12:24
    ♥ Do have faith in what you're doing.