本文为云贝教育 刘峰 原创，请尊重知识产权，转发请注明出处，不接受任何抄袭、演绎和未经注明出处的转载。

一、索引分裂概述

Oracle数据库中的索引分裂是数据库维护和性能管理中的一个重要概念，特别是在处理大量数据插入、更新和删除操作时。索引分裂发生的原因、类型以及对数据库性能的影响如下：

1.1 索引分裂的原因

索引分裂主要发生在以下场景：

• 插入操作：当向已满或接近满的索引块中插入新记录时，如果该块没有足够的空间容纳新数据，索引就需要分裂来创造额外的空间。

• 更新操作：如果更新导致索引项的大小变化，以至于原块无法容纳，则可能触发索引块的分裂。

• 删除操作：虽然删除通常不会直接导致索引块分裂，但在某些情况下，删除后留下的空间碎片可能需要通过索引重组或合并来优化。

1.2 索引分裂的类型

索引分裂主要有三种基本类型：

1. 叶节点分裂（Leaf Node Split）：这是最常见的类型，当索引叶子节点（存储实际索引值的块）没有足够的空间来容纳新的索引条目时，叶子节点会被分裂成两个或更多块。大多数情况下，分裂是90-10或50-50的比例，即原块的一部分数据迁移到新块，其余数据保留在原块。

2. 枝节点分裂（Branch Node Split）：当索引的枝节点（连接叶子节点的中间层块）满载时，也会发生分裂，以容纳更多的分支。

3. 根节点分裂（Root Node Split）：虽然较为罕见，但当根节点需要更多空间来存储指向枝节点的指针时，根节点也可能分裂，这通常伴随着索引层次的增加。

1.3 对性能的影响

• 性能下降：索引分裂会导致额外的I/O操作，增加数据库的写操作负担，可能会暂时降低查询性能。
• 空间碎片：频繁的分裂会导致索引结构中出现碎片，影响索引扫描效率。
• 维护成本：索引分裂需要数据库自动维护，增加了系统资源的消耗。

二、索引分裂验证

实验环境： oracle 19c

2.1 基础环境准备

