翻译：设计和优化索引

于 2018-12-12 23:07:30 发布
阅读量161 收藏
点赞数

译文：《Pro SQL Server Internals, 2nd edition》CHAPTER 7 Designing and Tuning the Indexes中的Clustered Index Design Considerations一节
作者：Dmitri Korotkevitch
翻译：刘琼滨

要定义一个在任何地方都有效的索引策略是不可能的。因为每个系统都是独一无二的，都需要基于工作量的索引方法，业务需求和其他的一些因素。然而，有一些设计方面的考虑和准则，可以适用于每一个系统。

当我们要优化现有系统时，也是这样。而优化是一个迭代过程，每个案例中都是独一无二的，有一系列技术可以用来检测数据库系统每个案例中的低效率的例子。

在本章中，我们将介绍一些重要的因素，在设计新索引和优化现有系统时需要牢记这些因素。

聚集索引设计注意事项

每次更改集群索引键的值时，都会发生两件事。首先，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。 这是理论上的限制，创建一个选择性这么差的索引显然是个不好的方式。

让我们看看在非惟一聚集索引中uniquifiers引入的开销。所示的代码在清单7-1中，创建了三个相同结构的不同表，并使用65,536行填充它们。表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(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 row
,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在数据的可变长度部分存储一个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不会将偏移量信息存储在NULL数据的尾随列的可变长度偏移数组中。尽管如此，uniquifier在dbo.NonUniqueCIDups表中也引入了8个字节的开销。 这八个字节由一个4字节的unquifier值，一个双字节的可变长度数据偏移数组条目和一个存储行中可变长度列数的双字节条目组成。

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

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

提示：如果预计聚簇索引值中存在大量重复项，可以将整数标识列作为最右边的列添加到索引中，从而实现唯一性。 与uniquifiers引入的最多8字节的不可预测存储开销相比，这将为每行增加一个4字节的可预测存储开销。当你通过其该行的聚簇索引列使用该行时，这还可以提高单个查找操作的性能。

用最小化插入新行引起索引碎片化的方式设计聚簇索引是有好处的。实现这一点的方法之一是不断增加聚簇索引值。 标识列上的索引就是一个这样的例子。 另一个示例是使用插入时的当前系统时间填充的datetime列。

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

下一个问题更复杂。在索引不断增加的情况下，数据总是插入到索引的末尾。一方面，它可以防止页面拆分、减少碎片，另一方面，它会置于危险，就是序列化延迟，当多个会话试图修改同一数据页和/或分配新页面或区域时会发生序列化延迟。sql服务器不允许多个会话更新相同的数据结构，只能进行序列化操作。

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

最后，如果一个系统有一组需要经常执行的重要查询语句，最好是考虑群集索引，因为它可以优化这些索引。群集索引能消除高消耗的查找键操作，提高了系统的性能。

虽然可以通过覆盖非聚集索引优化此类查询，但它并不是在每个情况下都很理想的解决方案。有时候它需要创建非常宽泛的非聚集索引，这样会消耗磁盘和缓冲池中的大量存储空间。

另一个重要因素是修改列的频率。把经常修改的列添加到非聚集索引中，需要sql服务器在多个地方更改数据，这样会对系统的更新性能产生负面影响而且增加阻塞。

尽管如此，不是每次都能设计出能够满足所有要求的集群索引的准则。此外，不要把这些准则当做绝对的要求。你要分析系统，业务需求，工作量和查询，然后选择对您有利的集群索引，即使你的选择会违反准则。

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

我们经常用标识、序列和单元素标识符作为聚集索引键。和其他情况一样，这种做法有它自己的利弊。

定义在此类列上的聚集索引是唯一的、静态的、狭窄的。此外，标识和序列不断增加可以减少指指针碎片。

理想用例之一是目录实体表。可以把存储了客户、文章或设备的表当成示例。这些表存储了几千行，甚至几百万行，虽然数据相对是静态的，但热点不是问题。另外，这类表格通常用外键连接.非常简洁有效的整数或大整数列的索引能提高查询的性能。

笔记：我们将在第8章“约束”中更详细地讨论外键约束。

在事务表下，标识列或序列列上的聚集索引的效率比较低，它们高速收集大量数据，所以引入了潜在的热点。Uniqueidentifiers，在另一方面，一般不用唯一标识符作为聚集或非聚集索引的对象。用newid()函数生成的随机值大大增加了索引碎片。另外，唯一标识符降低了批量操作的性能。我们看一个示例来创建两个表：一个在标识列上有聚集索引，另一个在统一标识列上有聚集索引。在下一步，我们将在两个表中插入65,536行记录。您可以在清单7-4中看到步骤的代码。

清单7-4 Uniqueidentifiers：表创建(
    
     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 将数据插入表中：执行计划

就像你看到的，uniqueidentifier列上的索引有另一个排序运算符。SQL Server在插入之前对随机生成的uniqueidentifier值进行排序，这种操作会降低查询的性能。接下来我们在表中插入另一批行并检查索引碎片。执行此操作的代码如清单7-5所示。图7-4显示了查询的结果。

清单7-5 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列上的索引类似于标识列和序列列上的索引;但是，你要记住，uniqueidentifier数据类型使用16字节的存储空间，而4字节的int或8字节的bigint数据类型。

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

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

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

在这种情况下，一种可能的改进是不在uniqueidentifier列上创建索引，而是使用CHECKSUM（）函数创建计算列，然后对其进行索引。 代码如清单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索引非常分散，但与uniqueidentifier列上的索引（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字节（非聚集索引键的最大大小）的字符串列时，可以使用同样的技术，即使这样的索引不支持范围扫描操作，它也可以用于点查找。

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值