如何无锁关联删除MySQL中3个关联表的指定数据？

针对你的需求——删除表A中time_column早于指定日期的数据，同时关联删除表B、C中对应common_column的记录，直接关联删除会因数据量过大锁表，以下是基于LIMIT的分批删除实现方案：

前提优化：添加必要索引 #

先给表添加索引提升删除效率，减少锁表时间：

-- 给表A添加联合索引，加速待删数据筛选
CREATE INDEX idx_a_time_common ON table_a(time_column, common_column);
-- 给表B、C的关联字段添加索引
CREATE INDEX idx_b_common ON table_b(common_column);
CREATE INDEX idx_c_common ON table_c(common_column);

方案一：直接关联分批删除 #

不需要临时表，每次小批量删除关联数据：

1. 分批删除表B的关联数据 #

REPEAT
  DELETE b FROM table_b b
  INNER JOIN table_a a ON b.common_column = a.common_column
  WHERE a.time_column < '2020-04-17'
  LIMIT 1000; -- 批次大小可根据数据库负载调整，如500、2000
UNTIL ROW_COUNT() = 0 END REPEAT;

2. 分批删除表C的关联数据 #

REPEAT
  DELETE c FROM table_c c
  INNER JOIN table_a a ON c.common_column = a.common_column
  WHERE a.time_column < '2020-04-17'
  LIMIT 1000;
UNTIL ROW_COUNT() = 0 END REPEAT;

3. 分批删除表A的目标数据 #

REPEAT
  DELETE FROM table_a
  WHERE time_column < '2020-04-17'
  LIMIT 1000;
UNTIL ROW_COUNT() = 0 END REPEAT;

方案二：临时表存储待删ID（更高效） #

先把需要删除的common_column存入临时表，再基于临时表分批删除，避免重复关联查询表A：

1. 创建临时表并导入待删ID #

CREATE TEMPORARY TABLE tmp_delete_ids (
  common_column INT PRIMARY KEY
) ENGINE=InnoDB;

-- 导入表A中符合条件的common_column
INSERT INTO tmp_delete_ids
SELECT common_column FROM table_a
WHERE time_column < '2020-04-17';

2. 分批删除表B数据 #

REPEAT
  DELETE b FROM table_b b
  INNER JOIN tmp_delete_ids t ON b.common_column = t.common_column
  LIMIT 1000;
UNTIL ROW_COUNT() = 0 END REPEAT;

3. 分批删除表C数据 #

REPEAT
  DELETE c FROM table_c c
  INNER JOIN tmp_delete_ids t ON c.common_column = t.common_column
  LIMIT 1000;
UNTIL ROW_COUNT() = 0 END REPEAT;

4. 分批删除表A数据 #

REPEAT
  DELETE a FROM table_a a
  INNER JOIN tmp_delete_ids t ON a.common_column = t.common_column
  LIMIT 1000;
UNTIL ROW_COUNT() = 0 END REPEAT;

关键注意事项 #

• 批次大小调整：业务低峰期可适当调大批次（如2000-5000），高峰期调小（如500-1000），平衡删除效率与锁表影响。
• 外键处理：若表B、C与表A存在外键约束，需先删除子表（B、C）数据，再删除主表（A）数据，避免外键报错；若外键设置了ON DELETE CASCADE，可直接删除表A数据，但仍建议分批执行。
• 事务控制：无需强一致性时，不要将多批次删除放入同一事务，小事务能更快释放锁；若需一致性，可将单批次的B、C、A删除放入一个事务。
• 负载监控：执行过程中监控数据库锁状态、CPU及IO负载，根据实际情况调整批次大小。

内容的提问来源于stack exchange，提问作者CMGames