我前阵子接了个数据迁移的活儿几千张表、上亿条记录要从旧库搬到新库最头疼的就是往 MySQL 里灌数据。单线程一条条插照那个速度得跑到天荒地老网上的方案一搜一大堆不是只讲理论就是代码跑起来各种翻车。折腾了几天踩了不少坑总算总结出一套能扛住千万级数据量的多线程插入数据库方案。这套东西我自己用下来效率提升特别明显从单线程的每秒几百条干到了每秒几万条而且稳定性还不错。这篇文章就把我的完整思路、核心代码、参数计算逻辑还有那些文档里根本不会写的避坑经验一次性给大伙儿捋清楚。不管你是做数据迁移、日志入库、还是批量同步任务只要你准备用多线程往数据库里灌数据这文章就能让你少走几个礼拜弯路。1. 一开始得想明白多线程插入到底在解决什么问题很多朋友一上来就开线程池噼里啪啦写一堆并发代码结果一跑数据库直接被干趴或者速度还不如单线程。为啥因为根本没搞明白瓶颈在哪儿。1.1 单线程插入慢慢在哪里咱们用大白话拆解一下。数据库插入一条数据本质上要经过网络传输、SQL解析、执行计划生成、数据页加载、行锁竞争、索引维护、事务日志写入、最终刷盘这几个步骤。单线程的时候这些工序是一条流水线一批一批地走。假设一条数据走完全程要 2 毫秒你一批插 1000 条那就要 2 秒这还只是理想情况。但真实生产环境数据库服务器的磁盘、内存、CPU 都在干别的活单线程经常要等锁、等IO、等网络往返实际耗时可能比理论值高好几倍。这种时候光靠把 SQL 从单条改成批量能把网络往返的时间省下来但 CPU 和磁盘的利用效率还是上不去等于厨房里只有一个厨师锅再多也白搭。1.2 多线程到底优化了哪一环多线程的核心作用就是把这条串行的流水线拆成多条并行流水线让数据库服务器的 CPU 多核能力、磁盘并发能力真正被用起来。还是拿厨房打比方单线程是一个厨师从头炒到尾多线程是多个厨师同时开火各炒各的锅。只要你配料间的食材供应跟得上连接池有足够连接传菜通道够宽网络带宽充足整体出菜速度就能成倍往上翻。但这里有个前提——你得先确认瓶颈确实在单线程的串行执行上而不是已经在数据库本身的硬资源上。如果磁盘 IO 已经满了、或者表结构锁竞争非常激烈你再开 10 个线程也没用只会让情况更糟。所以动手之前先跑一个基准测试看看单线程批量插入 1 万条要多久心里有个底后面调参数才有依据。1.3 哪些场景适合这套方案历史数据迁移从文件或者老库往新库导数据。日志类的流水表入库数据量大、没有复杂的事务关联。从接口或者消息队列拉取数据批量写入冷表或者统计表。定时任务批量同步业务数据。反过来如果业务逻辑本身就要求强事务、强一致比如下单扣库存这种多线程插入方案就不太适合直接照搬因为跨线程的事务边界真的很难控制后面我会详细讲。2. 方案选型与核心原理拆解2.1 线程池搭配批量提交为什么是黄金组合我之前也见过有人用多线程但是每条 SQL 还是单条插入那效果说实话很一般。因为多线程帮你把 CPU 跑满了但是网络往返这一层单条插入的代价一点没少。一次网络往返在局域网内也得几百微秒到几毫秒一万条就是一万次往返浪费的时间非常可观。所以我们一定要多线程 批量提交一起上。假设单条插入耗时 2 毫秒1000 条组装成一个批次批量插入可能只需要 30 毫秒平均每条的耗时就被拉低了一个数量级。线程数一多吞吐量自然就上来了。批量提交 SQL 的写法也很有讲究我用的是 JDBC 的rewriteBatchedStatementstrue这个参数它能把 MyBatis 或者 JDBC 攒下来的批量 SQL 在数据库驱动层重写成一条多 VALUES 的 INSERT性能差距少说也有 3 到 5 倍。这个参数真的非常重要很多人批量插入慢就是没加它。2.2 事务边界怎么控制这里有个特别常见的认知误区新手经常会问我开了 8 个线程同时插入一批数据它们怎么保持同一个事务直说吧普通的多线程插入方案里每个线程必须各管各的事务不存在跨线程共享一个数据库事务这种操作。除非你引入分布式事务那一套像 Seata、两阶段提交那些但那种东西在数据迁移和批量入库场景里完全是杀鸡用牛刀而且性能会掉得你怀疑人生。那数据一致性怎么办我的做法是每个线程处理自己负责的数据分片插入完成后自己提交。如果某个分片失败了记录下失败的数据范围和原因后面整体重跑一次就行。只要你的数据源是幂等的比如插入用主键或唯一索引去重整体重跑成本极低根本不需要搞什么分布式事务。所以说设计多线程插入方案第一件事不是写代码而是确认你的业务是不是能接受局部失败 整体重跑这个模型。如果答案是绝对不能接受任何一条脏数据那你可能得回到单线程批量插入的思路上来先把基础做扎实。2.3 数据分片谁来分配任务多线程干活最关键的就是每个人拿到的活不能重、不能漏。常见分片策略有这几种按主键 ID 范围分片好比把 1 到 1000 万的数据切成 100 个区间每个线程拿一个区间去查询和插入。优点是实现简单缺点是需要知道 ID 的分布情况如果数据有空洞会导致各个线程工作量不均。按时间分片适合日志、流水类的数据每天或每小时分一片。这个策略比较符合业务直觉。缺点是如果某一段时间的数据量特别大比如大促那天的日志单个分片可能把线程拖死。按哈希取模分片把每一条数据的某个字段做哈希然后对线程数取模比如hash(id) % threadCount这样数据分布非常均匀。缺点是如果你需要按顺序插入哈希分片会打乱顺序而且后续扩容线程数量会导致取模结果全面失效。我实际做数据迁移的时候大部分场景用的是先查最小 ID 和最大 ID然后按 ID 做均分。这个方案代码量最少对数据源本身没有侵入性。如果数据源不理想ID 分布不均匀就退回到按哈希分片。2.4 数据源读取与目标库写入的节奏多线程插入方案里还有一个隐藏关键点数据从哪来。很多人是开一个线程池每个线程自己去查源头数据库然后插入目标库这个没问题。但如果你是用单线程读数据、丢到内存队列然后多线程从队列里消费再插入目标库就要小心了—— 读取速度跟不上消费速度的时候队列会越堆越高最后直接内存溢出。所以我后来把读取提前到分片阶段直接处理每个线程自己负责一部分数据的查询和插入不做公共队列共享这样全局的任务分配和内存占用都变得可控。3. 实操实现一套能直接跑的代码方案3.1 基础环境配置先交代一下我当时的环境方便大家参考对比数据库MySQL 8.0InnoDB 引擎机器是 SSD 磁盘。服务器4 核 8G 的云主机。代码Java 8Spring Boot MyBatis。目标场景把某张 1000 万级别的历史流水表从文件导入新表。连接池我用的是 HikariCP配置了最大连接数 16。线程池的核心线程数我一开始给的是 8后来根据实测调整为 16原因后面再说。数据库连接串里JDBC 的rewriteBatchedStatementstrue和useServerPrepStmtstrue两个参数一定要加。这俩参数的作用一个是让驱动把批量插入改写成真正的多值 INSERT另一个是让语句执行走服务端预编译减少 SQL 解析的开销。单是这两个参数我自己测下来就能带来 2 倍以上的性能提升。3.2 核心代码分片与线程池管理先创建线程池我用的是ThreadPoolExecutor没用无敌的Executors.newFixedThreadPool因为那个阻塞队列是无界的任务一多内存容易爆。线程池名字可以随便起个容易排查问题的比如>int processors Runtime.getRuntime().availableProcessors(); int threadCount Math.max(4, processors * 2); ThreadPoolExecutor executor new ThreadPoolExecutor( threadCount, threadCount, 60L, TimeUnit.SECONDS, new LinkedBlockingQueue(threadCount * 2), new ThreadFactory() { private final AtomicInteger counter new AtomicInteger(1); Override public Thread newThread(Runnable r) { return new Thread(r, data-import-worker- counter.getAndIncrement()); } }, new ThreadPoolExecutor.CallerRunsPolicy() );注意核心线程数我为什么没有开特别大因为这个任务是 IO 密集型的但数据库连接池就 16 个连接线程数再高也没有连接可用反而会出现大量线程在等待获取连接的阻塞状态白白消耗内存。CallerRunsPolicy 的作用是当线程队列满了之后让提交任务的线程自己执行这个任务相当于一个背压机制能避免任务无限堆积导致 OOM。然后写分片逻辑。假设我的源数据有一个主键 ID范围从 1 到 1000 万我要把它切成 16 片。先查出最大和最小 ID然后按区间均分。long minId sourceMapper.getMinId(); long maxId sourceMapper.getMaxId(); long total maxId - minId 1; long step total / threadCount; ListShard shards new ArrayList(); for (int i 0; i threadCount; i) { long start minId i * step; long end (i threadCount - 1) ? maxId : (start step); shards.add(new Shard(start, end)); }这个分片方式非常朴素但在绝大多数情况下都够用。每个分片对应一个线程线程独立读取和插入互不干扰。3.3 核心代码每个线程内的批量插入真正干活的是分片任务核心逻辑就是对分片内的数据进行分页读取然后批量插入目标表。public class ImportTask implements Runnable { private final Shard shard; private final TargetMapper targetMapper; private final int batchSize 2000; Override public void run() { long currentStart shard.getStart(); long end shard.getEnd(); while (currentStart end) { // 每次从源表捞 batchSize 条 ListDataRecord records sourceMapper.selectByRange(currentStart, currentStart batchSize - 1); if (records null || records.isEmpty()) { break; } // 批量写入目标表 targetMapper.batchInsert(records); currentStart batchSize; } } }这里batchSize为什么取 2000我是根据实验测出来的。批量太小SQL 组装和网络往返的占比会上升批量太大单条 SQL 过长超过 MySQL 的max_allowed_packet而且事务太大一旦出错回滚会非常慢undo log 也会膨胀。2000 是我当时环境下的一个甜点值。对应的 MyBatis Mapper 里批量插入用foreach来拼 VALUES。这里有一个性能细节非常重要如果你用 MyBatis 的foreach拼接底层其实还是走 JDBC 批量执行必须确保连接串上加了rewriteBatchedStatementstrue否则那个 foreach 是假批量性能提升非常有限。insert idbatchInsert parameterTypelist INSERT INTO target_table ( id, user_id, amount, create_time ) VALUES foreach collectionlist itemitem separator, (#{item.id}, #{item.userId}, #{item.amount}, #{item.createTime}) /foreach /insert3.4 主线程的任务编排与结果收集写完任务核心逻辑主线程就是提交任务、等待完成、汇总结果。我这里用了一个CountDownLatch来等待所有任务结束同时记录每个任务的成功与失败状态。int taskCount shards.size(); CountDownLatch latch new CountDownLatch(taskCount); ListFutureImportResult futures new ArrayList(); for (Shard shard : shards) { CallableImportResult task () - { try { ImportResult result new ImportTask(shard, targetMapper).call(); return result; } finally { latch.countDown(); } }; futures.add(executor.submit(task)); } latch.await(2, TimeUnit.HOURS);注意我用的是submit(Callable)而不是execute(Runnable)因为我想拿到每个分片的处理行数和异常信息方便最后的统计。如果某个分片失败了我不做实时重试而是先记录下来等全部任务跑完之后统一重跑失败分片。3.5 实测结果与参数调整记录这是我当时在测试环境跑的一组对比数据给大家参考。表结构是有 8 个字段、4 个索引的普通流水表一次性插入 500 万条数据都在本机文件里读取成本不计入插入耗时。线程数批量大小总耗时备注11000486秒基线单线程批量插入41000156秒提升明显约3倍8100088秒继续提升16100071秒开始接近瓶颈16200052秒批量大小翻倍耗时进一步下降32200061秒线程数过多反而变慢32500078秒批量过大SQL过长锁竞争明显从这个表可以看出来线程数从 1 到 16性能提升非常线性但到 32 就开始倒退了。原因很直接连接池就 16 个连接32 个线程有 16 个在等待连接上下文切换和线程调度的开销超过了并发带来的收益。批量大小同样存在甜点区2000 到 3000 在那个环境下最优。这个表也说明了一个问题网上的经验参数再对也要自己动手跑一遍因为你机器的 CPU、磁盘、数据库配置都不一样。4. 常见问题与排查技巧实录这部分就是我这几天踩坑踩出来的实战经验了。很多问题你不跑到一定数据量根本碰不到碰到了网上也很少有清晰解释我把自己排查的过程和思路完整写下来。4.1 数据库连接被耗尽程序卡死这是个非常典型的并发问题。现象是程序启动后跑了几分钟突然整体停摆日志里全是Connection is not available, request timed out。排查下来发现线程池的线程数设了 20连接池最大连接数却只配了 10而且每个线程在插入前还会查一次源表等于每个线程同时要占用 2 个连接。20 个线程就有 40 个连接需求连接池只有 10必然大量等待。解决思路也很简单让线程数、连接池大小、数据库实际负载形成一个稳定的三角关系。我当时把连接池调大到 20线程数还是 16同时把查询源表和插入目标表拆开不在同一个事务里持有源库连接这样单个线程的连接占用就控制在 1 个以内了。如果你的数据库服务器连接数本身上限不高那就得反过来调小线程数宁让 CPU 闲着也别让连接池爆掉。4.2 InnoDB 死锁锁等待超时第二个大坑是死锁。当时我用 16 个线程往一张有多个二级索引的表里插数据日志里开始出现Deadlock found when trying to get lock。这个问题的根源是 InnoDB 的加锁顺序问题多个线程同时插入不同的主键值但二级索引的更新顺序却不是完全一致的A 线程先锁了 idx_a 再锁 idx_bB 线程先锁了 idx_b 再锁 idx_a两边互相等就死锁了。排查死锁的标准动作是执行SHOW ENGINE INNODB STATUS里面会有最近一次死锁的详细信息能看到具体是哪个事务、持有哪些锁、等待哪个锁。我当时处理方案有三步第一步降低并发度从 16 降到 8死锁频率立即下降。第二步在所有分片任务里强制按主键排序后批量插入让各个线程的加锁顺序尽量一致。第三步对死锁异常做重试捕获异常后重试该批次最多 3 次。如果连重试都反复失败就要检查是不是表结构有问题比如过多的唯一索引在并发插入时冲突概率特别高那就需要先去掉部分索引等数据导完再统一重建。这个方法在数据迁移里非常常用。4.3 事务日志和撤销段膨胀磁盘飙红批量插入这种场景每批次 2000 条每个事务要处理 2000 行的 undo log。并发 16 个线程时同时活跃的事务有 16 个redo log 的写入和刷盘压力非常大。如果你的 MySQL 没有调优过innodb_log_file_size可能只有 48M 或者 100M几百个事务跑下来日志切换频繁性能会严重下降。我当时是把 innodb 日志文件调到 1G同时把innodb_flush_log_at_trx_commit在导入场景临时改成了 0。这个值改成 0 意味着每秒钟才刷一次日志事务提交的持久性会下降但导入速度能提升非常明显。注意这只是导入时的临时设置导入完成后一定要改回默认值 1否则数据库崩溃会丢最近一秒的事务生产环境绝对不能这么干。4.4 数据读取出错某个分片反复失败多线程还有个问题某个分片的数据本身有问题比如某条记录字段长度超了或者格式不对导致这个线程每次都失败影响整体进度。我当时排查的方式是在每个分片任务里做两层异常捕获第一层是整个 batch 级别的失败后把 batchSize 减半继续按更小的批次重试第二层是当 batchSize 已经缩小到 100 还失败就逐条插入定位具体是哪条数据有问题。这套机制看起来代码量不大但在几千万数据里排查脏数据的时候真的救命。不然你拿到一条堆栈信息根本不知道是 5000 万条里的哪一条出了幺蛾子。4.5 内存溢出任务队列堆积前面提到过如果提交任务的速度大于消费速度任务队列会无限增长。我当时就遇到过4 核 8G 的机器堆内存给了 4G一个失控的任务队列直接把堆撑爆了Full GC 频繁整个服务卡死。排查方式很简单先看线程池里的阻塞队列大小打印队列剩余容量。我当时没控制队列上限所以问题暴露得很直接。解决方案是用有界队列并且把拒绝策略设置为CallerRunsPolicy。这样一来当线程处理不过来的时候提交任务的线程自己也会变成工人天然形成了一层反向压力既不会 OOM也不会丢数据。5. 性能调优与 MySQL 侧配合多线程插入方案要发挥到极致不只是应用层改参数数据库侧配合也特别重要。5.1 关键参数的推荐值当时优化的最终形态我整理成下面这张表大家可以按照自己的实际环境再做微调。配置项推荐值说明线程池核心线程数2 倍 CPU 核数IO 密集型任务2 倍比较保险连接池最大连接数与线程数一致或略大于一定不要小于线程数JDBC 批量参数rewriteBatchedStatementstrue必开否则批量是假的单批次行数1000 到 3000根据字段数和行大小调整innodb_log_file_size至少 1G避免日志切换成为瓶颈innodb_flush_log_at_trx_commit导入时临时设 0导入完成后必须改回默认值max_allowed_packet至少 64M避免大批量 SQL 被拒二级索引导入前可临时删除导入完成后重建5.2 为什么索引在多线程插入时这么重要索引是写入性能的天敌这句话在并发场景下尤其成立。每插入一条数据InnoDB 需要同步更新主键索引和所有二级索引。二级索引多一个写入的 IO 和锁开销就多一截。尤其实在多线程并发插入同一条数据附近的主键区间索引页的竞争会变得特别激烈。我后来在导入目标表之前先把非必要的二级索引和触发器全部停掉导入完成后再一条命令重建索引。整个过程下来导入时间直接砍了一半。当然这个操作只适合离线迁移场景如果是线上实时写入业务动索引要非常谨慎尽量在流量低峰做。5.3 最终稳定状态长什么样经过上面所有调整我的多线程导入环境最终稳定在16 线程、16 连接、单批次 2000 条、rewriteBatchedStatementstrue、innodb_log_file_size 1G、offline 导索引。在这种状态下1000 万条数据的插入时间在 50 秒左右CPU 利用率稳定在 70% 到 80%没有死锁、没有连接池等待、没有 OOM。如果是比这更极端的数据量比如 1 亿条可以用同样的方案扩展成分批执行先把数据源分成多个大文件再对每个文件做一次多线程导入。整体思路完全一致只是外面再套了一层循环。6. 几个我之前忽略、但后来觉得特别重要的细节最后再补充几个实操中特别容易被人忽略的细节这些细节都极小但影响极大。第一件事如果你的源表和目标表不在同一个数据库实例上不要在应用层用同一个数据源来查和写。我当时图省事所有线程共用同一个数据源结果连接池资源在读和写两个方向上互相抢占性能反而下降。正确的做法是拆两个数据源一个给读取一个给写入各自独立连接池互不挤兑。第二件事程序跑完一定要主动executor.shutdown()然后等待线程池优雅关闭。我知道这听起来像废话但真有人在生产环境跑完任务未关闭线程池导致应用进程一直不退、连接不释放最后数据库连接池被占满其他业务全部连不上库。排查这个问题的时候我一度怀疑是数据库出问题了后来才发现是应用线程池没有释放。这个坑希望你们千万不要踩。第三件事尽量为每个批次的数据加上一个批次号batch_id字段。有了这个字段你就可以随时通过一条简单的 SQL 查出来哪些数据已经插入成功哪些还是缺失状态配合失败重跑机制排查问题的时候会比大海捞针容易得多。这个思路在数据迁移、日志入库、同步任务里都非常通用。多线程插入数据库核心不是堆线程而是理解并发环境下面临的三大矛盾连接池有限的资源、数据库锁竞争、日志与 IO 的吞吐。把这三个矛盾都理顺了你的方案自然就快了。我后来在另一个更复杂的项目里继续沿用了这套设计思路只是把分片逻辑从按 ID 改成了按业务维度拆但底层的线程池管理、批量参数、事务边界控制、失败重试机制基本上没有动过。这套方案的价值就在于它不依赖于某一个具体的表结构而是一套可以复用的方法论希望能帮到正在跟大批量数据入库搏斗的朋友们。 
长河君 · 2026/10/19 12:03:10 · 阅读 1.2k MySQL千万级数据多线程批量插入方案与性能调优实战
我前阵子接了个数据迁移的活儿几千张表、上亿条记录要从旧库搬到新库最头疼的就是往 MySQL 里灌数据。单线程一条条插照那个速度得跑到天荒地老网上的方案一搜一大堆不是只讲理论就是代码跑起来各种翻车。折腾了几天踩了不少坑总算总结出一套能扛住千万级数据量的多线程插入数据库方案。这套东西我自己用下来效率提升特别明显从单线程的每秒几百条干到了每秒几万条而且稳定性还不错。这篇文章就把我的完整思路、核心代码、参数计算逻辑还有那些文档里根本不会写的避坑经验一次性给大伙儿捋清楚。不管你是做数据迁移、日志入库、还是批量同步任务只要你准备用多线程往数据库里灌数据这文章就能让你少走几个礼拜弯路。1. 一开始得想明白多线程插入到底在解决什么问题很多朋友一上来就开线程池噼里啪啦写一堆并发代码结果一跑数据库直接被干趴或者速度还不如单线程。为啥因为根本没搞明白瓶颈在哪儿。1.1 单线程插入慢慢在哪里咱们用大白话拆解一下。数据库插入一条数据本质上要经过网络传输、SQL解析、执行计划生成、数据页加载、行锁竞争、索引维护、事务日志写入、最终刷盘这几个步骤。单线程的时候这些工序是一条流水线一批一批地走。假设一条数据走完全程要 2 毫秒你一批插 1000 条那就要 2 秒这还只是理想情况。但真实生产环境数据库服务器的磁盘、内存、CPU 都在干别的活单线程经常要等锁、等IO、等网络往返实际耗时可能比理论值高好几倍。这种时候光靠把 SQL 从单条改成批量能把网络往返的时间省下来但 CPU 和磁盘的利用效率还是上不去等于厨房里只有一个厨师锅再多也白搭。1.2 多线程到底优化了哪一环多线程的核心作用就是把这条串行的流水线拆成多条并行流水线让数据库服务器的 CPU 多核能力、磁盘并发能力真正被用起来。还是拿厨房打比方单线程是一个厨师从头炒到尾多线程是多个厨师同时开火各炒各的锅。只要你配料间的食材供应跟得上连接池有足够连接传菜通道够宽网络带宽充足整体出菜速度就能成倍往上翻。但这里有个前提——你得先确认瓶颈确实在单线程的串行执行上而不是已经在数据库本身的硬资源上。如果磁盘 IO 已经满了、或者表结构锁竞争非常激烈你再开 10 个线程也没用只会让情况更糟。所以动手之前先跑一个基准测试看看单线程批量插入 1 万条要多久心里有个底后面调参数才有依据。1.3 哪些场景适合这套方案历史数据迁移从文件或者老库往新库导数据。日志类的流水表入库数据量大、没有复杂的事务关联。从接口或者消息队列拉取数据批量写入冷表或者统计表。定时任务批量同步业务数据。反过来如果业务逻辑本身就要求强事务、强一致比如下单扣库存这种多线程插入方案就不太适合直接照搬因为跨线程的事务边界真的很难控制后面我会详细讲。2. 方案选型与核心原理拆解2.1 线程池搭配批量提交为什么是黄金组合我之前也见过有人用多线程但是每条 SQL 还是单条插入那效果说实话很一般。因为多线程帮你把 CPU 跑满了但是网络往返这一层单条插入的代价一点没少。一次网络往返在局域网内也得几百微秒到几毫秒一万条就是一万次往返浪费的时间非常可观。所以我们一定要多线程 批量提交一起上。假设单条插入耗时 2 毫秒1000 条组装成一个批次批量插入可能只需要 30 毫秒平均每条的耗时就被拉低了一个数量级。线程数一多吞吐量自然就上来了。批量提交 SQL 的写法也很有讲究我用的是 JDBC 的rewriteBatchedStatementstrue这个参数它能把 MyBatis 或者 JDBC 攒下来的批量 SQL 在数据库驱动层重写成一条多 VALUES 的 INSERT性能差距少说也有 3 到 5 倍。这个参数真的非常重要很多人批量插入慢就是没加它。2.2 事务边界怎么控制这里有个特别常见的认知误区新手经常会问我开了 8 个线程同时插入一批数据它们怎么保持同一个事务直说吧普通的多线程插入方案里每个线程必须各管各的事务不存在跨线程共享一个数据库事务这种操作。除非你引入分布式事务那一套像 Seata、两阶段提交那些但那种东西在数据迁移和批量入库场景里完全是杀鸡用牛刀而且性能会掉得你怀疑人生。那数据一致性怎么办我的做法是每个线程处理自己负责的数据分片插入完成后自己提交。如果某个分片失败了记录下失败的数据范围和原因后面整体重跑一次就行。只要你的数据源是幂等的比如插入用主键或唯一索引去重整体重跑成本极低根本不需要搞什么分布式事务。所以说设计多线程插入方案第一件事不是写代码而是确认你的业务是不是能接受局部失败 整体重跑这个模型。如果答案是绝对不能接受任何一条脏数据那你可能得回到单线程批量插入的思路上来先把基础做扎实。2.3 数据分片谁来分配任务多线程干活最关键的就是每个人拿到的活不能重、不能漏。常见分片策略有这几种按主键 ID 范围分片好比把 1 到 1000 万的数据切成 100 个区间每个线程拿一个区间去查询和插入。优点是实现简单缺点是需要知道 ID 的分布情况如果数据有空洞会导致各个线程工作量不均。按时间分片适合日志、流水类的数据每天或每小时分一片。这个策略比较符合业务直觉。缺点是如果某一段时间的数据量特别大比如大促那天的日志单个分片可能把线程拖死。按哈希取模分片把每一条数据的某个字段做哈希然后对线程数取模比如hash(id) % threadCount这样数据分布非常均匀。缺点是如果你需要按顺序插入哈希分片会打乱顺序而且后续扩容线程数量会导致取模结果全面失效。我实际做数据迁移的时候大部分场景用的是先查最小 ID 和最大 ID然后按 ID 做均分。这个方案代码量最少对数据源本身没有侵入性。如果数据源不理想ID 分布不均匀就退回到按哈希分片。2.4 数据源读取与目标库写入的节奏多线程插入方案里还有一个隐藏关键点数据从哪来。很多人是开一个线程池每个线程自己去查源头数据库然后插入目标库这个没问题。但如果你是用单线程读数据、丢到内存队列然后多线程从队列里消费再插入目标库就要小心了—— 读取速度跟不上消费速度的时候队列会越堆越高最后直接内存溢出。所以我后来把读取提前到分片阶段直接处理每个线程自己负责一部分数据的查询和插入不做公共队列共享这样全局的任务分配和内存占用都变得可控。3. 实操实现一套能直接跑的代码方案3.1 基础环境配置先交代一下我当时的环境方便大家参考对比数据库MySQL 8.0InnoDB 引擎机器是 SSD 磁盘。服务器4 核 8G 的云主机。代码Java 8Spring Boot MyBatis。目标场景把某张 1000 万级别的历史流水表从文件导入新表。连接池我用的是 HikariCP配置了最大连接数 16。线程池的核心线程数我一开始给的是 8后来根据实测调整为 16原因后面再说。数据库连接串里JDBC 的rewriteBatchedStatementstrue和useServerPrepStmtstrue两个参数一定要加。这俩参数的作用一个是让驱动把批量插入改写成真正的多值 INSERT另一个是让语句执行走服务端预编译减少 SQL 解析的开销。单是这两个参数我自己测下来就能带来 2 倍以上的性能提升。3.2 核心代码分片与线程池管理先创建线程池我用的是ThreadPoolExecutor没用无敌的Executors.newFixedThreadPool因为那个阻塞队列是无界的任务一多内存容易爆。线程池名字可以随便起个容易排查问题的比如>int processors Runtime.getRuntime().availableProcessors(); int threadCount Math.max(4, processors * 2); ThreadPoolExecutor executor new ThreadPoolExecutor( threadCount, threadCount, 60L, TimeUnit.SECONDS, new LinkedBlockingQueue(threadCount * 2), new ThreadFactory() { private final AtomicInteger counter new AtomicInteger(1); Override public Thread newThread(Runnable r) { return new Thread(r, data-import-worker- counter.getAndIncrement()); } }, new ThreadPoolExecutor.CallerRunsPolicy() );注意核心线程数我为什么没有开特别大因为这个任务是 IO 密集型的但数据库连接池就 16 个连接线程数再高也没有连接可用反而会出现大量线程在等待获取连接的阻塞状态白白消耗内存。CallerRunsPolicy 的作用是当线程队列满了之后让提交任务的线程自己执行这个任务相当于一个背压机制能避免任务无限堆积导致 OOM。然后写分片逻辑。假设我的源数据有一个主键 ID范围从 1 到 1000 万我要把它切成 16 片。先查出最大和最小 ID然后按区间均分。long minId sourceMapper.getMinId(); long maxId sourceMapper.getMaxId(); long total maxId - minId 1; long step total / threadCount; ListShard shards new ArrayList(); for (int i 0; i threadCount; i) { long start minId i * step; long end (i threadCount - 1) ? maxId : (start step); shards.add(new Shard(start, end)); }这个分片方式非常朴素但在绝大多数情况下都够用。每个分片对应一个线程线程独立读取和插入互不干扰。3.3 核心代码每个线程内的批量插入真正干活的是分片任务核心逻辑就是对分片内的数据进行分页读取然后批量插入目标表。public class ImportTask implements Runnable { private final Shard shard; private final TargetMapper targetMapper; private final int batchSize 2000; Override public void run() { long currentStart shard.getStart(); long end shard.getEnd(); while (currentStart end) { // 每次从源表捞 batchSize 条 ListDataRecord records sourceMapper.selectByRange(currentStart, currentStart batchSize - 1); if (records null || records.isEmpty()) { break; } // 批量写入目标表 targetMapper.batchInsert(records); currentStart batchSize; } } }这里batchSize为什么取 2000我是根据实验测出来的。批量太小SQL 组装和网络往返的占比会上升批量太大单条 SQL 过长超过 MySQL 的max_allowed_packet而且事务太大一旦出错回滚会非常慢undo log 也会膨胀。2000 是我当时环境下的一个甜点值。对应的 MyBatis Mapper 里批量插入用foreach来拼 VALUES。这里有一个性能细节非常重要如果你用 MyBatis 的foreach拼接底层其实还是走 JDBC 批量执行必须确保连接串上加了rewriteBatchedStatementstrue否则那个 foreach 是假批量性能提升非常有限。insert idbatchInsert parameterTypelist INSERT INTO target_table ( id, user_id, amount, create_time ) VALUES foreach collectionlist itemitem separator, (#{item.id}, #{item.userId}, #{item.amount}, #{item.createTime}) /foreach /insert3.4 主线程的任务编排与结果收集写完任务核心逻辑主线程就是提交任务、等待完成、汇总结果。我这里用了一个CountDownLatch来等待所有任务结束同时记录每个任务的成功与失败状态。int taskCount shards.size(); CountDownLatch latch new CountDownLatch(taskCount); ListFutureImportResult futures new ArrayList(); for (Shard shard : shards) { CallableImportResult task () - { try { ImportResult result new ImportTask(shard, targetMapper).call(); return result; } finally { latch.countDown(); } }; futures.add(executor.submit(task)); } latch.await(2, TimeUnit.HOURS);注意我用的是submit(Callable)而不是execute(Runnable)因为我想拿到每个分片的处理行数和异常信息方便最后的统计。如果某个分片失败了我不做实时重试而是先记录下来等全部任务跑完之后统一重跑失败分片。3.5 实测结果与参数调整记录这是我当时在测试环境跑的一组对比数据给大家参考。表结构是有 8 个字段、4 个索引的普通流水表一次性插入 500 万条数据都在本机文件里读取成本不计入插入耗时。线程数批量大小总耗时备注11000486秒基线单线程批量插入41000156秒提升明显约3倍8100088秒继续提升16100071秒开始接近瓶颈16200052秒批量大小翻倍耗时进一步下降32200061秒线程数过多反而变慢32500078秒批量过大SQL过长锁竞争明显从这个表可以看出来线程数从 1 到 16性能提升非常线性但到 32 就开始倒退了。原因很直接连接池就 16 个连接32 个线程有 16 个在等待连接上下文切换和线程调度的开销超过了并发带来的收益。批量大小同样存在甜点区2000 到 3000 在那个环境下最优。这个表也说明了一个问题网上的经验参数再对也要自己动手跑一遍因为你机器的 CPU、磁盘、数据库配置都不一样。4. 常见问题与排查技巧实录这部分就是我这几天踩坑踩出来的实战经验了。很多问题你不跑到一定数据量根本碰不到碰到了网上也很少有清晰解释我把自己排查的过程和思路完整写下来。4.1 数据库连接被耗尽程序卡死这是个非常典型的并发问题。现象是程序启动后跑了几分钟突然整体停摆日志里全是Connection is not available, request timed out。排查下来发现线程池的线程数设了 20连接池最大连接数却只配了 10而且每个线程在插入前还会查一次源表等于每个线程同时要占用 2 个连接。20 个线程就有 40 个连接需求连接池只有 10必然大量等待。解决思路也很简单让线程数、连接池大小、数据库实际负载形成一个稳定的三角关系。我当时把连接池调大到 20线程数还是 16同时把查询源表和插入目标表拆开不在同一个事务里持有源库连接这样单个线程的连接占用就控制在 1 个以内了。如果你的数据库服务器连接数本身上限不高那就得反过来调小线程数宁让 CPU 闲着也别让连接池爆掉。4.2 InnoDB 死锁锁等待超时第二个大坑是死锁。当时我用 16 个线程往一张有多个二级索引的表里插数据日志里开始出现Deadlock found when trying to get lock。这个问题的根源是 InnoDB 的加锁顺序问题多个线程同时插入不同的主键值但二级索引的更新顺序却不是完全一致的A 线程先锁了 idx_a 再锁 idx_bB 线程先锁了 idx_b 再锁 idx_a两边互相等就死锁了。排查死锁的标准动作是执行SHOW ENGINE INNODB STATUS里面会有最近一次死锁的详细信息能看到具体是哪个事务、持有哪些锁、等待哪个锁。我当时处理方案有三步第一步降低并发度从 16 降到 8死锁频率立即下降。第二步在所有分片任务里强制按主键排序后批量插入让各个线程的加锁顺序尽量一致。第三步对死锁异常做重试捕获异常后重试该批次最多 3 次。如果连重试都反复失败就要检查是不是表结构有问题比如过多的唯一索引在并发插入时冲突概率特别高那就需要先去掉部分索引等数据导完再统一重建。这个方法在数据迁移里非常常用。4.3 事务日志和撤销段膨胀磁盘飙红批量插入这种场景每批次 2000 条每个事务要处理 2000 行的 undo log。并发 16 个线程时同时活跃的事务有 16 个redo log 的写入和刷盘压力非常大。如果你的 MySQL 没有调优过innodb_log_file_size可能只有 48M 或者 100M几百个事务跑下来日志切换频繁性能会严重下降。我当时是把 innodb 日志文件调到 1G同时把innodb_flush_log_at_trx_commit在导入场景临时改成了 0。这个值改成 0 意味着每秒钟才刷一次日志事务提交的持久性会下降但导入速度能提升非常明显。注意这只是导入时的临时设置导入完成后一定要改回默认值 1否则数据库崩溃会丢最近一秒的事务生产环境绝对不能这么干。4.4 数据读取出错某个分片反复失败多线程还有个问题某个分片的数据本身有问题比如某条记录字段长度超了或者格式不对导致这个线程每次都失败影响整体进度。我当时排查的方式是在每个分片任务里做两层异常捕获第一层是整个 batch 级别的失败后把 batchSize 减半继续按更小的批次重试第二层是当 batchSize 已经缩小到 100 还失败就逐条插入定位具体是哪条数据有问题。这套机制看起来代码量不大但在几千万数据里排查脏数据的时候真的救命。不然你拿到一条堆栈信息根本不知道是 5000 万条里的哪一条出了幺蛾子。4.5 内存溢出任务队列堆积前面提到过如果提交任务的速度大于消费速度任务队列会无限增长。我当时就遇到过4 核 8G 的机器堆内存给了 4G一个失控的任务队列直接把堆撑爆了Full GC 频繁整个服务卡死。排查方式很简单先看线程池里的阻塞队列大小打印队列剩余容量。我当时没控制队列上限所以问题暴露得很直接。解决方案是用有界队列并且把拒绝策略设置为CallerRunsPolicy。这样一来当线程处理不过来的时候提交任务的线程自己也会变成工人天然形成了一层反向压力既不会 OOM也不会丢数据。5. 性能调优与 MySQL 侧配合多线程插入方案要发挥到极致不只是应用层改参数数据库侧配合也特别重要。5.1 关键参数的推荐值当时优化的最终形态我整理成下面这张表大家可以按照自己的实际环境再做微调。配置项推荐值说明线程池核心线程数2 倍 CPU 核数IO 密集型任务2 倍比较保险连接池最大连接数与线程数一致或略大于一定不要小于线程数JDBC 批量参数rewriteBatchedStatementstrue必开否则批量是假的单批次行数1000 到 3000根据字段数和行大小调整innodb_log_file_size至少 1G避免日志切换成为瓶颈innodb_flush_log_at_trx_commit导入时临时设 0导入完成后必须改回默认值max_allowed_packet至少 64M避免大批量 SQL 被拒二级索引导入前可临时删除导入完成后重建5.2 为什么索引在多线程插入时这么重要索引是写入性能的天敌这句话在并发场景下尤其成立。每插入一条数据InnoDB 需要同步更新主键索引和所有二级索引。二级索引多一个写入的 IO 和锁开销就多一截。尤其实在多线程并发插入同一条数据附近的主键区间索引页的竞争会变得特别激烈。我后来在导入目标表之前先把非必要的二级索引和触发器全部停掉导入完成后再一条命令重建索引。整个过程下来导入时间直接砍了一半。当然这个操作只适合离线迁移场景如果是线上实时写入业务动索引要非常谨慎尽量在流量低峰做。5.3 最终稳定状态长什么样经过上面所有调整我的多线程导入环境最终稳定在16 线程、16 连接、单批次 2000 条、rewriteBatchedStatementstrue、innodb_log_file_size 1G、offline 导索引。在这种状态下1000 万条数据的插入时间在 50 秒左右CPU 利用率稳定在 70% 到 80%没有死锁、没有连接池等待、没有 OOM。如果是比这更极端的数据量比如 1 亿条可以用同样的方案扩展成分批执行先把数据源分成多个大文件再对每个文件做一次多线程导入。整体思路完全一致只是外面再套了一层循环。6. 几个我之前忽略、但后来觉得特别重要的细节最后再补充几个实操中特别容易被人忽略的细节这些细节都极小但影响极大。第一件事如果你的源表和目标表不在同一个数据库实例上不要在应用层用同一个数据源来查和写。我当时图省事所有线程共用同一个数据源结果连接池资源在读和写两个方向上互相抢占性能反而下降。正确的做法是拆两个数据源一个给读取一个给写入各自独立连接池互不挤兑。第二件事程序跑完一定要主动executor.shutdown()然后等待线程池优雅关闭。我知道这听起来像废话但真有人在生产环境跑完任务未关闭线程池导致应用进程一直不退、连接不释放最后数据库连接池被占满其他业务全部连不上库。排查这个问题的时候我一度怀疑是数据库出问题了后来才发现是应用线程池没有释放。这个坑希望你们千万不要踩。第三件事尽量为每个批次的数据加上一个批次号batch_id字段。有了这个字段你就可以随时通过一条简单的 SQL 查出来哪些数据已经插入成功哪些还是缺失状态配合失败重跑机制排查问题的时候会比大海捞针容易得多。这个思路在数据迁移、日志入库、同步任务里都非常通用。多线程插入数据库核心不是堆线程而是理解并发环境下面临的三大矛盾连接池有限的资源、数据库锁竞争、日志与 IO 的吞吐。把这三个矛盾都理顺了你的方案自然就快了。我后来在另一个更复杂的项目里继续沿用了这套设计思路只是把分片逻辑从按 ID 改成了按业务维度拆但底层的线程池管理、批量参数、事务边界控制、失败重试机制基本上没有动过。这套方案的价值就在于它不依赖于某一个具体的表结构而是一套可以复用的方法论希望能帮到正在跟大批量数据入库搏斗的朋友们。