用几个实验验证char、varchar和text的底层存储
发布于:2024-4-7
最后更新: 2024-4-16
次查看
MySQL
URL
date
slug
status
tags
summary
type
我们知道，MySQL分为Server层和存储引擎层。而主流的存储引擎一般都使用InnoDB，主要因为它支持事务，它也是MySQL默认的存储引擎。
而Server层和InnoDB层对于单行记录的大小，有各自的限制规则：
  1. MySQL Server层限制单行记录的大小不超过65535字节
      2. Despite differences in storage layout on disk, the internal MySQL APIs that communicate and exchange information about table rows use a consistent data structure that applies across all storage engines. https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html
  1. InnoDB层限制单行记录的大小不能超过innodb_page_size的一半（实际上还要小一点，因为要扣除一些页中元数据信息），以默认的16K设置为例，其限制为8126字节。
⚠️
看到这里你会不会认为，8126 < 65535，那限制就是8126字节了。不是这样的，因为Server层和InnoDB层对于行记录大小的计算逻辑不一样。接着往下看吧
下面我们会通过一些实验来验证上面说的两个限制。

varchar字段

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

单字节编码

为了演示，我们这里使用ascii编码（单字节编码，1个字符对应1个字节）
mysql> CREATE TABLE `row_size_limit`  (
  `a` varchar(65535) CHARACTER SET ascii COLLATE ascii_bin NULL
);
1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
虽然MySQL Server对单行大小的限制是65535个字节，但是从上面的实验来看似乎并不能达到。因为行记录还有几个额外的信息需要占据这65535个字节：
  1. 变长字段varchar需要把自己的实际长度保存到一个变长长度列表里。这个长度位所占的空间和字段支持的最大长度有关，如果字段支持的最大长度大于255字节，需要使用2个字节保存
  1. 为了节约空间，nullable字段并不会单独存储，而是共享一个bitmap来标识字段值是否为null，每8个可空字段占用一个字节的8位
所以字段a实际能支持最大的字节数为65535 - 2 - 1 = 65532
mysql> CREATE TABLE `row_size_limit`  (
  `a` varchar(65533) CHARACTER SET ascii COLLATE ascii_bin NULL
);
1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

mysql> CREATE TABLE `row_size_limit`  (
  `a` varchar(65532) CHARACTER SET ascii COLLATE ascii_bin NULL
);
Query OK, 0 rows affected (0.06 sec)

多字节变长编码

前面用了一个单字节编码举例，主要是为了更加精准的来控制字段大小，也比较容易理解。如果是多字节变长编码，需要按照最大字节计算。比如utf8mb4（1~4字节的变长编码），那么需要按照4个字节计算。此时字段a的最大长度为65532 / 4 = 16383个字符
mysql> CREATE TABLE `row_size_limit`  (
  `a` varchar(16384) CHARACTER SET utf8mb4 NULL
);
1074 - Column length too big for column 'a' (max = 16383); use BLOB or TEXT instead

mysql> CREATE TABLE `row_size_limit`  (
  `a` varchar(16383) CHARACTER SET utf8mb4 NULL
);
Query OK, 0 rows affected (0.05 sec)
不过这里使用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里。
1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
我们尝试给一张已经到极限的表加一个text字段：
mysql> CREATE TABLE `row_size_limit`  (
  `a` varchar(65532) CHARACTER SET ascii COLLATE ascii_bin NULL
);
Query OK, 0 rows affected (0.04 sec)

mysql> ALTER TABLE `test`.`row_size_limit`
ADD COLUMN `b` text NULL AFTER `a`;
1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
还是超了。不过这里似乎是我们忘记了前面提到的变长字段的长度，text也需要2个字节存储其长度，而空值信息可以共享之前的那个字节，所以我们把a字段的长度减少2再试试：
mysql> CREATE TABLE `row_size_limit`  (
  `a` varchar(65530) CHARACTER SET ascii COLLATE ascii_bin NULL
);
Query OK, 0 rows affected (0.05 sec)

mysql> ALTER TABLE `test`.`row_size_limit`
ADD COLUMN `b` text NULL AFTER `a`;
1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
依然报错，看起来似乎还有一些额外的存储占用。我们翻看了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看看：
// 65521可以
mysql> CREATE TABLE `row_size_limit`  (
  `a` varchar(65521) CHARACTER SET ascii COLLATE ascii_bin NULL
);
Query OK, 0 rows affected (0.02 sec)

mysql> ALTER TABLE `test`.`row_size_limit`
ADD COLUMN `b` text NULL AFTER `a`;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop table row_size_limit;
Query OK, 0 rows affected (0.03 sec)

// 65522也可以
mysql> CREATE TABLE `row_size_limit`  (
  `a` varchar(65522) CHARACTER SET ascii COLLATE ascii_bin NULL
);
Query OK, 0 rows affected (0.04 sec)

mysql> ALTER TABLE `test`.`row_size_limit`
ADD COLUMN `b` text NULL AFTER `a`;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop table row_size_limit;
Query OK, 0 rows affected (0.03 sec)

// 65523就不行了
mysql> CREATE TABLE `row_size_limit`  (
  `a` varchar(65523) CHARACTER SET ascii COLLATE ascii_bin NULL
);
Query OK, 0 rows affected (0.04 sec)

