前几天接到一个需求要把一批历史数据灌进 MySQL数据量大概在 100 万行左右。我第一反应是“直接写个循环慢慢插呗”——说实话很多做了几年的 PHP 开发也是这么想的。但真的测下来单条 INSERT 循环插入一万条数据就够你喝一壶等跑到 10 万条的时候那个耗时已经没法看了。这篇文章把我从“单条循环插入”到“百万数据几分钟内入库”的完整折腾过程整理出来包括代码、参数、踩过的坑给要处理大批量导入的朋友做个参考。先说结论百万行级别用 PHP 插入 MySQL 完全可以做到“分钟级”但前提是你得把 SQL 拼接、事务提交、MySQL 参数这几个环节同时做对。只改其中任何一个性能都上不去。后面我会把每一步为什么这么做、数据是多少、踩了哪些坑全部讲清楚。1. 为什么单条插入跑不动先把瓶颈搞清楚1.1 一条 INSERT 到底经历了什么你在 PHP 里写for循环每秒能执行几十次 INSERT这已经是不少人的直觉天花板。问题是这条路上每一行插入都要走完整套流程SQL 文本交给 MySQL 解析、优化器生成执行计划、写入 InnoDB 缓冲池、记录 Redo Log、提交事务autocommit 模式下还要把日志落盘。落盘这个词听起来轻飘飘实际就是一次磁盘 fsync机械盘上动辄几毫秒SSD 也要零点几毫秒。还有一层隐藏成本是 PHP 和 MySQL 之间的“来回”。哪怕你用localhost连接每一条 INSERT 都是一次完整的请求-响应往返。1 万条就是 1 万次往返100 万条就是 100 万次。这相当于你每天单独给快递员打一个电话只为了取一个包裹取 100 万个包裹就得打 100 万次电话真正送货的时间反而少得可怜。把这两件事叠加在一起单条循环插入的数据量只要上到几十万耗时就会从“有点慢”变成“完全不可接受”。1.2 三种插入方式的实测对比我在本地用相同的数据做了一组对照测试环境是 PHP 8.1 MySQL 8.0InnoDB100 万行、每行 4 个字段结果差距非常大插入方式耗时说明单条 INSERT 循环每行自动提交约 18 分钟每条语句独立事务日志频繁落盘多值批量 INSERT每批 500 行每 2 万行提交一次约 2 分 10 秒消息往返次数降到千分之一多值批量 INSERT每批 2000 行每 4 万行提交一次约 1 分 30 秒进一步减少解析和提交次数配合关闭唯一约束检查 参数调优后的批量插入约 50 秒需要兼顾数据安全见第 3 节注意这里不是同一台机器上的精确基准但量级关系是稳定的改对方案之后百万数据从“小时级”进到“分钟级”再通过参数调优冲进“1 分钟内”一点都不夸张。2. 方案选型百万数据导入到底该用哪套2.1 批量拼接多值 INSERT 是性价比之王性能提升最直接的办法是把多条单行 INSERT 拼成一条多值 INSERTINSERT INTO test_bulk_insert (name, mobile, status, created_at) VALUES (user_1, 13800000001, 1, 2024-01-01 00:00:00), (user_2, 13800000002, 2, 2024-01-01 00:01:00), (user_3, 13800000003, 0, 2024-01-01 00:02:00);一次拼接 500 行原来要往返 500 次现在只往返 1 次原先要解析 500 条 SQL现在只解析 1 条。这个方案对代码结构改动最小在 PDO 里完全可控也是我最后采用的主方案。拼接的时候要特别注意两个上限。第一个是 MySQL 的max_allowed_packet默认一般是 16MB如果拼接后的整条 SQL 超过这个值MySQL 会直接断开或者说 “packet too large”。第二个是单条预编译语句的参数总数默认上限是 16382 个。500 行 4 个字段也就是 2000 个参数安全如果你贪心拼 1 万行 5 个字段5 万个参数会直接触发参数上限报错。所以批大小既要按行数控制也要按 SQL 字节数控制。2.2 预处理循环为什么效率依旧不高有人会说我用 PDO 预处理prepare()一次然后循环execute()不也挺好比单条拼接快但依然远不如多值批量。原因是预处理只省下了 SQL 解析和计划生成的部分开销但客户端和服务端的往返次数、每行独立提交事务这两个大头一点没省。如果数据量只有几千行预处理循环完全够用代码也干净。但到了百万量级差距就会拉到 5 到 10 倍。我的建议是需要写大量 INSERT 的批处理任务直接上多值批量需要逐行处理且数据量小才用预处理循环。2.3 什么时候才该上 LOAD DATA INFILELOAD DATA INFILE是 MySQL 官方的 CSV 批量导入工具速度比多值 INSERT 又快一个量级我实测百万行 CSV 大概 30 到 40 秒。但它的使用条件不少服务器端需要开启secure_file_priv且要加载的文件必须在允许目录内PHP 进程得有FILE权限。MySQL 8.0 默认禁用了客户端侧的LOAD DATA LOCAL INFILEPHP 这边要通过PDO::MYSQL_ATTR_LOCAL_INFILE true显式开启运维层面还要响应几个回调处理文件内容。另外LOAD DATA INFILE和INSERT INTO ... SELECT一样是从外部数据源灌数据的“终极大招”。如果你的源数据本身就在另一张表里直接INSERT INTO ... SELECT连 PHP 这层都省了。但大部分业务场景下数据是要在 PHP 里清洗、转换、补字段的所以我才把批量多值 INSERT 当作默认首选LOAD DATA保留给“数据已经是干净 CSV、表结构完全对上”的场合。3. 完整实操从零把 100 万条数据灌进 MySQL3.1 环境准备与表结构设计我用的测试表结构如下CREATE TABLE test_bulk_insert ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(64) NOT NULL DEFAULT , mobile VARCHAR(20) NOT NULL DEFAULT , status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, PRIMARY KEY (id), KEY idx_status (status) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;几个设计上的考虑主键用自增保证 InnoDB 插入顺序和物理聚集索引顺序一致避免无序主键导致页分裂status这个二级索引在批量插入时会带来额外开销真正的压测可以临时删掉它灌完数据再加回来。utf8mb4一个字符最多占 4 字节这直接决定了拼出来的 SQL 实际字节数后面估算批大小的时候别忘了。3.2 核心代码实现细节核心思路是三个关键词的组合分批拼接、小事务提交、生成器流水线。先说生成器。如果你一次性在 PHP 数组里生成 100 万行光内存就要吃上百 MB非常难看。我改成生成器逐行产出主循环攒够一批就插入一批内存占用永远只有批大小那么大function generateRows(int $total, string $baseTime): Generator { for ($i 0; $i $total; $i) { yield [ name user_ . $i, mobile 138 . str_pad((string)($i % 100000000), 8, 0, STR_PAD_LEFT), status $i % 5, created_at date(Y-m-d H:i:s, strtotime($baseTime) - $i * 60), ]; } }注意date()在 100 万次循环里其实挺耗 CPU。这里我偷了个懒基准时间固定每行递减 60 秒保证数据分布合理又不会把时间都花在格式化上。实际业务里如果有批量计算的字段尽量在生成阶段用数组缓存计算结果不要在循环里重复算。然后是插入函数和主循环function insertBatch(PDO $pdo, array $rows): int { $cols [name, mobile, status, created_at]; $values []; foreach ($rows as $row) { $values[] ( . $pdo-quote($row[name]) . , . $pdo-quote($row[mobile]) . , . (int)$row[status] . , . $pdo-quote($row[created_at]) . ); } $sql INSERT INTO test_bulk_insert ( . implode(, , $cols) . ) VALUES . implode(, , $values); return $pdo-exec($sql); } $total 1000000; $batchSize 500; $commitEveryRows 20000; $pdo-beginTransaction(); $buffer []; $inserted 0; foreach (generateRows($total, 2024-01-01 00:00:00) as $row) { $buffer[] $row; if (count($buffer) $batchSize) { insertBatch($pdo, $buffer); $buffer []; $inserted $batchSize; if ($inserted % $commitEveryRows 0) { $pdo-commit(); $pdo-beginTransaction(); } } } if ($buffer) { insertBatch($pdo, $buffer); } $pdo-commit();这里quote()是对拼好后的值做转义。因为是批量插入、又用生成器控制内存我选择了直接拼 SQL 的方式。如果你处理的是用户可控输入建议改成带VALUES (?, ?, ?, ?)的预编译批量参数安全性更高。事务提交节奏也要说清楚不是整批 100 万行一个事务也不是每行一个事务。100 万行一个事务会让 Undo 日志非常膨胀而且回滚代价极高每行一个事务又回到了 autocommit 的老路。我实测每 2 万行提交一次是稳定且高效的折衷既控制了长事务风险也没有因为提交次数过多拖慢速度。3.3 MySQL 侧参数调整PHP 代码再好MySQL 参数不对照样慢。我这次调整了以下内容按收益排序参数默认值调整值作用innodb_flush_log_at_trx_commit10提交时不强制刷 Redo Log专治日志落盘瓶颈unique_checks10批量导入时跳过唯一索引约束检查速度明显提升sync_binlog10配合上面的参数减少二进制日志刷盘max_allowed_packet16M64M允许更大的批量 SQL 包这几个参数必须反复强调它们的代价innodb_flush_log_at_trx_commit 0意味着数据库突然宕机时可能丢失最近 1 秒内已提交的事务unique_checks 0意味着如果源数据里真混入了重复唯一键插入阶段不会报错最后会卡在重建索引或校验阶段甚至产生脏数据。所以这套参数只服务于一次性导入任务导完必须立刻恢复默认值。说明一点网上常有人建议ALTER TABLE xxx DISABLE KEYS。这条只对 MyISAM 非唯一索引有效InnoDB 里压根不起作用我一开始也白折腾过。InnoDB 下二次索引没有“禁用”一说要么删掉索引导入完再重建要么就老老实实让它跟着插入。3.4 实测结果与不同批次参数对比我分别跑了不同批次大小的实验数据都是 100 万行每 2 万行提交一次参数已调优批大小行单条 SQL 大约体积总耗时备注100约 12 KB约 90 秒往返次数还是偏多500约 60 KB约 62 秒稳定且内存友好推荐2000约 240 KB约 50 秒速度和体积的甜点区5000约 600 KB约 48 秒收益开始边际递减10000约 1.2 MB约 55 秒单条 SQL 太大解析和网络开销上升500 到 2000 行每批是最舒服的区间。再往上省不了多少时间反而增加超大 SQL 在传输、解析、Binlog 写入上的风险。如果你的行字段特别长我建议按“SQL 总字节数”来切批控制在 1MB 以内最稳。4. 常见问题与排查技巧实录4.1 内存暴涨与 PHP 执行超时这是新手最容易踩的坑。最初我图省事用array_push先攒了 100 万行再分批插入结果memory_limit直接爆掉——PHP 数组本身就是“稀疏哈希表”100 万行关联数组的内存开销远比直觉大动辄三四百 MB。改成生成器之后内存占用稳定在几十 MB 以内。如果你只能拿到一个已经很大的数组至少要定期unset掉已经消费完的块或者用array_slice配合索引切片处理。另一个隐患是 Web 模式的max_execution_time跑批任务一律推荐命令行执行php -d memory_limit256M bulk_insert.php不要在 Nginx/PHP-FPM 下跑百万级插入max_execution_time和网关超时随时会切断任务中断后你还得处理“插了一半怎么办”的烂摊子。4.2 SQL 超过 max_allowed_packet我试过把批大小调到 5 万行结果 MySQL 直接返回Got a packet bigger than max_allowed_packet bytes连接被断开脚本报错。这个报错很迷惑因为 MySQL 5.7 里默认才 4MB8.0 默认 64MB不同环境差异很大。根因是客户端发出的整条 SQL 报文超过了服务器允许的上限。解决方式有两种一是修改max_allowed_packet然后重启 MySQL二是在代码里动态控制批大小。更稳妥的是后者每拼入一行就估算 SQL 字节数接近 1MB 就立刻执行这一批这样无论字段多长都不会踩线。4.3 事务过长、死锁与锁等待百万行塞进一个事务时LOCK WAIT和 Undo 膨胀问题会很突出。我说一个小场景导入任务和线上业务共用一张表脚本事务一直不提交线上写入就会被阻塞监控里全是innodb_lock_wait_timeout超时。所以才有“每 2 万行提交一次”的节奏本质上是为了让锁窗口短一点。另一个并发问题如果同一个表同时有别的线程在写批量插入这种长事务更容易触发死锁。碰上Deadlock found when trying to get lock; try restarting transaction不要慌写一个简单的重试机制捕获40001错误码后重新尝试当前批次即可。4.4 插入慢的通用排查清单如果你把代码改成批量了、参数也调了还是慢按下面顺序逐项排查现象可能原因处理办法每秒插入行数上不去磁盘 I/O 饱和Redo 日志落盘太频繁临时调innodb_flush_log_at_trx_commit0CPU 高但速度低SQL 拼接和date()消耗过多用生成器 预生成时间值带索引的表插入慢二级索引每行都要更新导入前删索引结束后重建经常报参数太多批大小 * 字段数超过 16382控制批大小按参数数量计算插入顺序乱导致页分裂主键不是自增或无序加自增代理主键还有一个容易被忽略的是字符集。PHP 连接串里如果没有显式SET NAMES utf8mb4客户端和服务器按不同字符集解释MySQL 会做隐式转码不仅慢还可能把字段截断。我在初始化 PDO 时加了PDO::MYSQL_ATTR_INIT_COMMAND SET NAMES utf8mb4这个细节也算老手和新手的区别之一。5. 个人体会这套方案在实际项目里的正确打开方式5.1 先确认数据来源再决定方案如果数据本来就是另一张 MySQL 表我建议直接INSERT INTO ... SELECTPHP 都不应该参与搬运如果数据来自 CSV 且表结构完全匹配LOAD DATA INFILE是首选只有数据需要在 PHP 里做清洗、转换、分配逻辑时才需要回到批量多值 INSERT 事务分批这套组合。我这次因为要把线上导出的杂七杂八数据统一成规范格式所以才用 PHP 走了全套批处理。5.2 导入任务一定要做成可重入的这是我在真实项目里吃的最大教训。百万数据导入通常不是跑一次就完事经常会因为数据源更新、字段规则调整而重跑。如果每次都是INSERT第二次跑就会撞上唯一键轻则报错中断重则产生重复数据。我给要导的表加了一个业务层面的batch_id字段导入前先按batch_id删除旧批次再插入新批次或者用INSERT ... ON DUPLICATE KEY UPDATE做幂等。百万级导入配合幂等逻辑才是能交给定时任务反复执行的东西。5.3 关于“亲测成功”的较真我这个标题敢挂着“亲测成功”是因为真的跑通了 100 万行也真的踩过上面每一个坑。但“成功”有个前提你得知道自己测的是什么环境。同样是百万数据本地 MySQL 8.0、SSD、无并发写入和线上机械盘、高负载、有业务写入参数和批大小都要重新校准。我建议初次实践的朋友先拿 10 万行数据把整套流程跑通记录基线时间再放大到 100 万行。中途的任何参数调整都以这个基线做对照这样到底哪个改动有效一目了然。最后分享一个小技巧批量插入完成后记得顺手执行一次ANALYZE TABLE或者OPTIMIZE TABLE。大数据量导入之后表的统计信息往往会失真优化器可能选错索引后续查询突然变慢。我每次导完都会跑一句ANALYZE TABLE test_bulk_insert这个小动作花不了一秒却能省掉接下来很多莫名其妙的排查时间。