怎样在MySQL存储过程中实现按批次提交以减少锁表-mysql教程-PHP中文网

直接 INSERT INTO SELECT 会锁表，因其作为单事务执行，导致 undo/redo 日志暴涨、buffer pool 压力大，并对扫描行加锁（可能升级为间隙锁或表级锁）；应改用按主键分批+显式 COMMIT 的小事务方案。

怎样在mysql存储过程中实现按批次提交以减少锁表

为什么直接 INSERT INTO SELECT 会锁表

因为整条语句被当作单事务执行，InnoDB 要维护完整的 undo/redo 日志，同时对扫描范围内的所有行加锁（甚至升级为间隙锁或表级锁）。哪怕加了 WHERE id BETWEEN ? AND ?，只要没走索引或扫描行数过多，照样触发 Lock wait timeout exceeded 或让主从延迟飙升。

用 WHILE + 主键游标分批插入的硬性要求

核心不是“循环”，而是“定位不重复、提交不遗漏、退出不卡死”。必须满足以下三点：

游标字段必须是有索引的单调递增列（如自增 id 或 created_at），禁用 OFFSET——它会导致越往后越慢
每次 INSERT 后必须紧跟 COMMIT，不能依赖存储过程末尾自动提交；autocommit=OFF 下尤其危险
循环退出条件必须基于 ROW_COUNT() 或显式查询最大值，例如：SELECT MAX(id) INTO last_id FROM target_table WHERE id > last_id;，否则可能漏数据或死循环

batch_size 设多少才合适

没有固定值，取决于单行数据大小、服务器 innodb_buffer_pool_size 和磁盘 I/O 能力。常见经验值是 1000–5000 行，但要注意：

MySQL8 CRM Schema Design Expert

面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。

下载

若单行超 1KB，建议下调到 500–1000；否则 redo log 写满、buffer pool 淘汰加剧
别硬编码在存储过程里，应设为 IN batch_size INT DEFAULT 2000 参数，方便压测时动态调整
首次运行可先试 500，观察 SHOW ENGINE INNODB STATUS 中的 LOG 和 TRANSACTIONS 区段，确认无长时间未提交事务

比游标快十倍的替代写法：INSERT ... SELECT 套子查询

只要逻辑能用 SQL 表达，就别用 DECLARE cur CURSOR。比如要过滤、转换字段，直接塞进子查询：

INSERT INTO target (a, b, c) 
SELECT 
  src.id,
  CASE WHEN src.status = 'active' THEN 1 ELSE 0 END,
  COALESCE(src.updated_at, src.created_at)
FROM source_table src 
WHERE src.id > last_id 
ORDER BY src.id 
LIMIT batch_size;

这种写法由引擎一次性完成读取+计算+写入，避免游标 fetch 的逐行开销和锁持有时间拉长。只有当某行需调用 UDF、做跨表校验、或依赖上一行结果时，才考虑游标——而那通常说明设计已偏离批量场景本质。

真正容易被忽略的是：分批本身不解决锁竞争，只缓解单事务压力；如果目标表上有其他长事务正在更新同一主键范围，仍会等锁。上线前务必检查是否有未提交的事务占着 target_table。