索引的五种使用模式

实验目的： 深刻体会索引对数据库的巨大影响，了解索引不同 实验背景知识：

索引在数据库中是很重要的。没有索引的数据库是不可想象的，我们普通的表是无序的，也叫做堆表(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. SQL>set 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 | |* 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 | ------------------------------------------------------------------------- 执行计划为全扫描索引，含义是按照叶子的大小顺序来读索引，因为我们要求输出是有序的。代价为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的记录，并查看执行计划 SQL>DELETE 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对索引使用的影响