InnoDB 数据存储结构

1、单位

(1)磁盘与内存交互基本单位

(2)数据库管理存储空间的基本单位

(3)数据库 I/O 操作的最小单位

2、索引信息以及数据记录都存储在页结构中

3、页结构概述

(1)页可以不在物理结构上相连,只要通过双向链表相关联即可

(2)每个数据页中的记录,按照主键值从小到大的顺序,组成一个单向链表

(3)每个数据页都会为其记录生成一个页目录,在通过主键查找某条记录时,可以在页目录中使用二分法快速定位到对应的槽,然后再遍历该槽对应分组中的记录即可快速找到指定的记录

3、页的大小

(1)不同的数据库管理系统的页大小不同

(2)查看页的大小

SHOW VARIABLES LIKE '%innodb_page_size%';

(3)InnoDB 将数据划分为若干个页,InnoDB 中页的大小默认为 16KB

(4)SQL Server 页的大小为 8KB

(5)Oracle 用块(Block)代表页(Page),Oralce 支持的块大小为 2KB、4KB、8KB、16K8、32KB、64KB

 

页的上层结构

1、区(Extent)

(1)比页大一级的存储结构

(2)在 InnoDB 存储引擎中,区在文件系统是一个连续分配的空间,一个区会分配 64 个连续的页

(3)InnoDB 页的默认大小 16KB,所以一个区默认大小 64 * 16KB = 1MB

2、段(Segment)

(1)由一个或多个区组成

(2)段不要求区与区之间是相邻

(3)段是数据库中的分配单位,不同类型的数据库对象以不同的段形式存在

(4)当创建数据表、索引时,创建对应的段,如:创建一张表时,则创建一个表段;创建一个索引时,则创建一个索引段

3、表空间(Tablespace)

(1)一个逻辑容器

(2)表空间存储的对象是段

(3)在一个表空间中可以有一个或多个段,但是一个段只能属于一个表空间

(4)数据库由一个或多个表空间组成

(5)表空间从管理上可以划分为:系统表空间、用户表空间、撤销表空间、临时表空间等

 

页的内部结构

1、按类型划分:数据页(保存 B+ 树节点)、系统页、Undo 页、事务数据页等

2、数据页的存储空间被划分为七个部分

名称 占用空间大小(字节) 说明
文件头(File Header) 38 描述页的通用信息
页面头(Page Header) 56 页的状态信息(数据页专有信息)
页面中的最小记录、最大记录(Infimum + Supremum) 26 两个虚拟的行
用户记录(User Records) 不确定 用户存储的行内容
空闲空间(Free Space) 不确定 页中还没有被使用的空间
页目录(Page Directory) 不确定 存储用户记录的相对位置
文件尾(File Tailer) 8 校验页是否完整

 

文件头

1、描述各种页的通用信息

2、组成

名称 占用字节 描述
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 页属于哪个表空间

(1)FIL_PAGE_OFFSET:4 字节,每一个页都有一个单独的页号,InnoDB 通过页号可以唯一定位一个页

(2)FIL_PAGE_TYPE:2 字节,代表当前页的类型

类型名称 十六进制 描述
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 溢出页
FIL_PAGE_INDEX 0x45BF 索引页,即数据页

(3)FIL_PAGE_PREV、FIL_PAGE_NEXT:都占用 4 字节,通过建立一个双向链表,实现数据也的链接,保证这些页之间不需要是物理上的连续,而是逻辑上的连续

3、检验页的完整性

(1)FIL_PAGE_SPACE_OR_CHKSUM:4 字节,代表当前页面的校验和(checksum)

(2)InnoDB 以页为单位把数据加载到内存中处理,如果该页中的数据在内存中被修改,那么在修改后的某个时间需要把数据同步到磁盘中

(3)为了检测一个页是否完整,这时可以通过文件尾的校验和(checksum 值)与文件头的校验和做比对,如果两个值不相等则证明页的传输有问题,需要重新进行传输,否则认为页的传输已经完成

 

文件尾

1、前 4 个字节

(1)代表页的校验和,该部分是和文件头中的校验和相对应

(2)每当一个页面在内存中发生修改时,在刷新之前就计算页面的校验和

(3)因为 File Header 在页面之前,所以 File Header 中的校验和会被首先刷新到磁盘,当完全写完后,校验和也会被写到页的尾部

(4)如果页面刷新成功,则页首和页尾的校验和应该是一致的,二者不同则意味着刷新期间发生了错误

2、后 4 个字节

(1)代表页面被最后修改时,对应的日志序列位置(LSN)的后 4 个字节

(2)正常情况下应该与 File Header 部分的 FIL_PAGE_LSN 的后 4 字节相同

