深入浅出Oracle索引技术栈

作者:IT邦德
中国DBA联盟(ACDU)成员,目前从事DBA及程序编程
(WebjavaPython)工作,主要服务于生产制造
现拥有 Oracle 11g  OCP/OCM、
Mysql、Oceanbase(OBCA)认证
分布式TBaseTDSQL数据库、国产达梦数据库以及红帽子认证
从业8年DBA工作,在数据库领域有丰富的经验

擅长主流数据Oracle、MySQL、PG 运维开发,备份恢复,安装迁移,性能优化、故障应急处理等。

前言

索引原理及应用实践


1.导读

    对于OLTP系统而言,其操作特点是每次操作,几乎都是访问和处理极少的数据,
    比如访问某一张订单,查看某一位客户的资料等。
    如果通过在检索条件的相关列上,在选择性较好的列或列的组合上创建索引,
    就可以利用索引快迅定位相关记录。
    这要比从头至尾扫描整张表的资源开销小得多,效率也自然高得多,执行时长也会短得多。
    此外,由于索引只是由一列或少数几列构成,其相比于表中十几,
    甚至几十上百列而言,其体积要小得多。
    如果所需访问的列(检索条件列和返回的列)均在索引上,则可以避免对相对大的表的访问,而只需要访问体积小得多的索引。这也会带来访问开销的降低,从而提升SQL执行效率的效果。

2.索引分类

2.1 B- tree 索引

在这里插入图片描述

索引页块中存储键值和 rowid,常用于 OLTP 系统,
针对基数比较高(high cardinality)的列 (重复值较少)

2.1.1 查看 rowid
alter user scott identified by tiger account unlock;  --解锁用户
select rowid,
dbms_rowid.rowid_object(rowid) object#,
dbms_rowid.rowid_relative_fno(rowid) datafile#,
dbms_rowid.rowid_block_number(rowid) block#,
dbms_rowid.rowid_row_number(rowid) row#,
empno,ename from emp;

2.1.2 创建测试表 test1 、索
SCOTT@PROD> create table test1 as select * from emp;
SCOTT@PROD> create index test_idx1 on test1 (ename);
SCOTT@PROD> explain plan for select * from test1 where ename ='SCOTT';
SCOTT@PROD> @?/rdbms/admin/utlxplp.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3447293396
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     1 |    87 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1     |     1 |    87 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX1 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

select * from table(dbms_xplan.display());
SCOTT@PROD> drop index test_idx1;  --删除索引

2.2 位图索引

在这里插入图片描述

    一个键值对应很多行(rowid), 格式:键值 start_rowid end_rowid 位图,索引页块中通过位图的 0 和 1 标识键值和表中行的关系,页块中存储起始 rowid 和结束,rowid,占用空间比较少,针对基数比较低的列(low cardinality),DML 操作锁定索引 entry,更新代价比较高,适合只读表或 OLAP/DSS 系统 (never updated)

2.2.1 test1 创建位图索引
SCOTT@PROD> create bitmap index test_idx1 on test1(job);
SCOTT@PROD> explain plan for select * from test1 where job='CLERK';
SCOTT@PROD> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2884149098
------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     4 |   348 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | TEST1     |     4 |   348 |     1   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS|           |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE | TEST_IDX1 |       |       |            |          |
------------------------------------------------------------------------------------------

2.2.2 位图索引锁代价
位图索引一个键值指向多行(成百上千),“牵一发而动全身”,锁代价昂贵,严重影响更新和删除效率
【实验案例1】
session1 更新某 行索引列
SCOTT@PROD> select * from test1 order by job;
SCOTT@PROD> update test1 set job='CLERK' where empno=7788;  --更新 job=ANALYST 的 7788,job 列更新为 CLERK
session2 更新其他行测试
更新 job=ANALYST 的 7902、job=CLERK 的 7900,job 列更新为其他值,锁等待
SCOTT@PROD> update test1 set job='test' where empno=7902;
ERROR at line 1:
ORA-01013: user requested cancel of current operation
更新 job=MANAGER 的 7566,job 列更新为非 ANALYST、CLERK 的其他值,正常
SCOTT@PROD> update test1 set job='SALESMAN' where empno=7566;
1 row updated.

【实验案例2】
test1 插入数据
begin
for i in 1..999 loop
insert into test1 select * from emp;
end loop;
commit;
end;
/

PL/SQL procedure successfully completed.
SCOTT@PROD> select count(*) from test1;
  COUNT(*)
----------
     14000

创建测试表 test2 、b-tree 索引
SCOTT@PROD> create table test2 as select * from test1;
SCOTT@PROD> create index test2_idx1 on test2(job);

查看索引页块数量
SCOTT@PROD> select index_name,index_type,LEAF_BLOCKS from user_indexes;

INDEX_NAME                     INDEX_TYPE                  LEAF_BLOCKS
------------------------------ --------------------------- -----------
TEST2_IDX1                     NORMAL                               37
TEST_IDX1                      BITMAP                                1
ORDERS_PK                      NORMAL                                0
PK_EMP                         NORMAL                                1
PK_DEPT                        NORMAL                                1

删除测试表

