深知大多数程序员,想要提升技能,往往是自己摸索成长,但自己不成体系的自学效果低效又漫长,而且极易碰到天花板技术停滞不前!
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,涵盖了95%以上鸿蒙开发知识点,真正体系化!
由于文件比较多,这里只是将部分目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新
如果所需访问的列(检索条件列和返回的列)均在索引上,则可以避免对相对大的表的访问,而只需要访问体积小得多的索引。这也会带来访问开销的降低,从而提升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.索引相关常用命令
深知大多数程序员,想要提升技能,往往是自己摸索成长,但自己不成体系的自学效果低效又漫长,而且极易碰到天花板技术停滞不前!
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,涵盖了95%以上鸿蒙开发知识点,真正体系化!
由于文件比较多,这里只是将部分目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新
链图片转存中…(img-mxv0ZG9B-1715522813229)]
[外链图片转存中…(img-Wvn01BX5-1715522813230)]
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,涵盖了95%以上鸿蒙开发知识点,真正体系化!
由于文件比较多,这里只是将部分目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新