全网最硬核的操作，如何以最快的速度将10亿条数据插入MySQL？

这是我曾经问过的一个面试问题，这个问题不是关于你回答的确切时间，而是关于如何设计一个可以尽快插入 10 亿条数据的系统。

作者傻傻地回答了三个小时，但没有说明原因。 面试官看到我还没有醒来，就叫我回去等通知。 好在他把简历还给了我，我保存了一份简历。 今天试着重新考虑一下，然后打他的脸。

要想尽快将10亿条数据导入数据库，首先需要和面试官明确，10亿条数据存在什么形式，每条数据有多大，是否有序导入，是否不能重复，数据库是否mysql

在与面试官澄清假设后，存在以下约束：

10 亿条数据，每条数据 1 KB

数据内容是非结构化的用户访问日志，需要解析并写入数据库。

数据存储在 HDFS 或 S3 分布式文件存储中。

十亿条数据不是一个大文件，而是大致分为100个文件，后缀按顺序标注。

要求有序导入，尽量不要重复。

数据库是 MySQL

1. 数据库中的单个表可以支持10亿吗？

首先，在MySQL中将10亿条数据写入单个表是否可行？

答案是否定的，单表推荐值小于2000w。 这个值是如何计算的？

MySQL索引数据结构为B+树，所有数据存储在主键索引的叶节点上，即集群索引。 B+树插入和查询的性能与B+树层数直接相关，3层索引低于2000W，4层索引高于2000W。

MySQL B+ 索引的叶节点每页大小为 16k。 目前，每条数据正好是 1k，所以简单来说，每个叶节点存储 16 条数据。 b+ 索引的每个非叶子节点的大小也是 16k，但只需要存储主键和指向叶子节点的指针，我们假设主键的类型是 bigint，长度是 8 字节，在 innodb 中指针大小设置为 6 字节， 这样一共 14 个字节，这样一个非叶子节点可以存储 16 * 1024 14=1170。

即每个非叶子节点可以关联1170个叶子节点，每个叶子节点存储16条数据。 由此，我们可以得到B+树索引的层数和存储量的数量。 2kw以上的分度层数为4层，性能更差。

更多详情请参考 B+ 树层计算 （baijiahao.）。baidu.com/s?id=1709205029044038742&wfr=spider&for=pc）。

为了便于计算，我们可以设计一个容量为 1 kW 的单表，总共 100 个表，包含 10 亿条数据。

2. 如何高效写入数据库

如果单次写入数据库性能较差，可以考虑批量写入数据库，批量值可以动态调整。 每条消息为1k，默认可以批量调整为100条。

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

如果重试 N 次后仍然失败，可以考虑向数据库写入 100 条，打印失败数据的记录，然后丢弃。

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

是否需要同时写入同一个表

不。 对同一表的并发写入不能保证数据按顺序写入。

提高了批量插入的阈值，在一定程度上提高了插入的并发性，无需同时写入单个表。

3. MySQL存储引擎的选择

Myisam的插入性能比InnoDB更好，但是它失去了事务支持，并且不能保证批量插入会同时成功或失败，因此当批量插入超时或失败时，如果重试，必然会导致一些重复数据的出现。 但是，为了保证更快的导入速度，Myisam存储引擎可以列为计划之一。

在这个阶段，我想引用其他人的性能测试结果 Myisam vs. InnoDB （T.）。csdn.cn/efm9z）。

从数据中可以看出，批量写入明显优于单次写入。 而且在InnoDB禁用即时刷新磁盘策略后，InnoDB插入性能并不比Myisam差多少。

trx commit时的innodb flush log：控制MySQL中将数据刷新到磁盘的策略。

默认值 = 1，即每次提交事务时都会刷新数据到磁盘，安全性最高，不会丢失数据。

如果配置为每 1 秒刷新一次数据到磁盘，则当系统宕机或 MySQL 崩溃时，数据可能会丢失 1 秒。

考虑到InnoDB在禁用即时刷新磁盘策略时具有良好的批处理性能，因此暂时需要先使用InnoDB（如果公司的MySQL集群不允许更改此策略值，则可能需要使用MyISAM）。 在在线环境测试中，您可以重点比较两者的插入性能。

