#> RESTKHZ _

休止千鹤 | 我依旧是一名平凡的学生

MYSQL: 如何快速导入上亿规模大量数据

  休止千鹤  |    10/07/2022

长话短说,用自带的mysqlimport工具。基于LOAD DATA INFILE。少用INSERT。

起因

服务暴露在外网,我发现一些爬虫和扫描bot十分有趣,想要跟踪他们。我收集了很多站,几年加起来总共上亿条访问日志,要导入Mysql用于查询分析。毕竟我之前导入过最多也只是一些博客和小日志的SQL。直接在服务器一如既往用了source一堆insert导入一个InnoDB表,结果先是炸了内存OOM。于是,我自己用Python拆分SQL成200多个sql文件,写了一个sh自动导入结果导入速度依旧不怎么好看。导入进行了大约12个小时,一个列的索引的建立又进行了12个小时。

btw,我的Mysql在虚拟机上,性能有限。

用中文搜索了一下,感觉回答零碎且不可信:
有人各种玄学改参数,有人关log,有人source外面套事务。没人说为什么这么做,也没有整理。更有甚者自己数据库引擎到底是否支持事务都不知道。也真敢写,底下评论还真敢用?

我这里就不具体列举了。

用英文搜索了一遍。我找到了:

惊觉LOAD DATA INFILE这不是之前SQL注入后利用的东西吗?不得不说我的确是知其然不知其所以然了。

而后我使用了mysqlimport在一个MyISAM引擎表插入,2.5小时后,导入和两个索引全部完成,整个过程最大内存占用略微大于1G(我参数设置保守了)。对比之前source insert和一个的索引花费的24+小时,附加还炸一次,已经好太多了。

一开始不想写这篇,因为看文档就解决了。但是看到简中圈子里绝大多数是一些奇怪的玩意儿,还是一群程序员写的。我还是打算这里半翻译半自由发挥整理,写一下。

有能力的话,推荐你亲自去看看上面链接里的官方文档文章。

最快的导入方法:使用LOAD DATA INFILE

推荐使用LOAD DATA INFILE,而不是INSERT
(若一定要用INSERT的话,如何优化在后面。)

这条命令设计就是为了快速导入。文件格式类似CSV,可以手动指定分隔符等等。详细用法请参考文档。

LOAD DATA INFILE 'file_name' INTO TABLE table_name;

这条命令会让服务器自己去读取服务器里的文件。

LOAD DATA LOCAL INFILE 'file_name' INTO TABLE table_name;

加了个LOCAL可以让本地客户端程序读客户端的文件发到服务器。

为什么是最快的?

  1. 不需要解析SQL,省CPU省内存。
  2. 服务器是按一个大块来读取文件的(Big block,我不太懂,应该是read buffer里的block,目前权当字面意义。也可能是把I/O单位弄的很大节约I/O, 批量处理。)
  3. 会自动禁用索引(UNIQUE除外)
  4. 引擎会先缓存行再写入一个大块(MyISAM,Aria支持)
  5. 对于空表,像Aria这样的事务引擎会停止log插入事务。毕竟回滚操作直接删表就行。

另外,对于MyISAM它还支持并发插入。和INSERT比,优势明显。

而且很节约内存。对于导入大的sql遇到内存不足很有用。我一个10+G级别的日志可以不需要拆分。服务器内存3.5G。
之前我用source insert,客户端读一遍,socket过一遍,而后丢到数据库里缓存,缓存后还要解析,解析完了也先堆服务器里…别忘了SQL的括号逗号单引号也占用不少空间。

工具:mysqlimport

(Mysql和Mariadb都有自家的,但是貌似略有不同)
这个工具内部调用LOAD DATA INFILE,很方便。另外,在网络带宽不足的时候可以压缩发送流量,提高传输效率。

回归老问题:插入速度优化原理

脱离工具我们应该做什么。除了工具内部优化的部分,还有什么别的我们可以做的?

背景

当我们插入数据的时候,什么消耗时间?如果按重要程度排序的话。

  1. 事务最后一步把数据同步到硬盘,众所周知硬盘读写和内存比特别慢。引擎没有事务,一条一条写硬盘很慢。而事务又要一条条记录,一次又一次的锁,解锁,又记录日志,又写硬盘……我们可不可以批量一次多写一点呢?
  2. 键的更新,索引越大耗时越长。可以想象你手里抓着3张牌往里面插入牌和抓着30张牌插入的速度肯定不同。而且一条一条INSERT就是一张一张插入,排序。可能还涉及写硬盘…这个我们可以到插入完数据以后再做。
  3. 检查外键,如果有的话
  4. 在存储引擎里加新的行
  5. 把数据发给server