mysql> ALTER TABLE `test`.`row_size_limit`
ADD COLUMN `b` text NULL AFTER `a`;
1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
我们发现，最初我们给text算了2个字节的长度信息好像已经涵盖在10个字节里了。也就是说，text所占用的10个字节里应该已经包含了长度信息。
我们可以通过源码进一步感受一下，其中2个字节是长度信息，8个字节是指针：
size_t calc_pack_length(enum_field_types type, size_t length) {
  switch (type) {
    case MYSQL_TYPE_VAR_STRING:
    case MYSQL_TYPE_STRING:
    case MYSQL_TYPE_DECIMAL:
      return (length);
    case MYSQL_TYPE_VARCHAR:
      return (length + (length < 256 ? 1 : 2));
    case MYSQL_TYPE_BOOL:
    case MYSQL_TYPE_YEAR:
    case MYSQL_TYPE_TINY:
      return 1;
    case MYSQL_TYPE_SHORT:
      return 2;
    case MYSQL_TYPE_INT24:
    case MYSQL_TYPE_NEWDATE:
      return 3;
    case MYSQL_TYPE_TIME:
      return 3;
    case MYSQL_TYPE_TIME2:
      return length > MAX_TIME_WIDTH
                 ? my_time_binary_length(length - MAX_TIME_WIDTH - 1)
                 : 3;
    case MYSQL_TYPE_TIMESTAMP:
      return 4;
    case MYSQL_TYPE_TIMESTAMP2:
      return length > MAX_DATETIME_WIDTH
                 ? my_timestamp_binary_length(length - MAX_DATETIME_WIDTH - 1)
                 : 4;
    case MYSQL_TYPE_DATE:
    case MYSQL_TYPE_LONG:
      return 4;
    case MYSQL_TYPE_FLOAT:
      return sizeof(float);
    case MYSQL_TYPE_DOUBLE:
      return sizeof(double);
    case MYSQL_TYPE_DATETIME:
      return 8;
    case MYSQL_TYPE_DATETIME2:
      return length > MAX_DATETIME_WIDTH
                 ? my_datetime_binary_length(length - MAX_DATETIME_WIDTH - 1)
                 : 5;
    case MYSQL_TYPE_LONGLONG:
      return 8; /* Don't crash if no longlong */
    case MYSQL_TYPE_NULL:
      return 0;
    case MYSQL_TYPE_TINY_BLOB:
      return 1 + portable_sizeof_char_ptr;
    // 对于text/blob类型，是通过 2（字节） + 一个指针大小（8个字节）来计算的。2（字节）应该是用来保存text字段实际的长度，再加上一个指针指向具体的字符起始位置
    case MYSQL_TYPE_BLOB:
      return 2 + portable_sizeof_char_ptr;
    case MYSQL_TYPE_MEDIUM_BLOB:
      return 3 + portable_sizeof_char_ptr;
    case MYSQL_TYPE_LONG_BLOB:
      return 4 + portable_sizeof_char_ptr;
    case MYSQL_TYPE_GEOMETRY:
      return 4 + portable_sizeof_char_ptr;
    case MYSQL_TYPE_JSON:
      return 4 + portable_sizeof_char_ptr;
    case MYSQL_TYPE_SET:
    case MYSQL_TYPE_ENUM:
    case MYSQL_TYPE_NEWDECIMAL:
      assert(false);
      return 0;  // This shouldn't happen
    case MYSQL_TYPE_BIT:
      return length / 8;
    case MYSQL_TYPE_INVALID:
    case MYSQL_TYPE_TYPED_ARRAY:
      break;
  }
  assert(false);
  return 0;
}

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层面的极限，这篇文章有详细的计算过程。我这里总结一下重点：
  1. text字段最大存储就是40个字节（溢出的情况只存一个指针，小于40字节），再加1个字节的长度，总共41字节
  1. 除了我们显式创建的text字段外，每行记录还存在一些额外信息
    1. header信息，5字节
    2. null值bitmap，（ceil(x/8) 即向上取整）字节
    3. DB_ROW_ID（6字节）、DB_TRX_ID（6字节）和DB_ROLL_PTR（7字节）
那么计算公式就是：
5 + ceil(x/8) + 6 + 6 + 7 + x * 41 < 8126，求 x 的解。
最终我们可以计算出符合该公式的x的解为196。而x=197时，左边的结果刚好等于8126。此时如果我们给这张表加一个int类型（4字节）的主键，那么InnoDB就不会再加上6字节的ROW_ID，也就是可以减少2个字节（6 -> 4）的空间，从而支持197个text字段

varchar

