摘要

任何事物都有它的两面性，索引也不例外，本文我们主要来阐述索引的坏处。

在数据库系统中，索引对查询的性能比较好，不过如果更新语句出现，索引的缺点将很明显。因为索引本身是有序的，而更新数据的过程中也要更新索引，更新完后还要保持索引的有序性，这就需要付出很大的开销了，索引不好的一面就从这里开始显示出来了。

本章我们先简要综述一下索引的不足之处有哪些，接下来详细阐述，随后一起和大家探索各种工作中的案例，最后为思考回顾。总体学习思路如下图所示：

关键词

开销、失效、热点块、聚合因子、并行、更新、锁等待

1 索引的不足之处

关于索引的不足之处我们可以从索引的开销和容易失效这两个方面来讨论，如下图所示：

1.1 索引的各种开销

还记得之前关于索引结构的分析吗？通过系列步骤，我们明白了索引的结构，推导出索引的三大特性，并应用这些特性让SQL跑得更快。

这只是索引好的一面。真正有问题的一面被掩盖了。都有什么问题呢？

1.1.1 .热块竞争

你看，索引最新的数据块一般是在最右边，而我们访问数据时正常来说也是访问比较新的数据，历史数据很少有人关注。然后问题来了，大家都一起访问最新的数据，不是都集中于同一个目标来访问了吗？这就很容易产生热块竞争。

1.1.2 . 回表开销

另外，大家都知道索引存储索引列的值和rowid，通过rowid来定位回到表中。其实这个回到表中的开销也是很大，具体情况我们随后可以了解到。

 1.1.3 . 更新开销

索引的有序性是一个非常重要的特性，这个特性能够消除排序等开销，但是索引块要保持有序性，可不是一件容易的事。毕竟索引列的数据是随机插入的，比如你在原来的索引列中存储的是100、110、111，等等时，现在要插入101，就应该在100和111之间插入，为了保证这个顺序索引需要做很多事，比如索引块分裂。而索引列的增删改的开销是很大的。

1.1.4 . 建立开销

 还有千万别忽略了建立索引的开销，这也和索引的有序性有关。我们在建索引的过程中，首先把索引列的数据排序提取出来，再插入到块中形成索引块，这时如果数据不断地插入，排序提取这个动作什么时候能结束呢？所以还必须要锁表，这就是一个很大的开销（online建索引是一种特殊的思路，这里不做描述）。当然建索引过程中排序这个动作本身也是一个不小的开销。

1.2 索引使用失效

索引的不足之处除了上述的几点外，从另一个维度看，还会有失效的可能。我们现在知道建索引对查询一般比较有利，对更新一般比较有害。不过有的时候，虽然建了索引，但其对查询毫无帮助，这种情况还是有的。比如索引失效了，这分为逻辑失效和物理失效两种。

1.2.1 . 逻辑失效

 逻辑失效是索引本身并没有真正失效，只是由于写法的问题导致索引用不上，比如对SQL的条件列进行运算，类似select * from t where upper(name)=‘ABC’等，这时在name列上建了Btree索引是用不上的。再或者比如被人强制用了全表扫描的Hint等导致数据库被迫不用索引，等等。

1.2.2 . 物理失效

而物理失效就是索引真的失效了，比如被人误设了unusable动作，或者是一些类似分区表的不规范操作导致的索引失效。对此后续有详细的例子说明。

2 感受美好索引另一面

前面简要描述了索引的不足之处，接下来我们进行更加详细的展开说明，具体细节如下：

 2.1 索引各种开销

2.1.1 . 访问集中导致热块的竞争

由于一般来说，最新的值都是最新产生的，所以访问它容易产生热块竞争。举例来说，如： select * from t where id=100000，select * from t where id=99999;select * from t where id=99998;select * from t where id=99997;这些数据很可能是相邻的，那么它们就会在同一个索引块上，这样很容易产生热点索引块竞争。

2.1 2 . 回表性能取决于聚合因子

结论：索引查询要尽可能避免回表，如果不可避免，则需要关注聚合因子是否过大。（注：这个例子在前面的章节已经说过了，这里就不再详述了。）在该例子中，构造脚本ORGANIZED表的聚合因子比较小，回表的代价较低，产生了2900个BUFFER，如下：

