基于InnoDB存储引擎的mysql数据库表结构详解

前言

前一篇文章基于InnoDB存储引擎的mysql数据库文件详解中的数据文件就是本篇文章的表文件。基于InnoDB存储引擎的mysql数据库所有数据都存在这个表文件中，可以叫做表空间。InnoDB存储引擎的表空间由段（segment），区（extent），页（page）组成。

理解一个概念：如果一台运行mysql数据库的服务器内存大小为1G，那么如果你查询大于1G的数据，必挂无疑。像select * from table的数据小于1G那么也会很慢，但是可以查询。mysql数据库把所有的数据传送完之后会存储一部分数据到内存。提供给下次的查询。

InnoDB存储引擎表空间结构图

 

 

虽然表空间结构是如上图所示，但是也并不是严格按照上面的结构划分的，比如一个表空间可能由几个段和很多个零散的页组成。

对于innodb文件结构不理解可以查看基于InnoDB存储引擎的mysql数据库文件详解。

段

一个表空间常见的段有数据段，索引段，回滚段。一个段有多个区组成。这里不做详细解释，意义不大。

区

一个区由连续的64个页组成。大小为64*16KB = 1MB。这里不做详细解释，意义不大。

页

一个页由多个行组成，因为是索引组织表，所以一个页最少要有2个行。

页是innoDB存储引擎最小的磁盘空间单位。一个页的大小为固定的16KB。

为什么说页是innodb存储引擎的最小磁盘空间单位？

比如新建一个test空表。如果往里面插入一行只有1KB的数据。那么innodb存储引擎就会从硬盘申请16KB的磁盘空间。虽然里面只有1KB的数据，但是还是会占据16KB的磁盘空间。剩余的15KB是空闲的空间，用于下次插入数据的空间使用。

打开innodb_file_per_table参数。进行下面的测试。

新建一个空表test。

mysql>create table test(id int(10) unsigned auto_increment,name varchar(8000),primary key(id))engine = innodb;

查看新表的大小。

#ls –lh test.ibd;

可以看到使用innodb存储引擎的新建的空表的大小为96KB。也就是96KB/16KB=6个页。也就是说innodb为每个空表分配了6个页大小的空间用。

给name列添加索引。

mysql>create index name on test(name);

在查看表的大小。

#ls –lh test.ibd;

大小变为112KB，增加了112KB-96KB=16KB。也就是说为一个列添加一条索引innodb就会给再申请16KB大小的磁盘空间，也就是1个页大小的空间使用。

删除这个name索引，再看看表空间大小。

 

表空间的大小又变回96KB。虽然磁盘空间的大小成为96KB，但是这里面还有很多的空间可以用于新数据的存储。下面看看这个96KB大小的空间能够存储多少数据才会增加。

往表里面添加3行数据。大小为3*8000B，也就是3*8000B/1024 = 3*7.8kb = 23.4KB。在查看表空间大小。

mysql>insert test select null,repeat(‘a’,8000);

可以看到还是96KB大小。

再插入一条数据查看大小。

mysql>insert test select null,repeat(‘a’,8000);

表空间大小已经变为128KB了。增加了128KB-96KB=32KB。也就是增加了32KB/16KB=2个页的大小。

就是说空表的96KB大小只能存储 23.4KB大小的数据。再插入数据的表空间大小就会增加了。其实这个大小不是23.4KB，确切的应该是32KB。就是两个页的大小。如果两个页全部用完innodb就会申请更多的空闲页。这里申请了2个空闲页。

基础插入59条数据（为什么插入59条呢？为了证明一个概念，仔细看下面的测试会明白。）。那么数据的总大小为前面的4条+新的59条=63条数据。大小为63*7.8KB = 491.4KB。在查看表空间大小。

可以看到表空间大小变为576KB大小。也就是576KB/16KB=36个页。

这个时候再插入一条数据。查看大小。

