2022年索引的五种使用模式 2.pdf

实验目的：深刻体会索引对数据库的巨大影响，了解索引不同实验背景知识：索引在数据库中是很重要的。没有索引的数据库是不可想象的，我们普通的表是无序的，也叫做堆表 (heap table) 。而索引是有序的结构，通过索引可以快速定位我们要找的行，避免全表扫描。索引的访问模式有五种：1、INDEX UNIQUE SCAN ：效率最高，主键或唯一索引；2、INDEX FAST FULL SCAN：读的最块，可以并行访问索引，但输出不按顺序；3、INDEX FULL SCAN：有顺序的输出，不能并行读索引；4、INDEX RANGE SCAN：给定的区间查询；5、INDEX SKIP SCAN：联合索引，不同值越少的列，越要放在前面。参数 optimizer_index_cost_adj 定义了索引的权重，该值越大， 数据库认为使用索引的成本越高， 默认值为100。如果设置为50，那么数据库认为使用索引的代价比它计算出来的少一半；如果你设置为1000，那么认为你使用索引的成本为计算出来的10 倍。该值最大为10000，最小为1。实验步骤：1、 登录到 scott 用户，创建测试表SQL conn scott/tiger Connected. SQL drop table t1 purge; Table dropped. SQL create table t1 as select * from dba_objects; Table created. 2、 收集测试表的统计信息，并建立索引SQL analyze table t1 compute statistics; Table analyzed. SQL create unique index i2t1 on t1(object_id); Index created. SQLset autot traceonly explain 3、 使用不同的索引访问模式查询测试表（一） INDEX UNIQUE SCAN 效率最高，主键或唯一索引SQL select * from t1 where object_id=9999; Execution Plan - Plan hash value: 1026981322 - | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | - | 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 87 | 2 (0)| 00:00:01 | 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 5 页 - - - - - - - - - |* 2 | INDEX UNIQUE SCAN | I2T1 | 1 | | 1 (0)| 00:00:01 | 执行计划为唯一定位，最快。（二） INDEX FAST FULL SCAN 读的最块，可以并行访问索引，但输出不按顺序（1）直接执行查询SQL select object_id from t1 ; Execution Plan - Plan hash value: 3617692013 - | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | - | 0 | SELECT STATEMENT | | 49859 | 194K| 337 (1)| 00:00:07 | | 1 | TABLE ACCESS FULL| T1 | 49859 | 194K| 337 (1)| 00:00:07 | 可见，查询没有使用索引，而进行了全表扫描。这是因为object_id 可能有 null 值，而null 不入普通索引，我们进行全索引的扫描就会得到错误的结果。（2）删除表中object_id 为 null 的记录，并给object_id 列加上非空约束：SQL delete t1 where object_id is null; SQL alter table t1 modify (OBJECT_ID not null); （3）再次执行相同查询SQL select object_id from t1 ; Execution Plan - Plan hash value: 2003301201 - | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | - | 0 | SELECT STATEMENT | | 49859 | 194K| 51 (2)| 00:00:02 | | 1 | INDEX FAST FULL SCAN| I2T1 | 49859 | 194K| 51 (2)| 00:00:02 | 可见，查询计划仅扫描了索引，代价为51。因为所有的行都在索引中了，使用索引不会造成错误的结果。因为我们的输出没有要求有序，所以数据库将高水位下所有的索引块都读一遍就可以了，这就叫索引的快速全扫描。由此可知， 要想索引可用，应对索引列进行非空约束。（三） INDEX FULL SCAN 有顺序的输出，不能并行读索引SQL select object_id from t1 order by object_id ; Execution Plan - Plan hash value: 1111347323 - | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | - | 0 | SELECT STATEMENT | | 49859 | 194K| 106 (2)| 00:00:03 | | 1 | INDEX FULL SCAN | I2T1 | 49859 | 194K| 106 (2)| 00:00:03 | - 执行计划为全扫描索引，含义是按照叶子的大小顺序来读索引，因为我们要求输出是有名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 5 页 - - - - - - - - - 序的。代价为106，高于快速全扫描，因为我们不是将高水位的块连续读，而是按照叶子的顺序读。正因为是按照叶子的顺序读，所以不能并行操作。（四） INDEX RANGE SCAN 给定的区间查询SQL select * from t1 where object_id between 300 and 400; Execution Plan - Plan hash value: 1490405106 - | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | - | 0 | SELECT STATEMENT | | 93 | 8091 | 4 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T1 | 93 | 8091 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | I2T1 | 93 | | 2 (0)| 00:00:01 | - 当我们的索引为非唯一，或者我们的索引唯一但查询的条件为一个范围的时候，数据库会选择范围定位。代价的大小取决于你所查询行的多少。（五） INDEX SKIP SCAN 联合索引，不同值越少的列，越要放在前面数据库的主键、唯一约束和外键的使用也要索引的参与。SQL drop table t2 purge; Table dropped. 建立一个含有owner 的表SQL create table t2 as select distinct owner from dba_objects; Table created. 建立主键SQL alter table t2 add constraint pk_t2 primary key (owner); Table altered. 建立一个级联删除的外键SQL alter table t1 add constraint fk_t1 foreign key (owner) references t2(owner) on delete cascade; Table altered. 删除 owner 列为 SYS的记录，并查看执行计划SQLDELETE T2 WHERE OWNER=SYS; 执行计划如下：Rows Row Source Operation - - 1 DELETE T2 (cr=726 pr=0 pw=0 time=16740731 us) 1 INDEX UNIQUE SCAN PK_T2 (cr=1 pr=0 pw=0 time=52 us)(object id 54503) Rows Row Source Operation - - 0 DELETE T1 (cr=725 pr=0 pw=0 time=16714571 us) 22984 TABLE ACCESS FULL T1 (cr=690 pr=0 pw=0 time=160995 us) 由此可知，在删除t2 的同时，要全表扫描t1 表，因为我们建立了外键。如果在外键上有索引，那么就很可能走索引，会极大的提高数据库的性能。4、 参数 optimizer_index_cost_adj 对索引使用的影响名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 5 页 - - - - - - - - - SQL conn scott/tiger Connected. SQL set autotrace on （一） optimizer_index_cost_adj 值为 100 时SQL alter session set optimizer_index_cost_adj = 100; Session altered. SQL select * from emp order by empno; Execution Plan - Plan hash value: 4170700152 - | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | - | 0 | SELECT STATEMENT | | 14 | 532 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 532 | 2 (0)| 00:00:01 | | 2 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 | - 可见，查询走了索引。SQL select * from emp where empno between 1 and 1000; no rows selected Execution Plan - Plan hash value: 169057108 - | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | - | 0 | SELECT STATEMENT | | 1 | 38 | 4 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | PK_EMP | 1 | | 2 (0)| 00:00:01 | 我们做了一个区间的范围查询，走了索引，进行了索引的范围查询。（二） optimizer_index_cost_adj 值为 1000 时SQL alter session set optimizer_index_cost_adj=1000; Session altered. SQL select * from emp order by empno; Execution Plan - Plan hash value: 150391907 - | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | - | 0 | SELECT STATEMENT | | 14 | 532 | 5 (20)| 00:00:01 | | 1 | SORT ORDER BY | | 14 | 532 | 5 (20)| 00:00:01 | | 2 | TABLE ACCESS FULL| EMP | 14 | 532 | 4 (0)| 00:00:01 | - 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 5 页 - - - - - - - - - 由此可见，数据库评估索引的时候认为成本很高，所以改为全表扫描了。SQL select * from emp where empno between 1 and 1000; no rows selected Execution Plan - Plan hash value: 3956160932 - | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | - | 0 | SELECT STATEMENT | | 1 | 38 | 4 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 4 (0)| 00:00:01 | 由此可见，数据库评估索引的时候认为成本很高，所以改为全表扫描了。实验结论：索引使用总论: 1、能用唯一索引，一定用唯一索引；2、能加非空，就加非空约束；3、一定要统计表的信息，索引的信息，柱状图的信息；4、联合索引的顺序不同，影响索引的选择，尽量将不同值少的列放在前面。只有做到以上四点，数据库才会正确的选择执行计划。索引是在不修改代码的情况下提高性能的重要手段，索引也是约束的维护纽带，在外键上最好建立索引。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 5 页 - - - - - - - - -