批量数据操作：高效写法与性能对比

批量数据操作高效写法与性能对比同样是插10万条数据有人写30秒有人写10分钟。差距不在数据库在写法。核心原则永远不要逐条操作先说结论——操作逐条执行批量执行性能差距插入 10万条~10分钟~2秒300倍更新 10万条~8分钟~1秒480倍删除 10万条~6分钟~0.8秒450倍以上基于 MySQL 8.0 / InnoDB / 单表 10万行数据量越大差距越夸张。原因很简单每一条 SQL 都是一次网络往返 一次事务 一次索引维护。10万条就是10万次。批量操作把这些合并成1次。一、批量插入写法对比写法示例性能适用场景逐条 INSERTINSERT INTO t VALUES (1,a); INSERT INTO t VALUES (2,b); ...⭐永远别用拼接 VALUESINSERT INTO t VALUES (1,a),(2,b),(3,c),...;⭐⭐⭐⭐⭐首选单次建议500~2000条LOAD DATA INFILELOAD DATA INFILE /tmp/data.csv INTO TABLE t;⭐⭐⭐⭐⭐⭐百万级数据最快INSERT ... SELECTINSERT INTO t2 SELECT * FROM t1 WHERE ...;⭐⭐⭐⭐表间迁移ON DUPLICATE KEY UPDATEINSERT INTO t VALUES (1,a),(2,b) ON DUPLICATE KEY UPDATE nameVALUES(name);⭐⭐⭐⭐upsert 场景关键细节拼接 VALUES 不是越多越好。sql-- ❌ 一次拼10万条包体超限直接报错 INSERT INTO t VALUES (1,a),(2,b),...,(100000,z); -- ✅ 每次1000条循环100次 -- 性能几乎一样但稳定不报错max_allowed_packet默认 64MB一条 SQL 别超过 16MB 包体安全起见控制在500~2000条/批。LOAD DATA 比 INSERT 快 2~5 倍因为它绕过了 SQL 解析层直接写数据文件。但需要文件落地不适合纯内存数据。二、批量更新这是最容易写出烂性能的操作。写法对比写法示例性能评价逐条 UPDATEUPDATE t SET namea WHERE id1; UPDATE t SET nameb WHERE id2; ...⭐别用CASE WHENUPDATE t SET name CASE id WHEN 1 THEN a WHEN 2 THEN b ... END WHERE id IN (1,2,3...);⭐⭐⭐⭐推荐单次500~1000条临时表 JOINUPDATE t JOIN tmp ON t.idtmp.id SET t.nametmp.name;⭐⭐⭐⭐⭐大批量首选批量 INSERT ON DUPLICATE KEY先拼 INSERT冲突时 UPDATE⭐⭐⭐⭐upsert 场景CASE WHEN 写法示例sql-- 更新1000条不同的值一条SQL搞定 UPDATE user SET status CASE id WHEN 1 THEN 1 WHEN 2 THEN 0 WHEN 3 THEN 2 ... END WHERE id IN (1,2,3,...);原理MySQL 只扫描一次表根据 CASE 分支一次性更新所有行。临时表 JOIN 写法大批量最优sql-- 1. 建临时表批量插入待更新数据 CREATE TEMPORARY TABLE tmp_update (id BIGINT PRIMARY KEY, name VARCHAR(100), status INT); -- 2. 批量插入用拼接 VALUES INSERT INTO tmp_update VALUES (1,Alice,1),(2,Bob,0),...; -- 3. 一条 JOIN UPDATE UPDATE user u JOIN tmp_update t ON u.id t.id SET u.name t.name, u.status t.status; -- 4. 删临时表自动为什么比 CASE WHEN 快因为 CASE WHEN 的 SQL 文本长度随数据量线性增长10万条时 SQL 本身就几 MB。临时表方案 SQL 长度恒定优化器更友好。数据量CASE WHEN临时表 JOIN100条差不多差不多1000条稍慢快一点1万条明显慢快很多10万条很慢SQL巨大依然很快三、批量删除写法对比写法示例性能评价逐条 DELETEDELETE FROM t WHERE id1; DELETE FROM t WHERE id2; ...⭐别用IN 子句DELETE FROM t WHERE id IN (1,2,3,...,10000);⭐⭐⭐小批量够用JOIN 删除DELETE t FROM t JOIN tmp ON t.idtmp.id;⭐⭐⭐⭐⭐大批量首选TRUNCATETRUNCATE TABLE t;⭐⭐⭐⭐⭐⭐全表清空才用IN 子句的坑sql-- ❌ IN 列表超过1000个性能骤降 DELETE FROM t WHERE id IN (1,2,3,...,50000); -- ✅ 分批删除每批1000条 DELETE FROM t WHERE id IN (1,...,1000); DELETE FROM t WHERE id IN (1001,...,2000); ...IN列表过长时MySQL 优化器会放弃索引走全表扫描。JOIN 删除推荐sql-- 和批量更新同理用临时表 CREATE TEMPORARY TABLE tmp_delete (id BIGINT PRIMARY KEY); INSERT INTO tmp_delete VALUES (1),(2),(3),...; DELETE t FROM target_table t JOIN tmp_delete d ON t.id d.id;一条 SQL走索引稳定快。四、终极性能对比以 MySQL InnoDB、单表、10万条数据为基准1倍 逐条执行耗时操作逐条拼接VALUES/INCASE WHEN / JOINLOAD DATA / 临时表插入1x0.003x-0.001x更新1x0.005x0.002x0.001x删除1x0.01x0.002x0.001x数据来源实际压测 社区基准不同硬件有波动但量级差距是稳定的。五、最佳实践清单✅ 插入拼接 VALUES每批 500~2000 条循环执行 ✅ 更新 1000条用 CASE WHEN 1000条用临时表 JOIN ✅ 删除永远用 JOIN 或分批 IN禁止逐条 ✅ 事务批量操作外面包一层事务别每条一个事务 ✅ 索引批量操作前确保 WHERE 条件走索引 ✅ 主键批量插入一定带主键否则 InnoDB 退化为堆表最后一句批量操作不是优化是基本功。写逐条 SQL 不是慢一点的问题是工程质量问题。