[翻译] 聚集索引表 VS 堆表

问题描述

     创建一个新表时，一个非常重要的设计原则就是创建还是不创建聚集索引的决定。没有聚集索引的表被称为堆，拥有聚集索引的表叫聚集索引表。 聚集索引表比堆表拥有一些好处（优势），比如聚集索引表是基于聚集索引键顺序存储的，正因为如此，所以通过聚集索引可以快速查找某一行；可以通过重建聚集 索引重新组织数据。当你对表进行INSERT、UPDATE、DELETE等操作时可能会使物理数据变得碎片化，这种碎片化可能会导致你浪费大量数据库存 储空间，因为本来可以存储在一页的数据，需要存储在多个数据页上（Pages）,那么，我们该怎么做呢？

解决方案

  我们要解决的首要问题是发生在数据库正常活动中的碎片。你的表是否拥有聚集索引 将决定您是否可以在数据库物理层面很容易的解决碎片化问题。因为堆或聚集索引决定你表数据的物理存储，每个表要么拥有一个聚集索引或没有一个聚集索引，所 以每个表要么是一个堆或聚集索引表。

让我们来看看一个堆表和聚集索引表之间的差异：

HEAP

    数据存储没有任何特定的顺序。

    不能快速的找到特定数据，除非也有非聚集索引。

    数据页之间没有指针关联，所以顺序访问需要重新返回到索引分配映射（IAM）页

    既然没有聚集索引，所以不用额外的时间去维护聚集索引。

    既然没有聚集索引，所以不用额外的空间去存储聚集索引树。

    堆表的索引在sys.indexes目录视图的记录的index_id字段值为0

Clustered Table

     数据存储基于聚集索引键顺序存储。

     如果查询时使用聚集索引列，数据可以基于聚集索引键快速检索到。

     数据页之间有指针链接，可以更快速的顺序访问。

     当INSERT、UPDATE、DELETE操作时，需要额外的时间维护聚集索引。

     需要额外的空间存储聚集索引树。

     聚集索引表在sys.indexes目录视图的记录的index_id值为1.

所以，基于以上你可以看到有一个表是否具有聚簇索引将决定表的一些根本性的不同之处。

碎片化问题

所有的表都会发生的一个问题就是碎片化的问题。根据不同的操作，比如删除，插入和更新，您的堆表和聚集索引表将会变得越来越碎片化。碎片化很多时候取决于INSERT、UPDATE、DELTE这类操作，以及用作聚集索引的键。

  如果您的堆表只有INSERT操作，你的表不会变得碎片化，因为只有新的数据写入。

  如果您的聚集索引键是连续的，比如一个自增字段。并且对该表你只有INSERT操作，这同样也不会变得碎片化，因为新的数据总是写在聚簇索引的后面。

  但是，如果你的表是一个堆或聚集表，并有大量的插入，更新和删除操作，数据页碎片化可能会变得越来越严重。这不仅会导致浪费额外的空间，而且需要读取额外的数据页来满足查询。 

  当一个表在堆上创建，SQL Server不会强迫在那个新数据页（New Page）写入数据。每当新的数据写入时，该数据总是写在表的末端，或者分配给该表中的下一个可用的页面上。当数据被删除时，数据页上的空间释放出来，但 它不重复使用，因为新数据总是写入到下一个可用的页面。

  具有聚簇索引，根据索引键，新的记录可能会被写入到现有的页面，这些页面可能存在的空闲的空间或者有可能需要分割成多个页面的页面。以便插入新的数据。删 除时会发生同样的问题时，与一个堆对比，但是这些空闲间可以再次使用，如果数据需要插入到具有可用空间的现有页面中的一个。

  所以，基于以上叙述 ，你的堆表可能变得比你的聚集表更加支离破碎。

查看碎片化
要确定您的聚集索引表或堆表是否碎片化，你要么通过运行DBCC SHOWCONTIG（SQL SEREVER 2000或SQL SEREVER 2005）查看，或使用新的DMVsys.dm_db_index_physical_stats（SQL SERVER 2005 以及以后版本）。这些命令能让你查看表中可能存在的碎片化问题。如需更多相关信息，看看以前的这篇文章提示：SQL Server 2000 to 2005 Crosswalk - Index Rebuilds.

解决碎片化

聚集索引表

    解决聚集索引的碎片化可以很容易地通过重建或重新组织你的聚集索引来完成。这表现在这之前的提示： SQL Server 2000 to 2005 Crosswalk - Index Rebuilds

堆表

对于堆表来说，这个不太容易的。您可以采取以下不同的方法来解决碎片问题：

1. 对堆表创建一个聚集索引
2. 创建一个新的堆表，并根据某种顺序将旧表中的数据插入到新表
3. 导出数据，截断表并导入数据返回到表

附加信息

    当你通过企业管理器或Management Studio创建一个新表并在新表中指定一个主键，管理工具会自动为其创建一个聚集索引，但可以被重写。当通过脚本创建一个新表时，你需要明确指定创建聚 集索引。所以，正是由于主键关系，你大部分的表将会拥有一个聚集索引，但如果创建表时，你不指定一个主键或建立聚集索引，该表的数据将被存储为一个堆。

下一步

   维持表和索引的碎片化在控制范围内是保持数据库最佳性能的一个关键过程。现在你可以明白一个堆与聚集索引表在解决碎片化上的不同，看看你的表结构，看看你需要解决这些问题。

   即使对所有表一个星期做一次索引重建，你的堆表是永远不会解决琐碎化问题的，所以你需要想出另一种策略来处理堆表的碎片问题。

   一起来看看这些相关技巧：

      SQL Server 2000 to 2005 Crosswalk - Database Fragmentation.

      SQL Server 2000 to 2005 Crosswalk - Index Rebuilds.

  基于上述论证，似乎所有的表都应该有一个聚集索引。在大多数情况下是这样，但也可能由于某种原因，你不希望有一个聚集索引。一个原因可能是该表只有INSERT操作，例如一个日志记录的表。但是毫无疑问，有聚集索引一定好过没有聚集索引