select /*+ index( colocated colocated_pk ) */ * from colocated where x between 20000 and 40
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'runstats_last'));                                                                                                         
-------------------------------------------------------------------------
| Id |Operation                   |Name        |Starts|E-Rows|A-Rows|   A-Time   |Buffers |
-------------------------------------------------------------------------------------------
|   0|SELECT STATEMENT            |            |     1|      | 20001|00:00:00.05 |   2900 |
|   1| TABLE ACCESS BY INDEX ROWID|COLOCATED   |     1| 20002| 20001|00:00:00.05 |   2900 |
|*  2|  INDEX RANGE SCAN          |COLOCATED_PK|     1| 20002| 20001|00:00:00.03 |   1375 |
-------------------------------------------------------------------------------------------

而DISORGANIZED表的聚合因子比较大，回表的代价很高，如下，产生21360个BUFFER：

---两者性能差异显著,DISORGANIZED表的聚合因子比较大，回表的代价很高，如下，产生21360个BUFFER
select /*+ index( disorganized disorganized_pk ) */* from disorganized  where x between 20000 and 40000;
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'runstats_last'));
--------------------------------------------------------------------------
| Id |Operation                   |Name           |Starts|E-Rows|A-Rows|   A-Time  |Buffers|
--------------------------------------------------------------------------------------------
|   0|SELECT STATEMENT            |               |     1|      | 20001|00:00:00.09|  21360|
|   1| TABLE ACCESS BY INDEX ROWID|DISORGANIZED   |     1| 20002| 20001|00:00:00.09|  21360|
|*  2|  INDEX RANGE SCAN          |DISORGANIZED_PK|     1| 20002| 20001|00:00:00.03|   1375|
--------------------------------------------------------------------------------------------    
select a.table_name,
       a.index_name,
       a.blevel,
       a.leaf_blocks,
       b.num_rows,
       b.blocks,
       a.clustering_factor,
       trunc(a.clustering_factor / b.num_rows,2) cluster_rate
  from user_indexes a, user_tables b
 where a.table_name = b.table_name
     and index_name in ('COLOCATED_PK', 'DISORGANIZED_PK' )
     and a.clustering_factor is not null
 order by cluster_rate desc;
TABLE_NAME   INDEX_NAME    BLEVEL LEAF_BLOCKS  NUM_ROWS BLOCKS CLUSTERING_FACTOR CLUSTER_RATE
----------   ----------     ------------------ ---------------------------------------------
DISORGANIZED DISORGANIZED_PK    1        208    100000    1219         99927         .99
COLOCATED    COLOCATED_PK       1        208    100000    1252          1190         .01

2.1.3. 索引更新方面的开销比较大

环境搭建：

drop table t_big purge;
drop table t purge;
create table t as select * from dba_objects;
set autotrace off
create table t_big as select * from t ;
insert into  t_big select * from t_big;
insert into  t_big select * from t_big;
insert into  t_big select * from t_big;
insert into  t_big select * from t_big;
insert into  t_big select * from t_big;
insert into  t_big select * from t_big;
commit;
drop table  t_small purge;
create table t_small as select * from t where rownum<=1000;
 
set timing on 
insert into t_small select * from t_big;
已创建4684096行。
已用时间:  00: 00: 28.46
commit;
insert into t_big select * from t_big;
已创建4684096行。
已用时间:  00: 00: 28.22
commit;

由上面代码可以看出，虽然t_small是小表，t_big是大表。但是插入一般不会随着记录的增加越插越慢。什么时候会越插越慢，就是当表有索引的时候。因为索引需要维护，越大维护越困难。我们继续做一组试验。

‘环境准备（建3张结构和记录都一样的表，只是索引分别是6个、2个及无索引）：

drop table test1 purge;
drop table test2 purge;
drop table test3 purge;
drop table t purge;
create table t as select * from dba_objects;
create table test1 as select * from t;
create table test2 as select * from t;
create table test3 as select * from t;
create index idx_owner on test1(owner);
create index idx_object_name on test1(object_name);
create index idx_data_obj_id on test1(data_object_id);
create index idx_created on test1(created);
create index idx_last_ddl_time on test1(last_ddl_time);
create index idx_status on test1(status);
create index idx_t2_sta on test2(status);
create index idx_t2_objid on test2(object_id);

分别往这三张表里插记录：

