网站首页 > 技术文章 正文
本文为云贝教育 刘峰 原创，请尊重知识产权，转发请注明出处，不接受任何抄袭、演绎和未经注明出处的转载。
一、索引分裂概述
Oracle数据库中的索引分裂是数据库维护和性能管理中的一个重要概念，特别是在处理大量数据插入、更新和删除操作时。索引分裂发生的原因、类型以及对数据库性能的影响如下：
1.1 索引分裂的原因
索引分裂主要发生在以下场景：
- 插入操作：当向已满或接近满的索引块中插入新记录时，如果该块没有足够的空间容纳新数据，索引就需要分裂来创造额外的空间。
- 更新操作：如果更新导致索引项的大小变化，以至于原块无法容纳，则可能触发索引块的分裂。
- 删除操作：虽然删除通常不会直接导致索引块分裂，但在某些情况下，删除后留下的空间碎片可能需要通过索引重组或合并来优化。
1.2 索引分裂的类型
索引分裂主要有三种基本类型：
- 叶节点分裂（Leaf Node Split）：这是最常见的类型，当索引叶子节点（存储实际索引值的块）没有足够的空间来容纳新的索引条目时，叶子节点会被分裂成两个或更多块。大多数情况下，分裂是90-10或50-50的比例，即原块的一部分数据迁移到新块，其余数据保留在原块。
- 枝节点分裂（Branch Node Split）：当索引的枝节点（连接叶子节点的中间层块）满载时，也会发生分裂，以容纳更多的分支。
- 根节点分裂（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 655362.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
922.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类型。
四、思考
- 出现严重的索引分裂会带来什么样的问题？又是如何解决？
- 什么情况下会出现50-50 splits类型的索引分裂？
在下一篇文章中，我们再揭幕。
想了解更多 Oracle 相关的学习资料(技术文章和视频)，可以微信公众号或B站搜索《云贝教育》，免费获取。
想了解更多 Oracle 相关的学习资料(技术文章和视频)，可以微信公众号或B站搜索《云贝教育》，免费获取。
想了解更多 Oracle 相关的学习资料(技术文章和视频)，可以微信公众号或B站搜索《云贝教育》，免费获取。
猜你喜欢
- 2024-11-10 详解Oracle数据库is null和is not null在索引上的区别
- 2024-11-10 Oracle中会产生索引抑制的几种情况
- 2024-11-10 Oracle 分区表之相关陷阱 oracle分区表的缺点
- 2024-11-10 玩dotNetCore及EF6失败还是聊下Oracle索引吧
- 2024-11-10 如何“破解”Oracle数据库性能测试过程中索引使用问题?
- 2024-11-10 SQL查询慢，是因为没用对索引 sql语句查询慢 从哪些方面分析
- 2024-11-10 在Oracle中，索引有什么优点和缺点?
- 2024-11-10 Oracle 数据库索引扫描方式 oracle数据库索引查询
- 2024-11-10 Oracle 分区索引类型简述 oracle分区表索引是否失效
- 2024-11-10 Oracle 索引是一种单独的物理结构
欢迎 你 发表评论:
- 616℃几个Oracle空值处理函数 oracle处理null值的函数
- 609℃Oracle分析函数之Lag和Lead()使用
- 596℃0497-如何将Kerberos的CDH6.1从Oracle JDK 1.8迁移至OpenJDK 1.8
- 593℃Oracle数据库的单、多行函数 oracle执行多个sql语句
- 590℃Oracle 12c PDB迁移(一) oracle迁移到oceanbase
- 581℃【数据统计分析】详解Oracle分组函数之CUBE
- 570℃最佳实践 | 提效 47 倍，制造业生产 Oracle 迁移替换
- 560℃Oracle有哪些常见的函数? oracle中常用的函数
- 最近发表
-
- PageHelper - 最方便的 MyBatis 分页插件
- 面试二:pagehelper是怎么实现分页的，
- MyBatis如何实现分页查询?（mybatis-plus分页查询）
- SpringBoot 各种分页查询方式详解(全网最全)
- 如何在Linux上运行exe文件，怎么用linux运行windows软件
- 快速了解hive（快速了解美国50个州）
- Python 中的 pyodbc 库（pydbclib）
- Linux搭建Weblogic集群（linux weblogic部署项目步骤）
- 「DM专栏」DMDSC共享集群之部署(一)——共享存储配置
- 故障分析 | MySQL 派生表优化（mysql pipe）
- 标签列表
-
- 前端设计模式 (75)
- 前端性能优化 (51)
- 前端模板 (66)
- 前端跨域 (52)
- 前端缓存 (63)
- 前端aes加密 (58)
- 前端脚手架 (56)
- 前端md5加密 (54)
- 前端路由 (61)
- 前端数组 (73)
- 前端js面试题 (50)
- 前端定时器 (59)
- 前端获取当前时间 (50)
- Oracle RAC (76)
- oracle恢复 (77)
- oracle 删除表 (52)
- oracle 用户名 (80)
- oracle 工具 (55)
- oracle 内存 (55)
- oracle 导出表 (62)
- oracle约束 (54)
- oracle 中文 (51)
- oracle链接 (54)
- oracle的函数 (58)
- 前端调试 (52)
本文暂时没有评论，来添加一个吧(●'◡'●)