【MySQL进阶（二）】InnoDB存储引擎下的表的逻辑存储

首先需要注意的是，存储引擎本就是针对表而言的。

知识在这本书中《MySQL的技术内幕 InnoDB存储引擎 第二版》
链接：https://pan.baidu.com/s/10SGjh1P6dEF9u3haHZRzAg?pwd=5don
提取码：5don

InnoDB逻辑存储结构

从 InnoDB 存储引擎的逻辑存储结构上看，所有的数据都被逻辑地存放在一个空间中，称为表空间。表空间又由段（segment，如索引段、数据段、回滚段等等）、区（extent）、页（page）组成。

下图是InnoDB的逻辑存储结构图

1. 表空间

表空间可以看做是 InnoDB 存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。（表空间其实就是个抽象概念，本质上就是文件）

（1）系统表空间

• 系统表空间包含了很多【公共数据】，比如 InnoDB 的数据字典、回滚信息、系统事务信息、二次写缓冲等。
• 系统表空间是一个共享的表空间因为它是被多个表共享的。
• 该空间数据文件通过参数【innodb_data_file_path】控制，默认值是ibdata1:12M:autoextend（文件名为ibdata1、12MB、自动扩展）。
• 当然系统表空间也可以通过配置，修改文件的名称和个数。

下面查看默认配置

默认配置下的表空间

（2）独立表空间

• 独立表空间是默认开启的，在5.6.6版本以后，innodb不在默认将各个表的数据存储在【系统表空间】当中了，而是会为每一个表建立一个独立表空间，innodb存储引擎的独立表空间为【.ibd】文件。
• 如何启动了【innodb_file_per_table】参数，需要注意的是每一张表的表空间内存放的只是【数据】、【索引】和【插入缓冲bitmap页】，其他数据如：回滚信息、系统事务信息等还是放在原来的系统表空间内。
• 同时说明一个问题：即使启用了【innodb_file_per_table】参数，系统表空间还是会不断的增加其大小的。
  

下图说明了 Innodb 默认情况下是一些表信息放在独立表空间中，一些信息放在系统表空间中。

2. 段

从上面的 InnoDB 逻辑存储结构图中可以看出，表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。数据段即为 B+ 树的叶子节点（Leaf node segment），索引段即为 B+ 树的非叶子节点（Non-leaf node segment）。

3. 区

区是由连续页组成的空间，在任何情况下每个区的大小都为1MB。为了保证区中页的连续性，InnoDB 存储引擎第一次从磁盘申请 4~5 个区。在默认情况下，InnoDB 存储引擎页的大小为 16KB，即一个区中一共有 64 个连续的页。

注意：在用户启动了参数innodb_file_per_table后，创建的表默认大小是96KB。明显和区中连续64个页不符，其实这是因为在每一个段开始时，先会去用 32 个页大小的碎片页来存数据，使用完之后才会去申请 64 个连续页。

4. 页

页是 InnoDB 磁盘管理的最小单位（也可以称为块）。在 InnoDB 存储引擎中，默认每个页的大小为 16KB。
在 InnoDB 存储引擎中，常见的页类型有：

• 数据页（B-tree Node）
• undo页（undo Log Page）
• 系统页（System Page）
• 事务数据页（Transaction system Page）
• 插入缓冲位图页（Insert Buffer Bitmap）
• 插入缓冲空闲列表页（Insert Buffer Free List）
• 未压缩的二进制大对象页（Uncompressed BLOB Page）
• 压缩的二进制大对象页（compressed BLOB Page）

行记录格式

从上面的 InnoDB 逻辑存储结构图中可以看出，页中数据记录是以行的形式作为存储的，意味着页中保存着表中的一行一行的数据。

• 一个表的【行记录格式】决定表中行的【物理存储模式】，决定了【DQL】和【DML】的操作性能。越多的行被匹配进独立的磁盘页，sql的性能会更好一些，需要的缓存及IO操作就越少。
• 一条完整的信息记录分为：【记录的额外信息】和【记录的真实数据】两大部分，就如同一箱苹果里的苹果分为包装盒苹果一样。

指定行格式的语法如下（关键字 ROW_FORMAT）

-- 创建数据表时,显示指定行格式
CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称;
-- 创建数据表时,修改行格式
ALTER TABLE 表名 ROW_FORMAT=行格式名称;

