开发者社区> 嗯哼9925> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

数据库索引实例之二consistent gets

简介:
+关注继续查看

数据来源

根据博客:某社区600万用户数据导入MYSQL、MSSQL、Oracle数据库方法,我们得到了一个含有600多万条用户数据的oracle数据库。本文就是根据这个来验证数据库索引的特性。

1.测试数据库CSDNUSER

View Code

数据导入方法参考某社区600万用户数据导入MYSQL、MSSQL、Oracle数据库方法

上述数据库包含主键索引,但是没有为主键命名,因此搜索该索引的等级BLEVEL,可以通过以下查询语句求出:

select index_name, blevel, num_rows from user_indexes where table_name = 'CSDNUSER'; 

查询结果如下图所示:

从上述查询结果中我们发现没有BLEVEL和NUM_ROWS。

1.未命名的主键

接下来我们查询ID>700万数据,之所以是700万是因为我们总共数据时600多万,这样可以更加明显的看出来有没有索引的查询效率。查询语句如下:

SET AUTOTRACE ON
SELECT * FROM CSDNUSER2 WHERE ID>7000000;

查询执行计划如下:

View Code

从统计信息中我们看出一共有“92  consistent gets”,相当于有92次IO。

2.无主键

然后我们删除上述主键SYS_COO38672,删除语句如下:

--删除主键
alter table csdnuser2 drop constraint SYS_C0038672;

再次执行上述查询语句,查询执行计划如下:

View Code

从统计信息中我们看出一共有“45129  consistent gets”,相当于有45129次IO。

3.添加命名主键

添加主键语句如下:

--添加主键
alter table csdnuser add constraint pk_csdnuser primary key(ID);

查询该索引的等级BLEVEL,可以通过以下查询语句求出:

select index_name, blevel, num_rows from user_indexes where table_name = 'CSDNUSER'; 

查询结果如下图所示:

从上述查询结果中我们发现BLEVEL:2和NUM_ROWS=6428632,为表中记录数。

再次执行上述查询语句,查询执行计划如下:

View Code

发现是“ 3  consistent gets”,表明添加命名索引以后,只需要3次IO就可以结束查询。

PS:2012-6-13解释前面错误理解

上述的命名主键与非命名主键的说法是错误的,第二次consistent gets子所以很大是因为删除了主键索引,这是没有错的。而第三次的consistent gets为3,而第一次consistent gets为92,并不说明自定义命名的索引效率比系统命名的索引效率高。之所以第三次只需要3次consistent gets是因为执行完第一次以后有缓存存在。假设在第一次查询以后再一次查询,那么统计结果跟第三次一模一样。

2.测试数据库USERINFO

http://www.itpub.net/thread-1313899-1-1.html

2.1创建数据库USERINFO

View Code

2.2创建序列

View Code

2.3插入100000条数据

View Code

2.4统计结果

查询NO=5000,查询语句如下:

View Code

第一次查询得到的统计信息:

View Code

第二次查询得到的统计信息:

View Code

第三次查询得到的统计信息:

View Code

为NO字段添加索引IX_USERINFO_NO

View Code

第四次查询得到的统计信息:

View Code

第五次查询得到的统计信息:

View Code

删除索引IX_USERINFO_NO

View Code

添加主键PK_USERINFO_NO

View Code

第六次查询得到的统计信息:

View Code

第七次查询得到的统计信息:

View Code

2.5统计分析

第一次到第三次查询,都是无索引状态下的查询,我们可以发现:

  1. 第一次查询时recursive calls=5>0,而后面两次recursive calls都为0,这个也适用于后期有索引的情况
  2. consistent gets在不断缩小,知道最后不变。例如第一次查询的consistent gets最大,而第二次和第三次的consistent gets相等。
  3. 在三次查询过程中,physical reads都为0。(physical reads=0表明物理IO次数为0,也就是说没有从硬盘上读取数据到内存中。之所以physical reads=0,是因为前面insert的100000数据已经在buffer_cache里了

第四次到第五次查询,都是有索引状态下的插叙,我们可以发现:

  1. consistent gets次数在下降,consistent gets最后变到4。
  2. recursive calls次数在下降,recursive calls最后变到0。
  3. 相对于第一次到第三次查询,consistent gets明显下降,这是因为添加了索引,前面第一次到第三次是全表扫描,而第四次跟第五次查询是索引扫描。逻辑读(consistent gets)之所以最后会是4,是因此需要读取根节点一个,分支一个,叶子一个,表块一个,共4个。
  4. physical reads同上。

从六次到第七次查询,是将原来索引换成了主键,我们可以发现:

  1. consistent gets最后降为3,而不是4,这个是不明白的地方。
  2. consistent gets同上
  3. recursive calls同上
  4. physical reads同上

主键也是索引的一种,只要加了索引,那么逻辑读(consistent gets)就明显降低,查询效率大大提高。这也是索引的作用。

2.6清空缓存后的physical reads

假设我们运行如下命令清空缓存:

alter system flush buffer_cache;
alter system flush shared_pool;

然后再次执行查询语句,得到的统计信息如下:

View Code

可以看到physical reads=308

再次执行查询语句,,得到的统计信息如下:

View Code

 

 本文转自xwdreamer博客园博客,原文链接:http://www.cnblogs.com/xwdreamer/archive/2012/06/11/2545689.html,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Boltdb学习笔记之二--数据结构
Boltdb学习笔记之二--数据结构
0 0
数据库索引原理
数据库索引原理
0 0
数据库索引,真的越建越好吗?(中)
索引是提高关系型数据库查询性能的利器,但其并非银弹,必须精通其原理,才能发挥奇效。
0 0
数据库索引,真的越建越好吗?(上)
索引是提高关系型数据库查询性能的利器,但其并非银弹,必须精通其原理,才能发挥奇效。
0 0
数据库索引,真的越建越好吗?(下)
索引是提高关系型数据库查询性能的利器,但其并非银弹,必须精通其原理,才能发挥奇效。
0 0
【总结】数据库索引的实现原理
如果您觉得文章对您有帮助,可以【打赏】博主或点击文章右下角【推荐】一下。您的鼓励是博主坚持原创和持续写作的最大动力!
415 0
3.3.1 Cache一致性的基本概念
<div class="bct fc05 fc11 nbw-blog ztag"><div> <p style="TEXT-INDENT: 21pt;"><span lang="EN-US" xmllang="EN-US">PCI</span><span style="FONT-FAMILY: 宋体; mso-ascii-font-family: 'Times new roman'; mso
970 0
+关注
文章
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载