(3)该部分也是为了校验页的完整性,如果首部和尾部的 LSN 值校验不成功,说明同步过程出现了问题

3、File Trailer 与 File Header 类似,都通用于所有类型的页

 

空闲空间

1、存储的记录会按照指定的行格式,存储到用户记录

2、在开始生成页时,并没有用户记录,每当插入一条记录,都会从空闲空间中,申请一个记录大小的空间划分到用户记录

3、当空闲空间全部被用户记录替代后,即该页已经使用完,若有新的记录插入,就需要去申请新的页

 

用户记录

1、按照指定的行格式存放

2、相互之间形成单链表

 

最大记录(Supremum)、最小记录( Infimum)

1、记录可以比大小,对于一条完整的记录来说,比较记录的大小就是比较主键的大小

2、InnoDB 规定的最小记录与最大记录,都是由 5 字节大小的记录头信息,和 8 字节大小的一个固定的部分组成

3、两条虚拟行记录,可以理解为单向链表的链表头、链表尾

(1)最小记录的下一条记录,就是本页主键值最小的用户记录

(2)本页中主键值最大的用户记录,下一条记录就是最大记录

4、最小记录真实数据信息是固定的:69 6E 66 69 6D 75 6D 00,代表单词 Infimum

5、最大记录真实数据信息是固定的:73 75 70 72 65 6D 75 6D,代表单词 Supremum

 

页目录

1、在页中,记录是以单向链表的形式进行存储

2、单向链表的特点就是插入、删除非常方便,但是检索效率不高,最差的情况下,需要遍历链表上的所有节点才能完成检索

3、页目录

(1)专门给记录做一个目录,通过二分查找法的方式进行检索,提升效率

(2)将所有的记录分成几个组,这些记录包括最小记录、最大记录,但不包括标记为已删除的记录

(3)第一组,即最小记录所在的分组,只有 1 个记录

(4)最后一组,即最大记录所在的分组,会有 1~ 8 条记录

(5)其余组记录数量在 4 ~ 8 条之间

(6)分组作用:除第一组(最小记录所在组)以外,其余组的记录数会尽量平分

(7)n_owned:在每个组中最后一条记录的头信息,存储该组一共有多少条记录

(8)页目录:存储每组最后一条记录的地址偏移量(该记录的真实数据与页面中第 0 个字节之间的距离),这些地址偏移量会按照先后顺序存储起来到靠近页尾部

(9)每组的地址偏移量也被称之为槽(slot),每个槽(2 字节)相当于指针,指向不同组的最后一个记录,页目录就是由多个槽组成

(10)一个正常的页面是 16 KB 大小,即 16384 字节,而 2 字节可以表示的地址偏移量范围是 0 ~ 65535,所以使用 2 字节足够表示一个槽

4、插入数据

(1)在初始情况下,一个数据页中只有 Infimum 记录、Supremum 记录,它们分属于两个分组,页目录中也只有两个槽,分别代表 Infimum 记录、Supremum 记录在页面中的地址偏移量

(2)对于 Infimum 记录、Supremum 录来说,它们虽然没有主键值,但是人为规定它们是一个页面中最小和最大的记录

(3)每插入一条记录,都会从页目录中,找到主键值比本记录的主键值大,并且差值最小的槽,然后把该槽对应的记录的 n_owned 值加 1,表示本组内添加一条记录,直到该组中的记录数等于 8 个

(4)在一个组中的记录数等于 8 个后,再插入一条记录时,会将组中的记录拆分成两个组,一个组中 4 条记录,另一个 5 条记录,这个过程会在页目录中,新增一个槽,来记录这个新增分组中,最大的那条记录的偏移量

5、在一个数据页中查找指定主键值的记录

(1)通过二分法确定该记录所在的槽,根据上一个槽,查找该槽所在分组中主键值最小的那条记录

(2)通过记录 next_record 属性,遍历该槽所在的组中的各个记录

 

页面头

1、获取一个数据页中存储的记录的状态信息,占用固定 56 个字节

名称 占用字节 描述
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 页定义

2、PAGE_DIRECTION

(1)表示最后一条记录插入方向的状态

(2)若新插入的一条记录的主键值比上一条记录的主键值大,则这条记录的插入方向是右边,反之则是左边

3、PAGE_N_DIRECTION

(1)若连续几次插入新记录的方向都是一致的,InnoDB 会把沿着同一个方向插入记录的条数记下来,条数使用 PAGE_N_DIRECTION 表示

(2)若最后一条记录的改变插入方向,这个状态的值会被清零重新统计

 

数据页角度的 B+ 树查询

1、一棵 B+ 树按照节点类型,分成两部分

(1)叶子节点:B+ 树最底层的节点,节点的高度为 0,存储行记录

