MySQL 批量插入和批量更新如何优化？

简化版

批量写入要减少网络往返和事务提交次数，控制批大小，利用批量 INSERT、事务、预编译和合理索引；同时避免单次事务过大导致锁等待、redo/binlog 暴涨和主从延迟。

详细版

逐条写入最常见的问题是每行一次网络请求、一次 SQL 解析、一次事务提交，吞吐很低。批量插入可以用一条 INSERT ... VALUES (...), (...), ...，或在一个事务中执行多条预编译语句。

批量更新要更谨慎。按主键分批、按稳定顺序处理，可以减少锁冲突；每批 500 或 1000 行之类的大小需要根据表结构、索引、机器和业务压力压测确定。

优化不是把所有数据塞进一个巨大事务。过大事务会占用 undo、redo、binlog，锁持有时间变长，失败回滚成本也高。正确做法是分批、限速、监控和可恢复。

完整版教学

一、逐条写为什么慢

逐条写入会放大固定成本。

INSERT INTO logs(user_id, content) VALUES (1, 'a');
INSERT INTO logs(user_id, content) VALUES (2, 'b');
INSERT INTO logs(user_id, content) VALUES (3, 'c');

每条语句都可能经历网络发送、解析优化、执行、提交、写日志。如果有 10 万行，就有 10 万次类似固定开销。

记忆钩子：批量写入优化的第一原则，是减少“每行一次”的固定成本。

二、批量 INSERT 怎么写

常见批量插入：

INSERT INTO logs(user_id, content)
VALUES
  (1, 'a'),
  (2, 'b'),
  (3, 'c');

它减少 SQL 往返和解析开销，也能让 InnoDB 更集中地写日志。

如果一次插入 10 万行，不建议拼成一条超大 SQL。可以按批处理：

每批 500~2000 行
批间短暂停顿或按负载限速
失败后记录批次位置，支持重试

具体批大小没有万能值，需要压测。字段多、索引多、行大时，批大小应更保守。

三、事务提交次数很关键

如果每条写入都自动提交，事务提交成本会非常高。

START TRANSACTION;
INSERT INTO logs(user_id, content) VALUES (1, 'a');
INSERT INTO logs(user_id, content) VALUES (2, 'b');
INSERT INTO logs(user_id, content) VALUES (3, 'c');
COMMIT;

把多条写入放进一个事务，可以减少提交刷日志的次数。但事务过大也有风险。

事务大小	优点	风险
每行提交	简单	吞吐低
中等批次	吞吐和风险平衡	需要批次控制
超大事务	提交次数少	锁久、回滚慢、日志压力大

面试回答要强调“适度批量”，不是“越大越好”。

四、批量更新要按索引和顺序处理

批量更新如果没有命中索引，会扫描和锁住大量行。

UPDATE orders
SET status = 'expired'
WHERE expire_time < NOW()
  AND status = 'unpaid'
LIMIT 1000;

适合的索引可能是：

CREATE INDEX idx_status_expire ON orders(status, expire_time);

为了减少死锁和锁冲突，批量更新最好按主键或索引顺序推进：

id 1~1000
id 1001~2000
id 2001~3000

不要多个任务用不同顺序更新同一批数据。

五、索引数量会影响写入

每插入或更新一行，MySQL 不只改数据页，还要维护相关二级索引。

假设一张表有 1 个主键、8 个二级索引，插入 100 万行时，要维护 8 棵二级索引 B+ 树。索引越多，写入越慢。

批量导入前可以评估：

是否有冗余索引
是否能先导入再建索引
是否影响线上查询
是否需要关闭非必要触发器或外部同步

线上业务表不能随便删索引或延迟建索引，但离线导入、初始化表时可以利用这个思路。

六、复制延迟和日志压力不能忽略

批量写入会产生大量 redo log 和 binlog。从库要重放这些变更，如果主库短时间写入太快，从库可能明显延迟。

执行批量任务时应监控：

主库 QPS / TPS
redo 写入压力
binlog 大小
从库延迟
锁等待
慢查询和业务错误率

如果发现延迟上升，可以降低批大小、增加批间 sleep、切分时间窗口，或改到低峰执行。

七、常见误区与追问

误区：批越大性能越好。 超大批次会带来锁、日志、回滚和复制延迟风险。
误区：批量更新不需要索引。 没有索引会扫描和锁住大量行，影响线上业务。
误区：只要用了事务就一定快。 事务要控制大小，过大事务可能更危险。
误区：写入优化只看主库。 主从复制延迟和下游消费也要监控。
追问：批量插入推荐多大一批？ 没有固定值，常从几百到几千行压测，按行大小、索引数和负载调整。
追问：如何降低死锁概率？ 按相同索引顺序分批更新，缩短事务，减少单批锁范围。

八、加强记忆

批量写入优化抓住“批量、事务、索引、限速、监控”。减少固定成本能提吞吐，但不要变成超大事务；线上批处理要可暂停、可重试、可观察，别让一次任务把主从和业务一起压住。

简化版

详细版