组合索引的优化技巧与总结

scott

725

3页

5次

2019-12-08

5墨值下载

组合索引经要素!

/* 1.适用在单独查询返回记录很多，组合查询后忽然返回记录很少的情况:

比如 where 学历=硕士以上返回不少的记录

比如 where 职业=收银员同样返回不少的记录

于是无论哪个条件查询做索引，都不合适。

可是，如果学历为硕士以上，同时职业又是收银员的，返回的就少之又少了。

于是联合索引就可以这么开始建了。

/* 2.组合查询的组合顺序，要考虑单独的前缀查询情况（否则单独前缀查询的索引不能生效或者只能用到跳

跃索引）

比如你在建 id,object_type 的联合索引时，要看考虑是单独 where id=xxx 查询的多，还是单独

where object_type 查询的多。

这里细节就暂时略去了，在案例的部分中还有描述

--3.仅等值无范围查询时，组合索引顺序不影响性能（比如 where col1=xxx and col2=xxx，无论

COL1+COL2 组合还是 COL2+COL1 组合）

drop table t purge;

create table t as select * from dba_objects;

insert into t select * from t;

update t set object_id=rownum ;

commit;

create index idx_id_type on t(object_id,object_type);

create index idx_type_id on t(object_type,object_id);

set autotrace off

alter session set statistics_level=all ;

set linesize 366

select /*+index(t,idx_id_type)*/ * from t where object_id=20 and

object_type='TABLE';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

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

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

A-Time | Buffers |

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

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

| 0 | SELECT STATEMENT | | 1 | | 1 |

00:00:00.01 | 5 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 57 | 1 |

00:00:00.01 | 5 |

|* 2 | INDEX RANGE SCAN | IDX_ID_TYPE | 1 | 9 | 1 |

00:00:00.01 | 4 |

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

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

select /*+index(t,idx_type_id)*/ * from t where object_id=20 and

object_type='TABLE';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

Plan hash value: 3420768628

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

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

A-Time | Buffers |

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

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

| 0 | SELECT STATEMENT | | 1 | | 1 |

00:00:00.01 | 5 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 57 | 1 |

00:00:00.01 | 5 |

|* 2 | INDEX RANGE SCAN | IDX_TYPE_ID | 1 | 9 | 1 |

00:00:00.01 | 4 |

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

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

--4.组合索引最佳顺序一般是将列等值查询的列置前。（测试组合索引在条件是不等的情况下的情况，条件

经常是不等的，要放在后面，让等值的在前面）

select /*+index(t,idx_id_type)*/ * from t where object_id>=20 and

object_id<2000 and object_type='TABLE';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

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

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

A-Time | Buffers |

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

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

| 0 | SELECT STATEMENT | | 1 | | 469 |

00:00:00.01 | 86 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 14 | 469 |

00:00:00.01 | 86 |

|* 2 | INDEX RANGE SCAN | IDX_ID_TYPE | 1 | 1 | 469 |

00:00:00.01 | 40 |

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

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

select /*+index(t,idx_type_id)*/ * from t where object_id>=20 and

object_id<2000 and object_type='TABLE';

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

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

A-Time | Buffers |

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

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

| 0 | SELECT STATEMENT | | 1 | | 469 |

00:00:00.01 | 81 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 469 | 469 |

00:00:00.01 | 81 |

|* 2 | INDEX RANGE SCAN | IDX_TYPE_ID | 1 | 469 | 469 |

00:00:00.01 | 35 |

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

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

--5.注意组合索引与组合条件中关于 IN 的优化(将会在案例部分描述，展现结果就不在这里贴出了）

--案例 1

组合索引经要素!

/* 1.适用在单独查询返回记录很多，组合查询后忽然返回记录很少的情况:

比如 where 学历=硕士以上返回不少的记录

比如 where 职业=收银员同样返回不少的记录

于是无论哪个条件查询做索引，都不合适。

可是，如果学历为硕士以上，同时职业又是收银员的，返回的就少之又少了。

于是联合索引就可以这么开始建了。

/* 2.组合查询的组合顺序，要考虑单独的前缀查询情况（否则单独前缀查询的索引不能生效或者只能用到跳

跃索引）

比如你在建 id,object_type 的联合索引时，要看考虑是单独 where id=xxx 查询的多，还是单独

where object_type 查询的多。

这里细节就暂时略去了，在案例的部分中还有描述

--3.仅等值无范围查询时，组合索引顺序不影响性能（比如 where col1=xxx and col2=xxx，无论

COL1+COL2 组合还是 COL2+COL1 组合）

drop table t purge;

create table t as select * from dba_objects;

insert into t select * from t;

update t set object_id=rownum ;

commit;

create index idx_id_type on t(object_id,object_type);

create index idx_type_id on t(object_type,object_id);

set autotrace off

alter session set statistics_level=all ;

set linesize 366

select /*+index(t,idx_id_type)*/ * from t where object_id=20 and

object_type='TABLE';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

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

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

A-Time | Buffers |

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

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

| 0 | SELECT STATEMENT | | 1 | | 1 |

00:00:00.01 | 5 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 57 | 1 |

00:00:00.01 | 5 |

|* 2 | INDEX RANGE SCAN | IDX_ID_TYPE | 1 | 9 | 1 |

00:00:00.01 | 4 |

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

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

select /*+index(t,idx_type_id)*/ * from t where object_id=20 and

object_type='TABLE';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

Plan hash value: 3420768628

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

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

A-Time | Buffers |

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

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

| 0 | SELECT STATEMENT | | 1 | | 1 |

00:00:00.01 | 5 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 57 | 1 |

00:00:00.01 | 5 |

|* 2 | INDEX RANGE SCAN | IDX_TYPE_ID | 1 | 9 | 1 |

00:00:00.01 | 4 |

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

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

--4.组合索引最佳顺序一般是将列等值查询的列置前。（测试组合索引在条件是不等的情况下的情况，条件

经常是不等的，要放在后面，让等值的在前面）

select /*+index(t,idx_id_type)*/ * from t where object_id>=20 and

object_id<2000 and object_type='TABLE';

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

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

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

A-Time | Buffers |

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

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

| 0 | SELECT STATEMENT | | 1 | | 469 |

00:00:00.01 | 86 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 14 | 469 |

00:00:00.01 | 86 |

|* 2 | INDEX RANGE SCAN | IDX_ID_TYPE | 1 | 1 | 469 |

00:00:00.01 | 40 |

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

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

select /*+index(t,idx_type_id)*/ * from t where object_id>=20 and

object_id<2000 and object_type='TABLE';

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

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

A-Time | Buffers |

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

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

| 0 | SELECT STATEMENT | | 1 | | 469 |

00:00:00.01 | 81 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 469 | 469 |

00:00:00.01 | 81 |

|* 2 | INDEX RANGE SCAN | IDX_TYPE_ID | 1 | 469 | 469 |

00:00:00.01 | 35 |

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

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

--5.注意组合索引与组合条件中关于 IN 的优化(将会在案例部分描述，展现结果就不在这里贴出了）

--案例 1

of 3

5墨值下载

oracle 安装部署

关注

评论