往期回顾 

     论索引影响性能的一面①索引的各种开销 

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

摘要：

在数据库优化的世界里，索引是提升查询性能的银弹，但滥用或忽视其另一面——维护开销，则可能引发严重的性能灾难。本文将通过三个“血淋淋”的真实案例，深入剖析索引并行属性、分区表插入以及Online DDL操作中隐藏的性能陷阱，帮助你建立对索引成本的全面认知，写出更健壮、更高性能的SQL。

引言：为何我们总在谈论索引的好处，却忽视其代价？

作为DBA或后端开发者，我们对“索引能加速查询”这一信条深信不疑。但索引并非没有成本。它是一个需要存储空间、并在DML（INSERT/UPDATE/DELETE）操作时需要实时维护的数据库对象。这种维护开销，在特定场景下会成为系统的性能瓶颈，甚至引发故障。

本文不谈索引如何提升SELECT，而是聚焦于其“副作用”，通过三个经典的生产案例，带你深入理解索引的另一面。

 3 从案例看索引各种苦

3.1. 设置索引并行属性引风波

某系统，为了提高建索引的效率，相关人员采用了并行建索引的方式，最终索引建成功后，并行度设到了索引的属性中去了，然后忘记将并行度取消。结果很多查询都调用并行，引起资源争用，最终引发了性能故障。

drop table t purge;
create table t as select * from dba_objects where object_id is not null;
alter table T modify object_id not null;
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;
set timing on
create index idx_object_id on t(object_id) parallel 8;
select index_name,degree from user_indexes where table_name='T';
INDEX_NAME                     DEGREE
------------------------------ -------
IDX_OBJECT_ID                  8
对应的执行计划如下：
set linesize 1000
set autotrace traceonly
select count(*) from t;
执行计划
--------
|Id|Operation                 |Name         |Rows |Cost(%CPU)|  TQ |IN-OUT|PQ Distrib|
--------------------------------------------------------------------------------------
| 0|SELECT STATEMENT          |             |   1 |5797   (2)|     |      |          |
| 1| SORT AGGREGATE           |             |   1 |          |     |      |          |
| 2|  PX COORDINATOR          |             |     |          |     |      |          |
| 3|   PX SEND QC (RANDOM)    |:TQ10000     |   1 |          |Q1,00| P->S |QC (RAND) |
| 4|    SORT AGGREGATE        |             |   1 |          |Q1,00| PCWP |          |
| 5|     PX BLOCK ITERATOR    |             |8100K|5797   (2)|Q1,00| PCWC |          |
| 6|      INDEX FAST FULL SCAN|IDX_OBJECT_ID|8100K|5797   (2)|Q1,00| PCWP |          |
-------------------------------------------------------------------------------------
统计信息
--------
         24  recursive calls
          0  db block gets
      25365  consistent gets
      20769  physical reads
          0  redo size
        426  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

这里可以看出，一个普通查询产生了并行操作，所有调用该表的语句都会产生并行动作，最终必然会导致争用！一般来说，如果我们要进行并行操作，建议用HINT的方式给语句加并行属性，比如/*+parallel n*/。

3.2. 分区表与插入性能的提升

如果表上没有索引，插入的速度一般都不会慢，只有在有索引的情况下，才要考虑插入速度的优化。如果表有大量索引，一般来说，对于分区表的局部索引，由于只需要更新局部分区的索引，所以索引的开销会比较小，所以插入性能比有着相同的记录数、列及索引的普通表更快。

信不，觉得有道理？嗯，来，看看试验过程，首先是构造环境，如下：

