声明：全网搜索本文的标题有很多，各个网站都有转载本文，本文内容已更新。

此篇讲解varchar存储原理，知识难度较大且涉及到计算，欢迎有兴趣者阅读。

1. InnoDB是干嘛的？

  InnoDB是一个将表中的数据存储到磁盘上的存储引擎。

2. InnoDB是如何读写数据的？

  InnoDB处理数据的过程是发生在内存中的，需要把磁盘中的数据加载到内存中，如果是处理写入或修改请求的话，还需要把内存中的内容刷新到磁盘上。

  读写磁盘的速度非常慢，和内存读写差了几个数量级，所以当我们想从表中获取某些记录时，InnoDB存储引擎将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小默认为 16 KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，或者一次最少把内存中的16KB内容刷新到磁盘中。

  所以当你用postman测试一个HTTP分页查询接口（每页10条数据）时，发现第一次打印耗时300 ~ 400ms，往后不停的查找下一页10条数据时都是30 ~ 40ms，原因就是第一次请求接口时，读数据库的时候需要读磁盘，从磁盘加载16KB的数据到内存，往后HTTP请求每次查10条数据的时候都是从内存中获取，没有再读磁盘，除非在内存中的16KB的数据中找不到，才会再次读磁盘获取下一个16KB的数据到内存中。（我们不讨论mysql 8.0舍弃的查询缓存特性，我测试过mysql 5.7中关闭了查询缓存，也仍然是第一次慢，后续查询很快，查询时间相差大概10倍的样子）

温馨提示：分页查询和数据库的一页16KB中的"页"是两个概念。

  总结：由于磁盘I/O速度相对内存来说较慢，因此第一次查询可能会比较耗时。一旦数据被加载到内存中，后续的查询就可以直接从内存中读取数据，这样的速度要比从磁盘读取数据快得多。这就解释了为什么第一次查询可能会比后续的查询慢。

查看磁盘和内存之间进行数据交换的页有多大

注意：innodb_page_size变量在服务器运行过程中不可以更改，只能在第一次初始化MySQL数据目录时指定。所以页在运行时的大小不可更改。

3. InnoDB行格式

  看到这里，你一定有着和我相同的疑问，比如varchar(255)后面这个最大长度应该怎么选择呢？为什么不能varchar(65535)而最大只能varchar(16383)呢？

  我们平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为行格式或者记录格式。行格式有4种，分别是Dynamic、Compact、Redundant和Compressed

MySQL 5+默认行格式都是Dynamic， 在MySQL 5 和 MySQL 8经过验证确实是的。

SHOW VARIABLES LIKE "innodb_default_row_format"

  大家在业务中和平时使用中都几乎没有修改过或者注意过InnoDB行格式，那么我就只重点讲默认行格式dynamic，让大家更深层次理解平时开发中的varchar。

请记住这个表结构，后面会围绕这个来讲

CREATE TABLE test ( 
c1 VARCHAR(10), 
c2 VARCHAR(10) NOT NULL, 
c3 CHAR(10), 
c4 VARCHAR(10)) CHARSET = utf8mb4;

现在业务数据库字符集都是utf8mb4，我就以这个来讲，把理解难度降到最低。

INSERT INTO test ( c1, c2, c3, c4 )
VALUES('aaaa', '你好啊', 'cc', 'd'),('eeee', 'fff', NULL, NULL);

现在，表中的记录就是这样

3.1 dynamic——innodb默认行格式

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

  在这里我只讲变长字段长度列表、NULL值列表。因为记录头信息非常的绕和本篇没多大关系。

3.2 innodb怎么知道varchar真正有多长？——变长字段长度列表

  一些变长的数据类型，比如VARCHAR(M)、各种TEXT类型，各种BLOB类型，变长数据类型的字段中存储多少字节的数据是不固定的，在存储真实数据的时候需要把这些数据占用的字节数也存起来。

  就像设计String类型，不仅仅是存放真实数据的char数组，还有length变量去记录字符串长度。又比如input输入框最大限制500字，但是还得有一个变量去统计真实在输入框内有多少字符。同理，varchar也有记录真实数据长度的变量（假设为L，后文沿用方便描述），L表示varchar真实占用的字节数，innodb最多分配2个字节去表示这个L，就像unsigned short类型，2个字节，寄存器最多只有16位来存这个长度，所以L记录范围是2^16 - 1 = 65535。