四、是否开展分库

对单个 MySQL 数据库的并发写入存在性能瓶颈，并且 5K TPS 写入通常很高。

目前数据存储在SSD中，性能应该更好。 但是，如果是硬盘，虽然顺序读写会有非常高的性能，但硬盘无法应对并发写入，比如每个数据库10个表，假设并发写入10个表，虽然每个表都是顺序写入的，但由于多个表的存储位置不同，HDD只有1个磁头， 不支持并发写入，只能重新查找，耗时会大大增加，顺序读写的高性能会丢失。

因此，对于 HDD，在单个数据库中同时写入多个表并不是一个好的解决方案。 回到SSD场景，不同的SSD厂商有不同的写入能力，并发写入的能力也不同，有的支持500M s，有的支持1G S读写，有的支持8并发，有的支持4并发。 **在开始实验之前，我们不知道实际性能如何。

因此，为了在设计上更加灵活，它需要支持以下功能：

可以配置的数据库数。

您可以配置要写入的并发表数（如果MySQL是HDD盘，则只按顺序写入一个表，其他任务等待）。

通过以上配置，可以灵活调整在线数据库的数量和表写入的并发性，无论是HDD还是SSD，我们的系统都可以支持。 无论什么制造商的 SSD 型号运行，您都可以调整配置以不断获得更高的性能。 这也是后期设计的思想，它不固定一定数量的阈值，但必须是动态可调的。

接下来，我们来谈谈文件读取，10亿条数据，每条1k，共计931g。 将近1T的大文件，一般不会生成这么大的文件。 因此，默认情况下，该文件大致分为 100 个文件。 每个文件的文件数大致相同。

为什么要切成100块？难道不能通过划分多达 1000 个并增加读取的并发性来更快地导入数据库吗？如前所述，数据库的读写性能受磁盘限制，但任何磁盘的读取操作都比写入操作快。 特别是，在读取时，只需要从文件中读取，但是在写入时，MySQL需要执行索引、解析SQL语句、事务等复杂过程。 因此，写入的最大并发为 100，读取文件的并发不需要超过 100。

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

5. 如何保证写入数据库有序

由于文件分为 100 个 10 g 的小文件，因此可以使用文件行号中的文件后缀 + 作为唯一键来记录文件，并确保将同一文件的内容写入同一个表中。 例如：

index_90.TXT 写入数据库 9，表 0，索引 67TXT 被写入数据库 数据库 6，表 7。

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

6. 如何更快地阅读文档

一个10g的文件显然不可能一下子全部读入内存，场景读取的文件包括：

files.ReadAllBytes 一次性加载所有内存。

FileReader+ BufferedReader 逐行读取。

file+ bufferedreader

扫描仪逐行读取。

J**a NIO 文件通道缓冲区模式。

在 Mac 上，使用以下几种方式阅读 34G大小文件的性能比较：

更多信息请参考：读取文件性能对比（zhuanlan.）zhihu.com/p/142029812）

可以看出，使用 j**anio filechannnel 显然更好，但使用 filechannel 的方式是先读取固定大小的缓冲区，不支持逐行读取。 也不能保证缓冲区将包含完全整数的数据行。 如果缓冲区的最后一个字节卡在一行数据的中间，则需要在下一批数据中读取它。 如何将缓冲区转换为一行数据是很困难的。

file file = new file("/xxx.zip");fileinputstream fileinputstream = null;long now = system.currenttimemillis();trysystem.out.println("file size:" + size);} catch (filenotfoundexception e) catch (ioexception e) finallysystem.out.println("time:" + system.currenttimemillis() now));

但是bufferedreader j**aio的读取方式自然可以支持行截断，而且性能还不错，10G文件，大致只需要读取30s，因为导入的整体瓶颈在写入部分，即使30s读取，也不会影响整体性能。 因此，使用 bufferedreader 逐行读取文件，即方案 3。

7. 如何协调读文件和写数据库的任务

这篇文章比较混乱，请耐心阅读。

是否可以读取 100 个读取任务，每个任务读取一批数据，并立即写入数据库？如前所述，由于数据库中并发写入的瓶颈，不可能满足一个数据库同时大批量写入10个表的要求，因此同时向数据库写入100个任务，这必然会导致每个数据库同时顺序写入10个表， 这加剧了磁盘上的并发写入压力。