2.3 函数索引

基于表达式或函数包括的列创建索引,它将一个函数计算得到的结果存贮在索引中
2.3.1 创建测试表、普通索引
SCOTT@PROD> create table test as select empno,initcap(ename) ename,job from emp;
SCOTT@PROD> select * from test;

2.3.2 创建普通索引 查看执行计划
SCOTT@PROD> create index ind_test_ename on test(ename);
SCOTT@PROD> explain plan for select * from test where ename='Scott';
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 418585065
----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    26 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST           |     1 |    26 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_TEST_ENAME |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
数据库中存储的数据大小写敏感,应用程序已经将用户输入的数据转换为大写
SCOTT@PROD> explain plan for select * from test where upper(ename)='SCOTT';
SCOTT@PROD> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    26 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |    26 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

2.3.3 创建函数索引
SCOTT@PROD> create index ind2_test_ename on test(upper(ename));
SCOTT@PROD> select index_name,index_type from user_indexes where table_name='TEST';
INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
IND2_TEST_ENAME                FUNCTION-BASED NORMAL
IND_TEST_ENAME                 NORMAL
SCOTT@PROD> explain plan for select * from test where upper(ename)='SCOTT';
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2085671027
-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     1 |    33 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST            |     1 |    33 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND2_TEST_ENAME |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

SCOTT@PROD> exec 
dbms_stats.gather_table_stats('SCOTT','TEST',cascade=>true,method_opt=>'for columns (upper(ename)) size auto')
SCOTT@PROD> drop table test purge;

2.4 反向索引

   将正常的键值头尾调换 后再进行存储,RAC 环境中,如果索引列通过序列产生,并发 insert 操作时容易产生索引热块(index hot spots)
buffer busy wait,将字节倒置后组织键值,可以防止叶节点出现热块现象,反向索引不支持索引范围扫描(index range scan)

2.4.1 创建测试表、序列
SCOTT@PROD> create table test (id number,name varchar2(20));
SCOTT@PROD> create sequence seq1 start with 1 increment by 1;

2.4.2 插入数据
begin
for i in 1..10 loop
insert into test values (seq1.nextval,'OCM');
end loop;
commit;
end;
/

2.4.3 创建反向索引
SCOTT@PROD> create index ind_test_id on test(id) reverse;
SCOTT@PROD> select index_name,index_type from user_indexes where table_name='TEST';
INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
IND_TEST_ID                    NORMAL/REV

2.4.4 查询执行计划
SCOTT@PROD> explain plan for select * from test where id=2;
SCOTT@PROD> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1064545891
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    25 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_TEST_ID |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

SCOTT@PROD> explain plan for select * from test where id<2;
SCOTT@PROD> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    25 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |    25 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

2.4.5 重建为普通索引(重新查看)
SCOTT@PROD> alter index ind_test_id rebuild noreverse;
SCOTT@PROD> select index_name,index_type from user_indexes where table_name='TEST';
INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
IND_TEST_ID                    NORMAL
SCOTT@PROD> explain plan for select * from test where id<2;
SCOTT@PROD> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1064545891
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    25 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |     1 |    25 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_TEST_ID |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
SCOTT@PROD> drop index IND_TEST_ID;

3.索引访问执行计划

3.1 index unique scan

适合唯一索引的情形
在这里插入图片描述

3.2 INDEX RANGE SCAN

大于,小于、或者普通索引等
在这里插入图片描述

3.3 INDEX FAST FULL SCAN

在这里插入图片描述

3.4 INDEX FULL SCAN

在这里插入图片描述

4.索引相关常用命令

5.1 并行度(资源换取时间)
alter table OBJECT_TEST parallel(degree 1) --直接指定表的并行度
alter table OBJECT_TEST noparallel;  --取消并行度
SELECT /*+ PARALLEL(8) */ MAX(sal),AVG(comm) FROM emp,dept
select a.TABLE_NAME, a.degree from  dba_tables a where a.TABLE_NAME ='OBJECT_TEST'
并行度的优点就是能够最大限度的利用机器的多个cpu资源,是多个cpu同时工作,
从而达到提高数据库工作效率的目的,建议并行度为 2~4 * CPU 数)

5.2 添加主键
  alter table EDS_MOD_DEFECT
  add constraint EDS_MOD_DEFECT_PK primary key  (DATUM, TABLECOMMENT)
  using index
  tablespace PROPOSAL_DAT_IDX local;  --分区索引(非分区,不用加local)
5.3 删除主键
  alter table EDS_MOD_DEFECT drop constraint  EDS_MOD_DEFECT_PK;

5.4 创建索引
##分区索引(分区增加后,索引表空间按指定的索引表空间分配)
create index EDS_MOD_DEFECT_IDX1 on  EDS_MOD_DEFECT(DATUM,TABLECOMMENT) tablespace  PROPOSAL_DAT_IDX LOCAL;
##全局索引
create index T_RANGE_IDX1 on T_RANGE(TEST_DATE) tablespace USERS  online;

5.5 删除索引
drop index EDS_MOD_DEFECT_IDX1;

