十亿数据快速插入MySQL：从参数调优到分库分表的全链路方案

MySQL批量插入LOAD DATA

于 2026-10-01 06:01:46 修改

·本内容遵循CC 4.0 BY-SA版权协议

十亿数据快速插入 MySQL，这个题目放在任何大厂后端面试里都分量很重。说实话，大多数候选人第一反应就是“分批 INSERT”，但如果你真的只聊到这一层，基本等于没答。真正有经验的工程师拿到这道题，脑子里冒出来的不是 SQL 语句怎么写，而是一整套链路：存储引擎、参数、索引策略、并发模型、数据分片、校验兜底。这篇文章我就顺着面试官的视角，把这套链路一层层拆开，给出一套可以直接落地的方案。

1. 问题拆解：10 亿数据插入 MySQL 的瓶颈到底在哪

先别急着写代码，先把瓶颈看明白。10 亿行数据如果一次一次地逐行 INSERT，哪怕单行只要 1 毫秒，也要 11 天半，这还没算上网络往返和日志落盘。十亿级数据什么概念？按一条记录平均 200 字节算，裸数据 20GB，加上索引、日志、临时文件，整个导入期间对磁盘、内存、CPU 的压力都是持续的。这不是普通业务写入，这是一个数据工程场景，思路必须切换成“批处理思维”。

1.1 为什么逐行 INSERT 会导致灾难性表现

每一行 INSERT 在 InnoDB 里不是简单加一条记录，它至少要经历这么几步：

解析 SQL，生成执行计划；
B+ 树定位目标页，如果页满了还要触发页分裂；
更新聚簇索引，产生 redo log；
更新二级索引（如果表上有的话）；
写 undo log，用于事务回滚和 MVCC；
提交事务时把 binlog 和 redo log 落盘。

单条执行时，以上这些开销被固定成本摊得非常薄。网络来回一次、日志 fsync 一次、事务提交一次，所有资源都在空转，真正用来写数据的比例不到 5%。所以逐行插入这种方案在十亿级规模面前可以直接被判死刑。

1.2 更容易被忽略的隐藏开销：日志刷盘与页分裂

很多人以为把 SQL 拼成大 batch 就完事了，但实测中还有两个隐形杀手。

第一个是事务提交时的刷盘。如果保持默认的 innodb_flush_log_at_trx_commit=1，每个事务提交都要把 redo log 刷到磁盘。你就算一次事务包 1 万条，刷盘次数是少了，但单次刷盘的数据量上去了，磁盘 IO 依然会被顶满。第二个是页分裂。如果数据行的主键不是递增的，比如使用 UUID 或者业务随机串做主键，新数据要插入的位置不固定，InnoDB 需要频繁做页分裂和页重组，这会造成随机写放大，性能直接掉一个量级。所以在设计导入方案之前，必须先确认主键的生成方式，这通常是被忽略的第一道坎。

2. 调参数的正确姿势：别急着上框架，先把 MySQL 自身压榨干净

我见过不少团队，数据量一大就上消息队列、上分布式任务，结果回头一看，MySQL 侧连最基本的参数都没调。十亿级插入没必要一上来就引入重型组件，先把 MySQL 自身的写链路调到最极限，往往能省掉一大半的架构成本。

2.1 事务批量大小与自动提交的取舍

最基础但最关键的一步：关闭自动提交，用一个事务包住尽可能多的 INSERT。可以把 autocommit=0，然后分批提交。批量大小不是越大越好。我做过一组测试，在机械硬盘和 SSD 上结论不同，但经验值是单事务 5000 到 20000 行之间表现最佳。太大有两个坏处：

undo log 暴涨，回滚段空间吃紧；
事务持续时间过长，历史列表（history list）过长，purge 线程来不及清理，导致 undo 膨胀。

一个常用的公式是，控制单个事务的redo log 产出在 1GB 以内。可以通过在事务完成后查看 SHOW ENGINE INNODB STATUS 确认最终刷盘量，据此反过来调 batch 大小。

