10亿数据如何最快速批量插入Mysql？这篇让你拍案叫绝

最快的速度把10亿条数据导入到数据库，首先需要和面试官明确一下，10亿条数据什么形式存在哪里，每条数据多大，是否有序导入，是否不能重复，数据库是否是MySQL？

假设和面试官明确后，有如下约束

1. 10亿条数据，每条数据 1 Kb
2. 数据内容是非结构化的用户访问日志，需要解析后写入到数据库
3. 数据存放在Hdfs 或 S3 分布式文件存储里
4. 10亿条数据并不是1个大文件，而是被近似切分为100个文件，后缀标记顺序
5. 要求有序导入，尽量不重复
6. 数据库是 MySQL

首先考虑10亿数据写到MySQL单表可行吗？

数据库单表能支持10亿吗？

答案是不能，单表推荐的值是2000W以下。这个值怎么计算出来的呢？

MySQL索引数据结构是B+树，全量数据存储在主键索引，也就是聚簇索引的叶子结点上。B+树插入和查询的性能和B+树层数直接相关，2000W以下是3层索引，而2000w以上则可能为四层索引。

Mysql b+索引的叶子节点每页大小16K。当前每条数据正好1K，所以简单理解为每个叶子节点存储16条数据。b+索引每个非叶子节点大小也是16K，但是其只需要存储主键和指向叶子节点的指针，我们假设主键的类型是 BigInt，长度为 8 字节，而指针大小在 InnoDB 中设置为 6 字节，这样一共 14 字节，这样一个非叶子节点可以存储 16 * 1024/14=1170。

也就是每个非叶子节点可关联1170个叶子节点，每个叶子节点存储16条数据。由此可得到B+树索引层数和存储数量的表格。2KW 以上 索引层数为 4 层，性能更差。

为了便于计算，我们可以设计单表容量在1KW，10亿条数据共100个表。

如何高效的写入数据库

单条写入数据库性能比较差，可以考虑批量写入数据库，批量数值动态可调整。每条1K，默认可先调整为100条批量写入。

批量数据如何保证数据同时写成功？MySQL Innodb存储引擎保证批量写入事务同时成功或失败。

写库时要支持重试，写库失败重试写入，如果重试N次后依然失败，可考虑单条写入100条到数据库，失败数据打印记录，丢弃即可。

此外写入时按照主键id顺序顺序写入可以达到最快的性能，而非主键索引的插入则不一定是顺序的，频繁地索引结构调整会导致插入性能下降。最好不创建非主键索引，或者在表创建完成后再创建索引，以保证最快的插入性能。

是否需要并发写同一个表

不能

1. 并发写同一个表无法保证数据写入时是有序的。
2. 提高批量插入的阈值，在一定程度上增加了插入并发度。无需再并发写入单表

MySQL存储引擎的选择

Myisam 比innodb有更好的插入性能，但失去了事务支持，批量插入时无法保证同时成功或失败，所以当批量插入超时或失败时，如果重试，势必对导致一些重复数据的发生。但是为了保证更快的导入速度，可以把myisam存储引擎列为计划之一。

现阶段我引用一下别人的性能测试结果：MyISAM与InnoDB对比分析

图片

从数据可以看到批量写入明显优于单条写入。并且在innodb关闭即时刷新磁盘策略后，innodb插入性能没有比myisam差太多。

innodb_flush_log_at_trx_commit: 控制MySQL刷新数据到磁盘的策略。

1. 默认=1，即每次事务提交都会刷新数据到磁盘，安全性最高不会丢失数据。
2. 当配置为0、2 会每隔1s刷新数据到磁盘， 在系统宕机、mysql crash时可能丢失1s的数据。

考虑到Innodb在关闭即时刷新磁盘策略时，批量性能也不错，所以暂定先使用innodb（如果公司MySQL集群不允许改变这个策略值，可能要使用MyIsam了。）。线上环境测试时可以重点对比两者的插入性能。

要不要进行分库

mysql 单库的并发写入是有性能瓶颈的，一般情况5K TPS写入就很高了。

