SQL查询优化:详解SQL Server非聚集索引

转载 2012年03月26日 17:55:12
 
SQL SERVER中,非聚集索引其实可以看作是一个含有聚集索引的表.但相比实际的表而言.非聚集索引中所存储的表的列数要窄很多,因为非聚集索引仅仅包含原表中非聚集索引的列和指向实际物理表的指针。

  并且,对于非聚集索引表来说,其中所存放的列是按照聚集索引来进行存放的.所以查找速度要快了很多。但是对于性能的榨取来说,SQLSERVER总是竭尽所能,假如仅仅是通过索引就可以在B树的叶子节点获取所需数据,而不再用通过叶子节点上的指针去查找实际的物理表,那性能的提升将会更胜一筹.
  下面我们来看下实现这一点的几种方式.
  非聚集索引的覆盖
  正如前面简介所说。非聚集索引其实可以看作一个聚集索引表.当这个非聚集索引包含了查询所需要的所有信息时,则查询不再需要去查询基本表,而仅仅是从非聚集索引就能得到数据:

  下面来看非聚集索引如何覆盖的:
  在adventureWorks的SalesOrderHeader表中,现在只有CustomerID列有非聚集索引,而BillToAddressID没有索引,我们的查询计划会是这样:

  查询会根据CustomerID列上的非聚集索引找到相应的指针后,去基本表上查找数据.从执行计划可以想象,这个效率并不快。
  下面我们来看覆盖索引,通过在CustomerID和BillToAddressID上建立非聚集索引,我们覆盖到了上面查询语句的所有数据:

  通过覆盖索引,可以看到执行计划简单到不能再简单,直接从非聚集索引的叶子节点提取到数据,无需再扫描基本表!
  这个性能的提升可以从IO统计看出来,下面我们来看有覆盖索引和没有覆盖索引的IO对比:

  索引的覆盖不仅仅带来的是效率的提升,还有并发的提升,因为减少了对基本表的依赖,所以提升了并发,从而减少了死锁!
  理解INCLUDE的魔力
  上面的索引覆盖所带来的效率提升就像魔术一样,但别着急,正如我通篇强调的一样,everything has price.如果一个索引包含了太多的键的话,也会带来很多副作用。INCLUDE的作用使得非聚集索引中可以包含更多的列,但不作为“键”使用。
  比如:假设我们上面的那个查询需要增加一列,则原来建立的索引无法进行覆盖,从而还需要查找基本表:

  但是如果要包含SubTotal这个总金额,则索引显得太宽,因为我们的业务很少根据订单价格作为查询条件,则使用INCLUDE建立索引:

  理解INCLUDE包含的列和索引建立的列可以这样理解,把上述建立的含有INCLUDE的非聚集索引想像成:

  使用INCLUDE可以减少叶子“键”的大小!
  非聚集索引的交叉
  非聚集索引的交叉看以看作是覆盖索引的扩展!
  由于很多原因,比如:
  在生产环境中,我们往往不能像上面建立覆盖索引那样随意改动现有索引,这可能导致的结果是你会更频繁的被客户打电话“关照”
  现有的非聚集索引已经很“宽”,你如果继续拓宽则增改查带来的性能下降的成本会高过提高查询带来的好处
  这时候,你可以通过额外建立索引。正如我前面提到的,非聚集索引的本质是表,通过额外建立表使得几个非聚集索引之间进行像表一样的Join,从而使非聚集索引之间可以进行Join来在不访问基本表的情况下给查询优化器提供所需要的数据:
  比如还是上面的那个例子.我们需要查取SalesOrderHeader表,通过BillToAddressID,CustomerID作为选择条件,可以通过建立两个索引进行覆盖,下面我们来看执行计划:

  非聚集索引的连接
  非聚集索引的连接实际上是非聚集索引的交叉的一种特例。使得多个非聚集索引交叉后可以覆盖所要查询的数据,从而使得从减少查询基本表变成了完全不用查询基本表:

  比如还是上面那两个索引,这时我只查询非聚集索引包含的数据,则完全不再需要查询基本表:
  看起来这样的查询意义不大?但当你把查询条件变为<号时呢?或者给定范围时。还是有一定实际意义的。
  非聚集索引的过滤
  很多时候,我们并不需要将基本表中索引列的所有数据全部索引,比如说含有NULL的值不希望被索引,或者根据具体的业务场景,有一些数据我们不想索引。这样可以:
  减少索引的大小
  索引减少了,从而使得对索引的查询得到了加速
  小索引对于增删改的维护性能会更高
  比如说,如下语句:

  我们为其建立聚集索引后:

  这时我们为其加上过滤条件,形成过滤索引:

  由上面我们可以看出,使用过滤索引的场景要和具体的业务场景相关,对于为大量相同的查询条件建立过滤索引使得性能进一步提升!
  总结
  本文从介绍了SQL SERVER中非聚集索引的覆盖,连接,交叉和过滤。对于我们每一点从SQLSERVER榨取的性能的提升往往会伴随着另一方面的牺牲。作为数据库的开发人员或者管理人员来说,以全面的知识来做好权衡将会是非常重要.系统的学习数据库的知识不但能大量减少逻辑读的数据,也能减少客户打电话"关照”的次数:-)

