索引块分裂测试.txt - 墨天轮文档

索引块分裂测试.txt

杨卓

398

6页

6次

2022-02-11

5墨值下载

索引块分裂测试

测试目的

制造一个索引存储叶子块都满的情况下，插入一个中间值的数据，查询等待事件？

索引是有序的，但是索引存储的叶子块都已经充满的情况下，查询一个中间值的数据，索引叶块如何分裂呢？

参考 LOB

https://blog.csdn.net/zhaoyangjian724/article/details/40261677

https://blog.csdn.net/trsenzhang/article/details/41249989

http://blog.itpub.net/9240380/viewspace-756476/

http://www.askmaclean.com/archives/index-split.html

索引块分裂原理解析

场景：对某个表进行 Insert/update 操作时，索引块中无空闲空间，需要进行块分裂 splits，容纳新的

记录,并发 Insert 操作将产生 Mode 4 TX lock，直到之前的 session 做完 index blocks splits 为

止

Index block splits 概念：

索引块分三层结构：root block/ branch block / leaf block

root block 索引最大的管理层

branch block 分枝，中间层，通过此键值寻找 B-TREE 的 leaf block

leaf block 存放数据的索引块，数据实际存放在 Leaf block 中

Index block split 过程，在新的记录插入 index block 时，发现没有空闲空间：

split 会分配新块

复制一定的百分比 rows 到新的 buffer block

添加新的 buffer block 到对应的索引结构中并且提交操作

Index block split 类型：

select sid,name,a.value,b.value from v$mystat a,v$sysstat b where

a.statistic#=b.statistic# and name like '%split%'

SID NAME VALUE VALUE

------ ------------------------------ ---------- ----------

154 leaf node splits 0 1709

154 leaf node 90-10 splits 0 971

154 branch node splits 0 3

154 root node splits 0 1

154 queue splits 0 0

--Leaf node splits 索引叶块分裂 55 分裂，一个块分为两个块，数据一半对一半，往往发生在数据插

入的值对索引的键值来说中间值

--leaf node 90-10 splits 索引块分裂的模式：俗称 91 分裂，此分裂往往发生在 B 树索引类型（有

序），在 Insert 操作时，插入的数据是最大值，且该数据无法插入索引对应层级块中的最后一个块，需要

split 分裂新的索引块时，将之前块中的数据拷贝 1% rows，插入新的索引块中，且新的 insert 插入新的

块中，完成分裂模式（由于拷贝的数据量少，与 55 分裂比较，因此我们希望索引进行 91 分裂）

--branch node splits 分枝节点块分裂，当中间管理层数据已经达到块的上限值，底层叶快需要写入

结构数据，会对分枝块进行分裂，模式只有一种 55 分裂

--root node splits 管理层分裂，当原最高管理层，出现块满现象，需要新建上层

索引的高度 height 视乎索引中记录的多少、Leaf Block 的数量而定，一般 Height 为 3~4；举例来

说当一个索引的高度为 4，但是其中包含大量删除的记录，那么通过索引 rebuild 往往可以降低其高度，例

如从 4 降低到 3。假设该索引再次插入了大量的数据，造成 leaf block 不断分裂，最终导致 root block

再次分裂，索引高度从 3 再次上升到 4，在这个 root block split 的过程中可能短期内阻塞索引的 DML

维护，导致进程等待” enqueue TX: index contention” ，这在 OLTP 环境中是很常见的问题，所以一

般不推荐在 OLTP 环境中 rebuild index，虽然 rebuild index 能够减少索引碎片回收空间，但由于

rebuild index 可能导致索引高度降级，所以对于 OLTP 环境的索引 rebuild 需要慎重

在 split 过程中寻找可复用的 free block 的过程称之为 failed probes on index block

reclamation，在正常的情况下这种找寻可复用块的过程是很快的，但是如果恰好遇到物理读缓慢或者全

局的数据块争用时，该过程可能变得很慢，这将直接导致 split 变慢，进而导致大量 INSERT 进程长时间等

待 enq: TX – index contention

--测试

--创建测试表

CREATE TABLE TEST(ID INT,NAME VARCHAR2(50),CREATED DATE);

--创建存储过程

BEGIN

FOR I IN 10000..20000 LOOP

INSERT INTO TEST

VALUES(I,RPAD(I,50,'X'),SYSDATE);

END LOOP;

commit;

END;

--查询数据行数量

SQL> select count(*) from test;

COUNT(*)

----------

10001

--创建测试索引，预留空间 0%

CREATE INDEX IDX_TEST_01 ON TEST(ID,NAME) PCTFREE 0;

ANALYZE INDEX IDX_TEST_01 VALIDATE STRUCTURE;

--查询索引信息：索引非分区索引，高度 3 层，索引总块数量 88，叶块数量 85，一个 root 块，两个

branch 块数量，使用率 98%

SELECT

name,PARTITION_NAME,HEIGHT,BLOCKS,LF_ROWS,LF_BLKS,PCT_USED,DEL_LF_ROWS,DEL_LF_RO

WS_LEN FROM INDEX_STATS;

NAME PARTI HEIGHT BLOCKS LF_ROWS LF_BLKS

PCT_USED DEL_LF_ROWS DEL_LF_ROWS_LEN

-------------------- ----- ---------- ---------- ---------- ----------

---------- ----------- ---------------

IDX_TEST_01 2 88 10001 85

98 0 0

--插入新的数据

INSERT INTO TEST VALUES(20001,RPAD(20001,50,'X'),SYSDATE);

SQL> commit;

--分析再次查询

ANALYZE INDEX IDX_TEST_01 VALIDATE STRUCTURE;

