Index的原理

最新推荐文章于 2022-01-23 17:57:23 发布

BaldwinSu

最新推荐文章于 2022-01-23 17:57:23 发布

阅读量516

点赞数

分类专栏： warehouse_11gR2 文章标签： Oracle Index B树

本文链接：https://blog.csdn.net/sudahai102448567/article/details/114890935

版权

warehouse_11gR2 专栏收录该内容

28 篇文章 1 订阅

订阅专栏

本文章为网络笔记，看了warehouse老师的视频受益匪浅，更是感觉自己技术太过初级，特写了本笔记，方便以后反复学习！
如有任何不妥，请发邮件至102448567@qq.com删除文章！
关于warehouse：
http://blog.itpub.net/19602/viewspace-1059211/
11gR2视频第四版 8_03_index的原理
1.index
bitmap index（位图索引）
b-tree index（balance（平衡） tree）不是二叉树，特点是根节点（root）很少，叶子节点（leaf）很多，所以通俗的讲这颗树不高所以查找的很快，数据最终都是放在叶子节点上
在这里插入图片描述 root下不止有一个branches，可以有多个，branches下面还有branches那时候的数据很大了，差不多就上亿了

1.为啥要建索引？
2.索引为什么会提高查询效率？
3.是不是有了索引之后就一定会提高查询效率？

索引可以提高查询效率，但是它本身也是一个segment，需要大量的存储空间，做DML操作的时候索引也需要维护
实验：

SQL> create table tt2 tablespace users as select * from dba_objects;

Table created.
SQL> insert into tt2 select * from tt2;

15358 rows created.

SQL> insert into tt2 select * from tt2;

30716 rows created.

SQL> insert into tt2 select * from tt2;

61432 rows created.

SQL> insert into tt2 select * from tt2;

122864 rows created.

SQL> select count(1) from tt2;

  COUNT(1)
----------
    245728

建一个索引，索引是一个segment，所以他也有metadata id和data id

SQL> create index idx_tt2 on tt2(object_id) tablespace users;

Index created.

查看一下这个index的metadata id和data id，Oracle有一条内部命令可以分析b-tree索引的结构，这个命令就用到了object_id（元数据id）

SQL> select object_id,data_object_id from dba_objects where object_name = 'IDX_TT2';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     15762	    15762

这个内容就放在了为这个session服务的process的trace文件里

SQL> alter session set events 'immediate trace name treedump level 15762';

Session altered.

当前session sid

SQL> select distinct sid from v$mystat;

       SID
----------
       136

找出为这个session服务的process的操作系统的进程号

SELECT *
       FROM v$process
      WHERE ADDR = (SELECT paddr FROM v$session WHERE sid = '136')

在这里插入图片描述
后台进程dump下来的文件就是在background_dump_dest里

SQL> show parameter dump;

NAME				     	TYPE	 	VALUE
-------------------------- ----------- ------------------------------
background_core_dump		string	 	partial
background_dump_dest		string	 	/u01/app/oracle/diag/rdbms/buy/sales/trace
core_dump_dest			    string	 	/u01/app/oracle/diag/rdbms/buy/sales/cdump
max_dump_file_size		    string	 	unlimited
shadow_core_dump		    string	 	partial
user_dump_dest			    string	 	/u01/app/oracle/diag/rdbms/buy/sales/trace

就是这个sales_ora_6598.trc

[oracle@oracle /u01/app/oracle/diag/rdbms/buy/sales/trace]$ ls -lrt
-rw-r----- 1 oracle oinstall    865 Mar 17 10:40 sales_mman_5417.trc
-rw-r----- 1 oracle oinstall     60 Mar 17 10:40 sales_ora_5451.trm
-rw-r----- 1 oracle oinstall    993 Mar 17 10:40 sales_ora_5451.trc
-rw-r----- 1 oracle oinstall     69 Mar 17 10:40 sales_j000_5473.trm
-rw-r----- 1 oracle oinstall    981 Mar 17 10:40 sales_j000_5473.trc
-rw-r----- 1 oracle oinstall     80 Mar 17 10:41 sales_dbrm_5413.trm
-rw-r----- 1 oracle oinstall   1282 Mar 17 10:41 sales_dbrm_5413.trc
-rw-r----- 1 oracle oinstall 175003 Mar 17 12:30 alert_sales.log
-rw-r----- 1 oracle oinstall     71 Mar 17 14:23 sales_ora_6598.trm
-rw-r----- 1 oracle oinstall  29781 Mar 17 14:23 sales_ora_6598.trc
-rw-r----- 1 oracle oinstall    625 Mar 17 14:37 sales_mmon_5430.trm
-rw-r----- 1 oracle oinstall   6666 Mar 17 14:37 sales_mmon_5430.trc

