• InnoDB 行存储结构
  • InnoDB 数据页结构
  • Mysql 索引结构
  • Mysql 索引优化

未命名绘图.png

InnoDB 行结构

InnoDB 行结构分类：

  • Compact
  • Redundant
  • Dynamic
  • Compressed

行格式语法：

  • 创建表结构：create table 表名 （列信息） row_format=行格式名称；
  • 修改表结构：alter table 表名 row_format=行格式名称；

Compact 行格式

Compact行格式示意图.png

变长字段长度列表

mysql 支持变长的数据类型，varchar()、varbinary()、text 类型、blob 类型等，这些变长的数据类型占用的存储空间分为两部分：

  • 真正的数据内容
  • 占用的字节数

如果不保存真实数据占用字节数，那么 mysql 服务器无法判断出真实数据的长度，导致无法准确读取。在 Compact 格式中，把所有变长类型列的长度存放在行记录的开头部位形成一个列表，按照列的顺序逆序存放

进行验证：

  • Ubuntu 上 mysql 配置文件存放位置：/etc/mysql/my.cnf
  • Ubuntu 数据库文件默认存放位置：/var/lib/mysql/数据库名/表名.ibd
  1. # 创建数据库
  2. create database test;
  4. # 使用数据库
  5. use test;
  7. # 创建数据表
  8. create table demo(
  9.     c1 varchar(10),
  10.   c2 varchar(10) not null,
  11.   c3 char(10),
  12.   c4 varchar(10)
  13. ) charset=ascii row_format=compact;
  15. # 构造数据
  16. insert into demo values 
  17. ('aaaa','bbb','cc','d'),
  18. ('eeee','fff',null,null);
  20. # 验证
  21. select * from demo;
  23. # 将数据文件转化为16进制编码
  24. hexdump -C -v /var/lib/mysql/test/demo.ibd > ~/demo.txt
  26. # 切换到home
  27. cd ~
  29. # 打开文件，查找 supremum
  30. # 原因：由上述表结构可知，infimum + supremum后面跟着的就是行数据
  31. vim demo.txt

在 mysql 5.7 上的输出结果（一部分）：
image.png

  1. 0000c060  02 00 1e 69 6e 66 69 6d  75 6d 00 03 00 0b 00 00  |...infimum......|
  2. 0000c070  73 75 70 72 65 6d 75 6d  01 03 04 00 00 00 10 00  |supremum........|
  3. 0000c080  2d 00 00 00 00 02 00 00  00 00 00 05 07 a7 00 00  |-...............|
  4. 0000c090  01 1b 01 10 61 61 61 61  62 62 62 63 63 20 20 20  |....aaaabbbcc   |
  5. 0000c0a0  20 20 20 20 20 64 03 04  06 00 00 18 ff c2 00 00  |     d..........|
  6. 0000c0b0  00 00 02 01 00 00 00 00  05 07 a7 00 00 01 1b 01  |................|
  7. 0000c0c0  1e 65 65 65 65 66 66 66  00 00 00 00 00 00 00 00  |.eeeefff........|
  9. 分析：
  10. 01 03 04 /* 变长字段，表中有三个字段类型为varchar */
  11. 00              /* NULL标志位，第一行没有null的数据 */
  12. 00 00 10 00 00 /* 记录头 */
  13. 00 c0 80 2d 00 00 00 00 02 00 00
  14. 00 00 00 05 07 a7 00 00 00 00 c0 90 01 1b 01 10 
  15. 61 61 61 61 62 62 62 63 63  /* 第一行的数据：aaaabbbcc */
  17. 20 20 20 00 00 c0 a0 20 20 20 20 20 64 
  19. 03 04 /* 第二行的头信息 */
  20. 06    /* null位标志 */
  21. 00 00 18 ff c2 /* 记录头 */
  22. 00 00 00 00 c0 b0 00 00 02 01 00
  23. 00 00 00 05 07 a7 00 00 01 1b 01 00 00 c0 c0 1e 
  24. 65 65 65 65 66 66 66 /* 第二行的数据：eeeefff */

由于第一行记录中 c1、c2、c4 列中的字符串都比较短，也就是说内容占用的字节数比较小，用1个字节就可以表示，但是如果变长列的内容占用的字节数比较多，可能就需要用 2 个字节来表示。具体用 1 个字节还是 2 个字节来表示真实数据占用的字节数，我们用公式来表示一下，首先声明一下 W、M 和 L 的意思

  1. 在某个字符集中表示一个字符最多需要使用的字节数为 W。如 utf8 字符集中的 W 就是 3， gbk字符集中的 W 就是 2， ascii 字符集中的 W 就是 1
  2. 对于变长类型 VARCHAR（M） 来说，表示能存储最多 M 个字符，所以这个类型能表示的字符串最多占用的字节数就是 M*W
  3. 假设存储的字符串占用的字节数是 L