这里我们还尝试了varchar字段个数的极限，varchar字段不同于text/blob，它是按照最大存储字节计算的。比如ascii编码的varchar(10)就是占用10个字节，再加上1个字节的长度，总共就是占用11个字节。
假设我们用ascii编码的varchar(19)，看看能建多少列，那么计算公式就是：
5 + ceil(x/8) + 6 + 6 + 7 + x * (19 + 1) < 8126，求 x 的解。
求得，x最大为402，并且此时还可以极限增加一列varchar(9)
mysql> CREATE TABLE `row_size_limit`  (a1 varchar(19), a2 varchar(19), a3 varchar(19), a4 varchar(19), a5 varchar(19), a6 varchar(19), a7 varchar(19), a8 varchar(19), a9 varchar(19), a10 varchar(19), a11 varchar(19), a12 varchar(19), a13 varchar(19), a14 varchar(19), a15 varchar(19), a16 varchar(19), a17 varchar(19), a18 varchar(19), a19 varchar(19), a20 varchar(19), a21 varchar(19), a22 varchar(19), a23 varchar(19), a24 varchar(19), a25 varchar(19), a26 varchar(19), a27 varchar(19), a28 varchar(19), a29 varchar(19), a30 varchar(19), a31 varchar(19), a32 varchar(19), a33 varchar(19), a34 varchar(19), a35 varchar(19), a36 varchar(19), a37 varchar(19), a38 varchar(19), a39 varchar(19), a40 varchar(19), a41 varchar(19), a42 varchar(19), a43 varchar(19), a44 varchar(19), a45 varchar(19), a46 varchar(19), a47 varchar(19), a48 varchar(19), a49 varchar(19), a50 varchar(19), a51 varchar(19), a52 varchar(19), a53 varchar(19), a54 varchar(19), a55 varchar(19), a56 varchar(19), a57 varchar(19), a58 varchar(19), a59 varchar(19), a60 varchar(19), a61 varchar(19), a62 varchar(19), a63 varchar(19), a64 varchar(19), a65 varchar(19), a66 varchar(19), a67 varchar(19), a68 varchar(19), a69 varchar(19), a70 varchar(19), a71 varchar(19), a72 varchar(19), a73 varchar(19), a74 varchar(19), a75 varchar(19), a76 varchar(19), a77 varchar(19), a78 varchar(19), a79 varchar(19), a80 varchar(19), a81 varchar(19), a82 varchar(19), a83 varchar(19), a84 varchar(19), a85 varchar(19), a86 varchar(19), a87 varchar(19), a88 varchar(19), a89 varchar(19), a90 varchar(19), a91 varchar(19), a92 varchar(19), a93 varchar(19), a94 varchar(19), a95 varchar(19), a96 varchar(19), a97 varchar(19), a98 varchar(19), a99 varchar(19), a100 varchar(19), a101 varchar(19), a102 varchar(19), a103 varchar(19), a104 varchar(19), a105 varchar(19), a106 varchar(19), a107 varchar(19), a108 varchar(19), a109 varchar(19), a110 varchar(19), a111 varchar(19), a112 varchar(19), a113 varchar(19), a114 varchar(19), a115 varchar(19), a116 varchar(19), a117 varchar(19), a118 varchar(19), a119 varchar(19), a120 varchar(19), a121 varchar(19), a122 varchar(19), a123 varchar(19), a124 varchar(19), a125 varchar(19), a126 varchar(19), a127 varchar(19), a128 varchar(19), a129 varchar(19), a130 varchar(19), a131 varchar(19), a132 varchar(19), a133 varchar(19), a134 varchar(19), a135 varchar(19), a136 varchar(19), a137 varchar(19), a138 varchar(19), a139 varchar(19), a140 varchar(19), a141 varchar(19), a142 varchar(19), a143 varchar(19), a144 varchar(19), a145 varchar(19), a146 varchar(19), a147 varchar(19), a148 varchar(19), a149 varchar(19), a150 varchar(19), a151 varchar(19), a152 varchar(19), a153 varchar(19), a154 varchar(19), a155 varchar(19), a156 varchar(19), a157 varchar(19), a158 varchar(19), a159 varchar(19), a160 varchar(19), a161 varchar(19), a162 varchar(19), a163 varchar(19), a164 varchar(19), a165 varchar(19), a166 varchar(19), a167 varchar(19), a168 varchar(19), a169 varchar(19), a170 varchar(19), a171 varchar(19), a172 varchar(19), a173 varchar(19), a174 varchar(19), a175 varchar(19), a176 varchar(19), a177 varchar(19), a178 varchar(19), a179 varchar(19), a180 varchar(19), a181 varchar(19), a182 varchar(19), a183 varchar(19), a184 varchar(19), a185 varchar(19), a186 varchar(19), a187 varchar(19), a188 varchar(19), a189 varchar(19), a190 varchar(19), a191 varchar(19), a192 varchar(19), a193 varchar(19), a194 varchar(19), a195 varchar(19), a196 varchar(19), a197 varchar(19), a198 varchar(19), a199 varchar(19), a200 varchar(19), a201 varchar(19), a202 varchar(19), a203 varchar(19), a204 varchar(19), a205 varchar(19), a206 varchar(19), a207 varchar(19), a208 varchar(19), a209 varchar(19), a210 varchar(19), a211 varchar(19), a212 varchar(19), a213 varchar(19), a214 varchar(19), a215 varchar(19), a216 varchar(19), a217 varchar(19), a218 varchar(19), a219 varchar(19), a220 varchar(19), a221 varchar(19), a222 varchar(19), a223 varchar(19), a224 varchar(19), a225 varchar(19), a226 varchar(19), a227 varchar(19), a228 varchar(19), a229 varchar(19), a230 varchar(19), a231 varchar(19), a232 varchar(19), a233 varchar(19), a234 varchar(19), a235 varchar(19), a236 varchar(19), a237 varchar(19), a238 varchar(19), a239 varchar(19), a240 varchar(19), a241 varchar(19), a242 varchar(19), a243 varchar(19), a244 varchar(19), a245 varchar(19), a246 varchar(19), a247 varchar(19), a248 varchar(19), a249 varchar(19), a250 varchar(19), a251 varchar(19), a252 varchar(19), a253 varchar(19), a254 varchar(19), a255 varchar(19), a256 varchar(19), a257 varchar(19), a258 varchar(19), a259 varchar(19), a260 varchar(19), a261 varchar(19), a262 varchar(19), a263 varchar(19), a264 varchar(19), a265 varchar(19), a266 varchar(19), a267 varchar(19), a268 varchar(19), a269 varchar(19), a270 varchar(19), a271 varchar(19), a272 varchar(19), a273 varchar(19), a274 varchar(19), a275 varchar(19), a276 varchar(19), a277 varchar(19), a278 varchar(19), a279 varchar(19), a280 varchar(19), a281 varchar(19), a282 varchar(19), a283 varchar(19), a284 varchar(19), a285 varchar(19), a286 varchar(19), a287 varchar(19), a288 varchar(19), a289 varchar(19), a290 varchar(19), a291 varchar(19), a292 varchar(19), a293 varchar(19), a294 varchar(19), a295 varchar(19), a296 varchar(19), a297 varchar(19), a298 varchar(19), a299 varchar(19), a300 varchar(19), a301 varchar(19), a302 varchar(19), a303 varchar(19), a304 varchar(19), a305 varchar(19), a306 varchar(19), a307 varchar(19), a308 varchar(19), a309 varchar(19), a310 varchar(19), a311 varchar(19), a312 varchar(19), a313 varchar(19), a314 varchar(19), a315 varchar(19), a316 varchar(19), a317 varchar(19), a318 varchar(19), a319 varchar(19), a320 varchar(19), a321 varchar(19), a322 varchar(19), a323 varchar(19), a324 varchar(19), a325 varchar(19), a326 varchar(19), a327 varchar(19), a328 varchar(19), a329 varchar(19), a330 varchar(19), a331 varchar(19), a332 varchar(19), a333 varchar(19), a334 varchar(19), a335 varchar(19), a336 varchar(19), a337 varchar(19), a338 varchar(19), a339 varchar(19), a340 varchar(19), a341 varchar(19), a342 varchar(19), a343 varchar(19), a344 varchar(19), a345 varchar(19), a346 varchar(19), a347 varchar(19), a348 varchar(19), a349 varchar(19), a350 varchar(19), a351 varchar(19), a352 varchar(19), a353 varchar(19), a354 varchar(19), a355 varchar(19), a356 varchar(19), a357 varchar(19), a358 varchar(19), a359 varchar(19), a360 varchar(19), a361 varchar(19), a362 varchar(19), a363 varchar(19), a364 varchar(19), a365 varchar(19), a366 varchar(19), a367 varchar(19), a368 varchar(19), a369 varchar(19), a370 varchar(19), a371 varchar(19), a372 varchar(19), a373 varchar(19), a374 varchar(19), a375 varchar(19), a376 varchar(19), a377 varchar(19), a378 varchar(19), a379 varchar(19), a380 varchar(19), a381 varchar(19), a382 varchar(19), a383 varchar(19), a384 varchar(19), a385 varchar(19), a386 varchar(19), a387 varchar(19), a388 varchar(19), a389 varchar(19), a390 varchar(19), a391 varchar(19), a392 varchar(19), a393 varchar(19), a394 varchar(19), a395 varchar(19), a396 varchar(19), a397 varchar(19), a398 varchar(19), a399 varchar(19), a400 varchar(19), a401 varchar(19), a402 varchar(19), a403 varchar(10) ) ENGINE = INNODB ROW_FORMAT = DYNAMIC DEFAULT CHARSET ascii;
1118 - Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

