mysql数据库索引实例图示_数据库索引实例之三

参照博客某社区600万用户数据导入MYSQL、MSSQL、Oracle数据库方法中“ 3.3为数据表添加年龄列(2012-6-13)”提到的方法创建测试数据库。

1.无索引状态

1.1统计 select count(*)

然后执行query1，统计年龄在20到30之间的人数。

set autotrace on

select count(*) from csdnuser3 where age between 20 and 30;

查询与统计结果如下

View Code

COUNT(*)----------

1429651执行计划----------------------------------------------------------

Plan hash value: 3799347318

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 13112 (1)| 00:02:38 |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

|* 2 | TABLE ACCESS FULL| CSDNUSER3 | 1717K| 21M| 13112 (1)| 00:02:38 |

--------------------------------------------------------------------------------

Predicate Information (identifiedbyoperation id):---------------------------------------------------

2 - filter("AGE">=20 AND "AGE"<=30)

Note-----

- dynamic sampling used forthis statement

统计信息----------------------------------------------------------

5recursive calls0db block gets47795consistent gets47702physical reads0redo size422 bytes sent via SQL*Net toclient416 bytes received via SQL*Net fromclient2 SQL*Net roundtrips to/fromclient0sorts (memory)0 sorts (disk)1 rows processed

再次执行query1，查询与统计结果如下

View Code

COUNT(*)----------

1429651执行计划----------------------------------------------------------

Plan hash value: 3799347318

--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 13112 (1)| 00:02:38 |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

|* 2 | TABLE ACCESS FULL| CSDNUSER3 | 1717K| 21M| 13112 (1)| 00:02:38 |

--------------------------------------------------------------------------------

Predicate Information (identifiedbyoperation id):---------------------------------------------------

2 - filter("AGE">=20 AND "AGE"<=30)

Note-----

- dynamic sampling used forthis statement

统计信息----------------------------------------------------------

0recursive calls0db block gets47707consistent gets47702physical reads0redo size422 bytes sent via SQL*Net toclient416 bytes received via SQL*Net fromclient2 SQL*Net roundtrips to/fromclient0sorts (memory)0 sorts (disk)1 rows processed

对照上述两次查询，我们可以发现：

两次physical reads=47702 没有改变，

第一次consistent gets=47795，第二次 consistent gets=47707，略微改变。

1.2查询 select *

我们查询所有年龄在20到30之间的人员，执行如下查询语句query2

autotrace traceonlyselect * from csdnuser3 where age between 20 and 30;

查询统计结果如下：

View Code

已选择1429651行。

执行计划----------------------------------------------------------

Plan hash value: 3659233856

-------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1717K| 681M| 13112 (1)| 00:02:38 |

|* 1 | TABLE ACCESS FULL| CSDNUSER3 | 1717K| 681M| 13112 (1)| 00:02:38 |

-------------------------------------------------------------------------------

Predicate Information (identifiedbyoperation id):---------------------------------------------------

1 - filter("AGE">=20 AND "AGE"<=30)

Note-----

- dynamic sampling used forthis statement

统计信息----------------------------------------------------------

198recursive calls0db block gets142441consistent gets0physical reads0redo size83189634 bytes sent via SQL*Net toclient1048826 bytes received via SQL*Net fromclient95312 SQL*Net roundtrips to/fromclient6sorts (memory)0 sorts (disk)1429651 rows processed

在执行一次query2

查询统计结果如下：

View Code

已选择1429651行。

执行计划----------------------------------------------------------

Plan hash value: 3659233856

-------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1717K| 681M| 13112 (1)| 00:02:38 |

|* 1 | TABLE ACCESS FULL| CSDNUSER3 | 1717K| 681M| 13112 (1)| 00:02:38 |

-------------------------------------------------------------------------------

Predicate Information (identifiedbyoperation id):---------------------------------------------------

1 - filter("AGE">=20 AND "AGE"<=30)

Note-----

- dynamic sampling used forthis statement

统计信息----------------------------------------------------------

0recursive calls0db block gets142322consistent gets0physical reads0redo size83189634 bytes sent via SQL*Net toclient1048826 bytes received via SQL*Net fromclient95312 SQL*Net roundtrips to/fromclient0sorts (memory)0 sorts (disk)1429651 rows processed

对比上述两次查询，我们可以发现：

因为查询的数据已经在缓存中，所以physical reads=0

consistent gets相对于第一次的142441，第二次为142322，略微有些想将。

2.为age字段添加索引

在age字段上添加索引

--创建索引

create index IX_CSDNUSER3_AGE on CSDNUSER3(AGE);