奇迹出现，表空间大小变为2M。也就是2*1024KB/16KB = 128个页。为什么呢？

因为innodb在新建一个表的时候会用零散的36个页存储数据，如果数据量大于36页就会开始申请更大的空间存储数据。申请64个连续的页大小的空间。以后随着数据的增加继续申请64个页的空间。

页的结构

File Header	文件头	38个字节
Page Header	页头	56个字节
Infimun+Supermun Records	主键最大值，最小值	分配剩余的16KB*1024-38B-56B-8B=16282B
User Records	记录数据	分配16282B
Free Space	空闲空间	分配16282B
Page Directory	页目录	分配16282B
File Trailer	文件结尾信息	8个字节

其中文件头，页头，文件结尾信息的大小是固定。其余的会根据数据大小分配剩下空间。

文件头File header组成

名称	解释	大小
FIL_PAGE_SPACE_OR_CHKSUM	该页数据那个表空间，ibdata文件中插入缓冲页中这个值记录这test.ibd中的test表空间	4字节
FIL_PAGE_OFFSET	表空间中页的偏移量	4字节
FIL_PAGE_PREV	上一个页的偏移量	4字节
FIL_PAGE_NEXT	下一个页的偏移量	4字节
FIL_PAGE_LSN	该页最后被修改的日志位置LSN（log sequence number）	8字节
FIL_PAGE_TYPE	该页存放的数据类型。数据页，索引页等	2字节
FIL_PAGE_FILE_FLUSH_LSN	文件至少被更新到了LSN值	8字节
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID	页属性属于那个表空间	4字节

页头Page Header的组成

名称	解释	大小
PAGE_N_DIR_SLOTS	在页目录（Page Directory）中slot（槽）数	2字节
PAGE_HEAP_TOP	堆中第一个记录的指针	2字节
PAGE_N_HEAP	堆中的记录数	2字节
PAGE_FREE	指向空闲列表的首指针	2字节
PAGE_GARBAGE	已删除记录的字节数，就是行记录结构中delete falg为1的记录	2字节
PAGE_LAST_INSERT	最后插入记录的位置	2字节
PAGE_DIRECTION	最后插入的方向，可能值PAGE_LEFT(0×01)，PAGE_RIGHT(0×02)	2字节
PAGE_N_DERECTION	一个方向连续插入记录的数量	2字节
PAGE_N_RECS	该页中记录数	2字节
PAGE_MAX_TRX_ID	修改当前页的最大失误ID	8字节
PAGE_LEVEL	当前页在索引树种的位置，0×00代表叶节点	2字节
PAGE_INDEX_ID	当前页属于那个索引	8字节
PAGE_BTR_SEG_LEAF	B+树的叶节点中，文件段的首页指针位置	10字节
PAGE_BTR_SEG_TOP	B+树的非叶节点中，文件段的首指针位置	10字节

Infimun+Supermun Records的组成

这两个是该页的两个边界，infimun记录比该页中任何主键的值都要小。supermun记录比该页中任何可能最大的主键值都要大。

User Records 的组成

存储着所有的行记录内容。

FreeSpace 的组成

就是空闲的空间。

Page Directory 页目录的组成

存放了记录的相对位置。

File Trailer 文件尾的组成

名称	解释	大小
FIL_PAGE_END_LSN	与FILE_HEADER的FIL_PAGE_LSN值比对，用来检查页的完整性	8字节

行

InnoDB存储引擎的数据是按照一行一行存储的。每个页里面最多存放16KB大小的行。最小2行，因为是索引组织表。

InnoDB有很多行的记录格式。默认是Compact行记录格式。

Compact行记录格式

变长字段长度列表	NULL标记位	记录头信息	列1数据	列2数据	列3数据	…………	列n数据
存储vachar等变长类型的长度	记录列中是否有NULL数据	关于行的信息记录	具体的列	具体的列	具体的列	具体的列	具体的列

 

总结