DML操作对索引的影响

delete操作

delete是对整行数据的操作，只要有索引存在，都会被影响（列值为null除外），delete删除数据的同时，索引叶块上对应的索引条目也会被打上删除标记，类似于逻辑删除，也就是说索引条目并没有被清理，仍然保存在索引块中，被标记删除的条目随后会被重用或者被延迟块处理过程清除，但是这部分空间很难被重用，除非插入适合该索引条目的数据，才能在相同的索引键位置插入数据。

下面看看数据被删除后索引块数量的情况

SQL> create table test as select * from dba_objects where object_id < 30000;Table created.SQL> create index idx_test_id on test(object_id);Index created.SQL> select index_name,leaf_blocks,blocks from user_indexes a,user_segments b where a.index_name=b.segment_name;INDEX_NAME       LEAF_BLOCKS     BLOCKS------------------------------ ----------- ----------IDX_TEST_ID65   80

索引创建后，占据80数据块，其中叶块65个，下面删除一部分数据

SQL> delete from test where object_id > 20000;9999 rows deleted.SQL> commit;Commit complete.SQL> begin  2  dbms_stats.gather_table_stats('qbuser','test');  3  end;  4  /PL/SQL procedure successfully completed.SQL> select index_name,leaf_blocks,blocks from user_indexes a,user_segments b where a.index_name=b.segment_name;INDEX_NAME       LEAF_BLOCKS     BLOCKS------------------------------ ----------- ----------IDX_TEST_ID43   80

删除一部分数据后，叶块数量减少，但整个索引占据的块数依然是没有变化，叶块数量减少是由于前面删除的数据object_id是连续的，删除后部分索引块都会变成空块，也就会从叶块上脱落，如果删除不连续的数据，叶块数量不会发生变化

SQL> drop table test;Table dropped.SQL> create table test as select * from dba_objects where object_id < 30000;Table created.SQL> create index idx_test_id on test(object_id);Index created.SQL> select index_name,leaf_blocks,blocks from user_indexes a,user_segments b where a.index_name=b.segment_name;INDEX_NAME       LEAF_BLOCKS     BLOCKS------------------------------ ----------- ----------IDX_TEST_ID65   80SQL> delete from test where mod(object_id,4) = 0;7388 rows deleted.SQL> commit;Commit complete.SQL> begin  2  dbms_stats.gather_table_stats('qbuser','test');  3  end;  4  /PL/SQL procedure successfully completed.SQL> select index_name,leaf_blocks,blocks from user_indexes a,user_segments b where a.index_name=b.segment_name;INDEX_NAME       LEAF_BLOCKS     BLOCKS------------------------------ ----------- ----------IDX_TEST_ID65   80

上面的删除操作就是在每个索引叶块中删除一部分数据，这样索引块不会成为空块，叶子块数量也不会减少，但是相比之前，叶子块存储的数据少了一半，这一半的空间也可以称之为索引碎片，这样的空间很难被重用，除非有合适的索引条目插入或者进行碎片整理，下面测试下碎片空间什么情况下可以重用。

判断空间碎片是否重用，简单点就是插入数据后查看叶块数量变化情况，插入数据较少可能无法引起叶块数量变化，部分块剩余空间足够容纳小范围索引条目

首先测试下插入的索引条目值不在当前索引范围的

SQL> insert into test select * from dba_objects where object_id between 30000 and 37000;7001 rows created.SQL> commit;Commit complete.SQL> begin  2  dbms_stats.gather_table_stats('qbuser','test');  3  end;  4  /PL/SQL procedure successfully completed.SQL> select index_name,leaf_blocks,blocks from user_indexes a,user_segments b where a.index_name=b.segment_name;INDEX_NAME       LEAF_BLOCKS     BLOCKS------------------------------ ----------- ----------IDX_TEST_ID79   88

之前30000条数据占用65个块，每个块差不多461条记录，现在插入7000条数据，如果全部使用新块存储这7000个索引条目差不多是15个左右，跟现在的79个差不多，上面的测试说明如果索引值不在当前索引值范围内，是不会重用之前由于删除操作产生的碎片空间，下面试试插入索引值在当前范围内的数据。

SQL> create table test as select * from dba_objects where object_id < 30000;Table created.SQL> create index idx_test_id on test(object_id);Index created.SQL> select index_name,leaf_blocks,blocks from user_indexes a,user_segments b where a.index_name=b.segment_name;INDEX_NAME       LEAF_BLOCKS     BLOCKS------------------------------ ----------- ----------IDX_TEST_ID65   80SQL> delete from test where mod(object_id,4) = 0;7388 rows deleted.SQL> commit;Commit complete.SQL> insert into test select * from dba_objects where object_id < 30000 and mod(object_id,4) = 2;7382 rows created.SQL> commit;Commit complete.SQL> begin  2  dbms_stats.gather_table_stats('qbuser','test');  3  end;  4  /PL/SQL procedure successfully completed.SQL> select index_name,leaf_blocks,blocks from user_indexes a,user_segments b where a.index_name=b.segment_name;INDEX_NAME       LEAF_BLOCKS     BLOCKS------------------------------ ----------- ----------IDX_TEST_ID65   80SQL> delete from test where mod(object_id,4) = 1;7365 rows deleted.SQL> commit;Commit complete.SQL> insert into test select * from dba_objects where object_id < 30000 and mod(object_id,4) = 3;7378 rows created.SQL> commit;Commit complete.SQL> begin  2  dbms_stats.gather_table_stats('qbuser','test');  3  end;  4  /PL/SQL procedure successfully completed.SQL> select index_name,leaf_blocks,blocks from user_indexes a,user_segments b where a.index_name=b.segment_name;INDEX_NAME       LEAF_BLOCKS     BLOCKS------------------------------ ----------- ----------IDX_TEST_ID65   80

