sql-server - 从 SQL Server 中的多个表中删除多条记录的最佳做法

Question

有一个包含数百万条记录的父表和三个外键指向父表主键的子表。像这样:

  Parent
 parent_id (PK) \       Child1
                |     child1_id (PK)
                |---- parent_id (FK)
                |
                |      Child2
                |     child2_id (PK)
                |---- parent_id (FK)
                |
                |      Child3
                |     child3_id (PK)
                |---- parent_id (FK)

从 Parent 中硬删除数十万条记录的最佳做法是什么？我想在以下条件下删除:DELETE FROM PARENT WHERE [STATUS] = 'DONE'。有没有办法在删除发生时不锁定表？以便其他记录可以插入到所有这些表中？我能想到的选项:

1. 在外键上使用CASCADE DELETE。
2. 使用软删除:开始事务，UPDATE parent SET [DELETED] = 1 WHERE [STATUS] = 'DONE'，删除具有这些父 ID 的每个子项，然后硬删除父项和 promise 。
3. 类似于 2. 但使用过程并将要删除的那些 ID 保存在表变量中，这样我就不需要将新的 [DELETED] 列添加到 Parent 表。
4. 选择要删除的 ID SELECT parent_id FROM parent WHERE [STATUS] = 'DONE' 然后通过所有这些 ID 进行批量删除。 (这表现得非常糟糕，所以我要放弃它)。

我正在使用 SQL Server 2014 和 spring jdbc。

Answer 1

我更喜欢使用TOP x批量删除

所以对于每个子表:

DELETE TOP 10000
FROM child1 
FROM child 1 as c1
INNER join parent
On parent_Id = c1.parent_id
AND parent.[STATUS] = 'DONE'

为每个子表重复多个批处理。

您可以定期删除没有子项的父项记录。

DELETE TOP 10000
FROM parent 
FROM parent as p
Left outer join child1 c1
On p.parent_Id = c1.parent_id
AND c1.child_id IS NULL
 Left outer join child2 c2
On p.parent_Id = c2.parent_id
AND c2.child_id IS NULL
Left outer join child3 c3
On p.parent_Id = c3.parent_id
AND c3.child_id IS NULL
WHERE parent.[STATUS] = 'DONE'

每个父项有多少个子项将决定您运行父项删除的频率。你当然可以改变 X 我会测试小，然后增加说 50000