mysql> CREATE TABLE `row_size_limit`  (a1 varchar(19), a2 varchar(19), a3 varchar(19), a4 varchar(19), a5 varchar(19), a6 varchar(19), a7 varchar(19), a8 varchar(19), a9 varchar(19), a10 varchar(19), a11 varchar(19), a12 varchar(19), a13 varchar(19), a14 varchar(19), a15 varchar(19), a16 varchar(19), a17 varchar(19), a18 varchar(19), a19 varchar(19), a20 varchar(19), a21 varchar(19), a22 varchar(19), a23 varchar(19), a24 varchar(19), a25 varchar(19), a26 varchar(19), a27 varchar(19), a28 varchar(19), a29 varchar(19), a30 varchar(19), a31 varchar(19), a32 varchar(19), a33 varchar(19), a34 varchar(19), a35 varchar(19), a36 varchar(19), a37 varchar(19), a38 varchar(19), a39 varchar(19), a40 varchar(19), a41 varchar(19), a42 varchar(19), a43 varchar(19), a44 varchar(19), a45 varchar(19), a46 varchar(19), a47 varchar(19), a48 varchar(19), a49 varchar(19), a50 varchar(19), a51 varchar(19), a52 varchar(19), a53 varchar(19), a54 varchar(19), a55 varchar(19), a56 varchar(19), a57 varchar(19), a58 varchar(19), a59 varchar(19), a60 varchar(19), a61 varchar(19), a62 varchar(19), a63 varchar(19), a64 varchar(19), a65 varchar(19), a66 varchar(19), a67 varchar(19), a68 varchar(19), a69 varchar(19), a70 varchar(19), a71 varchar(19), a72 varchar(19), a73 varchar(19), a74 varchar(19), a75 varchar(19), a76 varchar(19), a77 varchar(19), a78 varchar(19), a79 varchar(19), a80 varchar(19), a81 varchar(19), a82 varchar(19), a83 varchar(19), a84 varchar(19), a85 varchar(19), a86 varchar(19), a87 varchar(19), a88 varchar(19), a89 varchar(19), a90 varchar(19), a91 varchar(19), a92 varchar(19), a93 varchar(19), a94 varchar(19), a95 varchar(19), a96 varchar(19), a97 varchar(19), a98 varchar(19), a99 varchar(19), a100 varchar(19), a101 varchar(19), a102 varchar(19), a103 varchar(19), a104 varchar(19), a105 varchar(19), a106 varchar(19), a107 varchar(19), a108 varchar(19), a109 varchar(19), a110 varchar(19), a111 varchar(19), a112 varchar(19), a113 varchar(19), a114 varchar(19), a115 varchar(19), a116 varchar(19), a117 varchar(19), a118 varchar(19), a119 varchar(19), a120 varchar(19), a121 varchar(19), a122 varchar(19), a123 varchar(19), a124 varchar(19), a125 varchar(19), a126 varchar(19), a127 varchar(19), a128 varchar(19), a129 varchar(19), a130 varchar(19), a131 varchar(19), a132 varchar(19), a133 varchar(19), a134 varchar(19), a135 varchar(19), a136 varchar(19), a137 varchar(19), a138 varchar(19), a139 varchar(19), a140 varchar(19), a141 varchar(19), a142 varchar(19), a143 varchar(19), a144 varchar(19), a145 varchar(19), a146 varchar(19), a147 varchar(19), a148 varchar(19), a149 varchar(19), a150 varchar(19), a151 varchar(19), a152 varchar(19), a153 varchar(19), a154 varchar(19), a155 varchar(19), a156 varchar(19), a157 varchar(19), a158 varchar(19), a159 varchar(19), a160 varchar(19), a161 varchar(19), a162 varchar(19), a163 varchar(19), a164 varchar(19), a165 varchar(19), a166 varchar(19), a167 varchar(19), a168 varchar(19), a169 varchar(19), a170 varchar(19), a171 varchar(19), a172 varchar(19), a173 varchar(19), a174 varchar(19), a175 varchar(19), a176 varchar(19), a177 varchar(19), a178 varchar(19), a179 varchar(19), a180 varchar(19), a181 varchar(19), a182 varchar(19), a183 varchar(19), a184 varchar(19), a185 varchar(19), a186 varchar(19), a187 varchar(19), a188 varchar(19), a189 varchar(19), a190 varchar(19), a191 varchar(19), a192 varchar(19), a193 varchar(19), a194 varchar(19), a195 varchar(19), a196 varchar(19), a197 varchar(19), a198 varchar(19), a199 varchar(19), a200 varchar(19), a201 varchar(19), a202 varchar(19), a203 varchar(19), a204 varchar(19), a205 varchar(19), a206 varchar(19), a207 varchar(19), a208 varchar(19), a209 varchar(19), a210 varchar(19), a211 varchar(19), a212 varchar(19), a213 varchar(19), a214 varchar(19), a215 varchar(19), a216 varchar(19), a217 varchar(19), a218 varchar(19), a219 varchar(19), a220 varchar(19), a221 varchar(19), a222 varchar(19), a223 varchar(19), a224 varchar(19), a225 varchar(19), a226 varchar(19), a227 varchar(19), a228 varchar(19), a229 varchar(19), a230 varchar(19), a231 varchar(19), a232 varchar(19), a233 varchar(19), a234 varchar(19), a235 varchar(19), a236 varchar(19), a237 varchar(19), a238 varchar(19), a239 varchar(19), a240 varchar(19), a241 varchar(19), a242 varchar(19), a243 varchar(19), a244 varchar(19), a245 varchar(19), a246 varchar(19), a247 varchar(19), a248 varchar(19), a249 varchar(19), a250 varchar(19), a251 varchar(19), a252 varchar(19), a253 varchar(19), a254 varchar(19), a255 varchar(19), a256 varchar(19), a257 varchar(19), a258 varchar(19), a259 varchar(19), a260 varchar(19), a261 varchar(19), a262 varchar(19), a263 varchar(19), a264 varchar(19), a265 varchar(19), a266 varchar(19), a267 varchar(19), a268 varchar(19), a269 varchar(19), a270 varchar(19), a271 varchar(19), a272 varchar(19), a273 varchar(19), a274 varchar(19), a275 varchar(19), a276 varchar(19), a277 varchar(19), a278 varchar(19), a279 varchar(19), a280 varchar(19), a281 varchar(19), a282 varchar(19), a283 varchar(19), a284 varchar(19), a285 varchar(19), a286 varchar(19), a287 varchar(19), a288 varchar(19), a289 varchar(19), a290 varchar(19), a291 varchar(19), a292 varchar(19), a293 varchar(19), a294 varchar(19), a295 varchar(19), a296 varchar(19), a297 varchar(19), a298 varchar(19), a299 varchar(19), a300 varchar(19), a301 varchar(19), a302 varchar(19), a303 varchar(19), a304 varchar(19), a305 varchar(19), a306 varchar(19), a307 varchar(19), a308 varchar(19), a309 varchar(19), a310 varchar(19), a311 varchar(19), a312 varchar(19), a313 varchar(19), a314 varchar(19), a315 varchar(19), a316 varchar(19), a317 varchar(19), a318 varchar(19), a319 varchar(19), a320 varchar(19), a321 varchar(19), a322 varchar(19), a323 varchar(19), a324 varchar(19), a325 varchar(19), a326 varchar(19), a327 varchar(19), a328 varchar(19), a329 varchar(19), a330 varchar(19), a331 varchar(19), a332 varchar(19), a333 varchar(19), a334 varchar(19), a335 varchar(19), a336 varchar(19), a337 varchar(19), a338 varchar(19), a339 varchar(19), a340 varchar(19), a341 varchar(19), a342 varchar(19), a343 varchar(19), a344 varchar(19), a345 varchar(19), a346 varchar(19), a347 varchar(19), a348 varchar(19), a349 varchar(19), a350 varchar(19), a351 varchar(19), a352 varchar(19), a353 varchar(19), a354 varchar(19), a355 varchar(19), a356 varchar(19), a357 varchar(19), a358 varchar(19), a359 varchar(19), a360 varchar(19), a361 varchar(19), a362 varchar(19), a363 varchar(19), a364 varchar(19), a365 varchar(19), a366 varchar(19), a367 varchar(19), a368 varchar(19), a369 varchar(19), a370 varchar(19), a371 varchar(19), a372 varchar(19), a373 varchar(19), a374 varchar(19), a375 varchar(19), a376 varchar(19), a377 varchar(19), a378 varchar(19), a379 varchar(19), a380 varchar(19), a381 varchar(19), a382 varchar(19), a383 varchar(19), a384 varchar(19), a385 varchar(19), a386 varchar(19), a387 varchar(19), a388 varchar(19), a389 varchar(19), a390 varchar(19), a391 varchar(19), a392 varchar(19), a393 varchar(19), a394 varchar(19), a395 varchar(19), a396 varchar(19), a397 varchar(19), a398 varchar(19), a399 varchar(19), a400 varchar(19), a401 varchar(19), a402 varchar(19), a403 varchar(9) ) ENGINE = INNODB ROW_FORMAT = DYNAMIC DEFAULT CHARSET ascii;
Query OK, 0 rows affected (0.06 sec)

