Innodb行格式

Compact

img

记录头部的变长字段长度列表逆序存储所有变长字段的真实数据占用的字节长度。变长字段长度列表中只存储值为 非NULL 的列内容占用的长度。

值为 NULL 的列由NULL值列表统一管理，将每个允许存储 NULL 的列对应一个二进制位，二进制位按照列的顺序逆序排列。

记录头信息

img

名称 大小 (bit) 描述
预留位1 1 没有使用
预留位2 1 没有使用
delete_mask 1 标记该记录是否被删除，0：没有删除，:已经删除
min_rec_mask 1 B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned 4 表示当前记录拥有的记录数
heap_no 13 表示当前记录在记录堆的位置信息
record_type 3 表示当前记录的类型， 0 表示普通记录（即行记录），1 表示B+树非叶子节点记录（索引的节点记录）， 2 表示最小记录， 3 表示最大记录
next_record 16 表示下一条记录的相对位置（从当前记录的真实数据到下一条记录的真实数据的地址偏移量）

隐藏字段

列名 是否必须 占用空间 描述
DB_ROW_ID 否 6 字节 行ID，唯一标识一条记录
DB_TRX_ID 是 6 字节 事务ID
DB_ROLL_PTR 是 7 字节 回滚指针

行溢出

​ innodb一个页的大小是 16KB ，也就是 16384 字节，而一个 VARCHAR(M) 类 型的列就最多可以存储 65532 个字节，这样就可能造成一个页存放不了一条记录，会把多余的数据存储到其他页中，这种现象称为行溢出 。对于 Compact 和 Reduntant 行格式来说，如果某一列中的数据非常多的话，在本记录的真实 数据处只会存储该列的前 768 个字节的数据和一个指向其他页的地址，然后把剩下的数据存放到其他页中，这个 过程也叫做行溢出 ，存储超出 768 字节的那些页面也被称为溢出页。

img

不只是 VARCHAR(M) 类型的列，其他的 TEXT、BLOB 类型的列在存储数据非常多的时候也会发生行溢出 。

img

Redundant

img

记录头信息

名称 大小(bit) 描述
预留位1 1 没有使用
预留位2 1 没有使用
delete_mask 1 删除标志
min_rec_mask 1 B+树的每层非叶子节点中的最小记录都会添 加该标记
n_owned 4 表示当前记录拥有的记录数
heap_no 13 表示当前记
n_field 10 表示记录中列的数量
1byte_offs_flag 1 标记字段长度偏移列表中每个列对应的偏移量是使用1字节还是2字节表示的
next_record 16 表示下一条记录的相对位置

Dynamic和Compressed

​ 这俩行格式与Compact 行格式类似，只不过在处理行溢出 数据时有点儿分歧，它们不会在记录的真实数据处存储字段真实数据的前 768 个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址:

img

Compressed 行格式和 Dynamic 不同的一点是， Compressed 行格式会采用压缩算法对页面进行压缩，以节省空间。

修改表行格式

1
ALTER TABLE table_name ROW_FORMAT=Redundant

char

​ CHAR(M)类型的列来说，当列采用的是定长字符集时，该列占用的字节数不会被加到变长字段长度列表，而如果采用变长字符集时，该列占用的字节数也会被加到变长字段长度列表

VARCHAR(M)

​ VARCHAR类型的列最多可以占用 65535 个字节，首部的变长列长度记录最大占用两个字节，65535 是 16 位二进制数所能表示的最大值。一个 VARCHAR(M) 类型的列，其实需要占用3部分存储空间:

  • 真实数据

  • 真实数据占用字节的长度

  • NULL 值标识，如果该列有 NOT NULL 属性则可以没有这部分存储空间

如果 VARCHAR 类型的列有 NOT NULL 属性，那最多只能存储 65533 个字节的数据，因为真实数据的长度可能占用 2个字节，不需要 NULL 值标识，没有则最多存储65532个字节

innodb数据页

img

img

image-20220502013457323

InnoDB 自动给每个页里边儿加了两个记录，分别为最小记录与最大记录。这两条记录的构造十分简单，都是由5字节大小的 记录头信息 和8字节大小的一个固定的部分组成的，由于这两个记录 并不是我们自己插入的，被单独放在 一个称为 Infimum + Supremum 的部分，有时候也称为伪记录或者虚拟记录。 下一条记录 指得并不是按照我们插入顺序的下一条记录，而 是按照主键值由小到大的顺序的下一条记录。

