从大型生产表中复制数据

Question

我目前工作的公司有一些巨大的日志/记录表，每隔10秒左右就会写入一些事务。我想从这个表中复制出很多数据，并从该表中删除它们，因为约75%的旧数据可以放在归档表中，但如果我操作不当并且锁定了该表，那将是一场灾难。

在之前的一个问题中，有人提出了类似以下的建议，我想知道这样做是否会导致问题，nolock提示是否足以确保安全并使所有写操作正常工作？如果不是，我应该怎么做？

set identity_insert newtable on
DECLARE @StartID bigint, @LastID bigint, @EndID bigint
select @StartID = max(id)+1
from newtable

select @LastID = max(ID)
from oldtable

while @StartID < @LastID
begin
set @EndID = @StartID + 1000000

insert into newtable (FIELDS,GO,HERE)
select FIELDS,GO,HERE from oldtable (NOLOCK)
where id BETWEEN @StartID AND @EndId

set @StartID = @EndID + 1
end
set identity_insert newtable off
go

- master_cylinder

---

这很好，但你有删除旧记录的计划吗？没有某种形式的锁定是无法做到的。 - JNK

是的，我稍微澄清了一下问题，那么在不妨碍正常数据库操作的情况下，删除旧记录的最佳方法是什么？ - master_cylinder

Answer 1

生成列表时的极度谨慎可能有些过头，但您需要运行批量删除。

对于INSERT，您可能不需要WHILE循环。对于DELETE，我建议使用以下代码（根据需要调整批处理大小）：

WHILE 1=1
BEGIN
   DELETE TOP (10000) o
   FROM OldTable o
   INNER JOIN NewTable N
      ON o.id = n.id
   IF @@ROWCOUNT < 10000 BREAK;
END

只要有需要删除的记录，这段代码每次会一次性 DELETE 1万条记录。

- JNK

---

我以前用一个大表做过类似的事情，分批归档和删除记录。基本上它看起来像你的代码，但是在 while 循环内部有一个 "begin tran" 和一个 "commit tran"。然后我备份、截断和收缩了日志。 - Tim Lehner

如果他担心争用问题，我不会执行事务，因为那会锁定表 :) - JNK

谢谢JNK，所以您建议我先复制再删除？还有其他需要担心的事情吗？抱歉，我只是非常谨慎，但我认为这似乎可以起作用。 - master_cylinder

是的，我会说操作顺序是：1-复制，2-验证，3-删除。 - JNK

Answer 2

一种选择是按小时对表进行分区（假设您在表中有一个DATETIME列，该列在每次插入时默认为GETDATE()）。拥有这些分区可以让您在不影响当前分区的情况下对旧分区进行维护（删除、复制等）。

- sazh

---

他关注的是要锁定足够长的时间来进行选择操作，而你建议添加分区方案？我很确定这也会导致一些锁定问题... - JNK

@JNK 是的，但这可能是未来计划停机的一部分。 - Alan Barber