[翻译]——集群索引:通往SQL Server索引级别3的阶梯

IF EXISTS (SELECT * FROM sys.tables  2 WHERE OBJECT_ID = OBJECT_ID(‘dbo.SalesOrderDetail_index‘)) 3 DROP TABLE dbo.SalesOrderDetail_index; 4 GO 5 IF EXISTS (SELECT * FROM sys.tables  6 WHERE OBJECT_ID = OBJECT_ID(‘dbo.SalesOrderDetail_noindex‘)) 7 DROP TABLE dbo.SalesOrderDetail_noindex; 8 GO 9 10 11 SELECT * INTO dbo.SalesOrderDetail_index FROM Sales.SalesOrderDetail; 12 SELECT * INTO dbo.SalesOrderDetail_noindex FROM Sales.SalesOrderDetail; 13 GO 14 15 16 CREATE CLUSTERED INDEX IX_SalesOrderDetail 17 ON dbo.SalesOrderDetail_index (SalesOrderID, SalesOrderDetailID) 18 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

 

在创建了上面所示的聚集索引之后，结果表/聚集索引看起来是这样的:

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所示。

SQL	SELECT * FROM SalesOrderDetail WHERE SalesOrderID = 43671 AND SalesOrderDetailID = 120
Heap	(1 row(s) affected) Table ‘SalesOrderDetail_noindex‘. Scan count 1, logical reads 1495.
Clustered Index	(1 row(s) affected) Table ‘SalesOrderDetail_noindex‘. Scan count 1, logical reads 3.
Impact of having the Clustered Index	IO reduced from 1495 reads to 3 reads.
Comments	No surprise.  Table scanning 121,317 rows to find just one is not very efficient.

表1:检索一行

我们的第二个查询为单个销售订单检索所有行，您可以看到表2中的执行细节。

SQL	SELECT * FROM SalesOrderDetail WHERE SalesOrderID = 43671
Heap	(11 row(s) affected) Table ‘SalesOrderDetail_noindex‘. Scan count 1, logical reads 1495.
Clustered Index	(11 row(s) affected) Table ‘SalesOrderDetail_noindex‘. Scan count 1, logical reads 3.
Impact of having the Clustered Index	IO reduced from 1495 reads to 3 reads.
Comments	Same statistics as the previous query.  The heap still required a table scan, while the clustered index grouped the 11 detail rows of the requested order sufficiently close together so that the IO required to retrieve 11 rows was the same as the IO required to retrieve one row.  An upcoming Level will explain in detail why no additional reads were required to retrieve the additional 10 rows.

表2:为单个SalesOrder检索所有行

第三个查询检索单个产品的所有行，执行结果如表3所示。

SQL	SELECT * FROM SalesOrderDetail WHERE ProductID = 755
Heap	(228 row(s) affected) Table ‘SalesOrderDetail_noindex‘. Scan count 1, logical reads 1495.
Clustered Index	(228 row(s) affected) Table ‘SalesOrderDetail_index‘. Scan count 1, logical reads 1513.
Impact of having the Clustered Index	IO slightly greater for the clustered index version; 1513 reads versus 1495 reads.
Comments	Without a nonclustered index on the ProductID column to help find the rows for a single Product, both versions had to be scanned.  Because of the overhead of having a clustered index, the clustered index version is the slightly larger table; therefore scanning it required a few more reads than scanning the heap.

表3:检索单个产品的所有行

我们的前两个查询大大受益于聚集索引的存在;第三个是近似相等的。是否有时聚类索引是一种损害?答案是肯定的，它主要与插入、更新和删除行有关。就像在这些早期的层次中遇到的许多其他的索引一样，它也是一个将在更高层次上更详细地讨论的主题。

一般来说，检索收益大于维护损失;使聚集索引更适合堆。如果您在Azure数据库中创建表，则没有选择;每个表都必须是一个聚集索引。

结论

集群索引是一个排序表，它的序列由您在创建索引时指定，并由SQL Server维护。该表中的任何一行都可以通过其键值快速访问。在索引键序列中，任何一组行，都可以快速访问，因为它们的键的范围很广。

每个表只能有一个集群索引。哪个列应该是聚集索引键列的决定是您将为任何表做出的最重要的索引决定。

在第4级，我们将重点从逻辑转移到物理上，引入页面和区段，并检查索引的物理结构。