索引黄金三大特征:
1.索引的高度较低
索引高度较低的学习:
随着数据的不断增多,从底层的block块开始,如果存储满了会向上保存目录,但是向上增长缓慢,查询数据的以后从最上级开始向下查询,一层一个io,大量数据主要在吞吐量缓慢。
测试:可以建立表插入数据,然后建立索引,然后用hitns进行全表扫描,查看执行计划看consistent gets数量。
例子:
drop table t1 purge;
drop table t2 purge;
drop table t3 purge;
drop table t4 purge;
drop table t5 purge;
drop table t6 purge;
drop table t7 purge;
create table t1 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=1;
create table t2 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=10;
create table t3 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=100;
create table t4 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=1000;
create table t5 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=10000;
create table t6 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=100000;
create table t7 as select rownum as id ,rownum+1 as id2,rpad('*',1000,'*') as contents from dual connect by level<=1000000;
create index idx_id_t1 on t1(id);
create index idx_id_t2 on t2(id);
create index idx_id_t3 on t3(id);
create index idx_id_t4 on t4(id);
create index idx_id_t5 on t5(id);
create index idx_id_t6 on t6(id);
create index idx_id_t7 on t7(id);
set linesize 1000
set autotrace off
select index_name,
blevel,
leaf_blocks,
num_rows,
distinct_keys,
clustering_factor
from user_ind_statistics
where table_name in( 'T1','T2','T3','T4','T5','T6','T7');
2.存储列值:列值+rowid
3.本身有序
例子:优化count(*),如果列值存在null那么索引将失效。在列值上加上索引,count(*)会走索引。
补充:sum等也会走索引
例子:select id from t order by id; 查看执行计划不会产生sorts(memory),因为索引本身就是排序的
例子:select max(id) from t;速度异常快,因为不论表的数据如何增加,max min是在固定位置的,所以查询很快。
例子:union无法优化,索引无法消除union排序,一般来说使用union存在必要性,在数据不会重复时候用union all
例子:索引之排序
drop table t purge;
create table t as select * from dba_objects ;
set autotrace traceonly
--oracle还算智能,不会傻到这里都去排序,做了查询转换,忽略了这个排序
select count(*) from t order by object_id;
---以下语句说明排序
set autotrace traceonly
set linesize 1000
drop table t purge;
create table t as select * from dba_objects;
--以下语句没有索引又有order by ,必然产生排序
select * from t where object_id>2 order by object_id;
---新增索引后,Oracle就有可能利用索引本身就有序的特点,利用索引来避免排序,如下:
create index idx_t_object_id on t(object_id);
set autotrace traceonly
select * from t where object_id>2 order by object_id;
--如下情况Oracle肯定毫不犹豫的选择用索引,因为回表取消了 !
select object_id from t where object_id>2 order by object_id;
例子:索引值max/min查询
--MAX/MIN 的索引优化
drop table t purge;
create table t as select * from dba_objects;
update t set object_id=rownum;
alter table t add constraint pk_object_id primary key (OBJECT_ID);
set autotrace on
set linesize 1000
select max(object_id) from t;
--最小值老师的试验就无需展现执行计划结果了,必然和最大值的执行计划一样!
select min(object_id) from t;
--如果没用到索引的情况是如下,请看看执行计划有何不同,请看看代价和逻辑读的差异!
select /*+full(t)*/ max(object_id) from t;
---另外,可以做如下试验观察在有索引的情况下,随这记录数增加,性能差异是否明显?
set autotrace off
drop table t_max purge;
create table t_max as select * from dba_objects;
insert into t_max select * from t_max;
insert into t_max select * from t_max;
insert into t_max select * from t_max;
insert into t_max select * from t_max;
insert into t_max select * from t_max;
select count(*) from t_max;
create index idx_t_max_obj on t_max(object_id);
set autotrace on
select max(object_id) from t_max;
补充知识:
INDEX FULL SCAN (MIN/MAX)
TABLE ACCESS BY INDEX ROWID(回表)