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

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

起因

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

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

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

我这里就不具体列举了。

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

• How to Quickly Insert Data Into MariaDB
• LOAD DATA INFILE
• mysqlimport

惊觉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%正确的，如果有问题，欢迎指出。