数据库索引实例之二consistent gets
数据来源
根据博客：某社区600万用户数据导入MYSQL、MSSQL、Oracle数据库方法，我们得到了一个含有600多万条用户数据的oracle数据库。本文就是根据这个来验证数据库索引的特性。
1.测试数据库CSDNUSER
View Code
CREATE TABLE "SCOTT"."CSDNUSER" ( "ID" int primary key not null, "USERNAME" VARCHAR2(256), "PASSWORD" VARCHAR2(256), "EMAIL" VARCHAR2(256) )
数据导入方法参考某社区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
执行计划
----------------------------------------------------------
Plan hash value: 2317779687
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 403 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| CSDNUSER2 | 1 | 403 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SYS_C0038672 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">7000000)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
9 recursive calls
0 db block gets
92 consistent gets
2 physical reads
116 redo size
450 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
从统计信息中我们看出一共有“92 consistent gets”，相当于有92次IO。
2.无主键
然后我们删除上述主键SYS_COO38672，删除语句如下：
--删除主键 alter table csdnuser2 drop constraint SYS_C0038672;
再次执行上述查询语句，查询执行计划如下：
View Code
执行计划
----------------------------------------------------------
Plan hash value: 1618046436
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 498 | 195K| 12348 (1)| 00:02:29 |
|* 1 | TABLE ACCESS FULL| CSDNUSER2 | 498 | 195K| 12348 (1)| 00:02:29 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID">7000000)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
182 recursive calls
0 db block gets
45129 consistent gets
45015 physical reads
0 redo size
450 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
0 rows processed
从统计信息中我们看出一共有“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
执行计划
----------------------------------------------------------
Plan hash value: 2702699671
--------------------------------------------------------------------------------
-----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 403 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| CSDNUSER | 1 | 403 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PK_CSDNUSER | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID">7000000)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
450 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
发现是“ 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
CREATE TABLE "USERINFO" ( "NO" INT , "NAME" VARCHAR2(50), "BIRTHDAY" DATE )
2.2创建序列
View Code
CREATE SEQUENCE SEQ_USERINFO_NO INCREMENT BY 1 -- 每次加几个 START WITH 1 -- 从1开始计数
2.3插入100000条数据
View Code
begin for i in 1..100000 loop INSERT INTO USERINFO VALUES(SEQ_USERINFO_NO.nextval,'XUWEI',SYSDATE); end loop; end; /
2.4统计结果
查询NO=5000，查询语句如下：
View Code
set autotrace traceonly select * from userinfo where no = 5000;
第一次查询得到的统计信息：
View Code
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 3576123897
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 49 | 102 (0)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| USERINFO | 1 | 49 | 102 (0)| 00:00:02 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NO"=5000)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
5 recursive calls
0 db block gets
119023 consistent gets
0 physical reads
25048 redo size
391 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
第二次查询得到的统计信息：
View Code
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 3576123897
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 49 | 102 (0)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| USERINFO | 1 | 49 | 102 (0)| 00:00:02 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NO"=5000)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
100704 consistent gets
0 physical reads
21164 redo size
391 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
第三次查询得到的统计信息：
View Code
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 3576123897
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 49 | 102 (0)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| USERINFO | 1 | 49 | 102 (0)| 00:00:02 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NO"=5000)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
100704 consistent gets
0 physical reads
21208 redo size
391 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
为NO字段添加索引IX_USERINFO_NO
View Code
--创建索引 create index IX_USERINFO_NO on USERINFO(NO);
第四次查询得到的统计信息：
View Code
执行计划
----------------------------------------------------------
Plan hash value: 1066629497
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 49 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| USERINFO | 1 | 49 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_USERINFO_NO | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NO"=5000)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
9 recursive calls
0 db block gets
76 consistent gets
1 physical reads
0 redo size
543 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
第五次查询得到的统计信息：
View Code
执行计划
----------------------------------------------------------
Plan hash value: 1066629497
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 49 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| USERINFO | 1 | 49 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_USERINFO_NO | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NO"=5000)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
543 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
删除索引IX_USERINFO_NO
View Code
--删除索引 drop index IX_USERINFO_NO;
添加主键PK_USERINFO_NO
View Code
--添加主键 alter table USERINFO add constraint PK_USERINFO_NO primary key(NO);
第六次查询得到的统计信息：
View Code
执行计划 ---------------------------------------------------------- Plan hash value: 4161181038 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 49 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| USERINFO | 1 | 49 | 2 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_USERINFO_NO | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("NO"=5000) 统计信息 ---------------------------------------------------------- 192 recursive calls 0 db block gets 33 consistent gets 1 physical reads 0 redo size 447 bytes sent via SQL*Net to client 405 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 1 rows processed
第七次查询得到的统计信息：
View Code
执行计划 ---------------------------------------------------------- Plan hash value: 4161181038 -------------------------------------------------------------------------------- -------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 49 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| USERINFO | 1 | 49 | 2 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_USERINFO_NO | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("NO"=5000) 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 447 bytes sent via SQL*Net to client 405 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
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
执行计划 ---------------------------------------------------------- Plan hash value: 1066629497 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 49 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| USERINFO | 1 | 49 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IX_USERINFO_NO | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("NO"=5000) Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 401 recursive calls 0 db block gets 128 consistent gets 308 physical reads 0 redo size 543 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 1 rows processed
可以看到physical reads=308。
再次执行查询语句，，得到的统计信息如下：
View Code
执行计划 ---------------------------------------------------------- Plan hash value: 1066629497 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 49 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| USERINFO | 1 | 49 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IX_USERINFO_NO | 1 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("NO"=5000) Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 543 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