计算：

  • 如果 M*W < 256，则使用 1 个字节来表示真正字符串占用的字节数
  • 如果 M*W >= 256，则分两种表示情况
    • 如果 L < 128，则使用 1 个字节来表示真正字符串占用的字节数
    • 如果 L >=128，则使用 2 个字节来表示真正字符串占用的字节数
  • 即 varchar 类型允许的最大长度为2^16-1=65535

注意：变长字段长度列表只存储非 null 值的占用的长度，值为 null 的长度是不储存的，也就是说，对于第二条数据来说，因为 c4 列的值为 null，所以变长字段长度只需要存储 c1 和 c2 列的长度即可，其中，c1 列存储的值为 ‘eeee’，占用字节数为 4，c2 列存储的值 ‘fff’，占用的字节数为 3，所以变长字段长度列表需要 2 个字节

null 值列表

null 值列表：表中的某些列可能存储 null 值，如果把这些 null 值都放到记录的真实数据中存储会很占空间，所以 Compact 行格式把这些值为 null 的列统一管理起来，存储到 null 值列表中

  • 首先统计哪些列可以存储为 null 值：表中有允许存储为 null 的列时，则将每个允许存储 null 的列对应一个二进制位，二进制位按照列的顺序逆序排序
  • 根据列的实际值，用0或1填充null值列表
    • 二进制位的值为 1 时，代表该列的值为 null
    • 二进制位的值为 0 时，代表该列的值不为 null
    • null 值列表必须用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则在字节的高位补 0
    • null 值只占用标志位空间，不占用真实数据空间
  • 如果表中数据均不允许为 null，则不分配 null 值列表

如上述实验的第二列有 3 个列 1、3、4 允许为 null，而第 3 位，第 4 位值为 null，根据 null 值列表规则，则写成 1 1 0，即为 6
image.png

记录头信息

记录头信息：用来描述行的记录头信息，由固定的 5 个字节组成，即 40 个二进制位，不同的位代表不同的意思
隐藏列：

  • row_id：6字节，未添加主键时创建，行ID，唯一标识一条记录
  • transaction_id：7字节，必须，事务 ID
  • roll_pointer：7字节，必须，回滚指针

varchar 长度：

  • 如果 varchar 类型的列没有 not null 属性，那么最多只能存储 65532 个字节的数据，因为真实数据的长度需要占用两个字节，null 值标识需要占用一个字节
  • 如果 varchar 类型的列有 not null 属性，那么最多只能存储 65533 个字节的数据，因为真实数据的长度需要占用两个字节，不需要 null 值标识

行溢出：对于 Compact 格式，如果某一列中的数据非常多的话，从本记录的真实数据处只会存储该列的前 768 个字节的数据和一个指向其他页的地址，然后把剩下的数据存放到其他页中，这个过程叫做行溢出
image.png
行溢出临界点：8098

  1. MySQL中规定一个页中至少存放两行记录
  2. 每个页除了存放记录以外，需要存储一些元数据信息（136个字节的空间）
  3. 每个记录需要的元数据是27字节
    • 2个字节用于存储真实数据的长度
    • 1个字节用于存储列是否是NULL值
    • 5个字节大小的头信息
    • 6个字节的row id列
    • 6个字节的transaction id列
    • 7个字节的roll pointer列

InnoDB 页结构

InnoDB 将数据划分为若干页，以页作为磁盘和内存之间交互的基本单位，InnoDB 中页的大小一般为 16 kb；也就是在一般情况下，一次最少从磁盘中读取 16 kb 的内容到内存中；一次最少把内存中的 16kb 内容刷新到磁盘中。页内的物理地址是连续的。
**
屏幕截图 2021-01-10 104048.png
说明：
image.png

页中记录的插入：
image.png
记录头信息：
无标题 - 图9

  • deleted_flag：1个二进制位，标识当前记录是否被删除，为0标识记录没有被删除，为1说明记录被删除
  • min_rec_flag：标记该记录是否是B+树的非叶子节点中的最小记录
  • heap_no：当前记录在本页中的位置
    • innoDB 会自动在每页中添加两条记录，称为伪记录或者虚拟记录，一个代表最小记录，一个代表最大记录，存放在 Infimum + Supremum 中
    • 最小记录：最小记录的 heap_no 值是 0
    • 最大记录：最大记录的 heap_no 是 1
  • record_type：记录类型
    • 0：普通记录
    • 1：B+树记录
    • 2：最小记录
    • 3：最大记录
  • next_record：当前记录到下一条真实数据的地址偏移量
    • 下一条真实记录：指的不是插入顺序的下一条记录，而是按照主键值由小到大的顺序的下一条记录
    • 最小记录的下一条记录就是本页中主键值最小的记录
    • 本页中主键值最大记录的下一条记录就是最大记录

**
根据主键查找数据：