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

2021-02-01 10:35:20 +08:00
 Dreamerwwr

我这里的 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)

请教各位，有什么优化方案和优化吗？感谢不吝点拨：

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

另外，如果想要看倒入了多少条的话可以 explain select count(*) from xxx，这样不会阻塞，但是获得的是一个粗略值，用来看大概的条数是没问题的
Lee2019
2021-02-01 14:13:35 +08:00
如果可以停库的话，把数据目录直接 copy 过去最快
weizhen199
2021-02-01 15:24:22 +08:00
你这说的我以为是 120G 的 insert sql 。。

这是一个专为移动设备优化的页面（即为了让你能够在 Google 搜索结果里秒开这个页面），如果你希望参与 V2EX 社区的讨论，你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/750169

V2EX 是创意工作者们的社区，是一个分享自己正在做的有趣事物、交流想法，可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX