索引负面影响测试

什么是索引

索引是为了提供更好的查询性能，通过更少的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('TEST','IDX_TEST_NAME');SEGMENT_NAME  SEGMENT_TYPE  BYTES     BLOCKS------------------------- ------------------ ---------- ----------IDX_TEST_NAME  INDEX 3145728        384TEST  TABLE 9437184       1152

如果test是压缩表，那索引占据的空间跟表就差不多了

2，对DML操作有影响，DML操作如果涉及到索引列，在表中数据更新的同时，索引也会产生维护操作

SQL> truncate table test;Table truncated.Elapsed: 00:00:00.42SQL> insert into test select * from dba_objects;    //无索引 插入72040 rows created.Elapsed: 00:00:00.39Statistics----------------------------------------------------------781  recursive calls       9987  db block gets       3840  consistent gets  2  physical reads    8421032  redo size843  bytes sent via SQL*Net to client798  bytes received via SQL*Net from client  3  SQL*Net roundtrips to/from client  2  sorts (memory)  0  sorts (disk)      72040  rows processedSQL> commit;Commit complete.Elapsed: 00:00:00.02SQL> create index idx_test_name on test(object_name);   //创建索引Index created.Elapsed: 00:00:00.15SQL> truncate table test;Table truncated.Elapsed: 00:00:00.46SQL> insert into test select * from dba_objects;    //有索引 插入72041 rows created.Elapsed: 00:00:01.36Statistics----------------------------------------------------------       1497  recursive calls      87825  db block gets       6116  consistent gets  5  physical reads   29216072  redo size843  bytes sent via SQL*Net to client798  bytes received via SQL*Net from client  3  SQL*Net roundtrips to/from client  1  sorts (memory)  0  sorts (disk)      72041  rows processedSQL> commit;Commit complete.Elapsed: 00:00:00.00

这个表上只有一个索引，性能差距如此明显，如果再加一个索引，性能耗费成倍上升

使用索引不一定总是能提升查询性能，在索引的使用上不能只考虑个别查询性能上的提升，毕竟数据库性能的提升是多方面综合的结果。