这些变长字段(比如varchar)占用的存储空间分为两部分：

1. 真正的数据内容部分，放在对应的列
2. 真实占用的字节数，放在变长字段列表部分

  我们拿test表中的第一条记录来举个例子。因为test表的c1、c2、c4列都是VARCHAR(10)类型的，说明最大10个字符，所以这三个列的值的长度都需要保存在记录开头处，因为test表中的各个列都使用的是utf8mb4字符集，每个字符最大需要4个字节来进行编码（不使用utf8而是utf8mb4是因为可能存储emoji表情，如果只是文字，utf8就足够），来看一下第一条记录各变长字段内容的长度：

怎么确定这些字段有多少字节？

比如这里c2的"你好啊"，使用如下sql可以确定

SELECT LENGTH(c2) from test where c1='aaaa';

各变长字段数据占用的字节数按照列的顺序逆序存放！！

  由于第一行记录中c1、c2、c4列中的字符串都比较短，也就是说varchar真实占用的字节数比较小，L用1个字节(8个bit位) 就可以表示，但是如果varchar真实占用的字节数比较多，L可能就需要用2个字节(16个bit位) 来表示。到底varchar能存多少字节呢？继续往下看。

3.3 varchar(M) 能存多少个字符，为什么提示最大16383？

首先要理解varchar(M)的M是说字符个数，而不是字节。

为什么不能varchar(20000)之类的，是20000个字符放不下吗？

为什么提示只能最大16383个字符呢？这个数字是怎么算出来的？

这就得好好唠嗑了！

  varchar是变长的，varchar(64) 能存放0~64个字符不等，并不一定是存了最大64个字符，谁知道这个类型到底存了几个字符呢？innodb设计的时候，就已经考虑到了，不过是用字节作为单位，后续我们可以根据对应字符集转变为字符来理解，innodb必须记录变长字段varchar真实占用的字节数L。前面说过了，innodb最多分配2个字节(16个bit位)的空间去记录这个L。

InnoDB有它的一套规则，我们引入W、M和L这几个符号：

1. 假设某个字符集中最多需要W字节来表示一个字符

• utf8mb4字符集中的W就是4
• utf8字符集中W就是3
• gbk字符集中的W就是2
• ascii字符集中的W就是1。
  

1. 对于变长类型VARCHAR(M)来说，这种类型表示能存储最多M个字符（注意是字符不是字节）
   所以这个类型能表示的字符串最多占用的字节数就是M × W。
   
2. 假设它实际存储的字符串占用的字节数是L。

  来看极限边界情况，innodb为了记录一下varchar真实存储多少个字节，最多分配2个字节的空间去记录，2个字节16个比特位，全部为1，最大能记录的数字是2^16-1是65535个，innodb最大能记录varchar占用的字节数就是65535个，utf8mb4字符集一个字符是最大是4个字节，65535 / 4 = 16383.75，只要varchar字符数不超过16383个，innodb就可以记录真实占用的长度L，再多就记录不了了！所以就能解释刚刚的图了，varchar(20000)不行，最大也就16383个字符

但是！这里强调是有但是的！

  行最大长度是65535字节，行里面有很多东西，包括变长字段列表、NULL值列表、记录头信息。我们得考虑该字段如果允许为NULL，NULL值列表会占用一个字节(只要没超过8个字段)，每一列字段的变长字段实际长度会花费1~2个字节，而且在操作中还会存在内部碎片，后面会讲解。所以即便提示16383个字符，也绝对不可能存到16383。

我做了个测试

create table t2 ( name varchar(16383))charset=utf8mb4;

不断往这个字段添加字符保存测试，最后发现，这些字符总长度到极限也就是48545字节。

