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_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可以统计对当前用户下的所有索引。一般不需要对所有索引进行监控,毕竟监控也是有代价的。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |