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

简介

在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所示。

查看本栏目更多精彩内容:http://www.bianceng.cnhttp://www.bianceng.cn/database/SQLServer/

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

此时,我们来查看这个表所占的页数,如图3所示。

图3.插入重复键后10万数据占了359页

时间: 2024-09-07 20:34:35

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

SQL Server 聚集索引和非聚集索引的区别分析

聚集索引:物理存储按照索引排序非聚集索引:物理存储不按照索引排序优势与缺点聚集索引:插入数据时速度要慢(时间花费在"物理存储的排序"上,也就是首先要找到位置然后插入)查询数据比非聚集数据的速度快 汉语字典的正文本身就是一个聚集索引.比如,我们要查"安"字,就会很自然地翻开字典的前几页,因为"安"的拼音是"an",而按照拼音排序汉字的字典是以英文字母"a"开头并以"z"结尾的,那么&quo

SQL Server 聚集索引和非聚集索引的区别分析_MsSql

聚集索引:物理存储按照索引排序非聚集索引:物理存储不按照索引排序优势与缺点聚集索引:插入数据时速度要慢(时间花费在"物理存储的排序"上,也就是首先要找到位置然后插入)查询数据比非聚集数据的速度快 汉语字典的正文本身就是一个聚集索引.比如,我们要查"安"字,就会很自然地翻开字典的前几页,因为"安"的拼音是"an",而按照拼音排序汉字的字典是以英文字母"a"开头并以"z"结尾的,那么&quo

通过 SQL Server 2005 索引视图提高性能

本文介绍了 SQL Server 2005 Enterprise Edition 中经过改进的索引视图功能.文中对索引视图进行了说明介绍,并讨论了可通过该功能改善性能的一些具体情况 一.索引视图 多年以来,Microsoft SQL Server 一直支持创建称为视图的虚拟表.通常,这些视图的主要作用是: • 提供一种安全机制,将用户限制到一个或多个基表的某个数据子集中. • 提供一种机制,允许开发人员自定义用户通过逻辑方式查看存储在基表中的数据的方式. 通过 SQL Server 2000,S

浅谈SQL Server中的快照

原文:浅谈SQL Server中的快照 简介     数据库快照,正如其名称所示那样,是数据库在某一时间点的视图.是SQL Server在2005之后的版本引入的特性.快照的应用场景比较多,但快照设计最开始的目的是为了报表服务.比如我需要出2011的资产负债表,这需要数据保持在2011年12月31日零点时的状态,则利用快照可以实现这一点.快照还可以和镜像结合来达到读写分离的目的.下面我们来看什么是快照.   什么是快照     数据库快照是 SQL Server 数据库(源数据库)的只读静态视图

谈一谈SQL Server中的执行计划缓存(下)

原文:谈一谈SQL Server中的执行计划缓存(下) 简介     在上篇文章中我们谈到了查询优化器和执行计划缓存的关系,以及其二者之间的冲突.本篇文章中,我们会主要阐述执行计划缓存常见的问题以及一些解决办法.   将执行缓存考虑在内时的流程     上篇文章中提到了查询优化器解析语句的过程,当将计划缓存考虑在内时,首先需要查看计划缓存中是否已经有语句的缓存,如果没有,才会执行编译过程,如果存在则直接利用编译好的执行计划.因此,完整的过程如图1所示. 图1.将计划缓存考虑在内的过程      

谈一谈SQL Server中的执行计划缓存(上)

原文:谈一谈SQL Server中的执行计划缓存(上) 简介     我们平时所写的SQL语句本质只是获取数据的逻辑,而不是获取数据的物理路径.当我们写的SQL语句传到SQL Server的时候,查询分析器会将语句依次进行解析(Parse).绑定(Bind).查询优化(Optimization,有时候也被称为简化).执行(Execution).除去执行步骤外,前三个步骤之后就生成了执行计划,也就是SQL Server按照该计划获取物理数据方式,最后执行步骤按照执行计划执行查询从而获得结果.但查询

收集并存储性能监控器数据到SQL Server表

server|监控|数据|性能 收集并存储性能监控器数据到SQL Server表 ? ? 当我们需要监控数据库SQL Server服务器性能的时候,有些数据库管理人员可能会选择Windows为我们提供的'性能'监控器来操作(开始菜单à管理工具à性能). 如果可以将性能监控器采集到的数据记录到SQL Server 的数据库表中去,很多工作对我们来说也许方便得多.开启性能监控器点击开始菜单à运行à执行(perfmon) 或者 开始菜单à管理工具à性能 ? 就可以看到下面的画面了 ?定义性能监控器LO

用SQL Server 2005索引视图提高性能二

视图限制 如要在 SQL Server 2005 中的视图上创建一个索引,相应的视图定义必须包含: ANY.NOT ANY OPENROWSET.OPENQUERY.OPENDATASOURCE 不精确的(浮型.实型)值上的算术 OPENXML COMPUTE.COMPUTE BY ORDER BY CONVERT 生成一个不精确的结果 OUTER 联接 COUNT(*) 引用带有一个已禁用的聚集索引的基表 GROUP BY ALL 引用不同数据库中的表或函数 派生的表(FROM 列表中的子查询

如何找出你性能最差的SQL Server查询

原文:如何找出你性能最差的SQL Server查询 我经常会被反复问到这样的问题:"我有一个性能很差的SQL Server.我如何找出最差性能的查询?".因此在今天的文章里一些让你很容易找到问题答案的信息和向导. 问SQL Server! SQL Server的一个优点是它本身能回答几乎所有你的问题,因为SQL Server在各个DMV和DMF里存储了很多故障排除信息.另一方面这也是个缺点,因为你必须知道各个DMV/DMF,还有如何把它们解释和关联在一起. 至于你的最差性能SQL Se