trace文件的一部分

Trace file /u01/app/oracle/diag/rdbms/buy/sales/trace/sales_ora_6598.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name:    Linux
Node name:      oracle
Release:        3.10.0-862.el7.x86_64
Version:        #1 SMP Fri Apr 20 16:44:24 UTC 2018
Machine:        x86_64
VM name:        VMWare Version: 6
Instance name: sales
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 6598, image: oracle@oracle (TNS V1-V3)


*** 2021-03-17 14:23:36.913
*** SESSION ID:(136.23) 2021-03-17 14:23:36.913
*** CLIENT ID:() 2021-03-17 14:23:36.913
*** SERVICE NAME:(SYS$USERS) 2021-03-17 14:23:36.913
*** MODULE NAME:(sqlplus@oracle (TNS V1-V3)) 2021-03-17 14:23:36.913
*** ACTION NAME:() 2021-03-17 14:23:36.913
##下面这个就是已经生成index了，这个branch可以看成是一个特殊的root，因为数据量比较少所以branch下面就是leaf
----- begin tree dump
branch: 0x100010b 16777483 (0: nrow: 526, level: 1)
   leaf: 0x100010c 16777484 (-1: nrow: 512 rrow: 512)
   leaf: 0x100010d 16777485 (0: nrow: 512 rrow: 512)
   leaf: 0x100010e 16777486 (1: nrow: 512 rrow: 512)
   leaf: 0x100010f 16777487 (2: nrow: 481 rrow: 481)

再插入一些数据，索引对应的segment也是会增大的

SQL> insert into tt2 select * from tt2;

245728 rows created.

SQL> commit;

Commit complete.

SQL> select count(1) from tt2;

  COUNT(1)
----------
    491456
    
SQL> alter session set events 'immediate trace name treedump level 15762';

Session altered.

重新打开这个文件sales_ora_6598.trc让他加载一下，注意这个begin tree dump上面的时间是不是对应执行alter session set events的时间，因为这个文件是累加的防止上面的文件干扰，还是只截取一部分

*** 2021-03-17 14:49:45.710
----- begin tree dump
branch: 0x100010b 16777483 (0: nrow: 2, level: 2)	##这个branch其实就是可以看成是root
   branch: 0x1001073 16781427 (-1: nrow: 482, level: 1)	##这里有一个branch（第一个）
      leaf: 0x100010c 16777484 (-1: nrow: 512 rrow: 512)
      leaf: 0x1001064 16781412 (0: nrow: 512 rrow: 512)
      leaf: 0x100010d 16777485 (1: nrow: 512 rrow: 512)
      leaf: 0x1001068 16781416 (2: nrow: 512 rrow: 512)
      leaf: 0x100010e 16777486 (3: nrow: 512 rrow: 512)
      leaf: 0x1001045 16781381 (4: nrow: 512 rrow: 512)
      leaf: 0x100010f 16777487 (5: nrow: 480 rrow: 480)
      leaf: 0x1001055 16781397 (6: nrow: 481 rrow: 481)
      leaf: 0x1000110 16777488 (7: nrow: 479 rrow: 479)
      leaf: 0x1001059 16781401 (8: nrow: 463 rrow: 463)
      leaf: 0x1000111 16777489 (9: nrow: 482 rrow: 482)

一共是有两个branch，然后就是leaf了，所以balance tree的特点就是扁平的

      leaf: 0x1000efd 16781053 (479: nrow: 478 rrow: 478)
      leaf: 0x1001303 16782083 (480: nrow: 480 rrow: 480)
   branch: 0x1001077 16781431 (0: nrow: 570, level: 1)	##这里也是一个branch（第二个）
      leaf: 0x1000efe 16781054 (-1: nrow: 480 rrow: 480)
      leaf: 0x1001307 16782087 (0: nrow: 462 rrow: 462)
      leaf: 0x1000eff 16781055 (1: nrow: 482 rrow: 482)
      leaf: 0x1001317 16782103 (2: nrow: 477 rrow: 477)
      leaf: 0x1000f02 16781058 (3: nrow: 499 rrow: 499)
      leaf: 0x100131b 16782107 (4: nrow: 459 rrow: 459)
      leaf: 0x1000f03 16781059 (5: nrow: 481 rrow: 481)
      leaf: 0x1001250 16781904 (6: nrow: 477 rrow: 477)

