innodb文件分析工具innodb_ruby

innodb文件分析工具innodb_ruby

1. 前言

  一提到innodb大多数都知道它是个B+Tree存储结构，但是对于想深入了解innodb存储引擎的小伙伴来说远远不够，我也是其中之一。于是带着疑问和思考上路来进行学习和分析，既然是数据库存储引擎，就需要有一个比较可视化的工具来帮助我们理解和拆解复杂的存储文件信息等，最终我找到了innodb_ruby一个基于ruby语言开发且开源的innodb存储引擎解析工具，下面我们通过它来一层层揭开神秘面纱探究清楚innodb底层的实现原理。我们知道mysql中有诸如参数配置、日志、表结构、存储引擎等很多文件，这里主要围绕innodb存储引擎相关的文件进行展开和剖析。

2. MySQL基础知识

2.1 表结构定义

  首先，我们需要了解MySQL的存储引擎是插件式的，数据的存储是根据表来进行组织的，每个表都会有与之对应的文件。但不论表采用何种存储引擎，MySQL都会有一个frm为后缀的文件，这个文件就是表结构定义。

frm还用来存放视图的定义，当创建了一个视图的时候，会创建形如view.frm文件，由于它是文本文件，可以直接使用cat 命令查看定义情况

2.2 InnoDB存储引擎文件

  一般来说，数据都会拆分较为合理的分层，以方便组织和维护数据存储及使用，对于InnoDB来说也是一样的，下面由宏观视角到局部组成层层递进，从表空间(tablespace)、段(segment)、区(extent)、页(page)、行(row)来一探究竟。

表空间(tablespace)

  表空间，作为InnoDB存储引擎逻辑结构的最高层，所有
InnoDB存储引擎采用表空间(tablespace)的方式对数据进行存储，默认情况下会有一个ibdata1的文件，这就是默认的表空间文件。

  1. 关于ibdata1的文件命名，猜测是 innodb data 1 是缩写，因为表空间可以通过参数innodb_data_file_path来设置，用户是可以通过多个文件组成一个表空间的，这里是默认一个。适当的配置表空间来负载存储对于性能是有提升的，感兴趣可以自行了解，不在本文主要范畴内不展开。
  2. 可以通过show variables like ‘%xxxx%’ 命令来查看innodb的配置参数，innodb_data_file_path可用来指定表空间文件存放路径，innodb_file_per_table用来指定是否每个表一个表空间，这里默认是开启的，所以用户基于innodb存储引擎的表都会有一个名为表名.ibd独立的表空间，而不需要存放在默认表空间中，更加清晰和便于后续维护，对于存储和查询性能来说也是较为友好的。
  3. 一般用户表空间，每张表都会有两个文件结对出现:
    表名.frm 表示表结构，它是MySQL范畴的，和存储引擎无关
    表名.ibd 表示表空间，它是Innodb独有的，用来存储该表的数据、索引、和插入缓冲BITMAP等信息，其余信息如回滚(undo)信息、插入缓存冲索引页、系统事务信息、二次写缓冲(Double write buffer)等还是存放在默认的表空间（或者可以叫做共享表空间），因此共享表空间由于需要存储和管理这些数据也是会不断增加的

段(segment)

  表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等

区(extent)

  区是由连续页组成的空间，每个区的大小为1MB，为了保证区中页的连续性，InnoDB存储引擎每次会从磁盘申请4～5个区。InnoDB存储引擎中页的大小为16KB，即一个区中一共有64个连续的页。

  1. 区大小：1MB = 1024KB
    页大小：16KB
    1024KB / 16KB = 64个
  2. 创建的表默认大小为96KB，先用32个页大小的碎片页存放数据，使用完之后才会申请64个连续页。目的是对于小表，或者是undo类型的段可以申请较少空间，节省磁盘容量

页(page)

  InnoDB同大多数数据库一样，也有页(Page)的概念，它是InnoDB磁盘管理的最小单位。默认每个页的大小是16KB。

以下来源于《MySQL技术内幕 - InnoDB存储引擎 第2版》

