聚集索引设计的注意事项（翻译）

最新推荐文章于 2022-12-17 11:27:40 发布

Suqing49

最新推荐文章于 2022-12-17 11:27:40 发布

阅读量490

点赞数

分类专栏： C#&&SQL学习笔记

C#&&SQL学习笔记专栏收录该内容

11 篇文章 2 订阅

订阅专栏

【翻译自】

《Pro SQL Server Internals,2nd edition》作者：Dmitri Korotkevitch

CHAPTER 7 Designing and Tuning the Indexes

Clustered Index Design Considerations

每次更改聚集索引键的值时，都会发生两件事。
①SQL Server将行移动到聚集索引页链中和数据文件中的一个不同的位置。
②它更新了行ID值（row-id），也就是聚集索引键。集索引键存储在非聚簇索引中，并且需要在所有非聚集索引中更新。
就I / O而言，这可能是昂贵的，特别是在批量更新的情况下。此外，它会增加聚集索引的碎片，并且在行ID值大小增加的情况下，会增加非聚簇索引的碎片。因此，最好有一个静态聚集索引，其中的键值不会改变。

所有的非聚集索引都使用聚集索引键作为行ID值。过宽的（即字节数过多）聚集索引键会增加非聚集索引行的大小，并且需要更多空间来存储它们。由于这个原因，SQL Server需要在索引或范围扫描操作期间处理更多数据页，这将会降低索引的效率。

对于非唯一非聚集索引，行ID值也存储在非叶级索引，这反过来导致每页索引记录的数量减少，并可能产生索引中的额外中间级别。尽管非叶级索引通常缓存在内存中，但每次SQL Server遍历非聚集索引B树（B-Tree）时，这都会增加额外的逻辑读取。

最后，较大的非聚集索引在缓冲池中将会占用更多空间，并在索引维护期间产生更多开销。显然，不可能提供一个通用阈值，来定义可应用于所有表的密钥的最大可接受大小。但是，作为一般规则，最好使用窄的（即短字节的）聚集索引键，让索引键尽可能小。

将聚集索引定义为唯一的也是有益的。这很重要，但原因并不明显。让我们来考虑一种情况，如果表中没有唯一的聚簇索引，而您希望在执行计划中运行使用非聚集索引查找的查询。在这种情况下，如果非聚集索引中的行ID值不是唯一的，则SQL Server将不知道在键查找操作期间要选择哪个聚集索引行。

SQL Server将通过向非唯一的聚集索引添加另一个名为uniquifier（唯一标识符）的可空整数列来解决此类问题。对于第一次出现的键值，SQL Server使用NULL填充唯一标识符（uniquifier），并且为插入表中的每个后续副本自动增加它的值。

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

让我们来看看非唯一聚集索引中的唯一标识符（uniquifier）产生的开销。下面显示的代码创建了三个具有相同结构的不同表，每个表都填充65,536行。
<1>表dbo.UniqueCI是唯一定义了唯一聚集索引的表
<2>表dbo.NonUniqueCINoDups没有任何重复的键值
<3>表dbo.NonUniqueCDups在索引中有大量重复项

非唯一聚集索引：表创建


 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-1所示

非唯一聚集索引：检查聚集索引的行的大小


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

在这种情况下，当聚集索引具有重复值时，唯一标识符（uniquifier）会再添加另外四个字节，这将会产生总共六个字节的开销。

值得一提的是，在某些边缘情况下，唯一标识符（uniquifier）使用的额外存储空间会减少可放入数据页面中的行数。我们的例子说明了这种情况。如您所见，表dbo.UniqueCI使用的数据页数比其他两个表少15％。

现在，让我们来看看唯一标识符（uniquifier）如何影响非聚簇索引。下面的代码在三个表中都创建了非聚集索引。图7-2显示了这些索引的物理统计信息。

非唯一聚集索引：检查非聚集索引的行的大小


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.N个非唯一聚集索引：非聚集索引的行大小

表dbo.NonUniqueCINoDups中的非聚集索引没有开销。您可能还记得，SQL Server不会将偏移量信息存储在可变长度偏移数组中，从而用于存储NULL数据的尾随列。尽管如此，唯一标识符（uniquifier）在表dbo.NonUniqueCIDups中产生了8个字节的开销。这八个字节由一个四字节的唯一标识符（uniquifier）的值，一个双字节的可变长数据偏移数组条目和一个存储行中可变长度列数目的双字节条目组成。

我们可以通过以下方式总结唯一标识符（uniquifier）的存储开销。对于唯一标识符（uniquifier）为NULL的行，如果索引至少有一个存储非空（NOT NULL）值的可变长度列，则会产生两个字节的开销。该开销来自唯一标识符（uniquifier）列的可变长偏移数组条目。否则没有开销。

在填充唯一标识符（uniquifier）的情况下，如果存在存储非空（NOT NULL）值的可变长度列，则开销为六个字节。否则，开销是八个字节。

■提示 如果预计聚集索引值中存在大量重复项，则可以添加整数
标识列作为索引的最右列，从而使其唯一。相比于由唯一标识符（uniquifier）产生的不可预测的高达8字节的存储开销，这为每一行增加了四字节的可预测的存储开销。当您通过它所有的聚集索引列引用该行时，这还可以提高单个查找操作的性能。

