第11周翻译：Clustered Index Design Considerations

每次更改聚集索引键的值时，都会发生两件事。首先，SQL Server移动行移动到聚集索引页链和数据文件中的不同位置。其次，它更新行id，这是聚集索引键。行id存储在所有非集群索引中，需要更新。就I/O而言，这可能非常昂贵，尤其是在批更新的情况下。此外，它可以增加聚集索引的碎片，在行id大小增加的情况下，非聚集索引的碎片。因此,最好有一个不改变键值的静态聚集索引。
所有非聚集索引都使用聚集索引键作为行id。一个太宽的聚集索引键增加非聚集索引行的大小，并需要更多空间来存储它们。因此，SQL Server在索引或范围扫描操作期间需要处理更多的数据页，这使得索引非常高效。
在非惟一非聚集索引的情况下，行id也存储在非叶索引级别，反过来，减少了每页索引记录的数量，并可能导致索引中额外的中间层。即使非叶索引级别通常缓存在内存中，这也会引入额外的逻辑读取，每次SQL Server遍历非聚集索引b树时。
最后，较大的非聚集索引在缓冲池中使用更多的空间，并引入更多的开销，在索引维护。显然，不可能提供定义，可应用于任何表的键的最大可接受大小。然而，一般来说，最好是拥有一个窄的聚集索引键，索引键越小越好。
将聚集索引定义为惟一索引也是有益的。这很重要的原因并不重要，显而易见的。考虑这样一种场景:一个表没有惟一的聚集索引，而您想要运行一个在执行计划中使用非聚集索引查找的查询。在本例中，如果非集群中的行id索引不是惟一的，SQL Server不知道在键期间选择什么聚集索引行查找操作。
SQL Server通过将另一个名为uniquifier的可空整数列添加到nonunique来解决这些问题，聚集索引。SQL Server为键的第一次出现使用NULL填充uniquifier值，然后为插入到表中的每个后续副本自动递增该值。
让我们看看uniquifier在非惟一集群索引中引入的开销。所示的代码。在清单7-1中，创建了三个相同结构的不同表，并用65,536行填充，UniqueCI是惟一定义了惟一集群索引的表。表dbo.NonUniqueCINoDups没有任何重复的键值。最后,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© as (select 0 union all select 0) – 2 rows
,N2© as (select 0 from N1 as T1 cross join N1 as T2) – 4 rows
,N3© as (select 0 from N2 as T1 cross join N2 as T2) – 16 rows
,N4© as (select 0 from N3 as T1 cross join N3 as T2) – 256 rows
,N5© 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;
Now, let’s look at the clustered indexes’ physical statistics for each table. The code for this is shown in
Listing 7-2 , and the results are shown in Figure 7-1 .
Listing 7-2. Nonunique clustered index : Checking clustered indexes’ row sizes
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会添加另外四个字节，即总共有6个字节。
值得一提的是，在某些边缘情况下，uniquifier使用的额外存储空间可以减少数据页上可容纳的行数。我们的示例演示了这种情况。作为你可以看到，UniqueCI使用的数据页比其他两个表少15%。
现在，让我们看看uniquifier如何影响非聚集索引。清单7-3所示的代码创建所有三个表中的非聚集索引。图7-2显示了这些索引的物理统计数据。
清单7：非唯一聚集索引:检查非聚集索引的行大小。
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]
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
图7 - 2：非唯一聚集索引:非聚集索引的行大小。
dbo中的非聚集索引没有超出。NonUniqueCINoDups表。你们应该还记得，SQL Server不将偏移量信息存储在用于存储尾随列的变长偏移量数组中的空数据。尽管如此，uniquifier在dbo中引入了8个字节的超出。在NonUniqueCIDups表中，这8个字节包括一个4字节的uniquifier值，一个2字节的可变长度数据偏移数组条目，以及存储行中可变长度列数的双字节条目。
我们可以用以下方式总结uniquifier的存储开销。对于那些行，如果索引至少有一个可变长度的列，则uniquifier为NULL，则有两个字节的超出，存储一个NOT NULL值。这个开销来自uniquifier的可变长度偏移数组条目列，否则就没有超出。
在填充uniquifier的情况下，如果有可变长度的列，开销是6个字节存储NOT NULL值。否则，开销是8个字节。
以最小化由以下原因引起的索引碎片的方式设计聚集索引是有益的。插入新行，实现这一点的方法之一是创建聚集索引值ever增加。identity列上的索引就是这样一个例子。另一个例子是datetime列，用插入时的当前系统时间填充。
然而，索引不断增长存在两个潜在问题。第一个与统计有关。您在第3章中了解到，SQL Server中的遗留基数估计器在低估基数直方图中没有参数值。您应该在统计数据中考虑这种行为是系统的维护策略，除非您正在使用新的SQL Server 2014-2016基数估计器，它假定直方图外的数据具有类似于其他数据的分布表。
下一个问题更加复杂。随着索引的不断增加，数据总是插入到索引结束。一方面，它可以防止页面分裂和减少碎片。另一方面，它会导致热点，即当多个会话试图修改时发生的序列化延迟相同的数据页和分配新页或区段。SQL Server不允许多个会话，更新相同的数据结构，并序列化这些操作。
热点通常不是问题，除非系统以非常高的速率和索引收集数据，每秒处理数百个插入。我们将在第27章“系统”中讨论如何检测这样的问题的故障排除”。
最后，如果一个系统有一组频繁执行的重要查询，那么它可能对考虑一个集群索引，它可以优化它们。这消除了昂贵的键查找操作和提高系统性能。
尽管可以通过覆盖非聚集索引来优化此类查询，但它并不总是理想的解决方案。在某些情况下，它要求您创建非常广泛的非聚集索引，这将耗尽a磁盘和缓冲池中的大量存储空间。
另一个重要因素是修改列的频率。添加经常修改的列，非聚集索引要求SQL Server在多个位置更改数据，这对更新系统性能，增加阻塞。
综上所述，设计能够满足所有这些要求的聚集索引并不总是可能的指导方针。此外，您不应该认为这些指南是绝对的需求。你应该分析系统、业务需求、工作负载和查询，并选择集群索引
让你受益，即使他们违反了其中的一些准则。
Identities, Sequences, and Uniqueidentifiers
人们通常选择标识符、序列和uniqueidentifier作为聚集索引键,每种方法都有自己的优缺点。
在这些列上定义的聚集索引是惟一的、静态的和窄的。此外,身份和序列不断增加，这减少了索引碎片。它们的一个理想用例是目录表实体。您可以将存储客户、文章或设备列表的表视为的例子。这些表存储了数千行，甚至几百万行，尽管数据是相对的静态，因此，热点不是一个问题。此外，此类表通常由外部引用键，用于连接。integer或bigint列上的索引非常紧凑和高效，这将会提高查询的性能。
在事务表的情况下，在标识列或序列列上聚集索引的效率较低，它们以非常高的速度收集大量的数据，这是由于它们引入了潜在的热点。
另一方面，uniqueidentifier很少是索引(集群索引和索引)的好选择。非聚集使用NEWID()函数生成的随机值大大增加了索引碎片。此外，uniqueidentifier上的索引会降低批处理操作的性能。我们来看一个示例并创建两个表:一个表在标识列上具有聚集索引，另一个表具有聚集索引uniqueidentifier列上的索引。下一步，我们将在两个表中插入65,536行。你可以参见清单7-4中的代码。
清单7 - 4：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© as (select 0 union all select 0) – 2 rows
,N2© as (select 0 from N1 as T1 cross join N1 as T2) – 4 rows
,N3© as (select 0 from N2 as T1 cross join N2 as T2) – 16 rows
,N4© as (select 0 from N3 as T1 cross join N3 as T2) – 256 rows
,N5© 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© as (select 0 union all select 0) – 2 rows
,N2© as (select 0 from N1 as T1 cross join N1 as T2) – 4 rows
,N3© as (select 0 from N2 as T1 cross join N2 as T2) – 16 rows
,N4© as (select 0 from N3 as T1 cross join N3 as T2) – 256 rows
,N5© 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;
CHAPTER 7 ■ DESIGNING AND TUNING THE INDEXES
162
The execution time on my computer and number of reads are shown in Table 7-1 . Figure 7-3 shows
execution plans for both queries.
图7：将数据插入表:执行计划。
如您所见，对于uniqueidentifier列上的索引，还有另一个排序操作符。SQL Server在插入之前对随机生成的uniqueidentifier值进行排序，这会减少查询的性能。
让我们在表中插入另一批行并检查索引碎片。执行此操作的代码。如清单7-5所示。
图7-4显示了查询的结果。
清单7 - 5。uniqueidentifier:插入行并检查碎片。
;with N1© as (select 0 union all select 0) – 2 rows
,N2© as (select 0 from N1 as T1 cross join N1 as T2) – 4 rows
,N3© as (select 0 from N2 as T1 cross join N2 as T2) – 16 rows
,N4© as (select 0 from N3 as T1 cross join N3 as T2) – 256 rows
,N5© 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© as (select 0 union all select 0) – 2 rows
,N2© as (select 0 from N1 as T1 cross join N1 as T2) – 4 rows
,N3© as (select 0 from N2 as T1 cross join N2 as T2) – 16 rows
,N4© as (select 0 from N3 as T1 cross join N3 as T2) – 256 rows
,N5© 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。这可能会显著降低操作性能。
当您想要在uniqueidentifier上创建索引时，有两个常见的用例列。第一个是支持跨多个数据库的值的唯一性。考虑一个可以将行插入每个数据库的分布式系统。开发人员经常使用uniqueidentifier确保每个键值在系统范围内都是唯一的。
这种实现中的关键元素是如何生成键值。正如你们已经做过的，可以看到，NEWID()函数或客户机代码中生成的随机值会对系统产生负面影响的性能。但是，您可以使用NEWSEQUENTIALID()函数，该函数生成惟一且一般的不断增加的值(SQL Server会不时重置它们的基本值)。索引uniqueidentifier使用NEWSEQUENTIALID()函数生成的列类似于标识和序列上的索引列;但是，您应该记住uniqueidentifier数据类型使用16字节的存储空间，与4字节int或8字节bigint数据类型相比。
作为另一种解决方案，您可以考虑创建一个包含两列的复合索引(InstallationId Unique_Id_Within_Installation)。这两列的组合保证了这一点跨多个安装和数据库的惟一性，并且比uniqueidentifier使用更少的存储空间做的。您可以使用整数标识或序列来生成Unique_Id_Within_Installation值，这将减少索引的碎片。
在需要跨数据库中的所有实体生成惟一键值的情况下，您可以这样做考虑跨所有实体使用单个sequence对象。这种方法满足了需求，但是使用了比uniqueidentifier更小的数据类型
另一个常见的用例是安全性，其中uniqueidentifier值用作安全令牌或不幸的是，您不能在这个场景中使用NEWSEQUENTIALID()函数，因为可以猜测该函数返回的下一个值。
此场景中一个可能的改进是使用CHECKSUM()创建一个计算列函数，然后对其进行索引，而不在uniqueidentifier列上创建索引。代码如清单7-6所示。
清单7。使用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()函数的结果不一定是这样的值是唯一的。应该将这两个谓词都包含到查询中，如清单7-7所示。
清单7。使用CHECKSUM():选择数据
select ArticleId / Other Columns */
from dbo.Articles
where checksum(@ExternalId) = ExternalIdCheckSum and ExternalId = @ExternalId