(2)非叶子节点:节点高度大于 0,存储索引键和页面指针,并不存储行记录本身

2、B+ 树检索记录

(1)首先是从 B+ 树的根开始,逐层检索,直到找到叶子节点,即找到对应的数据页为止

(2)将数据页加载到内存中,页目录中的槽(slot)采用二分查找的方式,找到一个粗略的记录分组

(3)再在分组中通过链表遍历的方式查找记录

3、普通索引、唯一索引的查询效率

(1)唯一索引就是在普通索引上增加了约束性,即关键字唯一,找到关键字就停止检索

(2)普通索引,可能会存在用户记录中的关键字相同的情况,根据页结构的原理,当读取一条记录时,不是单独将这条记录从磁盘中读出去,而是将这个记录所在的页加载到内存中进行读取

(3)InnoDB 存储引擎的页大小为 16KB,在一个页中可能存储着上千个记录,在普通索引的字段上进行查找,即在内存中多几次判断下一条记录的操作

(4)对于 CPU 来说,这些操作所消耗的时间是可以忽略不计的,所以对一个索引字段进行检索,采用普通索引或唯一索引在检索效率上基本上没有差别

 

InnoDB 行格式 / 记录格式

1、数据以行为单位来向表中插入数据,这些记录在磁盘上的存放方式也被称为行格式 / 记录格式

2、InnoDB 设计 4 种不同类型的行格式:Compact、Redundant、Dynamic、Compressed

3、查看 MySQL 默认行格式

SELECT @@innodb_default_row_format;

(1)MySQL 8、MySQL 5.7:Dynamic

4、查看具体表使用的行格式

SHOW TABLE STATUS like '表名'\G

5、指定行格式的语法

(1)创建表时

CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称;

(2)修改已有表

ALTER TABLE 表名 ROW_FORMAT=行格式名称;

 

COMPACT 行格式

1、一条完整的记录

(1)记录的额外信息:变长字段长度列表、NULL 值列表、记录头信息

(2)记录的真实数据:列 1 值,……,列 n 值

2、变长字段长度列表

(1)在 Compact 行格式中,把所有变长字段的真实数据占用的字节长度,都存放在记录的开头部位,从而形成一个变长字段长度列表

(2)注意:存储的变长长度和字段顺序是反过来的,如:两个 VARCHAR 字段在表结构的顺序是 a(10),b(15),则在变长字段长度列表中存储的长度顺序是 15,10

(3)变长字段长度列表中只存储值为非 NULL 的列的内容长度,不存储值为 NULL 的列的内容长度

(4)不是所有行都有变长字段长度列表,如果表中所有的列都不是变长的数据类型或者所有列的值都是 NULL,就不需要有变长字段长度列表

(5)对于 CHAR(M) 类型的列来说,当列采用的是定长编码的字符集时,该列占用的字节数不会被加到变长字段长度列表;而如果采用变长编码的字符集时,该列占用的字节数就会被加到变长字段长度列表

(6)假设:某个字符集中最多需要 W 字节来表示一个字符、指定存储最多 M 个字符(注意是字符不是字节)、实际存储的字符串占用 L 字节,则 M * W 为字符串最多占用的字节数

(7)若 M * W <= 255,那么使用 1 字节来表示真实数据占用的字节数

(8)InnoDB 在读取记录的变长字段长度列表时先查看表结构,如果某个变长字段允许存储的最大字节数 <= 255,可以认为只使用 1 字节来表示真实数据占用的字节数

(9)如果 M * W > 255,且 L <= 127,则用 1 字节来表示真实数据占用的字节数

(10)如果 M * W > 255,且 L > 127,则用 2 字节来表示真实数据占用的字节数

(11)InnoDB 在读取记录的变长字段长度列表时先查看表结构,如果某个变长字段允许存储的最大字节数 > 255,如何区分它正在读的某个字节是一个单独的字段长度还是半个字段长度?使用该字节的第一个二进制位作为标志位:如果该字节的第一个位为 0,该字节就是一个单独的字段长度;如果该字节的第一个位为 1,该字节就是半个字段长度

3、NULL 值列表

(1)Compact 行格式统一管理可以为 NULL 的列,存在一个标记为 NULL 值列表中,如果表中没有允许存储 NULL 的列,则 NULL 值列表也不存在

(2)存储 NULL 原因:数据都需要对齐,如果没有标注 NULL 值的位置,有可能在查询数据时出现混乱

(3)如果使用一个特定的符号,放到相应数据位表示空置,虽然能达到效果,但是很浪费空间,所以直接在行数据得头部,使用一块空间专门记录该行数据,哪些是非空数据,哪些是空数据