5.6 Index Rebuild(一般表空间变)
##分区索引
alter index EDS_MOD_DEFECT_IDX1 rebuild partition  EDS_MOD_DEFECT_2003 tablespace PROPOSAL_DAT_IDX  online parallel 3;
alter index index_name rebuild partition p_name online parallel 3;
##全局索引
alter index idx_tbl_col rebuild online;  

注:在create index ... nologging 和 alter index ... rebuild nologging时,nologging会生效。
在后续DML操作时,会生成redo log,虽然index设置为nologging

5.7.分区增加后,索引表空间默认分区的表空间
create index EDS_GLASS_HIST_IDX_02 on  EDS_GLASS_HIST(DATUM) LOCAL
(
  PARTITION GLASS_HIST_TP_2001 TABLESPACE  EDS_GLS_HIST_IDX_PAR_01,
  PARTITION GLASS_HIST_TP_2002 TABLESPACE  EDS_GLS_HIST_IDX_PAR_02,
  PARTITION GLASS_HIST_TP_2003 TABLESPACE  EDS_GLS_HIST_IDX_PAR_03,
  PARTITION GLASS_HIST_TP_2004 TABLESPACE  EDS_GLS_HIST_IDX_PAR_04,
  PARTITION GLASS_HIST_TP_2005 TABLESPACE  EDS_GLS_HIST_IDX_PAR_05,
  PARTITION GLASS_HIST_TP_2006 TABLESPACE  EDS_GLS_HIST_IDX_PAR_06,
  PARTITION GLASS_HIST_TP_2007 TABLESPACE  EDS_GLS_HIST_IDX_PAR_07,
  PARTITION GLASS_HIST_TP_2008 TABLESPACE  EDS_GLS_HIST_IDX_PAR_08,
  PARTITION GLASS_HIST_TP_2009 TABLESPACE  EDS_GLS_HIST_IDX_PAR_09,
  PARTITION GLASS_HIST_TP_2010 TABLESPACE  EDS_GLS_HIST_IDX_PAR_10,
  PARTITION GLASS_HIST_TP_2011 TABLESPACE  EDS_GLS_HIST_IDX_PAR_11,
  PARTITION GLASS_HIST_TP_2012 TABLESPACE  EDS_GLS_HIST_IDX_PAR_12
)

5.8 不可见索引 (Invisible Index) 11g
      不可见索引是会被优化器忽略的不可见索引,我们可以将无用或低效的索引设置为不可见索引,当观察一段时间后,发现其对系统性能并无任何影响,那么就可以彻底删除索引了。
create index test_idx1 on test(empno) invisible; --执行计划不识别,即使使用提示hint强制其走这个索引。优化器还是不会走索引扫描

SCOTT@PROD> select index_name, status,visibility from user_indexes;
INDEX_NAME                     STATUS   VISIBILIT
------------------------------ -------- ---------
TEST_IDX1                           VALID    INVISIBLE
ORDERS_PK                        VALID    VISIBLE
PK_EMP                              VALID    VISIBLE
PK_DEPT                             VALID    VISIBLE

select /*+ index(text, idx_test_id) */ * from test where object_id=12;
注:设置参数optimizer_use_invisible_indexes为true后,此时优化器就会走索引范围扫描了,默认为flase
ALTER INDEX TEST_IDX1 VISIBLE;   --修改索引可见
oracle如何使得index变成不可用状态,此时Oracle就不维护它了
ALTER INDEX TEST1_IDX1 UNUSABLE;
执行成功后,如果后期需要用到该索引的话,必须重建。重建后自动变成usable状态
ALTER INDEX TEST1_IDX1 REBUILD ONLINE;

5.9 创建唯一索引
create unique index test_idx2 on emp_test(deptno);  --不能有重复值(空值除外),不会创建约束
alter table emp_test add constraint test_idx3 unique(ename);  --创建约束,同时创建唯一索引
alter index index_name noparallel ;  --设备没有并行

5.10 创建索引unusable
create index test_idx5 on test(sal) unusable;
alter index TEST_IDX5 rebuild;  --重建后索引会生效

--针对函数索引,设置enable/disable
alter index ind2_test_ename enable;
alter index ind2_test_ename disable;
select  a.INDEX_NAME,a.index_type,a.status,a.FUNCIDX_STATUS  from user_indexes a;  --函数是否可用于不可用

总结

1.一般不同值分布占全表记录数15%以上就可以建立索引提高效率
select  a.table_name,
a.column_name,
a.num_distinct,
round(a.num_distinct*100/b.num_rows) "distinct  percent%"
from  user_tab_columns a ,user_tables b
where  a.table_name=b.table_name and  b.table_name='TEST';

2.建索引的原则
索引覆盖避免回表,一般建复合索引;
避免排序;
复合索引等值在前;

3.哪些情况走不上索引呢?
统计信息不准
倾斜值
聚簇因子
隐式转换(表关联的时候,类型转换要注意这种情况)
条件列上存在运算符
索引状态
表很小
null值 要么where条件限制is not null 要么字段属性设为not null

大家点赞、收藏、关注、评论啦 、查看??????微信公众号获取联系方式??????

本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
THE END
分享
二维码
< <上一篇
下一篇>>