索引，使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。
在关系数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的语句执行得更快。索引的作用相当于图书的目录，可以根据目录中的快速找到所需的内容。当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量 时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。
索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑 清单。
索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引的方式与您使用书籍中的索引的方式很相似：它搜索索引以找到特定值，然后顺指针找到包含该值的行。
点评：索引是提高语句性能的关键，而且不用修改程序！
该实验的目的是深刻体会索引对数据库的巨大影响。
索引在数据库中是很重要的。没有索引的数据库是不可想象的,我们普通的表是无序的,也叫做堆表(heap table),一句话概括索引,索引是有序的结构,通过索引可以快速定位我们要找的行,避免全表扫描。索引的访问模式有八种。
1.INDEX UNIQUE SCAN 效率最高，主键或唯一索引，走树结构。
2.INDEX FAST FULL SCAN 读所有块，可以并行访问索引，但输出不按顺序。
3.INDEX FULL SCAN 有顺序的输出，不能并行读索引，走链表结构。
4.INDEX RANGE SCAN 给定的区间查询，最常见的访问模式。
5.INDEX SKIP SCAN 联合索引的第二列为条件，不同值越少的列，越要放在前面。
6. SCAN DESCENDING 降序扫描，自动选择降序使用索引。
7. index join 索引的连接，通过索引获得全部数据，可以不扫描表。
8. bitmap join 索引的位图连接，多个条件上的列都有索引的情况。
SQL> conn scott/tiger
Connected.
SQL> drop table t1 purge;
Table dropped.
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> analyze table t1 compute statistics;
收集表t1的统计信息
Table analyzed.
SQL> create unique index i2t1 on t1(object_id);
Index created.
SQL>set autot traceonly explain
1.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 |
执行计划为唯一定位,最快。
2.INDEX FAST FULL SCAN 读的最块，可以并行访问索引，但输出不按顺序
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不入普通索引。我们进行全索引的扫描就会得到错误的结果。这是全表扫描是正确的。虽然我们的的查询仅包含了索引中的值。
我们如果有非空约束就会极大的提高性能。
SQL> delete t1 where object_id is null;
SQL> alter table t1 modify (OBJECT_ID not null);
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.因为所有的行都在索引中了,使用索引不会造成错误的结果。因为我们的输出没有要求有序,所以数据库将高水位下所有的索引块都读一遍就可以了,这就叫索引的快速全扫描。
3.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,高于快速全扫描,因为我们不是将高水位的块连续读,而是按照叶子的顺序读。正因为是按照叶子的顺序读,所以不能并行操作。
4.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 |
------------------------------------------------------------------------------------
当我们的索引为非唯一,或者我们的索引唯一但查询的条件为一个范围的时候数据库会选择范围定位。
代价的大小取决于你所查询行的多少。
5.INDEX SKIP SCAN 联合索引，不同值越少的列，越要放在前面
在下一个实验 联合索引中有详细的描述。
数据库的主键,唯一约束和外键的使用也要索引的参与。
SQL> drop table t2 purge;
Table dropped.
SQL> create table t2 as select distinct owner from dba_objects;
建立一个含有owner的表。
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.
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表,因为我们建立了外键。如果在外键上有索引,那么就很可能走索引,会极大的提高数据库的性能。
6. SCAN DESCENDING 降序使用索引
SQL> SELECT * FROM EMP ORDER BY 1 DESC;
执行计划
----------------------------------------------------------
Plan hash value: 3088625055
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 546 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 546 | 2 (0)| 00:00:01 |
| 2 | INDEX FULL SCAN DESCENDING| PK_EMP | 14 | | 1 (0)| 00:00:01 |
7. index join
SQL> spool c:\1.txt
SQL> conn scott/tiger
已连接。
SQL> drop table t1 purge;
表已删除。
SQL> create table t1 as select * from dba_objects;
表已创建。
SQL> create index i1 on t1(object_name);
索引已创建。
SQL> create index i2 on t1(object_type);
索引已创建。
SQL> exec dbms_stats.gather_table_stats('SCOTT','T1');
PL/SQL 过程已成功完成。
SQL> set autot trace expl
SQL> SELECT OBJECT_NAME,OBJECT_TYPE FROM T1
2 WHERE OBJECT_NAME LIKE 'E%' AND OBJECT_TYPE='TABLE';
执行计划
----------------------------------------------------------
Plan hash value: 1423132391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21 | 714 | 11 (10)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 21 | 714 | 11 (10)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEXRANGESCAN| I2 | 21 | 714 | 5 (0)| 00:00:01 |
|* 4 | INDEXRANGESCAN| I1 | 21 | 714 | 6 (17)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_TYPE"='TABLE' AND "OBJECT_NAME" LIKE 'E%')
2 - access(ROWID=ROWID)
3 - access("OBJECT_TYPE"='TABLE')
4 - access("OBJECT_NAME" LIKE 'E%')
8. bitmap join
SQL> SELECT COUNT(*) FROM T1 WHERE OBJECT_NAME LIKE 'E%' OR OBJECT_TYPE='TABLE';
执行计划
----------------------------------------------------------
Plan hash value: 1019523335
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 11 (10)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 34 | | |
| 2 | BITMAP CONVERSION COUNT | | 2172 | 73848 | 11 (10)| 00:00:01 |
| 3 | BITMAP OR | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 5 | INDEX RANGE SCAN | I2 | | | 5 (0)| 00:00:01 |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 7 | SORT ORDER BY | | | | | |
|* 8 | INDEX RANGE SCAN | I1 | | | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("OBJECT_TYPE"='TABLE')
8 - access("OBJECT_NAME" LIKE 'E%')
filter("OBJECT_NAME" LIKE 'E%' AND "OBJECT_NAME" LIKE 'E%')
索引使用总论:
能用唯一索引，一定用 唯一索引
能加非空，就加 非空约束
一定要 统计表的信息，索引的信息，柱状图的信息。
联合索引的 顺序不同，影响索引的选择，尽量将不同值少的列放在前面
如果你需要的列都存在于索引中，那么数据库只需要查询索引而不去查询表，大大提高系统的性能。
只有做到以上四点，数据库才会正确的选择执行计划。
索引是在不修改代码的情况下提高性能的重要手段。索引也是约束的维护纽带。在 外键上最好建立索引。
参数optimizer_index_cost_adj 定义了索引的权重,该值越大,数据库认为使用索引的成本越高,默认值为100,如果设置为50,那么数据库认为使用索引的代价比它计算出来的少一半,如果你设置为1000,那么认为你使用索引的成本为计算出来的10倍,该值最大为10000,最小为1.
SQL> conn scott/tiger
Connected.
SQL> set autot on
SQL> alter session set optimizer_index_cost_adj = 100;
SQL> select * from emp order by empno;
这句话可以走索引,也可以不走索引。默认为100的情况。
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 |
我们做了一个区间的范围查询,走了索引,进行了索引的范围查询。
SQL> alter session set optimizer_index_cost_adj= 1000;
Session altered.
将该参数的值改为1000,数据库评估索引的时候认为成本很高。
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 |
---------------------------------------------------------------------------
所以改为全表扫描了。
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 |
认为索引的成本高,改为全表扫描了。