当前数据都采用SSD 存储，性能应该更好一些。但如果是HDD的话，虽然顺序读写会有非常高的表现，但HDD无法应对并发写入，例如每个库10张表，假设10张表在并发写入，每张表虽然是顺序写入，由于多个表的存储位置不同，HDD只有1个磁头，不支持并发写，只能重新寻道，耗时将大大增加，失去顺序读写的高性能。所以对于HDD而言，单库并发写多个表并不是好的方案。回到SSD的场景，不同SSD厂商的写入能力不同，对于并发写入的能力也不同，有的支持500M/s，有的支持1G/s读写，有的支持8个并发，有的支持4个并发。在线上实验之前，我们并不知道实际的性能表现如何。

所以在设计上要更加灵活，需要支持以下能力

1. 支持配置数据库的数量
2. 支持配置并发写表的数量，（如果MySQL是HDD磁盘，只让一张表顺序写入，其他任务等待）

通过以上配置，灵活调整线上数据库的数量，以及写表并发度，无论是HDD还是SSD，我们系统都能支持。不论是什么厂商型号的SSD，性能表现如何，都可调整配置，不断获得更高的性能。这也是后面设计的思路，不固定某一个阈值数量，都要动态可调整。

接下来聊一下文件读取，10亿条数据，每条1K，一共是931G。近1T大文件，一般不会生成如此大的文件。所以我们默认文件已经被大致切分为100个文件。每个文件数量大致相同即可。为什么切割为100个呢？切分为1000个，增大读取并发，不是可以更快导入数据库吗？刚才提到数据库的读写性能受限于磁盘，但任何磁盘相比写操作，读操作都要更快。尤其是读取时只需要从文件读取，但写入时MySQL要执行建立索引，解析SQL、事务等等复杂的流程。所以写的并发度最大是100，读文件的并发度无需超过100。

更重要的是读文件并发度等于分表数量，有利于简化模型设计。即100个读取任务，100个写入任务，对应100张表。

如何保证写入数据库有序

既然文件被切分为100个10G的小文件，可以按照文件后缀+ 在文件行号 作为记录的唯一键，同时保证同一个文件的内容被写入同一个表。例如

1. index_90.txt 被写入 数据库database_9，table_0 ，
2. index_67.txt被写入数据库 database_6，table_7。

这样每个表都是有序的。整体有序通过数据库后缀+表名后缀实现。

如何更快地读取文件

10G的文件显然不能一次性读取到内存中，场景的文件读取包括

1. Files.readAllBytes一次性加载内内存
2. FileReader+ BufferedReader 逐行读取
3. File+ BufferedReader
4. Scanner逐行读取
5. Java NIO FileChannel缓冲区方式读取

在MAC上，使用这几种方式的读取3.4G大小文件的性能对比

详细的评测内容请参考：读取文件性能比较 ：https://zhuanlan.zhihu.com/p/142029812

由此可见 使用JavaNIO FileChannnel明显更优，但是FileChannel的方式是先读取固定大小缓冲区，不支持按行读取。也无法保证缓冲区正好包括整数行数据。如果缓冲区最后一个字节正好卡在一行数据中间，还需要额外配合读取下一批数据。如何把缓冲区变为一行行数据，比较困难。

File file = new File("/xxx.zip");<br>FileInputStream fileInputStream = null;<br>long now = System.currentTimeMillis();<br>try {<br>       fileInputStream = new FileInputStream(file);<br>       FileChannel fileChannel = fileInputStream.getChannel();<p>       int capacity = 1 * 1024 * 1024;//1M<br>       ByteBuffer byteBuffer = ByteBuffer.allocate(capacity);<br>       StringBuffer buffer = new StringBuffer();<br>       int size = 0;<br>       while (fileChannel.read(byteBuffer) != -1) {<br>          //读取后，将位置置为0，将limit置为容量, 以备下次读入到字节缓冲中，从0开始存储<br>          byteBuffer.clear();<br>          byte[] bytes = byteBuffer.array();<br>          size += bytes.length;<br>       }<br>       System.out.println("file size:" + size);<br>} catch (FileNotFoundException e) {<br>   e.printStackTrace();<br>} catch (IOException e) {<br>   e.printStackTrace();<br>} finally {<br>   //TODO close资源.<br>}<br>System.out.println("Time:" + (System.currentTimeMillis() - now));<br></p>