Mysql 学习（二）InnDB 存储引擎-记录结构

程序胖
176 阅读10分钟

InnoDB 简介

  • InnoDB 是一个将表中的数据存储到磁盘上的存储引擎，真正处理数据的过程是发生在内存中，所以当修改和添加数据的时候，需要将数据加载到内存中，处理好之后在刷新到磁盘中。
  • 有个问题，磁盘的读写非常慢，要是 请求记录是一条一条从磁盘读取到内存，就会非常慢，所以InnoDB采取的方式，将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为16KB，也就是一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。

InnoDB 行格式

  • 一般情况下，插入表中的数据都是以记录插入的，也就是以行的方式插入的，而这些记录存放在磁盘中的格式被称之为行格式或者记录格式。
  • 行格式在InnoDB 中 有四种类型，分别是 Compact，Redundant，Dynamic，Compressed
  • 设定行格式的语法：
CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
    
ALTER TABLE 表名 ROW_FORMAT=行格式名称

--例子：
CREATE TABLE test1 ( 
t1 VARCHAR ( 10 ), 
t2 VARCHAR ( 10 ) NOT NULL, 
t3 CHAR ( 10 ), 
t4 VARCHAR ( 10 ) 
) charset = ascii ROW_FORMAT = COMPACT

INSERT INTO test1(t1, t2, t3, t4) 
VALUES
	('tttt', 'tt', 'tt', 't'), 
	('t1t1', 't2t', NULL, NULL)
  • 表结构就是这样了 在这里插入图片描述

COMPACT 行格式

  • 由于行格式里面 COMPACT 比较经典，所以我们就以 这个类型 来讲解记录是以怎么样的格式存储到磁盘中
  • 由下图可知，一条完整的记录其实可以被分为 记录的格外信息 和 记录的真实数据 两大部分 在这里插入图片描述

记录的额外信息

  • 这部分信息是服务器为了描述这条记录而不是不额外添加的一些信息，这些额外的信息分为三类，分别是 变长字段长度列表，NULL值列表 和 记录头信息

变长字段长度列表

  • 变长字段长度列表：mysql中一些不确定长度的字段数据类型，被称之为变长字段，比如：VARCHAR(M)、VARBINARY(M)、各种TEXT类型，各种BLOB类型，因为变长字段中存储多少个字节是不确定的，为了减轻mysql服务器的性能，存储结构就分为两部分：
    • 真正的数据内容
    • 占用的字节数
  • 在COMPACT中，我们把数据真正数据占用的字节数存储到记录的开头部位，从而形成一个变长字段长度列表，各变长字段数据占用的字节数按照列的顺序逆序存放
  • 接下来我们就来根据第一条记录来讲解如何存储
    • test1 表中 的 t1 t2 t4 都是varchar(10) 类型的，所以这三列都需要保存长度在开头，因为我们这张表test1 字符集是 ascii 字符集，所以每个字符都是1个字节，所以下面的表格就可以展示各个字段的长度
    • 又因为是逆序，所以记录头部存储的数据就是 01 03 04
列名称存储内容内容长度(十进制)内容长度(十六进制)
t1tttt40x04
t2tt30x03
t4t10x01
  • 还有个问题 我们使用 ascii 字符集的时候，现在的内容是一个字符一个字节，但是如果是别的字符集呢？具体使用几个字节是通过什么来决定呢？
  • 这时候就需要引出一套规则了，分别是 W，M，L的定义：
    • 假设某个字符集中表示一个字符最多需要使用的字节数为W，也就是使用SHOW CHARSET语句的结果中的Maxlen列，比方说utf8字符集中的W就是3，gbk字符集中的W就是2，ascii字符集中的W就是1。
    • 对于变长类型VARCHAR(M)来说，这种类型表示能存储最多M个字符（注意是字符不是字节），所以这个类型能表示的字符串最多占用的字节数就是M×W。
    • 假设它实际存储的字符串占用的字节数是L。
    • 如果M×W <= 255，那么使用1个字节来表示真正字符串占用的字节数。
      • 就是说InnoDB在读记录的变长字段长度列表时先查看表结构，如果某个变长字段允许存储的最大字节数不大于255时，可以认为只使用1个字节来表示真正字符串占用的字节数。
    • 如果M×W > 255，则分为两种情况：
      • 如果L <= 127，则用1个字节来表示真正字符串占用的字节数。
      • 如果L > 127，则用2个字节来表示真正字符串占用的字节数。
      • InnoDB在读记录的变长字段长度列表时先查看表结构，如果某个变长字段允许存储的最大字节数大于255时，该怎么区分它正在读的某个字节是一个单独的字段长度还是半个字段长度呢？设计InnoDB的大佬使用该字节的第一个二进制位作为标志位：如果该字节的第一个位为0，那该字节就是一个单独的字段长度（使用一个字节表示不大于127的二进制的第一个位都为0），如果该字节的第一个位为1，那该字节就是半个字段长度。
      • 对于一些占用字节数非常多的字段，比方说某个字段长度大于了16KB，那么如果该记录在单个页面中无法存储时，InnoDB会把一部分数据存放到所谓的溢出页中（我们后边会介绍），在变长字段长度列表处只存储留在本页面中的长度，所以使用两个字节也可以存放下来。
  • 总结一下，如果该可变字段允许存储的最大字节数（M X W）超过 255 字节并且真实存储的字节数 （L）超过 127 字节，则使用 2个字节，否则使用 1个字节
    • 变长字段长度列表中只存储值为 非NULL 的列内容占用的长度，值为 NULL 的列的长度是不储存的

