type
status
date
slug
summary
tags
category
icon
password
1、Mysql的文件存储
Mysql 总体的物理文件可以分为下面三类
- 数据文件：数据文件用来存储数据库中的表文件数据，通常会在
/var/lib/mysql/目录下面建立一个以数据库为名的目录，另外还有四个系统数据库mysql、information_schema、performance_schema和sys。不同的数据库引擎，目录下表对应的文件扩展名也不一样。可以使用SHOW VARIABLES LIKE '%datadir%';命令查看数据目录所在位置。
- 日志文件：日志文件中记录着 MySQL 数据库运行期间发生的变化，可以通过
SHOW VARIABLES LIKE '%\_log_%';查询当前 MySQL 日志使用情况。日志分类有以下类型： - 错误日志（Error Log）：格式为 *.err，主要记录 MySQL 服务器启动和停止过程中的信 息、服务器在运行过程中发生的故障和异常情况等。错误日志中记录的并非全是错误的信息，例如MySQL如何启动InnoDB的表空间文件，如何初始化自己的存储引擎等等，这些也记录在错误日志文件中。
- 事务日志（redo Log & undo Log）：事务日志是InnoDB独有的日志，使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把修改行为记录到硬盘上持久的事务日志中，事务日志持久以后，内存中被修改的数据在后台可以慢慢的刷回到磁盘。目前大多数的存储引擎都是这样实现的。（修改后的数据最终还是慢慢刷回硬盘，为什么中间要增加写事务日志这一步？因为如果修改后的内存中的数据如果在还没有写回磁盘前数据库就宕机了，会造成数据丢失；但如果每次写内存的同时立刻刷回磁盘性能又太低，因为数据刷回磁盘文件要找到正确落盘位置的过程是随机IO，而写事务采用文件追加的方式，性能比随机IO会高很多。采用事务日志的方式在提高安全性的同时性能也不会太差）。事务日志又分为重做日志(redo Log)和回滚日志(undo Log)，当事务对数据库进行修改，InnoDB会同时生成redo log 和 undo log日志。
- redo Log：记录事务操作引起数据的变化，目的是用于重做事务。如果事务执行完成后内存数据还没有刷回磁盘数据库就宕机了，redo log 可以保证这些内存数据不会被丢失。格式为 ib_logfile0 等。redo log 用于保证事务特性的持久性。
- undo log：记录事务操作前数据的信息，目的是用于恢复事务前的状态，如果事务执行失败或者调用了rollback，导致事务需要回滚，undo log 可以保证数据恢复到事务前的状态。undo 文件默认记录在表空间文件里面例如 .ibdata。undo log 用于保证事务特性的原子性和实现MVCC。
- 二进制日志（Binary Log）：用于记录数据库的变化情况，即 SQL 语句的 DDL 和 DML 语句，不包含数据记录查询操作，用于数据库恢复和主从同步。格式为 mysql-bin.000001等。
- 查询日志（Query Log）：用来记录建立的客户端连接和执行的语句。
- 慢查询日志（Show Query Log）：用来记录在MySQL中响应时间超过阀值的语句。long_query_time的默认值为10（即10秒，通常设置为1秒），开启慢查询日志会对性能造成一定影响，默认情况下，MySQL数据库是不开启慢查询日志的。格式为 *show.log。
- 中继日志（Relay log）：用于主从复制的从服务器上。从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后从服务器SQL线程会读 取relay-log日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致。
- 配置文件：用于存放 MySQL 所有的配置信息的文件。
- 系统配置文件：命名为
my.ini或my.cnf，可以使用mysql --help|grep my.cnf查看MySQL配置文件的位置。 - socket文件：命名为
mysql.sock，MySQL服务器启动后 socket 文件自动生成，该文件主要用来连接客户端。
下面重点介绍 Mysql 的数据文件格式。
2、InnoDB的数据文件
MySQL 存储的行为是由存储引擎实现的，MySQL 支持多种存储引擎，不同的存储引擎保存的文件自然也不同。
InnoDB 是我们常用的存储引擎，也是 MySQL 默认的存储引擎。所以，本文主要以 InnoDB 存储引擎展开讨论。
先来看看 MySQL 数据库的文件存放在哪个目录？
我们每创建一个 database（数据库） 都会在 /var/lib/mysql/ 目录里面创建一个以 database 为名的目录，然后保存表结构和表数据的文件都会存放在这个目录里。
比如，我这里有一个名为 my_test 的 database，该 database 里有一张名为 t_order 数据库表。
然后，我们进入 /var/lib/mysql/my_test 目录，看看里面有什么文件？
可以看到，共有三个文件，这三个文件分别代表着：
- db.opt，用来存储当前数据库的默认字符集和字符校验规则。
- t_order.frm ，t_order 的表结构会保存在这个文件。在 MySQL 中建立一张表都会生成一个.frm 文件，该文件是用来保存每个表的元数据信息的，主要包含表结构定义。Mysql存储引擎篇：InnoDB数据文件存储。
- t_order.ibd，t_order 的表数据会保存在这个文件。这里有两种存储方式（独立表空间文件和共享表空间文件）。这个行为是由参数
innodb_file_per_table控制的，若设置了参数 `innodb_file_per_table 为 1，则会将存储的数据、索引等信息单独存储在一个独占表空间，从 MySQL 5.6.6 版本开始，它的默认值就是 1 了，因此从这个版本之后， MySQL 中每一张表的数据都存放在一个独立的 .ibd 文件。 - 独立表空间文件，格式为 表名.ibd。每一个表都有一个.frm表描述文件和一个.ibd文件，这个.ibd 文件存储一个表的数据和索引内容。
- 共享表空间文件，格式为 ibdata1，ibdata2 等。某一个数据库的所有的表数据，索引文件全部放在一个文件中，所有表可以共同使用一个（或者多个，可自行配置）ibdata文件。
好了，现在我们知道了一张数据库表的数据是保存在「 表名字.ibd 」的文件里的，这个文件也称为独占表空间文件。
2.1 表空间文件
表空间由段（segment）、区（extent）、页（page）、行（row）组成，InnoDB存储引擎的逻辑存储结构大致如下图：
2.1.1 行（row）
数据库表中的记录都是按行（row）进行存放的，每行记录根据不同的行格式，有不同的存储结构。
后面我们详细介绍 InnoDB 存储引擎的行格式，也是本文重点介绍的内容。
2.1.2 页（page）
记录是按照行来存储的，但是数据库的读取并不以「行」为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。
因此，InnoDB 的数据是按「页」为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个行记录从磁盘读出来，而是以页为单位，将其整体读入内存。
默认每个页的大小为 16KB，也就是最多能保证 16KB 的连续存储空间。
页是 InnoDB 存储引擎磁盘管理的最小单元，意味着数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。
页的类型有很多，常见的有数据页、undo 日志页、溢出页等等。数据表中的行记录是用「数据页」来管理的，数据页的结构这里我就不讲细说了总之知道表中的记录存储在「数据页」里面就行。
2.1.3 区（extent）
我们知道 InnoDB 存储引擎是用 B+ 树来组织数据的。
B+ 树中每一层都是通过双向链表连接起来的，如果是以页为单位来分配存储空间，那么链表中相邻的两个页之间的物理位置并不是连续的，可能离得非常远，那么磁盘查询时就会有大量的随机I/O，随机 I/O 是非常慢的。
解决这个问题也很简单，就是让链表中相邻的页的物理位置也相邻，这样就可以使用顺序 I/O 了，那么在范围查询（扫描叶子节点）的时候性能就会很高。
那具体怎么解决呢？
在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了。
2.1.4 段（segment）
表空间是由各个段（segment）组成的，段是一个逻辑上的概念，并不对应表空间中某一个连续的物理区域，它由若干个完整的区组成（还会包含一些碎片页），不同的段不能使用同一个区。。段一般分为数据段、索引段和回滚段等。
- 索引段：存放 B + 树的非叶子节点的区的集合；
- 数据段：存放 B + 树的叶子节点的区的集合；
- 回滚段：存放的是回滚数据的区的集合，MVCC 利用了回滚段实现了多版本查询数据。
存放叶子节点的区的集合就是数据段，存放非叶子节点的区的集合就是索引段。也就是说一个索引会生成2个段，一个叶子节点段（数据段），一个非叶子节点段（索引段）。
3、InnoDB数据页
记录是按照行来存储的，但是数据库的读取并不以「行」为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。
因此，InnoDB 的数据是按「数据页」为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。
数据库的 I/O 操作的最小单位是页，InnoDB 数据页的默认大小是 16KB，意味着数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。
数据页包括七个部分，结构如下图：
这 7 个部分的作用如下图：
页结构整体上可以分为三大部分，分别为通用部分(文件头、文件尾)、存储记录空间、索引部分。
3.1 通用部分(文件头、文件尾)
第一部分通用部分，主要指文件头和文件尾。将页的内容进行封装，通过文件头和文件尾校验的 CheckSum 方式来确保页的传输是完整的。在 File Header 中有两个指针，分别指向上一个数据页和下一个数据页，连接起来的页就形成了一个双向链表。让数据页之间不需要是物理上的连续，而是逻辑上的连续。
3.2 存储记录空间
第二个部分是记录部分。页的主要作用是存储记录。
- 页面头、最小记录和最大记录这三部分大小是固定的，用于描述整个页面的基本信息。
- 用户记录，占了页结构的主要空间。对于不同功能的页，用户记录存储的内容不同。数据页的每行用户记录存储实际的数据，而索引页的每行用户记录存储子页的最小主键和页地址。
- 空闲空间，是个灵活的部分，当有新的记录插入时，会从空闲空间中进行分配用于存储新记录。
3.3 索引部分
第三部分是索引部分，指的是页目录（Page Directory）。不管是什么类型的数据页（索引页、数据页）都会包含页目录。因为B+树索引本身并不能找到具体的一条记录，只能找到该记录所在的页，然后需要把页读入内存里面，遍历页内数据才能定位到具体的数据行。如果页内的数据量比较大的话，这种方式的性能较差。页目录就是用来解决这个问题的，利用页目录可以在页内快速定位到目标数据。页目录的设计思路类似于给页面数据创建一个目录，利用二分查找的方式快速查找。
页目录创建的过程如下：
- 将所有的记录划分成几个组，这些记录包括最小记录和最大记录，但不包括标记为“已删除”的记录；
- 每个记录组的最后一条记录就是组内最大的那条记录，并且最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段（上图中粉红色字段）
- 页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，每组的地址偏移量也被称之为槽（slot），每个槽相当于指针指向了不同组的最后一个记录。
从图可以看到，页目录就是由多个槽组成的，槽相当于分组记录的索引。然后，因为记录是按照「主键值」从小到大排序的，所以我们通过槽查找记录时，可以使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，再遍历槽内的所有记录，找到对应的记录，无需从最小记录开始遍历整个页中的记录链表。
以上面那张图举个例子，5 个槽的编号分别为 0，1，2，3，4，我想查找主键为 11 的用户记录：
- 先二分得出槽中间位是 (0+4)/2=2 ，2号槽里最大的记录为 8。因为 11 > 8，所以需要从 2 号槽后继续搜索记录；
- 再使用二分搜索出 2 号和 4 槽的中间位是 (2+4)/2= 3，3 号槽里最大的记录为 12。因为 11 < 12，所以主键为 11 的记录在 3 号槽里；
- 这里有个问题，「槽对应的值都是这个组的主键最大的记录，如何找到组里最小的记录」？比如槽 3 对应最大主键是 12 的记录，那如何找到最小记录 9。解决办法是：通过槽 3 找到 槽 2 对应的记录，也就是主键为 8 的记录。主键为 8 的记录的下一条记录就是槽 3 当中主键最小的 9 记录，然后开始向下搜索 2 次，定位到主键为 11 的记录，取出该条记录的信息即为我们想要查找的内容。
看到第三步的时候，可能有的同学会疑问，如果某个槽内的记录很多，然后因为记录都是单向链表串起来的，那这样在槽内查找某个记录的时间复杂度不就是 O(n) 了吗？
这点不用担心，InnoDB 对每个分组中的记录条数都是有规定的，槽内的记录就只有几条：
- 第一个分组中的记录只能有 1 条记录；
- 最后一个分组中的记录条数范围只能在 1-8 条之间；
- 剩下的分组中记录条数范围只能在 4-8 条之间。
4、InnoDB行格式
行格式（row_format），就是一条记录的存储结构。
InnoDB 提供了 4 种行格式，分别是 Redundant、Compact、Dynamic和 Compressed 行格式。可以通过命令
show variables like 'innodb_default_row_format'; 来查看使用的行格式。- Redundant 是很古老的行格式了， MySQL 5.0 版本之前用的行格式，现在基本没人用了。
- 由于 Redundant 不是一种紧凑的行格式，所以 MySQL 5.0 之后引入了 Compact 行记录存储方式，Compact 是一种紧凑的行格式，设计的初衷就是为了让一个数据页中可以存放更多的行记录，从 MySQL 5.1 版本之后，行格式默认设置成 Compact。
- Dynamic 和 Compressed 两个都是紧凑的行格式，它们的行格式都和 Compact 差不多，因为都是基于 Compact 改进一点东西。从 MySQL5.7 版本之后，默认使用 Dynamic 行格式。
对四种行格式做一个总结。
行格式 | 特点 | 行溢出处理 |
REDUNDANT | Redundant 是 MySQL5.0 版本之前 InnoDB 的行记录存储方式，已经比较老了，现在基本也不再使用这种格式 | 一些大对象如 TEXT、BLOB 存储的数据可能超过一个页的大小，这时一个页可能就存不了一条记录。这个时候就会发生行溢出，多的数据就会存到另外的溢出页中。REDUNDANT将前768个字节的可变长度列值（VARCHAR、VARBINARY 以及 BLOB 和 TEXT 类型）存储在 B 树节点内的索引记录中，其余部分存储在溢出页上。 |
COMPACT | 与REDUNDANT 行格式相比，COMPACT 行格式减少了大约 20% 的行存储空间 | 同REDUNDANT |
DYNAMIC | 行格式基本与 COMPACT 行格式相同，但为长可变长度列增加了增强的存储功能，并支持大索引键前缀，可以认为是compact的升级版本。 | 采用完全的行溢出方式，数据页不会存储真实数据的前768字节，只存储20个字节的指针来指向溢出页。而实际的数据都存储在溢出页中 |
COMPRESSED | 与 DYNAMIC 行格式相同，增加了对表和索引数据压缩的支持。 | 同DYNAMIC |
重点介绍 Compact 行格式，因为 Dynamic 和 Compressed 这两个行格式跟 Compact 非常像。所以，弄懂了 Compact 行格式，之后你们在去了解其他行格式，很快也能看懂。
4.1 COMPACT 行格式
先跟 Compact 行格式混个脸熟，它长这样：
可以看到，一条完整的记录分为「记录的额外信息」和「记录的真实数据」两个部分。
接下来，分别详细说下。
4.2 记录的额外信息
记录的额外信息包含 3 个部分：变长字段长度列表、NULL 值列表、记录头信息。
4.2.1 变长字段长度列表
varchar(n) 和 char(n) 的区别是什么，相信大家都非常清楚，char 是定长的，varchar 是变长的，变长字段实际存储的数据的长度（大小）不固定的。
所以，在存储数据的时候，也要把数据占用的大小存起来，存到「变长字段长度列表」里面，读取数据的时候才能根据这个「变长字段长度列表」去读取对应长度的数据。其他 TEXT、BLOB 等变长字段也是这么实现的。
为了展示「变长字段长度列表」具体是怎么保存「变长字段的真实数据占用的字节数」，我们先创建这样一张表，字符集是 ascii（所以每一个字符占用的 1 字节），行格式是 Compact，t_user 表中 name 和 phone 字段都是变长字段：
现在 t_user 表里有这三条记录：
接下来，我们看看看看这三条记录的行格式中的 「变长字段长度列表」是怎样存储的。
先来看第一条记录：
- name 列的值为 a，真实数据占用的字节数是 1 字节，十六进制 0x01；
- phone 列的值为 123，真实数据占用的字节数是 3 字节，十六进制 0x03；
- age 列和 id 列不是变长字段，所以这里不用管。
这些变长字段的真实数据占用的字节数会按照列的顺序逆序存放（等下会说为什么要这么设计），所以「变长字段长度列表」里的内容是「 03 01」，而不是 「01 03」。
同样的道理，我们也可以得出第二条记录的行格式中，「变长字段长度列表」里的内容是「 04 02」，如下图：
第三条记录中 phone 列的值是 NULL，NULL 是不会存放在行格式中记录的真实数据部分里的，所以「变长字段长度列表」里不需要保存值为 NULL 的变长字段的长度。
为什么「变长字段长度列表」的信息要按照逆序存放？
这个设计是有想法的，主要是因为「记录头信息」中指向下一个记录的指针，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。
「变长字段长度列表」中的信息之所以要逆序存放，是因为这样可以使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，这样就可以提高 CPU Cache 的命中率。
同样的道理， NULL 值列表的信息也需要逆序存放。
如果你不知道什么是 CPU Cache，可以看这篇文章 (opens new window)，这属于计算机组成的知识。
每个数据库表的行格式都有「变长字段字节数列表」吗？
其实变长字段字节数列表不是必须的。
当数据表没有变长字段的时候，比如全部都是 int 类型的字段，这时候表里的行格式就不会有「变长字段长度列表」了，因为没必要，不如去掉以节省空间。
所以「变长字段长度列表」只出现在数据表有变长字段的时候。
4.2.2 NULL 值列表
表中的某些列可能会存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中会比较浪费空间，所以 Compact 行格式把这些值为 NULL 的列存储到 NULL值列表中。
如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列。
- 二进制位的值为
1时，代表该列的值为NULL。
- 二进制位的值为
0时，代表该列的值不为NULL。
另外，NULL 值列表必须用整数个字节的位表示（1字节8位），如果使用的二进制位个数不足整数个字节，则在字节的高位补
0。还是以 t_user 表的这三条记录作为例子：
接下来，我们看看看看这三条记录的行格式中的 NULL 值列表是怎样存储的。
先来看第一条记录，第一条记录所有列都有值，不存在 NULL 值，所以用二进制来表示是酱紫的：
但是 InnoDB 是用整数字节的二进制位来表示 NULL 值列表的，现在不足 8 位，所以要在高位补 0，最终用二进制来表示是酱紫的：
所以，对于第一条数据，NULL 值列表用十六进制表示是 0x00。
接下来看第二条记录，第二条记录 age 列是 NULL 值，所以，对于第二条数据，NULL值列表用十六进制表示是 0x04。
最后第三条记录，第三条记录 phone 列 和 age 列是 NULL 值，所以，对于第三条数据，NULL 值列表用十六进制表示是 0x06。
我们把三条记录的 NULL 值列表都填充完毕后，它们的行格式是这样的：
每个数据库表的行格式都有「NULL 值列表」吗？
NULL 值列表也不是必须的。
当数据表的字段都定义成 NOT NULL 的时候，这时候表里的行格式就不会有 NULL 值列表了。
所以在设计数据库表的时候，通常都是建议将字段设置为 NOT NULL，这样可以至少节省 1 字节的空间（NULL 值列表至少占用 1 字节空间）。
「NULL 值列表」是固定 1 字节空间吗？如果这样的话，一条记录有 9 个字段值都是 NULL，这时候怎么表示？
「NULL 值列表」的空间不是固定 1 字节的。
当一条记录有 9 个字段值都是 NULL，那么就会创建 2 字节空间的「NULL 值列表」，以此类推。
4.2.3 记录头信息
记录头信息里的字段比较关键，以COMPACT为例，存储格式如下图：
名称 | 大小(Bit) | 说明 |
预留位1 | 1 | 没有使用 |
预留位2 | 1 | 没有使用 |
deleted_flag | 1 | 记录删除标记 |
min_rec_flag | 1 | B+树非叶子节点的最小目录项标记 |
n_owned | 4 | 同一页内同一组里最大的记录会记录组里的记录数量，其余记录该值为0 |
heap_no | 13 | 当前记录在页面堆里的相对位置 |
record_type | 3 | 记录类型。0:普通记录,1:B+树非叶子节点目录项记录,2:Infimum记录,3:Supremum记录. |
next_record | 16 | 下一条记录的相对位置 |
记录头信息的最后2字节用来连接下一条记录，将页内所有记录串联成一个单向链表。
其中Infimum和Supremum是索引页内的两条虚拟记录，InnoDB规定所有索引页都会有这两条记录，而且所有的用户记录都比Infimum大，都比Supremum小。
记录头信息里的heap_no代表记录在堆里的相对位置，该值越小代表记录越靠前。细心的同学会发现，上图中的用户记录heap_no值是从2开始的，那0和1呢？不说你也肯定猜到了，就是被Infimum和Supremum占用了。Infimum和Supremum的heap_no值分别是0和1，它俩在所有用户记录的最前面。
Infimum和Supremum结构非常的简单，和用户记录一样也有头信息，真实数据部分是固定的字符串，如下图所示：
我们把这两条虚拟记录也加入到记录里面，完整的结构就是下面这样的。其中 Supremum 记录的 next_record 属性为0，代表它已经没有下一条记录了。
4.3 记录的真实数据
记录真实数据部分除了我们定义的字段，还有三个隐藏字段，分别为：row_id、trx_id、roll_pointer，我们来看下这三个字段是什么。
- row_id
如果我们建表的时候指定了主键或者唯一约束列，那么就没有 row_id 隐藏字段了。如果既没有指定主键，又没有唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段。row_id不是必需的，占用 6 个字节。
- trx_id
事务id，表示这个数据是由哪个事务生成的。 trx_id是必需的，占用 6 个字节。
- roll_pointer
这条记录上一个版本的指针。roll_pointer 是必需的，占用 7 个字节。
如果你熟悉 MVCC 机制，你应该就清楚 trx_id 和 roll_pointer 的作用了。
4.4 行溢出
MySQL 中磁盘和内存交互的基本单位是页，一个页的大小一般是
16KB，也就是 16384字节，而一个 varchar(n) 类型的列最多可以存储 65532字节，一些大对象如 TEXT、BLOB 可能存储更多的数据，这时一个页可能就存不了一条记录。这个时候就会发生行溢出，多的数据就会存到另外的「溢出页」中。如果一个数据页存不了一条记录，InnoDB 存储引擎会自动将溢出的数据存放到「溢出页」中。在一般情况下，InnoDB 的数据都是存放在 「数据页」中。但是当发生行溢出时，溢出的数据会存放到「溢出页」中。
当发生行溢出时，在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。大致如下图所示。
上面这个是 Compact 行格式在发生行溢出后的处理。
Compressed 和 Dynamic 这两个行格式和 Compact 非常类似，主要的区别在于处理行溢出数据时有些区别。
这两种格式采用完全的行溢出方式，记录的真实数据处不会存储该列的一部分数据，只存储 20 个字节的指针来指向溢出页。而实际的数据都存储在溢出页中，看起来就像下面这样：
4.5 varchar(n) 中 n 最大取值为多少？
我们要清楚一点，MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节。
也就是说，一行记录除了 TEXT、BLOBs 类型的列，限制最大为 65535 字节，注意是一行的总长度，不是一列。
知道了这个前提之后，我们再来看看这个问题：「varchar(n) 中 n 最大取值为多少？」
varchar(n) 字段类型的 n 代表的是最多存储的字符数量，并不是字节大小哦。
要算 varchar(n) 最大能允许存储的字节数，还要看数据库表的字符集，因为字符集代表着，1个字符要占用多少字节，比如 ascii 字符集， 1 个字符占用 1 字节，那么 varchar(100) 意味着最大能允许存储 100 字节的数据。
4.5.1 单字段的情况
前面我们知道了，一行记录最大只能存储 65535 字节的数据。
那假设数据库表只有一个 varchar(n) 类型的列且字符集是 ascii，在这种情况下， varchar(n) 中 n 最大取值是 65535 吗？
不着急说结论，我们先来做个实验验证一下。
我们定义一个 varchar(65535) 类型的字段，字符集为 ascii 的数据库表。
看能不能成功创建一张表：
可以看到，创建失败了。
从报错信息就可以知道一行数据的最大字节数是 65535（不包含 TEXT、BLOBs 这种大对象类型），其中包含了 storage overhead。
问题来了，这个 storage overhead 是什么呢？其实就是「变长字段长度列表」和 「NULL 值列表」，也就是说一行数据的最大字节数 65535，其实是包含「变长字段长度列表」和 「NULL 值列表」所占用的字节数的。所以， 我们在算 varchar(n) 中 n 最大值时，需要减去 storage overhead 占用的字节数。
这是因为我们存储字段类型为 varchar(n) 的数据时，其实分成了三个部分来存储：
- 真实数据
- 真实数据占用的字节数
- NULL 标识，如果不允许为NULL，这部分不需要
本次案例中，「NULL 值列表」所占用的字节数是多少？
前面我创建表的时候，字段是允许为 NULL 的，所以会用 1 字节来表示「NULL 值列表」。
本次案例中，「变长字段长度列表」所占用的字节数是多少？
「变长字段长度列表」所占用的字节数 = 所有「变长字段长度」占用的字节数之和。
所以，我们要先知道每个变长字段的「变长字段长度」需要用多少字节表示？具体情况分为：
- 条件一：如果变长字段允许存储的最大字节数小于等于 255 字节，就会用 1 字节表示「变长字段长度」；
- 条件二：如果变长字段允许存储的最大字节数大于 255 字节，就会用 2 字节表示「变长字段长度」；
我们这里字段类型是 varchar(65535) ，字符集是 ascii，所以代表着变长字段允许存储的最大字节数是 65535，符合条件二，所以会用 2 字节来表示「变长字段长度」。
因为我们这个案例是只有 1 个变长字段，所以「变长字段长度列表」= 1 个「变长字段长度」占用的字节数，也就是 2 字节。
因为我们在算 varchar(n) 中 n 最大值时，需要减去 「变长字段长度列表」和 「NULL 值列表」所占用的字节数的。所以，在数据库表只有一个 varchar(n) 字段且字符集是 ascii 的情况下，varchar(n) 中 n 最大值 = 65535 - 2 - 1 = 65532。
我们先来测试看看 varchar(65533) 是否可行？
可以看到，还是不行，接下来看看 varchar(65532) 是否可行？
可以看到，创建成功了。说明我们的推论是正确的，在算 varchar(n) 中 n 最大值时，需要减去 「变长字段长度列表」和 「NULL 值列表」所占用的字节数的。
当然，我上面这个例子是针对字符集为 ascii 情况，如果采用的是 UTF-8，varchar(n) 最多能存储的数据计算方式就不一样了：
- 在 UTF-8 字符集下，一个字符最多需要三个字节，varchar(n) 的 n 最大取值就是 65532/3 = 21844。
上面所说的只是针对于一个字段的计算方式。
4.5.2 多字段的情况
如果有多个字段的话，要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535。
这里举个多字段的情况的例子（感谢@Emoji同学提供的例子）
总结
MySQL 的 Compact 行格式中会用「NULL值列表」来标记值为 NULL 的列，NULL 值并不会存储在行格式中的真实数据部分。
NULL值列表会占用 1 字节空间，当表中所有字段都定义成 NOT NULL，行格式中就不会有 NULL值列表，这样可节省 1 字节的空间。
MySQL 的 Compact 行格式中会用「变长字段长度列表」存储变长字段实际占用的数据大小。
一行记录最大能存储 65535 字节的数据，但是这个是包含「变长字段字节数列表所占用的字节数」和「NULL值列表所占用的字节数」。所以， 我们在算 varchar(n) 中 n 最大值时，需要减去这两个列表所占用的字节数。
如果一张表只有一个 varchar(n) 字段，且允许为 NULL，字符集为 ascii。varchar(n) 中 n 最大取值为 65532。
计算公式：65535 - 变长字段字节数列表所占用的字节数 - NULL值列表所占用的字节数 = 65535 - 2 - 1 = 65532。
如果有多个字段的话，要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL值列表所占用的字节数 <= 65535。
如果一个数据页存不了一条记录，InnoDB 存储引擎会自动将溢出的数据存放到「溢出页」中。
Compact 行格式针对行溢出的处理是这样的：当发生行溢出时，在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。
Compressed 和 Dynamic 这两种格式采用完全的行溢出方式，记录的真实数据处不会存储该列的一部分数据，只存储 20 个字节的指针来指向溢出页。而实际的数据都存储在溢出页中。
参考
- Author:mcbilla
- URL:http://mcbilla.com/article/4d50572d-cbd6-4ee4-8c75-c1ff15abc2a5
- Copyright:All articles in this blog, except for special statements, adopt BY-NC-SA agreement. Please indicate the source!
Relate Posts