先纠正一个普遍存在的认知误区MySQL批量插入不是一个“加分技巧”而是大数据导入场景下的“生存技能”。我见过太多项目前期表结构设计得漂漂亮亮一到灌数据阶段就卡在几万行上动辄跑几个小时最后不得不停下来重构导入逻辑。这篇文章我会把批量插入这件事拆透——从为什么慢、怎么提速、参数怎么调到实操中容易踩的坑全部摊开来讲。先说清楚适用人群需要向MySQL写入大量数据的开发、运维、数据分析师无论你是做ETL同步、初始化种子数据还是处理业务日志入库这篇内容都能直接落地。文中涉及的操作我都用MySQL 8.0验证过5.7版本基本通用个别参数差异我会单独标注。1. 为什么单条INSERT会让导入卡到怀疑人生1.1 慢的真正根源不是SQL执行时间而是“对话成本”很多人以为单条INSERT慢是因为MySQL执行INSERT语句本身耗时这个理解基本是错的。单条INSERT的SQL执行时间通常不到1毫秒但如果你在应用层循环执行一万条INSERT实际消耗的时间可能超过几分钟。差距就在每一次INSERT都要走完整的“客户端 → 服务端”往返链路。这条链路上至少有四层开销第一层是网络传输每发一条SQL都要打包、传输、等待响应即使在同一台机器上走localhostTCP协议栈的处理也有成本第二层是SQL解析MySQL收到文本形式的SQL后要做词法分析、语法分析、生成执行计划这个动作每条语句都会重复执行第三层是事务开销默认autocommit模式下每条INSERT都是独立事务每次都要写redo log、刷binlog、释放锁资源第四层是日志同步尤其当innodb_flush_log_at_trx_commit1时每次事务提交都要强制把日志刷到磁盘。把这四层开销乘上循环次数就是灾难。我在本地虚拟机里测过一组基准数据向一张10个字段的普通表写入10万条记录单条循环INSERT耗时约180秒而多值批量INSERT只需要6秒差距接近30倍。这个数据一点都不夸张网络环境越差、单条SQL越复杂差距越悬殊。1.2 批量插入为什么能快三条路同时缩短批量插入的快本质上是压缩了上面四层开销。拿多值INSERT来说一条INSERT语句携带1000行数据网络往返从1000次降到1次SQL解析从1000次降到1次这是第一个提速点。第二个提速点是事务粒度。批量插入允许你把大量数据包在一个事务里提交次数从1000次降到1次。这意味着磁盘fsync的次数大幅减少在机械硬盘和高延迟存储上效果尤其明显。第三个提速点是MySQL内部的执行优化。InnoDB引擎对一条语句内的多行插入有专门的批量处理路径可以减少B树索引更新的随机IO次数。这一点很多人忽略单条INSERT每插一行都要走一次索引查找和页分裂逻辑而批量插入时索引更新可以合并处理顺序IO的比例明显提升。2. 批量插入的三种主流方案怎么选2.1 多值INSERT最通用、最稳妥的起点多值INSERT就是把多个值组拼在一条语句里格式长这样INSERT INTO user_info (name, age, city) VALUES (张三, 25, 北京), (李四, 30, 上海), (王五, 28, 广州);这种方案的优势是兼容性最好所有客户端、所有驱动、所有MySQL版本都支持不需要额外的文件操作权限。操作上最核心的一个参数是“一次插多少行”。我自己的经验是500到1000行是一个甜点区间。低于100行语句条数太多网络往返压缩不充分高于2000行单条SQL过大一方面max_allowed_packet可能顶不住另一方面事务时间过长会增大锁竞争和回滚风险。有人会问要不要一次性把10万行全拼进去千万不要。一条INSERT涉及的行数过多时InnoDB需要维护的undo log和锁信息会暴涨一旦中间某行违反约束导致整条语句失败回滚代价极高。而且MySQL的binlog默认按事务记录超大事务会导致主从同步延迟飙升。分段批量插入是必须要做的。2.2 事务包裹配合循环更新的“加速器”多值INSERT适合从零写入但实际业务里还有一种高频场景需要循环更新或逐行处理后写入。这种场景下你不能把所有数据一次性拼进一条SQL但又想减少提交次数解决办法就是显式开启事务在事务里循环执行单条INSERT最后统一提交。import pymysql conn pymysql.connect(hostlocalhost, userroot, password123456, databasetest_db) cursor conn.cursor() try: cursor.execute(START TRANSACTION) for i in range(10000): cursor.execute( INSERT INTO user_info (name, age, city) VALUES (%s, %s, %s), (fuser_{i}, i % 60, 北京) ) conn.commit() except Exception as e: conn.rollback() print(f事务回滚: {e}) finally: cursor.close() conn.close()这个方案的提速逻辑和多值INSERT不太一样它没有减少SQL解析次数但把磁盘同步和事务提交从一万次压缩到一次。实测10万条记录开启事务循环插入比autocommit模式快8到12倍。这里有一个关键操作事务不是越大越好通常建议每1万到5万行提交一次。原因是InnoDB的MVCC机制会保留未提交事务的快照数据事务过长会导致undo log膨胀影响后续查询的可见性判断甚至拖垮purge线程。记住批量提交不是“一把梭”而是“分段提交”。2.3 LOAD DATA INFILE官方钦定的“核武器”如果你的数据已经落成文件比如CSV、TSV格式那LOAD DATA INFILE就是最优解。它的执行路径绕开了SQL层的逐行解析直接由存储引擎层批量装载数据速度比多值INSERT还要快一个量级。我处理过一份5GB的CSV数据入库多值INSERT跑了13分钟LOAD DATA只用了不到2分钟。LOAD DATA LOCAL INFILE /tmp/user_data.csv INTO TABLE user_info FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (id, name, age, city);使用时有几个细节必须注意。第一LOCAL关键字允许从客户端所在机器读取文件但需要客户端和服务端都开启local_infile参数MySQL 8.0默认关闭需要手动开启。第二字段顺序和数量必须严格对应多余字段可以通过用户变量丢弃比如SET col NULL的写法。第三文件编码必须是UTF-8否则中文乱码问题会让你排查半天。LOAD DATA还有一个隐藏优势可以配合FIELDS ESCAPED BY处理特殊字符也可以在导入前通过预处理逻辑做简单清洗。如果你的业务逻辑不复杂完全可以把清洗工作放在SQL语句里完成省掉一个处理环节。3. 必须调优的系统参数不调等于白干3.1 写入瓶颈的“三板斧”buffer pool、redo log、binlog批量插入的性能上限不完全由插入方式决定数据库自身的配置参数同样关键。我给所有做数据导入的团队一个排查顺序先看存储引擎配置再看日志策略。首先是innodb_buffer_pool_size。InnoDB的所有数据读写都要经过buffer pool导入过程中索引页和数据页都在这个内存区域里操作。如果buffer pool太小频繁的页换入换出会带来大量额外IO。生产服务器建议设置为物理内存的60%到70%在专用数据库实例上甚至可以更高。举个例子32GB内存的机器可以给20GB16GB内存的机器至少给10GB。导入任务完成后如果想收紧内存占用可以临时调小并重启生效。其次是innodb_flush_log_at_trx_commit。这个参数控制事务提交时redo log的刷盘策略默认值是1含义是每次提交都强制刷盘数据安全性最高但性能最差。导入场景如果允许极少数日志丢失的风险可以临时改成2性能能提升一个档次。这个参数在MySQL 5.6以后的版本支持动态修改不需要重启导入完成后记得改回来。最后是binlog相关配置。如果开启了binlog可以临时把sync_binlog设为0让MySQL不强制每条事务都同步binlog到磁盘。这个操作能明显提升导入速度但代价是断电时可能丢失最近的操作日志。生产环境谨慎使用测试环境可以放心开。3.2 容易踩坑的max_allowed_packet和事务隔离级别max_allowed_packet是很多人批量插入报错的“罪魁祸首”。当你的多值INSERT语句特别大时服务端会直接拒绝并报错“Packet too large”。这个参数有两层服务端的max_allowed_packet和客户端的max_allowed_packet两边的值取较小者生效。建议统一设置为64MB或128MB注意是字节单位。SET GLOBAL max_allowed_packet 134217728;事务隔离级别对批量插入也有影响默认的REPEATABLE READ在并发写场景下容易产生间隙锁和next-key lock导致不必要的锁等待。如果导入过程允许同时读数据可以临时将隔离级别改为READ COMMITTED配合批量插入能减少不少锁冲突。修改语句如下SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;还有一个经常被忽略的参数是innodb_autoinc_lock_mode。MySQL 8.0默认值是2即交错模式适合高并发插入如果你的核心表中存在自增主键且批量插入量极大建议确认这个参数没有被改成0或1。传统模式在批量插入时会持有表级AUTO-INC锁高并发下会拖慢其他插入操作。4. 实战拆解从慢速到高速的完整改造过程4.1 场景设定与初始方案我最近接手了一个会员数据迁移任务需要把一份20万行的老系统会员数据导入新库。表结构大概是id自增主键、user_no唯一索引、mobile唯一索引、name、level、create_time等字段共12列。初始方案是网上最常见的写法——ORM框架里循环save20万条数据跑了整整40分钟还没跑完。我先停下来算了一笔账40分钟意味着平均每条记录耗时12毫秒这个时延主要花在网络往返和事务提交上SQL本身绝对没有这么慢。果断换方案。第一步改造是用多值INSERT每500行拼一条SQL用Python的executemany实现。这里有个小细节pymysql的executemany会自动把参数列表拼接成多值INSERT但内部拼接的SQL大小受max_allowed_packet限制所以批次大小要先测一下我这边测试下来500行一条的包体在200KB左右非常安全。import pymysql import csv conn pymysql.connect(hostlocalhost, userroot, password123456, databasenew_db) cursor conn.cursor() batch [] batch_size 500 count 0 with open(member_old.csv, r, encodingutf-8) as f: reader csv.reader(f) header next(reader) for row in reader: batch.append(row) if len(batch) batch_size: cursor.executemany( INSERT INTO member (user_no, mobile, name, level, create_time) VALUES (%s, %s, %s, %s, %s), batch ) count len(batch) batch.clear() print(f已导入 {count} 条) if batch: cursor.executemany( INSERT INTO member (user_no, mobile, name, level, create_time) VALUES (%s, %s, %s, %s, %s), batch ) conn.commit()这个方案的实测结果是20万条数据耗时约4分钟比循环save提升了10倍。但还有优化空间主要瓶颈已经转移到了事务提交和索引更新上。4.2 二次优化加载速度再翻倍的组合拳第二次优化我做了三件事关闭唯一键检查、调整日志策略、扩大批量到1000行。先处理唯一键的问题。导入数据里如果确认没有重复可以先执行SET unique_checks0让MySQL在插入时跳过唯一索引的重复检查插入完成后再恢复。原理是唯一索引的每次插入都要在辅助索引上做一次查找全表20万行这个查找成本累计起来不容小视。然后是外键检查如果是空表导入且没有复杂的级联约束直接SET foreign_key_checks0。最后把innodb_flush_log_at_trx_commit临时改成2、sync_binlog改成0同时在应用层把每次提交行数调整为1万行。cursor.execute(SET unique_checks0) cursor.execute(SET foreign_key_checks0) cursor.execute(SET SESSION innodb_flush_log_at_trx_commit2)经过这轮调整同样20万行数据耗时降到了约1分20秒。说实话到这一步我已经比较满意了因为数据量本身不大继续挖掘优化的边际收益很低。但如果你处理的是千万级甚至上亿级数据接下来要看的LOAD DATA方案才是正餐。4.3 终极方案当数据量到达千万级别千万级数据的导入我强烈建议走LOAD DATA 分段提交 并行导入的组合路线。分段提交是指你在源文件层面就按逻辑拆成多个小文件比如100万行一个文件逐个执行LOAD DATA每个文件导入后立即提交这样即使中途失败也只需要重导一个分片不用从头再来。并行导入要谨慎。MySQL 8.0的LOAD DATA本身是单线程的但你可以同时开多个会话每个会话导入不同的分片文件利用多核CPU的并行能力。并行数建议控制在2到4不要贪多。我见过有人开8个并行导入结果直接打满磁盘IO单个导入速度反而比串行还慢还拖垮了线上业务。千万级数据导入还有一个宏观原则先导数据、后建索引。如果是全新表可以先删除所有非主键索引包括唯一索引导入完成后再通过ALTER TABLE ADD INDEX重建索引。原因是导入过程中每一条记录都要维护索引结构索引越多耗时越长而导入后统一建索引只需要一次全表扫描。这个技巧在数百万行以上的数据量效果非常显著。5. 高频问题排查与避坑手册5.1 死锁与锁等待——“批量插入居然也能死锁”很多人以为批量插入是纯写入操作不会出现死锁实际恰恰相反。批量插入往往包含大量行的写入行与行之间的锁获取顺序如果存在交叉两个并发事务就可能互相等待。我遇到过最典型的一种死锁场景两个事务都在批量插入同一个表但各自数据内部的顺序不一致事务A先锁了id1的行再锁id2的行事务B反过来正好卡上。解决办法有两个方向。第一应用层保证批量插入的数据按主键或唯一键排序这是最根本的解法第二把事务隔离级别降为READ COMMITTED减少间隙锁的参与范围。如果已经出现死锁MySQL不会让事务一直挂起它会自动回滚其中一个事务并抛出1213错误应用层要做好重试机制。5.2 主从延迟拉爆——批量插入的“隐性成本”开启主从复制的环境下大事务批量插入会直接造成主从延迟。原因很简单从库是单线程应用binlog的一个大事务需要完整执行完才能提交期间从库上的其他更新都被堵住。20万行的多值INSERT在主库上可能只需要几秒但从库上执行同样的事务可能要几十秒。我处理线上问题时的策略是限制单批数据量让每个事务控制在20秒内执行完。如果业务允许可以在导入期间临时把从库的并行复制参数调大MySQL 8.0的MTS并行复制对大数据量导入的缓解效果比较明显。5.3 常见报错速查表把批量插入过程中最容易碰到的几个报错整理成一张表方便你直接对照处理报错信息根因解决方案Packet too largeSQL包大小超过max_allowed_packet调大参数并减小批次行数Deadlock found (1213)并发事务锁顺序冲突按主键排序插入、降低隔离级别Data too long for column字段长度超出定义检查源数据或先扩容字段再回缩Duplicate entry唯一索引冲突导入前用临时表去重或INSERT IGNORELock wait timeout exceeded事务长时间持有行锁缩短单事务数据量分批提交The total number of locks exceeds the lock table size单事务加锁总数超出阈值缩小批量大小扩大buffer pool其中Duplicate entry的处理方式要单独说一下。批量插入时如果有一行触发唯一键冲突整条SQL都会失败。你可以选择把INSERT改成INSERT IGNORE跳过冲突行继续插入也可以用INSERT ... ON DUPLICATE KEY UPDATE在冲突时改为更新操作。这两个方案的性能差异不大关键看业务上是想保留旧数据还是覆盖新数据。5.4 批量插入的“隐藏技能”临时表中转最后一个技巧我想重点分享的是临时表中转法。当你要导入的数据需要经过复杂清洗、关联补全、去重等操作后再写入目标表时最稳妥的做法是先把原始数据LOAD DATA到一张临时表然后在临时表上做各种处理最后用INSERT INTO ... SELECT把最终数据一次性写入目标表。CREATE TEMPORARY TABLE tmp_member_import LIKE member; LOAD DATA LOCAL INFILE /tmp/member_old.csv INTO TABLE tmp_member_import FIELDS TERMINATED BY , LINES TERMINATED BY \n; INSERT INTO member (user_no, mobile, name, level, create_time) SELECT user_no, IFNULL(mobile, ), TRIM(name), level, NOW() FROM tmp_member_import WHERE level 0;这种做法的核心价值在于临时表不记录binlog、不触发外键检查、用完即毁所有清洗逻辑都在隔离环境中完成即使处理出错也不会污染正式数据。我做大表迁移时几乎必用这招既能保证数据质量又不影响线上表的读写。写在最后的一点个人体会批量插入做了这么多年我的最大感受是不要一上来就追求最极端的方案。先按多值INSERT 分段事务跑通然后根据数据量和瓶颈点逐层加优化——调整日志参数、关闭检查项、换LOAD DATA、并行导入。每一步优化都要有数据支撑用导入耗时说话而不是凭感觉“调得越多越快”。这套方法论在几十万行到几亿行的数据迁移中都验证过方向对了剩下的只是时间问题。
MySQL批量插入优化：告别低效导入，应对千万级数据迁移实战
先纠正一个普遍存在的认知误区MySQL批量插入不是一个“加分技巧”而是大数据导入场景下的“生存技能”。我见过太多项目前期表结构设计得漂漂亮亮一到灌数据阶段就卡在几万行上动辄跑几个小时最后不得不停下来重构导入逻辑。这篇文章我会把批量插入这件事拆透——从为什么慢、怎么提速、参数怎么调到实操中容易踩的坑全部摊开来讲。先说清楚适用人群需要向MySQL写入大量数据的开发、运维、数据分析师无论你是做ETL同步、初始化种子数据还是处理业务日志入库这篇内容都能直接落地。文中涉及的操作我都用MySQL 8.0验证过5.7版本基本通用个别参数差异我会单独标注。1. 为什么单条INSERT会让导入卡到怀疑人生1.1 慢的真正根源不是SQL执行时间而是“对话成本”很多人以为单条INSERT慢是因为MySQL执行INSERT语句本身耗时这个理解基本是错的。单条INSERT的SQL执行时间通常不到1毫秒但如果你在应用层循环执行一万条INSERT实际消耗的时间可能超过几分钟。差距就在每一次INSERT都要走完整的“客户端 → 服务端”往返链路。这条链路上至少有四层开销第一层是网络传输每发一条SQL都要打包、传输、等待响应即使在同一台机器上走localhostTCP协议栈的处理也有成本第二层是SQL解析MySQL收到文本形式的SQL后要做词法分析、语法分析、生成执行计划这个动作每条语句都会重复执行第三层是事务开销默认autocommit模式下每条INSERT都是独立事务每次都要写redo log、刷binlog、释放锁资源第四层是日志同步尤其当innodb_flush_log_at_trx_commit1时每次事务提交都要强制把日志刷到磁盘。把这四层开销乘上循环次数就是灾难。我在本地虚拟机里测过一组基准数据向一张10个字段的普通表写入10万条记录单条循环INSERT耗时约180秒而多值批量INSERT只需要6秒差距接近30倍。这个数据一点都不夸张网络环境越差、单条SQL越复杂差距越悬殊。1.2 批量插入为什么能快三条路同时缩短批量插入的快本质上是压缩了上面四层开销。拿多值INSERT来说一条INSERT语句携带1000行数据网络往返从1000次降到1次SQL解析从1000次降到1次这是第一个提速点。第二个提速点是事务粒度。批量插入允许你把大量数据包在一个事务里提交次数从1000次降到1次。这意味着磁盘fsync的次数大幅减少在机械硬盘和高延迟存储上效果尤其明显。第三个提速点是MySQL内部的执行优化。InnoDB引擎对一条语句内的多行插入有专门的批量处理路径可以减少B树索引更新的随机IO次数。这一点很多人忽略单条INSERT每插一行都要走一次索引查找和页分裂逻辑而批量插入时索引更新可以合并处理顺序IO的比例明显提升。2. 批量插入的三种主流方案怎么选2.1 多值INSERT最通用、最稳妥的起点多值INSERT就是把多个值组拼在一条语句里格式长这样INSERT INTO user_info (name, age, city) VALUES (张三, 25, 北京), (李四, 30, 上海), (王五, 28, 广州);这种方案的优势是兼容性最好所有客户端、所有驱动、所有MySQL版本都支持不需要额外的文件操作权限。操作上最核心的一个参数是“一次插多少行”。我自己的经验是500到1000行是一个甜点区间。低于100行语句条数太多网络往返压缩不充分高于2000行单条SQL过大一方面max_allowed_packet可能顶不住另一方面事务时间过长会增大锁竞争和回滚风险。有人会问要不要一次性把10万行全拼进去千万不要。一条INSERT涉及的行数过多时InnoDB需要维护的undo log和锁信息会暴涨一旦中间某行违反约束导致整条语句失败回滚代价极高。而且MySQL的binlog默认按事务记录超大事务会导致主从同步延迟飙升。分段批量插入是必须要做的。2.2 事务包裹配合循环更新的“加速器”多值INSERT适合从零写入但实际业务里还有一种高频场景需要循环更新或逐行处理后写入。这种场景下你不能把所有数据一次性拼进一条SQL但又想减少提交次数解决办法就是显式开启事务在事务里循环执行单条INSERT最后统一提交。import pymysql conn pymysql.connect(hostlocalhost, userroot, password123456, databasetest_db) cursor conn.cursor() try: cursor.execute(START TRANSACTION) for i in range(10000): cursor.execute( INSERT INTO user_info (name, age, city) VALUES (%s, %s, %s), (fuser_{i}, i % 60, 北京) ) conn.commit() except Exception as e: conn.rollback() print(f事务回滚: {e}) finally: cursor.close() conn.close()这个方案的提速逻辑和多值INSERT不太一样它没有减少SQL解析次数但把磁盘同步和事务提交从一万次压缩到一次。实测10万条记录开启事务循环插入比autocommit模式快8到12倍。这里有一个关键操作事务不是越大越好通常建议每1万到5万行提交一次。原因是InnoDB的MVCC机制会保留未提交事务的快照数据事务过长会导致undo log膨胀影响后续查询的可见性判断甚至拖垮purge线程。记住批量提交不是“一把梭”而是“分段提交”。2.3 LOAD DATA INFILE官方钦定的“核武器”如果你的数据已经落成文件比如CSV、TSV格式那LOAD DATA INFILE就是最优解。它的执行路径绕开了SQL层的逐行解析直接由存储引擎层批量装载数据速度比多值INSERT还要快一个量级。我处理过一份5GB的CSV数据入库多值INSERT跑了13分钟LOAD DATA只用了不到2分钟。LOAD DATA LOCAL INFILE /tmp/user_data.csv INTO TABLE user_info FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (id, name, age, city);使用时有几个细节必须注意。第一LOCAL关键字允许从客户端所在机器读取文件但需要客户端和服务端都开启local_infile参数MySQL 8.0默认关闭需要手动开启。第二字段顺序和数量必须严格对应多余字段可以通过用户变量丢弃比如SET col NULL的写法。第三文件编码必须是UTF-8否则中文乱码问题会让你排查半天。LOAD DATA还有一个隐藏优势可以配合FIELDS ESCAPED BY处理特殊字符也可以在导入前通过预处理逻辑做简单清洗。如果你的业务逻辑不复杂完全可以把清洗工作放在SQL语句里完成省掉一个处理环节。3. 必须调优的系统参数不调等于白干3.1 写入瓶颈的“三板斧”buffer pool、redo log、binlog批量插入的性能上限不完全由插入方式决定数据库自身的配置参数同样关键。我给所有做数据导入的团队一个排查顺序先看存储引擎配置再看日志策略。首先是innodb_buffer_pool_size。InnoDB的所有数据读写都要经过buffer pool导入过程中索引页和数据页都在这个内存区域里操作。如果buffer pool太小频繁的页换入换出会带来大量额外IO。生产服务器建议设置为物理内存的60%到70%在专用数据库实例上甚至可以更高。举个例子32GB内存的机器可以给20GB16GB内存的机器至少给10GB。导入任务完成后如果想收紧内存占用可以临时调小并重启生效。其次是innodb_flush_log_at_trx_commit。这个参数控制事务提交时redo log的刷盘策略默认值是1含义是每次提交都强制刷盘数据安全性最高但性能最差。导入场景如果允许极少数日志丢失的风险可以临时改成2性能能提升一个档次。这个参数在MySQL 5.6以后的版本支持动态修改不需要重启导入完成后记得改回来。最后是binlog相关配置。如果开启了binlog可以临时把sync_binlog设为0让MySQL不强制每条事务都同步binlog到磁盘。这个操作能明显提升导入速度但代价是断电时可能丢失最近的操作日志。生产环境谨慎使用测试环境可以放心开。3.2 容易踩坑的max_allowed_packet和事务隔离级别max_allowed_packet是很多人批量插入报错的“罪魁祸首”。当你的多值INSERT语句特别大时服务端会直接拒绝并报错“Packet too large”。这个参数有两层服务端的max_allowed_packet和客户端的max_allowed_packet两边的值取较小者生效。建议统一设置为64MB或128MB注意是字节单位。SET GLOBAL max_allowed_packet 134217728;事务隔离级别对批量插入也有影响默认的REPEATABLE READ在并发写场景下容易产生间隙锁和next-key lock导致不必要的锁等待。如果导入过程允许同时读数据可以临时将隔离级别改为READ COMMITTED配合批量插入能减少不少锁冲突。修改语句如下SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;还有一个经常被忽略的参数是innodb_autoinc_lock_mode。MySQL 8.0默认值是2即交错模式适合高并发插入如果你的核心表中存在自增主键且批量插入量极大建议确认这个参数没有被改成0或1。传统模式在批量插入时会持有表级AUTO-INC锁高并发下会拖慢其他插入操作。4. 实战拆解从慢速到高速的完整改造过程4.1 场景设定与初始方案我最近接手了一个会员数据迁移任务需要把一份20万行的老系统会员数据导入新库。表结构大概是id自增主键、user_no唯一索引、mobile唯一索引、name、level、create_time等字段共12列。初始方案是网上最常见的写法——ORM框架里循环save20万条数据跑了整整40分钟还没跑完。我先停下来算了一笔账40分钟意味着平均每条记录耗时12毫秒这个时延主要花在网络往返和事务提交上SQL本身绝对没有这么慢。果断换方案。第一步改造是用多值INSERT每500行拼一条SQL用Python的executemany实现。这里有个小细节pymysql的executemany会自动把参数列表拼接成多值INSERT但内部拼接的SQL大小受max_allowed_packet限制所以批次大小要先测一下我这边测试下来500行一条的包体在200KB左右非常安全。import pymysql import csv conn pymysql.connect(hostlocalhost, userroot, password123456, databasenew_db) cursor conn.cursor() batch [] batch_size 500 count 0 with open(member_old.csv, r, encodingutf-8) as f: reader csv.reader(f) header next(reader) for row in reader: batch.append(row) if len(batch) batch_size: cursor.executemany( INSERT INTO member (user_no, mobile, name, level, create_time) VALUES (%s, %s, %s, %s, %s), batch ) count len(batch) batch.clear() print(f已导入 {count} 条) if batch: cursor.executemany( INSERT INTO member (user_no, mobile, name, level, create_time) VALUES (%s, %s, %s, %s, %s), batch ) conn.commit()这个方案的实测结果是20万条数据耗时约4分钟比循环save提升了10倍。但还有优化空间主要瓶颈已经转移到了事务提交和索引更新上。4.2 二次优化加载速度再翻倍的组合拳第二次优化我做了三件事关闭唯一键检查、调整日志策略、扩大批量到1000行。先处理唯一键的问题。导入数据里如果确认没有重复可以先执行SET unique_checks0让MySQL在插入时跳过唯一索引的重复检查插入完成后再恢复。原理是唯一索引的每次插入都要在辅助索引上做一次查找全表20万行这个查找成本累计起来不容小视。然后是外键检查如果是空表导入且没有复杂的级联约束直接SET foreign_key_checks0。最后把innodb_flush_log_at_trx_commit临时改成2、sync_binlog改成0同时在应用层把每次提交行数调整为1万行。cursor.execute(SET unique_checks0) cursor.execute(SET foreign_key_checks0) cursor.execute(SET SESSION innodb_flush_log_at_trx_commit2)经过这轮调整同样20万行数据耗时降到了约1分20秒。说实话到这一步我已经比较满意了因为数据量本身不大继续挖掘优化的边际收益很低。但如果你处理的是千万级甚至上亿级数据接下来要看的LOAD DATA方案才是正餐。4.3 终极方案当数据量到达千万级别千万级数据的导入我强烈建议走LOAD DATA 分段提交 并行导入的组合路线。分段提交是指你在源文件层面就按逻辑拆成多个小文件比如100万行一个文件逐个执行LOAD DATA每个文件导入后立即提交这样即使中途失败也只需要重导一个分片不用从头再来。并行导入要谨慎。MySQL 8.0的LOAD DATA本身是单线程的但你可以同时开多个会话每个会话导入不同的分片文件利用多核CPU的并行能力。并行数建议控制在2到4不要贪多。我见过有人开8个并行导入结果直接打满磁盘IO单个导入速度反而比串行还慢还拖垮了线上业务。千万级数据导入还有一个宏观原则先导数据、后建索引。如果是全新表可以先删除所有非主键索引包括唯一索引导入完成后再通过ALTER TABLE ADD INDEX重建索引。原因是导入过程中每一条记录都要维护索引结构索引越多耗时越长而导入后统一建索引只需要一次全表扫描。这个技巧在数百万行以上的数据量效果非常显著。5. 高频问题排查与避坑手册5.1 死锁与锁等待——“批量插入居然也能死锁”很多人以为批量插入是纯写入操作不会出现死锁实际恰恰相反。批量插入往往包含大量行的写入行与行之间的锁获取顺序如果存在交叉两个并发事务就可能互相等待。我遇到过最典型的一种死锁场景两个事务都在批量插入同一个表但各自数据内部的顺序不一致事务A先锁了id1的行再锁id2的行事务B反过来正好卡上。解决办法有两个方向。第一应用层保证批量插入的数据按主键或唯一键排序这是最根本的解法第二把事务隔离级别降为READ COMMITTED减少间隙锁的参与范围。如果已经出现死锁MySQL不会让事务一直挂起它会自动回滚其中一个事务并抛出1213错误应用层要做好重试机制。5.2 主从延迟拉爆——批量插入的“隐性成本”开启主从复制的环境下大事务批量插入会直接造成主从延迟。原因很简单从库是单线程应用binlog的一个大事务需要完整执行完才能提交期间从库上的其他更新都被堵住。20万行的多值INSERT在主库上可能只需要几秒但从库上执行同样的事务可能要几十秒。我处理线上问题时的策略是限制单批数据量让每个事务控制在20秒内执行完。如果业务允许可以在导入期间临时把从库的并行复制参数调大MySQL 8.0的MTS并行复制对大数据量导入的缓解效果比较明显。5.3 常见报错速查表把批量插入过程中最容易碰到的几个报错整理成一张表方便你直接对照处理报错信息根因解决方案Packet too largeSQL包大小超过max_allowed_packet调大参数并减小批次行数Deadlock found (1213)并发事务锁顺序冲突按主键排序插入、降低隔离级别Data too long for column字段长度超出定义检查源数据或先扩容字段再回缩Duplicate entry唯一索引冲突导入前用临时表去重或INSERT IGNORELock wait timeout exceeded事务长时间持有行锁缩短单事务数据量分批提交The total number of locks exceeds the lock table size单事务加锁总数超出阈值缩小批量大小扩大buffer pool其中Duplicate entry的处理方式要单独说一下。批量插入时如果有一行触发唯一键冲突整条SQL都会失败。你可以选择把INSERT改成INSERT IGNORE跳过冲突行继续插入也可以用INSERT ... ON DUPLICATE KEY UPDATE在冲突时改为更新操作。这两个方案的性能差异不大关键看业务上是想保留旧数据还是覆盖新数据。5.4 批量插入的“隐藏技能”临时表中转最后一个技巧我想重点分享的是临时表中转法。当你要导入的数据需要经过复杂清洗、关联补全、去重等操作后再写入目标表时最稳妥的做法是先把原始数据LOAD DATA到一张临时表然后在临时表上做各种处理最后用INSERT INTO ... SELECT把最终数据一次性写入目标表。CREATE TEMPORARY TABLE tmp_member_import LIKE member; LOAD DATA LOCAL INFILE /tmp/member_old.csv INTO TABLE tmp_member_import FIELDS TERMINATED BY , LINES TERMINATED BY \n; INSERT INTO member (user_no, mobile, name, level, create_time) SELECT user_no, IFNULL(mobile, ), TRIM(name), level, NOW() FROM tmp_member_import WHERE level 0;这种做法的核心价值在于临时表不记录binlog、不触发外键检查、用完即毁所有清洗逻辑都在隔离环境中完成即使处理出错也不会污染正式数据。我做大表迁移时几乎必用这招既能保证数据质量又不影响线上表的读写。写在最后的一点个人体会批量插入做了这么多年我的最大感受是不要一上来就追求最极端的方案。先按多值INSERT 分段事务跑通然后根据数据量和瓶颈点逐层加优化——调整日志参数、关闭检查项、换LOAD DATA、并行导入。每一步优化都要有数据支撑用导入耗时说话而不是凭感觉“调得越多越快”。这套方法论在几十万行到几亿行的数据迁移中都验证过方向对了剩下的只是时间问题。