定长字段超限

官方有个例子：https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html
mysql> CREATE TABLE t4 (
       c1 CHAR(255),c2 CHAR(255),c3 CHAR(255),
       c4 CHAR(255),c5 CHAR(255),c6 CHAR(255),
       c7 CHAR(255),c8 CHAR(255),c9 CHAR(255),
       c10 CHAR(255),c11 CHAR(255),c12 CHAR(255),
       c13 CHAR(255),c14 CHAR(255),c15 CHAR(255),
       c16 CHAR(255),c17 CHAR(255),c18 CHAR(255),
       c19 CHAR(255),c20 CHAR(255),c21 CHAR(255),
       c22 CHAR(255),c23 CHAR(255),c24 CHAR(255),
       c25 CHAR(255),c26 CHAR(255),c27 CHAR(255),
       c28 CHAR(255),c29 CHAR(255),c30 CHAR(255),
       c31 CHAR(255),c32 CHAR(255),c33 CHAR(255)
       ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET latin1;
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help.
In current row format, BLOB prefix of 0 bytes is stored inline.
33 * 255 + 5 = 8420 > 8126 肯定不行。而32 * 255 + 5 = 8165 > 8126还是不行。不过31个char(255)就可以了，那我们再算算第32个字段最大支持多少个字符。
5 + ceil(x/8) + 6 + 6 + 7 + 31 * 255 + x = 5 + 4 + 6 + 6 + 7 + 31 * 255 + x < 8126
最终算出符合公式的x最大为192。
mysql> CREATE TABLE t4 (c1 CHAR (255),c2 CHAR (255),c3 CHAR (255),c4 CHAR (255),c5 CHAR (255),c6 CHAR (255),c7 CHAR (255),c8 CHAR (255),c9 CHAR (255),c10 CHAR (255),c11 CHAR (255),c12 CHAR (255),c13 CHAR (255),c14 CHAR (255),c15 CHAR (255),c16 CHAR (255),c17 CHAR (255),c18 CHAR (255),c19 CHAR (255),c20 CHAR (255),c21 CHAR (255),c22 CHAR (255),c23 CHAR (255),c24 CHAR (255),c25 CHAR (255),c26 CHAR (255),c27 CHAR (255),c28 CHAR (255),c29 CHAR (255),c30 CHAR (255),c31 CHAR (255),c32 CHAR (193)) ENGINE=INNODB ROW_FORMAT=DYNAMIC DEFAULT CHARSET latin1;
1118 - Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

mysql> CREATE TABLE t4 (c1 CHAR (255),c2 CHAR (255),c3 CHAR (255),c4 CHAR (255),c5 CHAR (255),c6 CHAR (255),c7 CHAR (255),c8 CHAR (255),c9 CHAR (255),c10 CHAR (255),c11 CHAR (255),c12 CHAR (255),c13 CHAR (255),c14 CHAR (255),c15 CHAR (255),c16 CHAR (255),c17 CHAR (255),c18 CHAR (255),c19 CHAR (255),c20 CHAR (255),c21 CHAR (255),c22 CHAR (255),c23 CHAR (255),c24 CHAR (255),c25 CHAR (255),c26 CHAR (255),c27 CHAR (255),c28 CHAR (255),c29 CHAR (255),c30 CHAR (255),c31 CHAR (255),c32 CHAR (192)) ENGINE=INNODB ROW_FORMAT=DYNAMIC DEFAULT CHARSET latin1;
Query OK, 0 rows affected (0.04 sec)
定长字段，比如char、int、bigint等，由于不涉及到溢出的场景，直接按照字面大小计算即可。不过马上就会介绍一种特殊情况

char变长字符集

我们再来看看不定长编码的char，用utf8mb4编码试试，反而能支持到64个字段
mysql> CREATE TABLE `row_size_limit` (a1 CHAR (255),a2 CHAR (255),a3 CHAR (255),a4 CHAR (255),a5 CHAR (255),a6 CHAR (255),a7 CHAR (255),a8 CHAR (255),a9 CHAR (255),a10 CHAR (255),a11 CHAR (255),a12 CHAR (255),a13 CHAR (255),a14 CHAR (255),a15 CHAR (255),a16 CHAR (255),a17 CHAR (255),a18 CHAR (255),a19 CHAR (255),a20 CHAR (255),a21 CHAR (255),a22 CHAR (255),a23 CHAR (255),a24 CHAR (255),a25 CHAR (255),a26 CHAR (255),a27 CHAR (255),a28 CHAR (255),a29 CHAR (255),a30 CHAR (255),a31 CHAR (255),a32 CHAR (255),a33 CHAR (255),a34 CHAR (255),a35 CHAR (255),a36 CHAR (255),a37 CHAR (255),a38 CHAR (255),a39 CHAR (255),a40 CHAR (255),a41 CHAR (255),a42 CHAR (255),a43 CHAR (255),a44 CHAR (255),a45 CHAR (255),a46 CHAR (255),a47 CHAR (255),a48 CHAR (255),a49 CHAR (255),a50 CHAR (255),a51 CHAR (255),a52 CHAR (255),a53 CHAR (255),a54 CHAR (255),a55 CHAR (255),a56 CHAR (255),a57 CHAR (255),a58 CHAR (255),a59 CHAR (255),a60 CHAR (255),a61 CHAR (255),a62 CHAR (255),a63 CHAR (255),a64 CHAR (255)) ENGINE=INNODB ROW_FORMAT=DYNAMIC DEFAULT CHARSET utf8mb4;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE `row_size_limit` (a1 CHAR (255),a2 CHAR (255),a3 CHAR (255),a4 CHAR (255),a5 CHAR (255),a6 CHAR (255),a7 CHAR (255),a8 CHAR (255),a9 CHAR (255),a10 CHAR (255),a11 CHAR (255),a12 CHAR (255),a13 CHAR (255),a14 CHAR (255),a15 CHAR (255),a16 CHAR (255),a17 CHAR (255),a18 CHAR (255),a19 CHAR (255),a20 CHAR (255),a21 CHAR (255),a22 CHAR (255),a23 CHAR (255),a24 CHAR (255),a25 CHAR (255),a26 CHAR (255),a27 CHAR (255),a28 CHAR (255),a29 CHAR (255),a30 CHAR (255),a31 CHAR (255),a32 CHAR (255),a33 CHAR (255),a34 CHAR (255),a35 CHAR (255),a36 CHAR (255),a37 CHAR (255),a38 CHAR (255),a39 CHAR (255),a40 CHAR (255),a41 CHAR (255),a42 CHAR (255),a43 CHAR (255),a44 CHAR (255),a45 CHAR (255),a46 CHAR (255),a47 CHAR (255),a48 CHAR (255),a49 CHAR (255),a50 CHAR (255),a51 CHAR (255),a52 CHAR (255),a53 CHAR (255),a54 CHAR (255),a55 CHAR (255),a56 CHAR (255),a57 CHAR (255),a58 CHAR (255),a59 CHAR (255),a60 CHAR (255),a61 CHAR (255),a62 CHAR (255),a63 CHAR (255),a64 CHAR (255),a65 CHAR (255)) ENGINE=INNODB ROW_FORMAT=DYNAMIC DEFAULT CHARSET utf8mb4;
1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
并且再往上增长，也只是受到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字节才会变长还是只要是变长字符集就是变长？这个我们再通过一个实验印证一下。
mysql> CREATE TABLE `row_size_limit` (a1 CHAR (128),a2 CHAR (128),a3 CHAR (128),a4 CHAR (128),a5 CHAR (128),a6 CHAR (128),a7 CHAR (128),a8 CHAR (128),a9 CHAR (128),a10 CHAR (128),a11 CHAR (128),a12 CHAR (128),a13 CHAR (128),a14 CHAR (128),a15 CHAR (128),a16 CHAR (128),a17 CHAR (128),a18 CHAR (128),a19 CHAR (128),a20 CHAR (128),a21 CHAR (128),a22 CHAR (128),a23 CHAR (128),a24 CHAR (128),a25 CHAR (128),a26 CHAR (128),a27 CHAR (128),a28 CHAR (128),a29 CHAR (128),a30 CHAR (128),a31 CHAR (128),a32 CHAR (128),a33 CHAR (128),a34 CHAR (128),a35 CHAR (128),a36 CHAR (128),a37 CHAR (128),a38 CHAR (128),a39 CHAR (128),a40 CHAR (128),a41 CHAR (128),a42 CHAR (128),a43 CHAR (128),a44 CHAR (128),a45 CHAR (128),a46 CHAR (128),a47 CHAR (128),a48 CHAR (128),a49 CHAR (128),a50 CHAR (128),a51 CHAR (128),a52 CHAR (128),a53 CHAR (128),a54 CHAR (128),a55 CHAR (128),a56 CHAR (128),a57 CHAR (128),a58 CHAR (128),a59 CHAR (128),a60 CHAR (128),a61 CHAR (128),a62 CHAR (128),a63 CHAR (128),a64 CHAR (128),a65 CHAR (128),a66 CHAR (128),a67 CHAR (128),a68 CHAR (128),a69 CHAR (128),a70 CHAR (128),a71 CHAR (128),a72 CHAR (128),a73 CHAR (128),a74 CHAR (128),a75 CHAR (128),a76 CHAR (128),a77 CHAR (128),a78 CHAR (128),a79 CHAR (128),a80 CHAR (128),a81 CHAR (128),a82 CHAR (128),a83 CHAR (128),a84 CHAR (128),a85 CHAR (128),a86 CHAR (128),a87 CHAR (128),a88 CHAR (128),a89 CHAR (128),a90 CHAR (128),a91 CHAR (128),a92 CHAR (128),a93 CHAR (128),a94 CHAR (128),a95 CHAR (128),a96 CHAR (128),a97 CHAR (128),a98 CHAR (128),a99 CHAR (128),a100 CHAR (128),a101 CHAR (128),a102 CHAR (128),a103 CHAR (128),a104 CHAR (128),a105 CHAR (128),a106 CHAR (128),a107 CHAR (128),a108 CHAR (128),a109 CHAR (128),a110 CHAR (128),a111 CHAR (128),a112 CHAR (128),a113 CHAR (128),a114 CHAR (128),a115 CHAR (128),a116 CHAR (128),a117 CHAR (128),a118 CHAR (128),a119 CHAR (128),a120 CHAR (128),a121 CHAR (128),a122 CHAR (128),a123 CHAR (128),a124 CHAR (128),a125 CHAR (128),a126 CHAR (128),a127 CHAR (128)) ENGINE=INNODB ROW_FORMAT=DYNAMIC DEFAULT CHARSET utf8mb4;
Query OK, 0 rows affected (0.05 sec)
也成功了，达到了127个字段，看起来并不是只有char(255)才会变长存储。char(128)也变长存储了
我们再来验证一下，这里需要注意，server层的计算不用考虑存储引擎层的逻辑，比如对于char来说，在server层并不会考虑其最终使用的是变长编码，在计算row size的时候不会把变长列表的长度考虑进去，所以计算公式为：
127 * 128 * 4 + ceil(127 / 8) = 65024 + 16 = 65040
那么还剩余的行空间为65535 - 65040 = 495，剩余字段我们用ascii来编码，这样会更精准，那么至少还需要2个char字段，并且此时null值列表需要再增加1个字节（17个字节）保存了。其中一个字段为char(255)，则另一个字段为char(239)，计算逻辑为：495 - 1 - 255 = 239
mysql> CREATE TABLE `row_size_limit` (a1 CHAR (128),a2 CHAR (128),a3 CHAR (128),a4 CHAR (128),a5 CHAR (128),a6 CHAR (128),a7 CHAR (128),a8 CHAR (128),a9 CHAR (128),a10 CHAR (128),a11 CHAR (128),a12 CHAR (128),a13 CHAR (128),a14 CHAR (128),a15 CHAR (128),a16 CHAR (128),a17 CHAR (128),a18 CHAR (128),a19 CHAR (128),a20 CHAR (128),a21 CHAR (128),a22 CHAR (128),a23 CHAR (128),a24 CHAR (128),a25 CHAR (128),a26 CHAR (128),a27 CHAR (128),a28 CHAR (128),a29 CHAR (128),a30 CHAR (128),a31 CHAR (128),a32 CHAR (128),a33 CHAR (128),a34 CHAR (128),a35 CHAR (128),a36 CHAR (128),a37 CHAR (128),a38 CHAR (128),a39 CHAR (128),a40 CHAR (128),a41 CHAR (128),a42 CHAR (128),a43 CHAR (128),a44 CHAR (128),a45 CHAR (128),a46 CHAR (128),a47 CHAR (128),a48 CHAR (128),a49 CHAR (128),a50 CHAR (128),a51 CHAR (128),a52 CHAR (128),a53 CHAR (128),a54 CHAR (128),a55 CHAR (128),a56 CHAR (128),a57 CHAR (128),a58 CHAR (128),a59 CHAR (128),a60 CHAR (128),a61 CHAR (128),a62 CHAR (128),a63 CHAR (128),a64 CHAR (128),a65 CHAR (128),a66 CHAR (128),a67 CHAR (128),a68 CHAR (128),a69 CHAR (128),a70 CHAR (128),a71 CHAR (128),a72 CHAR (128),a73 CHAR (128),a74 CHAR (128),a75 CHAR (128),a76 CHAR (128),a77 CHAR (128),a78 CHAR (128),a79 CHAR (128),a80 CHAR (128),a81 CHAR (128),a82 CHAR (128),a83 CHAR (128),a84 CHAR (128),a85 CHAR (128),a86 CHAR (128),a87 CHAR (128),a88 CHAR (128),a89 CHAR (128),a90 CHAR (128),a91 CHAR (128),a92 CHAR (128),a93 CHAR (128),a94 CHAR (128),a95 CHAR (128),a96 CHAR (128),a97 CHAR (128),a98 CHAR (128),a99 CHAR (128),a100 CHAR (128),a101 CHAR (128),a102 CHAR (128),a103 CHAR (128),a104 CHAR (128),a105 CHAR (128),a106 CHAR (128),a107 CHAR (128),a108 CHAR (128),a109 CHAR (128),a110 CHAR (128),a111 CHAR (128),a112 CHAR (128),a113 CHAR (128),a114 CHAR (128),a115 CHAR (128),a116 CHAR (128),a117 CHAR (128),a118 CHAR (128),a119 CHAR (128),a120 CHAR (128),a121 CHAR (128),a122 CHAR (128),a123 CHAR (128),a124 CHAR (128),a125 CHAR (128),a126 CHAR (128),a127 CHAR (128),`a128` CHAR (255) CHARACTER SET ascii COLLATE ascii_bin, `a129` CHAR (240) CHARACTER SET ascii COLLATE ascii_bin) ENGINE=INNODB ROW_FORMAT=DYNAMIC DEFAULT CHARSET utf8mb4;
1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

mysql> CREATE TABLE `row_size_limit` (a1 CHAR (128),a2 CHAR (128),a3 CHAR (128),a4 CHAR (128),a5 CHAR (128),a6 CHAR (128),a7 CHAR (128),a8 CHAR (128),a9 CHAR (128),a10 CHAR (128),a11 CHAR (128),a12 CHAR (128),a13 CHAR (128),a14 CHAR (128),a15 CHAR (128),a16 CHAR (128),a17 CHAR (128),a18 CHAR (128),a19 CHAR (128),a20 CHAR (128),a21 CHAR (128),a22 CHAR (128),a23 CHAR (128),a24 CHAR (128),a25 CHAR (128),a26 CHAR (128),a27 CHAR (128),a28 CHAR (128),a29 CHAR (128),a30 CHAR (128),a31 CHAR (128),a32 CHAR (128),a33 CHAR (128),a34 CHAR (128),a35 CHAR (128),a36 CHAR (128),a37 CHAR (128),a38 CHAR (128),a39 CHAR (128),a40 CHAR (128),a41 CHAR (128),a42 CHAR (128),a43 CHAR (128),a44 CHAR (128),a45 CHAR (128),a46 CHAR (128),a47 CHAR (128),a48 CHAR (128),a49 CHAR (128),a50 CHAR (128),a51 CHAR (128),a52 CHAR (128),a53 CHAR (128),a54 CHAR (128),a55 CHAR (128),a56 CHAR (128),a57 CHAR (128),a58 CHAR (128),a59 CHAR (128),a60 CHAR (128),a61 CHAR (128),a62 CHAR (128),a63 CHAR (128),a64 CHAR (128),a65 CHAR (128),a66 CHAR (128),a67 CHAR (128),a68 CHAR (128),a69 CHAR (128),a70 CHAR (128),a71 CHAR (128),a72 CHAR (128),a73 CHAR (128),a74 CHAR (128),a75 CHAR (128),a76 CHAR (128),a77 CHAR (128),a78 CHAR (128),a79 CHAR (128),a80 CHAR (128),a81 CHAR (128),a82 CHAR (128),a83 CHAR (128),a84 CHAR (128),a85 CHAR (128),a86 CHAR (128),a87 CHAR (128),a88 CHAR (128),a89 CHAR (128),a90 CHAR (128),a91 CHAR (128),a92 CHAR (128),a93 CHAR (128),a94 CHAR (128),a95 CHAR (128),a96 CHAR (128),a97 CHAR (128),a98 CHAR (128),a99 CHAR (128),a100 CHAR (128),a101 CHAR (128),a102 CHAR (128),a103 CHAR (128),a104 CHAR (128),a105 CHAR (128),a106 CHAR (128),a107 CHAR (128),a108 CHAR (128),a109 CHAR (128),a110 CHAR (128),a111 CHAR (128),a112 CHAR (128),a113 CHAR (128),a114 CHAR (128),a115 CHAR (128),a116 CHAR (128),a117 CHAR (128),a118 CHAR (128),a119 CHAR (128),a120 CHAR (128),a121 CHAR (128),a122 CHAR (128),a123 CHAR (128),a124 CHAR (128),a125 CHAR (128),a126 CHAR (128),a127 CHAR (128),`a128` CHAR (255) CHARACTER SET ascii COLLATE ascii_bin, `a129` CHAR (239) CHARACTER SET ascii COLLATE ascii_bin) ENGINE=INNODB ROW_FORMAT=DYNAMIC DEFAULT CHARSET utf8mb4;
Query OK, 0 rows affected (0.05 sec)
这篇文章通过ibd文件来验证char在不定长字符编码下是被当做变长字段保存的。

行溢出

前面提到过变长字段会溢出存储，溢出存储的临界点是InnoDB要保证一页至少要存放2条行数据。溢出的条件是行大小，但是溢出存储的却是变长列。下面我们通过实验来加深一下感受：
我们新建一张测试列溢出的表
mysql> CREATE TABLE `off_page` (
  `id` int NOT NULL,
  `name` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.04 sec)
根据off_page表的结构我们可以算出行溢出的临界点是
8126 - 5（header） - 4（id） - 6（ROW_TRX_ID） - 7（ROW_ROLL_PTR） - 2（text字段长度位） = 8102
name字段长度大于等于8102字节则会溢出存储。我们通过information_schema.TABLES表来验证看看
mysql> insert off_page(id, name) values(1, repeat('a',8101));
Query OK, 1 row affected (0.03 sec)
 
mysql> ANALYZE TABLE off_page;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| test.off_page | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.03 sec)
 
