INSERT INTO ... SELECT 在RR级别下会锁全表，因加临键锁；应确保SELECT走主键索引、改RC级别或分批基于id > 上次最大值推进。
为什么直接 INSERT INTO ... SELECT 会锁死整张表
MySQL 在可重复读（RR）隔离级别下，INSERT INTO ... SELECT 默认会对源表执行 SELECT 范围内的所有行加临键锁（next-key lock），哪怕只查 10 万行，也可能锁住百万级主键区间。尤其当源表有大范围 WHERE 条件、没走索引或扫描二级索引时，锁范围更难预测。
实操建议：
- 用
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)，而是本次查询实际插入的最大id），用于下轮WHERE - 跳过已存在的目标记录？用
INSERT IGNORE或ON DUPLICATE KEY UPDATE，但注意它们也会对冲突行加锁
如何控制迁移频率避免压垮从库或主库
高频小批次插入看似“温和”，但可能因事务提交太密，导致 binlog 写入陡增、从库 SQL 线程积压、甚至主库 innodb_log_waits 上升。
实操建议：
- 每批插入后加
SLEEP(0.1)（单位秒），例如：SELECT SLEEP(0.1);，缓解 I/O 和锁竞争 - 监控
SHOW PROCESSLIST中是否有大量Waiting for table metadata lock，出现即说明频率过高或有长事务阻塞 - 从库延迟高时，主动降低批次频率或暂停迁移；可用
pt-heartbeat实时跟踪延迟 - 禁用
innodb_flush_log_at_trx_commit=2仅限测试环境；生产环境切勿为提速牺牲持久性
真正容易被忽略的锁细节：UNIQUE 索引和间隙锁
即使你只按主键分批查，如果目标表有 UNIQUE 索引（非主键），INSERT 过程中仍会对该索引的插入位置前后间隙加锁——这可能导致和其它业务语句发生隐式死锁，而错误日志里只显示 Deadlock found when trying to get lock，不提示具体哪条语句。
排查与规避：
- 执行前用
SHOW CREATE TABLE target_table;检查所有UNIQUE索引；如有非主键唯一约束，考虑临时 DROP，迁移完再 ADD（前提是业务允许短暂缺失约束） - 用
SELECT * FROM information_schema.INNODB_TRX;和INNODB_LOCK_WAITS查看实时锁等待链 - 避免在迁移期间执行
UPDATE ... WHERE unique_col = ?类语句，极易撞上间隙锁
分批逻辑本身不复杂，难点在于锁的传播路径不可见——一旦涉及唯一索引、二级索引扫描、或并发 DML，锁行为就脱离直觉。动手前务必在测试库用真实数据量+真实并发压测一遍。
