第十周翻译_第10周翻译-CSDN博客

设计和调整索引
不可能定义一个适用于任何地方的索引策略。每个系统都是独特的，并且需要基于工作负载、业务需求和许多其他因素的自己的索引方法。但是，在每个系统中都可以应用几个设计考虑和指导原则。当我们优化现有系统时也是如此。虽然优化是一个在任何情况下都是唯一的迭代过程，但是有一组技术可以用来检测每个数据库系统中的效率低下。在本章中，我们将介绍一些在设计新索引和优化现有系统时需要牢记的重要因素。
聚集索引设计注意事项：
每次更改聚集索引键的值时，都会发生两件事。首先，SQL Server将行移动到聚集索引页链和数据文件中的其他位置。其次，它更新行ID，这是聚集索引键。行ID已存储，需要在所有非聚集索引中更新。这在I/O方面可能很昂贵，特别是在批量更新的情况下。此外，它还可以增加聚集索引的碎片，在行ID大小增加的情况下，还可以增加非聚集索引的碎片。因此，最好有一个静态聚集索引，其中键值不会改变。所有非聚集索引都使用聚集索引键作为行ID。太宽的聚集索引键会增加非聚集索引行的大小，并需要更多的空间来存储它们。因此，在索引或范围扫描操作期间，SQL Server需要处理更多的数据页，这会降低索引的效率。对于非唯一非聚集索引，行ID也存储在非叶索引级别，这反过来减少了每页索引记录的数量，并可能导致索引中出现额外的中间级别。尽管非叶索引级别通常缓存在内存中，但每次SQL Server遍历非聚集索引B树时，都会引入额外的逻辑读取。最后，较大的非聚集索引在缓冲池中使用更多的空间，并在索引维护期间引入更多的开销。显然，不可能提供一个通用阈值来定义可以应用于任何表的键的最大可接受大小。但是，作为一般规则，最好使用窄聚集索引键，索引键尽可能小。将聚集索引定义为唯一索引也是有益的。这一点很重要的原因并不明显。考虑这样一种情况：表没有唯一的聚集索引，并且你希望运行一个在执行计划中使用非聚集索引查找的查询。在这种情况下，如果非聚集索引中的行ID不唯一，SQL Server将不知道在键查找操作期间要选择什么聚集索引行。SQL Server通过将另一个名为唯一性的可以为空的整数列添加到非唯一的聚集索引中来解决这些问题。SQL Server会在第一次出现键值时用空值填充唯一性，并自动为插入到表中的每个后续重复项增加它。
注意：每个聚集索引键值的可能重复数受整型域值的限制。具有相同聚集索引键的行不能超过2147483648行。这是一个理论上的限制，显然创建选择性如此差的索引是一个坏主意。
让我们看看唯一性在非唯一聚集索引中引入的开销。清单7-1中所示的代码创建了三个具有相同结构的不同表，并用65536行填充它们。表dbo.uniqueci是唯一定义了唯一聚集索引的表。表dbo.nonuniquecinodups没有任何重复的键值。最后，表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© 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在数据的可变长度部分存储一个唯一性，这两个字节由可变长度数据偏移量数组中的另一个条目添加。在这种情况下，当一个聚集索引有重复的值时，唯一性又添加了四个字节，这就产生了总共六个字节的开销。值得一提的是，在某些边缘情况下，唯一性使用的额外存储空间可以减少可以容纳在数据页上的行数。我们的示例演示了这样一个条件。如你所见，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’);