NULL值列表

  • 由于变长字段列表只存储非NULL的列，那 NULL的列 如何存储呢？
  • 答案是放在NULL值列表中，因为如果把这些 NULL值都放到记录的真实数据中存储会很占地方，所以Compact行格式把这些值为NULL的列统一管理起来，存储到NULL值列表中。
  • 处理过程：
    • 首先统计表中允许存储NULL的列有哪些
    • 如果表中没有允许存储 NULL 的列，则 NULL值列表 也不存在了，否则将每个允许存储NULL的列对应一个二进制位，二进制位按照列的顺序逆序排列，二进制位表示的意义如下：
      • 二进制位的值为1时，代表该列的值为NULL
      • 二进制位的值为0时，代表该列的值不为NULL
    • MySQL规定NULL值列表必须用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则在字节的高位补0。

记录头信息

  • 除了 变长字段长度列表，NULL值列表之外，还有一个用于描述记录的记录头信息，它是由固定的5个字节组成。5个字节也就是40个二进制位，不同的位代表不同的意思。 在这里插入图片描述

记录的真实数据

  • 对于test1这个表来说，记录的真实数据除了t1，t2，t3，t4 这几个我们自己定义的列以外，还会给每个记录添加一些列，如下： 在这里插入图片描述
  • 这里需要讲解一下InnoDB的主键生成策略：
    • 优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个Unique键作为主键
    • 如果表中连Unique键都没有定义的话，则InnoDB会为表默认添加一个名为row_id的隐藏列作为主键

CHAR(M) 列的存储格式

  • 因为我们上面的例子，test1表的字符集是ascii，字节数是1，所以字符集是定长的字符集，不会加到变长字段长度表，但是如果换成其他的字符集，变成变长字符集，就会被存储到变长字段长度列表中。

Redundant行格式

行溢出数据

  • 对于一行数据的大小，InnoDB是有规定的，所以当记录的数据大于这个规定的时候，我们需要怎么办呢？
  • 首先我们先来了解 VARCHAR(M) 类型存储的相关信息
  • 我们知道对于VARCHAR(M)类型的列最多可以占用65535个字节。其中的M代表该类型最多存储的字符数量，如果我们使用ascii字符集的话，一个字符就代表一个字节，我们看看VARCHAR(65535)是否可用，运行下面的语句：
	CREATE TABLE varchar_size_demo(
	c VARCHAR(65535)
)	CHARSET=ascii ROW_FORMAT=Compact;
  • 运行以后发现，报错了 在这里插入图片描述
  • 从报错信息来看，是字节长度超过了最大字节长度，mysql对一条记录占用的最大存储空间是要求 65535 个字节，但是这个 65535 个字节除了真实数据外还有一些其他的数据
  • 如果该VARCHAR类型的列没有NOT NULL属性，那最多只能存储65532个字节的数据，因为真实数据的长度可能占用2个字节，NULL值标识需要占用1个字节
  • 如果VARCHAR类型的列有NOT NULL属性，那最多只能存储65533个字节的数据，因为真实数据的长度可能占用2个字节，不需要NULL值标识
  • 如果VARCHAR(M)类型的列使用的不是ascii字符集，那M的最大取值取决于该字符集表示一个字符最多需要的字节数。在列的值允许为NULL的情况下，gbk字符集表示一个字符最多需要2个字节，那在该字符集下，M的最大取值就是32766（也就是：65532/2），也就是说最多能存储32766个字符；utf8字符集表示一个字符最多需要3个字节，那在该字符集下，M的最大取值就是21844，就是说最多能存储21844（也就是：65532/3）个字符

记录中的数据太多产生的溢出

  • 了解了行数据的条件，MySQL中磁盘和内存交互的基本单位是页，也就是说MySQL是以页为基本单位来管理存储空间的，我们的记录都会被分配到某个页中存储。而一个页的大小一般是16KB，也就是16384字节，而一个VARCHAR(M)类型的列就最多可以存储65532个字节，这样就可能造成一个页存放不了一条记录的尴尬情况。

  • 在Compact和Reduntant行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中，然后记录的真实数据处用20个字节存储指向这些页的地址（当然这20个字节中还包括这些分散在其他页面中的数据的占用的字节数），从而可以找到剩余数据所在的页 在这里插入图片描述

  • 从图中可以看出来，对于Compact和Reduntant行格式来说，如果某一列中的数据非常多的话，在本记录的真实数据处只会存储该列的前768个字节的数据和一个指向其他页的地址，然后把剩下的数据存放到其他页中，这个过程也叫做行溢出，存储超出768字节的那些页面也被称为溢出页。画一个简图就是这样： 在这里插入图片描述

avatar
文章
阅读
粉丝