为了最大限度地提高速度并减少磁盘并发写入导致的性能下降，需要暂停某些写入任务。 那么读任务需要限制并发吗？不，你没有。

如果写入任务和读取任务组合在一起，则读取任务的并发性将受到影响。 初步规划的读写任务是分开处理的，任何一个都不会耽误另一个。 然而，实际设计发现这个方案更加困难。

最初的想法是引入 Kafka，即 100 个读取任务向 Kafka 投递数据，写入任务消耗 Kafka 写入数据库。 当 100 个读取任务向 Kafka 投递消息时，顺序被打乱，那么如何保证消息有序写入数据库呢？我以为可以使用 Kafka 分区路由，即读取任务 ID 将同一任务的所有消息路由到同一个分区，以保证每个分区的有序消费。

您需要准备多少个分片？如果分区小于 100，则 100 显然太多了，例如 10。 然后，肯定会有多个任务的消息混合。 如果同一数据库中的多个表在 Kafka 分区中，并且该数据库仅支持批量写入单个表，但不支持并发写入多个表。 该数据库中多个表的消息混合在一个分片中，由于并发的限制，不支持写入的表对应的消息只能丢弃。 因此，该解决方案既复杂又难以实施。

因此，最终放弃了 Kafka 方案，暂时放弃了读写任务分离的方案。

最终方案简化为：读取任务读取一批数据，写入一批数据。 也就是说，该任务负责读取文件并将其插入数据库。

8.如何保证任务的可靠性

如果读取任务处于任务中间，并且服务已关闭，或者服务已释放，该怎么办？或者，如果数据库出现故障，写入失败，任务暂时终止，如何保证再次拉取任务时，在断点处继续处理，不会出现重复写入？

如前所述，您可以为每条记录设置一个主键 ID，即文件后缀索引 + 文件行号。 写入的幂等性可以通过主键 ID 来保证。

文件所在的行号，最大值大约是10g 1k=10m，即10000000。 拼接的最大后缀是 99。 最大的 ID 是 9900000000。

因此，数据库不需要自动递增主键ID，批量插入时可以指定主键ID。

如果另一个任务也需要导入数据库，该怎么办？如何隔离主键 ID，因此主键 ID 仍然需要与 taskID 连接。 例如，转换为长整型。 如果taskid过大，拼接值过大，则任务id转换为long类型时可能会报错。

最重要的是，如果某些任务写入 1kw，而其他任务写入 100w，则无法使用 long 类型知道每个占位符的长度，并且存在冲突的可能性。 但是，如果在串联的字符串中添加唯一索引，插入性能会更差，无法尽快导入数据。 因此，我们需要考虑另一种选择。

考虑使用 Redis 来记录当前任务的进度。 例如，Redis 记录任务的进度，并在任务成功写入数据库后批量更新任务的进度。

incrby key_name incr_amount

如果不确定 Redis 进度和数据库更新的一致性，可以考虑使用数据库二进制日志，每条新记录都会是 redis +1。

如果任务中断，则首先查询任务的偏移量。 然后，将文件读取到指定的偏移量以继续处理。

9. 如何协调阅读任务的并发性

如前所述，单个数据库插入表的并发性过高，影响数据库性能。 请考虑限制并发性。 如何？

由于读取任务和写入任务是组合的。 然后，您需要同时限制读取任务。 即一次只选择一批读写任务执行。

在此之前，您需要设计任务表的存储模型。

Bizid 预设了这些字段，以便将来支持其他产品线。 默认值为 1，表示当前业务线。

DatBaseIndex 代表分配的数据库后缀。

tableindex 表示分配的表名后缀。

ParentTaskId，即任务总 ID。

偏移量可用于记录当前任务的进度。

将 10 亿条数据导入数据库并划分为 100 个任务后，将添加 100 个 taskID 来处理一部分数据，即一个 10G 文件。

状态状态用于区分当前任务是否正在执行以及是否执行完成。

如何在抢占模式下考虑如何给每个节点分配任务。 每个节点需要抢占一个任务，每个节点一次只能抢占一个任务。 如何？可以考虑在每个节点上启动一个定时任务，周期性地扫描表，扫描到要执行的子任务，并尝试执行该任务。