如果超过就会报错

  这里48545个字节，再多一个字符就会报错，远不到65535字节，差了1W多字节。主要是因为溢出列的原因，数据分散在不同的行中，所以，很长的数据，建议往text类型考虑。这个现象可以看出，varchar(M)的M很大，实际是达不到M这个边界值的。

  我使用的是英文字母测试而不是中文字符，大部分不是4字节的，所以能够存储更多的字符。如果考虑到额外的元数据，实际能够存储的VARCHAR字符数会更少，关于影响每行的实际可用空间有哪些因素，请接着往下看后面小节。

下面说明一下规则(讲解中字符集用utf8mb4，W=4)

  规则一：如果允许存储的最大字节数M × W <= 255，varchar占用的真实字节数L只分配1个字节来表示。

  有人说，允许存储的最大字节数M × W <= 255，即允许存储的最大字符数 <= ⌊255 / 4⌋ = 63个时，varchar占用的真实字节数L仅分配1个字节就能表示。这个结论正确吗？
  显然错误，因为这里255 / 4，这怎么知道每个存储的一个字符是4个字节呢？难道全部存的emoji表情？不存字母汉字啥的？

  实际上不是所有的字符都会占用W个字节。例如，在utf8mb4字符集中，一个英文字母只占用1个字节，而一个emoji表情符号会占用4个字节。因此，“最多M个字符”并不意味着总是需要M × W个字节。
  InnoDB在读记录的变长字段长度列表时先查看表结构，如果某个变长字段允许存储的最大字节数不大于255时，只用1个字节来表示真实数据占用的字节。

  规则二：如果允许存储的最大字节数M × W > 255，则分为两种情况：

  如果实际存储字节L <= 127，varchar占用的真实字节数L仅分配1个字节就能表示。(⌊ .. ⌋表示向下取整)

  有人说，实际存储字节L <= 127，即实际存储字符 <= ⌊127 / 4⌋ = 31个时，varchar占用的真实字节数L仅分配1个字节就能表示。这个结论正确吗？
  还是错误，道理和上面一样。

  如果实际存储字节L > 127，varchar占用的真实字节数L需要分配2个字节才能表示。

为什么分界线是127？

  在变长字段长度的编码中，选择127作为阈值，是基于兼顾存储效率和容量需求的考虑。 当字段长度小于等于127字节时，只需要使用一个字节来表示长度。这是因为在很多情况下，字段值较短，为了存储效率，尽量减少记录长度的存储开销。这种情况下，一个字节足够表示长度，并且留有一个最高位作为标志位，用于区分是否需要更多字节来存储长度值。

  当实际存储的字节L > 127时，即需要更多空间来存储实际长度，InnoDB会分配2个字节（最多到2^16 - 1即65535）来记录长度。这是因为超过127后，一个字节不足以表示所有可能的长度值，尤其是当字段容量很大时（如VARCHAR(16383)在utf8mb4编码下）。

另外需要注意的是，变长字段列表只存储非NULL的列的长度。

表记录是这样的

对于第二条记录，c4列值为NULL，所以只存储c1和c2列即可。

  第一条记录的变长字段长度列表部分占用3字节空间，因为有c1、c2、c4列，且内容都很少，每列真实占用字节数用1个字节可以表示，加起来就是3个字节，第二条记录变长字段长度列表部分占用2字节。

  当然，并不是所有记录都有这个变长字段长度列表部分，比方说表中所有的列都不是变长的数据类型或者 所有列的值都是NULL 的话，这一部分就不需要有。实际业务开发中，几乎没有不使用varchar的，所以实际开发中的记录都会有变长字段长度列表部分

3.4 记录为NULL，innodb如何处理？——NULL值列表

  如果你和我一样开发规范中不推荐NULL，一般都写NOT NULL，其实记录中就不存在NULL值列表了，也节省了空间。

  如果表中的某些列可能存储NULL值，把这些NULL值都放到记录的真实数据中存储会很占地方，所以dynamic行格式把这些值为NULL的列统一管理起来，存储到NULL值列表中，它的处理过程是这样的：