[oracle@db2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 26 16:21:21 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCLPDB1                       READ WRITE NO

SQL> alter session set container=ORCLPDB1;

Session altered.


SQL> create table APPS.TEST1(id number(20) not null,name varchar(20));
alter table APPS.TEST1 add constraint APPS.PK_ID primary key (id);

切换到pdb，创建一张带主键的表

2.2 查看表头信息

SQL> col owner for a15
SQL> col segment_name for a15
SQL> col segment_type for a15
SQL> set linesize 200
SQL> set pagesize 999
SQL>select t.owner,t.segment_name,t.segment_type,t.header_file,t.header_block,t.blocks,t.bytes 
  from dba_segments t where t.segment_name in ('TEST1','PK_ID');

OWNER           SEGMENT_NAME    SEGMENT_TYPE    HEADER_FILE HEADER_BLOCK     BLOCKS      BYTES
--------------- --------------- --------------- ----------- ------------ ---------- ----------
APPS             PK_ID           INDEX                     9       220640          8      65536
APPS             TEST1           TABLE                     9       220632          8      65536

2.3 查看当前表和索引的分配的区

SQL> select t.owner,t.segment_name,t.segment_type,t.file_id,t.block_id,t.blocks from dba_extents t where t.segment_name in ('TEST1','PK_ID');

OWNER           SEGMENT_NAME    SEGMENT_TYPE       FILE_ID   BLOCK_ID     BLOCKS
--------------- --------------- --------------- ---------- ---------- ----------
APPS             PK_ID         INDEX                    9     220640          8
APPS             TEST1       TABLE                    9     220632          8

表和索引都分配了1个区，每个区有8个块。

2.4 查看当前索引分裂的情况

SQL> col name for a25
SQL> select t.inst_id,t.name,t.value from gv$sysstat t where t.NAME like '%splits%' order by t.inst_id,t.name;

   INST_ID NAME                           VALUE
---------- ------------------------- ----------
         1 branch node splits                 0
         1 leaf node 90-10 splits             0
         1 leaf node splits                   0
         1 queue splits                       0
         1 root node splits                   0

由于当前数据库刚启动，所以没有块分裂。

2.5 跟踪块分裂

通过10224事件跟踪块分裂

oradebug setmypid;
alter session set events '10224 TRACE NAME CONTEXT FOREVER,LEVEL 10';
alter session set  tracefile_identifier="TEST1_10224";
insert into TEST1  select level,'aaa' from dual connect by level<50000;
commit;
oradebug event 10046 trace name context off;
oradebug tracefile_name
--输出
/opt/oracle/diag/rdbms/orclcdb2/ORCLCDB2/trace/ORCLCDB2_ora_80074_TEST1_10224.trc 

分析trace

--索引分裂的明细
[root@db2 trace]# grep 'splitting' ORCLCDB2_ora_80074_TEST1_10224.trc 
splitting leaf,dba 0x00435de1,time 16:22:18.578
splitting leaf,dba 0x00435de3,time 16:22:18.579
splitting leaf,dba 0x00435de4,time 16:22:18.580
.....
splitting leaf,dba 0x00492d8c,time 16:22:18.696
splitting leaf,dba 0x00492d8d,time 16:22:18.697

--索引分裂次数
[root@db2 trace]#  grep 'splitting' ORCLCDB2_ora_80074_TEST1_10224.trc|awk -F '[ ,]' '{print $4}'|uniq|wc -l
92

2.6 查看表和索引占用的块数

--索引分裂的明细
[root@db2 trace]# grep 'splitting' ORCLCDB2_ora_80074_TEST1_10224.trc 
splitting leaf,dba 0x00435de1,time 16:22:18.578
splitting leaf,dba 0x00435de3,time 16:22:18.579
splitting leaf,dba 0x00435de4,time 16:22:18.580
.....
splitting leaf,dba 0x00492d8c,time 16:22:18.696
splitting leaf,dba 0x00492d8d,time 16:22:18.697

--索引分裂次数
[root@db2 trace]#  grep 'splitting' ORCLCDB2_ora_80074_TEST1_10224.trc|awk -F '[ ,]' '{print $4}'|uniq|wc -l
92

可以看到表和索引都

2.7 查看录入数据之后表和索引的分配的区

SQL> select t.owner,t.segment_name,t.segment_type,t.file_id,t.block_id,t.blocks from dba_extents t where t.segment_name in ('TEST1','PK_ID');

OWNER           SEGMENT_NAME    SEGMENT_TYPE       FILE_ID   BLOCK_ID     BLOCKS
--------------- --------------- --------------- ---------- ---------- ----------
APPS             PK_ID         INDEX                    9     220640          8
APPS             PK_ID         INDEX                    9     220648          8
APPS             PK_ID         INDEX                    9     220664          8
APPS             PK_ID         INDEX                    9     601352          8
APPS             PK_ID         INDEX                    9     601368          8
APPS             PK_ID         INDEX                    9     601384          8
APPS             PK_ID         INDEX                    9     601400          8
APPS             PK_ID         INDEX                    9     601416          8
APPS             PK_ID         INDEX                    9     601432          8
APPS             PK_ID         INDEX                    9     601448          8
APPS             PK_ID         INDEX                    9     601464          8
APPS             PK_ID         INDEX                    9     601480          8
APPS             TEST1         TABLE                    9     220632          8
APPS             TEST1         TABLE                    9     220656          8
APPS             TEST1         TABLE                    9     601344          8
APPS             TEST1         TABLE                    9     601360          8
APPS             TEST1         TABLE                    9     601376          8
APPS             TEST1         TABLE                    9     601392          8
APPS             TEST1         TABLE                    9     601408          8
APPS             TEST1         TABLE                    9     601424          8
APPS             TEST1         TABLE                    9     601440          8
APPS             TEST1         TABLE                    9     601456          8
APPS             TEST1         TABLE                    9     601472          8
APPS             TEST1         TABLE                    9     601488          8

24 rows selected.

2.8 查看当前索引分裂的情况

SQL> select t.inst_id,t.name,t.value from gv$sysstat t where t.NAME like '%splits%' order by t.inst_id,t.name;

   INST_ID NAME                           VALUE
---------- ------------------------- ----------
         1 branch node splits                 0
         1 leaf node 90-10 splits            92
         1 leaf node splits                  92
         1 queue splits                       0
         1 root node splits                   0

观察到插入数据之后，产生了92次索引叶子块分裂，且分裂类型均为9-1单边分裂类型。

三、数据分析

结论：

• 从索引的分裂情况来看，在录入数据之前，索引PK_ID分配了一个区，这个区有8个块，但实际上只使用了一个块
• 在录入数据之后，索引的的分区有12个，共96个块，也就是说，索引最多扩展95(96-1)次。
• 从trace分析和v$sysstat视图可以确认，由于录入的数据是有序的，分裂了92次均为leaf node 90-10 splits类型。

四、思考

1. 出现严重的索引分裂会带来什么样的问题？又是如何解决？

2. 什么情况下会出现50-50 splits类型的索引分裂？

在下一篇文章中，我们再揭幕。

想了解更多 Oracle 相关的学习资料(技术文章和视频)，可以微信公众号或B站搜索《云贝教育》，免费获取。

想了解更多 Oracle 相关的学习资料(技术文章和视频)，可以微信公众号或B站搜索《云贝教育》，免费获取。

想了解更多 Oracle 相关的学习资料(技术文章和视频)，可以微信公众号或B站搜索《云贝教育》，免费获取。