数据来源
根据博客:某社区600万用户数据导入MYSQL、MSSQL、Oracle数据库方法,我们得到了一个含有600多万条用户数据的oracle数据库。本文就是根据这个来验证数据库索引的特性。
1.测试数据库CSDNUSER
数据导入方法参考某社区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;
查询执行计划如下:
从统计信息中我们看出一共有“92 consistent gets”,相当于有92次IO。
2.无主键
然后我们删除上述主键SYS_COO38672,删除语句如下:
--删除主键 alter table csdnuser2 drop constraint SYS_C0038672;
再次执行上述查询语句,查询执行计划如下:
从统计信息中我们看出一共有“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,为表中记录数。
再次执行上述查询语句,查询执行计划如下:
发现是“ 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
2.2创建序列
2.3插入100000条数据
2.4统计结果
查询NO=5000,查询语句如下:
第一次查询得到的统计信息:
第二次查询得到的统计信息:
第三次查询得到的统计信息:
为NO字段添加索引IX_USERINFO_NO
第四次查询得到的统计信息:
第五次查询得到的统计信息:
删除索引IX_USERINFO_NO
添加主键PK_USERINFO_NO
第六次查询得到的统计信息:
第七次查询得到的统计信息:
2.5统计分析
从第一次到第三次查询,都是无索引状态下的查询,我们可以发现:
- 第一次查询时recursive calls=5>0,而后面两次recursive calls都为0,这个也适用于后期有索引的情况
- consistent gets在不断缩小,知道最后不变。例如第一次查询的consistent gets最大,而第二次和第三次的consistent gets相等。
- 在三次查询过程中,physical reads都为0。(physical reads=0表明物理IO次数为0,也就是说没有从硬盘上读取数据到内存中。之所以physical reads=0,是因为前面insert的100000数据已经在buffer_cache里了)
从第四次到第五次查询,都是有索引状态下的插叙,我们可以发现:
- consistent gets次数在下降,consistent gets最后变到4。
- recursive calls次数在下降,recursive calls最后变到0。
- 相对于第一次到第三次查询,consistent gets明显下降,这是因为添加了索引,前面第一次到第三次是全表扫描,而第四次跟第五次查询是索引扫描。逻辑读(consistent gets)之所以最后会是4,是因此需要读取根节点一个,分支一个,叶子一个,表块一个,共4个。
- physical reads同上。
从六次到第七次查询,是将原来索引换成了主键,我们可以发现:
- consistent gets最后降为3,而不是4,这个是不明白的地方。
- consistent gets同上
- recursive calls同上
- physical reads同上
主键也是索引的一种,只要加了索引,那么逻辑读(consistent gets)就明显降低,查询效率大大提高。这也是索引的作用。
2.6清空缓存后的physical reads
假设我们运行如下命令清空缓存:
alter system flush buffer_cache; alter system flush shared_pool;
然后再次执行查询语句,得到的统计信息如下:
可以看到physical reads=308。
再次执行查询语句,,得到的统计信息如下:
本文转自xwdreamer博客园博客,原文链接:http://www.cnblogs.com/xwdreamer/archive/2012/06/11/2545689.html,如需转载请自行联系原作者