1. 统计表中允许存储NULL的列有哪些。
   
   主键列、被NOT NULL修饰的列都是不可以存储NULL值的，所以在统计的时候不会把这些列算进去。比方说表test的3个列c1、c3、c4都是允许存储NULL值的，而c2列是被NOT NULL修饰，不允许存储NULL值。
   

2. 如果表中没有允许存储 NULL 的列，则 NULL值列表也不存在了，否则将每个允许存储NULL的列对应一个二进制位，二进制位按照列的顺序逆序排列。二进制位的值为1时，代表该列的值为NULL，为0时，代表该列的值不为NULL。因为表test的c1、c3、c4都是允许存储NULL值的允许为NULL的列，所以这3个列和二进制位的对应关系就是这样：
   

3. NULL值列表必须用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则在字节的高位补0。

   也就是说，表test只有3个字段允许为NULL，对应3个二进制位，不足1字节，那么就在高位补0即可。
   

以此类推，如果表中有9个字段都允许为NULL，那么这个记录的NULL值列表就需要2个字节来表示，高字节高位补0。

对于第一条记录，c1、c3、c4都不为NULL，对应的为进制位为0，十六进制表示就是0x00

对于第二条记录，c3、c4都是NULL，对应的二进制位为1，十六进制表示就是0x06

这两条记录在填充了NULL值列表后示意图如下：

3.5 为什么varchar(16383)存不到理论字符16383，影响每行实际可用空间的因素有哪些？

  在utf8mb4字符集下，VARCHAR(16383)代表的是最多可以存储16383个字符。由于utf8mb4编码下，一个字符最多占用4个字节，所以理论上VARCHAR(16383)最多可以占用 16383 * 4 = 65532字节。但是还需要考虑到InnoDB的元数据和内部碎片等空间，由于这些额外的开销，无法在一个VARCHAR(16383)字段中存储16383个字符。

  内部碎片：内部碎片主要是由于数据库页（Page）或块（Block）的固定大小导致的。InnoDB的页大小通常设置为16KB，每一页中包含了多行数据以及额外的页级元数据。如果一页中的数据没有完全填满这个空间，那么剩余的空间就会成为内部碎片，不能被其他行使用。

  内部碎片通常在以下情况中出现：

1. 固定大小的数据页/块：数据库通常使用固定大小的数据页（例如，在InnoDB中，页的大小通常为16KB）来存储数据。如果一页中的数据没有完全填满这个空间，剩下的空间就会成为内部碎片。
2. 数据更新：当一个字段的值被更新为一个更小的值时，剩下的空间可能会成为内部碎片。数据库可能会保留这个空间，以便在未来这个字段的值再次增大时使用。
3. 预留空间：为了提高性能，数据库可能会预留一些空间，使得数据的插入和更新操作不需要立即重新分配空间。这些预留的空间也会成为内部碎片。

举个例子：

我们创建一个包含VARCHAR字段的表：

CREATE TABLE test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(100)
);

然后我们插入一行数据，其中data字段填充了100个字符：

INSERT INTO test (data) VALUES (REPEAT('a', 100));

接下来我们更新这行数据，将data字段的值改为只有10个字符：

UPDATE test SET data = REPEAT('a', 10) WHERE id = 1;

  在这个例子中，当我们更新data字段的值时，原来占用的100个字符的空间并不会立即被收缩，即使新的值只有10个字符。这就产生了内部碎片，即那些不再使用但还未被回收的空间。

  这种内部碎片化的影响在实际操作中可能并不那么明显，因为数据库系统会尽可能地重用这些空间。如果后续有新的数据需要更多的空间，这些内部碎片的空间就可能会被利用起来。但是如果后续数据主要进行读操作而很少进行写操作的情况下，内部碎片可能会成为影响数据库性能的一个因素。

  假设我们有一个包含大量数据的表，这个表目前主要进行读操作，而写操作则相对较少。如果这个表存在大量的内部碎片化（可能是由过去的写操作留下的，例如更新和删除），那么实际存储的数据可能只占用了可用空间的一小部分，大量的空间被内部碎片占用。这种情况下，数据库需要加载更多的页到内存中来获取相同量的数据，这会增加I/O操作，从而降低读操作的性能。

