10 亿数据如何快速插入 MySQL

适用范围与前提

本文面向 MySQL 8.x、InnoDB 引擎的大批量数据导入场景，以"新表离线导入 10 亿行"为主线。已有生产表追加数据、数据库迁移和程序持续写入需要分别处理，不能把新表离线导入的优化直接照搬到生产大表在线追加。准备代表性样本数据、目标表 DDL、服务器 CPU/内存/磁盘规格及预期完成窗口；以下为待环境验证的方案，不是具体服务器的性能承诺。

一、先选对导入方式

LOAD DATA 专门用于高速加载文本数据；MySQL Shell 则在此基础上提供并行导入能力。util.loadDump() 面向 Shell 生成的转储目录，支持记录进度和恢复未完成的加载。LOAD DATA 文档 说明语法与约束。

二、10 亿行要多久？先反推吞吐要求

下面只是算术换算，不是对服务器性能的承诺：

这些时间还没有包含数据导出、清洗、传输、后建索引、校验和副本追平。例如，要求 1 小时内完成数据加载，就意味着平均至少需要 1,000,000,000 ÷ 3,600 ≈ 277,778 行/秒。压测达不到这个速度，就需要调整窗口、数据结构或硬件/实例布局，不能只靠继续增加线程。

容量也要先计算。假设每行原始数据平均 500 字节，那么原始数据本身就是约 500 GB，还没包含 InnoDB 存储开销、索引和日志。尤其是后建索引，部分 DDL 操作还需要大量临时排序空间，不能只保证"表数据能放下"。InnoDB 索引类型 说明聚簇索引与二级索引的存储关系。

建议先用代表性的千万级样本测试完整流程，并观察持续写入阶段，不要只根据最初几分钟的速度推算。

三、推荐落地方案：新表 + 文件分片 + 并行导入

1. 建表时保留主键，延后非必要二级索引

CREATE DATABASE IF NOT EXISTS bulk_import DEFAULT CHARACTER SET utf8mb4;

