InnoDB 存储结构及索引

567 阅读18分钟

InnoDB存储结构

Mysql 是怎样运行的:从根上理解Mysql 读后总结

InnoDB 数据页

innodb将数据保存至磁盘中,所以每次读取数据时都会从磁盘中将数据读取到内存中,为了提高写入跟读取的效率,InnoDB 引入页的概念,类似于磁盘块,页为 InnoDB 每次读取和写入的最小单位,一般是16kb

InnoDB 页格式

页是 InnoDB 最小的操作单位,所以 InnoDB 设计了很多不同类型的页来管理不同的数据,首先了解的是数据页的格式

名称 描述 大小
File Header 页面的一些通用信息 38字节
Page Header 数据页专有的一些信息 56字节
Infimum + supermum 最大及最小记录,即数据的头尾指针,虚拟行记录 26字节
User Records 实际存储的用户记录 大小不确定
Free Space 页面尚未使用的空间 大小不确定
Page Directory 页目录,页面某些记录的相对位置,分级加速查找 大小不确定
File Tailer 校验页是否完整 8字节

InnoDB 行格式

在页格式中, User Records 用于存放实际用户需要存储的数据,而存储时会以行格式的形式进行保存,目前 InnoDB 总共有 4 种行格式,Compact、Redundant、Dynamic 和 Compressed

Compact行格式
名称 描述
变长字段长度列表 存放变长字段的长度信息
Null值列表 标记允许为空的数据是否为空
记录头信息 记录的一些通用信息
列1的真实值 真实的用户数据
... ... 真实的用户数据
  1. 变长字段长度列表

    Mysql 支持很多变长的数据类型,比如 varchar等等,还有char类型是固定长度的,但是字符集是utf8这类变长的字符集,这些变长的数据存储数据的长度是不确定的,所以还需要额外记录数据长度

    InnoDB 中的记录的真是数据是按列定义的顺序存放,而长度列表及Null值列表都是倒序的存放,这是因为每个行记录的指针,都在指向真实值,然后解析时会以指针两边同时进行解析,这样就可以一边解析长度,一边解析数据,提高解析效率

    注意:长度记录列表是依靠表字段定义顺序的反序记录,例如a, b, c 三个字段,都是varchar,ascii编码,一条记录三个长度值分别是1, 2, 3,所以在 变长字段长度列表记录 时记录值为 030201,每个长度占1字节

    这里就有一个问题,存储的字符串比较短的时候,1个字节就可以表示,比较大的时候就需要使用2个字节,InnoDB 对于这个有一个定义,字段允许的最大字符长度为m,字段的字符集最大占用字节数为w,例如ascii的w=1,gbk的w=2,utf8的w=3

    InnoDB 会首先检查 w*m,如果 w*m <= 255,那么就用1个字节表示变长的长度

    如果 w*m > 255,那么实际占用大小 <= 127,那么就用1个字节,否则就用两个字节,最高位为 0 表示单独的字节,1表示两个字节

    假如实际数据超过一个数据页保存的大小,那么就会将超过的数据保存到溢出页中,所以2个字节足够使用

    另外变长字段长度列表只保存非Null值列表中的数据,也就是说null值都不在记录范围内

  2. Null值列表

    如果把 null 值都记录到真实数据中,会比较占用空间,所以会把 null 值记录都记录到 Null值列表 中,InnoDB 的处理过程如下:

    1. 首先统计表中允许存储 null 值的列

    主键默认都是 NOT NULL 修饰的,所以统计的时候都不会计算,比如 a, b, c, d 四个字段,c 为NOT NULL 修饰的,那么需要记录的就是 a, b, d 三个字段

    1. 如果表中没有允许存储 null 值的列,那么 Null值列表 就存在了,否则每个字段用一个位标志,1表示为null,按列字段定义顺序反序的保存

    2. Null值列表由整数个字节表示,字段不足则高位补0

  3. 记录头信息

    记录头固定有 5 个字节组成,用于描述记录头信息

    名称 大小(bit) 描述
    预留位1 1 没有使用
    预留位2 1 没有使用
    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 下一条记录的相对位置
  4. 记录真实值

    InnoDB 在记录真实值时,除了自己定义列的数据,还额外记录一些隐藏列

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

    InnoDB 表对主键的生成策略是,会优先使用用户自定义的主键作为主键,如果用户没有自定义,则会选取一个 Unique 的键作为主键,如果没有则会自动生成一个 DB_ROW_ID 的隐藏列作为主键

    DB_TRX_IDDB_ROLL_PTR 是固定会添加的,DB_ROW_ID 只有没有主键时才会自动生成

    注意:

    1. 如果是定长类型,如果真是数据没有达到指定的长度,那么仍然会使用指定长度的空间存放数据,其余空间填充空格字符
    2. 如果在 Null值列表 中记录了值,那么在记录真实值的时候,就不会再记录这个字段
