mysql如何优化存储_MYSQL实战优化——数据在磁盘上的存储

写入数据库的一行数据在磁盘上的存储

我们平时写SQL语句的时候在脑子里都有一个表、行和字段的概念，但是跑到MySQL内部就出现了一堆表空间、数据区、数据页的概念。实际上，表、行和字段是逻辑上的概念，而表空间、数据区和数据页是物理上的概念。所以接下来，我们逐步的来讲解MySQL的表空间、数据区、数据页、磁盘上的物理文件这些概念。

1、一行数据在磁盘上是如何存储的

数据页中的每一行数据在磁盘上是如何存储的？这里涉及到一个概念，就是行格式。我们可以对一个表指定它的行存储的格式是什么样的，比如我们这里用一个compact格式。

create table table_name(columns)

row_format=compact

alter table table_name

row_format=compact

你可以在建表的时候就指定一个行存储的格式，也可以后续修改行存储的格式。这里指定了一个compact行存储格式，在这种格式下，每一行数据实际存储的时候大概格式如下面这样：

变长字段的长度列表，null值列表，数据头 column01的值，column02的值，column0n的值......

对于每一行数据，存储的时候都会有一些头字段对这行数据进行一定的描述，然后再放上它这一行数据每一列的具体值，这就是所谓的行格式。

2、变长字段在磁盘中的存储

大家都知道，在MySQL里有一些字段的长度是变长的，是不固定的，比如varchar(10)之类的这种类型的字段，实际上它里面存放的字符串的长度是不固定的，有可能是“hello”这么一个字符串，也可能是“ab”这么一个字符串。现在我们来假设一下，现在有一行数据，它的几个字段的类型为varchar(10)，char(1)，char(1)，那么它第一个字段是varchar(10)，这个长度是可能变化的，所以这一行数据可能就是类似于：hello a b 这样，另外一行数据可能类似于：hi c d这样。现在我们把这两条数据写入磁盘，它们在磁盘文件里大概类似于这样：hello a b hi c d 。

假如现在你要读取hello a b这行数据，第一个问题就是从这个磁盘文件里读取的时候，到底哪些内容是一行数据？我不知道啊！因为这个表里的第一个字段是varchar(10)类型的，第一个字段的长度是多少我们是不知道的。所以你有可能读取出来“hello a b hi”是一行数据，也有可能是别的。总之，你在不知道一行数据的每个字段到底是多少长度的情况下是无法正确读取数据的。那怎么解决这个问题呢？就是在存储每一行数据的时候都保存一下它的

所以要在存储每一行数据的时候都保存一下它的变长字段的长度列表，这样才能解决一行数据的读取问题。我们看到“hello”的长度是5，十六进制就是0x05，所以此时会在“hello a a”前面补充一些额外信息，首先就是变长字段的长度列表，它在磁盘文件里存储的时候类似如下格式：0x05 null值列表 数据头 hello 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”这一行数据。

假设有多个变长字段，如何存放他们的长度？比如一行数据有varchar(10)，varchar(5)，varchar(20)，char(1)，char(1)，一共5个字段，其中三个是变长字段，此时假设一行数据是这样的：hello hi hao a a 。此时在磁盘中的存储，必须在它开头的变长字段长度列表中存储几个变长字段的长度，这里要注意一点，它是逆序存储的。也就是说先存放varchar(20)这个字段的长度，然后存放varchar(5)这个字段的长度，最后存放varchar(10)这个字段的长度。所以一行数据实际存储大概是下面这样的：

0x03 0x02 0x05 null值列表 头字段 hello hi hao a a

3、NULL字段值在磁盘上的存储

我们现在来看下在磁盘上存储的一行数据里另外一块特殊的数据区域，就是NULL值列表。这个所谓的NULL列表，说的就是一行数据里可能有的字段值是NULL，比如有一个name字段，它是允许为NULL的，那么实际上在存储的时候，如果你没给它赋值，它这个字段的值就是NULL。那么假设这个字段的NULL值我们在磁盘上存储的时候，就是按照“NULL”这么个字符串来存储，是不是有点浪费存储空间？本来它就是个NULL，说明什么值都没有，你还给它存个“NULL”字符串，你说你这是干什么呢？所以实际在磁盘上存储数据的时候，一行数据里的NULL值是肯定不会直接按照字符串的方式存放在磁盘上浪费空间的。

对所有的NULL值，不是通过字符串在磁盘上存储，而是通过二进制的bit位来存储，一行数据里假设有多个字段的值都是NULL，那么就会以bit位的形式存放在NULL值列表中。现在我们来看一个例子：

create table customer(

name varchar(10) not null,

address varchar(20) ,

gender char(1),

job varchar(30) ,

school varchar(30)

) row_format=compact

示例中我们创建了一张表，假设有这么一条数据要存储：jack null m null xx_school ，它的5个字段里有两个字段都是NULL，那么这条数据在磁盘上应该如何存储呢？我们先回顾一下，一行数据在磁盘上的存储格式

变长字段的长度列表，null值列表，头信息 column01的值，column02的值，column0n的值......