SELECT

索引块分裂测试

测试目的

制造一个索引存储叶子块都满的情况下，插入一个中间值的数据，查询等待事件？

索引是有序的，但是索引存储的叶子块都已经充满的情况下，查询一个中间值的数据，索引叶块如何分裂呢？

参考 LOB

https://blog.csdn.net/zhaoyangjian724/article/details/40261677

https://blog.csdn.net/trsenzhang/article/details/41249989

http://blog.itpub.net/9240380/viewspace-756476/

http://www.askmaclean.com/archives/index-split.html

索引块分裂原理解析

场景：对某个表进行 Insert/update 操作时，索引块中无空闲空间，需要进行块分裂 splits，容纳新的

记录,并发 Insert 操作将产生 Mode 4 TX lock，直到之前的 session 做完 index blocks splits 为

止

Index block splits 概念：

索引块分三层结构：root block/ branch block / leaf block

root block 索引最大的管理层

branch block 分枝，中间层，通过此键值寻找 B-TREE 的 leaf block

leaf block 存放数据的索引块，数据实际存放在 Leaf block 中

Index block split 过程，在新的记录插入 index block 时，发现没有空闲空间：

split 会分配新块

复制一定的百分比 rows 到新的 buffer block

添加新的 buffer block 到对应的索引结构中并且提交操作

Index block split 类型：

select sid,name,a.value,b.value from v$mystat a,v$sysstat b where

a.statistic#=b.statistic# and name like '%split%'

SID NAME VALUE VALUE

------ ------------------------------ ---------- ----------

154 leaf node splits 0 1709

154 leaf node 90-10 splits 0 971

154 branch node splits 0 3

154 root node splits 0 1

154 queue splits 0 0

--Leaf node splits 索引叶块分裂 55 分裂，一个块分为两个块，数据一半对一半，往往发生在数据插

入的值对索引的键值来说中间值

--leaf node 90-10 splits 索引块分裂的模式：俗称 91 分裂，此分裂往往发生在 B 树索引类型（有

序），在 Insert 操作时，插入的数据是最大值，且该数据无法插入索引对应层级块中的最后一个块，需要

split 分裂新的索引块时，将之前块中的数据拷贝 1% rows，插入新的索引块中，且新的 insert 插入新的

块中，完成分裂模式（由于拷贝的数据量少，与 55 分裂比较，因此我们希望索引进行 91 分裂）

--branch node splits 分枝节点块分裂，当中间管理层数据已经达到块的上限值，底层叶快需要写入

结构数据，会对分枝块进行分裂，模式只有一种 55 分裂

--root node splits 管理层分裂，当原最高管理层，出现块满现象，需要新建上层

索引的高度 height 视乎索引中记录的多少、Leaf Block 的数量而定，一般 Height 为 3~4；举例来

说当一个索引的高度为 4，但是其中包含大量删除的记录，那么通过索引 rebuild 往往可以降低其高度，例

如从 4 降低到 3。假设该索引再次插入了大量的数据，造成 leaf block 不断分裂，最终导致 root block

再次分裂，索引高度从 3 再次上升到 4，在这个 root block split 的过程中可能短期内阻塞索引的 DML

维护，导致进程等待” enqueue TX: index contention” ，这在 OLTP 环境中是很常见的问题，所以一

般不推荐在 OLTP 环境中 rebuild index，虽然 rebuild index 能够减少索引碎片回收空间，但由于

rebuild index 可能导致索引高度降级，所以对于 OLTP 环境的索引 rebuild 需要慎重

在 split 过程中寻找可复用的 free block 的过程称之为 failed probes on index block

reclamation，在正常的情况下这种找寻可复用块的过程是很快的，但是如果恰好遇到物理读缓慢或者全

局的数据块争用时，该过程可能变得很慢，这将直接导致 split 变慢，进而导致大量 INSERT 进程长时间等

待 enq: TX – index contention

--测试

--创建测试表

CREATE TABLE TEST(ID INT,NAME VARCHAR2(50),CREATED DATE);

--创建存储过程

BEGIN

FOR I IN 10000..20000 LOOP

INSERT INTO TEST

VALUES(I,RPAD(I,50,'X'),SYSDATE);

END LOOP;

commit;

END;

--查询数据行数量

SQL> select count(*) from test;

COUNT(*)

----------

10001

--创建测试索引，预留空间 0%

CREATE INDEX IDX_TEST_01 ON TEST(ID,NAME) PCTFREE 0;

ANALYZE INDEX IDX_TEST_01 VALIDATE STRUCTURE;

--查询索引信息：索引非分区索引，高度 3 层，索引总块数量 88，叶块数量 85，一个 root 块，两个

branch 块数量，使用率 98%

SELECT

name,PARTITION_NAME,HEIGHT,BLOCKS,LF_ROWS,LF_BLKS,PCT_USED,DEL_LF_ROWS,DEL_LF_RO

WS_LEN FROM INDEX_STATS;

NAME PARTI HEIGHT BLOCKS LF_ROWS LF_BLKS

PCT_USED DEL_LF_ROWS DEL_LF_ROWS_LEN

-------------------- ----- ---------- ---------- ---------- ----------

---------- ----------- ---------------

IDX_TEST_01 2 88 10001 85

98 0 0

--插入新的数据

INSERT INTO TEST VALUES(20001,RPAD(20001,50,'X'),SYSDATE);

SQL> commit;

--分析再次查询

ANALYZE INDEX IDX_TEST_01 VALIDATE STRUCTURE;

SELECT

of 6

5墨值下载

oracle

关注

评论