img

当数据页中存在多条被删除掉的记录时，这些记录的next_record属性将会把这些被删除掉的记录组成

一个垃圾链表，以备之后重用这部分存储空间。

Page Directory (页目录)

​ innodb将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）划分为几个组。每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的 n_owned 属性表示该记录拥有多少条记录，也就是该组内共有几条记录。将每个组的最后一条记录的地址偏移量单独提取出来按顺序存储到靠近页的尾部的地方，这个地方就是所谓的 Page Directory ，也就是页目录 。页面目录中的这些地址偏移量被称为槽 （英文名： Slot ），所以这个页面目录就是由槽组成的。

image-20220502011017534

image-20220502011415062

对于最小记录所在的分组只能有 1 条记录，最大记录所在的分组拥有的记录条数只能在 1~8 条之间，剩下的分组中记录的条数范围只能在是 4~8 条之间。

分组步骤：

  1. 初始情况下一个数据页里只有最小记录和最大记录两条记录，它们分属于两个分组。
  2. 之后每插入一条记录，都会从 页目录 中找到主键值比本记录的主键值大并且差值最小的槽，然后把该槽对应的记录的 n_owned 值加1，表示本组内又添加了一条记录，直到该组中的记录数等于8个。
  3. 在一个组中的记录数等于8个后再插入一条记录时，会将组中的记录拆分成两个组，一个组中4条记录，另一个5条记录。这个过程会在 页目录 中新增一个 槽 来记录这个新增分组中最大的那条记录的偏移量。

在一个数据页中查找指定主键值的记录的过程分为两步：

  1. 通过二分法确定该记录所在的槽，并找到该槽中主键值最小的那条记录。

  2. 通过记录的 next_record 属性遍历该槽所在的组中的各个记录。

Page Header（页面头部）

名称 占用空间(字节) 描述
PAGE_N_DIR_SLOTS 2 在页目录中的槽数量
PAGE_HEAP_TOP 2 还未使用的空间最小地址，也就是说从该地址之后就是 Free Space
PAGE_N_HEAP 2 本页中的记录的数量（包括最小和最大记录以及标记为删除的记录）
PAGE_FREE 2 第一个已经标记为删除的记录地址（各个已删除的记录通过 next_record 也会组成一个单链表，这个单链表中的记录可以被重新利用）
PAGE_GARBAGE 2 已删除记录占用的字节数
PAGE_LAST_INSERT 2 最后插入记录的位置
PAGE_DIRECTION 2 记录插入的方向
PAGE_N_DIRECTION 2 一个方向连续插入的记录数量
PAGE_N_RECS 2 该页中记录的数量（不包括最小和最大记录以及被标记为删除的记录）
PAGE_MAX_TRX_ID 8 修改当前页的最大事务ID，该值仅在二级索引中定义
PAGE_LEVEL 2 当前页在B+树中所处的层级
PAGE_INDEX_ID 8 索引ID，表示当前页属于哪个索引
PAGE_BTR_SEG_LEAF 10 B+树叶子段的头部信息，仅在B+树的Root页定义
PAGE_BTR_SEG_TOP 10 B+树非叶子段的头部信息，仅在B+树的Root页定义

FileHeader(文件头部)

​ Page Header 是专门针对数据页记录的各种状态信息，如页里有多少个记录，多少个槽。File Header 针对各种类型的页都通用，也就是说不同类型的页都会以 File Header 作为第一个组成部分，它描述了一些针对各种页都通用的一些信息，比方说这个页的编号是多少，它的上一个页、下一个页等信息。

名称 占用空间(字节) 描述
FIL_PAGE_SPACE_OR_CHKSUM 4 页的校验和（checksum值）
FIL_PAGE_OFFSET 4 页号
FIL_PAGE_PREV 4 上一个页的页号
FIL_PAGE_NEXT 4 下一个页的页号
FIL_PAGE_LSN 8 页面被最后修改时对应的日志序列位置（英文名是：Log Sequence Number）
FIL_PAGE_TYPE 2 该页的类型
FIL_PAGE_FILE_FLUSH_LSN 8 仅在系统表空间的一个页中定义，代表文件至少被刷新到了对应的LSN值
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID 4 页属于哪个表空间

页类型(FIL_PAGE_TYPE)

