前言
- MySQL的默认存储引擎innodb是按16k大小的page来组织存储数据的
- MySQL的*.ibd 数据文件，大小一定是能被16kB整除的
- 在逻辑上innodb是按btree来组织数据存储的
- 针对每一行具体的数据，共有4种存储方式可供选择：Compact、Redundant、Dynamic和Compressed
- 其中：Redundant 已经被淘汰了，不建议使用
- Compact/Dynamic/Compressed 用的是同一个原理，只在细节上有点变化，不影响其实现逻辑
- 所以我们说行格式的时候，就可以从compact格式来分析，后两种是compact格式的变种
以下原理部分，都只说compact行格式。(?因为compact是基础，后两种都是基于它衍生出来的)
行格式在哪里看，怎么修改行格式
查看
mysql> show table status like '%dbooptest%' \G
*************************** 1. row ***************************
Name: dbooptest
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 9
Avg_row_length: 1820
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2020-06-10 20:22:49
Update_time: 2020-06-10 20:22:49
Check_time: NULL
Collation: utf8mb4_unicode_ci
Checksum: NULL
Create_options:
Comment: 测试
1 row in set (0.00 sec)
修改
CREATE TABLE dbooptest (id int ....) ROW_FORMAT=Compact;
ALTER TABLE 表名 ROW_FORMAT=Compressed; #注意不要在轻易在线上环境修改行格式,基本上等同于：作死！
查看当前默认的row_format
mysql> show global variables like 'innodb_default_row_format';
+---------------------------+---------+
| Variable_name | Value |
+---------------------------+---------+
| innodb_default_row_format | dynamic |
+---------------------------+---------+
1 row in set (0.00 sec)
代入开发者来思考怎么存储一条数据
理解compact格式的时候，你可以尝试代入Innnodb存储引擎的开发人员，怎么极限压缩存储空间。
假定有这样一张表,
create table dbooptest(
id int unsigned not null primary key ,
username varchar(20) not null default '',
userdesc varchar(255)
) ENGINE=InnoDB COMMENT='测试表' ;
# insert 一条记录
insert into dbooptest (id,username,userdesc) values (1,'51ak',null);
我们来思考如何减少存储空间
- 直接用类似json的方式存？
{id:1,username:"51ak",userdesc:null}- 显然，这里面的列名肯定是重的
- 真实的存储2进制是：
011110110110100101100100001110100011000100101100011101010111001101100101011100100110111001100001011011010110010100111010001000100011010100110001011000010110101100100010001011000111010101110011011001010111001001100100011001010111001101100011001110100110111001110101011011000110110001111101
- 把列名去掉，表结构和数据分开存储，这样每一行就不用存列名
{1,"51ak",null}- 还不错，这里明显节约了空间，但是用什么符号分隔？要不要引号限定符？能不能不要分隔符？
- 进一步优化，直接把数据挨个无缝的连起来存储:
151ak- 以这条数据举例
- 第一步compact格式根据数据字典，知道id列是个int型，所以用4个固定字节来存放，用了4个字节
- 第二步username是varchar变长字符，直接存放51ak，并且，在头部位置起个列表记下这个变长字符串"51ak"的长度为4 ，用了4+1个字节
- 第三步userdesc是null值，不存储任何内容，但是还需要在头部位置放置一个列表，标识第三列是null
- 再加上一些必须的行信息头（用于事务和系统要求）就行成了一条完整的行记录
是不是很极限？
真实的compact行格式
如上所说，我们知道compact格式真实的存储方式如下图所示
图:compact格式
对照真实的数据
第一部分：变长字段长度列表
变长的数据类型，varchar，VARBINARY(M)、各种TEXT类型，各种BLOB，这些都是变长类型。因为他们存储多少字节数据是不确定的，所以存储数据的时候，得把他们占用的字节数存起来。 所以这些变长字段占用需要记录它占用的字节数
在Compact行格式中
- 把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表，
- 各变长字段数据占用的字节数按照列的顺序逆序存放！
- 变长字段长度列表中只存储值为 非NULL 的列内容占用的长度，值为 NULL 的列的长度是不储存的 。
- 只有变长的字符串需要存储长度，固定长度的如int,datetime…都不需要存储
- 如果列字义长度（例如：varchar(100) 在utf8格式的时候定义长度是100*3=300）超过255就用两个字节来存储每个长度
第二部分：NULL值列表
- 某些列可能存储NULL值，如果把这些NULL值都放到记录的真实数据中存储会很占地方，所以Compact行格式把这些值为NULL的列统一管理起来，存储到NULL值列表中
- 二进制位按照列的顺序逆序排列，所以第一个列和最后一个二进制位对应。
- MySQL规定NULL值列表必须用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则在字节的高位补0。
第三部分：记录头信息（重要）
它是由固定的5个字节组成。5个字节也就是40个二进制位，不同的位代表不同的意思，如图：
这些二进制位代表的详细信息如下表：
图:compact格式下的记录头信息
对照真实的记录头信息
记录头各位置的意义
- delete_mask 1 标记该记录是否被删除
- min_rec_mask 1 B+树的每层非叶子节点中的最小记录都会添加该标记
- n_owned 4 表示当前记录拥有的记录数
- heap_no 13 表示当前记录在记录堆的位置信息
- record_type 3 表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录
- next_record 16 表示下一条记录的相对位置
第四部分：记录的真实数据
数据的隐藏列 （重要）：
mysql会给每个记录添加一些隐藏列，
- DB_ROW_ID 字节数不固定,唯一标识一条记录，如果没定义主键，则是系统生成所6个字节的行ID，如果定义了主键，这里是主键，长度按主键的大小
- DB_TRX_ID 6字节 事务ID
- DB_ROLL_PTR 7字节 回滚指针
这里的DB_TRX_ID和DB_ROLL_PTR 非常非常重要，是事务的核心知识点。
数据：
最后才是正实的数据，这时候反而简单了
- 数据按顺序存储，不需要记录数据长度（头部有列表）如果null就忽略。
- compact行格式和Dynamic，Compressed两个变种格式的区别也就在这里体现出来了
compact行格式和dynamic，compressed的区别
接上一节的最后一句话，当数据存储时，真实数据超过了768个字节（这里有个称呼：行溢出）
- compact行格式，会把超过768字节的部分，存储到其他页面中
- dynamic行格式，会把所有字节都存储到其他页面，只保留一个指针。
- compressed行格式，会把所有字节都存储到其他页面同时压缩它，只保留一个指针。
以上就是Innodb的行格式了，一方面需要思考MySQL是怎么做到尽可能少的利用空间把数据存储清楚。
更重要的是
- 在脑海里形成MySQL用page来管理数据，每一个Page里如何真实存储的概念
- 关注【第三部分信息头】/【每四部分中的数据的隐藏列】 这两个地方在理解事务/Mvcc里非常重要