mysql> select table_name, format_bytes(data_length) DATA_SIZE from information_schema.TABLES where table_name = 'off_page';
+------------+-----------+
| TABLE_NAME | DATA_SIZE |
+------------+-----------+
| off_page   | 16.00 KiB |
+------------+-----------+
1 row in set (0.01 sec)
 
mysql> update off_page set name = repeat('a',8102) where id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> ANALYZE TABLE off_page;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| test.off_page | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.03 sec)
 
mysql> select table_name, format_bytes(data_length) DATA_SIZE from information_schema.TABLES where table_name = 'off_page';
+------------+-----------+
| TABLE_NAME | DATA_SIZE |
+------------+-----------+
| off_page   | 32.00 KiB |
+------------+-----------+
1 row in set (0.01 sec)
可以看到，此时应该是增加了一个溢出页，那我们来看看这个溢出页可以保存的数据是多少。二分法试得结果为15680，在a字段的大小为15681时，此时又会增加一个溢出页。多个溢出页之间用单向链表连接（这是MySQL 8.0版本之前的结构，8.0之后就变了，下面会说）。
mysql> update off_page set name = repeat('a',15681) where id = 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> ANALYZE TABLE off_page;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| test.off_page | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.04 sec)
 
mysql> select table_name, format_bytes(data_length) DATA_SIZE from information_schema.TABLES where table_name = 'off_page';
+------------+-----------+
| TABLE_NAME | DATA_SIZE |
+------------+-----------+
| off_page   | 48.00 KiB |
+------------+-----------+
1 row in set (0.02 sec)
 