2.2 三个关键参数：innodb_flush_log_at_trx_commit、sync_binlog、innodb_buffer_pool_size

对于批量导入场景，我会把参数这样调：

参数	默认值	导入期建议值	说明
`innodb_flush_log_at_trx_commit`	1	0 或 2	0 每秒刷一次 redo，2 每次提交只写 OS cache，不强制 fsync
`sync_binlog`	1	0 或 100	减少 binlog fsync 次数，前提是能接受导入期间主库崩溃丢少量 binlog
`innodb_buffer_pool_size`	128MB	物理内存 60%-70%	让尽可能多的数据页留在内存
`innodb_doublewrite`	ON	OFF 可选	批量导入时对崩溃安全要求没那么高，可以关掉
`max_allowed_packet`	64MB	512MB 以上	防止大 batch 被拒

这里必须强调，导入结束后一定要把参数改回来。这不是让你在生产环境长期裸奔，而是“导入窗口期”的临时优化。如果业务团队不能接受任何数据丢失风险，那就至少把 innodb_flush_log_at_trx_commit 维持在 1，只调其他参数。

2.3 插入缓冲区真的能拯救随机插入吗

遇到随机主键时，有人会把 innodb_change_buffering 调成 all，希望靠 change buffer 把随机插入变成顺序写。这个思路对二级索引有效，对聚簇索引无效，因为聚簇索引本身就是主键顺序存储的，主键随机就意味着聚簇索引永久随机写。所以更彻底的做法是在设计阶段规避：要么主键严格自增，要么用雪花算法生成但保证趋势递增。如果数据本身就带无序主键，我建议导入前先按主键排序，排序成本远低于导入过程中随机写带来的性能损耗。

3. 别死磕 INSERT 了：LOAD DATA INFILE 的实测与前级处理

面试中如果你只说“分批 INSERT”，面试官大概率会追问一句：“那试过 LOAD DATA INFILE 吗？” 这是 MySQL 官方为大数据导入设计的最快通道，本质是绕开 SQL 层的语法解析和优化，直接以文件流的方式进入存储引擎。同样规模的数据量，LOAD DATA 通常比 multi-VALUES INSERT 快 3 到 10 倍。

3.1 使用 LOAD DATA 前必须搞清的文件准备

LOAD DATA 语法支持从本地或者服务端读取文件。需要注意 LOCAL 参数。如果数据文件在应用服务器，需要加 LOCAL 关键词，并且客户端和服务器都要开启 local_infile：

SQL

SET GLOBAL local_infile = 1;

LOAD DATA LOCAL INFILE '/data/user_records.csv'

INTO TABLE user_record

CHARACTER SET utf8mb4

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'

LINES TERMINATED BY '\n'

(id, name, phone, created_at);

文件格式建议直接用 CSV 或者文本分隔符，不要用 Excel 那种带 BOM 头的文件。BOM 会让第一行第一列的字符串带上隐藏字符，导完后你会发现数据整体偏移。另一个常见坑是日期格式。MySQL 的严格模式对 0000-00-00 这类日期直接拒绝，文件里如果存在空日期，要么在 LOAD 之前清洗，要么在语句里用 @var 做转换：

SQL

LOAD DATA LOCAL INFILE '/data/user_records.csv'

INTO TABLE user_record

(id, name, phone, @created_at)

SET created_at = NULLIF(@created_at, '');

3.2 为什么 LOAD DATA 快？以及它和批量 INSERT 的定位差异

LOAD DATA 快的核心原因是它把数据从字符流转成行的时候，复用了内部的协议层缓冲，同时支持多个值一次解析。相比之下，multi-VALUES INSERT 虽然减少了解析次数，但每一条仍然要走完整的 SQL 框架。实测中，用批量 INSERT 能达到 5 万行/秒已经相当不错，而 LOAD DATA 在机械盘上就能轻松做到 20 万行/秒以上，SSD 上到了百万行/秒也不稀奇。

