什么是索引
索引是为了提供更好的查询性能,通过更少的IO查找同样的数据,索引的性能参照就是全表扫描,oracle按照执行计划在表中查找数据只有全表扫描和索引这两种方式,这两种方式没有好坏之分,只有适不适合
索引负面影响
1,索引要额外占据存储空间,这个额外空间并不小
SQL> create table test as select * from dba_objects;
Table created.
SQL> create index idx_test_name on test(object_name);
Index created.
SQL> select segment_name,segment_type,bytes,blocks from user_segments where segment_name in('T
EST','IDX_TEST_NAME');
SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS
------------------------- ------------------ ---------- ----------
IDX_TEST_NAME INDEX 3145728 384
TEST TABLE 9437184 1152
如果test是压缩表,那索引占据的空间跟表就差不多了
2,对DML操作有影响,DML操作如果涉及到索引列,在表中数据更新的同时,索引也会产生维护操作
SQL> truncate table test;
Table truncated.
Elapsed: 00:00:00.42
SQL> insert into test select * from dba_objects; //无索引 插入
72040 rows created.
Elapsed: 00:00:00.39
Statistics
----------------------------------------------------------
781 recursive calls
9987 db block gets
3840 consistent gets
2 physical reads
8421032 redo size
843 bytes sent via SQL*Net to client
798 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
72040 rows processed
SQL> commit;
Commit complete.
Elapsed: 00:00:00.02
SQL> create index idx_test_name on test(object_name); //创建索引
Index created.
Elapsed: 00:00:00.15
SQL> truncate table test;
Table truncated.
Elapsed: 00:00:00.46
SQL> insert into test select * from dba_objects; //有索引 插入
72041 rows created.
Elapsed: 00:00:01.36
Statistics
----------------------------------------------------------
1497 recursive calls
87825 db block gets
6116 consistent gets
5 physical reads
29216072 redo size
843 bytes sent via SQL*Net to client
798 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
72041 rows processed
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00
这个表上只有一个索引,性能差距如此明显,如果再加一个索引,性能耗费成倍上升
使用索引不一定总是能提升查询性能,在索引的使用上不能只考虑个别查询性能上的提升,毕竟数据库性能的提升是多方面综合的结果。