一起养成写作习惯！这是我参与「掘金日新计划 · 4 月更文挑战」的第6天，点击查看活动详情。
📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程😜😜😜
擅长Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️
摘要：本人透彻的讲解了Oracle数据库的索引原理及应用场景
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.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.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.5 全局哈希分区索引
分区索引：
local :针对分区表，索引分区与表分区对应。
global:可以是分区表或非分区表，整张表只有一个索引，索引分区与表分区没有直接对应关系。
全局哈希分区索引 （hash-partitioned global index）
通过 hash 运算将键值分散到每一个分区;
解决索引热块冲突，支持 index range sca
2.5.1 创建全局哈希分区索引
SCOTT@PROD> create index ind_test_id on test(id) global partition by hash(id) partitions 4;
SCOTT@PROD> select segment_name,segment_type,partition_name from user_segments where segment_name='IND_TEST_ID';
SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME
-------------------- ------------------ ------------------------------
IND_TEST_ID INDEX PARTITION SYS_P101
IND_TEST_ID INDEX PARTITION SYS_P102
IND_TEST_ID INDEX PARTITION SYS_P103
IND_TEST_ID INDEX PARTITION SYS_P104
2.5.2 批量插入数据
begin
for i in 1..1000 loop
insert into test values (seq1.nextval,'OCM');
end loop;
commit;
end;
/
2.5.3 查看执行计划
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: 3148664816
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION HASH ALL | | 1 | 25 | 2 (0)| 00:00:01 | 1 | 4 |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 25 | 2 (0)| 00:00:01 | | |
|* 3 | INDEX RANGE SCAN | IND_TEST_ID | 1 | | 1 (0)| 00:00:01 | 1 | 4 |
------------------------------------------------------------------------------------------------------------
SCOTT@PROD> drop table test purge;
2.6 复合压缩索引
对多个字段的组合创建索引，可以对索引字段进行压缩，
减少空间占用（use less space）
组合索引的第一个字段称为先导列（leading column），
将 where 子句中常用的、选择性好的列作为先导列
单个列选择性低，使用复合索引可以提高选择性
查询访问的复合索引列，可以只访问索引，减少物理 I/O
2.6.1 创建测试表
SCOTT@PROD> create table test as select * from emp;
2.6.2 创建复合压缩索引
SCOTT@PROD> create index ind_test on test(job,deptno) compress 1; --对第一个列进行压缩
SCOTT@PROD> select index_name,index_type,COMPRESSION from user_indexes where table_name='TEST';
INDEX_NAME INDEX_TYPE COMPRESS
------------------------------ --------------------------- --------
IND_TEST NORMAL ENABLED
SCOTT@PROD> select index_name,COLUMN_NAME,COLUMN_POSITION from user_ind_columns where table_name='TEST';
INDEX_NAME COLUMN_NAM COLUMN_POSITION
------------------------------ ---------- ---------------
IND_TEST JOB 1
IND_TEST DEPTNO 2
2.6.3 查看执行计划
SCOTT@PROD> explain plan for select * from test where job='CLERK' and deptno=10;
SCOTT@PROD> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 3856466897
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_TEST | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
相关推荐阅读： MySQL8.0新特性抢先看，性能又双叒提升了
MySQL武林秘籍，SQL学废必过考试
Linux7.6源码安装Mysql8
Oracle巡检脚本大全，服务器可直接部署
MySQL root密码忘记找回妙招
监控神器Zabbix，从部署到应用
Oracle监听日志清除
大家可以点赞、收藏、关注、评论我啦 、有数据库相关的问题随时联系我或交流哟~！
