根本不用“避免”——锁扩散是REPEATABLE READ下SELECT部分的默认行为，关键在控制它：InnoDB对扫描范围加shared next-key lock，需建索引、避免全表扫描、分批处理、用临时表解耦，并结合EXPLAIN和锁信息精准判断。
根本不用“避免”——锁扩散是REPEATABLE READ下SELECT部分的默认行为，关键在控制它。你看到的“源表被锁住、其他INSERT/UPDATE卡住”，不是语句写错了，而是InnoDB在当前隔离级别下对扫描范围加了shared next-key lock（记录锁+间隙锁）。下面说清楚怎么做、为什么、以及最容易翻车的地方。
为什么INSERT INTO SELECT会锁住大片源表数据
问题不在INSERT，而在SELECT部分的读取方式：在默认REPEATABLE READ下，InnoDB不做快照读，而是做“当前读”，对所有扫描到的索引记录及其间隙都加共享临键锁。哪怕你只查WHERE status = 'done'，若status没索引，就会全表扫描 → 锁整个主键索引段。
- 全表扫描 = 扫多少行，就加多少个next-key lock，等效于“逻辑锁表”
- JOIN多表时，每张被驱动表都可能被加锁，锁范围指数级扩大
-
ORDER BY RAND()或LIMIT不带确定性WHERE，会导致重复扫描和锁升级
必须加索引，且WHERE条件要能走索引
这是成本最低、见效最快的控制手段。没有索引，其他所有优化都是补漏。
- 先用
EXPLAIN SELECT * FROM source_table WHERE ...确认type是range或ref，而不是ALL - 避免在WHERE里对字段做函数操作，比如
WHERE DATE(created_at) = '2026-10-01'→ 改成WHERE created_at >= '2026-10-01' AND created_at - 如果按时间迁移，建
INDEX idx_created_at (created_at)；如果按ID分批，确保主键或自增列可用，别依赖OFFSET
分批执行比调隔离级别更可靠
降为READ COMMITTED确实能让SELECT部分变快照读、不加锁，但它治标不治本：一旦SELECT本身要扫100万行，CPU、IO、网络都在扛压，事务提交慢，目标表的exclusive record lock照样拖长整体耗时。
面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。
- 用
WHERE id BETWEEN ? AND ?或WHERE id > ? ORDER BY id LIMIT 5000切分，每次只读5k~1w行 - 每次
INSERT INTO ... SELECT ...后立刻COMMIT，不要包在大事务里 - 避免用
SELECT ... ORDER BY id LIMIT ?, 5000，OFFSET越大越慢，且可能跳过/重复数据 - 如果源表无主键，宁可先加一个
created_at并建索引，也别用OFFSET
临时表方案适合高敏感核心表
当源表是orders、payments这类不能容忍任何读锁的业务表时，临时表是唯一能彻底解耦的方式。
- 先
CREATE TEMPORARY TABLE tmp AS SELECT ... FROM source_table WHERE ...—— 这步只锁源表极短时间 - 再
INSERT INTO target SELECT * FROM tmp—— 源表已完全释放 - 注意：
TEMPORARY TABLE只在当前会话可见；如需并发多线程协作，改用普通表CREATE TABLE tmp_20261104 LIKE target，最后DROP - 大批量时监控
innodb_buffer_pool_size，临时表不写redo，但刷脏压力可能陡增
真正难的不是语法，是判断哪部分锁是必须的、哪部分可以砍掉。比如INSERT INTO t2 SELECT * FROM t1 WHERE x = ?，如果你确认x有唯一索引，那锁就只落在那一行+间隙；但如果x只是普通索引、且值大量重复，next-key lock仍可能覆盖一大片。这种细节，不看EXPLAIN和SHOW ENGINE INNODB STATUS里的lock信息，光靠调参数是蒙不准的。
