性能比较 - Identity() x NewId() x NewSequentialId

引言

NEWSEQUENTIALID 系统函数是 SQL Server 2005 的一个新增功能。它试图融合 SQL Server 2000 中原本相互冲突的需求：即，身份级别的插入性能和全局唯一的值。

背景

GUID（全局唯一标识符） 是伪随机的 16 字节（128 位）数字，其生成算法提供了足够高的概率保证在任何时间、任何计算机上都不会生成相同的 GUID。虽然 GUID 是一个二进制数组，但它通常以十六进制形式表示，格式如下，例如：dbbc2827-edf8-4a2d-92ad-c1e0059304d7。这使得它们非常适合我们 Orders 表的“分布式唯一性要求”。

如果我们没有一些非同寻常的性能要求，特别是关于插入量和速度，那么问题基本上就解决了。由于 GUID 本质上是伪随机的（微软为响应隐私担忧而改变了随机性/可预测性程度），当它们用作聚集索引键时，它们不像标识列那样具有相同的有序插入优势。使用 GUID 而非 IDENTITY 列作为聚集索引键时，插入的 IO 模式从顺序访问变为随机访问。GUID 不仅需要更多的 IO 来定位聚集索引中的插入位置（每次插入都需要从根节点遍历索引），而且叶子节点的碎片化会导致页面密度降低，这反过来又需要碎片整理/重新索引。

Using the Code

NEWSEQUENTIALID()

这时就轮到 **NEWSEQUENTIALID()** 粉墨登场了。这是 SQL Server 2005 中包含的一个新的系统函数，它可以结合两者的优点。NEWSEQUENTIALID() 将生成一个比之前生成的 GUID 值更大的 GUID。通过查看以下 TSQL 脚本生成的输出，可以很容易地理解此函数的工作原理：

create table TestTable ( 
   id uniqueidentifier default newsequentialid() 
      not null primary key clustered, 
   sequence int); 
go 

-- Insert 100 rows. 
declare @count int; 
set @count = 0; 
while @count < 100 begin 
   insert TestTable (sequence) 
   values (@count); 

   set @count = @count + 1; 
end; 
go 

select * 
from TestTable 
order by id; 
go

输出

id sequence 
------------------------------------ ----------- 
FA780E3B-03C2-DB11-BD9F-0011D82F3F23 0 
FB780E3B-03C2-DB11-BD9F-0011D82F3F23 1 
FC780E3B-03C2-DB11-BD9F-0011D82F3F23 2 
FD780E3B-03C2-DB11-BD9F-0011D82F3F23 3 
FE780E3B-03C2-DB11-BD9F-0011D82F3F23 4 
...
5C790E3B-03C2-DB11-BD9F-0011D82F3F23 98 
5D790E3B-03C2-DB11-BD9F-0011D82F3F23 99

您会注意到 SQL Server 正在采用一种清晰的位移模式来保持 GUID 值的相对递增。NEWSEQUENTIALID 的使用存在以下限制：NEWSEQUENTIALID 不能在任意 TSQL 语句中使用。它只能指定为 uniqueidentifier 列的 DEFAULT 值。一张表可以有多个列使用 NEWSEQUENTIALID。它不能与其他标量函数结合使用，例如，不允许使用 REVERSE(NEWSEQUENTIALID())。生成的 GUID 中很容易识别出网卡 MAC 地址。我的笔记本电脑的 MAC 地址是 00-01-4A-28-64-8B。下面是生成的 NEWSEQUENTIALID GUID 之一：4EAC7708-30C3-DB11-B902-00014A28648B。因此，BOL（Books Online）中有关于隐私的警告：将 GUID 中 MAC 地址的位置与每次连续 GUID 递增/移位的位数相关联，可以揭示 GUID 保持全局唯一性的原因。GUID 中 MAC 地址的部分保持不变。

性能比较

以下测试展示了 NEWSEQUENTIALID 的优势。测量并比较了各种聚集索引变体的插入性能。对于每个测试，都会使用不同的 Id 列定义重新创建相同的 _TestTable_。

IDENTITY() 
NEWID() 
NEWSEQUENTIALID()

以下 TSQL 包含用于构造三个聚集键的表的 DDL。

-- IDENTITY 
create table TestTable ( 
   id int identity(1,1) not null primary key clustered, 
   sequence int not null, 
   data char(250) not null default ''); 
go 

-- NEWID 
create table TestTable ( 
   id uniqueidentifier default newid() not null primary key clustered, 
   sequence int not null, 
   data char(250) not null default ''); 
go 

-- NEWSEQUENTIALID 
create table TestTable ( 
   id uniqueidentifier default newsequentialid() not null primary key clustered, 
   sequence int not null, 
   data char(250) not null default ''); 
go

对于每个测试，使用以下 TSQL 脚本将 50,000 行插入到 TestTable 表中。

-- Insert 50,000 rows. 
declare @count int; 
set @count = 0; 
while @count < 50000 begin 
   insert TestTable (sequence) 
   values (@count); 

   set @count = @count + 1; 
end; 
go

每次运行后收集了以下指标。

-- Get the number of read / writes for this session... 
select reads, writes 
from sys.dm_exec_sessions 
where session_id = @@spid; 

-- Get the page fragmentation and density at the leaf level. 
select index_type_desc, index_depth, page_count, 
       avg_page_space_used_in_percent, avg_fragmentation_in_percent, record_count 
from sys.dm_db_index_physical_stats(db_id(), 
               object_id('TestTable'), null, null, 'detailed') 
where index_level = 0; 
go

结果非常令人信服。

	读取	写入	叶子页	平均页面使用率	平均碎片	记录数
IDENTITY()	0	1,683	1,667	98.9%	0.7%	50,000
NEWID()	0	5,386	2,486	69.3%	99.2%	50,000
NEWSEQUENTIALID()	0	1,746	1,725	99.9%	1.0%	50,000

结论

最引人注目的是 **NEWID** 系统函数所需的写入次数。这一点，加上 **69%** 的平均页面密度，证明了由于叶子节点插入的随机分布而导致的页面拆分。一旦页面填满，就需要将其拆分为两个 50% 的页面才能完成插入。页面拆分不仅导致页面密度低下，还严重导致了数据页的碎片化（下一个数据页与当前数据页不相邻的概率为 **99%**）。在我们的测试中，页面拆分所需的空闲页最可能位于表的末尾，无论行被插入到哪里。因此，要按顺序读取行，扫描需要不断地在广泛分布的拆分页面之间来回跳转，从而导致了糟糕的碎片化。在 IDENTITY 和 NEWSEQUENTIALID 指标之间的页面计数上的细微差异是由于 IDENTITY 的 INT（4 字节）和 **NEWSEQUENTIALID** 的 **UNQUEIDENTIFIER**（16 字节）之间的大小差异造成的。然而，请注意，在 UNIQUEIDENTIFIER 表上的任何非聚集索引将比 IDENTITY 表上的相同索引大四倍。因此，在两者之间选择时，IDENTITY 仍然优于 NEWSEQUENTIALID。**NEWSEQUENTIALID** 系统函数显然实现了其承诺，即提供类似 GUID 的唯一性以及类似身份级别的插入性能。写入次数、碎片化和页面密度都与身份级别指标一致。这些优势使得 NEWSEQUENTIALID 成为一个引人注目的特性。