文档章节

T-SQL查询高级--理解SQL SERVER中非聚集索引的覆盖,连接,交叉和过滤

BearCatYN
 BearCatYN
发布于 2015/07/09 12:04
字数 1720
阅读 62
收藏 1

 写在前面:这是第一篇T-SQL查询高级系列文章.但是T-SQL查询进阶系列还远远没有写完。这个主题放到高级我想是因为这个主题需要一些进阶的知识作为基础..如果文章中有错误的地方请不吝指正.本篇文章的内容需要索引的知识作为基础。

 

简介


    在SQL SERVER中,非聚集索引其实可以看作是一个含有聚集索引的表.但相比实际的表而言.非聚集索引中所存储的表的列数要窄很多,因为非聚集索引仅仅包含原表中非聚集索引的列和指向实际物理表的指针。

    1

    并且,对于非聚集索引表来说,其中所存放的列是按照聚集索引来进行存放的.所以查找速度要快了很多。但是对于性能的榨取来说,SQL SERVER总是竭尽所能,假如仅仅是通过索引就可以在B树的叶子节点获取所需数据,而不再用通过叶子节点上的指针去查找实际的物理表,那性能的提升将会更胜一筹.

    下面我们来看下实现这一点的几种方式.

 

非聚集索引的覆盖


    正如前面简介所说。非聚集索引其实可以看作一个聚集索引表.当这个非聚集索引中包含了查询所需要的所有信息时,则查询不再需要去查询基本表,而仅仅是从非聚集索引就能得到数据:

   2

   下面来看非聚集索引如何覆盖的:

   在adventureWorks的SalesOrderHeader表中,现在只有CustomerID列有非聚集索引,而BillToAddressID没有索引,我们的查询计划会是这样:

   3

   查询会根据CustomerID列上的非聚集索引找到相应的指针后,去基本表上查找数据.从执行计划可以想象,这个效率并不快。

   下面我们来看覆盖索引,通过在CustomerID和BillToAddressID上建立非聚集索引,我们覆盖到了上面查询语句的所有数据:

    4

    通过覆盖索引,可以看到执行计划简单到不能再简单,直接从非聚集索引的叶子节点提取到数据,无需再查找基本表!

    这个性能的提升可以从IO统计看出来,下面我们来看有覆盖索引和没有覆盖索引的IO对比:

    5

    索引的覆盖不仅仅带来的是效率的提升,还有并发的提升,因为减少了对基本表的依赖,所以提升了并发,从而减少了死锁!

   

 

理解INCLUDE的魔力

    上面的索引覆盖所带来的效率提升就像魔术一样,但别着急,正如我通篇强调的一样,everything has price.如果一个索引包含了太多的键的话,也会带来很多副作用。INCLUDE的作用使得非聚集索引中可以包含更多的列,但不作为“键”使用。

    比如:假设我们上面的那个查询需要增加一列,则原来建立的索引无法进行覆盖,从而还需要查找基本表:

    6

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

    8

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

     7

     使用INCLUDE可以减少叶子“键”的大小!

 

非聚集索引的交叉


    非聚集索引的交叉看以看作是覆盖索引的扩展!

     由于很多原因,比如:

  •     在生产环境中,我们往往不能像上面建立覆盖索引那样随意改动现有索引,这可能导致的结果是你会更频繁的被客户打电话“关照”

  •     现有的非聚集索引已经很“宽”,你如果继续拓宽则增改查带来的性能下降的成本会高过提高查询带来的好处

     这时候,你可以通过额外建立索引。正如我前面提到的,非聚集索引的本质是表,通过额外建立表使得几个非聚集索引之间进行像表一样的Join,从而使非聚集索引之间可以进行Join来在不访问基本表的情况下给查询优化器提供所需要的数据:

    比如还是上面的那个例子.我们需要查取SalesOrderHeader表,通过BillToAddressID,CustomerID作为选择条件,可以通过建立两个索引进行覆盖,下面我们来看执行计划:

   9

   

   

非聚集索引的连接


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

     比如还是上面那两个索引,这时我只查询非聚集索引中包含的数据,则完全不再需要查询基本表:

     10

 