如何控制并发？可以使用 Redission 的信号量。 键是数据库 ID

redissonclient redissonclient = redisson.create(config); rsemaphore rsemaphore = redissonclient.getsemaphore("semaphore");将 1 并发设置为 rsemaphoretrysetpermits(1); rsemaphore.tryacquire();**沈**锁，无堵塞。 

但是，使用信号量限流存在问题，如果任务忘记释放信号量，或者进程崩溃未能释放信号量，我该怎么办？请考虑向信号量添加超时。 那么如果任务执行时间过长，导致信号量提前释放，又有另一个客户争夺信号量，导致两个客户端同时编写一个任务，该如何处理呢？

什么，明明是导入10亿条数据到数据库的类似问题，怎么会变成分布式锁超时的类似问题？

其实没有解决信号量超时问题的好方法，正常的思维方式是：如果任务执行时间过长，导致信号量被释放，解决这个问题只需要续约，任务正在执行，只要信号量即将到期，续约一段时间， 并始终保持信号量未过期。但是 Redission 不提供信号量更新，怎么办？

换句话说，我们一直在尝试通过让多个节点竞争信号量来限制并发性。 您可以尝试选择一个主节点，并通过主节点轮换任务表。 有三种情况：

场景一：执行次数小于并发数

选择要以最低 ID 执行的任务，并将状态设置为“进行中”以通知发布消息。

使用消息的进程适用于分布式锁并开始处理任务。 处理完成后，将释放锁定。 在 Redission 分布式锁续费的帮助下，锁在任务完成之前不会过期。

场景 2：当前执行次数等于并发数

主节点尝试获取正在进行的任务是否有锁定。

如果没有锁定，则任务执行失败，应重新发布任务。 如果存在锁定，则表示正在执行任务。

场景三：当前执行中的执行次数大于并发数

报告异常情况，报警，并手动干预。

您可以使用主节点轮换任务以减少任务争用，通过 Kafka 发布消息，并处理接收消息的进程以处理任务。 为了保证更多的节点参与消费，可以考虑增加 Kafka 分片的数量。 尽管每个节点可以同时处理多个任务，但这并不影响性能，因为性能瓶颈在数据库中。

那么应该如何选择主节点呢？您可以使用 zookeeper+curator 来选择主节点。 可靠性比较高。

有许多因素会影响将 10 亿条数据插入数据库所需的时间。 这包括数据库磁盘类型和性能。 如果数据库分片数量可以分成1000个数据库，当然性能会更快，分片和表的数量应该根据在线的实际情况来决定，这极大地决定了写入速率。 最后，批量数据库插入的阈值也不是一成不变的，需要不断测试和调整才能达到最佳性能。 您可以按照 100、1000、10000 等最佳阈值继续尝试批量插入。

总结

最后，总结几个要点：

在设计解决方案之前，需要先确认约束条件。 确定面试官主要想问的方向，例如如何将 1t 文件剪切成更小的文件，是一个难点，但这可能不是面试官想要调查的问题。

从数据规模的角度来看，需要对库表进行分片，大致确定分表的规模。

根据对单个数据库写入瓶颈的分析，确定需要对数据库进行分片。

考虑到磁盘对并发写入的支持方式不同，需要限制多个表写入同一数据库的并发性。 并且支持动态调整，方便在环境中调试最优值。

MySQL InnoDB和Myisam存储引擎支持不同的写入性能，也要进行对比验证。

批量数据库插入的最佳阈值需要通过重复测试来确定。

由于并发限制，基于 Kafka 的读写任务很难分离。 因此，合并读取任务和写入任务。

需要 Redis 来记录任务执行的进度。 当任务失败时，重新导入时会记录进度，避免数据重复问题。

分布式任务的协调是一个难点，使用Redission信号量无法解决超时续费的问题。 主节点可以分配任务+分布式锁，保证任务独占写入。 使用 zookeeper+curator 选择主节点。

作者丨五羊神功**丨稀土掘金：觉进CN POST 7280436213902819369DBAPLUS社区欢迎技术人员在editor@dbapluscn