2.1统计 select count(*)

执行query1以后，查询分析结果如下：

View Code

COUNT(*)----------

1429651执行计划----------------------------------------------------------

Plan hash value: 1842112716

--------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 2912 (1)| 00:00:35 |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

|* 2 | INDEX RANGE SCAN| IX_CSDNUSER3_AGE | 1717K| 21M| 2912 (1)| 00:00:35 |

--------------------------------------------------------------------------------------

Predicate Information (identifiedbyoperation id):---------------------------------------------------

2 - access("AGE">=20 AND "AGE"<=30)

Note-----

- dynamic sampling used forthis statement

统计信息----------------------------------------------------------

9recursive calls0db block gets2887consistent gets2789physical reads0redo size422 bytes sent via SQL*Net toclient416 bytes received via SQL*Net fromclient2 SQL*Net roundtrips to/fromclient0sorts (memory)0 sorts (disk)1 rows processed

再执行一次query1，查询分析结果如下：

View Code

COUNT(*)----------

1429651执行计划----------------------------------------------------------

Plan hash value: 1842112716

--------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 13 | 2912 (1)| 00:00:35 |

| 1 | SORT AGGREGATE | | 1 | 13 | | |

|* 2 | INDEX RANGE SCAN| IX_CSDNUSER3_AGE | 1717K| 21M| 2912 (1)| 00:00:35 |

--------------------------------------------------------------------------------------

Predicate Information (identifiedbyoperation id):---------------------------------------------------

2 - access("AGE">=20 AND "AGE"<=30)

Note-----

- dynamic sampling used forthis statement

统计信息----------------------------------------------------------

0recursive calls0db block gets2790consistent gets0physical reads0redo size422 bytes sent via SQL*Net toclient416 bytes received via SQL*Net fromclient2 SQL*Net roundtrips to/fromclient0sorts (memory)0 sorts (disk)1 rows processed

对比上述两次查询可以发现：

consistent gets略微想将，最后在2790趋于稳定

physical reads因为第一次的执行而产生缓存，所以第二次physical reads=0

相对于1.1中稳定的consistent gets=47707，在age字段上加了索引以后，现在的consistent gets=2790，查询效率明显挺高。这表明在age字段上加索引对于select count(*)类型的查询，可以提升查询效率。

2.2查询 select *

执行query2以后，查询分析结果如下：

View Code

已选择1429651行。

执行计划----------------------------------------------------------

Plan hash value: 3659233856

-------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1717K| 681M| 13112 (1)| 00:02:38 |

|* 1 | TABLE ACCESS FULL| CSDNUSER3 | 1717K| 681M| 13112 (1)| 00:02:38 |

-------------------------------------------------------------------------------

Predicate Information (identifiedbyoperation id):---------------------------------------------------

1 - filter("AGE">=20 AND "AGE"<=30)

Note-----

- dynamic sampling used forthis statement

统计信息----------------------------------------------------------

7recursive calls0db block gets142419consistent gets0physical reads0redo size83189634 bytes sent via SQL*Net toclient1048826 bytes received via SQL*Net fromclient95312 SQL*Net roundtrips to/fromclient0sorts (memory)0 sorts (disk)1429651 rows processed

再次执行query2，查询分析结果如下：

View Code

已选择1429651行。

执行计划----------------------------------------------------------

Plan hash value: 3659233856

-------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1717K| 681M| 13112 (1)| 00:02:38 |

|* 1 | TABLE ACCESS FULL| CSDNUSER3 | 1717K| 681M| 13112 (1)| 00:02:38 |

-------------------------------------------------------------------------------

Predicate Information (identifiedbyoperation id):---------------------------------------------------

1 - filter("AGE">=20 AND "AGE"<=30)

Note-----

- dynamic sampling used forthis statement

统计信息----------------------------------------------------------

0recursive calls0db block gets142322consistent gets0physical reads0redo size83189634 bytes sent via SQL*Net toclient1048826 bytes received via SQL*Net fromclient95312 SQL*Net roundtrips to/fromclient0sorts (memory)0 sorts (disk)1429651 rows processed

对比上述两次查询，我们可以发现：

rows processed= 1429651 刚好就是查询结果一共的条目数

consistent gets 两次有略微想将，最后在142322趋于稳定。当时相对于1.1中select count(*)类型的查询，此处 consistent gets更大。

相对于1.2中无索引的select *，我们发现consistent gets都是142322，表明在age字段上加索引，对于select *类型的查询，没有提高查询效率。

3.主键索引

为什么要在主键上加索引，这是因为表连接的时候效率提高。主键是cluster索引，而其他为非cluster索引。