聚簇索引和非聚簇索引区别的应用

http://www.cnblogs.com/wuxiaoqian726/articles/2016095.html

     聚簇索引和非聚簇索引的一个标志性区别就是聚簇索引的叶节点对应着数据页,从中间级的索引页的索引行直接对应着数据页。而非聚簇索引的索引B+树叶节点不是直接指向数据页面的。如果表有聚集索引或索引视图上有聚集索引,则行定位器是行的聚集索引键。如果聚集索引不是唯一的索引,SQL Server 将添加在内部生成的值(称为唯一值)以使所有重复键唯一。此四字节的值对于用户不可见。仅当需要使聚集键唯一以用于非聚集索引中时,才添加该值。SQL Server 通过使用存储在非聚集索引的叶行内的聚集索引键搜索聚集索引来检索数据行。

View
 Code

      接下来对上面建立的表进行查询,select * from tb_index_test where it_smallint_test=20,通过I/O分析得知,这一条查询语句是通过Index Scan进行扫描。逻辑I/O读取次数为433次。对it_smallint_test列建立非聚簇索引后,在进行查询。通过I/O分析得知,在建立非聚簇索引之后,这一条查询语句还是通过Index Scan进行查询,逻辑I/O读取次数为还是为433次。将之前建立的非聚簇索引删除掉,对it_smallint_test建立聚簇索引。再进行查询,可以发现这时是采用Index Seek进行查询。

聚簇索引原因分析:使用SQL Server的DBCC指令进行分析。在建立聚簇索引的情况下,运行下面的指令获取数据表的页分配信息。

View
 Code

 在运行之后只要关注PageType列,PageType列为2的是表示该数据页是索引页面。PageType为2的数据行有以下这三个。 PagePID  IndexID  PageType IndexLevel NextPagePID  PrevPagePID  214         1              2          2                 0                  0

1600        1              2          1               1664               0

1664        1              2          1                 0                 1664         

 对数据也214运行SQL Server的查看页信息的指令。

View
 Code

 可以得到下面的信息:

 因此可以得知:214这个数据页是聚簇索引B+树的根,其下面有两个子节点:1600,1664。接下来再对1600这个数据也运行数据页查看指令。得到下面的信息:

 注意红色箭头的这两个列,一个是我们建立的索引列的值,一个是这个值对应的数据页面。也就是it_smalint_test的值为1对应的数据也是1377,1378,1379,1380,这四个数据页。在执行查询it_smalint_test的值为1是,通过通过索引的查询直接去读取这四个数据页面。因此逻辑I/O次数比较少,查询效率比较高。

    非聚簇索引的原因分析:

    将聚簇索引删除, 对it_smalint_test建立非聚簇索引。还是运行查看指令,这是会很意外的发现PageType为2的数据页好多,而且IndexLevel为1的值有一个,其他都是IndexLevel为0的数据页。对IndexLevel等于1的数据页也运行数据页查看指令。可以得到下面的信息:

    这是的ChildPageId对应的是PageType=2,并且IndexLevel=0的数据页,可以得知这时的非聚簇索引的B+树深度为1,只有一个根节点,有200个子节点。it_small_test的值是从1到100,每个值占用两个数据节点,即两个数据页(其实这样的看法是错误的1035这个索引数据页也有存放it_small_test=3的rid,这个B+树的查找算法有关)。 接下来运行数据页查看指令,查看PageId=1037这个数据页,可以得到以下信息。

    HEAP RID就是对于的数据列的rowid,这个是有文件id,数据页id组成,这是数据行的唯一标识。这就是非聚簇索引的特点,B+树叶子节点存放这rowid。因此在查找it_small_test=3时,是通过查找索引树找到rid,然后再去数据表的堆上去查找的数据行,注意这时是一行一行去查找的,而不是一个数据页一个数据页去查找的,和聚簇索引查找的情况是不一样的。这样的查询效率显然是低的,因为需要先去查找索引树,接下来再去遍历分配数据表的堆上面查找数据,这样还不如直接使用index scan扫面快,于是查询分析器自动选择index scan,其实绕了一大圈就是得出一个结论:在某一个数据列的取值分布在一个很小的区间(相对于数据总量),并且数据重复出现的频率高的情况下。像上面的数据表,使用非聚簇索引效率不高。使用聚簇索引熊侣比较高。

    另外的一个结论:在查询条件的between的时候,或者是大于某个值,小于某个值的时候,使用聚簇索引的效率比使用非聚簇索引效率高。

    这些结论还可能会有bug,在数据量是100w的情况下呢? 这里的答案是:非聚簇索引同样不适用,归结为一个原因:在返回大数据结果集的情况下是不适合使用非聚簇索引的。

参考文章:http://www.sql-server-performance.com/articles/per/index_data_structures_p1.aspx

http://technet.microsoft.com/zh-cn/library/ms177484.aspx    非聚簇索引结构

http://technet.microsoft.com/zh-cn/library/ms177443.aspx  聚簇索引结构

    索引设计指南:http://technet.microsoft.com/zh-cn/library/ms179560.aspx 索引设计基础知识

http://technet.microsoft.com/zh-cn/library/ms179325.aspx 非聚簇索引设计指南

http://technet.microsoft.com/zh-cn/library/ms190639.aspx 聚簇索引设计指南

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏企鹅号快讯

从商用到开源:15个维度,全面剖析DB2与MySQL数据库的差异

编辑手记 MySQL是目前最流行的开源数据库,由于其部署方便,运维简单,被广泛用于互联网的各个领域。随着整体IT架构的变更,传统的金融,电信业务,也逐渐走上从商...

2399
来自专栏维C果糖

史上最简单的 MySQL 教程(二十三)「数据的高级操作 之 查询(上)」

字段别名,即当数据进行查询的时候,有时候字段的名字并不一定满足需求(特别地,在多表查询的时候,很可能会有同名字段),这时就需要对字段进行重命名、取别名。

3606
来自专栏Hongten

python开发_sqlite3_绝对完整_博主推荐

=========================================

1504
来自专栏jouypub

MySQL的语句执行顺序

MySQL的语句一共分为11步,如下图所标注的那样,最先执行的总是FROM操作,最后执行的是LIMIT操作。其中每一个操作都会产生一张虚拟的表,这个虚拟的表作为...

371
来自专栏玩转JavaEE

初识MongoDB中的索引

索引就像图书的目录一样,可以让我们快速定位到需要的内容,关系型数据库中有索引,NoSQL中当然也有,本文我们就先来简单介绍下MongoDB中的索引。 本文是Mo...

3325
来自专栏小怪聊职场

MySQL(七)|MySQL分库分表的那点事(小怪的Java群第一次话题讨论)

2894
来自专栏程序猿

从0学习MySQL系列(三)概念篇

概要 ---- 在篇文章中提过:概念:数据库管理系统(Database Management System)一些语法的汇总点。 ...

3175
来自专栏逸鹏说道

通过MySQL自动同步刷新Redis

在服务端开发过程中,一般会使用MySQL等关系型数据库作为最终的存储引擎,Redis其实也可以作为一种键值对型的数据库,但在一些实际场景中,特别是关系型结构并不...

1.7K10
来自专栏待你如初见

Day16

1403
来自专栏农夫安全

注入学习之sqli-labs-3(第二关)

前言 本来是想一个个关卡讲下去,后来自己测试了一下,发现第二、三、四这三关跟第一关,起始原理是一样的,只不过是单引号,双引号,带不带括号的区别,只要我们带入的语...

3416

扫码关注云+社区

领取腾讯云代金券