类型名称 十六进制 描述
FIL_PAGE_TYPE_ALLOCATED 0x0000 最新分配，还没使用
FIL_PAGE_UNDO_LOG 0x0002 Undo日志页
FIL_PAGE_INODE 0x0003 段信息节点
FIL_PAGE_IBUF_FREE_LIST 0x0004 Insert Buffer空闲列表
FIL_PAGE_IBUF_BITMAP 0x0005 Insert Buffer位图
FIL_PAGE_TYPE_SYS 0x0006 系统页
FIL_PAGE_TYPE_TRX_SYS 0x0007 事务系统数据
FIL_PAGE_TYPE_FSP_HDR 0x0008 表空间头部信息
FIL_PAGE_TYPE_XDES 0x0009 扩展描述页
FIL_PAGE_TYPE_BLOB 0x000A BLOB页
FIL_PAGE_INDEX 0x45BF 索引页，也就是我们所说的数据页

image-20220504153242912

File Trailer

​ File Trailer由8 个字节组成，可以分成2个4字节的小部分：

  • 前4个字节代表页的校验和，与File Header 中的校验和相对应的。每当一个页面在内存中修改了，在同步之前就要把它的校验和算出来，因为 File Header 在页面的前边，所以校验和会被首先同步到磁盘，当完全写完时，校验和也会被写到页的尾部，如果完全同步成功，则页的首部和尾部的校验和应该是一致的。
  • 后4个字节代表页面被最后修改时对应的日志序列位置（LSN）。

File Trailer 与 File Header 类似，都是所有类型的页通用的。

索引

​ 由于数据页可能是不连续存储的，聚簇索引会根据主键值快速定位某些记录所在的页。每个页对应一个目录项（索引），每个目录项包括下边两个部分：

  • 页的用户记录中最小的主键值。
  • 页号。

image-20220503154745635

假设页28 中的记录都删除了， 页28 也就没有存在的必要了，那意味着目录项2也就没有存在的必要了，这就需要把目录项2 后的目录项都向前移动一下，为解决这种问题innodb复用了之前存储用户记录的数据页来存储目录项，为了和用户记录做一下区分，记录头的record_type设置为1

image-20220503154038546

从图中可以看出，实际用户记录都存放在B+树的最底层的节点上（叶子节点或叶节点），其余用来存放目录项的节点称为非叶子节点或者内节点，其中B+树最上边的那个节点也称为根节点。

聚簇索引

  • 页内的记录是按照主键的大小顺序排成一个单向链表。
  • 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
  • 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。
  • B+ 树的叶子节点存储的是完整的用户记录。

二级索引

  • 页内的记录是按照 索引列的大小顺序排成一个单向链表。
  • 各个存放用户记录的页也是根据页中记录的索引列大小顺序排成一个双向链表。
  • 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的索引列大小顺序排成一个双向链表。
  • B+ 树的叶子节点存储的并不是完整的用户记录，而只是索引列+主键这两个列的值。
  • 目录项记录中不再是主键+页号的搭配，而变成了索引列+页号 的搭配。

注：为保证在B+树的同一层内节点的目录项记录除页号这个字段以外是唯一的。对于二级索引的内节点的目录项记录的内容实际上是由三个部分构成的：索引列的值、主键值、页号。

联合索引

​ 联合索引本质上也是个二级索引，目录项存放定义联合索引的所有列+页号，按照定义的列的顺序排序。

页分裂

​ 为保证下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值，对页中的记录进行增删改操作的过程中，必须通过一些记录移动的操作来始终保证这个状态一直成立，这个过程称为页分裂 。

页合并

​ 当页中删除的记录达到MERGE_THRESHOLD（默认页体积的50%），InnoDB会判断最靠近的页（前或后），看看是否可以将两个页合并以优化空间使用。

注：

B+树索引的根节点创建后便不会再移动。只要我们对某个表建立一个索引，那么它的根节点的页号便会被记录到数据字典中。

InnoDB 的一个数据页至少可以存放两条记录。

MyISAM

​ InnoDB 中索引即数据(聚簇索引B+ 树的叶子节点中已经包含完整的用户记录)，MyISAM 的索引也是B+树，但索引和数据分开存储。将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件（这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录，通过行号而快速访问到一条记录）。物理上分别为.myi索引数据文件和.myd行数据文件（InnoDB 索引和行数据均在.idb文件中）。

​ MyISAM 中建立的索引全部都是 二级索引，索引信息存储到索引文件中。 MyISAM 会单独为表的主键创建一个索引，叶子节点中存储主键值 + 行号。也就是先通过索引找到对应的行号，再通过行号去找对应的记录！