在设计聚集索引时，最小化在插入新行时所导致的索引碎片，这样是有益的。实现此目标的方法之一，就是使聚集索引值不断增加。标识列上的索引就是一个这样的例子。另一个示例是使用插入新行时，用当前系统时间填充的日期时间列。

然而，不断增加的指数存在 两个潜在的问题

第一个涉及统计：

正如您在第3章中学到的，当直方图中不存在参数值时，SQL Server中的遗留基数估计器会低估基数。您应该将此类行为纳入系统的统计信息维护策略，除非您使用新的SQL Server 2014-2016基数估算器，该估算器假定直方图之外的数据具有与表中其他数据类似的分布。

下一个问题更复杂：

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

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

最后，如果系统具有一组频繁执行且重要的查询，那么考虑聚集索引可能是有益的，这会优化它们。这消除了昂贵的密钥查找操作的消耗并提高了系统的性能。

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

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

尽管如此，我们并不总是能够设计满足所有这些准则的聚集索引。此外，您不应将这些指南视为绝对要求。您应该分析系统，业务的需求，工作负载和查询，并选择有益于您的聚集索引，即使它们违反了某些准则。

身份，序列和唯一标识符

人们通常选择标识，序列和唯一标识符作为聚集索引键。一如既往，这种方法有其自身的优缺点。

在此类列上定义的聚集索引是唯一的，静态的和窄的。此外，身份和序列不断的增加，减少了索引碎片。其中一个理想的用例是目录实体表。作为示例，您可以考虑存储客户，文章或设备列表的表。尽管数据是相对静态的，这些表存储数千甚至数百万行，因此热点不是问题。此外，这些表通常由外键引用并用于连接。整数列或bigint列（SQL Server在整数值超过int数据类型支持的范围时，将使用 bigint数据类型。）上的索引非常紧凑和高效，这将提高查询的性能。

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

对事务表而言，身份或序列列上的聚集索引效率较低，事务表由于它们产生的潜在热点而以非常高的速率收集大量数据。

另一方面，Uniqueidentifiers（全局唯一的标识，该数据类型可存储16字节的二进制值）很少是聚集和非聚集索引的理想选择。使用NEWID（）函数生成的随机值极大地增加了索引碎片。此外，uniqueidentifiers上的索引会降低批处理操作的性能。让我们来看一个示例并创建两个表：
<1>一个在身份（Identity）列上有聚簇索引
<2>另一个在唯一标识符（uniqueidentifier）列上有聚簇索引
在下一步中，我们将在两个表中插入65,536行。您可以在下面看到执行此操作的代码。

Uniqueidentifiers：创建表


 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-3显示了两个查询的执行计划。

将数据插入表：执行统计

	读取次数	执行时间（ms）
身份 Identity	158,438	173 ms
唯一标识Uniqueidentifier	181,879	256 ms

在这里插入图片描述
图7-3.将数据插入表中：执行计划

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

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

Uniqueidentifiers：插入行并检查碎片


 ;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秒。

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

此类实例中的关键是如何生成键值。正如您已经看到的，使用NEWID（）函数或客户端代码生成的随机值会对系统性能产生负面影响。但是，您可以使用NEWSEQUENTIALID（）函数，该函数生成唯一且通常不断增加的值（SQL Server会不时重置其基值）。使用NEWSEQUENTIALID（）函数生成的uniqueidentifier列的索引，类似于身份（identity）和序列（sequence）列的索引; 但是，您要记住，相比于4字节的int或8字节的bigint数据类型，uniqueidentifier数据类型使用16字节的存储空间。

作为替代解决方案，您可以考虑创建具有两列的复合索引
（InstallationId，Unique_Id_Within_Installation）。这两列的组合保证了多个安装和数据库的唯一性，并且比唯一标识符使用更少的存储空间。您可以使用整数标识或序列来生成Unique_Id_Within_Installation值，这将减少索引的碎片。

如果需要在数据库中的所有实体上生成唯一键值，则可以考虑在所有实体中使用单个序列对象。此方法满足要求，但使用比uniqueidentifier更小的数据类型。

另一个常见用例是安全性，其中uniqueidentifier值用作安全性令牌或随机对象ID。不幸的是，您不能在此方案中使用NEWSEQUENTIALID（）函数，因为猜测该函数返回的下一个值是有可能的。

在这种情况下，可以采取的改进是使用CHECKSUM（）函数创建计算列，然后对其进行索引，而不在uniqueidentifier列上创建索引。代码如下

使用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索引会导致严重分段，但与uniqueidentifier列上的索引（4字节密钥与16字节）相比，它更紧凑。它还提高了批处理操作的性能，因为更快的排序，将消耗更小的内存。

您必须记住的一点是CHECKSUM（）函数的结果不保证是唯一的。您应该在查询中包含两个谓词，如下列代码

使用CHECKSUM（）：选择数据


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

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

Suqing49

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
聚集索引设计的注意事项（翻译）

1级标题加粗文本强调文本
复制链接

扫一扫

专栏目录