在测试的过程中,无法避免的需要做一些性能压测,造数据的时长在此时就会备受关注。比如,造数据的时候用多线程还是多进程,用直接插入DB方式还是用先写文件后导入mysql的方式,写文件是写批量sql后面source导入,还是写文本格式后面load ito file导入,使用不同的方法耗时结果肯定是不一样的。除此之外,还有mysql的版本,DB的引擎,表的结构设计这些都会影响大量数据插入的时间。
这次导入数据做了一个小试验:导入2000w笔数据到DB内。使用多线程的线程池技术,首先写2000w笔数据分成M个文件,然后使用N个线程去并发处理写好M个文件,并把M个文件导入到MYSQL中。之前同事写的文件后面都是用load data from file命令导入的,官网也有说明这个命令字跟insert比,性能会高出20倍,于是把这个命令字跟我常用的source命令字做了下对比,结论如下:
一、Insert命令字详解
基于这些试验和现象,确定load ito file加载数据的性能确实要比source导入大批量数据的性能更稳定,更快。但这又是为什么呢?查阅了下MYSQL的官网,insert命令字的插入过程如下,括号内的数字几乎表示可能占用的耗时比:
A: 打开连接:(3)
B: 向服务器发送查询:(2)
C:解析查询:(2)
D:插入行:(1×行大小)
E:插入索引:(1×数索引)
F:关闭连接(1)
以上步骤还不包含连接时,打开表的开销。 当大量的insert批量文件被多线程执行插入时,每一个线程都需要经过6步才能完成数据的插入,表的索引结构,表当前数据的行数对insert的每次插入都会影响。如果想提升写入大数据的性能,可以尝试批量insert(即insert后的值有多个values),这在一般情况下会单个insert要快,但是要注意设置mysql的bulk_insert_buffer_size参数的大小,之前开发有一些经验值,一般情况下是设置300-500一批插入性能最佳。但是想要性能更快更稳定,可以使用“LOAD DATA INFILE”,这个命令比单insert要快近20倍。
二、提升导入数据性能----mysql服务器端
1、当将数据导入到INNODB中时,关闭自动提交模式&