(转)SQL Server索引进阶第三篇：聚集索引

SQL Server索引进阶第三篇：聚集索引

索引设计是数据库设计中比较重要的一个环节，对数据库的性能其中至关重要的作用，但是索引的设计却又不是那么容易的事情，性能也不是那么轻易就获取到的，很多的技术人员因为不恰当的创建索引，最后使得其效果适得其反，可以说“成也索引，败也索引”。

 

本系列文章来自Stairway to SQL Server Indexes，然后经过我们团队的理解和整理发布在agilesharp，希望对广大的技术朋友在如何使用索引上有所帮助。

 

系列文章索目录：

SQL Server索引进阶第一篇：索引介绍
SQL Server索引进阶第二篇：深入非聚集索引
SQL Server索引进阶第三篇：聚集索引
SQL Server索引进阶第四篇：页和区

SQL Server索引进阶第五篇：索引包含列
SQL Server索引进阶第六篇：书签
SQL Server索引进阶第七篇：过滤的索引

SQL Server索引进阶第八篇：唯一索引
SQL Server索引进阶第九篇：解读执行计划
SQL Server索引进阶第十篇：索引的内部结构
SQL Server索引进阶第十一篇：索引碎片分析与解决（上）
SQL Server索引进阶第十一篇：索引碎片分析与解决（中）-碎片发生原理深度剖析
SQL Server索引进阶第十二篇：索引的创建，修改和删除
SQL Server索引进阶第十三篇：Insert,Update,Delete语句
SQL Server索引进阶第十四篇：索引统计
SQL Server索引进阶第十五篇：索引的最佳实践

 

在上一篇文章中，我们已经介绍了非聚集索引相关的内容。在本篇中，我们会介绍与聚集索引相关的话题。

 

首先，我们综合之前的文章，再来思考一下数据库接收到请求之后（Select,Update,Delete,Insert），获取数据的几种方式:

 

1.仅仅只访问非聚集索引而不访问底层的数据表来获取数据。这种情况只有当索引中包含了所有要请求的数据的时候才发生。

2.通过在索引中查找请求传入的关键字，找到对应的索引信息之后，然后通过索引定位到数据表中的数据列获取额外的信息。

3.不管索引，直接去底层的数据表中获取需要的数据列。

 

好，我们本篇就关注在第三种获取数据的方法。正如之前一样，我们还是使用SQL Server自带的数据库AdventureWorks，此时，我们只要关注在SalesOrderDetail表，因为表中包含了几十万条数据，可以让我们尽情的测试。另外，和之前一样，还采用了5个与销售和订单相关的表，大家可以参考之前的第一篇文章。

 

聚集索引示例

首先，我们考虑这样几个问题：

1.如果我们在一个没有非聚集索引的表中需找一行数据，所花的成本是多少？

2.在一个无序的表中查找需要的数据的时候是否就意味着对表中的每一行都进行扫描？

3.SQL Server是否可以把表中的数据进行永久的排序，使得数据的检索更快，就和我们之前使用关键字查找非聚集索引结构一样（我们之前的非聚集索引的表中的底层额数据是无序的）？

 

非聚集索引与底层的数据表不是保存在一起的，非聚集索引中包含了数据表中的一部数据，而且还包含了指向底层数据行的指针。聚集索引则不一样，它是和数据表在一起的。

 

通过在表上面建立聚集索引之后，SQL Server就会对表中的数据按照索引键进行排序，并且在每次数据修改的时候都对排序进行维护。现在我们没有必须知道太多的细节，只要知道：建立了聚集索引的表是一个有序表就行了，而且对于每一行数据都存在一个索引键，使得SQL Server可以通过这个键更快的访问每一行数据。

 

说了这么多，我们还是来看几个简单的小例子。我们会创建两个表，表中的数据来SalesOrderDetail。一个表上面有聚集索引，一个没有，代码如下：

1. IF EXISTS (SELECT * FROM sys.tables
3. WHERE OBJECT_ID = OBJECT_ID('dbo.SalesOrderDetail_index'))
5. DROP TABLE dbo.SalesOrderDetail_index
7. GO
11. IF EXISTS (SELECT * FROM sys.tables
13. WHERE OBJECT_ID = OBJECT_ID('dbo.SalesOrderDetail_noindex'))
15. DROP TABLE dbo.SalesOrderDetail_noindex
17. GO
21. SELECT * INTO dbo.SalesOrderDetail_index FROM Sales.SalesOrderDetail
23. SELECT * INTO dbo.SalesOrderDetail_noindex FROM Sales.SalesOrderDetail
25. GO
29. CREATE CLUSTERED INDEX IX_SalesOrderDetail ON
31. dbo.SalesOrderDetail_index (SalesOrderID, SalesOrderDetailID)
33. GO