下面分析叶子节点（leaf）

leaf: 0x100010c --16进制数、这部分是数据块的地址，地址是由数据文件号和块编号组成的 16777484 --16进制数对应的十进制数 (-1: nrow: 512 rrow: 512)

使用Oracle函数转化一下16进制数验证一下

SQL> select to_number('100010c','xxxxxxx') from dual;

TO_NUMBER('100010C','XXXXXXX')
------------------------------
		      16777484

知道了十进制数之后Oracle提供了包，包中有函数可以分解成是哪个数据文件上的哪个块

SQL> select dbms_utility.data_block_address_file(16777484) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(16777484)
----------------------------------------------
					     4

SQL> select dbms_utility.data_block_address_block(16777484) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(16777484)
-----------------------------------------------
					    268

知道这个index中的leaf是在哪个块了就把这个块dump下来

SQL> alter system dump datafile 4 block 268;

System altered.

SQL> select distinct sid from v$mystat;

       SID
----------
       137
       
SQL> select spid from v$process where addr = (select paddr from v$session where sid = '137');

SPID
------------------------
4691

SQL> show parameter dump;

NAME				     TYPE	 VALUE
------------------------ ------- ------------------------------
background_core_dump	 string	 partial
background_dump_dest	 string	 /u01/app/oracle/diag/rdbms/buy/sales/trace
core_dump_dest			 string  /u01/app/oracle/diag/rdbms/buy/sales/cdump
max_dump_file_size		 string	 unlimited
shadow_core_dump		 string	 partial
user_dump_dest			 string	 /u01/app/oracle/diag/rdbms/buy/sales/trace

[oracle@oracle ~]$ cd /u01/app/oracle/diag/rdbms/buy/sales/trace
[oracle@oracle /u01/app/oracle/diag/rdbms/buy/sales/trace]$ ls -lrt
-rw-r----- 1 oracle oinstall  92610 Apr 20 11:33 sales_ora_4691.trc

简单介绍dump块中的内容
从这里开始dump块

Start dump data blocks tsn: 4 file#:4 minblk 268 maxblk 268
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4 rdba=16777484
Block dump from disk:
buffer tsn: 4 rdba: 0x0100010c (4/268)
scn: 0x0000.0005f8e7 seq: 0x01 flg: 0x06 tail: 0xf8e70601
frmt: 0x02 chkval: 0x85fa type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F2B7FE15A00 to 0x00007F2B7FE17A00

下面都是16进制数，看不懂也不需要看他