上面的测试可以看出，如果插入当前索引值范围内的数据，碎片空间是可以被重用的，但是还有另外一种情况，如果索引块有足够的剩余空间，新的索引条目可能不会使用之前被删除的条目空间，如果真是这种情况，说明当前索引块空闲空间足够容纳新增的7000多个索引条目，那么即使在不删除数据的情况下，新增7000个索引条目也不会引起叶块数量的变化，下面再测试一下。

SQL> drop table test;Table dropped.SQL> create table test as select * from dba_objects where object_id < 30000;Table created.SQL> create index idx_test_id on test(object_id);Index created.SQL> select index_name,leaf_blocks,blocks from user_indexes a,user_segments b where index_name = segment_name;INDEX_NAME       LEAF_BLOCKS     BLOCKS------------------------------ ----------- ----------IDX_TEST_ID65   80SQL> insert into test select * from dba_objects where object_id < 30000 and mod(object_id,4) = 0;7388 rows created.SQL> commit;Commit complete.SQL> begin  2  dbms_stats.gather_table_stats('qbuser','test');  3  end;  4  /PL/SQL procedure successfully completed.SQL> select index_name,leaf_blocks,blocks from user_indexes a,user_segments b where index_name = segment_name;INDEX_NAME       LEAF_BLOCKS     BLOCKS------------------------------ ----------- ----------IDX_TEST_ID       129  256

现在看到叶块的数量翻倍了，也就是说索引块空闲空间是无法容纳新增的索引条目，几乎每个叶块都发生了分裂动作，导致索引叶块数量翻倍，所以之前关于索引条目没有使用碎片空间的猜测是错误的。

update操作

update操作是否影响索引取决于是不是修改了索引列，如果修改了索引列，在修改数据的同时对应的索引条目也会修改，索引本身没有修改操作，只有删除和插入，update动作发生时，会删除原有的索引条目，然后根据新的索引值寻找合适的索引块插入，现在把索引块弄紧凑一些，先建索引，再插入数据，这样操作可以让索引叶块处于饱和状态，update操作时会让索引叶块发生分裂动作，以此来判断update操作是不是在其他索引块有插入操作。

SQL> drop table test;Table dropped.SQL> create table test as select * from dba_objects where 1<0;Table created.SQL> create index idx_test_id on test(object_id);Index created.SQL> insert into test select * from dba_objects where object_id < 30000;29513 rows created.SQL> commit;Commit complete.SQL> begin  2  dbms_stats.gather_table_stats('qbuser','test');  3  end;  4  /PL/SQL procedure successfully completed.SQL> select index_name,leaf_blocks,blocks from user_indexes a,user_segments b where a.index_name=b.segment_name;INDEX_NAME       LEAF_BLOCKS     BLOCKS------------------------------ ----------- ----------IDX_TEST_ID59   72SQL> update test set object_id = 1000 where object_id = 10000;1 row updated.SQL> commit;Commit complete.SQL> begin  2  dbms_stats.gather_table_stats('qbuser','test');  3  end;  4  /PL/SQL procedure successfully completed.SQL> select index_name,leaf_blocks,blocks from user_indexes a,user_segments b where a.index_name=b.segment_name;INDEX_NAME       LEAF_BLOCKS     BLOCKS------------------------------ ----------- ----------IDX_TEST_ID60   72

update操作后，叶块A上索引值是10000的条目被标记删除，在叶块B上插入索引值1000的条目，由于叶块B上空间已满，发生分裂动作，叶块总数从59变成60,。

insert操作

insert操作影响表上的所有索引，当然，插入空值除外，索引不会对空值创建索引条目，索引不断增长的主要原因是insert操作，这是不可避免的，索引在面对insert操作是有些情况还是需要注意。

列值递增，比如该列使用了序列，或者是时间戳，这种情况下，新的索引值总是大于当前索引范围，如果由于delete和update操作出现空间碎片，很难被重用，长期积累下来得空间碎片是不小的开销，只能定期重建索引或者使用循环序列。

值范围太小，比如出生年份，由于业务特点，大部分用户出生年份都集中在某一个时间段，大量小范围值的插入会促使一小部分索引块不断的进行分裂产生新块，分支节点也会很快被新块的记录信息填满，最坏的结果是导致根块分裂，索引层级增加。这种数据倾斜的情况是不是可以考虑该索引对查询性能提升有多大，如果没有很明显的差距，去掉也许会更好。