mysql> update off_page set name = repeat('a',15680) where id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> ANALYZE TABLE off_page;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| test.off_page | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.04 sec)
 
mysql> select table_name, format_bytes(data_length) DATA_SIZE from information_schema.TABLES where table_name = 'off_page';
+------------+-----------+
| TABLE_NAME | DATA_SIZE |
+------------+-----------+
| off_page   | 32.00 KiB |
+------------+-----------+
1 row in set (0.01 sec)
那第二个溢出页可以保存的大小也是15680吗？答案是否定的，我们又试出第二个溢出页的容量为16327
mysql> update off_page set name = repeat('a',15680 + 16328) where id = 1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> ANALYZE TABLE off_page;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| test.off_page | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.03 sec)
 
mysql> select table_name, format_bytes(data_length) DATA_SIZE from information_schema.TABLES where table_name = 'off_page';
+------------+-----------+
| TABLE_NAME | DATA_SIZE |
+------------+-----------+
| off_page   | 64.00 KiB |
+------------+-----------+
1 row in set (0.00 sec)

mysql> update off_page set name = repeat('a',15680 + 16327) where id = 1;

Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> ANALYZE TABLE off_page;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| test.off_page | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.03 sec)
 
mysql> select table_name, format_bytes(data_length) DATA_SIZE from information_schema.TABLES where table_name = 'off_page';
+------------+-----------+
| TABLE_NAME | DATA_SIZE |
+------------+-----------+
| off_page   | 48.00 KiB |
+------------+-----------+
1 row in set (0.01 sec)
除了第一个溢出页的容量不一样之外，后面的溢出页的容量都是16327
mysql> update off_page set name = repeat('a',15680 + 16327*2) where id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> ANALYZE TABLE off_page;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| test.off_page | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.03 sec)
 