页的类型解释
FIL_PAGE_INDEXB+ 树叶节点
FIL_PAGE_UNDO_LOGundo log 页
FIL_PAGE_INODE索引节点
FIL_PAGE_IBUF_FREE_LISTInsert Buffer 空闲列表
FIL_PAGE_TYPE_ALLOCATED该页为最新分配
FIL_PAGE_IBUF_BITMAPInsert Buffer位图
FIL_PAGE_TYPE_SYS系统页
FIL_PAGE_TYPE_TRX_SYS事务系统数据
FIL_PAGE_TYPE_FSP_HDRFile Space Header
FIL_PAGE_TYPE_XDES扩展描述页
FIL_PAGE_TYPE_BLOBBLOB页

行(row)

  InnoDB是行存储数据库(row-oriented)，每个页存放的行记录有硬性要求，最多允许存放16KB / 2-200行的记录，即7992行记录。

2.3 InnoDB数据页结构

  这里先列举下最全的InnoDB数据页结构信息，其实InnoDB数据页结构组成会根据Page的类型差异化的进行组织的，不同Page类型内的子结构构成不一样，但是一般来说，无论何种类型的页，都一定包含File Header、File Trailer这两种子结构，其他的根据页类型有所差异。

以下来源于《MySQL技术内幕 - InnoDB存储引擎 第2版》

主结构大小 (字节)子结构大小 (字节)说明
File Header（文件头）38FIL_PAGE_SPACE_OR_CHKSUM4该值代表页的checksum值
FIL_PAGE_OFFSET4表空间中页的偏移值。
FIL_PAGE_PREV4当前页的上一个页，B+Tree特性决定了叶子结点必须是双向列表
FIL_PAGE_NEXT4当前页的下一个页，B+Tree特性决定了叶子结点必须是双向列表
FIL_PAGE_LSN8代表该页最后被修改的日志序列位置LSN（Log Sequence Number）
FIL_PAGE_TYPE2InnoDB存储引擎页的类型
FIL_PAGE_FILE_FLUSH_LSB8如果页存放在系统共享表空间，表示文件至少被更新到了该LSN值；对于独立表空间，都为0
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID4代表页属于哪个表空间
Page Header（页头）56PAGE_N_DIR_SLOTS2在Page Directory页目录中的Slot槽数
PAGE_HEAP_TOP2堆中第一个记录的指针，记录在页中是根据堆的形式存放的
PAGE_N_HEAP2堆中的记录数。一共占用2字节，但是第15位表示行记录格式
PAGE_FREE2指向可重用空间的首指针
PAGE_GARBAGE2已删除记录的字节数，即行记录结构中delete flag为1的记录大小的总数
PAGE_LAST_INSERT2最后插入记录的位置
PAGE_DIRECTION2最后插入的方向
PAGE_N_PAGE2一个方向连续插入记录的数量
PAGE_N_RECS2该页中记录的数量
PAGE_MAX_TRX_ID8修改当前页面的最大事务ID，注意该值仅在Secondary Index中定义
PAGE_LEVEL2当前页在索引树中的位置，0x00代表叶节点，叶节点总是在第0层
PAGE_INDEX_ID8索引ID，表示当前页属于哪个索引
PAGE_BTR_SEG_LEAF10B+树数据页非叶子结点所在段的segment header。注意该值仅在B+树的Root页中定义
PAGE_BTR_SEG_TOP8B+树数据页所在段的segment header。注意该值仅在B+树的Root页中定义
Infimum 和 Supremum Records动态--虚拟的行记录，用来限定记录的边界。Infimum比该页中任何主键值都要小，Supremum比任何可能大的值还要大。
User Records （用户记录，即行记录）动态--实际存储行记录，B+树索引组织的
Free Space（空闲空间）动态--空闲空间，链表数据结构，当一条记录被删除后，该空间会被加入到空闲链表中
Page Directory（页目录）动态--稀疏目录，记录数据的相对位置，一个槽（Slots）可能拥有多条记录，二叉查找的结果只是一个粗略的结果。B+树索引并不能找到具体的一条记录，能找到的只是该记录所在的页。数据库把页载入到内存，然后通过Page Directory再进行二叉查找。
File Trailer（文件结尾信息）8FIL_PAGE_END_LSN8检测页是否完整写入磁盘。前4字节代表页的checksum值，最后4字节和File Header中的FIL_PAGE_LSN相同。checksum的默认算法是crc32

