从性能的角度谈SQL Server聚集索引键的选择

发表于:2012-5-07 10:30

字体: | 上一篇 | 下一篇 | 我要投稿

 作者:宋沄剑    来源:51Testing软件测试网采编

#
server
#
SQL
分享:

  简介

  在SQL Server中,数据是按页进行存放的。而为表加上聚集索引后,SQL Server对于数据的查找就是按照聚集索引的列作为关键字进行了。因此对于聚集索引的选择对性能的影响就变得十分重要了。本文从旨在从性能的角度来谈聚集索引的选择,但这仅仅是从性能方面考虑。对于有特殊业务要求的表,则需要按实际情况进行选择。

  聚集索引所在的列或列的组合最好是唯一的

  这个原因需要从数据的存放原理来谈。在SQL Server中,数据的存放方式并不是以行(Row)为单位,而是以页为单位。因此,在查找数据时,SQL Server查找的最小单位实际上是页。也就是说即使你只查找一行很小的数据,SQL Server也会将整个页查找出来,放到缓冲池中。

  每一个页的大小是8K。每个页都会有一个对于SQL Server来说的物理地址。这个地址的写法是 文件号:页号(理解文件号需要你对文件和文件组有所了解).比如第一个文件的第50页。则页号为1:50。当表没有聚集索引时,表中的数据页是以堆(Heap)进行存放的,在页的基础上,SQL Server通过一个额外的行号来唯一确定每一行,这也就是传说中的RID。RID是文件号:页号:行号来进行表示的,假设这一行在前面所说的页中的第5行,则RID表示为1:50:5,如图1所示。

图1.RID的示例

  从RID的概念来看,RID不仅仅是SQL Server唯一确定每一行的依据,也是存放行的存放位置。当页通过堆(Heap)进行组织时,页很少进行移动。

  而当表上建立聚集索引时,表中的页按照B树进行组织。此时,SQL Server寻找行不再是按RID进行查找,转而使用了关键字,也就是聚集索引的列作为关键字进行查找。假设图1的表中,我们设置DepartmentID列作为聚集索引列。则B树的非叶子节点的行中只包含了DepartmentID和指向下一层节点的书签(BookMark)。

  而当我们创建的聚集索引的值不唯一时,SQL Server则无法仅仅通过聚集索引列(也就是关键字)唯一确定一行。此时,为了实现对每一行的唯一区分,则需要SQL Server为相同值的聚集索引列生成一个额外的标识信息进行区分,这也就是所谓的uniquifiers。而使用了uniquifier后,对性能产生的影响分为如下两部分:

  ● SQL Server必须在插入或者更新时对现在数据进行判断是否和现有的键重复,如果重复,则需要生成uniquifier,这个是一笔额外开销。

  ● 因为需要对相同值的键添加额外的uniquifier来区分,因此键的大小被额外的增加了。因此无论是叶子节点和非叶子节点,都需要更多的页进行存储。从而还影响到了非聚集索引,使得非聚集索引的书签列变大,从而使得非聚集索引也需要更多的页进行存储。

  下面我们进行测试,创建一个测试表,创建聚集索引。插入10万条测试数据,其中每2条一重复,如图2所示。

图2.插入数据的测试代码

21/212>
《2023软件测试行业现状调查报告》独家发布~

关注51Testing

联系我们

快捷面板 站点地图 联系我们 广告服务 关于我们 站长统计 发展历程

法律顾问:上海兰迪律师事务所 项棋律师
版权所有 上海博为峰软件技术股份有限公司 Copyright©51testing.com 2003-2024
投诉及意见反馈:webmaster@51testing.com; 业务联系:service@51testing.com 021-64471599-8017

沪ICP备05003035号

沪公网安备 31010102002173号