除了内部碎片之外，影响每行实际可用空间的其他因素可能包括以下几个：

1. 元数据：前文已经介绍，每行的元数据（包括记录头信息、NULL值列表和变长字段长度列表）都会占用一部分空间。
2. 行格式：InnoDB的行格式（COMPACT，DYNAMIC或REDUNDANT）会影响每行的实际可用空间。例如，COMPACT格式会更紧凑，因此可能会提供更多的可用空间。
3. 溢出页：对于非常大的字段（如BLOB和TEXT类型），InnoDB可能会将数据存储在单独的溢出页中，而不是直接在数据行中。这可以使得数据行保持较小的大小，但也会增加存储和检索这些字段的复杂性。

以下是一些主要的元数据：

• 记录头信息：每一行记录在InnoDB中都有一个记录头，包含了一些元数据，如记录类型、下一个记录的位置等。记录头的大小通常为5-7字节。
• NULL值列表：如果表中的字段允许NULL值，InnoDB会为每一行记录维护一个NULL值列表，用于标记哪些字段的值为NULL。每一个可以为NULL的字段会在这个列表中占用1位（不是1字节）。所以，如果有n个字段可以为NULL，那么NULL值列表就需要n位，即⌈n/8⌉字节（向上取整）。
• 变长字段长度列表：对于变长字段（如VARCHAR、VARBINARY、TEXT和BLOB类型），InnoDB需要存储每个字段实际值的长度。如果字段的最大可能长度不超过255字节，那么这个长度值会占用1个字节；如果字段的最大可能长度超过255字节，那么长度值可能会占用1个字节（如果实际长度不超过127字节）或2个字节（如果实际长度超过127字节）。

通常来说，内部碎片和元数据可能会对每行的实际可用空间产生最大的影响。

注意：CHAR类型和VARCHAR类型在元数据和内部碎片方面有些不同：

1. 元数据：由于CHAR类型是固定长度的，所以它不需要像VARCHAR类型那样存储额外的元数据来表示实际的长度。这意味着对于同样长度的字符串，CHAR类型会使用更少的空间来存储元数据。
2. 内部碎片：CHAR类型由于是固定长度的，可能会产生内部碎片。比如，如果定义了一个CHAR(100)字段，但实际上只存储了10个字符的字符串，那么剩下的90个字符的空间就会被浪费，这就是内部碎片。另一方面，VARCHAR类型只会使用实际所需的空间，因此内部碎片会较少。

所以，尽管CHAR类型不需要存储长度的元数据，但它可能会因为固定长度的特性而产生更多的内部碎片。

在MySQL中，任何类型的列都可以被声明为NULL或NOT NULL，所以CHAR类型也可以有NULL值列表。

3.6 某个列数据占用的字节数非常多怎么办？——dynamic行格式的溢出列

  在MySQL 5.7及之后的版本中，默认的行格式是DYNAMIC。在DYNAMIC行格式中，如果一个字段的大小超过了页面的可用空间，该字段就会被存储为溢出列。需要强调的是，这种机制主要适用于可变长度的大型数据类型，如BLOB和TEXT类型字段。

这里是一个例子：

CREATE TABLE `big_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` longblob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

  在这个表中，data列的类型是longblob，这意味着它可以存储的数据长度最大达到4GB。如果插入一个大于64KB的数据到这个表，那么data列的数据就会被作为溢出列处理。

