开发者日记

外观

10 万行导入实测：batch 重写与 LOAD DATA 选型 ​

问题背景：10 万行导入为什么会拖垮数据库 ​

业务系统中最常见的数据写入场景之一，是一次性导入 10 万行订单、用户标签或日志明细。很多应用最初写成循环单条 INSERT，在测试环境几千行时没有明显问题；到了生产环境，随着二级索引增多、触发器存在、主从复制开启，导入时间可能从几秒膨胀到几分钟，甚至引发长事务、行锁等待、undo log 膨胀和主从延迟。

批量导入优化不是简单把 autocommit 关掉，而是要同时处理四件事：

  1. 网络往返次数：每条 SQL 一次解析和网络交互，10 万次请求的固定成本极高。
  2. 事务粒度：一个事务提交 10 万行，可能导致 undo 过大；每行提交一次，又会产生大量 redo/binlog flush。
  3. 索引维护成本：聚簇索引要按主键组织页面，二级索引越多，每批写入的随机维护成本越高。
  4. 数据解析方式：普通 INSERT 走 SQL 解析、优化、执行链路；LOAD DATA 是更底层的批量读文件接口，适合大规模格式化导入。

下面先建立统一测试表，再对比三种典型写法。

方案对比：逐条 INSERT、batch 重写、LOAD DATA ​

建立可复现的测试表 ​

