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_IDT 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_IDT 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可以统计对当前用户下的所有索引。一般不需要对所有索引进行监控，毕竟监控也是有代价的。
