如何借助CASCADE DELETE实现子表记录的批量删除？

直接通过删除父表记录触发CASCADE DELETE会一次性删除该用户的所有百万条子表消息，这会导致事务过大、锁表时间过长，甚至引发数据库性能问题或超时。要实现批量删除，你需要拆分删除操作，分批次清理子表数据，再利用CASCADE完成收尾：

方案一：先分批清理子表，再删除父表记录 #

这是最直接的方案，先手动分批删除子表中目标用户的消息，最后删除父表用户（此时CASCADE仅处理少量遗漏数据）。

1. 分批删除子表记录 #

根据你的数据库类型，使用循环语句每次删除固定数量的记录（示例以SQL Server为例，其他数据库调整语法）：

-- SQL Server 版本
WHILE EXISTS (SELECT 1 FROM messages WHERE userid = '目标用户ID')
BEGIN
    -- 每次删除1000条，可根据数据库性能调整批次大小
    DELETE TOP (1000) FROM messages WHERE userid = '目标用户ID'
    -- 可选：每次删除后暂停1秒，降低数据库负载
    WAITFOR DELAY '00:00:01'
END

其他数据库对应语法：

• MySQL：

  WHILE EXISTS (SELECT 1 FROM messages WHERE userid = '目标用户ID') DO
      DELETE FROM messages WHERE userid = '目标用户ID' LIMIT 1000;
      DO SLEEP(1);
  END WHILE;
  

• PostgreSQL：

  LOOP
      DELETE FROM messages WHERE userid = '目标用户ID' LIMIT 1000;
      EXIT WHEN NOT FOUND;
      PERFORM pg_sleep(1);
  END LOOP;
  

2. 删除父表用户记录 #

子表数据清理完成后，执行父表删除操作，此时CASCADE DELETE只会处理可能遗漏的少量记录：

DELETE FROM users WHERE userid = '目标用户ID';

关键注意事项 #

• 批次大小调整：根据数据库服务器性能、业务低峰期负载，调整每次删除的记录数（比如500-5000条），平衡删除效率和数据库压力。
• 业务低峰执行：尽量在业务流量较小时操作，避免影响线上服务。
• 监控数据库状态：删除过程中关注CPU、IO、事务日志占用，及时调整批次或暂停间隔。

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