(4)COMPACT 行格式把一条记录中值为 NULL 的列统一管理起来,存储到 NULL 值列表中,统计表中允许存储 NULL 的列有哪些,主键列以及使用 NOT NULL 修饰的列都是不可以存储 NULL 值的,所以在统计的时候不会把这些列算进去

(5)格式:二进制位的值为 1 时,代表该列的值为 NULL;二进制位的值为 0 时,代表该列的值不为 NULL

(6)注意:顺序是反过来存放的

 

记录头信息

名称 占用大小(单位:bit) 描述
预留位1 1 没有使用
预留位2 1 没有使用
delete_mask 1 标记该记录是否被删除
mini_rec_mask 1 B+ 树的每层非叶子节点中的最小记录都会添加该标记
n_owned 4 表示当前记录拥有的记录数,一个页面中的记录会被分成若干个组,每个组中有一个记录是“带头大哥”,其余的记录都是“小弟”,“带头大哥”记录的 n_owncd 代表该组中所有的记录条数,“小弟"记录的n_owned 值都为 0
heap_no 13 表示当前记录在记录堆的相对位置信息
record_type 3 表示当前记录的类型,0 表示普通记录,1 表示 B+ 树非叶子节点记录,2 表示最小记录(lnfimum 记录),3 表示最大记录(Supremum 记录)
next_record 16 表示下一条记录的相对位置

1、delete_mask

(1)标记着当前记录是否被删除,占用 1 个二进制位

(2)值为 0:代表记录并没有被删除

(3)值为 1:代表记录被删除

2、逻辑删除

(1)被删除的记录之所以不立即从磁盘上移除,是因为移除它们之后其他的记录在磁盘上需要重新排列,导致性能消耗

(2)所有被删除掉的记录都会组成一个所谓的垃圾链表,在这个链表中的记录占用的空间称之为可重用空间,之后如果有新记录插入到表中的话,可能把这些被删除的记录占用的存储空间覆盖掉

3、min_rec_mask

(1)B+ 树的每层非叶子节点中的最小记录都会添加该标记

(2)min_rec_mask 值为 1

(3)非 B+ 树的非叶子节点中的最小记录值为 1

4、record_type

(1)表示当前记录的类型

(2)0:表示普通用户记录

(3)1:表示 B+ 树非叶节点记录(目录项记录)

(4)2:表示最小记录

(5)3:表示最大记录

5、heap_no

(1)表示当前记录在本页中的位置

(2)MySQL 自动给每个页里加两个记录,称为伪记录 / 虚拟记录,两个伪记录一个代表最小记录(Infimum),一个代表最大记录(Supremum)

(3)最小记录、最大记录的 heap_no 值分别是 0 和 1,即它们的位置最靠前

(4)InnoDB 设计规定:任何用户记录都比 Infimum 记录大 ,任何用户记录都比 Supremum 记录小

(5)虽然 Infimum 记录、Supremum 记录没有主键值,但 InnoDB 设计规定 lnfimum 记录是一个页面中最小的记录、Supremum 记录是一个页面中最大的记录

(6)堆中记录的 heap_no 值在分配之后就不会发生改动了,即使之后删除了堆中的某条记录,这条被删除记录的 heap_no 值也仍然保持不变

6、n_owned:页目录中每个组中最后一条记录头信息,存储该组一共有多少条记录

7、next_record

(1)表示从当前记录的真实数据,到下一条记录的真实数据的地址偏移量

(2)如果该属性值为 +n,说明当前记录的下一条记录在当前记录的后面,从当前记录的真实数据的地址处向后找 n 字节,便是下一条记录的真实数据

(3)如果该属性值为 -n,说明当前记录的下一条记录在当前记录的前面,从当前记录的真实数据的地址处向前找 n 字节,便是下一条记录的真实数据

(4)注意,下一条记录指的并不是插入顺序中的下一条记录,而是按照主键值由小到大的顺序排列的下一条记录

(5)记录按照主键从小到大的顺序形成单向链表

(6)Infimum 记录的下一条记录就是本页中主键值最小的用户记录

(7)本页中主键值最大的用户记录的下一条记录就是 Supremum 记录,Supremum 记录的 next_record 值为 0,即 Supremum 记录就是单向链表中的最后一个节点

(8)next_record 为什么指向记录头信息和真实数据之间的位置,而不是指向整条记录的开头位置,即记录的额外信息开头的位置?因为这个位置向左读取就是记录头信息,向右读取就是真实数据,而且变长字段长度列表、NULL 值列表中的信息都是逆序存放,这样可以使记录中位置靠前的字段和它们对应的字段长度信息在内存中的距离更近,这可能会提高高速缓存的命中率

