第11周 Designing and Tuning the Indexes

设计和调优索引

不可能定义一个到处都适用的索引策略。每个系统都是独特的，并且需要基于工作负载、业务需求和许多其他因素的自己的索引方法。然而，有几个设计考虑事项和准则可以应用于每个系统。

当我们优化现有系统时也是如此。虽然优化是一个迭代过程，在每种情况下都是独特的，但是有一组技术可以用于检测每个数据库系统中的低效性。

在本章中，我们将讨论在设计新索引和优化现有系统时需要牢记的几个重要因素。

聚集索引设计注意事项

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

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

在非惟一非聚集索引的情况下，行id也存储在非叶索引级别，这反过来减少了每页索引记录的数量，并可能导致索引中额外的中间级别。尽管非叶索引级别通常缓存在内存中，但每当SQL Server遍历非集群索引b树时，都会引入额外的逻辑读取。

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

将聚集索引定义为惟一索引也是有益的。这一点重要的原因并不明显。考虑这样一种场景:一个表没有惟一的聚集索引，而您希望在执行计划中运行一个使用非聚集索引查找的查询。在本例中，如果非聚集索引中的行id不是惟一的，SQL Server将不知道在键查找操作期间选择什么聚集索引行。

SQL Server通过向非惟一c已注册索引添加另一个名为uniquifier的可空整数列来解决这些问题。SQL Server为键值的第一次出现使用NULL填充uniquifier，为插入到表中的每个后续副本自动递增该值。

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

让我们看看uniquifier在非惟一集群索引中引入的开销。清单7-1中所示的代码创建了三个相同结构的不同表，并用65,536行填充它们。dbo表。UniqueCI是惟一定义了惟一集群索引的表。dbo表。NonUniqueCINoDups没有任何重复的键值。最后,dbo表。NonUniqueCDups在索引中有大量的副本。

清单7 - 1。非唯一群集索引:表创建

即使dbo中没有重复的键值。NonUniqueCINoDups表中，仍然有两个额外的字节添加到行中。SQL Server在数据的可变长度部分中存储一个uniquifier，这两个字节由可变长度数据偏移数组中的另一个条目添加。

在这种情况下，当聚集索引有重复的值时，uniquifier又添加了4个字节，这就造成了总共6个字节的开销。

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

现在，让我们看看uniquifier如何影响非聚集索引。清单7-3所示的代码在所有三个表中创建非聚集索引。图7-2显示了这些索引的物理统计数据。

清单7-3：非唯一聚集索引:检查非聚集索引的行大小

dbo中的非聚集索引没有开销。NonUniqueCINoDups表。您应该还记得，SQL Server并不将偏移量信息存储在用于存储尾随列的可变长度偏移量数组中空数据。尽管如此，uniquifier在dbo中引入了8个字节的开销。NonUniqueCIDups表。这8个字节由4字节uniquifier值、2字节可变长度数据偏移数组条目和存储行中可变长度列数的2字节条目组成。

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

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

■提示:如果希望聚集索引值中有大量重复项，可以添加一个整数

标识列作为索引的最右列，从而使其惟一。与uniquifier引入的不可预测的高达8字节的存储开销相比，这为每一行增加了4字节的可预测存储开销。当您通过行的所有聚集索引列引用该行时，这还可以提高单个查找操作的性能。

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

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

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

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

最后，如果一个系统有一组频繁执行的重要查询，那么考虑使用一个集群索引可能是有益的，它可以优化这些查询。这消除了昂贵的键查找操作，并提高了系统的性能。

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

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

综上所述，设计能够满足所有这些准则的聚集索引并不总是可能的。此外，您不应该认为这些指南是绝对的需求。您应该分析系统、业务需求、工作负载和查询，并选择对您有利的集群索引，即使它们违反了其中的一些准则。

标识符、序列和类型

人们通常选择标识符、序列和类型作为聚集索引键。与往常一样，这种方法有它自己的优缺点。

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

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

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

另一方面，uniqueidentifier很少是索引的好选择，不管是集群索引还是非集群索引。使用NEWID()函数生成的随机值大大增加了索引碎片。此外，uniqueidentifier上的索引会降低批处理操作的性能。让我们看一个示例并创建两个表:一个表在标识符列上使用聚集索引，另一个表在uniqueidentifier列上使用聚集索引。下一步，我们将在两个表中插入65,536行。您可以在清单7-4中看到这样做的代码。

我计算机上的执行时间和读取次数如表7-1所示。图7-3显示了这两个查询的执行计划。

表7 - 1：向表中插入数据:执行统计数据

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

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

清单7 - 5：uniqueidentifier:插入行并检查碎片

图7 - 4：索引的碎片化

如您所见，uniqueidentifier列上的索引被严重分割，与identity列上的索引相比，它使用了大约40%的数据页。

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

个人经验

不久前，我参与了一个系统的优化，该系统有一个250gb的表，其中有一个集群索引和三个非集群索引。非聚集索引之一是uniqueidentifier列。通过删除这个索引，我们能够将50,000行批插入的速度从45秒提高到7秒。

当您希望在uniqueidentifier列上创建索引时，有两种常见的用例。第一个是支持跨多个数据库的值的唯一性。考虑一个分布式系统，其中可以将行插入每个数据库。开发人员经常使用uniqueidentifier来确保每个键值在系统范围内都是惟一的。这种实现中的关键元素是如何生成键值。

正如您已经看到的，NEWID()函数或客户机代码中生成的随机值会对系统性能产生负面影响。但是，您可以使用NEWSEQUENTIALID()函数，该函数生成独特且通常不断增加的值(SQL Server会不时重置它们的基本值)。使用NEWSEQUENTIALID()函数生成的uniqueidentifier列上的索引类似于标识符列和序列列上的索引;但是，您应该记住，与4字节int或8字节bigint数据类型相比，uniqueidentifier数据类型使用16字节的存储空间。

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

在需要跨数据库中的所有实体生成惟一键值的情况下，可以考虑跨所有实体使用单个sequence对象。这种方法满足了需求，但是使用的数据类型比uniqueidentifier更小。另一个常见的用例是安全性，其中uniqueidentifier值用作安全令牌或随机对象ID。

在这个场景中，一个可能的改进是使用CHECKSUM()函数创建一个计算过的列，然后索引它，而不需要在uniqueidentifier列上创建索引。代码如清单7-6所示。

清单7-6：使用CHECKSUM():表结构

■提示：您可以在不持久化计算列的情况下索引它。

尽管IDX_Articles_ExternalIdCheckSum索引将被严重分割，但与uniqueidentifier列上的索引(4字节键与16字节键)相比，它将更加紧凑。它还提高了批处理操作的性能，因为排序速度更快，这也需要更少的内存。

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

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

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