24.读书笔记收获不止Oracle之 索引的危害

24.读书笔记收获不止Oracle之 索引的危害

           索引能带来好的一面，也必定会带来坏的的东西。

来看下：

SQL> create table t_no_idx as select *from dba_objects;

Table created.

SQL> insert into t_no_idx select * fromt_no_idx;

SQL> insert into t_no_idx select * fromt_no_idx;

SQL> insert into t_no_idx select * fromt_no_idx;

SQL>commit;

SQL> select count(*) from t_no_idx;

 

 COUNT(*)

----------

   727496

Create table t_1_idx as select * fromt_no_idx;

Create index idx_1_1 on t_1_idx(object_id);

Create table  t_2_idx as select * from t_no_idx;

Create index idx_2_1 on t_2_idx(object_id);

Create index idx_2_2 ont_2_idx(object_name);

Create table t_3_idx as select * fromt_no_idx;

Create index idx_3_1 on t_3_idx(object_id);

Create index idx_3_2 on t_3_idx(object_name);

Create index idx_3_3 on t_3_idx(object_type);

 

创建了3个表，没有索引的t_no_idx表，有1个索引的t_1_idx表，有2个索引的t_2_idx，有3个索引的t_3_idx表。表的记录都是一样。

1.  索引越多插入越多

Set timing on;

SQL> Insert into t_no_idx select * fromt_no_idx where rownum<=100000;

 

100000 rows created.

 

Elapsed: 00:00:00.19

SQL> Insert into t_1_idx select * fromt_1_idx where rownum<=100000;

 

100000 rows created.

 

Elapsed: 00:00:02.70

SQL> Insert into t_2_idx select * fromt_2_idx where rownum<=100000;

 

100000 rows created.

 

Elapsed: 00:00:36.74

SQL> Insert into t_3_idx select * fromt_3_idx where rownum<=100000;

 

100000 rows created.

 

Elapsed: 00:00:53.12

有了索引，更新了记录就更新了索引，要维护索引那种有序排序的结构，开销很大。

2.  无序插入索引影响

Set timing on

SQL> Insert into t_no_idx select * from t_no_idx whererownum<=100000 order by dbms_random.random;

100000 rows created.

Elapsed: 00:00:01.32

SQL> Insert into t_1_idx select * from t_1_idx whererownum<=100000 order by dbms_random.random;

100000 rows created.

Elapsed: 00:00:05.71

SQL> Insert into t_2_idx select * from t_2_idx whererownum<=100000 order by dbms_random.random;

100000 rows created.

Elapsed: 00:00:15.65

SQL> Insert into t_3_idx select * from t_3_idx whererownum<=100000 order by dbms_random.random;

100000 rows created.

Elapsed: 00:01:01.53

插入新数据导致索引变大，索引是有序的，新增的索引建值必须插入到特定的位置，而不是随机排放。

           比如某索引块C块存放取值为100~200的键值，这时插入120~130的记录，由于C块已经装满，索引和之前存放建值为100~200的建值放在一起，需要进行重组。C块附近扩展和重组数据的动作，需要很大的开销。

           优化手段是，等插入完毕后再建索引。

 

3.  修改删除和索引

如果建了过多的索引，删除语句实际上是更新了所有的索引，不过和UPDATE不同。UPDATE更新哪一列影响哪一列。DELETE删除索引列后，索引块中的相关需删除记录只是被打上一个删除标志而已，并没有真正删除。

 

4.  索引对更新影响小结

索引对更新的影响：

l  INSERT语句负面影响最大，百害无一利，有索引，插入就慢

l  对DELETE有好有坏，海量数据定位删除少数记录时，条件列是所以列显示是必要的，但是过多列有索引还是会影响明显，因为其他列的索引也要因此被更新。经常要删除大量记录的时候，危害加剧。

l  对UPDATE语句的负面影响最小，快速定位少量记录并更新的场景和DELETE类似，但是具体修改某列时却有差别，不会触及其他所有列的维护。

5.  建索引会引发排序及锁

索引会影响更新语句外，建索引动作也会引起排序和锁。

建索引的过程会产生锁，而且不是行级锁，是把整个表锁住，任何该表的DML操作都将被阻止。建索引需要把当前列的列值都取出来，排序后依次插入块中形成索引块的，加上锁是为了避免此时列值被更新，导致顺序又变化了，影响了建索引的工作。

5.1      合理控制索引的数量

需要跟踪的索引进行监控

Alter index 索引名monitoring usage;

通过观察v$object_usage进行跟踪

Select * fromv$object_usage;

具体如下：

SQL> create table t as select *from dba_objects;

Table created.

SQL> create index idx_t_id ont(object_id);

Index created.

SQL> create index idx_t_name ont(object_name);

Index created.

SQL> select * fromv$object_usage;

no rows selected

对两列索引进行监控，继续观察v$object_usage

SQL> alter index idx_t_id  monitoring usage;

Index altered.

SQL> alter index idx_t_name monitoringusage;

SQL> set linesize 166

SQL> col index_name for a10

SQL> col table_name for a10

SQL> col monitoring for a10

SQL> col used for a10

SQL> col start_monitoring for a25

SQL> col end_monitoring for a25

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MONITORING USED          START_MONITORING               END_MONITORING

---------- ---------- ---------- ----------------------------------- -------------------------

IDX_T_ID  T         YES         NO     09/25/2015 19:35:36

IDX_T_NAME T                 YES         NO      09/25/2015 19:35:40

执行一条查询语句，然后再一次查询

SQL> select object_id from t whereobject_id=19;

 

 OBJECT_ID

----------

           19

 

SQL> select * from v$object_usage;

 

INDEX_NAME TABLE_NAME MONITORING USED          START_MONITORING               END_MONITORING

---------- ---------- ---------- ----------------------------------- -------------------------

IDX_T_ID  T         YES         YES               09/25/2015 19:35:36

IDX_T_NAME T                 YES         NO      09/25/2015 19:35:40

 

发现IDX_T_ID使用变成了YES。说明刚被使用了。但是无法知道索引被使用了多少次。使用user_indexes可以统计对当前用户下的所有索引。一般不需要对所有索引进行监控，毕竟监控也是有代价的。