本篇博客参考掘金小册 --MySQL 是怎样运行的: 从根儿上理解 MySQL
先给大家讲一个故事, 我刚参加工作, 在一个小作坊里面当[码畜] (尽管现在也是), 有一天老板从我背后走过, 说了一句举世震惊的话: 我看你们的数据库和 Excel 一样, 不就是一行行数据, 人家 Excel 还可以对单元格进行美化, 还有各种函数, 生成各种报表, 你们的数据库有什么复杂的? 我竟无力反驳.
为什么要说这个故事呢, 当然是为了引出今天的话题 --InnoDB 的数据结构.
虽然做开发的各位, 或多或少都接触过数据库, 但是数据库中的一行行数据到底是怎么存储的, 存储的格式又是什么, 就不是每个开发都知道的了, 数据库对我们而言就是一个黑盒子, 你想打开这个黑盒子一探究竟吗?[不, 我不想, 我只想 CURD] [不, 这不是你的真实想法] . 当我们收了快递, 尽管我们已经知道是什么快递了, 但是我们还是会迫不及待的拆开快递, 更何况, 我们面对的是未知的事物, 作为人的天性, 一定是非常希望可以打开这个黑盒子, 更别提充满好奇心的程序猿了, 今天我就带着打开这神秘的黑盒子.
这次我们打开的黑盒子便是 InnoDB 存储数据结构, 换而言之, MySQL 其他的存储引擎, 如 Memory,MyISAM 不在本次的讨论范围.
InnoDB 页简介
InnoDB 是一个把数据存储在硬盘的存储引擎, 即使服务器重启, 数据依然不会丢失, 而真正的数据处理是发生在内存中的, 所以 nnoDB 需要把硬盘上数据加载到内存中, 然后在内存中进行各种数据处理, 最终在某个时机把内存中的数据刷新到硬盘. 而硬盘的处理速度是很慢很慢的, 和内存差的太远了, 如果 InnoDB 每次只从硬盘中读取一条数据, 显然是不行的, 速度会慢死, 所以 InnoDB 会把数据分成若干页, 以页作为内存和硬盘之间交互的基本单位, 说的再直白点: InnoDB 读取数据不是一行一行读, 而是以页为最小单位读取数据. 默认情况下, 一页是 16K, 也就是 InnoDB 读取数据的数据大小至少是 16K. 当然这个值是可以被修改的, 因为一般情况下, 也没人会修改这个值, 所以这里我就不说明应该怎么改了.
InnoDB 行格式
之所以, 文章开头的老板会认为数据库和 Excel 是一样的, 就是因为我们平时基本都是用可视化工具去管理表, 去查数据, 一个不懂的人乍一看, 确实和 Excel 有点像, 就是一行一行数据, 这些数据在硬盘上存储格式是需要我们去探究的.
InnoDB 提供了好 4 种行格式供我们选择, 分别是 Compact,Redundant,Dynamic 和 Compressed 行格式, 以后可能会有新的行格式出现, 但是区别并不是很大.
我们建表的时候, 可以指定某种行格式:
CREATE TABLE table_name (列信息) ROW_FORMAT = 行格式名称
也可以修改已经存在的表的行格式:
ALTER TABLE table_name ROW_FORMAT = 行格式名称
准备工作
为了后面的故事可以顺利展开, 我们先来建一张表:
- CREATE TABLE hero(
- `x` VARCHAR(10),
- `y` VARCHAR(10) NOT NULL,
- `z` CHAR(10),
- `t` VARCHAR(10)
- )CHARSET=ASCII, ROW_FORMAT=COMPACT;
我建了一张表, 指定的行格式是 COMPACT, 采用的字符集是 ASCII, 也就是我们的中文是无法存进去的, 现在我要向这张表添加两行数据:
INSERT INTO hero(x, y, z, t) VALUES('a', 'bb', 'cccc', 'ddddd'), ('a', 'b', NULL, NULL);
现在表中的数据是这样的:
表建好了, 数据填充好了, 下面我们就来分析下在 COMPACT 行格式下, 数据是如何存储的吧.
COMPACT 行格式
从上图可以看到, 一行数据被分为了两个部分, 一部分是记录的额外信息, 一部分是记录的真实数据.
记录的额外信息
变长字段字节数列表
varchar(X)和 char(X)的区别是什么, 相信大家都非常清楚, char 是定长的, varchar 是变长的, 变长字段中存储多少字节的数据不是固定的, 所以 InnoDB 在存储数据的时候, 会把这些数据占用的真实字节数也保存下来, 也就是变长字段是占用了两部分空间来存储的:
真实的数据内容
占用的字节数
在 COMPACT 行格式中, 把所有的变长字段所占用的字节数逆序排放在变长字段字节数列表中.
我们先前创建了一张表, 还准备了两条数据, 现在我们来看下第一条数据中的变长字段字节数列表是什么酱紫的.
表中有四个字段, 其中 x,y,t 三个字段都是变长字段, 所以这三个字段的字节数需要保存在变长字段字节数列表, 数据表采用的字符集是 ascii, 所以每一个字符占用的字节数是 1, 下面我们来看下第一条数据各个变长字段所占用的字节数:
字段名称 | 内容 | 占用字节数 (十进制) | 占用字节数 (十六进制) |
---|---|---|---|
x | a | 1 | 0x01 |
y | bb | 2 | 0x02 |
t | ddddd | 5 | 0x05 |
所以, 第一行数据 x,y,t 三个字段所占用的字节数分别是 1 2 5, 但是 InnoDB 会把所占用的字节数逆序排放, 如果用 16 进制来表示变长字段所占用的字节数就是这样的效果了:
为了更容易理解, 清晰, 所以我用了空格来分割, 其实是没有的.
由于数据的长度都比较小, 用一个字节就可以表示, 但是如果变长字段占用的字节数比较多, 就要用两个字节来表示了, 到底使用一个字节来表示, 还是用两个字节来表示, InnoDB 有着自己的一套规则. 在说这个规则之前, 要先说明下规则中用到的三个变量:
W: 指定字符集下, 一个字符最多需要占用的字节数. 比如, ascii 字符集的 W 是 1,GBK 字符集的 W 是 2,utf-8 字符集的 W 是 3.
M: 最多可以存储多少个字符, varchar(50)的 M 就是 50.
L: 实际存储字符占用了多少字节.
W*M: 指定字段类型, 字符集下, 存储的字符串最多占用的字节数.
下面就是规则了:
如果 M*W<=255, 那么用一个字节表示字符串所占用的字节数.
如果 M*W>255, 则分为两种情况:
2.1 如果 L<=127, 则用一个字节来表示字符串所占用的字节数.
2.2 如果 L>127, 则用两个字节来表示字符串所占用的字节数.
光看规则是不是觉得很绕, 总结一下, 该可变字段允许存储的最大字节数 (W*M)>255, 且真实存储的字节数(L) 超过 127, 就用两个字节来表示字符串所占用的字节数, 否则用一个字节来表示字符串所占用的字节数.
我们再来看看第二条数据, 字段 t 的值是 NULL, 变长字段字节数列表只存储非 NULL 列内容占用的字节数, 所以对于第二条数据, 变长字段字节数列表只要存储 x 和 y 所占用的字节数即可, 填充在变长字段字节数列表的效果是酱紫的:
变长字段字节数列表不是必须的, 如果一个表中所有的字段都不是变长的, 那么就没有变长字段字节数列表了.
我们建的表采用的字符集是 ascii 编码的, 一个字符所占用的字节固定是 1, 如果我们采用 utf-8 字符集, 一个字段所占用的字节就不是固定的了, 而是一个范围: 1-3, 所以如果我们采用这样的字符集, char(m)虽然是定长字段, 但是也会被加入到变长字段字节数列表中.
NULL 值列表
我待过一家公司, 对表设计, 规范有非常明确的规定, 其中有一条是任何字段都不允许为 NULL, 问原因, DBA 只是淡淡的说了句, 允许为 NULL 会额外占用一些空间. 我也没有继续追究下去, 就按照规定来呗. 下面我就来揭秘为什么会有这个蛋疼的规定.
如果表中有字段允许为 NULL,InnoDB 就会开辟一块空间来标识每个字段实际存储的数据是不是为 NULL, 如果表中的字段都不允许为 NULL, 那么这块空间就不复存在了.
那么 InnoDB 开辟出来的那块空间具体是怎么回事呢, 接下去往下看.
每个允许存储为 NULL 的字段对应一个二进制位:
如果字段实际存储的数据不为 NULL, 二进制是 0.
如果字段实际存储的数据是 NULL, 二进制是 1.
这里和变长字段字节数列表是一样的, 是逆序排放的.
我们新建的 hero 表有三个字段都允许为 NULL, 所以存在 NULL 值列表.
我们先来看第一条数据, 三个字段存储的实际数据都不为 NULL, 所以用二进制来表示是酱紫的:
但是 InnoDB 是用整数字节的二进制位来表示 NULL 值列表的, 现在不足 8 位, 所以要在高位补 0, 最终用二进制来表示是酱紫的:
所以, 对于第一条数据, NULL 值列表用十六进制表示是 0x00.
我们再来看看第二条数据, 其中 z 和 t 两个字段存储的实际数据都是 NULL, 我们来看看用二进制如何来表示:
同样的, 需要高位补 0:
所以, 对于第二条数据, NULL 值列表用十六进制表示是 0x06.
我们把两条数据的 NULL 值列表都填充完毕是酱紫的效果:
记录头信息
记录头信息中包含的内容很多, 我先随便列举几条:
delete_mask : 标识此条数据是否被删除.
next_record: 下一条数据的位置.
record_type: 表示当前记录的类型, 0 表示普通记录, 1 表示 B + 树非叶子节点记录, 2 表示最小记录, 3 表示最大记录
...
还有其他的, 或者更具体的解释等以后用到了再说吧.
记录真实数据
对于 hero 表来说, 记录真实数据部分除了我们定义的四个字段, 还有三个隐藏字段, 分别为: row_id,trx_id,roll_pointer, 我们来看下这三个字段是什么.
row_id
如果我们建表的时候指定了主键或者唯一约束列, 那么就没有 row_id 隐藏字段了. 如果既没有指定主键, 又没有唯一约束, 那么 InnoDB 就会为记录添加 row_id 隐藏字段. row_id 不是必需的, 占用 6 个字节.
trx_id
事务 Id, 表示这个数据是由哪个事务生成的. trx_id 是必需的, 占用 6 个字节.
roll_pointer
这条数据上一个版本的指针. roll_pointer 是必需的, 占用 7 个字节.
关于 trx_id,roll_pointer 的具体解释, 在我上一篇关于事务的博客有详细描述过, 感兴趣的小伙伴可以找来看看.
VARCHAR(M)最多能存储的数据
在讲可变字段字节数列表的时候, 讲到 InnoDB 会有一套规则, 计算是用一个字节来表示实际存储的字节数, 还是用两个字节来表示实际存储的字节数, 但是如果存储的字符串很长很长, 用两个字节都无法表示, 该怎么办呢?
我们先来看看用两个字节最多可以表示的字节数是多少:
用两个字节最多可以表示的字节数是 65535.
我们用这个最大字节数来试下, 能不能成功创建一张表:
- CREATE TABLE test_max ( test VARCHAR ( 65535 ) ) charset = ascii,
- row_format = Compact
- Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
看到了木有, 两个字节最多可以表示的字节数是 65535, 我们用这个数字创建表竟然失败了, 更别提 65536 了.
为什么失败呢?
从报错信息就可以知道一行数据的最大字节数是 65535, 其中包含了 storage overhead. 问题来了, 这个 storage overhead 是什么呢? 就是可变字段字节数列表, NULL 值列表.
我们存储 VARCHAR(M)类型的字段, 其实可能分成了三个部分来存储:
真实数据
真实数据占用的字节数
NULL 标识, 如果不允许为 NUL, 这部分不需要
刚刚我们尝试创建的表, 字段是允许为 NULL 的, 所以会占用一个字节来存储 NULL 标识, 真实的数据所占的字节数用两个字节来表示, 所以最多可以存储 65535-2-1=65532 个字节.
- CREATE TABLE test_max ( test VARCHAR ( 65532 ) ) charset = ascii,
- row_format = Compact
- > OK
> 时间: 0.229s
我们新建的表采用的字符集是 ascii, 如果采用的是 GBK 或者 UTF-8,VARCHAR(M)最多能存储的数据计算方式就不一样了:
在 GBK 字符集下, 一个字符最多需要两个字节, VARCHAR(M)的最大取值就是 65532/2=32766.
在 UTF-8 字符集下, 一个字符串最多需要三个字节, VARCHAR(M)的最大取值就是 65532/3=21844.
我们上面所说的只是针对于一个列的计算方式, 如果有多个列的话, 要保证多个列所允许占用的最大字节数 + 变长字段字节数列表所占用的字节数 + NULL 值列表所占用的字节数<=65535.
行溢出
文章开头的时候, 给大家简单的介绍了下页的概念, 我们知道硬盘和内存之间交互的基本单位是页, 而页的大小默认情况下 16K, 也就是 16384 字节, 而 VARCHAR(M)最多可以存储的远远不止 16384 字节, 这样就出现了一个页存放不了一条记录的局面.
在 Compact 和 Redundant 行格式中, 对于占用字节数非常大的列, 在记录的真实数据中只会存储一小部分数据(768 个字节), 剩余的数据分散存储在其他的页, 为了可以找到它们, 在记录的真实数据中会记录这些页的地址, 就像下面酱紫:
Dynamic 和 Compressed 行格式
Dynamic 和 Compressed 行格式和 COMPACT 行格式很相近, 只是在行溢出的处理方式上有所不同, 溢出后, Dynamic 和 Compressed 行格式不会在记录的真实数据中存储一小部分数据, 而是直接记录其他页的地址. Dynamic 和 Compressed 行格式的区别是 Compressed 格式会对页进行压缩以节省空间.
Redundant 行格式是 MySql5.0 之前使用的, 现在基本不会再使用, 这里就不介绍了.
本章内容到这里就结束了, 下次会介绍关于页的详细内容.
来源: https://www.cnblogs.com/CodeBear/p/12843449.html