SQL SERVER 聚集索引 非聚集索引 区别

一、理解索引的结构   索引在数据库中的作用类似于目录在书籍中的作用,用来提高查找信息的速度。使用索引查找数据,无需对整表进行扫描,可以快速找到所需数据。微软的SQL SERVER提供了两种索引:聚...
  • single_wolf_wolf
  • single_wolf_wolf
  • 2016年10月24日 21:26
  • 3238

SQL SERVER下非聚集索引引发的死锁问题

最近一个消息发送功能在测试的过程中遇到了因为非聚集索引引发的死锁问题,下面是小伙伴事后总结的内容,现分享出来。(因为一些内容涉及到公司信息,所以只分享了其中部分内容,但是应该不影响整体阅读)情景介绍表...
  • Knight_hf
  • Knight_hf
  • 2017年01月06日 10:29
  • 682

如何设计聚集索引和非聚集索引 in SQL Server 2000

1.聚簇索引(Cluster Index) 又称 聚集索引,聚簇索引中索引存储的值的顺序和表中的数据的物理存储顺序是完全一致的. 聚簇索引的特点 表的数据按照索引的...
  • jimung
  • jimung
  • 2008年04月04日 10:48
  • 982

数据库性能优化一:SQL索引一步到位

SQL索引在数据库优化中占有一个非常大的比例, 一个好的索引的设计,可以让你的效率提高几十甚至几百倍,在这里将带你一步步揭开他的神秘面纱。   1.1 什么是索引?   SQL索引有两种,...
  • guochunyang
  • guochunyang
  • 2015年11月10日 12:35
  • 2521

SQL Server的查询优化器详解

SQL Server的查询优化器在select查询执行的时候产生一个高效的查询执行计划。如果优化器不能选择最优的计划,那么就需要检查查询计划、统计信息、支持的索引等,而通过使用提示可以改变优化器选择查...
  • isoleo
  • isoleo
  • 2015年11月18日 17:47
  • 1354

SQLServer2012 (非)聚集索引存储探究

SQLServer2012 (非)聚集索引存储探究 Author:zfive5(zidong) Email:zfive5@163.com 引子 由于写了前一篇文字《SQLServer2012 表IAM...
  • zfive5
  • zfive5
  • 2014年10月17日 21:37
  • 1795

SQL 大数据查询如何进行优化?sqlserver和oracle整理

六十多条大数据优化建议。涉及到sqlserver和oracle的SQL语句。
  • cao919
  • cao919
  • 2017年03月30日 11:58
  • 1400

SQL Server多表查询优化方案总结

SQL Server多表查询的优化方案是本文我们主要要介绍的内容,本文我们给出了优化方案和具体的优化实例,接下来就让我们一起来了解一下这部分内容。 1.执行路径 ORACLE的这个功能大大地提高了...
  • jiangnengzhuo
  • jiangnengzhuo
  • 2014年03月12日 10:43
  • 2636

sqlserver 查询效率优化

很多人不知道SQL语句在SQL SERVER中是如何执行的,他们担心自己所写的SQL语句会被SQL SERVER误解。比如:   select * from table1 where name=...
  • shuaishifu
  • shuaishifu
  • 2015年10月29日 13:37
  • 1585

SQL Server索引进阶第五篇:索引包含列

包含列解析 所谓的包含列就是包含在非聚集索引中,并且不是索引列中的列。或者说的更通俗一点就是:把一些底层数据表的数据列包含在非聚集索引的索引页中,而这些数据列又不是索引列,那么这些列就是包含列。同时...
  • wozengcong
  • wozengcong
  • 2015年09月17日 14:47
  • 1598
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL查询优化:详解SQL Server非聚集索引
举报原因:
原因补充:

(最多只允许输入30个字)