MyISAM的行格式有定长记录格式（Static）、变长记录格式（Dynamic）和压缩记录格式（Compressed）

定长记录格式可以通过行号算出。对于变长记录格式，会直接在索引叶子节点处存储该条记录在数据文件中的地址偏移量。

索引使用原则

  1. 只为用于搜索、排序或分组的列创建索引。
  2. 为列的基数大的列创建索引。
  3. 索引列的类型尽量小。
  4. 可以只对字符串值的前缀建立索引。
  5. 只有索引列在比较表达式中单独出现才可以适用索引。
  6. 为了尽可能少的让 聚簇索引 发生页面分裂和记录移位的情况，建议让主键拥有 AUTO_INCREMENT 属性。
  7. 定位并删除表中的重复和冗余索引。
  8. 尽量使用 覆盖索引进行查询，避免 回表 带来的性能损耗。

默认索引（DB_ROW_ID）

​ 当我们不显式的为表定义主键，而且表中也没有 UNIQUE 索引，那么 InnoDB 存储引擎会默认为我们生成一个长度为6字节的row_id列作为主键，而且InnoDB 维护了一个全局的 Max Row ID，所有未定义主键的表都共享该Max Row ID ，每次插入一条数据，都把全局Max Row ID 当成主键id，然后全局Max Row ID 加1，该全局Max Row ID在代码实现上使用的是bigint unsigned类型，但实际上只给row_id留了6字节，这种设计就会存在一个问题：如果全局row_id一直涨，一直涨，直到2的48幂次-1时，这个时候再+1，row_id的低48位都为0，结果在插入新一行数据时，拿到的row_id就为0，存在主键冲突的可能性。

MySQL中字符集的转换

从发送请求到返回结果这个过程中伴随着多次字符集的转换，在这个过程中会用到如下3个系统变量

变量名 简介
character_set_client 服务器解码请求时使用的字符集
character_set_connection 服务器处理请求时会把请求字符串从 character_set_client 转为character_set_connection
character_set_results 服务器向客户端返回数据时使用的字符集

步骤图

img

Binlog

binlog第一个事件偏移量是4（MySQL 通过文件中的前 4 个字节，来判断这是不是一个 Binlog 文件）

参考链接

查看数据目录

1
2
3
4
5
6
mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+---------------------------------------+
| Variable_name | Value                                 |
+---------------+---------------------------------------+
| datadir       | D:\study\MySQL\MySQL Server 8.0\Data\ |
+---------------+---------------------------------------+

系统数据库简介

  • mysql

​ 存储了MySQL的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。

  • information_schema

​ 这个数据库保存着MySQL服务器维护的所有其他数据库的信息，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引等。这些信息并不是真实的用户数据，而是一些描述性信息，也称之为元数据。

  • performance_schema

​ 这个数据库里主要保存MySQL服务器运行过程中的一些状态信息，是对MySQL服务器的一个性能监控。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等等信息。

  • sys

​ 这个数据库主要是通过视图的形式把 information_schema 和 performance_schema 结合起来，可以方便的了解MySQL服务器的一些性能信息。

表空间

​ InnoDB 其实是使用 页（默认16kb）为基本单位来管理存储空间的。 InnoDB为更好的管理这些页，提出表空间抽象概念，它可以对应文件系统上一个或多个真实文件（不同表空间对应的文件数量可能不同）。每一个表空间可以被划分为多个页 ，数据库表数据就存放在某个表空间下的某些页里。

  • 系统表空间（system tablespace）

    ​ 系统表空间可以对应文件系统上一个或多个实际的文件，默认情况下， InnoDB 会在数据目录下创建一个名为 ibdata1 、大小为 12M 的文件，这个文件就是对应的系统表空间在文件系统上的表示。从MySQL5.5.7到MySQL5.6.6之间的各个版本中，表中的数据都会被默认存储到这个系统表空间。

  • 独立表空间(file-per-table tablespace)

    ​ 在MySQL5.6.6以及之后的版本中， InnoDB 并不会默认的把各个表的数据存储到系统表空间中，而是为每一个表建立一个独立表空间。在该表所属数据库对应的子目录下创建一个表示该独立表空间 的文件，文件名和表名相同，以 .ibd为扩展名。

  • 其他类型的表空间

    ​ 随着MySQL的发展，除了上述两种老牌表空间之外，现在还新提出了一些不同类型的表空间，比如通用表空间（general tablespace）、undo表空间（undo tablespace）、临时表空间（temporary tablespace）。