Redundant行格式
名称 描述
字段长度偏移列表 每个字段的长度偏移信息
记录头信息 记录的一些通用信息
列1的真实值 真实的用户数据
... ... 真实的用户数据
  1. 字段长度偏移列表

    区别于 Compact行格式,Redundant行格式对所有的字段(包括隐藏列)都计算了长度偏移,然后倒序存放,

  2. 记录头

    Redundant行格式记录头占用6个字节

    名称 大小(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 表示下一条记录的相对位置

    1byte_offs_flag 取值,当记录真实数据总大小 <= 127 时,就为 0 表示 1 个字节记录偏移长度,当总大小 > 127 且 <= 32767 时,是为1表示2个字节记录偏移长度。当长度 >= 32767时,本页仅记录 768 个字节数据和 20 字节个溢出页面地址,所以偏移长度两个字节依然够用

    null值的处理

    在 Redundant 行格式中并没有null值列表,而是在每个长度便宜最高位用来表示,1表示该字段为null,0表示该字段为非null

Dynamic 和 Compressed 行格式

MySQL 5.7 默认都是使用 Dynamic 行格式,这两个行格式跟 Compact 格式很相似,但是在处理溢出数据时,处理的方式不一样,Dynamic 行格式会直接将所有的数据都存储到溢出页中,Compressed 行格式跟 Dynamic 行格式的区别在于会采用压缩算法对页面进行压缩,以节省空间

InnoDB 数据存储

在页格式中,没插入一条记录,就会使用 Free Space 空间,按指定的行格式,插入数据,插入的所有数据就是 User Records

在 行格式的记录头中,每插入一条记录,就会在 next_record 中指向下一条记录值的真实数据地址偏移量,所以所有的记录都会被串联成一个链表

当数据记录被删除的时候,跟链表的记录删除方式相似,将上一条记录的 next_record 指向下一条记录,然后将删除记录头中的 delete_mask 置为1表示已经删除,然后统一记录到一个垃圾列表中进行标记,这里主要是考虑磁盘数据重新排列的性能消耗

如果记录是b+树每层非叶子节点的最小记录,都会将min_rec_mask 置为 1

然后记录头中的 heap_no 表示该条记录在本页中的序号,所有记录会以记录主键的大小进行增序排列,不管我们有没有插入记录,InnoDB 都会自定创建两条伪记录为最小记录和最大记录,这两条记录非常简单,由5字节记录头信息和8字节大小的固定部分组成

因为最大和最小记录不是用户插入的数据,所以这两条记录被单独记录在 Infimum + supermum空间内

record_type 表示记录的类型,0表示普通记录,1表示b+树的非叶子节点记录,2表示最小记录,3表示最大记录,在 InnoDB 中,非叶子节点不保存用户记录,空间都用于记录下级记录地址,所以需要单独标记

Page Directory 页目录

如果要从一个数据页中查找一条记录,那么按链表的搜索方式,需要从最小记录开始遍历,直到查找到该记录的值为止,为了加速搜索,InnoDB 引入了页目录来加速这个搜索过程

InnoDB 会将所有的记录(包含最大记录跟最小记录)分成几个组,然后每个组的最后一条记录(最大纪录)的记录头中的 n_owned 会记录这个组中有几个记录,然后将每个组的最后一条记录的地址取出存放到靠近页尾的 Page Directory 中,也就是页目录了

对于页目录有以下的规定:

  • 最小记录所在的分组只能有 1 条记录,最大记录所在的分组拥有的记录条数只能在 1~8 条之间,剩下的分组中记录的条数范围只能在是 4~8 条之间
  • 初始情况下,页目录中仅有最大记录跟最小记录两个分组,之后每插入一条记录,都会在页目录中找到比主键值大且差值最小的组,然后对应分组的 n_owned 字段 +1,直到该组的数据到8个
  • 当超过 8 条记录后,就会分裂为两个组,然后在页目录中增加一个分组来记录新增分组的最大记录的地址偏移量

有了页目录之后,数据查找时,就通过页目录进行二分法搜索,找到数据所在分组,然后在分组内进行遍历搜索,因为每个分组的数量是比较小的,所以比之前的性能会高不少

Page Header 页面头部

为了能快速获取该数据页面的状态信息,比如本页存放了多少条记录,第一条记录地址是多少,页目录中有多少分组,所以定义了 Page Header 专门存放各种状态信息,这部分数据占用 56 字节

名称 占用大小 描述
PAGE_N_DIR_SLOTS 2字节 页目录中的分组数量
PAGE_HEAP_TOP 2字节 还未使用的空间最小地址,该地址之后都是 Free Space
PAGE_N_HEAP 2字节 标记本页中记录数量(含最大最小及删除的记录)
PAGE_FREE 2字节 第一个删除记录的地址,串联起来就是一个垃圾链表
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页定义
PAGE_N_DIR_SLOTS

主要记录页目录中的分组数量

PAGE_HEAP_TOP

Free Space 的起始地址,后续需要从 Free Space 分配空间都会修改这个值

PAGE_N_HEAP

标记本页中记录数量(含最大最小及删除的记录)

PAGE_FREE

删除的记录并不会直接移除,而是被标记为已经删除,并且在记录的链表中移除,然后加入垃圾链表中,这个字段就是存放垃圾链表的第一个记录的地址

PAGE_DIRECTION

插入的记录,如果比上一条记录的主键值要大,则记录插入方向是右边,反之则是左边,这个信息由 PAGE_DIRECTION 记录

PAGE_N_DIRECTION

假设最近几次的插入记录的方向都是一样的,InnoDB 会将沿着同一个方向插入记录的条数记录下来,如果方向改变了则计数清零

File Header 文件头部

Page Header 是数据页的各种状态信息,而 File Header 则是针对所有页面通用的一些通用信息,比如这个页的编号是多少,上一页下一页是多少等等,总共占用 38 字节

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

代表该页面的一个校验和,用于校验页面数据是否完整

FIL_PAGE_OFFSET

每个页面都有一个唯一的页号,InnoDB 通过页号来定位页面的位置

FIL_PAGE_TYPE

页面类型,InnoDB 为了不同的功能设计了不同的页面,用该字段标志每个页面的类型,已有的类型包括 系统页、数据页、事务系统数据、表空间头部信息等等

FIL_PAGE_PREV 及 FIL_PAGE_NEXT

InnoDB 以页进行数据的存放,所以页之间通过这两个地址信息标记前驱和后继,所以整个表的数据都是一个以页为单位的双向链表,通过链表遍历即可访问所有的记录

File Trailer

File Trailer 跟 File Header 一样,所有页面通用,占用 8 字节分为两个部分:

前 4 字节代表页的校验和:

InnoDB 数据引擎会将数据持久化到磁盘,然而磁盘的速度比较慢,所以可能会出现写入一半后断电之类的,导致数据不对的问题,所以在尾部也增加一个校验和,正常情况首部和尾部的校验和都是一样的,如果出现写入一半的情况,首部和尾部的校验和不一致

后 4 字节表示最后修改对应的日志序列位置(LSN):

这部分也是用于校验页面完整性

B+树索引

数据搜索过程

InnoDB 中所有的记录都保存在数据页中,各个数据页组成一个双向链表,然后所有的记录在每个数据页中保存一个单向链表,通过页目录进行二分法快速找到数据所在分组,然后在分组内遍历快速找到指定的数据

这时有个前提就是,查询条件是通过主键进行查找,因为页目录及页面的记录都是按主键的大小进行排序的,所以能按上述的方式快速的查找,但是如果不是主键,那就必须要再页面内的单向链表中进行遍历搜索

很多页的数据查找

大部分情况下,一个表中都会有很多的数据记录,所以在页面内搜索之前,都需要先定位数据在哪个数据页中,在没有索引的情况下,就需要通过数据页的双向链表中进行遍历来查找数据,这样显然是非常耗时的

为了加速数据的查找,每个数据页面将会组合成一个 b+ 树的结构,所有的目录仅保存下级页面的地址和主键值,所有的数据都保存在叶子节点上,分级之后,即可通过页目录的二分法优化快速定位数据所在的子页面,然后去子页面中再次查找

从根上理解 MySQL 插图

聚簇索引

InnoDB 的树属于聚簇索引,聚簇索引具有以下的特点:

  1. 使用记录主键值的大小进行记录和页的排序
    • 页内的记录时按照主键的大小顺序排列的一个单链表
    • 各个存放用户记录的页面根据用户的主键大小排序组成一个双向链表
    • 存放目录项的页面,也根据页中用户记录的大小顺序组成一个双向链表
  2. b+ 树的叶子节点将存放完整的用户记录
二级索引

在聚簇索引中,只有通过主键搜索,才可以达到效率优化的目的,所以需要通过其他的数据进行搜索时,可以建立更多的二级索引,建立的 b+ 树跟主键的索引树是一样的,区别在于二级索引并不保存完整的用户记录,而是保存用户记录的主键,然后通过主键再去聚簇索引中再查找一个。另外排序比较的字段是指定的表列

联合索引

可以同时通过多个列简历二级索引,这是比较的字段会有多个,但是每次都会先比较第一个字段,如果第一个字段相同,再比较第二个字段,以此类推。保存的数据与普通单个列的二级索引没有区别,都是保存数据记录的主键用于查找

内节点的唯一性

对于二级索引为例,如果仅保存下级页面的地址和索引列的值时,当有相同的列值跨越多个表的,就不确定新插入的数据到底应该插入到哪个表中,所以二级索引建立时,默认都会增加一个主键值,当索引列都相同的时候,就会通过主键值再判断,因为主键值是唯一的,所以就能唯一确定新的记录需要插入到哪个页中

一个页面最少存储2条记录

当一个表的增删比较频繁的时候,页面就会不断的分裂,在极端的情况下,每个页面都被删除的仅剩一条记录,那么整个 b+ 树的空间利用率是非常低的,所以 InnoDB 规定,每个页最少存储两条记录,当数据记录不够时,就会与其他的页面进行合并,并且删除回收这个页面

MyISAM 索引介绍

MyISAM 存储引擎的索引也是树状的,但是跟 InnoDB 不同的是,MyISAM 采用的是非聚簇索引,所以所有的用户记录都会按用户的插入顺序单独存放到一个文件中,称之为数据文件,这个文件并不会按页进行切分,而是有多少数据就往这个文件中加入多少行,然后通过行号即可快速访问

每条记录也会包含一个记录头,用来存放这条记录的一些状态信息,单独通过数据文件,我们并不能通过主键来进行快速搜索某一条记录,所以 MyISAM 会创建一个单独的索引文件,而索引的叶子节点并不存放用户数据,而是访问数据文件的行号,所以每次通过主键查询,MyISAM 会从索引中定位数据的行号,然后通过行号进行回表查询再返回数据,当然 MyISAM 通过行号定位数据的效率非常高

需要其他的列建立索引与联合索引,跟主键索引基本一致,保存的也都是数据行号

SQL 语法
CREATE TALBE (
	各种列信息 ...
	[KEY|INDEX] 索引名 (被索引的单个列或多个列)
)

ALTER TABLE 表名 ADD [INDEX|KEY] 索引名 (被索引的单个列或多个列);
ALTER TABLE 表名 DROP [INDEX|KEY] 索引名;

-- KEY 和 INDEX 是同义词,任选一个即可