云贝教育 |【技术文章】oracle索引分裂
sinye56 2024-10-11 17:49 6 浏览 0 评论
本文为云贝教育 刘峰 原创，请尊重知识产权，转发请注明出处，不接受任何抄袭、演绎和未经注明出处的转载。
一、索引分裂概述
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站搜索《云贝教育》，免费获取。
相关推荐
- CTO偷偷传我的系统性能优化十大绝招(万字干货)
-
上篇引言：取与舍软件设计开发某种意义上是“取”与“舍”的艺术。关于性能方面，就像建筑设计成抗震9度需要额外的成本一样，高性能软件系统也意味着更高的实现成本，有时候与其他质量属性甚至会冲突，比如安全性、...
- 提升效率!VMware虚拟机性能优化十大实用技巧
-
我40岁，干跨境婚恋中介的。为服务各国用户，常得弄英语、日语、俄语系统环境，VMware虚拟机帮了不少忙。用久了发现优化下性能，效率能更高。今儿就来聊聊优化技巧和同类软件。一、VMware虚拟...
- 低延迟场景下的性能优化实践
-
本文摘录自「全球C++及系统软件技术大会」ScottMeyers曾说到过，如果你不在乎性能，为什么要在C++这里，而不去隔壁的Pythonroom呢？今天我们就从“低延迟的概述”、“低延迟系...
- Linux性能调优之内存负载调优的一些笔记
-
写在前面整理一些Linux内存调优的笔记，分享给小伙伴博文没有涉及的Demo,理论方法偏多，可以用作内存调优入门博文内容涉及:Linux内存管理的基本理论寻找内存泄露的进程内存交换空间调优不同方式的...
- 优化性能套路:带你战胜这只后段程序员的拦路虎
-
来源｜极客时间《卖桃者说》作者｜池建强编辑｜成敏你好，这里是卖桃者说。今天给大家推荐一篇文章，来自倪朋飞老师的专栏《Linux性能优化实战》，文章主要讲的是优化性能的套路，这几乎是每个后端程序员...
- SK海力士CXL优化解决方案已成功搭载于Linux:带宽提升30%，性能提升12%以上
-
SK海力士宣布，已将用于优化CXL（ComputeExpressLink）存储器运行的自研软件异构存储器软件开发套件（HMSDK）中主要功能成功搭载于全球最大的开源操作系统Linux上，不但提升了...
- Linux内核优化:提升系统性能的秘诀
-
Linux内核优化：提升系统性能的艺术在深入Linux内核优化的世界之前，让我们先来理解一下内核优化的重要性。Linux内核是操作系统的核心，负责管理系统资源和控制硬件。一个经过精心优化的内核可以显著...
- Linux系统性能优化:七个实战经验
-
Linux系统的性能是指操作系统完成任务的有效性、稳定性和响应速度。Linux系统管理员可能经常会遇到系统不稳定、响应速度慢等问题，例如在Linux上搭建了一个web服务，经常出现网页无法打开、打开速...
- 腾讯面试:linux内存性能优化总结
-
【1】内存映射Linux内核给每个进程都提供了一个独立且连续的虚拟地址空间，以便进程可以方便地访问虚拟内存；虚拟地址空间的内部又被分为内核空间和用户空间两部分，不同字长的处理器，地址空间的范围也不同...
- Linux文件系统性能调优《参数优化详解》
-
由于各种的I/O负载情形各异,Linux系统中文件系统的缺省配置一般来说都比较中庸,强调普遍适用性。然而在特定应用下,这种配置往往在I/O性能方面不能达到最优。因此,如果应用对I/O性能要求较高,除...
- Nginx 性能优化(吐血总结)
-
一、性能优化考虑点当我需要进行性能优化时，说明我们服务器无法满足日益增长的业务。性能优化是一个比较大的课题，需要从以下几个方面进行探讨当前系统结构瓶颈了解业务模式性能与安全1、当前系统结构瓶颈首先需要...
- Linux问题分析与性能优化
-
排查顺序整体情况：top/htop/atop命令查看进程/线程、CPU、内存使用情况，CPU使用情况；dstat2查看CPU、磁盘IO、网络IO、换页、中断、切换，系统I/O状态;vmstat2查...
- 大神级产品:手机装 Linux 运行 Docker 如此简单
-
本内容来源于@什么值得买APP，观点仅代表作者本人｜作者：灵昱Termux作为一个强大的Android终端模拟器，能够运行多种Linux环境。然而，直接在Termux上运行Docker并不可行，需要...
- 新手必须掌握的Linux命令
-
Shell就是终端程序的统称，它充当了人与内核（硬件）之间的翻译官，用户把一些命令“告诉”终端程序，它就会调用相应的程序服务去完成某些工作。现在包括红帽系统在内的许多主流Linux系统默认使用的终端是...
- Linux 系统常用的 30 个系统环境变量全解析
-
在Linux系统中，环境变量起着至关重要的作用，它们犹如隐藏在系统背后的“魔法指令”，掌控着诸多程序的运行路径、配置信息等关键要素。尤其在shell脚本编写时，巧妙运用环境变量，能让脚本如虎...
欢迎 你 发表评论:
- 一周热门
- 最近发表
- 标签列表
-
- oracle忘记用户名密码 (59)
- oracle11gr2安装教程 (55)
- mybatis调用oracle存储过程 (67)
- oracle spool的用法 (57)
- oracle asm 磁盘管理 (67)
- 前端 设计模式 (64)
- 前端面试vue (56)
- linux格式化 (55)
- linux图形界面 (62)
- linux文件压缩 (75)
- Linux设置权限 (53)
- linux服务器配置 (62)
- mysql安装linux (71)
- linux启动命令 (59)
- 查看linux磁盘 (72)
- linux用户组 (74)
- linux多线程 (70)
- linux设备驱动 (53)
- linux自启动 (59)
- linux网络命令 (55)
- linux传文件 (60)
- linux打包文件 (58)
- linux查看数据库 (61)
- linux获取ip (64)
- linux进程通信 (63)