第11周 翻译——《Pro SQL Server Internals, 2nd edition》中的Clustered Index Design Considerations一节

原文来源：《Pro SQL Server Internals, 2nd edition》的CHAPTER 7 Designing and Tuning the Indexes中的Clustered Index Design Considerations一节（即P155~P165）

作者：Dmitri Korotkevitch

                                   聚集索引设计注意事项

    每次更改聚集索引键的值时,都会发生两件事。首先, SQL Server将行移动到聚集索引页链和数据文件中的不同位置。其次,它更新行id,这是聚集索引键。行id存储在所有非聚集索引中,需要更新。就I/O而言,这可能非常昂贵,尤其是在批更新的情况下。此外,它可以增加聚集索引的碎片,在行id大小增加的情况下,还可以增加非聚集索引的碎片。因此,最好有一个不改变键值的静态聚集索引。
    所有非聚集索引都使用聚集索引键作为行id。太宽的聚集索引键会增加非聚集索引行的大小,并需要更多空间来存储它们。因此, SQL Server需要在索引或范围扫描操作期间处理更多的数据页,这使得索引的效率更低。
    在非惟一非聚集索引的情况下,行id也存储在非叶索引级别,这反过来减少了每页索引记录的数量,并可能导致索引中额外的中间级别。尽管非叶索引级别通常缓存在内存中,但每当 SQL Server遍历非集群索引B树时,都会引入额外的逻辑读取。
    最后,较大的非聚集索引使用缓冲池中的更多空间,并在索引维护期间引入更多开销。显然,不可能提供一个通用阈值来定义可应用于任何表的键的最大可接受大小。但是,作为一般规则,最好有一个窄的聚集索引键,索引键越小越好。
    将聚集索引定义为惟一索引也是有益的。这一点重要的原因并不明显。考虑这样一种场景：一个表没有惟一的聚集索引,而您希望在执行计划中运行一个使用非聚集索引查找的查询。在本例中,如果非聚集索引中的行id不是惟一的,SQL Server将不知道在键查找操作期间选择什么聚集索引行。
    SQL Server通过向非惟一聚集索引添加另一个名为唯一标识符的可空整数列来解决这些问题。SQL Server为键值的第一次出现使用NULL填充唯一标识符,为插入到表中的每个后续副本自动递增该值。
    ■注意,每个聚集索引键值的可能重复数受整数域值的限制。具有相同聚集索引键的行不能超过2,147,483,648行。这是一个理论上的限制,创建选择性如此差的索引显然不是一个好主意。
    让我们看看唯一标识符在非惟一聚集索引中引入的开销。列表7-1中所示的代码创建了三个相同结构的不同表,井用65,536行填充它们。表dbo.UniqueCI是惟一定义了惟一聚集索引的表。表dbo.NonUniqueCINoDupsdoes没有任何重复的键值。最后,表dbo.NonUniqueCDups在索引中有大量的副本。
    列表7-1。非唯一聚集索引：表创建

create table dbo.UniqueCI
(
KeyValue int not null,
ID int not null,
Data char(986) null,
VarData varchar(32) not null
constraint DEF_UniqueCI_VarData
default 'Data'
);

create unique clustered index IDX_UniqueCI_KeyValue
on dbo.UniqueCI(KeyValue);

create table dbo.NonUniqueCINoDups
(
KeyValue int not null,
ID int not null,
Data char(986) null,
VarData varchar(32) not null
constraint DEF_NonUniqueCINoDups_VarData
default 'Data'
);

create /*unique*/ clustered index IDX_NonUniqueCINoDups_KeyValue
on dbo.NonUniqueCINoDups(KeyValue);

create table dbo.NonUniqueCIDups
(
KeyValue int not null,
ID int not null,
Data char(986) null,
VarData varchar(32) not null
constraint DEF_NonUniqueCIDups_VarData
default 'Data'
);

create /*unique*/ clustered index IDX_NonUniqueCIDups_KeyValue

on dbo.NonUniqueCIDups(KeyValue);

-- Populating data
;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 rows
,N5(C) as (select 0 from N4 as T1 cross join N4 as T2) -- 65,536 rows
,IDs(ID) as (select row_number() over (order by (select null)) from N5)
insert into dbo.UniqueCI(KeyValue, ID)
select ID, ID from IDs;

insert into dbo.NonUniqueCINoDups(KeyValue, ID)
select KeyValue, ID from dbo.UniqueCI;

insert into dbo.NonUniqueCIDups(KeyValue, ID)
select KeyValue % 10, ID from dbo.UniqueCI;

