长话短说,用自带的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可以让本地客户端程序读客户端的文件发到服务器。
为什么是最快的?
- 不需要解析SQL
- 服务器是按一个大块来读取文件的(Big block,我不太懂,应该是read buffer里的block,目前权当字面意义)
- 会自动禁用索引(UNIQUE除外)
- 引擎会先缓存行再写入一个大块(MyISAM,Aria支持)
- 对于空表,像Aria这样的事务引擎会停止log插入事务。毕竟回滚操作直接删表就行。
另外,对于MyISAM
它还支持并发插入。和INSERT
比,优势明显。
而且很节约内存。对于导入大的sql遇到内存不足很有用。我一个10+G级别的日志可以不需要拆分。服务器内存3.5G。
之前我用source
insert,客户端读一遍,socket过一遍,而后丢到数据库里缓存,缓存后还要解析,解析完了也先堆服务器里…别忘了SQL的括号逗号单引号也占用不少空间。
工具:mysqlimport
(Mysql和Mariadb都有自家的,但是貌似略有不同)
这个工具内部调用LOAD DATA INFILE
,很方便。另外,在网络带宽不足的时候可以压缩发送流量,提高传输效率。
回归老问题:插入速度优化原理
脱离工具我们应该做什么。除了工具内部优化的部分,还有什么别的我们可以做的?
背景
当我们插入数据的时候,什么消耗时间?如果按重要程度排序的话。
- 事务最后一步把数据同步到硬盘
- 键的更新,索引越大耗时越长
- 检查外键,如果有的话
- 在存储引擎里加新的行
- 把数据发给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%正确的,如果有问题,欢迎指出。
Comments
hello world:
Beautiful
Reply