2.4 聚簇索引

  谈论完表空间，再深入到表空间里面来看下数据在表中具体是如何组织和存放的。InnoDB存储引擎中，表中的数据都是根据主键（Primary Key）顺序组织存放的，如果用户在创建表时没有指定主键，InnoDB存储引擎会按照以下方式来构建主键：

  • ① 判断表中是否存在满足非空的唯一索引(Unique Not Null)条件的列，如果有则以此为主键；多个满足选择定义顺序的第一个
  • ② 否则自动创建一个6字节大小的指针

通过 select _rowid from [table] 可以查询到表的主键值

3. 环境准备

  • Innodb_ruby源码地址：https://github.com/jeremycole/innodb_ruby
  • innodb_ruby学习手册：https://github.com/jeremycole/innodb_ruby/wiki#page-dump
  • innodb文件视图汇总：https://github.com/jeremycole/innodb_diagrams
  • 下载ruby：mac下安装需要依赖ruby，其他安装参考https://github.com/jeremycole/innodb_ruby/wiki#page-dump
brew install ruby

环境安装完毕后，创建一张用户数据表用来学习和测试验证innodb的数据存储和相关特性

CREATE TABLE `user_info`
(
    `id`          bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `user_id`     bigint(20) unsigned NOT NULL COMMENT '用户ID',
    `user_name`   varchar(32)         NOT NULL COMMENT '用户姓名',
    `age`         int(3)              NOT NULL COMMENT '用户年龄',
    `sex`         char(1)             NOT NULL COMMENT '用户性别',
    `address`     varchar(128)        NOT NULL COMMENT '用户住址',
    `phone`       varchar(15)             NOT NULL COMMENT '联系电话',
    `create_time` timestamp           NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` timestamp           NULL     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_user_id` (`user_id`),
    KEY `idx_create_time` (`create_time`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8 COMMENT ='用户信息表'

4. innodb ruby命令使用

通过 show variables like ‘datadir’查看mysql数据文件存放目录，比如我本机在 /usr/local/mysql/data/下，直接进入，然后就可以执行innodb ruby相关命令了

4.1 空间文件结构（Space File Structure）

4.1.1 列出全部表空间信息（system-spaces）

innodb_space -s ibdata1 system-spaces
namepagesindexes
(system)48647
mysql/engine_cost61
mysql/gtid_executed61
mysql/help_category72
… …… …… …
test/user_info 8 3

  上面说过ibdata1是innodb的系统表空间，是共享的，因此能看到这里包含了一些系统自身的表，也包含用户自定义的表信息，下面主要聚焦定义好的用户表信息来展开探究。

4.1.2 列出某个表的索引信息（space-indexes）

innodb_space -s ibdata1 -T test/user_info space-indexes
idnamerootfsegfseg_idusedallocatedfill_factor
184PRIMARY3internal111100.00%
184PRIMARY3leaf2000.00%
185uk_user_id4internal311100.00%
185uk_user_id4leaf4000.00%
186idx_create_time5internal511100.00%
186idx_create_time5leaf6000.00%

这里可以看到有三个索引:
PRIMARY 主键
uk_user_id 唯一索引
idx_create_time 普通索引
每个索引都有两条记录

4.1.3 列出页面类型区域（space-page-type-regions）

列出表空间中页的类型以及分布，这里我们来看下测试表 user_info

innodb_space -s ibdata1 -T test/user_info space-page-type-regions
startendcounttype
001FSP_HDR
111IBUF_BITMAP
221INODE
353INDEX
672FREE (ALLOCATED)

  可以看到，在测试表user_info中包含了不同类型的页(Page)，下面来逐一说明各类型页的作用：

  • FSP_HDR
    每一个表空间的第一个页为FSP_HDR(file space header)页，这个页保存了FSP header结构，这个结构保存了这个表空间的大小，以及完全没有被使用的extents，fragment的以及inode使用情况等等。

  • IBUF_BITMAP
    这个页就是插入缓存bitmap页，用于记录插入缓冲区的一些信息。主要用于跟踪随后的每个page的change buffer信息，使用4个bit来描述每个page的change buffer信息。

  • INODE
    该页用一个链表存储表空间中所有段(file segments)；之前说段是由若干个extents组成，其实段除了extents之外，还有32个单独分配的"碎片"页组成，因为有些段可能用不到一个区，所以这里主要是为了节省空间。

4.1.4 列出页面类型摘要（space-page-type-summary）

列出表空间中页（Page）类型摘要信息

innodb_space -s ibdata1 -T test/user_info space-page-type-summary
typecountpercentdescription
INDEX337.50B+Tree index
ALLOCATED225.00Freshly allocated
FSP_HDR112.50File space header
IBUF_BITMAP112.50Insert buffer bitmap
INODE112.50File segment inode

  这里看到的信息和1.3中展示的基本类似，更多的是空间占比的展示。这里的INDEX有数量是3，分别对应我们创建的三个索引：
PRIMARY 主键
uk_user_id 唯一索引
idx_create_time 普通索引

4.1.5 表空间的区域快照（space-extents-illustrate）

innodb_space -s ibdata1 -T test/user_info space-extents-illustrate
Page TypePagesRatio
█ System337.50%
█ Index 184 (test/user_info.PRIMARY)112.50%
█ Index 185 (test/user_info.uk_user_id)112.50%
█ Index 186 (test/user_info.idx_create_time)112.50%
░ Free space225.00%

  这里的数据比较有意思，对比1.4对比看。System包含FSP_HDR、IBUF_BITMAP、INODE，其他一致。

4.1.6 LSN空间年龄说明（space-lsn-age-illustrate）

innodb_space -s ibdata1 -T test/film space-lsn-age-illustrate

4.2. 页面结构（Page Structure）

4.2.1 查看页的概述情况

对于大多数的InnoDB中的页面类型都可以解析其结构内容

  我们可以使用1.3已经获取到的表空间Page索引位来进行查看每一个页的情况，这里直接列出表空间中的索引位（从0开始）和页类型

indexPage Type
0FSP_HDR
1IBUF_BITMAP
2INODE
3、4、5INDEX
6、7FREE (ALLOCATED)

FSP_HDR页的信息

innodb_space -s ibdata1 -T test/user_info -p 0 page-account

Accounting for page 0:
Page type is FSP_HDR (File space header, header page (page 0) for each tablespace file).
Extent descriptor for pages 0-63 is at page 0, offset 158.
Extent is not fully allocated to an fseg; may be a fragment extent.
Page is marked as used in extent descriptor.
Extent is in free_frag list of space.

IBUF_BITMAP页的信息

innodb_space -s ibdata1 -T test/user_info -p 1 page-account

Accounting for page 1:
Page type is IBUF_BITMAP (Insert buffer bitmap, bookkeeping for insert buffer writes to be merged).
Extent descriptor for pages 0-63 is at page 0, offset 158.
Extent is not fully allocated to an fseg; may be a fragment extent.
Page is marked as used in extent descriptor.
Extent is in free_frag list of space.

INODE页的信息

innodb_space -s ibdata1 -T test/user_info -p 2 page-account

Accounting for page 2:
Page type is INODE (File segment inode, bookkeeping for file segments).
Extent descriptor for pages 0-63 is at page 0, offset 158.
Extent is not fully allocated to an fseg; may be a fragment extent.
Page is marked as used in extent descriptor.
Extent is in free_frag list of space.

INDEX页的信息，一共有三个，分别来看下，首先是PRIMARY 主键

innodb_space -s ibdata1 -T test/user_info -p 3 page-account

Accounting for page 3:
Page type is INDEX (B+Tree index, table and index data stored in B+Tree structure).
Extent descriptor for pages 0-63 is at page 0, offset 158.
Extent is not fully allocated to an fseg; may be a fragment extent.
Page is marked as used in extent descriptor.
Extent is in free_frag list of space.
Page is in fragment array of fseg 1.
Fseg is in internal fseg of index 184.
Index root is page 3.
Index is test/user_info.PRIMARY.

第二个索引是uk_user_id 唯一索引

innodb_space -s ibdata1 -T test/user_info -p 4 page-account

Accounting for page 4:
Page type is INDEX (B+Tree index, table and index data stored in B+Tree structure).
Extent descriptor for pages 0-63 is at page 0, offset 158.
Extent is not fully allocated to an fseg; may be a fragment extent.
Page is marked as used in extent descriptor.
Extent is in free_frag list of space.
Page is in fragment array of fseg 3.
Fseg is in internal fseg of index 185.
Index root is page 4.
Index is test/user_info.uk_user_id.

第三个索引是 idx_create_time 普通索引

innodb_space -s ibdata1 -T test/user_info -p 5 page-account

Accounting for page 5:
Page type is INDEX (B+Tree index, table and index data stored in B+Tree structure).
Extent descriptor for pages 0-63 is at page 0, offset 158.
Extent is not fully allocated to an fseg; may be a fragment extent.
Page is marked as used in extent descriptor.
Extent is in free_frag list of space.
Page is in fragment array of fseg 5.
Fseg is in internal fseg of index 186.
Index root is page 5.
Index is test/user_info.idx_create_time.

最后是6，7两个页，都是 FREE (ALLOCATED)类型

innodb_space -s ibdata1 -T test/user_info -p 6 page-account

Accounting for page 6:
Page type is ALLOCATED (Freshly allocated, page type field has not been initialized).
Extent descriptor for pages 0-63 is at page 0, offset 158.
Extent is not fully allocated to an fseg; may be a fragment extent.
Page is marked as free in extent descriptor.
Extent is in free_frag list of space.

4.2.2 转储页面内容

对于大多数的InnoDB中的页面类型都可以解析其结构内容

innodb_space -s ibdata1 -T test/user_info -p 3 page-dump

这里输出内容较多，可自行尝试

4.2.3 汇总页面内的所有记录

  值得注意的是，这个命令只能对存储了数据行的索引页数据进行使用并进行解析，我们事先插入一条数据，如下：

insert into user_info (user_id, user_name, age, sex, address, phone, create_time, update_time)
values ('1','user001','20','1','北京市海淀区中关村',13800001122,now(),now())

在这里插入图片描述

下面先对PRIMARY 主键页数据进行解析

innodb_space -s ibdata1 -T test/user_info -p 3 page-records

Record 130: (id=1) → (
user_id=1,
user_name=“user001”,
age=20,
sex=“1”, address=“\xE5\x8C\x97\xE4\xBA\xAC\xE5\xB8\x82\xE6\xB5\xB7\xE6\xB7\x80\xE5\x8C\xBA\xE4\xB8\xAD\xE5\x85\xB3\xE6\x9D\x91”,
phone=“13800001122”,
create_time=“2022-06-03 13:39:57”,
update_time=“2022-06-03 13:39:57”
)

  可以看到节点数据存储的是 id = 1，而且行数据信息明细是挂载在主键上的，这也就是常说主键是一种聚簇索引的原因。

接下来，对uk_user_id 唯一索引页数据解析

innodb_space -s ibdata1 -T test/user_info -p 4 page-records

Record 125: (user_id=1) → (id=1)

  可以看到节点数据存储的是 user_id = 1，而且还存储了关联的数据行的主键值 id = 1。解析完数据后可以关联下索引覆盖、回表查询等概念，数据行存储在聚簇索引上，因此通过主键查询是效率较高的，如果是使用主键之外的索引进行查询的话，如果查询的数据正好在索引上，那么它的查询效率和通过主键进行查询没有太大差异，都是基于B+Tree，因此效率也是较高的。如果查询的数据是通过非主键盘索引完成检索且数据不完全在当前索引上需要通过主键指针回溯到主键索引上进行全部数据的返回，也就是回表操作。

最后，对idx_create_time 普通索引页数据解析，同上。

innodb_space -s ibdata1 -T test/user_info -p 5 page-records

Record 125: (create_time=“2022-06-03 13:39:57”) → (id=1)

4.2.4 转储页目录的内容

同样，这个命令也是只能对索引类型页进行使用和解析
下面先对PRIMARY 主键页数据进行解析

innodb_space -s ibdata1 -T test/user_info -p 3 page-directory-summary
slotoffsettypeownedkey
099infimum1
1112supremum2

4.2.5 展示页面内容数据分布

我们还是使用1.3已经获取到的表空间Page索引位来进行查看每一个页的情况，这里直接列出表空间中的索引位（从0开始）和页类型，关于每个类型页的结构情况可以参考上面陈述过的InnoDB页结构。

indexPage Type
0FSP_HDR
1IBUF_BITMAP
2INODE
3、4、5INDEX
6、7FREE (ALLOCATED)

先来看下FSP_HDR页的情况

innodb_space -s ibdata1 -T test/user_info -p 0 page-illustrate
Region TypeBytesRatio
█ FIL Header380.23%
█ FSP Header1120.68%
█ Extent Descriptor (free_frag)400.24%
█ Extent Descriptor (unused)1020062.26%
█ FIL Trailer80.05%
░ Garbage00.00%
Free598636.54%

IBUF_BITMAP页的情况

innodb_space -s ibdata1 -T test/user_info -p 1 page-illustrate
Region TypeBytesRatio
█ FIL Header380.23%
█ Insert Buffer Bitmap819250.00%
█ FIL Trailer80.05%
░ Garbage00.00%
Free814649.72%

INODE页的情况

innodb_space -s ibdata1 -T test/user_info -p 2 page-illustrate
Region TypeBytesRatio
█ FIL Header380.23%
█ Inode List Entry120.07%
█ Inode (used)11527.03%
█ Inode (free)1516892.58%
█ FIL Trailer80.05%
░ Garbage00.00%
Free60.04%

INDEX页的情况，先来看下PRIMARY 主键

innodb_space -s ibdata1 -T test/user_info -p 3 page-illustrate
Region TypeBytesRatio
█ FIL Header380.23%
█ Index Header360.22%
█ File Segment Header200.12%
█ Infimum130.08%
█ Supremum130.08%
█ Record Header100.06%
█ Record Data870.53%
█ Page Directory40.02%
█ FIL Trailer80.05%
░ Garbage00.00%
Free1615598.60%

再来看下uk_user_id 唯一索引

innodb_space -s ibdata1 -T test/user_info -p 4 page-illustrate
Region TypeBytesRatio
█ FIL Header380.23%
█ Index Header360.22%
█ File Segment Header200.12%
█ Infimum130.08%
█ Supremum130.08%
█ Record Header50.03%
█ Record Data160.10%
█ Page Directory40.02%
█ FIL Trailer80.05%
░ Garbage00.00%
Free1623199.07%

接下来，看下idx_create_time 普通索引

innodb_space -s ibdata1 -T test/user_info -p 5 page-illustrate
Region TypeBytesRatio
█ FIL Header380.23%
█ Index Header360.22%
█ File Segment Header200.12%
█ Infimum130.08%
█ Supremum130.08%
█ Record Header50.03%
█ Record Data120.07%
█ Page Directory40.02%
█ FIL Trailer80.05%
░ Garbage00.00%
Free1623599.09%

最后，看下FREE (ALLOCATED)页的情况

innodb_space -s ibdata1 -T test/user_info -p 6 page-illustrate
Region TypeBytesRatio
█ FIL Header380.23%
█ FIL Trailer80.05%
░ Garbage00.00%
Free1633899.72%

4.3. 索引结构（Index Structure）

4.3.1 索引递归

通过跟踪整个 B+Tree 来递归索引（执行完整的索引扫描）（通过递归扫描所有页面，而不仅仅是按列表扫描叶子页面）

先来遍历下PRIMARY 主键

innodb_space -s ibdata1 -T test/user_info -I PRIMARY index-recurse

ROOT NODE #3: 1 records, 97 bytes
RECORD: (id=1) → (user_id=1, user_name=“user001”, age=20, sex=“1”, address=“\xE5\x8C\x97\xE4\xBA\xAC\xE5\xB8\x82\xE6\xB5\xB7\xE6\xB7\x80\xE5\x8C\xBA\xE4\xB8\xAD\xE5\x85\xB3\xE6\x9D\x91”, phone=“13800001122”, create_time=“2022-06-03 13:39:57”, update_time=“2022-06-03 13:39:57”)

再来看下索引uk_user_id 唯一索引

innodb_space -s ibdata1 -T test/user_info -I uk_user_id index-recurse

ROOT NODE #4: 1 records, 21 bytes
RECORD: (user_id=1) → (id=1)

最后看下索引idx_create_time 普通索引

innodb_space -s ibdata1 -T test/user_info -I idx_create_time index-recurse

ROOT NODE #5: 1 records, 17 bytes
RECORD: (create_time=“2022-06-03 13:39:57”) → (id=1)

4.3.2 索引记录偏移量

像 index-recurse 一样递归索引，但打印索引页中每条记录的偏移量

先来看下PRIMARY 主键

innodb_space -s ibdata1 -T test/user_info -I PRIMARY index-record-offsets
page_offsetrecord_offset
5130

再来看下uk_user_id 唯一索引

innodb_space -s ibdata1 -T test/user_info -I uk_user_id index-record-offsets
page_offsetrecord_offset
4125

最后看下索引idx_create_time 普通索引

innodb_space -s ibdata1 -T test/user_info -I idx_create_time index-record-offsets
page_offsetrecord_offset
5125

4.3.3 索引级摘要

打印给定级别的所有索引页的摘要

先来看下PRIMARY 主键

innodb_space -s ibdata1 -T test/user_info -I PRIMARY -l 0 index-level-summary
pageindexleveldatafreerecordsmin_key
3187097161551id=1

再来看下uk_user_id 唯一索引

innodb_space -s ibdata1 -T test/user_info -I uk_user_id -l 0 index-level-summary
pageindexleveldatafreerecordsmin_key
4188021162311user_id=1

最后看下idx_create_time 普通索引

innodb_space -s ibdata1 -T test/user_info -I idx_create_time -l 0 index-level-summary
pageindexleveldatafreerecordsmin_key
5189017162351create_time=“2022-06-03 13:39:57”

4.4 行记录结构（Record Structure）

可以借助4.3.2的得到的record-offset来配置-R的参数值

先来看下PRIMARY主键

innodb_space -s ibdata1 -T test/user_info -p 3 -R 130 record-dump

Record at offset 130

Header:
Next record offset : 112
Heap number : 2
Type : conventional
Deleted : false
Length : 10

System fields:
Transaction ID: 186438
Roll Pointer:
Undo Log: page 425, offset 272
Rollback Segment ID: 61
Insert: true

Key fields:
id: 1

Non-key fields:
user_id: 1
user_name: “user001”
age: 20
sex: “1”
address: “\xE5\x8C\x97\xE4\xBA\xAC\xE5\xB8\x82\xE6\xB5\xB7\xE6\xB7\x80\xE5\x8C\xBA\xE4\xB8\xAD\xE5\x85\xB3\xE6\x9D\x91”
phone: “13800001122”
create_time: “2022-06-03 13:39:57”
update_time: “2022-06-03 13:39:57”

4.5 行记录历史（Record History）

一条记录的历史（undo log日志）

innodb_space -s ibdata1 -T test/user_info -p 3 -R 130 record-history
TransactionTypeUndo record
(n/a)insert(id=1) → ()

可以看到此刻只有一条 insert记录，下面执行一条修改语句，如下：

update user_info set age =21 where id = 1
TransactionTypeUndo record
186438update_existing(id=1) → (age=20, update_time=“2022-06-03 13:39:57”)
(n/a)insert(id=1) → ()

可以看到此刻新增了一条update_existing类型记录，记录了修改前数据快照情况，下面再继续执行一条修改语句，如下：

update user_info set age =22 where id = 1
TransactionTypeUndo record
186444update_existing(id=1) → (age=21, update_time=“2022-06-06 14:40:21”)
186438update_existing(id=1) → (age=20, update_time=“2022-06-03 13:39:57”)
(n/a)insert(id=1) → ()

5. 小结

  至此，innodb常用的文件结构、innodb ruby工具的常见用法基本概述了一遍，具体的使用场景和后续深入学习了解，可以通过结合B+Tree数据结构的存储特性、事务隔离级别等展开，将工具和具体场景深度结合来更为具象的了解innodb存储引擎的魅力和未知的神秘内部构造。

6. 参考

使用innodb_ruby分析InnoDb索引文件
MySQL · 引擎特性 · InnoDB 文件系统之文件物理结构
MySQL技术内幕 - InnoDB存储引擎 第2版（基于MySQL5.6）

posted @ 2022-06-06 23:16  大摩羯先生  阅读(104)  评论(0)  编辑  收藏  举报