![如何加快在 SQL Server 中向大型表添加列的速度](https://linux22.com/image/487330/%E5%A6%82%E4%BD%95%E5%8A%A0%E5%BF%AB%E5%9C%A8%20SQL%20Server%20%E4%B8%AD%E5%90%91%E5%A4%A7%E5%9E%8B%E8%A1%A8%E6%B7%BB%E5%8A%A0%E5%88%97%E7%9A%84%E9%80%9F%E5%BA%A6.png)
我想向一个包含约 1000 万行的 SQL Server 表添加一列。我认为这个查询会最终完成添加我想要的列:
alter table T
add mycol bit not null default 0
但它已经持续了几个小时。有没有捷径可以将“非空默认 0”列插入到大型表中?或者这本身就很慢?
这是 Sql Server 2000。稍后我必须在 Sql Server 2008 上做类似的事情。
答案1
根据您的行大小、表大小、索引等,我看到 SQL Server 2000 需要磨合几个小时(4-5 个小时)才最终完成。
你现在能做的最糟糕的事情就是“惊慌失措”并强行杀死它。让它自己跑出去。
将来,您可能希望尝试按照 Farseeker 所提到的操作,创建第二个(空)结构并以此方式复制您的记录。
- 表格行越长,花费的时间越长。
- 该表上的索引越多,所需的时间就越长。
- 如果您添加默认值(您已添加),则需要更长时间。
- 如果服务器使用量很大,则需要更长时间。
- 如果您不锁定该数据库或将其置于单用户模式,则需要更长时间。
当我不得不做这种丑陋的事情时,我会尝试在晚上做...比如凌晨 2 点,那时没人在(并且服务器没有进行维护)。
祝你好运! :-)
答案2
嗯,1000 万行是相当多的,但它并不超出 MSSQL 的范围,而且似乎非常慢。
我们有一张表,行数非常大(设计不佳),超过 1000 万行。当我们必须修改结构时,速度确实非常慢,所以我们做的是(让表保持在线,这是记忆中的粗略记录,因为那是很久以前的事了):
- 创建了带有后缀“C”(表示转换)和新结构(即与旧结构相同,但具有新的列/索引/等)的新表
- SELECT * INTO 表 C FROM 表
- sp_rename ‘表’ ‘表旧’
- sp_rename ‘表 C’ ‘表’
这样,转换需要多长时间就无关紧要了,因为旧数据是在线的。不过,在转换过程中,这可能会导致行写入表时出现问题(这对我们来说不是问题,因为数据每天只写入一次,但每小时查询数千次),所以您可能需要调查一下。
答案3
您可以尝试在单独的批处理中执行操作的每个步骤,例如
alter table T add mycol bit null
go
update T set mycol = 0
go
alter table T alter column mycol bit not null
go
alter table T add default 0 for mycol
go
优点是:
- 您可以获得有关操作进度的更好的反馈,因为现在有 4 个单独的批次,每个批次大约需要 1/4 的时间。
- 它降低了从客户端代码运行时出现超时错误的可能性。
- 我发现它有时可以提高性能。
您还可以尝试在进行更改之前删除表上的所有非聚集索引,然后恢复它们。添加列可能会涉及大规模页面拆分或其他低级重新排列,并且您可以在此过程中避免更新非聚集索引的开销。
答案4
我曾经在至少有 6500 万行的表中做过类似的事情,而且并没有花那么长时间。你的磁盘系统是否有足够的内存和足够的性能
如果您想加快该过程,您可以在更改表之前删除除聚集索引和外键约束之外的所有索引,但这必须在系统未使用时进行,否则您可能会得到不一致的数据。但最终您需要在完成之前应用外键和索引,但这样可以减轻事务日志的痛苦,至少如果您在简单恢复模式下运行的话。在 SQL Server 2008 中,您可以使用 ONLINE=on 和 SORT_IN_TEMPDB=on 构建索引
哈坎·温瑟