(9)当数据页中存在多条被删除的记录时,可以使用这些记录的 next_record 属性将这些被删除的记录组成一个垃圾链表,以备之后重用这部分存储空间

 

记录的真实数据

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

1、一个表没有手动定义主键,则会选取一个 Unique 键作为主键

2、InooDB 表的主键生成策略

(1)优先使用用户自定义的主键作为主键

(2)如果用户没有定义主键,则选取一个不允许存储 NULL 值的 UNIQUE 键作为主键

(3)如果表中不允许存储 NULL 值的 UNIQUE 键都没有定义,则 InooDB 会为表默认添加一个名为 DB_ROW_ID 隐藏列作为主键

 

COMPACT 的 CHAR(M) 列的存储格式

1、当 CHAR(M) 列采用定长编码的字符集时,该列占用的字节数不会被加到变长字段长度列表

2、当 CHAR(M) 列采用变长编码的字符集时,该列占用的字节数就会被加到变长字段长度列表

3、COMPACT 行格式下,采用变长编码字符集的 CHAR(M) 类型的列要求至少占 M 个字节,这主要是希望在将来更新该列时, 在新值的字节长度大于旧值字节长度但不大于 M 个字节,可以在该记录处直接更新,而不是在存储空间中再重新分配一个新的记录空间,导致原有的记录空间成为碎片

 

REDUNDANT 行格式

1、字段长度偏移列表:COMPACT 行格式开头是变长字段长度列表,而 REDUNDANT 行格式的开头是字段偏移列表,它与变长字段长度列表相比有两处不同

(1)REDUNDANT 行格式会把该条记录中所有列(包括隐藏列)的长度信息,都按照逆序存储到字段长度偏移列表

(2)计算列值长度方式不像 COMPACT 行格式直观,它是采用两个相邻偏移量的差值来计算各个列值的长度

2、记录头信息

名称 大小(位) 描述
预留位 1 1 没有使用
预留位 2 1 没有使用
deleted_flag 1 标记该记录是否被删除
min_rec_flag 1 B+ 树的每层非叶子节点中的最小的目录项记录都会添加该标记
n_owned 4 表示当前记录拥有的记录数,一个页面中的记录会被分成若干个组,每个组中有一个记录是“带头大哥”,其余的记录都是“小弟”,“带头大哥”记录的 n_owncd 代表该组中所有的记录条数,“小弟"记录的n_owned 值都为 0
heap_no 13 表示当前记录在记录堆的相对位置信息
n_field 10 表示记录中列的数量
1byte_offs_flag 1 标记字段长度偏移列表中每个列对应的偏移量是使用 1 字节还是 2 字节表示
next_record pointer 16 表示页中下一条记录的相对位置

3、1byte_offs_flag

(1)当记录的真实数据占用的字节数 <= 127(十六进制 0x7F,二进制 01111111)时,每个列对应的偏移量占用 1 字节,如果整个记录的真实数据占用的存储空间都不大于 127 字节,那么每个列对应的偏移量值肯定也就不大于 127,也就可以使用 1 字节来表示偏移量

(2)当记录的真实数据占用字节数 > 127,但 <= 32767(十六进制 0x7FFF,二进制 0111111111111111)时,每个列对应的偏移量占用 2 字节

(3)当记录的真实数据占用字节数 > 32767,此时部分记录已经存放到溢出页中,在本页中只保留前 768 字节、20 字节(溢出页面地址 + 其他信息),这种情况下只使用 2 字节存储每个列对应的偏移量就够了

(4)当它的值为 1 时,表明使用 1 字节存储偏移量

(5)当它的值为 0 时,表明使用 2 字节存储偏移量

4、REDUNDAN 行格式中 NULL 值的处理

(1)将列对应的偏移量值的第一个比特位作为是否为 NULL 的依据

(2)在解析一条记录的某个列时,首先看一下该列对应的偏移量的 NULL 比特位是否为 1,如果为 1,那么该列的值为 NULL,否则不为 NULL

(3)这解释了,既然一个字节表示的范围是 0 ~ 255,为什么在记录真实数据占用的存储空间大于 127 字节时,就采用 2 字节表示各个列的偏移量, 而不是大于 255 字节时,再采用 2 字节表示各个列的偏移量

(4)对于值为 NULL 的列来说,该列的类型是否为变长类型,决定了该列在记录的真实数据处的存储方式

(5)如果存储 NULL 值的字段是定长类型,则 NULL 也将占用记录的真实数据部分,并把该字段对应的数据使用 0x00 字节填充

(6)如果存储 NULL 值的字段是变长数据类型,则不在记录的真实数据部分占用任何存储空间

5、CHAR(M) 列的存储格式

(1)REDUNDANT 行格式,不管该列使用的字符集是什么,只要使用 CHAR(M) 类型,该列的真实数据占用的存储空间大小,就是该字符集表示一个字符最多需要的字节数 * M

