前言
索引原理及应用实践
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;
深知大多数程序员，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！
既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上鸿蒙开发知识点，真正体系化！
由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新
