如何提高增加包含大量记录的表的主键字段的效率

LazyBee

1 问题的提出：

在给客户升级数据库系统时，由于报表的需要，系统中每一个表都需要有主键字段。系统审计表自然也有这个要求—需要增加一个identify的字段，但这个表中有2000多万条记录，使用以下SQLl语句：alter table erAuditEventTime add EventTime_ID int IDENTITY primary key clustered来增加时需要4个多小时。客户要求我们对此进行提速。

2 抛砖--解决过程

问题出来之后，我第一时间上google去狂搜了一番，可是没有一条是关于对增加主键提速的。都是说建表增加主键提高效率的。这可怎么办？刚开始我以为是日志增长过快的原因，因为我在测试数据库上执行此语句时发现数据库日志文件在以“光速”狂飙，一段时间下来就长到十几个G，于是将数据库的恢复模式改成Simple(简单)，效率还是没有多大改善，于是认为会不会是锁的问题呢，但是由于SQL Server会根据情况自动将锁升级的，应该没有问题，不管怎么样还是给加上了with nocheck选项。好像依然没戏，不知什么时候“灵光”一现，我能不能采用拷贝策略呢，不管三七二十一先试试再说，于是将上面的语句改写成下面的方式：

 1--Rename table
 2exec sp_rename 'erAuditEventTime','zxg_erAuditEventTime'
 3go
 4--Copy table schema
 5select top 0 * into erAuditEventTime from zxg_erAuditEventTime 
 6go
 7--Add identify field
 8alter table erAuditEventTime 
 9add EventTime_ID int IDENTITY primary key clustered
10go
11--Copy data
12insert into erAuditEventTime select * from zxg_erAuditEventTime 
13go
14

改完之后，测试发现，完成这些语句需要38分26秒。欣喜……

3 反思

为什么将语句改成这种形式之后，效率能提高这么多呢？我觉得可能跟SQL Server的数据库物理存储有关。对此，让我们先了解一下SQL Server的物理存储：

数据库文件：SQL Server包含三种类型的数据库文件—主数据文件(Primary Data Files)、次要数据文件（Secondary data files）、日志文件（Log files）。主数据文件是数据库的起点，指向数据库中的其他文件。每个数据库都只有一个主数据文件（扩展名为.mdf）。除主数据文件以外的其他数据文件都是次要数据文件。有些数据库可能不含任何次要数据文件，而有些数据库则含有多个次要数据文件（扩展名为.ndf）。日志文件包含用于恢复数据库的所有日志信息。每个数据库至少有一个日志文件，当然也可以有多个（扩展名为.ldf）。数据库中所有文件的位置都记录在数据库的主文件和master数据库中。大多数情况下，SQL Server数据库引擎使用master数据库中的文件信息。只有在下列情况下，数据库引擎使用主数据文件的文件位置信息初始化master数据库中的文件位置项：还原master数据库时、使用带有For Attach或For ATTACH_REBUILD_LOG选项的Create Database语句来附加数据库时、从SQL Server2000升级到SQL Server2005时。

数据库文件组：文件组是命名的文件集合，为了便于分配和管理，可以将数据库对象和文件一起分成文件组。有两种类型的文件组：主文件组、用户定义文件组。主文件组包含主数据文件和没有明确分配给其他文件组的其他文件。系统表的所有页均分配在主文件组中。用户定义的文件组是通过在Create Database 或Alter DataBase语句中使用FILEGROUP关键字指定的任何文件组。（日志文件不包括在文件组内，日志空间和数据空间分开管理）。并且一个文件只能属于一个文件组。每个数据库中均有一个文件组被指定为默认文件组，如果创建表或索引时未指定文件组，则将假定所有页都从默认文件组分配。一次只能有一个文件组作为默认文件组。db_owner成员可以将默认文件组从一个文件组切换到另一个。如果没有指定默认文件组，则将主文件组作为默认文件组。

页（Page）：是SQL Server中存储数据的基本单位是页（Page），页的大小是8K，也就是SQL Server数据库中每MB中有128页。每页的开头是96个字节的标头，用于存储有关页的系统信息，包括页码、页的类型、页的可用空间以及拥有该页的对象的分配单元ID。下表说明SQL Server的数据库文件中使用的页类型：

注意：日志文件不包含页，而是包含一系列的日志记录。数据库的每个文件都有一个唯一的文件ID号，并且数据文件中的页是按顺序编号的，文件的首页以0开始。若要唯一表示数据库中的页，需要同时使用文件ID和页码。

区(Extents)：8个物理上连续的页为一个区（即64k）.区是SQL Server管理空间的基本单位，也就是说SQL Server为了提高效率，给对象分配空间时是以区为单位的，而不是以页为单位。为了使分配空间更有效，SQL Server不会将某一个区中的所有空间分配给包含少量数据的表。为此，SQL Server包含两种类型的区：统一区和混合区。统一区是由单个对象所有。区中的所有 8 页只能由所属对象使用。混合区，最多可由八个对象共享。区中八页的每页可由不同的对象所有。通常从混合区向新表或索引分配页。当表或索引增长到 8 页时，将变成使用统一区进行后续分配。如果对现有表创建索引，并且该表包含的行足以在索引中生成 8 页，则对该索引的所有分配都使用统一区进行。

结论：由于在对含有大量数据的erAuditEventTime表增加一个Identify字段的时，对每一行数据都需要进行变更，SQL Server为了保证同一行数据都位于同一页中，所以需要频繁移动原有页中的数据，导致大量而且频繁的IO操作；而采用另外新建一个表，然后使用insert into 语句来进行数据拷贝工作时，SQL Server只需要给新的表分配一系列的没有使用的统一区就可以了，大大减少了IO操作。而且这两种方式创建和保存索引的性能是一样的没有区别，这可能就是性能相差这么大的真正原因。

4 引玉

由于本人对SQL Server也不是特别懂，所以也请园子里的大虾们也发表发表高论，看看是否是这个原因导致的性能差异，以及针对这种案例是否有更好的解决方案？（转载请注明出处：http://lazybee.cnblogs.com/，谢谢！）