(2)将来在对该列进行更新时,可以直接在原位置更新,而不需要为记录申请新的存储空间,同时可能会浪费一些存储空间

 

溢出列

1、行溢出现象:一个页存放不了一条记录

2、页的拓展

(1)对于 COMPACT REDUNDANT 行格式来说,如果某一列中的据非常多,则在本记录的真实数据处只会存储该列前 768 字节的数据,以及一个指向其他页的地址,然后把剩下的数据存放到其他页中

(2)溢出页(Off Page):存储 768 字节之外的数据的页面

(3)剩余的数据分散存储,在几个其他的页中进行分页存储,这些页使用链表连接

(4)在记录的真实数据中用 20 个字节存储指向溢出页地址,20 个字节中还包括分散在其他页面中的数据,所占用的字节数,从而可以找到剩余数据所在的页

(5)溢出列的数据类型:VARCHAR(M)、TEXT、BLOB 等

3、产生溢出页的临界点

(1)MySQL 中规定一个页中至少存放两行记录

(2)每个页除了存放记录以外,也需要存储一些额外的信息(总共需要 132 字节),其他的空间都可以被用来存储记录

(3)每个记录需要的额外信息是 27 字节

2 字节用于存储真实数据的长度
1 字节用于存储列是否是 NULL 值
5 字节大小的头信息
6 字节的 DB_ROW_ID 列
6 字节的 DB_TRX_ID 列
7 字节的 DB_ROLL_PTR 列

(4)假设一个列的真实数据占用的字节数为 n,如果该列不发生溢出现象,就需要满足该不等式:132 + 2 *(27 + n)< 16384(16 KB),即 n < 8099 字节

 

DYNAMIC、COMPRESSED

1、在 MySQL 8.0 中,默认行格式:Dynamic

2、DYNAMIC、COMPRESSED和 Compact 相似,只在处理行溢出数据时有分歧

(1)COMPRESSED、DYNAMIC对于存放在 BLOB 中的数据采用完全的行溢出的方式,把该列的所有真实数据都存储到溢出页中,在数据页中只存放 20 个字节的指针,指向溢出页地址,这 20 字节还包括真实数据占用的字节数

(2)COMPACT 、REDUNDANT 在记录的真实数据存储一部分数据(存放 768 个前缀字节)

3、COMPRESSED 不同于 DYNAMIC 的一点,是 COMPRESSED 会采用压缩算法对页面进行压缩,以节省空间

4、REDUNDANT 是一种比较原始的行格式,它是非紧凑的;而 COMPACT、DYNAMIC、COMPRESSED 是较新的行格式 它们是紧凑的,占用的存储空间更少

 

1、B+ 树每一层中的页都会形成一个双向链表

(1)如果是以页为单位来分配存储空间,双向链表中相邻两个页之间的物理位置可能离得非常远

(2)B+ 树范围查询只需要定位到最左边的记录和最右边的记录,然后沿着双向链表一直扫描即可,但若链表中相邻的两个页物理位置离得非常远,即随机 I/O,效率则会降低

(3)所以应该尽量让链表中相邻的页的物理位置也相邻,则范围查询时才可以使用顺序 I/O

2、作用

(1)在表中数据量大时,为某个索引分配空间时,就不再按照页的单位分配,而是按照区为单位分配

(2)甚至在表中的数据特别多时,可以一次性分配多个连续的区

(3)虽然可能造成空间浪费(数据不足以填充满整个区),但是从性能角度,可以消除很多随机 I/O

3、对于 16 KB 的页来说,连续的 64 个页就是一个区,一个区默认占用 1 MB 空间大小

4、无论是系统表空间还是独立表空间,都可以看成是由若干个连续的区组成,每 256 个区被划分成一组

5、第一个组最开始的3个页面的类型是固定,即 extent0 最开始的 3 个页面的类型是固定的

(1)FSP_HDR:这个类型的页面用来登记整个表空间的一些整体属性以及本组所有区(extent0 ~ extent255)的属性,整个表空间只有一个 FSP_HDR 类型的页面

(2)IBUF_BITMAP:这个类型的页面用来存储关于 Change Buffer 的一些信息

(3)INODE:这个类型的页面存储 INODE Entry 的数据结构

6、其余各组最开始的 2 个页面的类型是固定的

(1)XDES:全称 extentd descriptor,登记本组 256 个区的属性

(2)IBUF_BITMAP:这个类型的页面用来存储关于 Change Buffer 的一些信息

 

1、对于范围查询,其实是对 B+ 树叶子节点中的记录进行顺序扫描