Dump of memory from 0x00007F2B7FE15A00 to 0x00007F2B7FE17A00
7F2B7FE15A00 0000A206 0100010C 0005F8E7 06010000  [................]
7F2B7FE15A10 000085FA 00000002 00003D92 0005E057  [.........=..W...]
7F2B7FE15A20 00000000 00320002 01000108 00130002  [......2.........]
7F2B7FE15A30 0000016D 00C00873 000100CD 00006001  [m...s........`..]
7F2B7FE15A40 0005F629 001E0003 0000017A 00C00A6C  [).......z...l...]
7F2B7FE15A50 001B006B 00002100 0005F8E7 00000000  [k....!..........]
7F2B7FE15A60 00000000 02800100 00000001 04240200  [..............$.]
7F2B7FE15A70 033C0760 00000000 01001064 00000000  [`.<.....d.......]
7F2B7FE15A80 00000000 00001F60 120411F8 121C1210  [....`...........]
7F2B7FE15A90 12341228 124C1240 12641258 127C1270  [(.4.@.L.X.d.p.|.]

从这里开始就是index segment真正记录的东西，注意看index segment也是二维结构，两个字段（col1、col2）也就是object_id和rowid

header address 139824805796452=0x7f2b7fe15a64
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 1
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 512
kdxcofbo 1060=0x424
kdxcofeo 1888=0x760
kdxcoavs 828
kdxlespl 0
kdxlende 0
kdxlenxt 16781412=0x1001064
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[4600] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 03  --第一行
col 1; len 6; (6):  01 00 00 8b 00 30
row#1[4612] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 00 01 e9 00 30
row#2[4624] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 00 02 ed 00 01
row#3[4636] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 00 03 d3 00 1c

我们创建index的时候是建在了object_id上，所以就会按照object_id进行排序，但是object_id是有重复的，按照object_id排序之后就按rowid进行排序了，如果object_id是唯一的就不用rowid
我们来验证col1记录的是什么

col 0; len 2; (2):  c1 03  --第一行

下面是表中的数据
在这里插入图片描述
以object_id排序了，第一行是2，看一下2的16进制数是多少

SQL> select dump(2,16) from dual;

DUMP(2,16)
-----------------
Typ=2 Len=2: c1,3
--Typ2表示number类型，Len2表示占用两个字节，c1,3中间的0省略了，也就是说c1,3就是0

也可以把c1 03转成number类型

SQL> select utl_raw.cast_to_number('c103') from dual;

UTL_RAW.CAST_TO_NUMBER('C103')
------------------------------
			     2

tt2表中一共有32条object_id为2的，看dump下来的文件正好是32行c1 03

SQL> select count(1) from tt2 where object_id = 2;

  COUNT(1)
----------
	32

row#0[4600] flag: ------, lock: 0, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 00 00 8b 00 30
--中间省略了....
row#31[1960] flag: ------, lock: 2, len=12
col 0; len 2; (2):  c1 03
col 1; len 6; (6):  01 00 1e ce 00 19

上面的实验就是说明了你在哪个字段上创建了索引，他就把这个字段的值保存到了B树索引中的leaf节点上了，而且这个B树的叶子节点中的值是排好序的，最小的在最左边，最大的在最右边
下面看col 1存放的是什么，他是16进制数

col 1; len 6; (6):  01 00 00 8b 00 30

把第一行的rowid拿出来AAAD2RAAEAAAACLAAw，这是64进制数
在这里插入图片描述
要先把十六进制数转换成二进制数（不足八位的前边补0）

01 00 00 8b 00 30

转换成

00000001 00000000 00000000 10001011 00000000 00110000

rowid：AAAD2RAAEAAAACLAAw

col1二进制数的前十位就是表示的相对文件编号对应rowid的7-9位（AAE）
再把前十位转换成十进制数0000000100的十进制数是4
col1二进制数的11-32位表示的是块编号对应rowid的第10-15位（AAAACL）
11-32位
000000 00000000 10001011的十进制数是139
再看rowid的结果，查出来的块编号也是139

SQL> select dbms_rowid.rowid_block_number('AAAD2RAAEAAAACLAAw') from dual;

DBMS_ROWID.ROWID_BLOCK_NUMBER('AAAD2RAAEAAAACLAAW')
---------------------------------------------------
						139

col1二进制数的33-最后表示的就是数据在这个数据块的哪一行
00000000 00110000十进制数是48
看rowid的计算结果，同样也是48

SQL> select dbms_rowid.rowid_row_number('AAAD2RAAEAAAACLAAw') from dual;

DBMS_ROWID.ROWID_ROW_NUMBER('AAAD2RAAEAAAACLAAW')
-------------------------------------------------
					       48

综上所述，我们dump下来的index的leaf中col1记录的就是rowid的后三部分（rowid一共有四部分组成），数据文件位置、在哪个块上、在数据块的哪一行上
rowid的第一个部分是数据段id，这个没有保存在leaf中因为不需要为了节省空间，在元数据中能够找到这个index的关联信息所以没有必要在leaf中保存
在这里插入图片描述
leaf中的col1就相当于是书的目录，col1就是具体的页码，leaf存放在哪个字段上建索引那个索引的值和这个值所在行的rowid，这个就是索引的原理

BaldwinSu

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Index的原理

本文章为网络笔记，看了warehouse老师的视频受益匪浅，更是感觉自己技术太过初级，特写了本笔记，方便以后反复学习！如有任何不妥，请发邮件至102448567@qq.com删除文章！关于warehouse：http://blog.itpub.net/19602/viewspace-1059211/11gR2视频第四版 8_03_index的原理1.indexbitmap index（位图索引）b-tree index（balance（平衡） tree）不是二叉树，特点是根节点（root）很少
复制链接

扫一扫