数据库索引实例之二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统计分析
从第一次到第三次查询，都是无索引状态下的查询，我们可以发现：
- 第一次查询时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;
然后再次执行查询语句，得到的统计信息如下：
View Code
可以看到physical reads=308。
再次执行查询语句，，得到的统计信息如下：
View Code
本文转自xwdreamer博客园博客，原文链接：http://www.cnblogs.com/xwdreamer/archive/2012/06/11/2545689.html，如需转载请自行联系原作者