学步园

 

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

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的数据库文件中使用的页类型：

页类型	内容
Data	当 text in row设置为 ON 时，包含除 text、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max)和 xml数据之外的所有数据的数据行。
Index	索引条目。
Text/Image	大型对象数据类型： ·                      text、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max)和 xml数据。 数据行超过 8 KB 时为可变长度数据类型列： ·                      varchar、nvarchar、varbinary和 sql_variant
Global Allocation Map、Shared Global Allocation Map	有关区是否分配的信息。
Page Free Space	有关页分配和页的可用空间的信息。
Index Allocation Map	有关每个分配单元中表或索引所使用的区的信息。
Bulk Changed Map	有关每个分配单元中自最后一条 BACKUP LOG 语句之后的大容量操作所修改的区的信息。
Differential Changed Map	有关每个分配单元中自最后一条 BACKUP DATABASE 语句之后更改的区的信息。

注意：日志文件不包含页，而是包含一系列的日志记录。数据库的每个文件都有一个唯一的文件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/，谢谢！）