用几个实验验证char、varchar和text的底层存储

type

status

date

slug

summary

varchar字段

我们先来用最常用的varchar单字段来练练手。注意，varchar后面的括号里的数字代表的是字符数，而并非字节数，具体的字节数需要结合对应的编码来计算。可以通过函数length(字段)和char_length(字段)进行区分。

单字节编码

为了演示，我们这里使用ascii编码（单字节编码，1个字符对应1个字节）

虽然MySQL Server对单行大小的限制是65535个字节，但是从上面的实验来看似乎并不能达到。因为行记录还有几个额外的信息需要占据这65535个字节：

变长字段varchar需要把自己的实际长度保存到一个变长长度列表里。这个长度位所占的空间和字段支持的最大长度有关，如果字段支持的最大长度大于255字节，需要使用2个字节保存

为了节约空间，nullable字段并不会单独存储，而是共享一个bitmap来标识字段值是否为null，每8个可空字段占用一个字节的8位

所以字段a实际能支持最大的字节数为65535 - 2 - 1 = 65532

多字节变长编码

前面用了一个单字节编码举例，主要是为了更加精准的来控制字段大小，也比较容易理解。如果是多字节变长编码，需要按照最大字节计算。比如utf8mb4（1~4字节的变长编码），那么需要按照4个字节（也就是我们前面说的“最坏情况”）计算。此时字段a的最大长度为65532 / 4 = 16383个字符

不过这里使用varchar(16384)建表时的报错信息和前面有一些细微的差别，错误码是1074——是针对于varchar列的限制，而不是单行大小限制。猜测这个varchar字段的限制应该也是因为单行65535的限制而限制的（不同的字符编码这个max是不一样的）。

注意，这里的65535个字节并没有考虑几个数据行里隐藏数据的影响：DB_ROW_ID（6字节）、DB_TRX_ID（6字节）和DB_ROLL_PTR（7字节），因为它们都是InnoDB层的概念，而非MySQL Server

text字段

不知道你有没有留意第一个实验的报错信息，里面除了报错，还附带有解决方案：提示我们可以把字段类型改成text或blob，看起来这俩并不会计算到row size里。

我们尝试给一张已经到极限的表加一个text字段：

还是超了。不过这里似乎是我们忘记了前面提到的变长字段的长度，text也需要2个字节存储其长度，而空值信息可以共享之前的那个字节，所以我们把a字段的长度减少2再试试：

依然报错，看起来似乎还有一些额外的存储占用。我们翻看了MySql官方文档，找到了如下两则描述：

The internal representation of a MySQL table has a maximum row size limit of 65,535 bytes, even if the storage engine is capable of supporting larger rows. BLOB and TEXT columns only contribute 9 to 12 bytes toward the row size limit because their contents are stored separately from the rest of the row. https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html

简单翻译一下：blob和text字段对于行大小（row size）占用只有9到12字节，因为它们的存储是和行数据分离的（但是存储的事儿并不是你Server的职责，感觉描述的不严谨，并且比如在InnoDB里text小于等于40字节也是必然和行数据一起存储的）。

The internal representation of a table has a maximum row size of 65,535 bytes, even if the storage engine is capable of supporting larger rows. This figure excludes BLOB or TEXT columns, which contribute only 9 to 12 bytes toward this size. For BLOB and TEXT data, the information is stored internally in a different area of memory than the row buffer https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html

而这一段大致意思和上面一段差不多，但是对于blob和text类型的字段，重点说明了在内存中表示时，并非保存在row buffer，而是其他区域。这个描述看起来更加精准一些，并且指明了是在内存中的表示。

具体的字段类型和row size的占用大小如下：

字段类型	占用字节大小
tinytext(tinyblob)	9 bytes
text(blob)	10 bytes
mediumtext(mediumblob)	11 bytes
longtext(longblob)	12bytes

按这个逻辑，那我们把a字段的长度再减10看看：

我们发现，最初我们给text算了2个字节的长度信息好像已经涵盖在10个字节里了。也就是说，text所占用的10个字节里应该已经包含了长度信息。

我们可以通过源码进一步感受一下，其中2个字节是长度信息，8个字节是指针：

InnoDB层的限制

有没有发现实验到现在碰到的都是MySQL Server层的限制，按理说InnoDB层的限制8126字节更小更容易达到，为什么前面都没有触发呢？主要因为上面演示的varchar和text都是变长字段，而InnoDB对于变长字段有一个溢出策略，所以它们在计算InnoDB层的行空间大小（row size）时也只“贡献”很小。

InnoDB层面的限制主要基于InnoDB要保证一页至少存两行数据，以此兜底性能。按照默认的1页16k大小，我们算出阈值即为(16384 - 38 - 36 - 20 - 26 - 8 - 4) / 2 = 16252 / 2 = 8126