2、如果不区分叶子节点、非叶子节点,把所有节点代表的页面,申请放到区中,范围扫描效率降低

3、InnoDB 对 B+ 树的叶子节点和非叶子节点进行了区别对待

(1)叶子节点有自己独有的区,存放叶子节点的区的集合就算是一个段(segment)

(2)非叶子节点也有自己独有的区,存放非叶子节点的区的集合也算是一个段

(3)即一个索引会生成 2 个段,一个叶子节点段,一个非叶子节点段

4、除索引的叶子节点段和非叶子节点段之外,InnoDB 中还有为存储一些特殊数据而定义的段,如:回滚段

5、常见段:数据段、索引段、回滚段

(1)数据段即为 B+ 树的叶子节点

(2)索引段即为 B+ 树的非叶子节点

6、在 InnoDB 中,对段的管理都是由引擎自身所完成,DBA 不能也没有必要对其进行控制,一定程度上简化 DBA 对于段的管理

7、段不对应表空间中的某一个连续的物理区域,而是一个逻辑上的概念,由若干个零散的页面以及一些完整的区组成

 

碎片区

1、默认情况下,一个使用 InnoDB 存储引擎的表只有一个聚簇索引

(1)一个索引会生成 2 个段,而段是以区为单位申请存储空间

(2)一个区默认占用 1M(64 * 16KB = 1024KB)存储空间,

(3)一个区被整个分配给某一个段,或区中的所有页面都是为了存储同一个段的数据而存在的,即使段的数据填不满区中所有的页面,那余下的页面也不能挪作他用

2、为了考虑以完整的区为单位,分配给某个段,对于数据量较小的表,浪费存储空间的情况,InnoDB 提出一个碎片(fragment)区概念

(1)在一个碎片区中,并不是所有的页,都是为了存储同一个段的数据而存在的

(2)碎片区中的页可以用于不同的目的,比如:有些页面用于段 A,有些页面用于段 B,有些页甚至哪个段都不属于

(3)碎片区直属于表空间,并不属于任何一个段

3、为某个段分配存储空间的策略

(1)刚开始向表中插入数据时,段是从某个碎片区,以单个页面为单位,来分配存储空间的

(2)当某个段已经占用 32 个碎片区页面之后,就会申请以完整的区为单位来分配存储空间

(3)段不能仅定义为是某些区的集合,应该是某些零散的页面已经一些完整的区的集合

 

区分为 4 种类型

1、空闲的区(FREE):现在还没有用到这个区中的任何页面

2、有剩余空间的碎片区(FREE_FRAG):表示碎片区中还有可用的页面

3、没有剩余空间的碎片区(FULL_FRAG):表示碎片区中的所有页面都被使用,没有空闲页面

4、附属于某个段的区(FSEG):每一索引都可以分为叶子节点段和非叶子节点段

5、处于 FREE、FREE_FRAG、FULL_FRAG 的区都是独立的,直属于表空间;处于 FSEG 的区是附属于某个段

 

表空间

1、看做是 InnoDB 逻辑结构的最高层,所有的数据都存放在表空间中

2、是一个逻辑容器

(1)表空间存储的对象是段

(2)在一个表空间中可以有一个或多个段,但是一个段只能属于一个表空间

3、表空间数据库由一个或多个表空间组成

4、表空间从管理上可以划分为:系统表空间(System tablespace)、独立表空间(File-per-table tablespace)、撤销表空间(Undo Tablespace)、临时表空间(Temporary Tablespace)等

 

独立表空间

1、每张表有一个独立的表空间,即数据和索引信息都会保存在自己的表空间中

2、独立的表空间(单表)可以在不同的数据库之间进行迁移

3、空间可以回收

(1)DROP TABLE 操作可自动回收表空间

(2)其他情况,表空间不能自己回收

(3)如果对于统计分析或是日志表,删除大量数据后,可以回收不用的空间

ALTER TABLE 表名 ENGINE=InnoDB;

(4)对于使用独立表空间的表,不管怎么删除,表空间的碎片不会太严重的影响性能,而且还有机会处理

4、独立表空间结构:由段、区、页组成

5、真实表空间对应的文件大小

(1)一开始表空间占用的空间很小,因为表里边都没有数据

(2).ibd 文件是自扩展,随着表中数据的增多,表空间对应的文件也逐渐增大

6、查看 InnoDB 表空间类型

SHOW VARIABLES LIKE 'innodb_file_per_table';

 

系统表空间

1、结构和独立表空间基本类似

2、整个 MySQL 进程只有一个系统表空间,在系统表空间中会额外记录一些有关整个系统信息的页面,这部分是独立表空间中没有的

3、每当向一个表中插入一条记录时,MySQL 校验过程如下

(1)先校验插入语句对应的表是否存在,插入的列和表中的列是否符合