不过 LOAD DATA 不是万能的。它对在线业务有个致命缺陷：导入期间表会加锁，如果是 LOAD DATA LOCAL INFILE，对线上读写的影响依然很大。所以真实业务里 LOAD DATA 更适合冷数据迁移、数仓初始化、离线批处理这类场景。在线增量数据，还是走批量 INSERT 更稳妥，这也是面试里一定要拎清楚的逻辑——不是哪个快就用哪个，而是要看是否匹配场景的可用性要求。

4. 索引与文件顺序：先删索引再导数据这件事到底值不值

很多开发者的习惯是先建表结构，再导数据。但当数据量到亿级时，“先建好索引再导入”是灾难性的决策。每个二级索引在插入时都要单独维护一棵 B+ 树，数据每插一条，索引就得同步更新，等于把一次写入放大成 N 次写入。

4.1 索引后置的黄金规则

正确顺序是：

先把表结构建好，但只保留主键索引，去掉所有二级索引、唯一索引；
导入全部数据；
数据校验完成后，再批量创建二级索引。

为什么这样可以快？因为创建二级索引时，InnoDB 会扫描聚簇索引，把索引列的值提取出来，排序后批量构建 B+ 树。这个过程是顺序的，比边插入边维护的随机写高效得多。而且，一次性建索引还能充分利用 innodb_buffer_pool，减少页换入换出。

4.2 主键顺序对导入速度的支配性影响

聚簇索引本身就决定了表数据的物理存储顺序。如果主键是乱序的，导入过程的每一次插入都可能让 B+ 树发生页分裂，页面缓存反复失效，磁盘随机写飙升。最直接的证据就是导入耗时曲线：主键有序的数据，导入耗时基本是线性的；主键无序的数据，越到后面越慢，耗时曲线呈指数上扬。

即使数据自带乱序主键，也建议在导入前先排序。排序可以在原库做，也可以把数据抽到 Spark 或者本地用分桶排序做完再导出 CSV。有一个经验值供参考：如果数据文件已经按照主键升序排列，导入速度最高可以提升 80% 以上。针对十亿级数据，这笔排序成本完全值得花。

4.3 唯一键冲突与数据清洗前置

除了索引，唯一键也是一大隐患。如果业务上需要 phone 唯一，导入前最好先在数据文件里做去重，而不是依赖 MySQL 的冲突检查。MySQL 检查唯一键冲突时，每次都要走索引探测，批量导入阶段它会成为巨大的性能瓶颈。

去重可以在 SQL 侧通过临时表完成：

SQL

CREATE TABLE temp_unique AS

SELECT MIN(id) AS id

FROM raw_data

GROUP BY phone;

然后把临时表 join 原表，只导入保留的记录。在导入机制之外先做数据治理，好处是让导入管道保持纯粹，速度和解障都更容易控制。

5. 十亿级数据正确的并发拆解思路：单机多管道到分库分表

单条链路跑满之后，下一个问题是：怎么并行。十亿数据即使每秒写入 20 万行，也要跑 5000 多秒，将近一个半小时。如果时间窗口只有 20 分钟，那就必须并行。

5.1 并发的正确姿势不是线程越多越好

多线程并发插入 MySQL 时,有个很容易踩的坑：即使开了 50 个线程，如果数据都往同一张表同一个分区里写，InnoDB 的全局锁竞争和磁盘 IO 会把收益吃掉。单表场景下，实测结论是 4 到 8 个并发连接是甜点区，再往上走，响应时间反而会因为锁等待和上下文切换而上升。

可行的做法是：按主键 ID 范围把 10 亿数据切分成多个独立文件，每个文件由一个线程执行 LOAD DATA 或者批量 INSERT。切分的时候要保证每个文件相互独立，不违反唯一约束。比如主键按用户 ID 取模分片，或者按业务时间窗口分片。

5.2 分库分表：不只是为了扛写入