mysql> select table_name, format_bytes(data_length) DATA_SIZE from information_schema.TABLES where table_name = 'off_page';
+------------+-----------+
| TABLE_NAME | DATA_SIZE |
+------------+-----------+
| off_page   | 64.00 KiB |
+------------+-----------+
1 row in set (0.01 sec)
 
mysql> update off_page set name = repeat('a',15680 + 16327*2 + 1) where id = 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> ANALYZE TABLE off_page;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| test.off_page | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.03 sec)
 
mysql> select table_name, format_bytes(data_length) DATA_SIZE from information_schema.TABLES where table_name = 'off_page';
+------------+-----------+
| TABLE_NAME | DATA_SIZE |
+------------+-----------+
| off_page   | 80.00 KiB |
+------------+-----------+
1 row in set (0.02 sec)
 
mysql> update off_page set name = repeat('a',15680 + 16327*3) where id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> ANALYZE TABLE off_page;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| test.off_page | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.03 sec)
 
mysql> select table_name, format_bytes(data_length) DATA_SIZE from information_schema.TABLES where table_name = 'off_page';
+------------+-----------+
| TABLE_NAME | DATA_SIZE |
+------------+-----------+
| off_page   | 80.00 KiB |
+------------+-----------+
1 row in set (0.01 sec)
 
mysql> update off_page set name = repeat('a',15680 + 16327*3 + 1) where id = 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> ANALYZE TABLE off_page;
+---------------+---------+----------+----------+
| Table         | Op      | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| test.off_page | analyze | status   | OK       |
+---------------+---------+----------+----------+
1 row in set (0.03 sec)
 
mysql> select table_name, format_bytes(data_length) DATA_SIZE from information_schema.TABLES where table_name = 'off_page';
+------------+-----------+
| TABLE_NAME | DATA_SIZE |
+------------+-----------+
| off_page   | 96.00 KiB |
+------------+-----------+
1 row in set (0.01 sec)
为什么溢出页的第一页和其他页的容量不一样呢？原来是因为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层面对行记录大小的限制，过程中也验证了一些其他的结论：
  1. char字段在变长编码下的表现和变长字段一样，比如
    1. 也需要额外的空间保存实际的长度信息
    2. 也可以溢出存储
  1. MySQL Server层限制单行记录的大小不超过65535字节
  1. InnoDB层限制单行记录的大小不能超过innodb_page_size的一半（实际上还要小一点，因为要扣除一些页中元数据信息），以默认的16K设置为例，其限制为8126字节
  1. Server层和存储引擎层对于一行数据有自己的视角，需要理解这两者的差异
  1. MySQL 8.0版本开始，溢出页的首页和后续页的结构是不一样的，首页里有索引结构可以根据offset快速定位到所在页，首页预留了10个索引位，不够的话会增加专门的索引页。首页的数据容量为15680，后续页的索引容量为16327
这只是通过创建表时的一些校验规则来验证char、varchar、text的底层存储规则。后续我们还会通过观察一行数据是如何存储在ibd文件上的来继续分析存储原理。

参考

  1. 技术分析 | MySQL TEXT 字段的限制
  1. 技术分享 | MySQL 字段长度限制的计算方法
  1. 可变长度字段在 InnoDB 中的存储方式
  1. Externally Stored Fields in InnoDB
  1. MySQL 8.0: InnoDB Introduces LOB Index For Faster Updates
  1. MYSQL 中 JSON 类型介绍 | 京东物流技术团队
  1. MySQL8.0 新特性：Partial Update of LOB Column
相关文章
2024-04-19
MySQL 溢出字段常规更新（full update）实验
2024-03-14
tomcat-jdbc连接池对于socketTimeout超时断连的处理BUG
2023-12-14
如何解析离线binlog文件
2023-10-08
mysql流式查询下的性能隐患
2023-10-08
mysql流式查询中的一个坑
2023-05-03
刷一张亿级表带来的思考
MySQL 溢出字段常规更新（full update）实验SpringBoot开启gzip压缩min-response-size不生效