第十一周-翻译：Clustered Index Design Considerations

原著信息：《Pro SQL Server Internals, 2nd edition》CHAPTER 7 Designing and Tuning the Indexes中的Clustered Index Design Considerations一节
作者：Dmitri Korotkevitch

Clustered Index Design Considerations  聚集索引设计注意事项

每次更改集群索引键的值都会发生两件事。首先，SQL Server移动行到群集索引页链和数据文件中的另一个位置。其次，它更新行id，这是群集索引键。行id存储在所有非集群索引中就需要更新。就I/O而言，这可能很昂贵，尤其是在批量更新的情况下。此外，它可以增加集群索引的碎片化，以及在行id大小增加的情况下，非集群索引的碎片化。因此,在键值不变的情况下，最好使用静态聚集索引。

  所有非聚集索引都使用聚集索引键作为行编号。一个太宽的聚集索引键增加非聚集索引行的大小，并需要更多空间来存储它们。因此，SQL Server在索引或范围扫描操作期间需要处理更多的数据页，这使得索引更少非常高效。

  对于非惟一的聚集索引，行编号也存储在非叶索引级别，反过来，也减少了每页索引记录的数量，并可能导致索引中额外的中间级别。尽管非叶索引级别通常缓存在内存中，但每次SQL Server遍历非聚簇索引B-Tree时，这都会引入额外的逻辑读取。

  最后，较大的非集群索引会在缓冲池中使用更多空间，并在索引维护中带来更多开销。显然，不可能提供一个通用阈值来定义可应用于任何表的键的最大可接受大小。但是，一般来说，最好是有一个窄的聚集索引键，索引键越小越好。

  将聚集索引定义为惟一的也是有益的。这很重要的原因不是显而易见的。考虑一个场景，其中一个表没有惟一的聚集索引，希望运行一个在执行计划中使用非集群索引的查询。在这种情况下，如果行id是非集的索引不是唯一的，SQL Server不知道在键期间选择哪个聚簇索引行查找操作。

  SQL Server通过向非唯一聚簇索引中添加另一个名为uniquifier的可空整数列的操作来解决此类问题。对于第一次出现的键值，SQL Server用NULL填充uniquifiers，并对插入到表中的后续重复值进行自动递增操作。

---

■注意 每个聚集索引键值可能重复的数目受到整数域值的限制。具有相同聚集索引键的行不能超过2,147,483,648行。这是理论上的局限性，显然创建一个选择性这么差的索引是个不好的方式。

---

  让我们看看在非唯一聚集索引中由uniquifier引入的开销。清单7-1中所示的代码创建了三个具有相同结构的不同表，并用65，5填充它们。每排36行。表dbo.UniqueCI是唯一定义了唯一聚集索引的表。表dbo.non-UniqueCINodups没有任何重复的键值。最后，表dbo.non-UniqueCDups有一个索引中的大量重复项。

 清单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); 
CHAPTER 7 ■ DESIGNING AND TUNING THE INDEXES
157
-- 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   非唯一聚集索引：检查聚集索引的行大小

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在数据的可变长度部分存储一个uniquifier，和这两个字节由可变长度数据偏移数组中的另一个条目添加。

  在这种情况下，当集群索引具有重复值时，uniquifiers将再添加4个字节，即导致总共6字节的开销。

  值得一提的是，在某些边缘情况下，uniquifier使用的额外存储空间可以减少数据页上可以容纳的行数。我们的示例演示了这种情况。你可以看到，表dbo.UniqueCI与其他两个表相比使用的数据页少了大约15%。

  现在，让我们看看uniquifier如何影响非集群索引。清单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不会将偏移量信息存储在可变长度偏移量数组中。尽管如此，uniquifier还是在表dbo.NonUniqueCIDups中引入了八个字节的开销。这八个字节由一个四字节的uniquifier值、一个两字节的可变长度数据偏移量数组条目和一个两字节的条目组成，这些条目存储行中可变长度列的数量。

  我们可以用下面的方法来总结uniquifier的存储开销。对于uniquifier为空的行，如果索引至少有一个存储非空值的可变长度列，则会有两个字节的开销，该开销来自uniquifier列的可变长度偏移数组项，否则没有开销。

   在填充uniquifier的情况下，如果存在存储非空值的可变长度列，则开销为6个字节，否则，开销为8字节。

---

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