其中：

FIL Header消耗38字节

Index Header消耗36字节

File Segment Header消耗20字节

Infimum & Supremum 两条虚拟记录共消耗26字节

FIL Trailer消耗8字节

Page Directory初始2个slots消耗4字节

变长字段超限

前面提到变长字段是可以溢出存储的，所以变长字段占用的行空间大小并不是其实际存储的数据的大小。比如按官方描述，text或blob字段值如果小于等于40字节，那么就直接存储在行内。所以对于text或blob字段在行内最大的存储长度是40 + 1 = 41字节，1字节是长度位大小

Whether columns are stored off-page depends on the page size and the total size of the row. When a row is too long, the longest columns are chosen for off-page storage until the clustered index record fits on the B-tree page. TEXT and BLOB columns that are less than or equal to 40 bytes are stored in line. https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html

💡

大于40个字节的也不一定溢出存储，溢出的条件还是看行大小有没有超过8126字节，只要没溢出就还是和行数据存一起。

text

关于text字段个数在InnoDB层面的极限，这篇文章有详细的计算过程。我这里总结一下重点：

text字段最大存储就是40个字节（溢出的情况只存一个指针，小于40字节），再加1个字节的长度，总共41字节

除了我们显式创建的text字段外，每行记录还存在一些额外信息

header信息，5字节
null值bitmap，（ceil(x/8) 即向上取整）字节
DB_ROW_ID（6字节）、DB_TRX_ID（6字节）和DB_ROLL_PTR（7字节）

那么计算公式就是：

最终我们可以计算出符合该公式的x的解为196。而x=197时，左边的结果刚好等于8126。此时如果我们给这张表加一个int类型（4字节）的主键，那么InnoDB就不会再加上6字节的ROW_ID，也就是可以减少2个字节（6 -> 4）的空间，从而支持197个text字段

varchar

这里我们还尝试了varchar字段个数的极限，varchar和text有一些区别，分两种情况：

如果建表时指定varchar的字节数（需要结合字符数和编码计算）大于等于40，那么和text的表现一致，最大存储就是按照40个字节计算。

如果varchar的最大存储字节数小于40，那么就按照最大存储字节计算

第一种情况和text一模一样，这里就不展开了。

这里只实验第二种情况，比如ascii编码的varchar(10)就是占用10个字节，再加上1个字节的长度，总共就是占用11个字节。

假设我们用ascii编码的varchar(19)，看看能建多少列，那么计算公式就是：

求得，x最大为402，并且此时还可以极限增加一列varchar(9)

定长字段超限

官方有个例子：https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html

33 * 255 + 5 = 8420 > 8126 肯定不行。而32 * 255 + 4 = 8164 > 8126还是不行。不过31个char(255)就可以了，那我们再算算第32个字段最大支持多少个字符。

最终算出符合公式的x最大为192。

定长字段，比如char、int、bigint等，由于不涉及到溢出的场景，直接按照字面大小计算即可。不过马上就会介绍一种特殊情况

char变长字符集

我们再来看看不定长编码的char，用utf8mb4编码试试，反而能支持到64个字段

并且再往上增长，也只是受到MySql Server层面的约束，而8126的约束似乎没有了。这也印证了mysql官网说的一个点：定长字段在长度超过768个字节会被编码成变长字段，比如utf8mb4编码下的char(255)字段。

When a table is created with ROW_FORMAT=DYNAMIC, InnoDB can store long variable-length column values (for VARCHAR, VARBINARY, and BLOB and TEXT types) fully off-page, with the clustered index record containing only a 20-byte pointer to the overflow page. Fixed-length fields greater than or equal to 768 bytes are encoded as variable-length fields. For example, a CHAR(255) column can exceed 768 bytes if the maximum byte length of the character set is greater than 3, as it is with utf8mb4. https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html

char是只有超过768字节才会变长还是只要是变长字符集就是变长？这个我们再通过一个实验印证一下。

也成功了，达到了127个字段，看起来并不是只有char(255)才会变长存储。char(128)也变长存储了

我们再来验证一下，这里需要注意，server层的计算不用考虑存储引擎层的逻辑，比如对于char来说，在server层并不会考虑其最终使用的是变长编码，在计算row size的时候不会把变长列表的长度考虑进去，所以计算公式为：

那么还剩余的行空间为65535 - 65040 = 495，剩余字段我们用ascii来编码，这样会更精准，那么至少还需要2个char字段，并且此时null值列表需要再增加1个字节（17个字节）保存了。其中一个字段为char(255)，则另一个字段为char(239)，计算逻辑为：495 - 1 - 255 = 239

这篇文章通过ibd文件来验证char在不定长字符编码下是被当做变长字段保存的。

行溢出