sql
-- 建立订单导入测试表
CREATE TABLE IF NOT EXISTS t_order_import (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  order_no VARCHAR(32) NOT NULL COMMENT '订单号',
  user_id BIGINT UNSIGNED NOT NULL COMMENT '用户 ID',
  amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '订单金额',
  status TINYINT NOT NULL DEFAULT 0 COMMENT '状态：0待支付 1已支付 2取消',
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_no (order_no),
  KEY idx_user_created (user_id, created_at),
  KEY idx_status_created (status, created_at)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci
COMMENT='批量导入测试表';

这张表包含一个唯一索引和两个普通二级索引，比较接近真实业务。若表上还有外键、触发器，导入速度还会继续下降。

三种写入路径的差异 ​

逐条 INSERT10 万次往返batch 多值重写每批 500 到 2000 行LOAD DATA文件流式加载慢、锁碎仅适合少量数据改造成本低业务系统首选吞吐最高适合文件导入

经验上，普通机械盘或虚拟化磁盘环境中，逐条插入可能只有数千行/分钟；将 SQL 重写为每批 1000 行的多值插入，通常能提升数倍到十几倍；规整 CSV 文件使用 LOAD DATA，在无复杂冲突处理时还能继续提升，但它对文件格式、权限、安全和错误数据定位有额外要求。

选型可以按这个判断：

  • 数据来自应用对象、字段需要业务转换、存在复杂冲突逻辑：优先使用 batch 多值插入。
  • 数据已经是服务端可访问的规整文本文件，追求最大吞吐：优先使用 LOAD DATA。
  • 只是几十、几百行配置数据：没必要引入复杂批处理，普通插入即可。

实操步骤一：把单条 INSERT 重写为 batch ​

反例：循环单条提交 ​

sql
-- 应用循环中反复执行下面语句，每次都提交一次
INSERT INTO t_order_import(order_no, user_id, amount, status, created_at)
VALUES ('NO20260101000001', 10001, 99.50, 1, '2026-01-01 10:00:00');

这种写法的问题不是单条 SQL 执行计划差，INSERT 本身通常没有复杂优化器选择；真正的问题是 10 万次解析、网络往返、事务提交和刷盘。

推荐：固定大小多值插入 ​

sql
-- 一次插入多行，批大小建议从 500 或 1000 开始压测
INSERT INTO t_order_import(order_no, user_id, amount, status, created_at)
VALUES
  ('NO20260101000001', 10001, 99.50, 1, '2026-01-01 10:00:00'),
  ('NO20260101000002', 10002, 120.00, 0, '2026-01-01 10:00:01'),
  ('NO20260101000003', 10003, 45.80, 1, '2026-01-01 10:00:02');

应用伪代码如下，重点是按批提交事务，而不是把 10 万行放进一个事务：

sql
-- 伪代码对应的事务边界
START TRANSACTION;
-- 执行一个 1000 行的多值 INSERT
INSERT INTO t_order_import(order_no, user_id, amount, status, created_at)
VALUES
  /* 应用拼接 1000 行，字段值必须使用参数绑定 */
  ('NO20260101000001', 10001, 99.50, 1, '2026-01-01 10:00:00');
COMMIT;

批大小不是越大越好。过小，网络与提交成本明显；过大，单条 SQL 长度可能超过 max_allowed_packet，应用内存上涨，失败重试代价也变大。MySQL 8.0 默认 max_allowed_packet 为 64MB，但生产中不应把 SQL 拼到极限。较稳妥的起点是：

  • 字段少、行宽小：1000 到 2000 行/批；
  • 字段多、包含长文本：200 到 500 行/批；
  • 每批事务控制在 100ms 到 2s 内，结合压测调整。

唯一键冲突如何处理 ​

如果导入数据可能重复，可以使用以下写法：

sql
-- 重复订单号时更新可变字段，避免整批报错
INSERT INTO t_order_import(order_no, user_id, amount, status, created_at)
VALUES
  ('NO20260101000001', 10001, 99.50, 1, '2026-01-01 10:00:00')
ON DUPLICATE KEY UPDATE
  amount = VALUES(amount),
  status = VALUES(status);

需要注意，ON DUPLICATE KEY UPDATE 会触发唯一键检查，冲突行还可能产生额外锁。若导入文件中重复数据很多，先在外部去重，通常比依赖数据库逐条冲突处理更快。

实操步骤二：使用 LOAD DATA 导入 CSV ​

准备数据文件 ​

/tmp/order_import.csv 内容示例：

text
NO20260101000001,10001,99.50,1,2026-01-01 10:00:00
NO20260101000002,10002,120.00,0,2026-01-01 10:00:01
NO20260101000003,10003,45.80,1,2026-01-01 10:00:02

本地文件导入语句 ​

sql
-- MySQL 服务端读取本地文件时，需要 FILE 权限以及 secure_file_priv 对应目录
LOAD DATA INFILE '/tmp/order_import.csv'
INTO TABLE t_order_import
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(order_no, user_id, amount, status, created_at);

如果文件在客户端机器上，可使用：

sql
-- 客户端本地文件需要开启 local_infile，并且客户端连接允许 LOCAL INFILE
LOAD DATA LOCAL INFILE '/tmp/order_import.csv'
INTO TABLE t_order_import
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
  OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(order_no, user_id, amount, status, created_at);

LOAD DATA 还支持导入前做表达式转换。例如源文件时间为空，想写入默认时间：

sql
-- 对输入字段做简单转换，空状态转为 0
LOAD DATA INFILE '/tmp/order_import.csv'
INTO TABLE t_order_import
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(order_no, user_id, amount, @status_value, created_at)
SET status = NULLIF(@status_value, '') + 0;

不过，转换表达式越复杂，LOAD DATA 的优势越容易被抵消。复杂清洗建议在 ETL 层完成，数据库只接收干净、规整的数据。

事务粒度与索引策略 ​

事务不要按请求自动提交，也不要无限包大 ​

导入前可以确认事务提交方式：

sql
-- 查看自动提交；应用连接池通常默认 ON
SHOW VARIABLES LIKE 'autocommit';

-- 导入期间临时关闭当前会话自动提交，由应用按批 COMMIT
SET autocommit = 0;

如果使用 LOAD DATA，一条语句本身通常就是一个较大的事务。10 万行大多可以接受；若文件达到数百万行，建议先拆成多个文件，例如每个 10 万到 50 万行，分文件提交。这样失败后只需要重放当前分片，也能降低回滚段和复制延迟压力。

索引是否应该先删除 ​

很多文章会建议导入前删除二级索引，导入后再重建。这个建议要分场景：

sql
-- 导入完成后重建二级索引，可能减少大量随机维护
ALTER TABLE t_order_import ADD KEY idx_user_created (user_id, created_at);
ALTER TABLE t_order_import ADD KEY idx_status_created (status, created_at);
  • 空表或低并发离线导入：先导入数据，再统一建二级索引，通常更快。
  • 表很大但只导入少量增量：删除并重建索引代价过高，不建议。
  • 生产在线表：删索引会影响查询，不能只看导入速度。
  • 唯一索引：不能为了速度随意删除，否则重复数据可能进入表中。

导入期间还可以临时调低持久性相关设置，但这属于离线初始化或可重跑任务的手段，生产核心数据不要为了速度牺牲安全性。

导入后的验证 SQL ​

不要只看程序返回成功，应验证行数、重复数据和异常值：

sql
-- 验证总行数
SELECT COUNT(*) AS total_count FROM t_order_import;

-- 检查订单号是否重复
SELECT order_no, COUNT(*) AS cnt
FROM t_order_import
GROUP BY order_no
HAVING COUNT(*) > 1
LIMIT 20;

-- 检查金额和状态异常
SELECT id, order_no, amount, status
FROM t_order_import
WHERE amount < 0 OR status NOT IN (0, 1, 2)
LIMIT 20;

-- 确认二级索引可被查询使用
EXPLAIN
SELECT id, order_no, amount
FROM t_order_import
WHERE user_id = 10001
ORDER BY created_at DESC
LIMIT 20;

如果 EXPLAIN 中没有使用预期的 idx_user_created，不一定是索引失效，也可能是优化器认为小表全表扫描成本更低。应在数据量达到预期后再判断。

避坑总结：生产批量导入的具体注意事项 ​

  1. 不要把 10 万行拼成一个巨型 INSERT。常见错误是应用启动后内存飙升，随后收到 Packet for query is too large 或 MySQL 连接断开。修正方法是按行数和字节数双重切分，例如每 1000 行且 SQL 不超过 4MB 到 8MB，并使用参数绑定，不能手工拼接用户输入。
  2. 不要一个事务跑完全程，也不要每行提交一次。前者的现象是 undo 使用量上涨、回耗时长、锁持有时间久；后者的现象是导入很慢、redo/binlog 刷盘频繁。正确做法是按批 START TRANSACTION、执行多值插入、COMMIT，并记录已成功批次，保证任务可断点重跑。
  3. LOAD DATA 失败先查文件路径和换行，不要怀疑 SQL 优化器。典型报错包括 ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option、字段错位、Windows 的 \r\n 导致最后一列带回车。修正方法是把文件放到 secure_file_priv 指定目录，或使用 LOAD DATA LOCAL INFILE，并显式声明 LINES TERMINATED BY '\r\n'。
  4. 唯一索引批量冲突不要盲目忽略。使用 INSERT IGNORE 可能把非重复原因的错误也降级成警告，例如字段截断、非空约束异常。更稳妥的方式是先清洗去重，再用 ON DUPLICATE KEY UPDATE 明确更新哪些列，并通过 SHOW WARNINGS 检查异常。
  5. 不要在业务高峰期对在线大表删索引、重建索引。现象是导入看似变快，但查询出现慢查询、连接池耗尽。离线初始化可先导入后建索引；在线增量导入应保留必要索引，使用低峰、限流、分批提交，并观察主从延迟。
  6. 不要只测无索引空表。空表导入成绩不能代表生产。至少要在相同字符集、相近行宽、相同二级索引和相近硬件下测试 1 万、10 万、100 万三档数据，记录每批耗时、失败重试成本、主从延迟和磁盘 IO，再决定 batch 重写还是 LOAD DATA。

结论很明确：10 万行规模下，应用系统最稳妥的默认方案是 500 到 2000 行一批的多值 INSERT 加显式事务粒度控制；当数据天然来自 CSV、清洗已经完成、部署权限允许时，LOAD DATA 是更高吞吐的选择。真正决定成败的，往往不是某一条 SQL 的写法，而是批大小、事务边界、索引维护和失败重跑机制是否一起设计。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

本文来自开发者日记（devdiary.cn）· 记录开发者实战笔记与免费在线工具箱

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。