SQL> set timing on
SQL> --语句1(test1表有6个索引）
SQL> insert into test1 select * from t;
已创建111128行。
已用时间:  00: 00: 06.08
SQL> commit;
提交完成。
已用时间:  00: 00: 00.00
SQL> --语句2(test2表有2个索引）
SQL> insert into test2 select * from t;
已创建111128行。
已用时间:  00: 00: 03.59
SQL> commit;
提交完成。
已用时间:  00: 00: 00.00
SQL> --语句3(test3表有无索引）
SQL> insert into test3 select * from t;
已创建111128行。
已用时间:  00: 00: 00.37
SQL> commit;
提交完成。
已用时间:  00: 00: 00.00

表记录越大，索引越多，插入越慢，从试验结果来看，这一点还是非常明显的。

2.1.4 . 建索引的过程开销也很大

（a）建索引过程会产生全表锁

drop table t purge;
create table t as select * from dba_objects;
insert into t  select * from t;
insert into t  select * from t;
insert into t  select * from t;
insert into t  select * from t;
insert into t  select * from t;
insert into t  select * from t;
insert into t  select * from t;
commit;
select sid from v$mystat where rownum=1; 
--12
set timing on
create index idx_object_id on t(object_id);
索引已创建。
session 2
sqlplus ljb/ljb
set linesize 1000
select sid from v$mystat where rownum=1; 
--134
--以下执行居然被阻塞，要直至建索引结束后，才能执行
update t set object_id=99999 where object_id=8;

可以通过如下方式查看被锁的情况：

打开一个新的窗口

set linesize 1000
select * from v$lock where sid in (12,134);
SQL> select * from v$lock where sid in (134,12);
ADDR     KADDR      SID TY        ID1        ID2   LMODE    REQUEST      CTIME   BLOCK
-------- -------- ----- -- ---------- ---------- ------- ---------- ---------- -------
2EB79320 2EB7934C    12 AE        100          0       4          0        409       0
2EB79394 2EB793C0   134 AE        100          0       4          0        254       0
2EB79408 2EB79434    12 TO      65921          1       3          0        402       0
2EB79574 2EB795A0    12 DL     106831          0       3          0         12       0
2EB795E8 2EB79614    12 DL     106831          0       3          0         12       0
0EDD7A9C 0EDD7ACC   134 TM     106831          0       0          3         10       0
0EDD7A9C 0EDD7ACC    12 TM     106831          0       4          0         12       1
0EDD7A9C 0EDD7ACC    12 TM         18          0       3          0         12       0
2C0D2844 2C0D28B0    12 TS          8   25202162       6          0          4       0
2C1A2A8C 2C1A2ACC    12 TX     393223      31633       6          0         12       0
select  /*+no_merge(a) no_merge(b) */
(select username from v$session where sid=a.sid) blocker,a.sid, 'is blocking',
(select username from v$session where sid=b.sid) blockee,b.sid
from v$lock a,v$lock b
where a.block=1 and b.request>0
and a.id1=b.id1
and a.id2=b.id2;
BLOCKER        SID 'ISBLOCKING BLOCKEE         SID
------------------ ----------- -------------------
LJB             12 is blocking LJB             134

（b）建索引过程会产生全表排序

未建索引前，观察一下数字字典中记录的系统排序情况，如下：

set linesize 266
drop table t purge;
create table t as select * from dba_objects;
select t1.name, t1.STATISTIC#, t2.VALUE
  from v$statname t1, v$mystat t2
 where t1.STATISTIC# = t2.STATISTIC#
   and t1.name like '%sort%';
NAME                                                             STATISTIC#      VALUE
---------------------------------------------------------------- ---------- ----------
sorts (memory)                                                          565        462
sorts (disk)                                                            566          0
sorts (rows)                                                            567       2174
```
建索引后，继续观察，发现排序次数sorts (memory)增加了，如下：
```sql
create index idx_object_id on t(object_id);
select t1.name, t1.STATISTIC#, t2.VALUE
  from v$statname t1, v$mystat t2
 where t1.STATISTIC# = t2.STATISTIC#
   and t1.name like '%sort%';
NAME                                                             STATISTIC#      VALUE
---------------------------------------------------------------- ---------- ----------
sorts (memory)                                                          565        463
sorts (disk)                                                            566          0
sorts (rows)                                                            567      75292

索引各种开销的总结：

未完待续...

论索引影响性能的一面②索引的使用失效销

公众号：收获不止数据库

微信：ljbyxl1213

• “大白话人工智能” 系列

• “数据库拍案惊奇” 系列

• “世事洞明皆学问” 系列