mysql innodb 表空间物理存储结构及内部逻辑组织结构

在物理存储层面，每个mysql的表（非系统表）就是.frm文件及一个.ibd文件（前提是开启了独立表空间）。在物理层面就是一个二进制文件。

在这个二进制文件的基础上，分析其内部逻辑组织结构。

表空间 tablespace

是一个逻辑概念

一个表空间中有很多的对象，这些对象都是以segment的方式进行组织的。简单来说，其有数据段，索引段，回滚段，double write段等

数据段即为B+树的叶子节点（Leafnode segment)

索引段即为B+树的非索引节点（Non-leaf node segment）

段 segment

段在这里也是一个逻辑的概念

在元数据表performance_schema下的表中，找不到段的信息

应该把重点 放到 区 和 页 里面

在innodb中，每一个索引都由两个段segment组成，一个是非叶节点段，一个是叶子节点段。段满了可以继续申请区，是auto extend的

树的高度为1，可以看做是一个页

当一个页装不下了，那就要变为两个页，于是就分裂了，树的高度也就变为2了

区 extent

区应该也看做是一个逻辑概念

区是最小的空间申请单位

区的大小固定为1M

比如说系统（共享）表空间，最开始分配12M，如果满了auto-extend，会以区的大小去申请空间

如果将innodb_page_size这个参数设置为16k，这1M就对应着64个页

区的大小一定是1M，从源码角度来看，通常一次申请4个区，某些特殊场景下，申请5个区 这样保证区中页的连续性，一个区中一共有64 个连续的页。

页

是最小的I/O操作单位，也是实际上的物理概念
在innodb中，是怎样定义一个页的？

查询information_schema下的INNODB_SYS_TABLES

select name,space,table_id from INNODB_SYS_TABLES where space <> 0;

结果：

space = 0，就是在共享表空间ibdata1中的

select name,space,table_id from INNODB_SYS_TABLES where space = 0;

结果

通过(space,page_no)就能够定位到某个页，也就能够找到哪个文件，多少偏移量及获取多少字节数来读取到需要页的内容

• spaceID 是用来定位表的，也就是与表的文件一一对应，也是自增的，保存在元数据库performance_schema中
• page no是自增的

一个区0-63这是连续的

另一个区64-127这也是连续的

但在读取的时候可能只会读取到page_no为1,400…这样的

对于一个二进制文件的读取，如fread(offset,byte[])

比如第一个页16k，第二个页16k，这张表的大小/16384 就能获取共有多少个页

# 读取页伪代码

fopen(idbdata1)

# 读第一个页 
fread(0,16384)

# 读第二个页 
fread(16384,16384)

页记录格式

有页头File Header，Page Header，页尾File Trailer

1. file Header

在B+树的叶子节点上，是双向链表，FIL_PAGE_PREV和FIL_PAGE_NEXT都是指针，既可以向前扫，也可以向后扫，其中保存的就是page number

在file Header里面有一个FIL_PAGE_LSN，4Bytes，代表每个数据页的LSN

FIL_PAGE_SPACE_OR_CHKSUM，4Bytes，检测页是否损坏

2. Page Header

该部分用来记录数据页的状态信息，由14 个部分组成，共占用56 字节

3. lnfimum 和Supremum Record

在InnoDB 存储引擎中，每个数据页中有两个虚拟的行记录，用来限定记录的边界。

因为在InnoDB中，记录是有序的，这两个用来标记这个页中的最小值和最大值，有什么用？锁中会使用到

4. User Record

User Record是实际存储行记录的内容。再次强调，InnoDB存储引擎表总是B+树索引组织的。

5. Free Space

Free Space很明显指的就是空闲空间，同样也是个链表数据结构。在一条记录被删除后，该空间会被加入到空闲链表中。

6. Page Directory

需要牢记的是，B+树索引本身并不能找到具体的一条记录，能找到只是该记录所在的页。数据库把页载入到内存，然后通过Page Directory再进行二分查找。

7. File Trailer

为了检测页是否已经完整地写入磁盘（如可能发生的写入过程中磁盘损坏、机器关机等），InnoDB存储引擎的页中设置了File Trailer部分。

InnoDB存储引擎每次从磁盘读取一个页就会检测该页的完整性

行记录

每个页里面是怎样表示每一条记录呢？里面的记录又是如何关联的呢？

heap_no表示页中每个记录插入的顺序序号

在一个页中，记录是以 堆heap 的方式进行存储的，但其又通过指针（单向，只指向下一条）的方式使其变得有序。

堆是一种数据结构

假设插入的数据是a, b, d, e, g ；则对应的heap_number 为2，3，4，5，6