现在，让我们看看每个表的聚集索引的物理统计信息。代码如列表7-2所示，结果如图7-1所示。

列表7-2.非唯一聚集索引：检查聚集索引的行大小

select index_level, page_count, min_record_size_in_bytes as [min row size]
,max_record_size_in_bytes as [max row size]
,avg_record_size_in_bytes as [avg row size]
from
sys.dm_db_index_physical_stats(db_id(), object_id(N'dbo.UniqueCI'), 1, null ,'DETAILED');

select index_level, page_count, min_record_size_in_bytes as [min row size]
,max_record_size_in_bytes as [max row size]
, avg_record_size_in_bytes as [avg row size]
from
sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.NonUniqueCINoDups'), 1, null ,'DETAILED');

select index_level, page_count, min_record_size_in_bytes as [min row size]
,max_record_size_in_bytes as [max row size]
,avg_record_size_in_bytes as [avg row size]
from
sys. dm_db_index_physical_stats(db_id(), object_id(N'dbo.NonUniqueCIDups'), 1, null ,'DETAILED');

图7-1.非聚集索引：聚集索引的行大小

即便dbo.NonUniqueCINoDups表中没有重复的键值,但仍然有两个额外的字节添加到行中。 SQL Server在数据的可变长度部分中存储一个唯一标识符,这两个字节由可变长度数据抵消数组中的另一个条目添加。

在这种情况下,当聚集索引有重复的值时,唯一标识符又添加了4个字节,这就造成了总共6个字节的开销。    

值得一提的是,在某些边缘情况下,唯一标识符使用的额外存储空间可以减少数据页上的行数。我们的示例演示了这种情况。正如您所看到的dbo.UniqueCI使用的数据页比其他两个表少15%。

    现在,让我们看看唯一标识符如何影响非聚集索引。列表7-3所示的代码在所有三个表中创建非聚集索引。图7-2显示了这些索引的物理统计数据。

    列表7-3。非唯一聚集索引检查非聚集索引的行大小

create nonclustered index IDX_UniqueCI_ID
on dbo.UniqueCI(ID);

create nonclustered index IDX_NonUniqueCINoDups_ID
on dbo.NonUniqueCINoDups(ID);

create nonclustered index IDX_NonUniqueCIDups_ID
on dbo.NonUniqueCIDups(ID);

select index_level, page_count, min_record_size_in_bytes as [min row size]
,max_record_size_in_bytes as [max row size]
,avg_record_size_in_bytes as [avg row size]
from
sys. dm_db_index_physical_stats(db_id(), object_id(N'dbo.UniqueCI'), 2, null ,'DETAILED');

select index_level, page_count, min_record_size_in_bytes as [min row size]
,max_record_size_in_bytes as [max row size]
,avg_record_size_in_bytes as [avg row size]
from
sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.NonUniqueCINoDups'), 2, null ,'DETAILED');

select index_level, page_count, min_record_size_in_bytes as [min row size]
,max_record_size_in_bytes as [max row size]
,avg_record_size_in_bytes as [avg row size]
from
sys. dm_db_index_physical_stats(db_id(), object_id(N'dbo.NonUniqueCIDups'), 2, null ,'DETAILED');

图7-2.非唯一聚集索引：非聚集索引的行大小

 dbo.NonUniqueCINoDups表中的非聚集索引没有开销。您应该还记得,SQL  Server并不将偏移量信息存储在可变长度抵消数组中,用于存储NULL数据的牵引列。尽管如此, 唯一标识符在dbo.NonUniqueCIDups表中引入了8个字节的开销。这8个字节由4字节 唯一标识符值、2字节可变长度数据抵消数组条目和存储行中可变长度列数的2字节条目组成。

    我们可以用以下方式总结唯一标识符的存储开销。对于将唯一标识符为NULL的行如果索引至少有一个存储不为空值的变长列,则会有两个字节的开销。这个开销来自唯一标识符列的可变长度抵消数组条目。否则就没有开销。

    在填充唯一标识符的情况下,如果有存储不为空值的可变长度列,则开销为6个字节。否则,开销是8个字节。

    ■ 提示:如果希望聚集索引值中有大量重复项，可以添加一个整数标识列作为索引的最石边列,从而使其唯一。与唯一标识符引入的不可预测的高达8字节的存储开销相比,这为每一行增加了4字节的可预测存储开销。当您通过行的所有聚集索引列引用该行时,这还可以提高单个查找操作的性能。

    以一种最小化插入新行导致的索引碎片的方式设计聚集索引是有益的。实现这一点的方法之一是使聚集索引值不断增加。 identity上的索引就是这样一个例子。另个例子是 datetime列,它在插入时填充了当前系统时间。

