MySQL数据迁移如何降低锁表风险：分批次与频率控制技巧

INSERT INTO ... SELECT 在RR级别下会锁全表，因加临键锁；应确保SELECT走主键索引、改RC级别或分批基于id > 上次最大值推进。

为什么直接 INSERT INTO ... SELECT 会锁死整张表

很多DBA都踩过这个坑：在MySQL的可重复读（RR）隔离级别下，一句看似简单的INSERT INTO ... SELECT，就可能让整张源表“卡死”。问题出在哪？关键在于临键锁（next-key lock）。这个机制会让语句对SELECT扫描到的所有行及其间隙都上锁。哪怕你只想迁移10万行数据，如果查询条件覆盖了一个百万级的主键区间，这个区间就可能被全部锁定。

更棘手的情况是，当SELECT语句没有使用主键或唯一索引，而是进行了全表扫描或二级索引扫描时，锁的范围会变得难以预测，风险急剧升高。

那么，如何规避这个风险？有几个实操要点：

• 执行前，务必先用EXPLAIN检查SELECT部分是否真的走了主键或唯一索引。如果没有，优先考虑添加合适索引或改写查询语句。
• 尽量避免在业务高峰期执行此类操作。如果无法避开，一个有效的方案是临时将事务隔离级别改为READ COMMITTED（当然，前提是业务逻辑能够接受由此可能带来的幻读问题）。
• 特别注意，在autocommit=0的手动长事务中执行这类语句是“大忌”，因为锁会一直持有到事务结束，阻塞时间被无限拉长。

分批次 INSERT 的关键控制点：WHERE + LIMIT + ORDER BY

既然整批操作有风险，分批次处理就成了自然的选择。但分批次也有讲究，单纯使用LIMIT 1000进行简单分页并不可靠——如果源表在迁移过程中有并发写入，基于OFFSET的分页很可能导致数据重复或遗漏。

正确的做法，必须依赖一个有序且稳定的字段（通常是自增主键id）来推进。核心思路是：记录上一批处理的最大id，作为下一批的起始点。

来看一个正确的写法示例：

INSERT INTO target_table (col1, col2) SELECT col1, col2 FROM source_table WHERE id > 100000 AND id <= 101000 ORDER BY id;

这里有几个细节需要把握：

• WHERE条件必须与ORDER BY id配合使用，确保每次查询的结果集顺序绝对稳定。
• 批次大小的选择是个平衡艺术。建议控制在500到5000行之间。太小，则网络往返和事务开销过大；太大，则单次操作的锁压力和内存占用又会升高。
• 关键一步：必须记录每一批实际插入的最大id（注意，是本次查询结果中的最大值，而非整个表的MAX(id)），作为下一轮循环的起点。
• 如果目标表可能已存在部分记录，可以考虑使用INSERT IGNORE或ON DUPLICATE KEY UPDATE。但要清楚，这些语法在遇到冲突时，同样会对目标表的相应行加锁。

如何控制迁移频率避免压垮从库或主库

你以为分好批次就万事大吉了？另一个隐形杀手是频率。高频的小批次插入，看起来对主库压力不大，但却可能产生意想不到的副作用：事务提交过于频繁，会导致二进制日志（binlog）写入量激增，进而造成从库的SQL线程应用延迟，甚至引发主库的innodb_log_waits等待上升。

因此，节奏控制至关重要：

• 在每批插入操作之后，主动加入一个短暂的休眠，例如执行SELECT SLEEP(0.1);。这0.1秒的间隔，能有效缓解I/O和锁的竞争压力。
• 持续监控SHOW PROCESSLIST的输出。如果出现大量的Waiting for table metadata lock状态，这就是一个明确的警报，说明当前操作频率过高，或者存在长事务阻塞。
• 密切监控从库延迟。一旦延迟升高，应立刻降低迁移频率或暂停操作。使用pt-heartbeat等工具可以更精准地跟踪复制延迟。
• 最后强调一个原则性问题：为了提升迁移速度而将innodb_flush_log_at_trx_commit设置为2，这种做法仅限于测试环境。在生产环境中，绝不能以牺牲数据持久性为代价来换取速度。

真正容易被忽略的锁细节：UNIQUE 索引和间隙锁

即使你完美地按照主键分批查询，依然可能踏入另一个“锁陷阱”。如果目标表上存在非主键的UNIQUE唯一索引，那么在INSERT每一行数据时，MySQL不仅会锁住该行，还会对这个唯一索引值“应该所在位置”的前后间隙加锁（间隙锁）。

这会导致什么？它可能与其他正在执行的、恰好操作相邻唯一键值的业务语句（比如UPDATE ... WHERE unique_col = ）形成隐式的死锁。而错误日志往往只给出“Deadlock found”的模糊提示，排查起来非常困难。

如何排查和规避这类隐蔽问题？

• 迁移开始前，先用SHOW CREATE TABLE target_table;仔细检查所有UNIQUE索引。如果存在非主键的唯一约束，可以评估是否能在业务低峰期临时移除（DROP），待数据迁移完成后再重建（ADD）。这能从根本上避免间隙锁冲突。
• 利用information_schema库中的INNODB_TRX和INNODB_LOCK_WAITS表，实时观察事务和锁等待链，这是定位锁问题的利器。
• 在数据迁移窗口期内，尽量避免或严格审查那些会操作目标表唯一索引字段的并发DML语句，它们是最容易触发间隙锁冲突的“火花”。

说到底，分批迁移的逻辑本身并不复杂，真正的难点在于锁的传播路径不可见。一旦涉及唯一索引、二级索引扫描，或者存在并发数据修改，锁的行为就会变得反直觉。因此，最稳妥的建议是：在正式操作前，务必在测试环境，用接近真实的数据量和并发压力，完整地演练一遍整个迁移流程。