行格式
- 我们在插入数据时,都是以行为单位的。也就是一行一行的插入数据的,这些数据在磁盘上的存放形式也被称为行格式。
- InnoDB存储引擎支持四种不同的行格式:
- COMPACT
- REDUNDANT
- DYNAMIC
- COMPRESSED
- 行格式决定了数据页中的每一行数据,在磁盘上是怎么存储的
- 我们可以对一个表指定它的行存储的格式是什么样的。
如何指定行格式
(1)创建表时指定
[charset_table_db]> use charset_table_db;
[charset_table_db]> CREATE TABLE record_format_table (
-> c1 VARCHAR(10),
-> c2 VARCHAR(10) NOT NULL,
-> c3 CHAR(10),
-> c4 VARCHAR(10)
-> ) CHARSET=ascii ROW_FORMAT=COMPACT;
(2)修改方法
[charset_table_db]> ALTER TABLE record_format_table ROW_FORMAT=COMPACT
COMPACT行格式
对于COMPACK行存储格式,在这种格式下,每一行数据实际存储的时候,大致格式类似下面这样:
也就是说,对于每一行数据,它包括两大部分:
- 记录的额外信息:是一些头部字符,用来描述这一行数据。包括三个部分:
- 变长字段长度列表
- NULL值列表
- 记录的头信息
- 记录的真实数据:每一个字段(列)具体的值
这就是所谓的行格式。
记录的额外信息
变长字段长度列表
变长字段在磁盘中是怎么存储的
-
在MySQL中有一些字段的长度是变长的,是不固定的,比如varchar(10)之类的这种类型的字段,实际上它里面存放的字符串的长度是不固定的,有可能是“hello”这么一个字符串,也可能是“a”这么一个字符串。
-
假设我们当前有一行数据,它的几个字段的类型为VRACHAR(10),CHAR(1),CHAR(1),那么他第一个字段是VARCHAR(10),这个长度是可能变化的,所以这一行数据可能就是类似于:hello a a,这样子,第一个字段的值是“hello”,后面两个字段的值都是一个字符,就是一个a
-
然后另外一行数据,同样也是这几个字段,他的第一个字段的值可能是“hi”,后面两个字段也是“a”,所以这一行数据可能是类似于:hi a a。一共三个字段,第一个字段的长度是是不固定的,后面两个字段的长度都是固定的1个字符。
-
那么现在,需要上上面两条数据写入一个磁盘文件里,那么这个时候在一个磁盘文件里可能有下面的两行数据:
hello a a hi a a
。 也就是说,两行数据在底层磁盘文件里是挨着存储的。 -
实际上,表里的很多行数据,最终落地在磁盘里的时候,都是上面这种样子的,一大坨数据放在一个磁盘文件里都是挨着存储的
存储在磁盘文件里的变长字段,为什么难以读取?
-
假设现在我们要读取上面的磁盘文件里的数据,要读取出来hello a a这一行数据,这个时候我们就遇到问题了:从这个磁盘文件里读取的时候,到底哪些内容是一行数据呢?
-
因为这个表里的第一个字段是VARCHAR(10)类型的,第一个字段的长度是多少我们是不知道的!所以有可能你读取出来的“hello a a hi”是一行数据,也可能读取出来的“hello a”是一行数据。在不知道一行数据的每个字段到底是多少长度的情况下,胡乱的去读取是不现实的,根本不知道磁盘文件里混成一坨的数据里,哪些数据是要读取的一行。
引入变长字段的长度列表,解决一行数据的读取问题
-
所以才要在存储每一行数据的时候,都保存一下它的变长字段的长度列表,这样才能解决一行数据的读取问题。
-
也就是说,在存储“hello a a”这行数据的时候,要带上一些额外的附加信息,比如第一块就是它里面的变长字段的长度列表。
-
也就是,这个hello的VARCHAR(10)类型的变长字段的值。可以看到“hello”的长度是5,也就是0x05,所以此时会在“hello a a”前面补充一些额外信息,首先是变长字段的长度列表,你会看到这行数据在磁盘文件里存储的时候,其实是类似如下格式:
0x05 null值列表 数据头 hello a a
-
还有一行数据可能就是:
0x02 null值列表 数据头 hi a a
,两行数据放在一起存储在磁盘文件里,看起来是如下所示的:
0x05 null值列表 数据头 hello a a 0x02 null值列表 数据头 hi a a
引入变长字段长度列表后,如何解决变长字段的读取问题
-
假设此时要读取“hello a a”这行数据,你首先会知道这个表里的三个字段的类型是VARCHAR(10) CHAR(1) CHAR(1),那么此时你先要读取第一个字段的值,那么第一个字段是变长的,到底它的实际长度是多少呢?
-
此时你会发现第一行数据的开头有一个变长字段的长度列表,里面会读取到一个0x05这个十六进制的数字,发现第一个变长字段的长度是5,于是按照长度为5,读取出来的第一个字段的值,就是“hello”
-
接着你知道后继两个字段都是CHAR(1),长度都是固定的1个字符,于是此时就依次按照长度为1读取出来后继两个字段的值,分别是“a”,“a”,于是最终你会读取出来“hello a a”这一行数据
-
接着假设你要读取第二行数据,你先看一下第二行数据后的变长字段长度列表,发现它第一个变长长度的字段是0x02,于是就读取长度为2的字段值,就是“hi”,再读取两个长度固定为1的字符串,都是“a”,此时读取出来“hi a a”这行数据
如果有多个变长字段,如何存放它们的长度?
-
比如一行数据有VARCHAR(10) VARCHAR(5) VARCHAR(20) CHAR(1) CHAR(1),一共5个字段,其中三个是变长字段,此时假设一行数据是这样的:hello hi hao a a。
-
此时在磁盘中存储的,必须在它开头的变长字段长度列表中存储几个变长字段的长度,一定要注意一点,它是逆序存储的。也就是说先存放VARCHAR(20)这个字段的长度,然后存放VARCHAR(5)这个字段的长度,最后存放VARCHAR(10)这个字段的长度。
-
现在hello hi hao三个字段的长度分别是0x05 0x02 0x03,但是实际存放在变长字段长度列表的时候,是逆序放的,所以一行数据实际存储可能是下面这样的:
0x03 0x02 0x05 null值列表 头字段 hello hi hao a a
NULL值列表
问题:一行数据中的多个NULL字段值在磁盘上怎么存储?
为什么一行数据里的NULL值不能直接存储?
-
磁盘上存储的一行数据里除了变长字段长度列表之外,还有另外一块特殊的数据区域,就是NULL值列表。
-
这个所谓的NULL值列表,就是你一行数据可能有的字段值是NULL,比如你有一个name字段,它是允许为null的,那么实际上存储的时候,如果你没给它赋值,那么这个字段就是null。
-
那么假设这个字段的NULL值在磁盘上存储的时候,就是按照“NULL”这个字符串来存储,是不是很浪费存储空间?本来它就是个NULL,还存个“NULL”字符串,不是多此一举吗?
-
所以实际上在磁盘上存储数据的时候,一行数据里的NULL值是肯定不会直接按照字符串的方式存放在磁盘上浪费空间的。
NULL值是以二进制bit位来存储的
- 对所有的NULL值,不通过字符串在磁盘上存储,而是通过二进制的bit位来存储,一行数据里假设有多个字段的值都是NULL,那么这多个字段的NULL,就会以bit位的形式存放在NULL值列表中。
举个例子:
- 假设有一个表
CREATE TABLE customer (
name VARCHAR(10) NOT NULL,
address VARCHAR(20),
gender CHAR(1),
job VARCHAR(30),
school VARCHAR(50)
) ROW_FORMAT=COMPACT;
-
上表示一个客户表,里面有5个字段,分别为name、address、genderjob、school,就代表了客户的姓名、地址、性别、工作以及学校。其中有4个变长字段,还有一个定长字段,然后第一个name字段声明了NOT NULL,其中4个没有,就有可能为NULL。
-
假设有一行数据
jack NULL m NULL xx_school
,这5个字段里有两个字段都是NULL。那这行数据在磁盘上是怎么存储的呢?
一行数据的磁盘存储格式
一行数据在磁盘上的存储格式应该是下面这样的:
变长字段长度列表 NULL值列表 头信息 column1=value1 column2=value2 ... columnN=valueN
对于上表,我们需要先看变长字段长度列表应该放什么东西
-
它一共有4个变长字段,那么按照我们上面说的,是不是应该按照逆序的顺序,先放school字段的长度,再放job、address、name这几个变长字段的长度?
-
理论上是这样的,但是这里要区分一个问题,那就是如果这个变长字段的值是NULL,就不用在变长字段长度列表里存放它的值长度了,所以在上面这行数据中,只有name和school两个变长字段是有值的,把它们的长度按照逆序放在变长字段长度列表中就可以了,如下所示:
0x09 0x04 NULL值列表 头信息 column1=value1 column2=value2 ... columnN=valueN
-
接着来看NULL值列表,这个NULL值列表是这样存放的,你所有允许值为NULL的字段,注意是允许,不是说一定值是NULL,只要是允许为NULL的字段,在这里每个字段都有一个二进制bit位的值,如果bit值是1说明是NULL,如果是0说明不是NULL。
-
比如上面4个字段都允许为NULL,每个人都会有一个bit位,这一行数据的值是“jack NULL m NULL xx_school”,然后其中2个字段是null,2个字段不是null,所以4个bit位应该是:1010
-
但是实际放在NULL值列表的时候,他是按逆序放的,所以在NULL值列表里,放的是:0101,整体这一行数据看着是下面这样的
0x09 0x04 0101 头信息 column1=value1 column2=value2 ... columnN=valueN
- 另外就是它实际NULL值列表存放的时候,不会说仅仅是4个bit位,它一般起码是8个bit位的倍数,如果不足8个bit为就高位补0,所以它实际存放看起来是下面这样的:
0x09 0x04 00000101 头信息 column1=value1 column2=value2 ... columnN=valueN
磁盘上的一行数据到底是如何读取出来的呢?
0x09 0x04 00000101 头信息 column1=value1 column2=value2 ... columnN=valueN
-
首先必须要把变长字段长度列表和NULL值列表读取出来,通过综合分析一下,就知道有几个变长字段,哪几个变长字段是NULL。
-
此时就可以从变长字段长度列表中解析出来不为NULL的变长字段的值长度,然后也知道哪几个字段是NULL的,此时根据这些信息,就可以从实际的列值存储区域里,把每个字段的值读取出来了。
-
如果是变长字段的值,就按照它的值长度来读取;如果是NULL,就知道没有值存储,为NULL;如果是定长字段,就按照定长长度读取。这样就可以完美的把一行数据的值都读取出来了
记录头信息
磁盘文件中,40个bit位的数据头以及真实数据是如何存储的
在磁盘上存储数据的时候,每一行数据都会有变长字段长度列表,逆序存放这行数据里的变长字段的长度。
然后会有NULL值列表,对于允许为NULL的字段都会有一个bit位标识这个字段是否为NULL,也是逆序排序的。
每一行数据存储的时候,还得有40个bit位的数据头,这个数据头是用来描述这行数据的。
- 这40个bit位里,第一个bit位和第二个bit位,都是预留位,是没任何含义的
- 接下来是
delete_mask
,它标识这行数据是否被删除了。也就是说在MySQL里删除一行数据的时候,未必是马上把它从磁盘上清理掉,而是在数据头里面用1个bit标记它已经被删除了 - 下一个是
min_rec_mask
,在B+树里每一层的非叶子节点里的最小值都有这个标记 - 然后是4bit的
n_owned
,记录了一个记录数 - 接着有13个bit位是
heap_no
,它表示当前这行数据在记录堆里的位置 - 然后是34个bit为的
record_type
,表示这行数据的类型。其中0表示普通类型,1表示B+树的非叶子节点,2表示最小值数据,3表示最大值数据 - 最后是16个bit的next_record,它执行它下一行数据的指针、
记录的真实数据
一行数据在磁盘文件里存储的时候,实际上首先会包含自己的变长字段的长度列表,然后是NULL指列表,然后是数据头,接着才是真实数据。那么真实数据是如何存储的呢?
存储真实数据的时候, 并没有什么特别的,无非就是按照我们哪个字段里的数据值去存储就行了。
举个例子,对于数据“jack NULL m NULL xx_school”,它的真实存储如下:
0x09 0x04 00000101 0000000000000000000010000000000000011001 jack m xx_schoo
先是他的变长字段的长度,用十六进制来存储,然后是NULL值列表,指出了谁是NULL,接着是40个bit位的数据头,然后是真实的数据值,就放在后面。
在读取这个数据的时候:
- 它会根据变长字段的长度,先读取出来jack这个值,因为它的长度是4,就读取4个长度的数据,jack就出来了。
- 然后发现第二个字段是NULL,就不用读取了
- 第三个是定长数据,直接读取一个字段就可以了,就得到了m
- 第四个字段是NULL,不用读取了
- 第五个字段变长字段长度是9,可以读取出xx_schoo
但是等等,大家觉得真正在磁盘上存储的时候,我们那些字符串就是直接这么存储在磁盘上吗?
显然不是的!实际上字符串这些东西都是根据我们数据库指定的字符集编码,进行编码之后再存储的,所以一行数据应该为:
0x09 0x04 00000101 0000000000000000000010000000000000011001 616161 636320 6262626262
从上面可以看到,我们的字符串和其他类型的数值最终都会根据字符集编码,变成一些数字和符号存储在磁盘上。
另外,实际存储一行数据的时候,会在它的真实数据部分,加入一些隐藏字段:
- 首先有一个
DB_ROW_ID
字段,这就是一个行的唯一标识,是数据库内部搞的一个标识,不是主键ID字段。如果我们没有指定主键和unique_key唯一索引的时候,它就内部自动加一个ROW_ID作为主键 - 接着是一个
DB_TRX_ID
字段,这是跟事务相关的,表示哪个事务更新的数据,为事务ID - 最后是一个
DB_ROLL_PTR
字段,这是回滚指针,用来进行事务回滚
加上这几个隐藏字段之后,实际一行数据看起来如下:
0x09 0x04 00000101 0000000000000000000010000000000000011001 00000000094C(DB_ROW_ID)
00000000032D(DB_TRX_ID) EA000010078E(DB_ROL_PTR) 616161 636320 6262626262
在磁盘上的数据,每一行数据就是类似“0x09 0x04 00000101 0000000000000000000010000000000000011001 00000000094C(DB_ROW_ID)00000000032D(DB_TRX_ID) EA000010078E(DB_ROL_PTR) 616161 636320 6262626262”这样的东西。这样我们就初步把磁盘上的数据和内存里的数据关联起来了
数据页
无论如何,每一行数据最终都要保存到磁盘中,问题是我们是没插入/更新一行数据,就直接操作磁盘上的数据的吗?
不是。
- InnoDB会将数据划分为若干个页,以页作为磁盘和内存之间交互的基本单位,这个页叫做数据页。
- 也就是说,InnoDB在存储数据的时候,是通过数据页的方式来组装数据的。
- 对于数据页的每一行数据,都有对应的行格式。
- 当我们在数据库里面插入一行数据的时候,实际上是在内存里插入一个有复杂存储结构的一行数据,然后随着一些条件的发生,这行数据会被刷到磁盘文件里去。
- 在磁盘文件存储的时候,这样数据也是按照复杂的存储结构去存放的。
- 每一行数据都是放在数据页里面的,我们是以数据页为单位把磁盘上的数据加载到内存的缓存页里来的,也是以页为单位,把缓存页的数据刷入磁盘上的数据页中。
那么每一页有多大呢?
- InnoDB中页的大小可以通过系统变量
innodb_page_size
查看- 默认是16KB。也就是一般情况下:
- 一次至少从磁盘中读取16KB的内容的内存中
- 一次至少把内存中的16KB内容刷新到磁盘中
- 这个变量大小只能在第一次初始化MySQL数据目录时指定(也就是通过mysqld --initialize时指定),一旦指定之后就不能更改(也就是在服务器运行过程中不可以更改这个值的大小)
- 默认是16KB。也就是一般情况下:
show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.01 sec)
那么如果以后数据的大小超过了页的大小怎么办呢?
比如有一个表的字段类型为varchar(56632),意识是最大可以包含65532个字符,这就远大于16KB的大小了,这就是说这一行的这个字段就远超一个数据页的大小了!
这个时候它会在那一页里存储你这行数据,然后在哪个字段中,仅仅包含一部分数据,同时包含一个20字节的指针,指向其他的一些数据页,这些数据页用链表串联起来,存放这个varchar(66632)超大字段里的数据。
这个过程叫做行溢出,就是说一行数据存储的内容太多了,一个数据页都放不下了,此时只能溢出这个数据页,把数据溢出存放到其他数据页里去,这些数据页就叫做溢出页。
包括其他的一些字段类型都是一样的,比如TEXT、BLOB这种类型的字段,都有可能溢出,如果溢出了一行数据就会存储在多个数据页里。
总结
问题:MySQL为什么要设计这么一套复杂的数据存取机制,要基于内存、日志、磁盘上的数据文件来完成数据的读写呢?为什么对insert、update请求,不直接更新磁盘文件里的数据呢?
因为来一个请求就直接对磁盘文件进行随机读写,然后更新磁盘文件里的数据,虽然技术上是可以做到的,但是必然会导致执行请求的性能极差。
因为磁盘随机读写的性能是最差的,所以直接更新磁盘文件,必然导致我们的数据库无法抗下任何一点点稍微高并发一点的场景。
所以MySQL才设计了如此复杂的一套机制,通过内存里更新数据,然后写redo log以及事务提交,后台线程不定时刷新内存里的数据到磁盘文件里。
通过这种方式保证,每个更新请求,尽量就是更新内存,然后顺序写日志文件。
更新内存的性能是极高的,然后顺序写磁盘上的日志文件的性能也是比较高的,因为顺序写磁盘文件,它的性能要远高于随机读写磁盘文件。
也正是通过这套机制,才能让MySQL数据库在较高配置的磁盘上,每秒可以抗下几千的读写请求
问题:当我们要执行update之类的SQL语句的时候,必然要涉及到对数据的更新操作,那么此时对数据时在哪里更新的?
此时并不是直接去更新磁盘文件,而是把磁盘上的一些数据加载到内存里来,然后对内存中的数据进行更新,同时写redo log到磁盘上去
但是这里就有一个问题了,难道我们每次都是把磁盘里的一条数据加载到内存里去进行更新,然后下次要更新别的数据的时候,再从磁盘里加载另外一条数据到内存里去?
肯定不行,这样效率太低了。为此innodb存储引擎引入了一个数据页的概念,也就是把数据组织成一页一页的概念,每一页有16kb,每次加载磁盘的数据到内存里的时候,至少加载一页数据进去,如下图:
假设我们有一次要更新一条id=1的数据:update xxx set xxx=xxx where id=1
那么此时他会把id=1这条数据所在的一页数据都加载到内存里去,这一页数据里,可能还包含了id=2,id=3等其他数据。
然后我们更新完id=1的数据之后,接着更新id=2的数据,那么此时是不是就不用再次读取磁盘里的数据了
这就是数据页的意义,磁盘和内存之间的数据交换通过数据页来执行,包括内存里更新后的脏数据,刷回磁盘的时候,也是至少一个数据页刷回去。
当IO线程把内存里的脏数据刷到磁盘上去的时候,也是以数据页为单位来刷回去的。