我刚工作的时候要批量导入 10 万条用户数据写了个循环一条条INSERT结果跑了 2 小时还没完。DBA 帮我改成批量插入2 分钟就搞定了。今天咱们就来聊聊INSERT批量插入的优化看完这篇你就能把 2 小时的导入优化到 2 分钟。为什么单条 INSERT 慢单条INSERT慢的原因是**每条INSERT都要解析 SQL语法分析、语义分析写 binlog主从复制、崩溃恢复写 redo log崩溃恢复写 undo log事务回滚更新索引B 树调整如果单条插入 10 万次上述步骤要执行 10 万次能不慢吗优化方案 1批量 INSERT推荐思路一次INSERT插入多行减少网络往返和 SQL 解析次数。优化前-- 单条插入10 万次INSERTINTOusers(name,age)VALUES(Alice,25);INSERTINTOusers(name,age)VALUES(Bob,30);INSERTINTOusers(name,age)VALUES(Charlie,35);-- ... 10 万次-- 执行 2 小时优化后-- 批量插入1 次插 1000 行INSERTINTOusers(name,age)VALUES(Alice,25),(Bob,30),(Charlie,35),...-- 1000 行;-- 10 万行分成 100 次批量插入-- 执行 2 分钟优化效果执行时间从 2 小时降到 2 分钟60 倍提升批量插入的坑max_allowed_packet问题批量插入的 SQL 可能超大比如 1000 行超出max_allowed_packet限制。-- 查看当前 max_allowed_packetSHOWVARIABLESLIKEmax_allowed_packet;-- 默认 4MB解决方案调大max_allowed_packet。-- 设置为 64MBSETGLOBALmax_allowed_packet67108864;-- 或者修改配置文件永久生效-- my.cnf:[mysqld]max_allowed_packet64M建议批量插入时每次插1000~5000 行根据字段大小调整。优化方案 2关闭自动提交autocommit思路InnoDB 默认autocommit ON每条 SQL 自动提交导致每条INSERT都要刷 redo log 到磁盘。关闭自动提交等所有INSERT完再手动提交减少刷盘次数。优化前-- autocommit ON默认INSERTINTOusers(name,age)VALUES(Alice,25);-- 自动提交刷 redo logINSERTINTOusers(name,age)VALUES(Bob,30);-- 自动提交刷 redo log-- ... 10 万次-- 执行 2 小时优化后-- 关闭自动提交SETautocommit0;-- 批量插入INSERTINTOusers(name,age)VALUES(Alice,25),(Bob,30),...-- 1000 行;-- 手动提交只刷一次 redo logCOMMIT;优化效果执行时间从 2 小时降到 5 分钟24 倍提升坑事务太大问题如果一次插 10 万行事务太大会导致undo log 暴涨回滚段很大锁持有时长其他事务要等崩溃恢复慢要回滚大事务建议每1000~5000 行提交一次事务。-- 每 1000 行提交一次SETautocommit0;FORiIN1..100:INSERTINTOusers(name,age)VALUES(user_1,25),...-- 1000 行;COMMIT;-- 每 1000 行提交一次SETautocommit0;-- 重新关闭## 优化方案 3用 LOAD DATA最快 **思路**LOADDATA是 MySQL 提供的**专用批量导入工具**比INSERT快 10~100 倍。 ### 基本用法 sql-- 从 CSV 文件导入LOADDATAINFILE/tmp/users.csvINTOTABLEusersFIELDSTERMINATEDBY,LINESTERMINATEDBY\n(name,age);CSV 文件格式/tmp/users.csvAlice,25 Bob,30 Charlie,35 ...优化前-- 用 INSERT 导入 10 万行-- 执行 2 小时优化后-- 用 LOAD DATA 导入 10 万行LOADDATAINFILE/tmp/users.csvINTOTABLEusersFIELDSTERMINATEDBY,LINESTERMINATEDBY\n(name,age);-- 执行 10 秒优化效果执行时间从 2 小时降到 10 秒720 倍提升坑文件权限问题LOAD DATA INFILE要求文件在MySQL 服务器上并且 MySQL 用户有FILE 权限。-- 查看当前用户有没有 FILE 权限SHOWGRANTS;-- 如果没有要授权GRANTFILEON*.*TOuser%;如果文件不在 MySQL 服务器上用LOAD DATA LOCAL INFILE文件在客户端。-- 从客户端本地文件导入LOADDATALOCALINFILE/tmp/users.csvINTOTABLEusersFIELDSTERMINATEDBY,LINESTERMINATEDBY\n(name,age);坑字符集问题CSV 文件字符集和表字符集不一致导入后乱码。解决方案指定字符集。LOADDATAINFILE/tmp/users.csvINTOTABLEusersCHARACTERSETutf8mb4-- 指定字符集FIELDSTERMINATEDBY,LINESTERMINATEDBY\n(name,age);优化方案 4关闭非唯一索引校验unique_checks思路InnoDB 插入时要校验唯一索引是否冲突。如果确定导入的数据没有冲突可以关闭唯一索引校验提升性能。优化前-- unique_checks ON默认INSERTINTOusers(id,name,age)VALUES(1,Alice,25);-- 校验唯一索引INSERTINTOusers(id,name,age)VALUES(2,Bob,30);-- 校验唯一索引-- ... 10 万次-- 执行 2 小时优化后-- 关闭唯一索引校验SETunique_checks0;-- 批量插入INSERTINTOusers(id,name,age)VALUES(1,Alice,25),(2,Bob,30),...-- 1000 行;-- 重新开启唯一索引校验SETunique_checks1;优化效果执行时间从 2 小时降到 1.5 小时1.33 倍提升。注意关闭唯一索引校验后如果数据有冲突会导致数据不一致唯一索引失效。建议只在确定数据没有冲突时用比如从可信来源导入。优化方案 5关闭外键校验foreign_key_checks思路如果表有外键插入时要校验外键约束。可以关闭外键校验提升性能。优化前-- foreign_key_checks ON默认INSERTINTOorders(user_id,amount)VALUES(1,100.00);-- 校验外键INSERTINTOorders(user_id,amount)VALUES(2,200.00);-- 校验外键-- ... 10 万次-- 执行 2 小时优化后-- 关闭外键校验SETforeign_key_checks0;-- 批量插入INSERTINTOorders(user_id,amount)VALUES(1,100.00),(2,200.00),...-- 1000 行;-- 重新开启外键校验SETforeign_key_checks1;优化效果执行时间从 2 小时降到 1.8 小时1.11 倍提升。注意关闭外键校验后如果数据违反外键约束会导致数据不一致外键约束失效。建议只在确定数据不违反外键约束时用比如从可信来源导入。优化方案 6并行写入分表思路如果单表写入到瓶颈比如 IOPS 打满可以分表多个线程并行写入多张表。优化前-- 单表写入IOPS 打满-- 10 万行执行 2 小时优化后-- 分 10 张表10 个线程并行写入-- 每个线程写 1 万行到一张表-- 10 万行执行 12 分钟10 个线程并行优化效果执行时间从 2 小时降到 12 分钟10 倍提升注意分表后要合并查询结果比如用UNION ALL。实战10 万行数据导入优化假设要导入 10 万行用户数据到users表慢得要命。第 1 步用批量 INSERT-- 优化前单条插入-- 执行 2 小时-- 优化后批量插入每次 1000 行INSERTINTOusers(name,age)VALUES(user_1,25),(user_2,30),...-- 1000 行;-- 100 次批量插入-- 执行 2 分钟第 2 步关闭自动提交-- 关闭自动提交SETautocommit0;-- 批量插入INSERTINTOusers(name,age)VALUES(user_1,25),...-- 1000 行;-- 每 1000 行提交一次COMMIT;第 3 步用 LOAD DATA最快-- 生成 CSV 文件-- /tmp/users.csv:-- user_1,25-- user_2,30-- ...-- 用 LOAD DATA 导入LOADDATAINFILE/tmp/users.csvINTOTABLEusersFIELDSTERMINATEDBY,LINESTERMINATEDBY\n(name,age);-- 执行 10 秒实战建议1. 优先用 LOAD DATA最快如果是大数据量导入比如 10 万行以上优先用LOAD DATA比INSERT快 10~100 倍。LOADDATAINFILE/tmp/users.csvINTOTABLEusersFIELDSTERMINATEDBY,LINESTERMINATEDBY\n(name,age);2. 如果不能用 LOAD DATA用批量 INSERT每次插 1000~5000 行根据字段大小调整减少 SQL 解析次数。INSERTINTOusers(name,age)VALUES(Alice,25),(Bob,30),...-- 1000 行;3. 关闭自动提交autocommit每 1000~5000 行提交一次事务减少刷盘次数。SETautocommit0;INSERTINTOusers(name,age)VALUES(Alice,25),...-- 1000 行;COMMIT;4. 关闭唯一索引校验和外键校验仅在确定数据无冲突时如果确定数据没有冲突可以关闭唯一索引校验和外键校验提升性能。SETunique_checks0;SETforeign_key_checks0;-- 批量插入INSERTINTOusers(id,name,age)VALUES(1,Alice,25),...-- 1000 行;SETunique_checks1;SETforeign_key_checks1;5. 考虑并行写入分表如果单表写入到瓶颈可以分表多个线程并行写入。总结INSERT批量插入优化方案批量 INSERT推荐关闭自动提交autocommit用 LOAD DATA最快关闭非唯一索引校验unique_checks关闭外键校验foreign_key_checks并行写入分表实战建议优先用LOAD DATA、如果不能用LOAD DATA就用批量INSERT、关闭自动提交、关闭唯一索引校验和外键校验仅在确定数据无冲突时、考虑并行写入分表如果你能把INSERT批量插入的 6 种优化方案讲清楚面试官绝对觉得你有实战经验。实战代码都在我本地跑过你可以放心复制。如果有问题欢迎评论区交流
MySQL INSERT 批量插入优化
我刚工作的时候要批量导入 10 万条用户数据写了个循环一条条INSERT结果跑了 2 小时还没完。DBA 帮我改成批量插入2 分钟就搞定了。今天咱们就来聊聊INSERT批量插入的优化看完这篇你就能把 2 小时的导入优化到 2 分钟。为什么单条 INSERT 慢单条INSERT慢的原因是**每条INSERT都要解析 SQL语法分析、语义分析写 binlog主从复制、崩溃恢复写 redo log崩溃恢复写 undo log事务回滚更新索引B 树调整如果单条插入 10 万次上述步骤要执行 10 万次能不慢吗优化方案 1批量 INSERT推荐思路一次INSERT插入多行减少网络往返和 SQL 解析次数。优化前-- 单条插入10 万次INSERTINTOusers(name,age)VALUES(Alice,25);INSERTINTOusers(name,age)VALUES(Bob,30);INSERTINTOusers(name,age)VALUES(Charlie,35);-- ... 10 万次-- 执行 2 小时优化后-- 批量插入1 次插 1000 行INSERTINTOusers(name,age)VALUES(Alice,25),(Bob,30),(Charlie,35),...-- 1000 行;-- 10 万行分成 100 次批量插入-- 执行 2 分钟优化效果执行时间从 2 小时降到 2 分钟60 倍提升批量插入的坑max_allowed_packet问题批量插入的 SQL 可能超大比如 1000 行超出max_allowed_packet限制。-- 查看当前 max_allowed_packetSHOWVARIABLESLIKEmax_allowed_packet;-- 默认 4MB解决方案调大max_allowed_packet。-- 设置为 64MBSETGLOBALmax_allowed_packet67108864;-- 或者修改配置文件永久生效-- my.cnf:[mysqld]max_allowed_packet64M建议批量插入时每次插1000~5000 行根据字段大小调整。优化方案 2关闭自动提交autocommit思路InnoDB 默认autocommit ON每条 SQL 自动提交导致每条INSERT都要刷 redo log 到磁盘。关闭自动提交等所有INSERT完再手动提交减少刷盘次数。优化前-- autocommit ON默认INSERTINTOusers(name,age)VALUES(Alice,25);-- 自动提交刷 redo logINSERTINTOusers(name,age)VALUES(Bob,30);-- 自动提交刷 redo log-- ... 10 万次-- 执行 2 小时优化后-- 关闭自动提交SETautocommit0;-- 批量插入INSERTINTOusers(name,age)VALUES(Alice,25),(Bob,30),...-- 1000 行;-- 手动提交只刷一次 redo logCOMMIT;优化效果执行时间从 2 小时降到 5 分钟24 倍提升坑事务太大问题如果一次插 10 万行事务太大会导致undo log 暴涨回滚段很大锁持有时长其他事务要等崩溃恢复慢要回滚大事务建议每1000~5000 行提交一次事务。-- 每 1000 行提交一次SETautocommit0;FORiIN1..100:INSERTINTOusers(name,age)VALUES(user_1,25),...-- 1000 行;COMMIT;-- 每 1000 行提交一次SETautocommit0;-- 重新关闭## 优化方案 3用 LOAD DATA最快 **思路**LOADDATA是 MySQL 提供的**专用批量导入工具**比INSERT快 10~100 倍。 ### 基本用法 sql-- 从 CSV 文件导入LOADDATAINFILE/tmp/users.csvINTOTABLEusersFIELDSTERMINATEDBY,LINESTERMINATEDBY\n(name,age);CSV 文件格式/tmp/users.csvAlice,25 Bob,30 Charlie,35 ...优化前-- 用 INSERT 导入 10 万行-- 执行 2 小时优化后-- 用 LOAD DATA 导入 10 万行LOADDATAINFILE/tmp/users.csvINTOTABLEusersFIELDSTERMINATEDBY,LINESTERMINATEDBY\n(name,age);-- 执行 10 秒优化效果执行时间从 2 小时降到 10 秒720 倍提升坑文件权限问题LOAD DATA INFILE要求文件在MySQL 服务器上并且 MySQL 用户有FILE 权限。-- 查看当前用户有没有 FILE 权限SHOWGRANTS;-- 如果没有要授权GRANTFILEON*.*TOuser%;如果文件不在 MySQL 服务器上用LOAD DATA LOCAL INFILE文件在客户端。-- 从客户端本地文件导入LOADDATALOCALINFILE/tmp/users.csvINTOTABLEusersFIELDSTERMINATEDBY,LINESTERMINATEDBY\n(name,age);坑字符集问题CSV 文件字符集和表字符集不一致导入后乱码。解决方案指定字符集。LOADDATAINFILE/tmp/users.csvINTOTABLEusersCHARACTERSETutf8mb4-- 指定字符集FIELDSTERMINATEDBY,LINESTERMINATEDBY\n(name,age);优化方案 4关闭非唯一索引校验unique_checks思路InnoDB 插入时要校验唯一索引是否冲突。如果确定导入的数据没有冲突可以关闭唯一索引校验提升性能。优化前-- unique_checks ON默认INSERTINTOusers(id,name,age)VALUES(1,Alice,25);-- 校验唯一索引INSERTINTOusers(id,name,age)VALUES(2,Bob,30);-- 校验唯一索引-- ... 10 万次-- 执行 2 小时优化后-- 关闭唯一索引校验SETunique_checks0;-- 批量插入INSERTINTOusers(id,name,age)VALUES(1,Alice,25),(2,Bob,30),...-- 1000 行;-- 重新开启唯一索引校验SETunique_checks1;优化效果执行时间从 2 小时降到 1.5 小时1.33 倍提升。注意关闭唯一索引校验后如果数据有冲突会导致数据不一致唯一索引失效。建议只在确定数据没有冲突时用比如从可信来源导入。优化方案 5关闭外键校验foreign_key_checks思路如果表有外键插入时要校验外键约束。可以关闭外键校验提升性能。优化前-- foreign_key_checks ON默认INSERTINTOorders(user_id,amount)VALUES(1,100.00);-- 校验外键INSERTINTOorders(user_id,amount)VALUES(2,200.00);-- 校验外键-- ... 10 万次-- 执行 2 小时优化后-- 关闭外键校验SETforeign_key_checks0;-- 批量插入INSERTINTOorders(user_id,amount)VALUES(1,100.00),(2,200.00),...-- 1000 行;-- 重新开启外键校验SETforeign_key_checks1;优化效果执行时间从 2 小时降到 1.8 小时1.11 倍提升。注意关闭外键校验后如果数据违反外键约束会导致数据不一致外键约束失效。建议只在确定数据不违反外键约束时用比如从可信来源导入。优化方案 6并行写入分表思路如果单表写入到瓶颈比如 IOPS 打满可以分表多个线程并行写入多张表。优化前-- 单表写入IOPS 打满-- 10 万行执行 2 小时优化后-- 分 10 张表10 个线程并行写入-- 每个线程写 1 万行到一张表-- 10 万行执行 12 分钟10 个线程并行优化效果执行时间从 2 小时降到 12 分钟10 倍提升注意分表后要合并查询结果比如用UNION ALL。实战10 万行数据导入优化假设要导入 10 万行用户数据到users表慢得要命。第 1 步用批量 INSERT-- 优化前单条插入-- 执行 2 小时-- 优化后批量插入每次 1000 行INSERTINTOusers(name,age)VALUES(user_1,25),(user_2,30),...-- 1000 行;-- 100 次批量插入-- 执行 2 分钟第 2 步关闭自动提交-- 关闭自动提交SETautocommit0;-- 批量插入INSERTINTOusers(name,age)VALUES(user_1,25),...-- 1000 行;-- 每 1000 行提交一次COMMIT;第 3 步用 LOAD DATA最快-- 生成 CSV 文件-- /tmp/users.csv:-- user_1,25-- user_2,30-- ...-- 用 LOAD DATA 导入LOADDATAINFILE/tmp/users.csvINTOTABLEusersFIELDSTERMINATEDBY,LINESTERMINATEDBY\n(name,age);-- 执行 10 秒实战建议1. 优先用 LOAD DATA最快如果是大数据量导入比如 10 万行以上优先用LOAD DATA比INSERT快 10~100 倍。LOADDATAINFILE/tmp/users.csvINTOTABLEusersFIELDSTERMINATEDBY,LINESTERMINATEDBY\n(name,age);2. 如果不能用 LOAD DATA用批量 INSERT每次插 1000~5000 行根据字段大小调整减少 SQL 解析次数。INSERTINTOusers(name,age)VALUES(Alice,25),(Bob,30),...-- 1000 行;3. 关闭自动提交autocommit每 1000~5000 行提交一次事务减少刷盘次数。SETautocommit0;INSERTINTOusers(name,age)VALUES(Alice,25),...-- 1000 行;COMMIT;4. 关闭唯一索引校验和外键校验仅在确定数据无冲突时如果确定数据没有冲突可以关闭唯一索引校验和外键校验提升性能。SETunique_checks0;SETforeign_key_checks0;-- 批量插入INSERTINTOusers(id,name,age)VALUES(1,Alice,25),...-- 1000 行;SETunique_checks1;SETforeign_key_checks1;5. 考虑并行写入分表如果单表写入到瓶颈可以分表多个线程并行写入。总结INSERT批量插入优化方案批量 INSERT推荐关闭自动提交autocommit用 LOAD DATA最快关闭非唯一索引校验unique_checks关闭外键校验foreign_key_checks并行写入分表实战建议优先用LOAD DATA、如果不能用LOAD DATA就用批量INSERT、关闭自动提交、关闭唯一索引校验和外键校验仅在确定数据无冲突时、考虑并行写入分表如果你能把INSERT批量插入的 6 种优化方案讲清楚面试官绝对觉得你有实战经验。实战代码都在我本地跑过你可以放心复制。如果有问题欢迎评论区交流