CREATE TABLE bulk_import.events_new (
  id         BIGINT UNSIGNED NOT NULL,
  user_id    BIGINT UNSIGNED NOT NULL,
  event_time DATETIME        NOT NULL,
  content    VARCHAR(500)    DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这里假设 id 由源数据提供，并且唯一、稳定。InnoDB 按聚簇索引组织数据，二级索引记录还会包含主键。因此，在业务语义允许的情况下，紧凑的主键和按主键排序的输入值得优先考虑。不要为了少维护一个索引而先不建主键，等 10 亿行导完再补。对于独立的新表，可以把普通查询索引延后创建，利用 InnoDB 的排序建索引机制。

2. 把数据准备成规范、可重跑的分片

建议的文件组织方式：

/data/import/
├── part-000001.tsv
├── part-000002.tsv
├── part-000003.tsv
└── manifest.json

压测起点：每片约 50～200 MB，先使用 4 个导入连接。文件约定为 UTF-8 编码、TAB 分列、LF 分行、反斜杠转义、无表头、NULL 以 \N 表示。字段内的反斜杠、制表符、换行符需要按 MySQL 文本格式正确转义。CSV 中可能有带引号的多行字段，所以不能不检查格式就用 `split -l` 拆文件。更稳妥的设计是在数据导出阶段按完整记录生成分片。

3. 文件在数据库服务器上：使用 LOAD DATA INFILE

先查看允许读取文件的位置：

SHOW VARIABLES LIKE 'secure_file_priv';
SHOW VARIABLES LIKE 'sql_mode';

假设允许目录为 /var/lib/mysql-files/，并且会话使用严格 SQL 模式：

LOAD DATA INFILE '/var/lib/mysql-files/part-000001.tsv'
INTO TABLE bulk_import.events_new
CHARACTER SET utf8mb4
FIELDS TERMINATED BY '\t' ESCAPED BY '\\'
LINES  TERMINATED BY '\n'
(id, user_id, event_time, content);

SHOW WARNINGS LIMIT 20;

非 LOCAL 模式需要相应的 FILE 权限，文件位置受 secure_file_priv 限制，并且必须能被 MySQL 服务进程读取。Docker/Kubernetes 环境中，这里应是 MySQL 容器内可见的路径。建议以分片为提交和恢复单位，不要把 10 亿行、几千个文件包进一个大事务。

4. 希望直接并行加载：使用 MySQL Shell

先由管理员记录 local_infile 原值并在允许的导入窗口开启：

SHOW GLOBAL VARIABLES LIKE 'local_infile';
SET GLOBAL local_infile = ON;

MySQL Shell 的 util.importTable() 使用 LOAD DATA LOCAL INFILE，因此需要目标服务器允许 local_infile，并使用经典 MySQL 协议连接。MySQL Shell 并行导入文档 说明参数与约束。

登录 MySQL Shell 后在 JavaScript 模式执行：

util.importTable(
  ["/data/import/part-*.tsv"],
  {
    schema:       "bulk_import",
    table:        "events_new",
    columns:      ["id", "user_id", "event_time", "content"],
    dialect:      "default",
    characterSet: "utf8mb4",
    threads:      4,
    showProgress: true
  }
);

这里使用 dialect: "default"，对应 TAB + LF + 反斜杠转义。对于单个未压缩大文件，也可以设置 bytesPerChunk: "64M" 让工具切块；该选项不适用于多文件列表。注意：LOCAL 模式可能把数据解释错误降级为告警，工具默认还会跳过重复键。命令执行成功，不等于所有输入数据都被正确写入。

导入结束后，按照原值恢复 local_infile，不要无条件关闭其他任务正在使用的配置。

5. 并发逐级测试，数据完成后补索引

建议比较 2 → 4 → 8 → 必要时再增加连接。记录每档并发的有效行数/秒、磁盘延迟、错误率和业务影响。停止加并发的条件是：吞吐不再提升，但延迟、等待或错误明显增加。尽量让不同任务处理不重叠的主键范围，并让分片内部按主键排序。官方建议批量数据按主键顺序加载。

数据校验通过后，再创建真正需要的查询索引：

ALTER TABLE bulk_import.events_new
  ADD INDEX idx_user_time  (user_id, event_time),
  ADD INDEX idx_event_time (event_time);

ANALYZE TABLE bulk_import.events_new;

索引应由查询模式决定，上面只是示例。后建索引的耗时和临时磁盘需求都必须计入完整导入窗口。

四、MySQL 参数怎么调？

优先优化批次、索引和数据布局，再调整参数。不要一上来就关闭可靠性保护。例如，仅针对 MySQL 8.0.30+/8.4、MySQL 独占 64 GiB 内存的测试环境，可以作为压测起点：

[mysqld]
innodb_buffer_pool_size        = 40G
innodb_redo_log_capacity       = 8G
innodb_log_buffer_size         = 128M
innodb_flush_log_at_trx_commit = 1
sync_binlog                    = 1

innodb_redo_log_capacity 从 MySQL 8.0.30 开始提供，较大容量可以减少部分检查点压力，但不能解决底层磁盘持续写入能力不足。容器环境还必须按实际内存限制规划。InnoDB 参数 列出完整说明。

以下"加速技巧"需要特别谨慎：

ALTER TABLE ... DISABLE KEYS 不是暂停 InnoDB 二级索引维护的办法，不要照搬针对 MyISAM 的导入教程。

五、10 亿行必须设计"失败后怎么继续"

分片清单。为每片记录文件名、文件校验和、预期行数、主键范围、任务状态和已提交结果。文件一旦进入导入流程，不再修改内容。

重跑规则。使用稳定的源主键，明确区分"未开始""已提交""提交结果未知"。自研导入器可以把目标数据和该分片的完成标记放在同一个 InnoDB 事务中提交。

数据验收。建议将"零未解释告警、零未解释跳过记录"设为验收门槛，按分片或主键范围核对行数，再核对关键字段、时间范围、NULL 分布和内容摘要。文件校验和只能证明文件没变，不能单独证明数据库内容正确。

六、两类特殊场景

场景 A：从另一套 MySQL 迁移

优先使用 MySQL Shell 的分片转储和加载。转储工具文档 说明分片输出与一致性选项。

// 连接源实例后执行
util.dumpTables(
  "source_db", ["events"],
  "/data/dump/events",
  { threads: 4, bytesPerChunk: "64M", consistent: true }
);

// 将转储目录传到导入机器，连接目标实例后执行
util.loadDump(
  "/data/dump/events",
  {
    threads:            4,
    deferTableIndexes:  "all",
    progressFile:       "/data/dump/events/load-progress.json",
    showProgress:       true
  }
);

util.loadDump() 加载的是 Shell 转储目录及其元数据，不是任意 CSV，也不是普通 mysqldump 生成的 SQL 文件。

场景 B：Python / Go 程序持续写入

使用多行 INSERT，减少客户端与服务器之间的交互次数：

INSERT INTO events_new (id, user_id, event_time, content)
VALUES (?, ?, ?, ?), (?, ?, ?, ?), (?, ?, ?, ?);

初始设计建议：批次 500～2000 行起测并限制编码后字节数；按批次提交，不做无限累积的大事务；4 个固定 worker 起测；有界队列给上游施加背压；明确回滚后仅对可重试故障做有限重试。批次还受 max_allowed_packet 限制，不能只按"多少行"配置。

已有生产表追加时，保留业务必需索引和日志，通过并发、批次、速率限制保护业务。使用影子表替换原表时，新表必须包含所需旧数据和导入期间的增量。

七、硬件配置建议

以下适用于 MySQL 8.x / InnoDB、单行有效数据约 200～1000 字节、少量普通二级索引、批量文件导入的评估场景。大量 TEXT/BLOB/JSON、宽联合索引、在线高并发业务需要重新核算。

物理核心与云主机 vCPU 不能简单等同，应核对具体实例的核心与线程配置。AWS 实例 CPU 配置 说明实例与 vCPU 的映射关系。

磁盘容量必须按导入期间峰值计算

原始数据 500 GB 的场景，导入期间可能需要数 TB 的磁盘空间：

磁盘需求 = (
  最终表数据与全部索引
  + 峰值日志空间
  + 建索引临时空间
  + 同盘保留的导入文件
  + 同盘其他数据
) ÷ (1 - 预留空闲比例)

建议通过代表性样本实际测量，而不是猜测：

ANALYZE TABLE bulk_import.events_sample;

SELECT TABLE_NAME,
       ROUND(DATA_LENGTH   / 1000000000, 3) AS data_gb,
       ROUND(INDEX_LENGTH  / 1000000000, 3) AS index_gb,
       ROUND((DATA_LENGTH + INDEX_LENGTH) / 1000000000, 3) AS total_gb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'bulk_import'
  AND TABLE_NAME   = 'events_sample';

InnoDB 的 DATA_LENGTH、INDEX_LENGTH 是近似分配空间，TABLE_ROWS 也是估计值。INFORMATION_SCHEMA.TABLES 说明字段含义。

磁盘性能比"上大内存"更重要

在批量写入已经优化的前提下，优先验证存储。MySQL 官方区分了数据文件、日志文件的主要 I/O 特征，并建议根据访问模式使用非机械存储、增加存储设备或分离物理磁盘。

NVMe 标称顺序写入 5 GB/s 不代表 MySQL 能以 5 GB/s 写入业务数据；标称 100 万 IOPS 也不代表每秒能插入 100 万行。对于企业级 SSD，建议核对是否具备硬件掉电保护（PLP）。

CPU 与内存配置

CPU 先从 24～32 个物理核心验证，不要直接堆到 64 核。是否增加核心数应根据瓶颈决定：CPU 持续繁忙而存储有余量时增加计算资源；磁盘延迟持续上升时优先解决存储瓶颈。

MySQL 官方建议把 Buffer Pool 配置为系统内存的约 50%～75%。128 GiB 内存并不意味着只能导入 128 GiB 数据。对于不能全部放进 Buffer Pool 的大表，按主键顺序加载尤其重要。

八、导入时看哪些指标？

可以周期性采集：

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Innodb_rows_inserted',
  'Innodb_os_log_written',
  'Innodb_log_waits',
  'Innodb_buffer_pool_wait_free',
  'Threads_running'
);