---

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

  然而，随着索引不断增加会出现两个潜在问题。第一个问题与统计有关。正如您在第3章中了解到的，当柱状图中没有参数值时，SQL Server中的遗留基数估计量低估了基数。除非您使用新的SQL Server 2014-2016基数估计量，该估计量假定柱状图之外的数据具有与表中其他数据相似的分布，否则应将这种行为考虑到系统的统计维护策略中。

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

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

  最后，如果一个系统有一组经常执行的重要查询，那么考虑一个可能会对它们进行优化的聚集索引是有益的。

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

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

  尽管如此，并不总是可能设计出满足所有这些准则的聚集索引。而且，您不应将这些准则视为绝对需求。您应该分析系统、业务需求、工作负载和查询，并选择有利于您的聚集索引，即使它们违反了其中的一些准则。

Identities, Sequences, and Uniqueidentifiers  标识、序列和统一标识符

人们通常选择标识、序列和统一标识符作为聚集索引键。与以往一样，这种方法有其本身的利弊。

  在这些列上定义的聚集索引是唯一的、静态的和窄的。此外，标识和序列不断增加，这减少了索引碎片。其中一个理想的使用的例子是编目实体表。您可以考虑以存储客户、物品或设备列表的表为例。这些表存储数千行，甚至几百万行，尽管数据是相对静态的，但是热点不是问题。此外，此类表通常由外键引用并用于联接。integer或bigint列上的索引非常紧凑和高效，这将提高查询的性能。

---

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

---

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

  另一方面，对于聚集索引和非聚集索引，唯一标识符很少是一个好的选择。NEWID()函数生成的随机值大大增加了索引碎片。此外，唯一标识符上的索引会降低批处理操作的性能。让我们来看一个例子并创建两个表：一个表在标识列上具有聚集索引，另一个表在唯一标识符列上具有聚集索引。在下一步中，我们将在两个表中插入65536行。您可以在清单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   将数据插入表：执行统计

	Number of Reads 读取次数	Execution Time (ms) 执行时间（ms）
Identity 标识	158,438	173 ms
Uniqueidentifier 唯一标识符	181,879	256 ms

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

  如您所见，uniqueidentifier列上的索引有另一个排序运算符。SQL Server在插入之前对随机生成的uniqueidentifier值进行排序，这会降低查询的性能。

  让我们在表中插入另一批行并检查索引碎片。执行此操作的代码如清单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   索引的碎片化

  如您所见，uniqueidentifier列上的索引非常零碎，与标识列上的索引相比，它使用的数据页大约多40%。

  批量插入到uniqueidentifier列的索引中会在数据文件的不同位置插入数据，这会导致大型表中出现大量随机物理I/O。这会显著降低操作性能。

---

  个人经验

---

不久前，我参与了一个系统的优化，该系统有一个250 GB的表，其中包含一个聚簇索引和三个非聚簇索引。 其中一个非聚簇索引是uniqueidentifier列上的索引。通过删除此索引，我们能够将50,000行的批量插入从45秒加速到7秒。

---

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

  这种实现中的关键元素是如何生成键值。正如您已经看到的，使用 NEWID() 函数或客户机代码生成的随机值会对系统性能产生负面影响。但是，您可以使用 NEWSEQUENTIALID() 函数，该函数生成唯一的值，通常会不断增加值（SQL Server会时不时地重置它们的基值）。使用 NEWSEQUENTIALID() 函数生成的uniqueidentifier列上的索引与标识列和序列列上的索引类似；但是你应该记住，与4字节int或8字节bigint数据类型相比，uniqueidentifier数据类型使用16字节的存储空间。

  作为另一种解决方案，您可以考虑创建包含两列的复合索引 (InstallationId, Unique_Id_Within_Installation) 。这两列的组合保证了跨多个安装设备和数据库的唯一性，并且使用的存储空间比唯一标识符少。您可以使用整数标识或序列生成 Unique_Id_Within_Installation 值，这将减少索引的碎片。

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

  另一个常见的用例是安全性，其中uniqueidentifier值用作安全令牌或随机对象ID。不幸的是，在这种情况下您不能使用NEWSEQUENTIALID() 函数，因为这可能会猜测到该函数返回的下一个值。

  在这种情况下，一个可能的改进是使用CHECKSUM()函数创建一个计算列，然后对其进行索引，而不在uniqueidentifier列上创建索引。代码如清单7-6所示。

 清单7-6   使用校验和()：表结构

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 索引将被严重分割，但与uniqueidentifier列上的索引（4字节的键与16字节的键）相比，它将更加紧凑。由于更快的排序，它还提高了批处理操作的性能，这也需要更少的内存来继续。

  您必须记住的一点是，CHECKSUM() 函数的结果不一定是唯一的。您应该将两个谓词都包含到查询中，如清单7-7所示。

 清单7-7   使用checksum()：选择数据

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

---

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

---