(2)若语法没有问题,还需要知道该表的聚簇索引、所有二级索引对应的根页面,属于哪个表空间的哪个页面

(3)然后把记录插入对应索引的 B+ 树中

4、InnoDB 数据字典

(1)元数据:管理用户数据

(2)InnoDB 定义一些列的内部系统表(internalsystem table)/ 数据字典,来记录元数据

(3)以 B+ 树形式保存在系统表空间的某些页面中,其中 SYS_TABLES、SYS_COLUNNS、SYS_INDEXES、SYS_FIELDS 称为基本系统表(basic system tables)

(4)用户不能直接访问 InnoDB 内部系统表,除非直接解析系统表空间对应文件系统上的文件

(5)系统数据库 information_schema 中提供以 INNODB_SYS 开头的表,并不是真正的内部系统表,而是在存储引擎启动时,读取以 SYS 开头的系统表,然后填充到以 INNODB_SYS 开头的表中

表名 描述(整个 InnoDB 存储引擎)
SYS_TABLES 所有的表的信息
SYS_COLUMNS 所有的列的信息
SYS_INDEXES 所有的索引的信息
SYS_FIELDS 所有的索引对应的列的信息
SYS_FOREIGN 所有的外键的信息
SYS_FOREIGN_COLS 所有的外键对应列的信息
SYS_TABLESPACES 所有的表空间信息
SYS_DATAFILES 所有的表空间,对应文件系统的文件路径信息
SYS_VIRTUAL 所有的虚拟生成列的信息

5、SYS_TABLES 表结构

列名 描述
NAME 表的名称、主键
ID InnoDB 中每个表都有一个唯一 ID(二级索引)
N_COLS 该表拥有列的个数
TYPE 表的类型,记录了一些文件格式、行格式、压缩等信息
MIX_ID 已过时
MIX_LEN 表的一些额外的属性
CLUSTER_ID 未使用
SPACE 该表所属表空间 ID

6、SYS_COLUNNS 表结构

列名 描述
TABLE_ID 该列所属表对应 ID,与 POS 一起构成联合主键
POS 该列在表中是第几列
NAME 该列的名称
MTYPE maindatatype,主数据类型
PRTYPE precise type,精确数据类型,修饰主数据类型,比如:是否允许 NULL 值,是否允许负数
LEN 该列最多占用存储空间的字节数
PREC 该列的精度,默认值都是 0

7、SYS_INDEXES 表结构

列名 描述
TABLE_ID 该索引所属表对应 ID,与 ID 一起构成联合主键
ID InnoDB 中每个索引都有一个唯一 ID
NAME 该索引的名称
N_FIELDS 该索引包含列的个数
TYPE 该索引的类型,比如:聚族索引、唯一索引、更改缓冲区的索引、全文索引、普通的二级索引等
SPACE 该索引根页面所在的表空间 ID
PAGE_NO 该索引根页面所在的页面号
MERGE_THRESHOLD 如果页面中的记录被删除到某个比例,则把该页面和相邻页面合并,此值即为比例

8、SYS_FIELDS 表结构

列名 描述
INDEX_ID 该索引列所属的索引 ID,与 POS 一起构成联合主键
POS 该索引列在某个索引中是第几列
COL_NAME 该索引列的名称

 

数据页加载

1、InnoDB 从磁盘中读取数据的最小单位是数据页

(1)对于 MySQL 存放的数据,逻辑概念上称为表,在磁盘等物理层面是按数据页形式存放

(2)当其加载到 MySQL 中称为缓存页

2、如果缓冲池中没有该页数据,那么缓冲池有以下三种读取数据的方式,每种方式的读取效率都不同

3、内存读取:如果该数据存在于内存中,基本上执行时间在 1ms 左右

4、随机读取

(1)如果数据没有在内存中,需要在磁盘上对该页进行查找,整体时间预估在 10ms 左右

(2)10ms:6ms 是磁盘的实际繁忙时间(包括寻道和半圈旋转时间),3ms 对可能发生的排队时间的估计值,1ms 传输时间,将页从磁盘服务器缓冲区,传输到数据库缓冲区中

image-20220601004847607

5、顺序读取

(1)一种批量读取的方式

(2)请求的数据在磁盘上往往都是相邻存储的,顺序读取可以批量读取页面

(3)一次性加载到缓冲池中,就不需要再对其他页面单独进行磁盘 I/O 操作

(4)采用批量读取的方式,即使是从磁盘上进行读取,效率也比从内存中只单独读取一个页的效率要高

posted @   半条咸鱼  阅读(494)  评论(0编辑  收藏  举报
(评论功能已被禁用)
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
点击右上角即可分享
微信分享提示