前面提到过变长字段会溢出存储，溢出存储的临界点是InnoDB要保证一页至少要存放2条行数据。溢出的条件是行大小，但是溢出存储的却是变长列（会优先选择实际存储最多的列）。下面我们通过实验来加深一下感受：

我们新建一张测试列溢出的表

根据off_page表的结构我们可以算出行溢出的临界点是

name字段长度大于等于8102字节则会溢出存储。我们通过information_schema.TABLES表来验证看看

可以看到，此时应该是增加了一个溢出页，那我们来看看这个溢出页可以保存的数据是多少。二分法试得结果为15680，在a字段的大小为15681时，此时又会增加一个溢出页。多个溢出页之间用单向链表连接（这是MySQL 8.0版本之前的结构，8.0之后就变了，下面会说）。

那第二个溢出页可以保存的大小也是15680吗？答案是否定的，我们又试出第二个溢出页的容量为16327

除了第一个溢出页的容量不一样之外，后面的溢出页的容量都是16327

为什么溢出页的第一页和其他页的容量不一样呢？原来是因为MySQL 8.0新增的JSON字段部分更新的功能带来的影响。简单来说就是把LOB（Large Object）的存储结构从原来的单一类型（FIL_PAGE_TYPE_BLOB）链表改造成了一个FIL_PAGE_TYPE_LOB_FIRST + FIL_PAGE_TYPE_LOB_INDEX + FIL_PAGE_TYPE_LOB_DATA 三种页类型的组合，所以第一页FIL_PAGE_TYPE_BLOB的容量和后续的不一样。这里就不展开细说了，感兴趣的可以看这篇文章，不想看的也可以等后面我的后续文章~

💡

这篇文章里实验的溢出页的类型都是一样的，且容量都是16330，版本是5.1.73

我们再看看FIL_PAGE_TYPE_LOB_FIRST 页的物理结构（来源https://developer.aliyun.com/article/598070）

字段	字节数	描述
OFFSET_VERSION	1	表示lob的版本号，当前为0，用于以后lob格式改变做版本区分
OFFSET_FLAGS	1	目前只使用第一个bit，被设置时表示无法做partial update，用于通知purge线程某个更新操作产生的老版本LOB可以被完全释放掉
OFFSET_LOB_VERSION	4	每个lob page都有个版本号，初始为1，每次更新后递增
OFFSET_LAST_TRX_ID	6	ㅤ
OFFSET_LAST_UNDO_NO	4	ㅤ
OFFSET_DATA_LEN	4	存储在该page上的数据长度
OFFSET_TRX_ID	6	创建存储在该page上的事务id
OFFSET_INDEX_LIST	16	维护lob page链表
OFFSET_INDEX_FREE_NODES	16	维护空闲节点
LOB_PAGE_DATA	600	存储数据的起始位置，注意第一个page同时包含了lob index 和lob data，但在第一个lob page中只包含了10个lob index记录，每个lob index大小为60字节

根据上表的结构，可以计算得出1 + 1 + 4 + 6 + 4 + 4 + 6 + 16 + 16 + 600 = 658，再加上FIL Header（38字节）和FIL Trailer（8字节），658 + 38 + 8 = 704，那么整个页面刚好剩下15680字节，16384 - 704 = 15680

总结

我们通过几组建表实验验证了MySQL Server层面和InnoDB层面对行记录大小的限制，当然建表层面因为没有实际数据，考虑的都是最坏情况，保证在最坏情况下，也不会超过这两个层面的限制。

在实验过程中我们也验证了一些其他的结论：

char字段在变长编码下的表现和变长字段一样，比如

也需要额外的空间保存实际的长度信息
也可以溢出存储

MySQL Server层限制单行记录的大小不超过65535字节

InnoDB层限制单行记录的大小不能超过innodb_page_size的一半（实际上还要小一点，因为要扣除一些页中元数据信息），以默认的16K设置为例，其限制为8126字节

Server层和存储引擎层对于一行数据有自己的视角，需要理解这两者的差异

MySQL 8.0版本开始，溢出页的首页和后续页的结构是不一样的，首页里有索引结构可以根据offset快速定位到所在页，首页预留了10个索引位，不够的话会增加专门的索引页。首页的数据容量为15680，后续页的索引容量为16327

这只是通过创建表时的一些校验规则来验证char、varchar、text的底层存储规则。后续我们还会通过观察一行数据是如何存储在ibd文件上的来继续分析存储原理。

参考

技术分析 | MySQL TEXT 字段的限制

技术分享 | MySQL 字段长度限制的计算方法

可变长度字段在 InnoDB 中的存储方式

Externally Stored Fields in InnoDB

MySQL 8.0: InnoDB Introduces LOB Index For Faster Updates

MYSQL 中 JSON 类型介绍 | 京东物流技术团队

MySQL8.0 新特性：Partial Update of LOB Column

Blob Storage in Innodb