SHOW ENGINE INNODB STATUS\G

Innodb_log_waits 增长表示日志缓冲区相关等待，Innodb_buffer_pool_wait_free 增长表示等待可用缓冲页。计数器应观察时间区间内的增量；实例级插入计数还可能包含其他业务。服务器状态变量 给出完整定义。

除此之外，建议把磁盘延迟与剩余空间、CPU、内存、复制延迟、业务接口延迟以及导入器记录的已提交有效行数/秒放在同一个监控视图中。调优目标是整体可交付速度，而不是某一时刻的客户端发送速度。

默认推荐路线总结

新表保留主键 → 数据按范围分片 → 用 LOAD DATA / MySQL Shell 从 4 路并发开始压测 → 校验后补二级索引 → 保留 Redo 和必要的 binlog。先把这条流程做稳，再讨论是否需要牺牲可靠性或增加实例。

补充 MySQL 版本、SHOW CREATE TABLE、服务器 CPU/内存/磁盘、数据来源，以及是否正在承载业务和期望完成窗口，才能把方案收敛到具体的分片大小、并发数和参数配置。

参考资料

• MySQL 8.4：优化 InnoDB 批量数据加载
• MySQL 8.4：LOAD DATA 语法与约束
• MySQL Shell 8.4：并行表导入工具
• MySQL Shell 8.4：转储加载工具
• MySQL 8.4：InnoDB 磁盘 I/O 优化
• MySQL 8.4：InnoDB 参数
• MySQL 8.4：INSERT 优化