我们上面讲过，对于变长字段，它的字段长度要放在变长字段长度列表里，但是如果这个变长字段的值是NULL，那么就无需再存放。接着来看NULL值列表，这个NULL值列表是这样存放的，你所有允许值为NULL的字段，注意，是允许值为NULL，不是说值一定就是NULL，只要是允许为NULL的字段，在这里每个字段都有一个二进制bit位的值，如果bit值是1说明就是NULL，如果bit值是0说明不是NULL。比如上面4个字段都允许为NULL，每个字段都会有一个bit位，这一行数据的值是“jack null m null xx_school”，所以4个bit位应该是：1010 。但是实际放在NULL值列表的时候，它是按逆序放的，所以在NULL值列表里，放的是：0101，整体这一行数据看着是下面这样的：

0x09 0x04 0101 头信息 column01的值，column02的值，column0n的值......

另外，实际NULL值列表存放的时候，不会说仅仅是4个bit位，它一般起码是8个bit位的倍数，如果不足8个bit位就高位补0，所以实际存放看起来是如下所示：

0x09 0x04 00000101 头信息 column01的值，column02的值，column0n的值......

首先必然要把变长字段长度列表和NULL值列表读取出来，这样就知道有几个变长字段，哪几个变长字段是NULL。此时就可以从变长字段长度列表中解析出来不为NULL的变长字段的值的长度，然后也知道哪几个字段是NULL，如果是定长字段，就按照定长长度来读取，根据这些信息，就可以从实际的列值存储区域里把每个字段的值读取出来。

4、磁盘文件中40个bit位的数据头以及真实数据的存储

每一行数据存储的时候还得有40个bit位的数据头，这个数据头是用来描述这行数据的。这40个bit位里，第一个bit位和第二个bit位，都是预留位，是没有任何含义的。

然后接下来有一个bit位是delete_mask，它标识的是这行数据是否被删除了，在MySQL删除一行数据的时候，未必是立马把它从磁盘上清理掉，而是给它在数据头里搞1个bit标记，它已经被删除了。

然后下一个bit位是min_rec_mask，这个bit位以后再细说，现在先知道一点，它是说在B+树里每一层的非叶子节点里的最小值都有这个标记。

接下来有4个bit位是n_owned，这个先不用关注，它其实就是记录了一个记录数，这个记录数的作用，后续我们再讲。

接着有13个bit位是heap_no，它代表的是当前这行数据在记录堆里的位置，这个后续再细说。

然后是3个bit位的record_type，这就是说这行数据的类型。0代表的是普通类型，1代表的是B+树非叶子节点，2代表的是最小值数据，3代表的是最大值数据。

最后是16个bit的next_record，这个是指向它下一条数据的指针。

我们之前讲了一个例子，有一行数据是“jack null m null xx_school”，大家觉得真正在磁盘上存储的时候，我们那些字符串就是直接这么存储在磁盘上吗？显然不是的！实际上字符串这些东西都是根据我们数据库指定的字符集编码，进行编码之后再存储的，所以大致看起来一行数据是如下所示的：

0x09 0x04 00000101 0000000000000000000000000000000000011001

616161 636320 60662626262

我们的字符串和其它类型的数值最终都会根据字符集编码，搞成一些数字和符合存储在磁盘上。在实际存储一行数据的时候，会在它的真实数据部分，加入一些隐藏字段，这个隐藏字段跟后续的一些内容是有关联的，这里先了解一下。

首先有一个db_row_id字段，这就是一个行的唯一标识，是数据库内部给你搞的一个标识，不是你的主键id字段。如果我们没有指定主键和unique key唯一索引的时候，它内部就自动加一个row_id作为主键。

接着是一个db_trx_id字段，这是跟事务相关的，它是说这是哪个事务更新的数据，这是事务id，这个后面再介绍。

最后是db_roll_ptr字段，这是回滚指针，是用来进行事务回滚的，后续讲解事务的时候再介绍。

加上隐藏字段之后，实际一行数据可能如下所示：

0x09 0x04 00000101

0000000000000000000000000000000000011001

00000000094C (db_row_id)

00000000032D (db_trx_id)

EA000010078E (db_roll_ptr)

616161 636320 60662626262

上面基本就是一行数据最终在磁盘上的样子。

5、行溢出

我们之前已经初步了解到，实际上我们每一行数据都是放在一个数据页里的，这个数据页默认的大小是16kb，现在有个问题，就是万一一行数据的大小超过了页的大小怎么办？比如有一个表的字段类型是varchar(65532)，意思就是最大可以包含65532个字符，那也就是65532个字节，这就远大于16kb了，也就是说，这一行数据的这个字段都远超一个数据页的大小了。这个时候实际上会在那一页里存储这行数据，然后在那个字段中仅仅包含它一部分数据，同时包含一个20个字节的指针，指向其它的一些数据页，那些数据页用链表串起来，存放这个varchar(65532)超大字段里的数据。我们看下图：

datapage1.jpg

上面说的这个过程就叫做行溢出，就是说一行数据存储的内容太多了，一个数据页都放不下了，此时只能溢出这个数据页，把数据溢出存放到其它数据页里去，那些数据页就叫溢出页。

6、总结

现在我们可以做一点点总结，当我们在数据库里插入一条数据的时候，实际上是在内存里插入一个有复杂存储结构的一行数据，然后随着一些条件的发生，这行数据会被刷入到磁盘文件里。在磁盘文件里存储的时候，这行数据也是按照复杂的存储结构去存放的。而且每一行数据都是放在数据页里的，如果一行数据太大了，就会产生溢出问题，导致一行数据溢出到多个数据页里去，那么这行数据在buffer pool可能就是存在多个缓存页里的，刷入磁盘的时候也是用磁盘上的多个数据页来存放这行数据的。