0 和1 被infimum 和supermum 所使用

infimum 对应最小的heap_number

supremum 对应最大的heap_number，随着数据的插入，该值会更新

update对heap_number没有影响

heap_no是物理的，存储在row记录格式的record_header 字段中

record_header 记录头信息

heap_number非常重要，数据库的锁是与其关联的

在mysql中如何查看哪条记录被锁住了呢？

把这个参数打开

show variables like "%innodb_status_output_locks%";
set global innodb_status_output_locks=1;

show engine InnoDB status;

结果：

TABLE LOCK table `employees`.`test_heap` trx id 65050 lock mode IX
RECORD LOCKS space id 258 page no 3 n bits 72 index PRIMARY of table `employees`.`test_heap` trx id 65050 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32

就是锁住了heap no 2的记录

 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000000fe1a; asc       ;;
 2: len 7; hex 3a0000010d2544; asc :    %D;;

之后是这三段内容，含义是

• 就是int 4个字节的主键值 80000001 就是1，最高位8说明是有符号的int，即signed int
• 6个字节的trx id
• 7个字节的rollback pointer

heap_no 1: supremum也是可能被锁住的

begin;
select * from test_heap where a > 1 for update;

行记录格式

1. variable string length list

变长字段长度列表

一个非NULL变长字段长度列表，并且其是按照列的顺序逆序放置的，其长度为：

• 若列的长度小于255 字节，用1字节表示
• 若大于255个字节，用2字节表示

变长字段的长度最大不可以超过2字节，这是因在MySQL 数据库中VARCHAR 类型的最大长度限制为65535

2. null flag

NULL标志位

该位指示了该行数据中是否有NULL 值，有则用1表示。该部分所占的字节应该为1字节。

3. record header

记录头信息

固定占用5字节（40位）

4. 列数据

列1数据，列2数据…

实际存储每个列的数据。需要特别注意的是，NULL不占该部分任何空间，即NULL除了占有NULL标志位，实际存储不占有任何空间。

另外有一点需要注意的是，每行数据除了用户定义的列外，还有两个隐藏列，事务ID列和回滚指针列，分别为6字节和7字节的大小。若InnoDB 表没有定义主键，每行还会增加一个6字节的row id列。

举例说明底层存储

create table mytest(
    t1 varchar(10),
    t2 varchar(10),
    t3 char(10),
    t4 varchar(10)
) engine=innodb row_format=compact;

insert into mytest values('a','bb','bb','ccc');
insert into mytest values('d','ee','ee','fff');
insert into mytest values('d',NULL,NULL,'fff');

查看其二进制文件

对插入数据的第一行存储进行分析

varchar需要一个Byte（定义长度小于255）或2个Byte（长度大于255，小于65535）来记录其所占字节长度，可以看到col1，col2，col4都是varchar类型，其中03记录的是col4的长度，02记录的是col2的长度，01记录的是col1的长度。

col3是char(10)，所以可以看到其填充了8个space，来凑成10个Byte

这就是需要理解的char和varchar的区别

char占用了10个字节，而varchar占用了2个字节

注意：这里的charset=latin1，而目前推荐使用charset=utf8mb4字符集了

如果使用的是utf8mb4多字符集，最大的字符如表情占用4个字节，中文一般3个字节，英文一般1个字节，varchar和char表示的都是字符，而不是字节。也就是说char(10)可能占用10~40个字节

那么现在如果我把字符集改为utf8mb4，那我col3插入bb，填充多少个空格呢？其实其填充的是最小字节数，所以会填充8个space，这样就看出了在多字符集的情况下，char失去了其优势，char也变为变长的了。定长类型的好处是可以进行原地更新。

每个页有预留的空间，varchar内容变长了，那是没办法进行原地更新的，是把原行删除掉，之后在预留空间新入新行，要占用新的存储空间，这样会造成一个页中能存放的记录数变少，会生成碎片，更容易发生分裂，一个页能存放的记录越多，则性能越优，原地更新好处就显示出来了。

碎片都会放到一个链表free-list（free space）中，每个页在split之前会reorganize一下，简单理解就是把这个页的数据重新插入一遍，是在内存中进行整理，然后再checkpoint到磁盘中，这样就把碎片整理好了

对有NULL值的插入数据第3行进行分析

第3行有NULL值，因此NULL标志位不再是00而是06，转换成二进制为00000110，有1的值代表第2列和第3列的数据为NULL。在其后存储数据的部分，会发现没有存储NULL列，而只是存储了第1列和第4列非NULL的值。说明，不管是CHAR类型还是VARCHAR类型，在compact格式下NULL值都不会占用任何存储空间。