同步到硬盘和发数据这里我就不多说了,我没有RAID也没有千兆网。这方面我不懂。

禁用索引

由上面第二点,我们知道每次都更新索引很麻烦,那我们可以到最后再做。

当原本表中数据本来就不多的时候,我们可以临时禁用索引。LOAD DATA INFILE会自动暂时禁用索引。INSERT导入你需要自己禁用。

注意,空表插入MariaDB会自动DISABLE KEYS,并且在结束后调用ENABLE KEYS。无论是INSERT还是LOAD DATA

ALTER TABLE table_name DISABLE KEYS;
BEGIN;
... 用INSERT或者LOAD DATA插入数据 ....
COMMIT;
ALTER TABLE table_name ENABLE KEYS;

在很多存储引擎中,至少对于MyISAM和Aria是这样:ENABLE KEYS是直接扫描所有行,然后排序键。

这样创建索引块比一行一行处理会快很多很多,并且使用更少的键值缓冲,节约内存。(如果你记得有个配置叫key_buffer_size)

禁用约束

完整性检查在大量数据插入时也会很耗时。如果可以的话,可以禁用唯一约束,外键约束和唯一性索引。

SET @@session.unique_checks = 0;
SET @@session.foreign_key_checks = 0;

对于InnoDB我们可以设置自增锁模式global.innodb_autoinc_lock_mode = 2;,改成交错锁。不锁表。对于并发有帮助。

触发器什么的,如果可以,也删了。

还有什么相关参数可以优化?

  • innodb_buffer_pool_size:老生常谈。如果使用InnoDB并且有很多索引,可以调大点。
  • key_buffer_size: 老生常谈。如果使用MyISAM并且有很多索引,可以调大点。
  • max_allowed_packet: 当你还在用INSERT,调大。至少得装下你source的文件吧?
  • read_buffer_size: 这里缓存了你LOAD DATA 读入的文件。

至于到底调多少,视具体情况。前提是弄清楚它到底是做什么的。如果不清楚,继续找官方文档。

还是用INSERT导入,如何优化?

1. 根据原则

首先还是根据之前列出的,首先临时禁用索引和外键。这两个开销很大。

2.扎堆放进一个事务

很多插入连续在一起,可以放进一个大的事务。可以避免频繁同步到硬盘,硬盘读写很耗时。比如1000次写入放进一个事务,可以提高性能1000倍左右。(文档如此)

BEGIN;
INSERT x1000
END;
BEGIN;
INSERT x1000
END;
...

某些地方你会看到有人把所有INSERT全都放进一个事务里,那样会产生一个庞大的日志记录操作。一个事务跑一半炸了是要重做,有的还要回滚的。那个日志就是为了以防万一。但是你绝对不希望它变得太大。于是有人干脆就禁用了日志(CSDN上比比皆是这种做法)我相信对于空表插入这问题不大。但是如果它原来有数据的话可能出现意外数据库挂了可能就麻烦了。你禁用了日志,那么表在你插入新数据之前是什么样子呢?

3.一次INSERT插入多个值

INSERT INTO table_name values(1,"row 1"),(2, "row 2"),...;

一堆冗余的INSERT字符也很浪费空间影响效率。要对得起max_allowed_packet的空间。

结尾

总结:RTFM pls

如果你看到了这里,感谢你的阅读。

不要盲信CSDN,某乎上的做法。希望这篇文章能帮助到你,更希望你学会看文档,理解原理,而不是抄答案。同样我也不保证我这里的理解就是100%正确的,如果有问题,欢迎指出。


Views:

 Comments


hello world:

Beautiful

 Reply


william:

总结: 1、Load local file infile 肯定比 批量插入要快 因为插入经历了 客户端、网络、解析sql、bufferpoll 、 磁盘 等问题 并且会自动拉满性能,一次读取最大量禁用索引 2、非要insert 也肯定是批量插入(无论自己组装还是 jdbc executeBatch,不可能用单条插入 辅助: 禁用不必要日志(binlog\querylog\slowquerylog 等) 禁用索引、 禁用外键、触发器之类的 禁用其他查询、插入干扰造成锁问题 事务无法关闭,则用最低限制事务如(read uncommitted) 当然如果单线程也不影响 增大缓存 比如每次事务就插入redulog 落盘,改为长周期落盘 总结,就是 增大一切可用资源,减少一切安全选项,就可以增大速度

 Reply