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

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 条评论
登录 后参与评论

相关文章

来自专栏Java后端技术栈

MySQL 清除表空间碎片

(1)表的存储会出现碎片化,每当删除了一行内容,该段空间就会变为空白、被留空,而在一段时间内的大量删除操作,会使这种留空的空间变得比存储列表内容所使用的空间更大...

984
来自专栏吴伟祥

百万级数据库优化方案 转

1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

1012
来自专栏企鹅号快讯

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

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

2339
来自专栏技术沉淀

Python: 操作MySQL数据库

1714
来自专栏唐郑望的专栏

Django数据处理的一些实践

提到 Django 肯定避不开 MVC 模式,即模型(Model)-视图(View)-控制器(Controller),通过将业务逻辑、数据、界面显示分离的方法组...

2801
来自专栏杨建荣的学习笔记

MySQL和Oracle对比学习之事务(r5笔记第4天)

MySQL中的存储引擎很是丰富,常用的有InnoDB,MyISAM等,也查看了不少的资料,基本也有所了解,从一些参考书中看MySQL中的sql部分也是一扫而过,...

4508
来自专栏程序猿

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

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

3095
来自专栏待你如初见

Day16

1333
来自专栏互扯程序

数据库SQL优化大总结之 百万级数据库优化方案

网上关于SQL优化的教程很多,但是比较杂乱。近日有空整理了一下,写出来跟大家分享一下,其中有错误和不足的地方,还请大家纠正补充。

1134
来自专栏Hongten

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

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

874

扫码关注云+社区