非聚集索引的过滤


    很多时候,我们并不需要将基本表中索引列的所有数据全部索引,比如说含有NULL的值不希望被索引,或者根据具体的业务场景,有一些数据我们不想索引。这样可以:

  •     减少索引的大小

  •     索引减少了,从而使得对索引的查询得到了加速

  •     小索引对于增删改的维护性能会更高

    比如说,如下语句:

    11

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

    12

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

    13

    由上面我们可以看出,使用过滤索引的场景要和具体的业务场景相关,对于为大量相同的查询条件建立过滤索引使得性能进一步提升

 

总结


    本文从介绍了SQL SERVER中非聚集索引的覆盖,连接,交叉和过滤。对于我们每一点从SQL SERVER榨取的性能的提升往往会伴随着另一方面的牺牲。作为数据库的开发人员或者管理人员来说,以全面的知识来做好权衡将会是非常重要.系统的学习数据库的知识不但能大量减少逻辑读的数据,也能减少客户打电话"关照”的次数:-)


本文转载自:http://www.cnblogs.com/CareySon/archive/2011/12/27/2303508.html

BearCatYN
粉丝 26
博文 158
码字总数 11947
作品 0
朝阳
程序员
私信 提问
非聚集索引设计

在SQL SERVER中,非聚集索引其实可以看作是一个含有聚集索引的表.但相比实际的表而言.非聚集索引中所存储的表的列数要窄很多,因为非聚集索引仅仅包含原表中非聚集索引的列和指向实际物理表的...

坦途abc
2018/02/27
12
0
SQL-SQL优化-索引

图文并茂详解 SQL JOIN Join 是关系型数据库系统的重要操作之一,一般关系型数据库中包含的常用 Join:内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另一...

掘金官方
2017/12/25
0
0
索引的一些总结

1.1.1 摘要 如果说要对数据库进行优化,我们主要可以通过以下五种方法,对数据库系统进行优化。 1. 计算机硬件调优 2. 应用程序调优 3. 数据库索引优化 4. SQL语句优化 5. 事务处理调优 在本...

长平狐
2012/06/11
154
0
SQL Server中的执行引擎入门

简介 当查询优化器(Query Optimizer)将T-SQL语句解析后并从执行计划中选择最低消耗的执行计划后,具体的执行就会交由执行引擎(Execution Engine)来进行执行。本文旨在分类讲述执行计划中每一...

范大脚脚
2017/12/14
0
0
T-SQL查询进阶--理解SQL Server中索引的概念,原理以及其他

简介 在SQL Server中,索引是一种增强式的存在,这意味着,即使没有索引,SQL Server仍然可以实现应有的功能。但索引可以在大多数情况下大大提升查询性能,在OLAP中尤其明显.要完全理解索引的...

Zhao-Qian
2012/08/20
153
0

没有更多内容

加载失败,请刷新页面

加载更多

nginx访问日志/日志切割/静态文件过期时间/防盗链

Nginx访问日志主要记录部署在nginx上的网站访问数据,日志格式定义在nginx主配置文件中。 nginx主配置文件:/etc/nginx/nginx.conf 查看nginx主配置文件: ```markup [root@linux ~]# cat /e...

asnfuy
16分钟前
5
0
JS_高程4.变量,作用域和内存问题(2)执行环境及作用域

本文转载于:专业的前端网站➨JS_高程4.变量,作用域和内存问题(2)执行环境及作用域 1.执行环境:执行环境定义了变量或函数有权访问的其他数据,决定了它们各自的行为,       每个执...

前端老手
20分钟前
3
0
手机迅雷下载的文件,电脑上找不到问题

不知道手机迅雷怎么做到的,手机迅雷下载的文件,手机上可以看的到 但连电脑后, 电脑上看不到文件 . 尝试了打开查看隐藏文件, 但没有效果 . 手机上查询文件权限 也是对的 . 可读,可写, 不隐藏 ...

ol_O_O_lo
21分钟前
3
0
python学习11:Python tuple元组详解

元组是 Python 中另一个重要的序列结构,和列表类似,也是由一系列按特定顺序排序的元素组成。和列表不同的是,列表可以任意操作元素,是可变序列;而元组是不可变序列,即元组中的元素不可以...

太空堡垒185
34分钟前
4
0
Java实现数据结构之线性结构

一、顺序表 顺序表本质是使用数组储存数组的一种数据结构,在计算机的储存中是连续的分配内存的。 下面是我自己使用java实现的简单顺序表结构 package list; public class MyArrayList<E> { ...

daxiongdi
36分钟前
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部