区 （ extent ）：

​ B+树的每个节点就是一个数据页，每一层中的页都会形成一个双向链表，以页为单位分配的话，相邻的页物理存储上可能不相邻，导致随机IO的产生，降低性能。所以引入区的概念，对于16KB的页来说，物理位置上连续的64个页就是一个区 ，一个区默认占用1MB(16KB*64 = 1MB)空间大小。不论是系统表空间还是独立表空间，都可以看成是由若干个区组成的，每256个区被划分成一组。

段（segment ）：

​ InnoDB 对 B+ 树的叶子节点和非叶子节点进行了区别对待，存放叶子节点的区的集合就算是一个段 ，存放非叶子节点的区的集合也算是一个段 。也就是说一个索引会生成2个段，一个叶子节点段，一个非叶子节点段。

Redo Log

redo日志会把事务在执行过程中对数据库所做的所有修改都记录下来，在之后系统奔溃重启后可以把事务所做的任何修改都恢复出来。

RedoLog 日志格式

type ：该条 redo 日志的类型。

space ID ：表空间ID。

page number ：页号。

data ：redo日志的具体内容。

注：type占一个字节。spacd ID和page number一般占用4个字节来存储，但是经过压缩后，可能使用更小的空间来存储。

日志类型

MySQL根据在页面中写入数据的多少划分了几种不同的 redo 日志类型：

  • MLOG_1BYTE （ type 字段对应的十进制数字为 1 ）：表示在页面的某个偏移量处写入1个字节的 redo 日志类型。
  • MLOG_2BYTE （ type 字段对应的十进制数字为 2 ）：表示在页面的某个偏移量处写入2个字节的 redo 日志类型。
  • MLOG_4BYTE （ type 字段对应的十进制数字为 4 ）：表示在页面的某个偏移量处写入4个字节的 redo 日志类型。
  • MLOG_8BYTE （ type 字段对应的十进制数字为 8 ）：表示在页面的某个偏移量处写入8个字节的 redo 日志类型。
  • MLOG_WRITE_STRING （ type 字段对应的十进制数字为 30 ）：表示在页面的某个偏移量处写入一串数据。
  • MLOG_REC_INSERT （对应的十进制数字为 9 ）：表示插入一条使用非紧凑行格式的记录时的 redo 日志类型。
  • MLOG_COMP_REC_INSERT （对应的十进制数字为 38 ）：表示插入一条使用紧凑行格式的记录时的 redo 日志类型。
  • ……

image-20220612003709986

MLOG_WRITE_STRING 类型的 redo 日志不能确定写入的具体数据占用多少字节，需要在日志结构中添加一个 len 字段：

image-20220612003633623

Mini-Transaction

有时一条语句在执行时候产生多个redolog日志，通过不可分割将这些redolog划分为若干个组。（如：1. 更新 Max Row ID 属性时产生的 redo 日志是不可分割的。向聚簇索引或某个二级索引对应 B+ 树的页面中插入一条记录时产生的 redo日志是不可分割的。主健或二级索引插入数据，可能导致页分裂，涉及多个页面的修改，由于对这些页面的更改都发生在 Buffer Pool 中，修改完页面之后需要记录一下相应的 redo 日志，在执行语句的过程中产生的所有redo日志被设计为不可分割的组。）同一组中的最后一条redo日志后边加上一条特殊类型的redo日志，该类型名称为 MLOG_MULTI_REC_END ， type十进制数字为 31 。该类型的 redo 日志结构很简单，只有一个 type 字段。

image-20220612215619962

区分Redolog数目

image-20220612215737654

如果 type 字段的第一个比特位为 1 ，代表该需要保证原子性的操作只产生了单一的一条 redo 日志，否则表示该需要保证原子性的操作产生了一系列的 redo 日志。

关系图

innodb把对底层页面中的一次原子访问的过程称之为一个 Mini-Transaction ，简称 mtr （向某个索引对应的 B+ 树中插入一条记录的过程算是一个 Mini-Transaction） 。一个事务可以包含若干条语句，每一条语句其实是由若干个 mtr 组成，每一个 mtr 又可以包含若干条 redo 日志。关系如下：

image-20220612222031914

其他

binlog与relay-log结构

参考

《MySQL是怎样运行的》