复制代码

 

 

在SalesOrderDetail没有建立聚集索引之前，数据是这样的：

 

20120827085222.png(34.67 K)

9/9/2012 11:14:21 AM

 

 

 

当我们把SalesOrderDetail导入到了dbo.SalesOrderDetail_index表中，然后建立了索引之后，数据是这样的：

 

20120827085237.png(40.68 K)

9/9/2012 11:14:21 AM

 

 

看完了上面一个小例子，我们就来谈一谈与聚集索引相关的内容。

 

聚集索引

 

我们可以在表中任何的列上面建立聚集索引，并不一定非得要在主键上面建立，只不过SQL Server在创建主键的时候，默认的就创建了聚集索引。

 

另外还有一点需要讲清楚的就是：因为聚集索引的每一个条目就是数据表中的数据行，所以通过聚集索引查找数据的时候，没有必要进行额外的“跳转”或者说没有必要进行所谓的书签查找。

 

啥都别说了，上个图，先了解一下吧：

 

 

20120827085343.png(42.61 K)

9/9/2012 11:14:21 AM

 

 

 

估计看到这个图之后，很多朋友就越来越糊涂了，因为存在下面的一个疑惑：

不是说：非聚集索引与底层的数据表不是保存在一起的，非聚集索引中包含了数据表中的一部数据，而且还包含了指向底层数据行的指针。聚集索引则不一样，它是和数据表在一起的。

但时从上面图的来看，聚集索引也是和数据表分开保存的！

 

如果这么问，说明你在思考了；如果没有问，说明你已经懂了。

我承认，之前说的有一些不准确，只要是为了让大家不要再拘泥于细节，有个总体的把握，现在是时候澄清一下内容了。

 

 

20120827085358.png(42.55 K)

9/9/2012 11:14:21 AM

 

 

 

我们现在应该这样说才算准确：非聚集索引中的数据与底层的数据表不是保存在一起的，因为各自都维护着一份数据，而且非聚集索引中包含了数据表中的一部数据，而且还包含了指向底层数据行的指针。而且在非聚集索引的结构中，最下面的叶子节点不是底层的数据表的数据页。

聚集索引则不一样，因为整个数据表中的数据页是它的节点，所以，聚集索引中包含了整个表的数据。

如何选择合适的列建立聚集索引

 

到这里，文章可能就和原本的英文有点不同了，以为我们团队认为这里应该加入更多的实际的内容，所以做了大胆的改动。

可以说这个话题是个被讨论很多，也存在很多争论的话题，我们这里主要不会对网络上面的每个论点都做解释，而是从分析的角度出发，帮大家理清本质的东西，好让大家自己决定。

 

首先解释一下聚集索引和非聚集索引在进行不同操作时的特点，如下表：

操作名字	聚集索引和非聚集索引比较
读取	聚集索引：很快，因为没有额外的书签查找操作。 非聚集索引：相对而言，可能会慢一些，尤其适当查找的数据列不包含在索引中的时候。如何包含了，也可能慢一些，因为可能查找的索引页要多一些。
更新	聚集索引：如果修改的是非索引列的数据，那是没问题了，如果修改的列包含在索引列中，波动就很大，因为会涉及到数据的重新排序，还有表上面的非聚集索引也会受到影响。 非聚集索引：前者类似，后者修改涉及到的波动少一些。
插入、删除	聚集索引：快些。 因为如果是非聚集索引，每次插入新数据之后，也需要同时插入数据到非聚集索引中，因为数据表和索引各自维护一份数据。 删除数据，道理亦然。

 

另外，还有一点需要说。如果添加的数据不是添加在数据表中数据页的最后面，那么可能这个插入数据的操作就会涉及到分页操作。此时，分页的波动对非聚集索引影响小一点。如果数据插入的时候，采用的自动增长列作为主键，而且建立了聚集索引，那么每次数据的插入都会在末尾，那么就不存在这个问题了。

 

最后要提的一点就是索引列大小的问题。在一个建立了聚集索引的表上面在建立非聚集索引，那么这个非聚集索引会在内部使用聚集索引来定位数据，如果聚集索引所在列很长，那么势必会使得非聚集索引所在空间变大，甚至产生更多的原本不需要的页拆分。

 

好了，今天就到这里，我们在后续会介绍更多的具体的案例和实践。