空数据。尽管如此，uniquifier在dbo.nonuniquecidups表中引入了八个字节的开销。这八个字节由一个四字节的uniquifier值、一个两字节的可变长度数据偏移量数组条目和一个两字节的条目组成，这些条目存储行中可变长度列的数量。我们可以用下面的方法来总结uniquifier的存储开销。对于uniquifier为空的行，如果索引至少有一个存储非空值的可变长度列，则会有两个字节的开销。该开销来自uniquifier列的可变长度偏移数组项。否则没有开销。在填充uniquifier的情况下，如果存在存储非空值的可变长度列，则开销为6个字节。否则，开销为8字节。
提示：如果期望聚集索引值中有大量重复项，可以添加一个整数。
将列标识为索引最右边的列，从而使其唯一。与uniquifiers引入的不可预测的高达8字节的存储开销相比，这为每行增加了4字节的可预测存储开销。当你通过行的所有聚集索引列引用该行时，这还可以提高单个查找操作的性能。
以最小化插入新行导致的索引碎片的方式设计聚集索引是有益的。实现这一点的方法之一是使聚集索引值不断增加。标识列上的索引就是这样一个例子。另一个例子是用插入时的当前系统时间填充的日期时间列。然而，指数不断增长有两个潜在问题。第一个与统计有关。正如你在第3章中了解到的，当柱状图中没有参数值时，SQL Server中的遗留基数估计量低估了基数。除非使用新的SQL Server 2014-2016基数估计量，否则应将这种行为考虑到系统的统计维护策略中，该估计量假定柱状图之外的数据具有与表中其他数据相似的分布。下一个问题更复杂。随着索引的不断增加，数据总是插入到索引的末尾。一方面，它可以防止页面拆分并减少碎片。另一方面，它可能会导致热点，即当多个会话试图修改同一数据页和/或分配新页或扩展数据块时发生的序列化延迟。SQL Server不允许多个会话更新相同的数据结构，而是序列化这些操作。热点通常不是问题，除非系统以非常高的速度收集数据，并且索引每秒处理数百个插入。我们将在第27章“系统故障排除”中讨论如何检测这样一个问题。最后，如果一个系统有一组频繁执行且重要的查询，那么考虑一个集群索引可能会对它们进行优化。这消除了昂贵的密钥查找操作，并提高了系统的性能。尽管可以通过覆盖非聚集索引来优化此类查询，但它并不总是理想的解决方案。在某些情况下，需要创建非常宽的非聚集索引，这将占用磁盘和缓冲池中的大量存储空间。另一个重要因素是修改列的频率。向非聚集索引添加频繁修改的列需要SQL Server在多个位置更改数据，这会对系统的更新性能产生负面影响，并增加阻塞。尽管如此，设计满足所有这些准则的聚集索引并不总是可能的。此外，你不应将这些指导原则视为绝对需求。你应该分析系统、业务需求、工作负载和查询，并选择有利于你的聚集索引，即使它们违反了其中的一些准则。
标识、序列和唯一标识符
人们通常选择标识、序列和唯一标识符作为聚集索引键。和往常一样，这种方法也有自己的利弊。在这些列上定义的聚集索引是唯一的、静态的和窄的。此外，身份和序列不断增加，这减少了索引碎片。其中一个理想的用例是编目实体表。你可以考虑以存储客户、物品或设备列表的表为例。这些表存储数千行，甚至几百万行，尽管数据是相对静态的，因此热点不是问题。此外，此类表通常由外键引用并用于联接。integer或bigint列上的索引非常紧凑和高效，这将提高查询的性能。
在事务表的情况下，标识列或序列列上的聚集索引效率较低，事务表收集大量数据的速度非常快，这是因为它们引入了潜在的热点。另一方面，对于聚集索引和非聚集索引，唯一标识符很少是一个好的选择。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© 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;
我的计算机上的执行时间和读取次数如表7-1所示。图7-3显示了两个查询的执行计划。
表7-1 向表中插入数据：执行统计信息
在这里插入图片描述
图7 -3 将数据插入表：执行计划
如你所见，在uniqueidentifier列的索引的情况下还有另一个排序运算符。SQL Server在插入之前对随机生成的uniqueidentifier值进行排序，这会降低查询的性能。让我们在表中插入另一批行并检查索引碎片。这样做的代码如清单7-5所示。图7-4显示了查询结果。
清单7-5 唯一标识符：插入行并检查碎片
;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索引分割
如你所见，单一标识符柱的索引是重叠的，它使用了大约40个钻孔数据页，与标识符柱的索引进行比较。一个批量插入在数据文件中不同位置的单一标识符柱插入数据索引中，该索引引导重力，随机物理I/O在大表中。这可以显著降低操作性能。
个人经验
一段时间前，我参与了一个系统的优化，该系统有一个250GB的表，其中包含一个聚集索引和三个非聚集索引。其中一个非聚集索引是
唯一标识符列。通过删除这个索引，我们能够将50000行的批量插入从45秒加速到7秒。
当你想要在唯一标识符列上创建索引时，有两个常见的用例。第一个是支持跨多个数据库的值的唯一性。考虑一个分布式系统，其中的行可以插入到每个数据库中。开发人员经常使用唯一标识符来确保每个键值在系统范围内都是唯一的。这种实现中的关键元素是如何生成键值。正如你已经看到的，使用newid（）函数或客户机代码生成的随机值会对系统性能产生负面影响。但是，你可以使用newsequentialid（）函数，该函数生成唯一的值，通常会不断增加值（SQL Server会不时重置它们的基值）。使用newSequentialID（）函数生成的uniqueIdentifier列上的索引与标识列和序列列上的索引类似；但是，应该记住，与4字节int或8字节bigint数据类型相比，uniqueIdentifier数据类型使用16字节的存储空间。
作为另一种解决方案，你可以考虑创建包含两列的复合索引
（InstallationID，安装中唯一的_id_）。这两列的组合保证了跨多个安装和数据库的唯一性，并且使用的存储空间比唯一标识符少。你可以使用整数标识或序列在安装值内生成唯一的_id_，这将减少索引的碎片。在需要跨数据库中的所有实体生成唯一键值的情况下，可以考虑跨所有实体使用单个序列对象。这种方法满足了要求，但使用的数据类型比唯一标识符小。另一个常见的用例是security，其中uniqueidentifier值用作安全令牌或随机对象ID。不幸的是，在这种情况下不能使用newsequentialid（）函数，因为可以猜测该函数返回的下一个值。在这种情况下，一个可能的改进是使用checksum（）函数创建一个计算列，然后对其进行索引，而不在uniqueidentifier列上创建索引。代码如清单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_项目的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字节的字符串列（这是非聚集索引键的最大大小），可以使用相同的技术。即使这样的索引不支持范围扫描操作，它也可以用于点查找。