更新大表并最大程度地减少用户影响 [英] Updating a large table and minimizing user impact

问题描述

我对一般的数据库/ sql服务器设计有疑问：

I have a question on general database/sql server designing:

有一个表，该表包含300万行，需要24x7全天候访问。我需要更新表中的所有记录。您能给我一些方法来做到这一点，以便在我更新表时最大程度地减少用户影响吗？

There is a table with 3 million rows that is being accessed 24x7. I need to update all the records in the table. Can you give me some methods to do this so that the user impact is minimized while I update my table?

预先感谢。

推荐答案

通常，您会编写一条update语句来更新行。但是，在您的情况下，您实际上想将其分解。

Normally you'd write a single update statement to update rows. But in your case you actually want to break it up.

http://www.sqlfiddle.com/#!3/c9c75/6
是常见模式的有效示例。您不希望批次大小为2，也许您希望为100,000或25,000-您必须在系统上进行测试，才能确定快速完成和低阻塞之间的最佳平衡。

http://www.sqlfiddle.com/#!3/c9c75/6 Is a working example of a common pattern. You don't want a batch size of 2, maybe you want 100,000 or 25,000 - you'll have to test on your system to determine the best balance between quick completion and low blocking.

declare @min int, @max int

select @min = min(user_id), @max = max(user_id)
from users

declare @tmp int
set @tmp = @min

declare @batchSize int
set @batchSize = 2


while @tmp <= @max
begin
  print 'from ' + Cast(@tmp as varchar(10)) + ' to ' + cast(@tmp + @batchSize as varchar(10)) + ' starting (' + CONVERT(nvarchar(30), GETDATE(), 120) + ')'
  update users
  set name = name + '_foo'
  where user_id >= @tmp and user_id < @tmp + @batchsize and user_id <= @max

  set @tmp = @tmp + @batchSize
  print 'Done  (' + CONVERT(nvarchar(30), GETDATE(), 120) + ')'

  WAITFOR DELAY '000:00:001'

end
update users
set name = name + '_foo' 
where user_id > @max

我们使用这种模式来更新用户表，大小约为表大小的10倍。如果有100,000个块，则大约需要一个小时。性能当然取决于您的硬件。

We use patterns like this to update a user table about 10x your table size. With 100,000 chunks it takes about an hour. Performance depends on your hardware of course.

这篇关于更新大表并最大程度地减少用户影响的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！