--构造分区表，插入数据。
drop table range_part_tab purge;
create table range_part_tab (id number,deal_date date,area_code number,nbr1 number,　nbr2 number,nbr3 number,contents varchar2(4000))
           partition by range (deal_date)
           (
           partition p_201501 values less than (TO_DATE('2024-02-01', 'YYYY-MM-DD')),
           partition p_201502 values less than (TO_DATE('2024-03-01', 'YYYY-MM-DD')),
           partition p_201503 values less than (TO_DATE('2024-04-01', 'YYYY-MM-DD')),
           partition p_201504 values less than (TO_DATE('2024-05-01', 'YYYY-MM-DD')),
           partition p_201505 values less than (TO_DATE('2024-06-01', 'YYYY-MM-DD')),
           partition p_201506 values less than (TO_DATE('2024-07-01', 'YYYY-MM-DD')),
           partition p_201507 values less than (TO_DATE('2024-08-01', 'YYYY-MM-DD')),
           partition p_201508 values less than (TO_DATE('2024-09-01', 'YYYY-MM-DD')),
           partition p_201509 values less than (TO_DATE('2024-10-01', 'YYYY-MM-DD')),
           partition p_201510 values less than (TO_DATE('2024-11-01', 'YYYY-MM-DD')),
           partition p_201511 values less than (TO_DATE('2024-12-01', 'YYYY-MM-DD')),
           partition p_201512 values less than (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
           partition p_201601 values less than (TO_DATE('2025-02-01', 'YYYY-MM-DD')),
           partition p_201602 values less than (TO_DATE('2025-03-01', 'YYYY-MM-DD')),
           partition p_201603 values less than (TO_DATE('2025-04-01', 'YYYY-MM-DD')),
           partition p_201604 values less than (TO_DATE('2025-05-01', 'YYYY-MM-DD')),
           partition p_max values less than (maxvalue)
           );
create index idx_parttab_id   on range_part_tab(id)         local;
create index idx_parttab_nbr1 on range_part_tab(nbr1)       local;
create index idx_parttab_nbr2 on range_part_tab(nbr2)       local;
create index idx_parttab_nbr3 on range_part_tab(nbr3)       local;
create index idx_parttab_area on range_part_tab(area_code)  local;
drop table normal_tab purge;
--接下来建普通表
create table normal_tab (id number,deal_date date,area_code number,nbr1 number,nbr2 number,nbr3 number,contents varchar2(4000));
create index idx_tab_id on normal_tab(id) ;
create index idx_tab_nbr1 on normal_tab(nbr1) ;
create index idx_tab_nbr2 on normal_tab(nbr2) ;
create index idx_tab_nbr3 on normal_tab(nbr3) ;
create index idx_tab_area on normal_tab(area_code) ;

接下来分别插入，比较性能:

SQL> set timing on
SQL> insert into range_part_tab
  2       select rownum,
  3               to_date( to_char(sysdate+60,'J')+TRUNC(DBMS_RANDOM.VALUE(0,60)),'J'),
  4               ceil(dbms_random.value(591,599)),
  5               ceil(dbms_random.value(18900000001,18999999999)),
  6               ceil(dbms_random.value(18900000001,18999999999)),
  7               ceil(dbms_random.value(18900000001,18999999999)),
  8               rpad('*',400,'*')
  9          from dual
 10        connect by rownum <= 400000;
已创建400000行。
已用时间:  00: 00: 51.20
SQL> insert into normal_tab
  2       select rownum,
  3               to_date( to_char(sysdate+60,'J')+TRUNC(DBMS_RANDOM.VALUE(0,60)),'J'),
  4               ceil(dbms_random.value(591,599)),
  5               ceil(dbms_random.value(18900000001,18999999999)),
  6               ceil(dbms_random.value(18900000001,18999999999)),
  7               ceil(dbms_random.value(18900000001,18999999999)),
  8               rpad('*',400,'*')
  9          from dual
 10        connect by rownum <= 400000;
已创建400000行。
已用时间:  00: 01: 20.04

普通表插入的时间是1分20秒，而分区表是51秒，看来结论果真不假。

 3.3 未用online建索引酿大错

普通的对表建索引的操作将会导致针对该表的更新操作无法进行，需要等待索引建完。更新操作将会被建索引动作阻塞。而ONLINE建索引的方式却不会阻止针对该表的更新操作，与建普通索引相反的是，ONLINE建索引的动作是反过来被更新操作阻塞。如下：

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) online;
索引已创建。
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;
接着分析：
session 3
set linesize 1000
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        278       0
2EB79394 2EB793C0    134 AE        100          0       4        0        303       0
2EB79408 2EB79434     12 DL     106831          0       3        0         25       0
2EB79574 2EB795A0     12 DL     106831          0       3        0         25       0
2EB795E8 2EB79614     12 OD     106831          0       4        0         25       0
2EB7965C 2EB79688     12 TX     131079      31688       0        4         11       0
0EDD7A9C 0EDD7ACC    134 TM     106831          0       3        0         23       0
0EDD7A9C 0EDD7ACC     12 TM     106831          0       2        0         25       0
0EDD7A9C 0EDD7ACC     12 TM     106834          0       4        0         25       0
2C17C3B8 2C17C3F8    134 TX     131079      31688       6        0         23       1
2C1A2448 2C1A2488     12 TX     589853      31754       6        0         25       0
已选择11行。
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              134 is blocking LJB               12

总结：建立索引的全生命周期管理思维

通过以上三个案例，我们可以看到索引并非“一建了之”的简单操作。它是一个需要我们从设计、运维到维护全方位考量的数据库对象。

设计时：要预判表的读写模型。对于写密集型的大表，应优先考虑分区表+局部索引的架构，从根源上降低DML开销。

运维时：要规范操作流程。任何可能阻塞业务的DDL，都必须使用**ONLINE关键字。任何临时的性能工具（如PARALLEL），都必须遵循“谁开启，谁清理”的原则。

维护时：要定期审视。索引的属性（如并行度）、健康状况（是否需要重建）都应纳入日常巡检，避免其成为潜在的性能隐患。

真正理解并敬畏索引的开销，才能在数据库优化的道路上走得更远、更稳。

未完待续...

论索引影响性能的一面④ 索引失踪之谜【上】

公众号：收获不止数据库

微信：ljbyxl1213

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

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

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