开发者社区> 潇湘隐者> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介:
+关注继续查看

问题描述

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

解决方案

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

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

HEAP

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

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

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

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

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

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

image

Clustered Table

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

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

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

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

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

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

image

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

碎片化问题

所有的表都会发生的一个问题就是碎片化的问题。根据不同的操作,比如删除,插入和更新,您的堆表和聚集索引表将会变得越来越碎片化。碎片化很多时候取决于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操作,例如一个日志记录的表。但是毫无疑问,有聚集索引一定好过没有聚集索引

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
索引
索引
49 0
索引组织表
说明聚集索引及索引组织表的关系及使用场景,优势。
1810 0
聚集索引和非聚集索引(转)
原文:http://www.cnblogs.com/aspnethot/articles/1504082.html 索引 官方说法:  聚集索引   一种索引,该索引中键值的逻辑顺序决定了表中相应行的物理顺序。
1066 0
索引
一、索引  在关系数据库中,索引是一种与表有关的数据库结构,它可以使对应于表的SQL语句执行得更快。索引的作用相当于图书的目录,可以根据目录中的页码快速找到所需的内容。当表中有大量记录时,若要对表进行查询,第一种搜索信息方式是全表搜索,是将所有记录一一取出,和查询条件进行一一对比,然后返回满足条件的记录,这样做会消耗大量数据库系统时间,并造成大量磁盘I/O操作;第二种就是在表中建立索引,然后在索引中找到符合查询条件的索引值,最后通过保存在索引中的ROWID(相当于页码)快速找到表中对应的记录。
872 0
索引
索引的概念类似于图书中提示用的术语索引。一本书中的术语索引可以帮助读者很快找到该术语在书中的具体解释信息。
429 0
+关注
潇湘隐者
网名潇湘隐者/潇湘剑客、英文名Kerry,兴趣广泛,广泛涉猎,个性随意,不善言辞。执意做一名会写代码的DBA,混迹于IT行业
文章
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载