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

标签: 性能 角度 sql | 发表时间:2014-07-30 10:07 | 作者:zhongguoren666
分享到:
出处:http://blog.csdn.net

简介

    在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

    图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

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

    

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

     3

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

 

    我们再次插入10万不重复的数据,如图4所示。

     4

    图4.插入10万不重复的建的代码

 

    此时,所占页数缩减为335页,如图5所示。

     5

    图5.插入不重复键后缩减为335页

 

     因此,推荐聚集索引所在列使用唯一键。

 

最好使用窄列或窄列组合作为聚集索引列

    这个道理和上面减少页的原理一样,窄列使得键的大小变小。使得聚集索引的非叶子节点减少,而非聚集索引的书签变小,从而叶子节点页变得更少。最终提高了性能。

 

使用值很少变动的列或列的组合作为聚集索引列

    在前面我们知道。当为表创建聚集索引后。SQL Server按照键查找行。因为在B数中,数据是有序的,所以当聚集索引键发生改变时,不仅仅需要改变值本身,还需要改变这个键所在行的位置(RID),因此有可能使得行从一页移动到另一页。从而达到有序。因此会带来如下问题:

  •     行从一页移动到另一页,这个操作是需要开销的,不仅如此,这个操作还可能影响到其他行,使得其他行也需要移动位置,有可能产生分页
  •     行在页之间的移动会产生 索引碎片
  •     键的改变会影响到非聚集索引,使得非聚集索引的书签也需要改变,这又是一笔额外的开销

     这也就是为什么很多表创建一列与数据本身无关的列作为主键比如AdventureWorks数据库中的Person.Address表,使用AddressID这个和数据本身无关的列作为聚集索引列,如图6所示。而使用AddressLine1作为主键的话,员工地址的变动则可能造成上面列表的问题。

     6

    图6.创建和数据本身无关的一列作为聚集索引列

 

最好使用自增列作为聚集索引列

    这个建议也同样推荐创建一个和数据本身无关的自增列作为聚集索引列。我们知道,如果新添加进来的数据如果聚集索引列需要插入当前有序的B树中,则需要移动其它的行来给新插入的行腾出位置。因此可能会造成分页和索引碎片。同样的,还会造成修改非聚集索引的额外负担。而使用自增列,新行的插入则会大大的减少分页和碎片。

   最近我碰到过一个情况。一个表每隔几个月性能就奇慢无比,初步查看是由于有大量的索引碎片。可是每隔几个月重建一次索引让我无比厌烦。最终我发现,问题是由于当时设计数据库的人员将聚集索引建在了GUID上,而GUID是随机生成的,则可能插入到表的任何位置,从而大大增加了碎片的数量。因此造成上面这种情况。

 

总结

    本文简单介绍了SQL Server存储的原理和应该规避的几种聚集索引建立情况,但这仅仅是从性能的角度来谈聚集索引的选择。对于聚集索引的选择,还是需要全面的考虑进行决定。

作者:zhongguoren666 发表于2014-7-30 10:07:01 原文链接
阅读:70 评论:0 查看评论

相关 [性能 角度 sql] 推荐:

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

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

Oracle SQL性能优化

- - 数据库 - ITeye博客
(1)      选择最有效率的表名顺序(只在基于规则的优化器中有效):. ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表 driving table)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表. 如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

SQL性能调优技巧

- - 数据库 - ITeye博客
Data Model设计的Tip. 以三个范式为基础,业务的独立性和原子性拆分要合适,杜绝Key的冗余和不充分依赖. 对于有NULL值的时候,说明可以拆分为子类, 如果有互斥值,比如两个字段,如果A有值,那么B就不能有值. 隐藏的约束,某个Column为A值,那么另外一个Column就必须为B值,或者某个Column只能是1~20的值.

SQL之性能优化

- - CSDN博客数据库推荐文章
在实际应用中,数据库中的数据会有很多,若要从这些数据表中检索数据,就需要对系统进行优化,提高数据库系统的响应速度,下面就是日常一些查询优化的方法. 索引可以提高数据库查询的速度,提高数据库的访问性能,但同时也会影响数据更新操作(例如插入、修改、删除)的速度. 如果WHERE子句中经常用到的某一列或者某几列创建索引.

Oracle SQL性能优化

- - 数据库 - ITeye博客
(1)      选择最有效率的表名顺序(只在基于规则的优化器中有效):. ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,FROM子句中写在最后的表(基础表 driving table)将被最先处理,在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表. 如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

优化SQL查询:如何写出高性能SQL语句_xuelinger

- - CSDN博客推荐文章
1、首先要搞明白什么叫执行计划. 执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生欀如一条SQL语句如果用来从一个10万条记录的表中查1条记录,那查询优化器会选择“索引查找”方式,如果该表进行了归档,当前只剩下5000条记录了,那查询优化器就会改变方案,采用 “全表扫描”方式.

SQL性能优化十条经验

- - CSDN博客推荐文章
尽量避免在一个复杂查询里面使用 LIKE '%parm1%'—— 红色标识位置的百分号会导致相关列的索引无法使用,最好不要用.. 其实只需要对该脚本略做改进,查询速度便会提高近百倍. a、修改前台程序——把查询条件的供应商名称一栏由原来的文本输入改为下拉列表,用户模糊输入供应商名称时,直接在前台就帮忙定位到具体的供应商,这样在调用后台程序时,这列就可以直接用等于来关联了.

如何写出高性能SQL语句

- - Oracle - 数据库 - ITeye博客
优化SQL查询:如何写出高性能SQL语句. 1、首先要搞明白什么叫执行计划. 执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案,这个方案是由查询优化器自动分析产生欀如一条SQL语句如果用来从一个10万条记录的表中查1条记录,那查询优化器会选择“索引查找”方式,如果该表进行了归档,当前只剩下5000条记录了,那查询优化器就会改变方案,采用“全表扫描”方式.

查看Oracle性能差的SQL

- - Oracle - 数据库 - ITeye博客
1.查看总消耗时间最多的前10条SQL语句. 2.查看CPU消耗时间最多的前10条SQL语句. 3.查看消耗磁盘读取最多的前10条SQL语句. 已有 0 人发表留言,猛击->> 这里<<-参与讨论. —软件人才免语言低担保 赴美带薪读研.

记一次成功的sql注入入侵检测附带sql性能优化

- Bloger - 博客园-首页原创精华区
很多同学和园友都遇到过sql注入的,其中大部分都是代码的不严谨造成的,都是犯过很多错误才学会认真起来. 但是如果是让你接手一个二等残废的网站,并让你在上面改版,而且不能推翻式改版,只能逐步替换旧的程序,那么你会非常痛苦,例如我遇到的问题:.                                                                                                       .