翻译：集群索引:通往SQL Server索引级别3的阶梯
集群索引:通往SQL Server索引级别3的阶梯
David Durant,2013/01/25(第一次出版:2011/06/22)
该系列
本文是楼梯系列的一部分:SQL Server索引的阶梯
索引是数据库设计的基础，并告诉开发人员使用数据库非常了解设计器的意图。不幸的是，当性能问题出现时，索引常常被添加到事后。这里最后是一个简单的系列文章，它应该能让任何数据库专业人员快速“跟上”他们的步伐
这个阶梯的前面的级别提供了一般的索引和非聚集索引的概述。它总结了关于SQL Server索引的关键概念。当请求到达数据库时，无论是SELECT语句还是INSERT、UPDATE或DELETE语句，SQL Server只有三种可能的方法来访问语句中引用的表的数据:
访问非聚集索引，避免访问表。只有在索引包含该查询请求的该表的所有数据时才有可能
使用search key(s)访问索引，然后使用选中的书签(s)来访问表的各个行。
忽略索引并搜索请求行的表。
这个层次的开始是关注上面列表中的第三个选择;搜索表。这又将引导我们讨论集群索引;在第2级中提到的，但未涉及的主题。
我们将在这个级别使用的主要AdventureWorks数据库表是SalesOrderDetail表。在121317行中，它足够大，足以说明在表上有聚集索引的一些好处。而且，有两个外键，它非常复杂，足以说明您必须对集群索引做出的设计决策。
示例数据库
虽然我们已经在第1级讨论了示例数据库，但它在这个时候重复。在整个楼梯中，我们将使用例子来阐明概念。这些示例基于Microsoft AdventureWorks示例数据库。我们专注于销售订单。5个表将提供事务性和非事务性数据的良好组合;客户，销售人员，产品，销售订单，和销售细节。为了保持注意力集中，我们使用了列的一个子集。因为AdventureWorks是一种标准化的，销售人员信息被分解为三个表:销售人员、员工和联系人。
在整个楼梯中，我们使用以下两个术语，指的是“行项目”和“订单细节”。前者是更常见的商业术语;后者出现在AdventureWorks表的名称中。
完整的表集以及它们之间的关系如图1所示。
图1:在这个楼梯的例子中使用的表
注:
在这个阶梯级别显示的所有TSQL代码都可以连同文章一起下载。
聚集索引
我们首先提出以下问题:如果不使用非聚集索引，那么在表中找到一行(s)需要多少工作?是否为被请求的行搜索表，意味着扫描无序表中的每一行?或者，SQL Server可以永久地对表中的行进行排序，以便能够快速地通过搜索键访问它们，就像它通过搜索键快速访问非聚集索引的条目一样?答案取决于您是否指示SQL服务器在表上创建集群索引。
与非聚集索引不同，它们是单独的对象，占用它们自己的空间，集群索引和表是相同的。通过创建集群索引，您可以指示SQL Server将表的行排序为索引键序列，并在以后的数据修改中维护该序列。即将到来的级别将查看生成的内部数据结构来完成此任务。但是现在，把聚集索引看作是可排序的表。给定一个行的索引键值，SQL Server可以快速访问该行;然后可以按顺序从这一行开始。
出于演示的目的，我们创建了示例表的两个副本，SalesOrderDetail;一个没有索引，一个有聚集索引。对于索引的键列，我们做出了与AdventureWorksdatabase的设计者相同的选择:SalesOrderID / SalesOrderDetailID。清单1中的代码制作了SalesOrderDetail表的副本。我们可以在任何时候重新运行这段代码，我们希望以“干净的slate”开始。
IF EXISTS (SELECT * FROM sys.tables 
WHERE OBJECT_ID = OBJECT_ID('dbo.SalesOrderDetail_index'))
DROP TABLE dbo.SalesOrderDetail_index;
GO
IF EXISTS (SELECT * FROM sys.tables 
WHERE OBJECT_ID = OBJECT_ID('dbo.SalesOrderDetail_noindex'))
DROP TABLE dbo.SalesOrderDetail_noindex;
GO
SELECT * INTO dbo.SalesOrderDetail_index FROM Sales.SalesOrderDetail;
SELECT * INTO dbo.SalesOrderDetail_noindex FROM Sales.SalesOrderDetail;
GO
CREATE CLUSTERED INDEX IX_SalesOrderDetail
ON dbo.SalesOrderDetail_index (SalesOrderID, SalesOrderDetailID)
GO
清单1:创建SalesOrderDetail表的副本
因此，假设SalesOrderDetail表在创建集群索引之前是这样的:
SalesOrderID SalesOrderDetailID ProductID OrderQty UnitPrice
69389 102201 864 3 38.10
56658 59519 711 1 34.99
59044 70000 956 2 1430.442
48299 22652 853 4 44.994
50218 31427 854 8 44.994
53713 50716 711 1 34.99
50299 32777 739 1 744.2727
45321 6303 775 6 2024.994
72644 115325 873 1 2.29
48306 22705 824 4 141.615
69134 101554 876 1 120.00
48361 23556 760 3 469.794
53605 50098 888 1 602.346
48317 22901 722 1 183.9382
66430 93291 872 1 8.99
65281 90265 889 2 602.346
52248 43812 871 1 9.99
47978 20189 794 2 1308.9375
After creating the clustered index shown above, the resulting table / clustered index would look like this:
SalesOrderID SalesOrderDetailID ProductID OrderQty UnitPrice
43668 106 722 3 178.58
43668 107 708 1 20.19
43668 108 733 3 356.90
43668 109 763 3 419.46
43669 110 747 1 714.70
43670 111 710 1 5.70
43670 112 709 2 5.70
43670 113 773 2 2,039.99
43670 114 776 1 2,024.99
43671 115 753 1 2,146.96
43671 116 714 2 28.84
43671 117 756 1 874.79
43671 118 768 2 419.46
43671 119 732 2 356.90
43671 120 763 2 419.46
43671 121 755 2 874.79
43671 122 764 2 419.46
43671 123 716 1 28.84
43671 124 711 1 20.19
43671 125 708 1 20.19
43672 126 709 6 5.70
43672 127 776 2 2,024.99
43672 128 774 1 2,039.99
43673 129 754 1 874.79
43673 130 715 3 28.84
43673 131 729 1 183.94
当您查看上面显示的示例数据时，您可能会注意到每个SalesOrderDetailID值都是惟一的。不要混淆;SalesOrderDetailID并不是表的主键。SalesOrderID / SalesOrderDetailID的组合是表的主键;以及聚集索引的索引键。
了解集群索引的基础知识
集群索引键可以由您选择的任何列组成;它不必基于主键。在我们的示例中，最重要的是，键的左大部分列是外键，SalesOrderID值。因此，销售订单的所有行项都在SalesOrderDetail表中连续出现。
请记住这些关于SQL Server集群索引的附加点:
由于聚集索引的条目是表的行，在集群索引条目中没有书签值。当SQL Server已经在一行时，它不需要一个信息来告诉它在哪里找到这一行。
集群索引总是覆盖查询。由于索引和表是相同的，所以表的每一列都在索引中。
在表上有集群索引不会影响您在该表上创建非聚集索引的选项。
选择聚集索引键列(s)
每个表最多只能有一个集群索引。表的行只能在一个序列中。你需要决定哪个序列，如果有的话，对每个表都是最好的;并且，如果可能的话，在表填充数据之前创建聚集索引。在做这个决定的时候，要记住，排序不仅意味着排序，还意味着分组;按销售顺序分组。
这就是为什么AdventureWorksdatabase的设计者在SalesOrderID中选择SalesOrderDetailID作为SalesOrderDetail表的序列;这是线条项目的自然顺序。
例如，如果用户请求一个订单的行项，他们通常会请求该订单的所有行项。一个典型的销售订单表格告诉我们，订单的打印副本总是包含所有的行项目。按销售订单的顺序排列，是销售订单业务的性质。仓库可能偶尔会有请求，希望通过产品而不是销售订单来查看产品;但是大部分的请求;例如销售人员、客户或打印发票的程序，或计算每个订单总价值的查询;对于任何给定的销售订单，将需要所有的行项目。
然而，仅用户需求并不能确定什么是最好的集群索引。本系列的未来水平将涵盖索引的内部;因为索引的某些内部方面也会影响到集群索引列的选择。
堆
如果表中没有聚集索引，那么表称为堆。每个表要么是堆，要么是聚集索引。因此，尽管我们经常声明每个索引属于两种类型之一，集群或非集群;同样重要的是要注意，每个表都属于两种类型之一;它是一个聚集索引，或者是堆。开发人员经常说，表“有”或“没有”聚集索引，但更有意义的是，表“是”或“不是”聚集索引。
在寻找行(不包括使用非聚集索引)时，SQL Server只有一种方法可以搜索堆(不包括使用非聚集索引)，这将从表的第一行开始，然后遍历表，直到所有的行都被读取。没有序列，就没有搜索键，也无法快速导航到特定的行。
将聚集索引与堆进行比较
为了评估集群索引与堆的性能，清单1列出了两份SalesOrderDetailtable的副本。一个副本是堆版本，另一个副本是在原始表上创建相同的集群索引(SalesOrderID、SalesOrderDetailID)。两个表都没有任何非聚集索引。
我们将对表的每个版本运行相同的三个查询;一个检索单个行，一个为单个订单检索所有行，一个为单个产品检索所有行。我们将在下面显示的表中显示SQL和每个执行的结果。
我们的第一个查询检索单个行，执行细节如表1所示。
表1:检索一行
我们的第二个查询为单个销售订单检索所有行，您可以看到表2中的执行细节。
sql select *
从SalesOrderDetail
在SalesOrderID = 43671
堆(s)(11行受影响)
表“SalesOrderDetail_noindex”。扫描计数1，逻辑读1495。
群集索引(11行(s)受影响)
表“SalesOrderDetail_noindex”。扫描计数1，逻辑读3。
将群集索引io的影响从1495读到3读。
与前面的查询相同的统计信息。堆仍然需要一个表扫描，而群集索引将请求的订单的11个细节行组合在一起，以便能够检索11行，就像检索一行的IO所需要的一样。一个即将到来的级别将详细解释为什么不需要额外的读取来检索额外的10行。
表2:为单个SalesOrder检索所有行
第三个查询检索单个产品的所有行，执行结果如表3所示。
SQL
SELECT *
从SalesOrderDetail
在ProductID = 755
堆
(228行受影响)
表“SalesOrderDetail_noindex”。扫描计数1，逻辑读1495。
群集索引(228行(s)受影响)
表“SalesOrderDetail_index”。扫描计数1，逻辑读取1513。
对聚集索引版本的集群索引io的影响稍微大一些;1513阅读与1495读。
在ProductID列上没有非聚集索引的注释帮助查找单个产品的行，这两个版本都需要扫描。由于有集群索引的开销，集群索引版本是稍微大一点的表;因此，扫描它需要更多的读取，而不是扫描堆。
表3:检索单个产品的所有行
我们的前两个查询大大受益于聚集索引的存在;第三个是近似相等的。是否有时聚类索引是一种损害?答案是肯定的，它主要与插入、更新和删除行有关。就像在这些早期的层次中遇到的许多其他的索引一样，它也是一个将在更高层次上更详细地讨论的主题。
一般来说，检索收益大于维护损失;使聚集索引更适合堆。如果您在Azure数据库中创建表，则没有选择;每个表都必须是一个聚集索引。
结论
集群索引是一个排序表，它的序列由您在创建索引时指定，并由SQL Server维护。该表中的任何一行都可以通过其键值快速访问。在索引键序列中，任何一组行，都可以快速访问，因为它们的键的范围很广。
每个表只能有一个集群索引。哪个列应该是聚集索引键列的决定是您将为任何表做出的最重要的索引决定。
在第4级，我们将重点从逻辑转移到物理上，引入页面和区段，并检查索引的物理结构。