当单表写入已经到了极限，分库分表是不二选择。这一步要回答的不是“用什么中间件”，而是“按什么维度拆”。

十亿数据按传统说法，单表即使在 SSD 上也会明显感觉到查询压力，分表是迟早的事。分片维度优先考虑查询场景：如果大部分查询都带用户 ID，就按用户 ID 取模分表；如果数据有明显的时间窗口，比如订单或日志，就做按月分表。分片后，每个分片的数据量被控制在千万级甚至更低，插入压力和后续的查询压力一起解决。

分库分表也不是必须用 ShardingSphere 这类中间件。数据导入本质是一次性的，完全可以让应用层直接维护数据源路由。根据分片键计算目标库，然后建立连接池并发写入。少套一层中间件，导入链路的故障点就少一个。

5.3 中间层削峰：什么时候该上消息队列

如果目标不只是“一次性导入 10 亿”，而是“持续地快速写入”，那就得上消息队列。生产端把数据发到队列，消费端批量拉取并写入 MySQL。这是典型的削峰填谷设计，理解它的价值比记住某个具体组件重要得多。

消息队列解决的核心问题不是写入变快，而是写入变得平滑。MySQL 能接受每秒 10 万写入，但生产端如果瞬间来 100 万请求，MySQL 直接被打死。队列把匀速供给变为可能，消费端始终以数据库能承受的速率批量落库。对于十亿级冷数据导入，消息队列是额外开销；但对于热数据持续写入，它就是刚需。

6. 导入完成后的正确校验姿势：别拿 count(*) 糊弄自己

数据导完不等于结束。导入环节任何一步出错，都可能导致数据缺失、重复或错位。校验这一步不是随便 select count(*) 比对一下数量就行了，十个亿的数据还要考虑效率。

6.1 全量行数校验的正确打开方式

直接 SELECT COUNT(*) 在大表上会走全表扫描，即便 InnoDB 有 MVCC 也不能像 MyISAM 那样直接拿 rows 结果，这个操作会扫全表，耗时跟导入本身有得一拼。我的方法是分片计数：按照业务自然分片维度（比如按天、按月、按用户 ID 取模），统计每个分片内的行数，再和目标文件的记录数做汇总对比。这样单次统计的数据量小，可以并行跑，也方便定位丢失发生在哪个分片。

也可以利用 information_schema.tables 里的 table_rows 做粗粒度估算，但它是不精确的，只能用来快速查看量级，不能作为最终结论。

6.2 抽样比对与唯一性检查

数量对上了，还要对内容。大表全量比对不现实，合理的方式是抽检：从源文件随机抽几条记录，在库里按主键查出来，比对关键字段是否一致。抽样比例可以按数据重要程度定，核心账号数据抽 10%，普通日志数据抽 0.1%。

另一个必要检查是唯一性。如果业务要求某个字段唯一，导入后要验证唯一索引是否真的能兜底。建索引之前可以先在临时表查重复：

SQL

SELECT phone, COUNT(*)

FROM user_record

GROUP BY phone

HAVING COUNT(*) > 1

LIMIT 10;

这一步在导入前做更好，导入后做也行，但如果导入前已经去重并建立了唯一索引，这句主要起复核作用。校验通过后，再把导入前的临时参数逐步改回生产值，观察主从延迟、慢查询、CPU 负载都恢复正常，这个导入任务才真正算闭环。

我在实际项目里还保留了一个习惯：导入完成后的第 24 小时，再跑一次按日的行数统计。有些潜在问题（比如数据迁移任务被后面某个定时任务覆盖掉）要过一阵子才会暴露，隔天复核一次，等于给整个流程上了一道保险。

这道题考察的不只是 SQL 写法，而是你有没有遇过到十亿级数据的真实场景，以及在这个场景下如何系统性地拆解问题。从参数调优、LOAD DATA 到索引后置、并发分片、校验复核，每一步单独拿出来都不难，但把它们串成一条完整的链路，才是区分初阶工程师和资深工程师的关键。