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

集群索引:通往SQL Server索引级别3的阶梯

By David Durant

 

本文是楼梯系列的一部分:SQL Server索引的阶梯

索引是数据库设计的基础，并告诉开发人员使用数据库非常了解设计器的意图。不幸的是，当性能问题出现时，索引常常被添加到事后。这里最后是一个简单的系列文章，它应该能让任何数据库专业人员快速“跟上”他们的步伐

这个阶梯的前面的级别提供了一般的索引和非聚集索引的概述。它总结了关于SQL Server索引的关键概念。当请求到达数据库时，无论是SELECT语句还是INSERT、UPDATE或DELETE语句，SQL Server只有三种可能的方法来访问语句中引用的表的数据:

1.访问非聚集索引，避免访问表。只有在索引包含该查询请求的该表的所有数据时才有可能

2.使用search key(s)访问索引，然后使用选中的书签(s)来访问表的各个行。

3.忽略索引并搜索请求行的表。

这个层次的开始是关注上面列表中的第三个选择;搜索表。这又将引导我们讨论集群索引;在第2级中提到的，但未涉及的主题。

我们将在这个级别使用的主要AdventureWorks数据库表是SalesOrderDetail表。在121317行中，它足够大，足以说明在表上有聚集索引的一些好处。而且，有两个外键，它非常复杂，足以说明您必须对集群索引做出的设计决策。

示例数据库

虽然我们已经在第1级讨论了示例数据库，但它在这个时候重复。在整个楼梯中，我们将使用例子来阐明概念。这些示例基于Microsoft AdventureWorks示例数据库。我们专注于销售订单。5个表将提供事务性和非事务性数据的良好组合;客户，销售人员，产品，销售订单，和销售细节。为了保持注意力集中，我们使用了列的一个子集。因为AdventureWorks是一种标准化的，销售人员信息被分解为三个表:销售人员、员工和联系人。

在整个Stairway中，我们使用以下两个术语，指的是“行项目”和“订单细节”。前者是更常见的商业术语;后者出现在AdventureWorks表的名称中。

完整的表集以及它们之间的关系如图1所示。

图1:这个表使用Stairway中的例子

注:

在这个阶梯级别显示的所有TSQL代码都可以连同文章一起下载。

聚集索引

我们首先提出以下问题:如果不使用非聚集索引，那么在表中找到一行(s)需要多少工作?是否为被请求的行搜索表，意味着扫描无序表中的每一行?或者，SQL Server可以永久地对表中的行进行排序，以便能够快速地通过搜索键访问它们，就像它通过搜索键快速访问非聚集索引的条目一样?答案取决于您是否指示SQL服务器在表上创建集群索引。

与非聚集索引不同，它们是单独的对象，占用它们自己的空间，集群索引和表是相同的。通过创建集群索引，您可以指示SQL Server将表的行排序为索引键序列，并在以后的数据修改中维护该序列。即将到来的级别将查看生成的内部数据结构来完成此任务。但是现在，把聚集索引看作是可排序的表。给定一个行的索引键值，SQL Server可以快速访问该行;然后可以按顺序从这一行开始。

出于演示的目的，我们创建了示例表的两个副本，SalesOrderDetail;一个没有索引，一个有聚集索引。对于索引的键列，我们做出了与AdventureWorksdatabase的设计者相同的选择:SalesOrderID / SalesOrderDetailID。清单1中的代码制作了SalesOrderDetail表的副本。我们可以在任何时候重新运行这段代码，我们希望以“clean slate”开始。

 1 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级，我们将重点从逻辑转移到物理上，引入页面和区段，并检查索引的物理结构。

 

 

 原文地址：http://www.sqlservercentral.com/articles/Stairway+Series/72351/

 

转载于:https://www.cnblogs.com/caiduncheng/p/7878240.html