然而,索引不断增长存在两个潜在问题。第一个与统计有关。正如您在第3章中了解到的,当直方图中没有参数值时, SQL Server中的遗留基数估计器会低估基数。您应该将这种行为考虑到系统的统计维护策略中,除非您使用新的 SQL Server2014-2016基数估计器,该估计器假定直方图之外的数据具有与表中其他数据类似的分

    下一个问题更加复杂。随着索引的不断增加,数据总是插入到索引的未尾。一方面,它可以防止页面分裂和减少碎片。另一方面,它可能导致热点,即当多个会话试图修改相同的数据页和/或分配新页或区段时发生的序列化延迟。 SQL Serve不允许多个会话更新相同的数据结构,而是序列化这些操作。

    热点通常不是问题,除非系统以非常高的速度收集数据,并且索引每秒处理数百个插入。我们将在第27章“系统故障排除"中讨论如何检测这样的问题。

    最后,如果一个系统有一组频繁执行的重要查询,那么考虑一个集群索引可能是有益的,它可以优化这些查询。这消除了昂贵的键査找操作,并提高了系统的性能。

尽管可以通过覆盖非聚集索引来优化此类查询,但它并不总是理想的解决方案。在某些情况下,它要求您创建非常广泛的非集群索引,这会占用磁盘和缓冲池中的大量存储空间

 另一个重要因素是修改列的频率。将经常修改的列添加到非集群索引需要SQL Server在多个位置更改数据,这将对系统的更新性能产生负面影响并增加阻塞。

  综上所述,设计能够满足所有这些准则的聚集索引并不总是可能的。此外,您不应该认为这些指南是绝对的需求。您应该分析系统、业务需求、工作负载和查询,并选择对您有利的集群索引,即使它们违反了其中的一些准则。

    标识符、序列和唯一标识符

 

    人们通常选择标识符、序列和唯一标识符作为聚集索引键。与往常一样,这种方法也有自己的优缺点。

    在这些列上定义的聚集索引是惟一的、静态的和窄的。此外,标识符和序列不断增加,这减少了索引碎片。它们的理想用例之一是目录实体表。您可以考虑以表为例,其中存储客户、文章或设备的列表。这些表存储数千行,甚至几百万行,尽管数据相对静态,因此热点不是问题。此外,此类表通常由外键引用,并在连接中使用。 整型或长整型列上的索引非常紧凑和高效,这将提高查询的性能。

    ■注意,我们将在第8章“约束”中更详细地讨论外键约束。

    在事务表的情况下,标识列或序列列上的聚集索引的效率较低,因为事务表以非常高的速度收集大量数据,这是由于它们引入了潜在的热点

 另一方面,唯一标识符很少是索引的好选择,不管是集群索引还是非集群索引。使用NEWD( )函数生成的随机值大大增加了索引碎片。此外唯一标识符上的索引会降低批处理操作的性能。让我们看一个示例并创建两个表一个表在标识符列上使用聚集索引,另一个表在 uniqueidentifier列上使用聚集索引。下一步,我们将在两个表中插入65,536行。您可以在列表7-4中看到这样做的代码。

列表7-4.唯一标识符：表创建

create table dbo.IdentityCI
(
ID int not null identity(1,1),
Val int not null,
Placeholder char(100) null
);

create unique clustered index IDX_IdentityCI_ID
on dbo.IdentityCI(ID);

create table dbo.UniqueidentifierCI
(
ID uniqueidentifier not null
constraint DEF_UniqueidentifierCI_ID
default newid(),
Val int not null,
Placeholder char(100) null,
);

create unique clustered index IDX_UniqueidentifierCI_ID
on dbo.UniqueidentifierCI(ID)
go

;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 rows
,N5(C) as (select 0 from N4 as T1 cross join N4 as T2) -- 65,536 rows
,IDs(ID) as (select row_number() over (order by (select null)) from N5)
insert into dbo.IdentityCI(Val)
select ID from IDs;

;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 rows
,N5(C) as (select 0 from N4 as T1 cross join N4 as T2) -- 65,536 rows
,IDs(ID) as (select row_number() over (order by (select null)) from N5)
insert into dbo.UniqueidentifierCI(Val)
select ID from IDs;