-- 具体如下：
CREATE TABLE `ydl_user`  (
  `user_id` bigint NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `user_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户账号',
  ....
  PRIMARY KEY (`user_id`) USING BTREE
) ROW_FORMAT = DYNAMIC;

Compact 行记录模式

Compact 行记录以如下方式进行存储

• 第一个部分是一个非 NULL【变长字段长度列表】。我们的数据类型除了定长的char、int还有不定长的如varchar、text 等，变长列的真实长度就保存在这个部分，他是按照列的顺序【逆序放置】的。当列的长度小于255字节，如（varchar（132）），用1字节表示；若大于255个字节（varchar（256）），用两个字节表示，这其实也就说明了为什么varchar的最大长度是65536的原因。
• 第二个部分是【NULL 标志位】，它指示了当前行数据中哪些位null值，用一个bitmap表示。举一个例子：假如该标志位为06（二进制：00000110）则表示第二三列（可以为空的列）的数据为NULL。需要注意的是，NULL值标志位仅仅针对可以为NULL的字段，如果某个字段被定义为not null，那么这个字段就不会进入NULL值标志位的 BitMap 中，这样可以节省很多空间，NULL值标志位也是逆序排列，占用空间按照字节数高位补零，如有九个字段可以为空（00000001 01010101）。
• 第三部分为记录头信息（record header），固定占用5个字节（40位），每位的含义下表有阐述：
  
• 第四部分就是实际存储的每个列的数据了。需要注意的是，NULL不占该部分任何数据，即NULL除了占有NULL标志位，实际存储不占有任何空间。InnoDB 存储变长列（VARCHAR、VARBINARY、BLOB、TEXT）的前768字节，剩下的部分存储在溢出页中。固定长度列，超过768字节的视为变长列。部分存储前768字节，20字节指针存储列的溢出页的地址，所以长度为768+20字节。
  
  注意：每行数据除了用户定义的列外，还有两个隐藏列，事务ID列和回滚指针列，分别为6个字节和7个字节的大小。若InnoDB表没有定义Primary Key，每行还会增加一个【6个字节的RowID列】。

例子（看看硬盘的存储格式）

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

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

节选对应的【真实的表空间】中的的二进制结构表示（使用 hexdump -C .ibd表空间 命令查看）：

2c 00 00 00 00 2b 68 00  00 00 00 06 05 80 00 00 
00 32 01 10 61 62 62 62  62 20 20 20 20 20 20 20 
20 63 63 63 03 02 01 00  00 00 18 00 2b 00 00 00  
00 02 01 00 00 00 00 0f  62 c9 00 00 01 b2 01 10  
64 65 65 65 65 20 20 20  20 20 20 20 20 66 66 66  
03 01 06 00 00 20 ff 98  00 00 00 00 02 02 00 00 
00 00 0f 67 cc 00 00 01  b6 01 10 64 66 66 66 

第一行整理如下，需要注意，我们有三个变长列varchar：

03 02 01 // 变长字段长度列表，逆序，t4列长度为3，t2列长度为2，t1列长度为1
00 // NULL标志位，第一行没有NULL值
00 00 10 00 2c // 记录头信息，固定5字节长度
00 00 00 00 2b 68 // RowID我们建的表没有主键，因此会有RowID，固定6字节长度
00 00 00 00 06 05 // 事务ID，固定6个字节
80 00 00 00 32 01 10 // 回滚指针，固定7个字节
61 // t1数据'a'
62 62 // t2'bb'
62 62 20 20 20 20 20 20 20 20 // t3数据'bb'  Ox20十进制是32对应ascii码是空字符
63 63 63 // t4数据'ccc'

第二行整理如下：

03 02 01 // 变长字段长度列表，逆序，t4列长度为3，t2列长度为2，t1列长度为1
00 // NULL标志位，第二行没有NULL值
00 00 18 00 2b // 记录头信息，固定5字节长度
00 00 00 00 02 01 // RowID我们建的表没有主键，因此会有RowID，固定6字节长度
00 00 00 00 0f 62 // 事务ID，固定6个字节
c9 00 00 01 b2 01 10 // 回滚指针，固定7个字节
64 // t1数据'd'
65 65 // t2数据'ee'
65 65 20 20 20 20 20 20 20 20 // t3数据'ee'
66 66 66 // t4数据'fff'

第三行整理如下：

03 01 // 变长字段长度列表，逆序，t4列长度为3，t1列长度为1
06 // 00000110 NULL标志位，t2和t3列为空
00 00 20 ff 98  // 记录头信息，固定5字节长度
00 00 00 00 02 02 // RowID我们建的表没有主键，因此会有RowID，固定6字节长度
00 00 00 00 0f 67 // 事务ID，固定6个字节
cc 00 00 01 b6 01 10 // 回滚指针，固定7个字节
64 // t1数据'd'
66 66 66 // t4数据'fff'