INSERT INTO big_data (data) VALUES (REPEAT('a', 67000));

  在这个例子中，插入的数据长度已经超过了64KB，所以data列的数据会被作为溢出列处理。在原始的表中，data列只会存储一个20字节的指针，这个指针指向实际数据的存储位置。

  这样的设计可以确保每个页内的数据都保持在合理的大小范围内，避免了由于单个字段数据过大导致的页分裂等问题，从而提高了整体的存储效率和查询性能。同时，对于读取溢出列的数据，虽然可能需要额外的磁盘I/O，但只要数据的访问是顺序的，通常这个开销并不会太大。

  再举个例子，如果一条记录里面有个字段是TEXT类型或它的变种（TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT）类型，那实际存储结构是什么样的？

  在MySQL中，如果使用MEDIUMTEXT类型字段时，实际存储结构的设计确实允许存储的数据量超过单条记录通常的大小限制（例如，InnoDB表的单行大小限制通常约为65535字节）。

  如果数据大小超过一定限制（这个限制取决于InnoDB的行格式），数据不会直接存储在表的行内。相反，行内会存储一个指针，指向实际数据存储的位置。这里涉及的外部存储可以是系统表空间、文件或独立表空间。这种方式允许MySQL存储大量的文本或二进制数据，远远超过单条记录的大小限制。

  比如具体到MEDIUMTEXT，它可以存储最多16,777,215字节（约16MB）的数据。当存储大量数据时，这种分离存储的结构允许：

• 减少主数据文件的大小：由于只在主数据文件中存储指向实际数据的指针，因此可以减少主数据文件的大小，提高数据检索的效率。

• 优化I/O操作：对于大型文本或二进制数据的读写操作可以直接在外部存储位置进行，避免了大量数据在主数据文件中的频繁读写，提高了I/O操作的效率。

  然而，这种存储方式也有其缺点，比如对于大量的小型TEXT或BLOB字段的读写操作，可能会因为频繁的外部文件访问而降低性能。但是并不一定会导致查询变得非常耗时，这主要取决于查询的性质、数据库的配置、系统资源以及数据的实际存储方式。

查询过程

对于含有大型文本字段的记录，查询过程通常遵循以下步骤：

1. 定位记录：首先，MySQL需要定位到包含所需数据的记录。这个过程可能涉及到扫描表的数据页或使用索引来快速定位记录。

2. 读取指针：对于大型文本字段，记录中实际存储的是指向数据实际存储位置的指针而不是数据本身。

3. 访问外部存储：MySQL根据指针信息访问外部存储（如系统表空间或独立表空间文件），读取实际的文本数据。

4. 返回结果：读取到的数据被处理（如应用任何查询中的过滤条件或函数）并返回给客户端。

写入过程

1. 内联存储尝试：当向表中插入包含大字段的记录时，InnoDB首先尝试将数据（无论大小）存储在行内。具体能存储多少数据取决于该行的其他数据所占用的空间以及InnoDB页面（通常为16KB）的大小限制。

  在MySQL 5.7之前，会将数据（前768字节）存储在行内，768字节是一个权衡结果，旨在平衡行内存储的效率和外部存储访问的需求，选择这个大小是基于常见的查询模式和数据存储效率的考虑。从MySQL 5.7开始，InnoDB的DYNAMIC行格式对变长字段（如TEXT和BLOB）的处理更加灵活，并没有固定将前768字节数据存储在行内的规则。行格式会根据可用空间和数据大小动态决定数据的存储方式，而不是简单地基于768字节的阈值。

1. 外部存储条件：

  如果整个记录（包括大字段数据）的大小超过了页面大小的一定比例（这个比例由InnoDB的内部算法决定，以优化存储效率和访问速度），InnoDB将选择将全部大字段数据存储在外部页中。在这种情况下，行内将保留一个指针指向外部存储的大字段数据的位置，这种方式减少了因单个字段数据过大导致的页分裂问题，优化了存储效率和查询性能

示例

• 750字节数据示例：如果一个MEDIUMTEXT字段包含750字节的数据，并且行内有足够的空间，则这750字节的数据很可能完全存储在行内，不需要外部存储。

• 1000字节数据示例：对于1000字节的数据，如果包含该MEDIUMTEXT字段的记录的总大小超过了InnoDB优化存储的阈值，行内会保留一个指向这些外部页的指针，不会出现既存有实际数据的一部分又有指向外部存储页的指针的情况。这种处理方式确保了数据的完整性和存取效率。

欢迎一键三连~



有问题请留言，大家一起探讨学习

----------------------Talk is cheap, show me the code-----------------------