我计算机上的执行时间和读取次数如表7-1所示。图7-3显示了这两个查询的执行计划。

表7-1.向表中插入数据：执行统计数据

 

图7-3.将数据插入表：执行计划

 

让我们在表中插入另一批行并检查索引碎片。执行此操作的代码如列表7-5所示。图7-4显示了查询的结果。

列表7-5.唯一标识符：插入行并检查碎片

;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 rows
,N5(C) as (select 0 from N4 as T1 cross join N4 as T2) -- 65,536 rows
,IDs(ID) as (select row_number() over (order by (select null)) from N5)
insert into dbo.IdentityCI(Val)
select ID from IDs;

;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 rows
,N5(C) as (select 0 from N4 as T1 cross join N4 as T2) -- 65,536 rows
,IDs(ID) as (select row_number() over (order by (select null)) from N5)
insert into dbo.UniqueidentifierCI(Val)
select ID from IDs;

select page_count, avg_page_space_used_in_percent, avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.IdentityCI'),1,null,'DETAILED');

select page_count, avg_page_space_used_in_percent, avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.UniqueidentifierCI'),1,null,'DETAILED');

图7-4.索引的碎片化

如您所见, 唯一标识符列上的索引被严重分割,与 identity列上的索引相比,它使用了大约40%的数据页。

批处理插入到唯一标识符列上的索引中会在数据文件的不同位置插入数据,这在大表的情况下会导致大量的随机物理I/O。这将显著降低操作的性能。

当您想要在唯一标识符上创建索引时,有两种常见的用例。第一个是支持跨多个数据库的值的唯一性。考虑—个分布式系统,其中可以将行插入每个数据库。开发人员经常使用 唯一标识符来确保每个键值在系统范围内都是惟一的。

    这种实现中的关键元素是如何生成键值。正如您已经看到的,NEMD0( )函数或客户机代码中生成的随机值会对系统性能产生负面影响。但是,您可以使用 NEWSEQUENTIALIDI( )函数,该函数生成惟一的、通常不断增刀的值(SQL Servers会不时重置它们的基本值)。使用 NEWSEQUENTIALID( )函数生成的唯一标识符列上的索引类似于identity和sequence列上的索引;但是,您应该记住,与4字节int或8字节长整型数据类型相比, 唯一标识符数据类型使用16字节的存储空间。

    作为替代解决方案,您可以考虑创建一个包含两列( Installationid、unique_ id within install)的复合索引。这两列的组合保证了跨多个安装和数据库的唯一性,并且比唯一标识符使用更少的存储空间。您可以使用整数标识或序列来生成 Unique_ Id within Installation值,这将减少索引的碎片。

    在需要跨数据库中的所有实体生成惟一键值的情况下,可以考虑跨所有实体使用单个 sequence对象。这种方法满足了需求,但是使用的数据类型比唯一标识符更小。

    另一个常见的用例是安全性,其中唯一标识符值用作安全令牌或随机对象ID。

在这个场景中，一个可能的改进是使用 CHECKSUMO( )函数创建一个计算过的列，然后索引它，而不需要在唯一标识符列上创建索引。代码如列表7-6所示。

    列表7-6.使用CHECKSUM( ):表结构

create table dbo.Articles
(
ArticleId int not null identity(1,1),
ExternalId uniqueidentifier not null
constraint DEF_Articles_ExternalId
default newid(),
ExternalIdCheckSum as checksum(ExternalId),
/* Other Columns */
);

create unique clustered index IDX_Articles_ArticleId
on dbo.Articles(ArticleId);

create nonclustered index IDX_Articles_ExternalIdCheckSum
on dbo.Articles(ExternalIdCheckSum);

■提示您可以在不持久化计算列的情况下索引它。

尽管IDX_Articles_ExternalIdCheckSum索引将被严重分割，但与唯一标识符列上的索引(4字节键与16字节键)相比,它将更加紧凑。它还提高了批处理操作的性能，因为排序速度更快,这也需要更少的内存。

    必须记住的一点是, CHECKSUM( )函数的结果不一定是惟一的。应该将这两个谓词都包含到查询中,如列表7-7所示。

    列表7-7使用 CHECKSUM( )：选择数据

    select ArticleId /* Other Columns */
from dbo.Articles
where checksum(@ExternalId) = ExternalIdCheckSum and ExternalId = @ExternalId

  ■提示当需要索引大于900/1,700字节的字符串列时，您可以使用相同的技术，这是非聚集索引键的最大大小。即使这样的索引不支持范围扫描操作,它也可以用于点查找。