Java最全【MySQL系列8】InnoDB中四种行格式分析及对varchar和char的限制，不止面试题，笔记源码统统都有

最后

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取

COMPACT格式

与REDUNDANT行格式相比，COMPACT行格式减少了约20%的行存储空间，但会增加某些操作的CPU使用。

对于变长字段的存储方式和REDUNDANT一致。

每行存储结构如下(表头部分没有含义，仅为了便于后文描述)：

| index1 | index2 | index3 | index4 | index5 | index6 |

| — | — | — | — | — | — |

| 变长字段长度列表 | NULL标志位 | 头信息 | 列1 | 列2 | 列… |

特性

• 每个索引记录包含一个5字节的头信息(index3)，并且前面可能还会有一个可变长度信息(index1和inex2)。头信息用于将连续的记录链接在一起，并用于行级锁定。

• 上图中index1部分最多2个字节。如果列的总长度小于255个字节，则index1部分用1个字节表示；如果列的长度大于255个字节，则inex1部分用2个字节表示。这是因为在InnoDB中varchar类型最大长度为65535，所以用2个字节表示就够了。

• 如果一行中有N个列允许为Null，则null标志位占用N/8个字节，也就是说，每8个Null列，就会占用1个字节.

• 如果一个列中没有Null值也没有可变长度列，上面的index1和index2就会没有（index1和index2同属于一个可变长头信息部分）。

• 将变长列值的前768字节存储在B+tree节点的索引记录中，其余的存储在溢出页中。

• 对于外部存储的列，2字节长度表示内部存储部分的长度加上指向溢出部分的20字节指针。内部部分是768字节，所以长度是768+20。20字节的指针存储列的真实长度。

• 上图中index4开始，存储的是非空列信息。

• 聚集索引中的记录包含所有用户定义列的字段。此外，还有一个6字节的事务ID字段和一个7字节的滚指针字段。

• 如果没有为表定义主键，则每个聚集索引记录还包含一个6字节的行ID字段。

• 每个辅助索引记录包含不在辅助索引中的聚集索引键定义的所有主键列。如果任何一个主键列是可变长度的，那么每个辅助索引的头信息都有一个可变长度的部分来记录它们的长度，即使辅助索引定长列。

• 在内部，对于不可变长字符集，像CHAR(10)这样的固定长度字符列以固定长度格式存储。尾随空格不会被截断。

• 在内部，对于可变长度的字符集，如utf8和utf8mb4, InnoDB尝试通过去除尾部空格将CHAR(N)存储为N个字节。如果CHAR(N)列值的字节长度超过N个字节，则会通过去除尾部空格来达到列值字节长度的最小值。CHAR(N)列的最大长度是最大字符字节长度乘以N(如uft8,CHAR(10)列最大长度为3*10)。

• 为CHAR(N)保留最小的N个字节。在多数情况下，保留最小空间可以在更新的时候不会导致索引页碎片。相比之下，在使用REDUNDANT行格式时，CHAR(N)列占用的最大字符字节长度乘以N(如utf8下，char(10)保留10个字节，而REDUNDANT会保留3*10个字节)。

DYNAMIC格式

DYNAMIC格式提供了和COMPACT相同的存储特征，但为比较长的可变长度的列增加了增强的存储能力，并支持大型索引键前缀。

当用ROW_FORMAT=DYNAMIC创建一个表时，InnoDB可以在完全脱离页面的情况下存储长列的可变长度值(对于VARCHAR、VARBINARY、BLOB和TEXT类型)，而聚集索引记录只包含一个指向溢出页面的20字节指针。大于或等于768字节的固定长度字段被编码为可变长度字段。

列是否在页外存储取决于页大小和行的总大小。如果行太长，则选择最长的列进行页外存储，直到聚集索引记录适合B+tree页为止。小于或等于40字节的文本和BLOB列存储在行中。

DYNAMIC行格式最多支持3072字节的索引键前缀。这个特性由innodb_large_prefix变量控制，该变量在默认情况下是启用的。

DYNAMIC行格式保持了在索引节点中存储整个行的效率(REDUNDANT和COMPACT格式也是如此)，但DYNAMIC行格式避免了用大量长列的数据字节填充B+树节点的问题。

动态行格式基于这样一种思想:如果长数据值的一部分存储在页外，那么在页外存储整个值通常是最有效的。使用DYNAMIC格式，较短的列可能保留在B+树节点中，从而最小化给定行所需的溢出页数。

其他的特性均与COMPACT行格式一致。

COMPRESSED格式

COMPRESSED行格式提供了与DYNAMIC行格式相同的存储特性和功能，但增加了对表和索引数据压缩的支持。

COMPRESSED行格式使用与DYNAMIC行行格式类似的实现来进行页外存储，同时还考虑了被压缩的表和索引数据的额外存储和性能考虑，并使用更小的页大小。

对于压缩行格式，KEY_BLOCK_SIZE选项控制有多少列数据存储在聚集索引中，以及有多少列数据存储在溢出页面上。

COMPRESSED行格式最多支持3072字节的索引键前缀。这个特性由innodb_large_prefix变量控制，该变量在默认情况下是启用的。

其余特性与COMPACT行格式一样。

指定表的行格式

====================================================================

InnoDB表的默认行格式是由innodb_default_row_format变量定义的，该变量的默认值为DYNAMIC。如果没有显式定义ROW_FORMAT表选项，或者指定了ROW_FORMAT= default，则使用默认的行格式。

SET GLOBAL innodb_default_row_format=DYNAMIC;

注意：有效的innodb_default_row_format选项包括DYNAMIC，COMPACT, 和REDUNDANT。因为system表空间不支持使用COMPACT行格式，因此不能将其定义为缺省值。只能在CREATE TABLE或ALTER TABLE语句中显式地指定它。试图设置innodb_default_row_format变量为COMPACT会报错:

SET GLOBAL innodb_default_row_format=COMPRESSED;

ERROR 1231 (42000): Variable ‘innodb_default_row_format’

can’t be set to the value of ‘COMPRESSED’

另外可以使用CREATE table或ALTER table语句中的ROW_FORMAT表选项显式定义表的行格式。例如:

CREATE TABLE t1 (c1 INT) ROW_FORMAT=DYNAMIC;

修改行格式注意事项

======================================================================

如果一张表的行格式需要从REDUNDANT或COMPACT改成DYNAMIC或COMPACT，需要注意的是：

REDUNDANT和COMPACT行格式支持最大索引键前缀长度为767字节，而DYNAMIC和COMPRESSED行格式支持最大索引键前缀长度为3072字节，尤其是主从复制时尤其要注意，如果主从设置的行格式不一致，可能导致一方的语句执行失败。

InnoDB限制

=====================================================================

• 一个表最多可以包含1017列(在MySQL 5.6.9中从之前的1000列限制提高了)。虚拟生成的列包括在这个限制中。

• 一个表最多可以包含64个二级索引。

• 如果innodb_large_prefix是启用的(默认)，对于使用DYNAMIC或COMPACT行格式的InnoDB表，索引键前缀限制为3072字节。如果innodb_large_prefix被禁用，对于任何行格式的表，索引键前缀限制为767字节。

innodb_large_prefix已被弃用，将在未来的版本中被删除。innodb_large_prefix是在MySQL 5.5中引入的，用于禁用大索引键前缀，以与不支持大索引键前缀的InnoDB早期版本兼容。

如果在创建MySQL实例时通过指定innodb_page_size选项将InnoDB页面大小减少到8KB或4KB，索引键的最大长度将按比例降低：3072字节限制基于16KB页面大小。也就是说，当页面大小为8KB时，最大索引键长度为1536字节，而当页面大小为4KB时，最大索引键长度为768字节。

• 适用于索引键前缀的限制也适用于全列索引键。

• 多列索引最多允许16列。

• 对于4KB、8KB、16KB和32KB的页，最大行大小(不包括存储在页外的任何可变长度的列)应略小于页大小的一半。然而对于64KB的页来说，最大行大小约为1/4页。

• LONGBLOB和LONGTEXT列必须小于4GB，包括BLOB和TEXT列在内的总行大小也必须小于4GB。

• 如果一行小于半页长，那么它全部存储在本地页中。如果它超过半页，那么将选择可变长度的列进行外部页外存储，直到行能适应半页的大小。

• 虽然InnoDB内部支持大于65,535字节的行大小，但MySQL本身对所有列的合并大小施加了65,535的行大小限制

• InnoDB日志文件的最大大小是512GB。

• 最小表空间大小略大于10MB。最大表空间大小取决于InnoDB的页面大小(表空间的最大值也是表的最大值)：

| 页大小 | 对应的表空间最大值 |

| — | — |

| 4KB | 16TB |

| 8KB | 32TB |

| 16KB | 64TB |

| 32KB | 126TB |

| 64KB | 256TB |

列的限制

---

MySQL的硬限制是每个表有4096列，但是对于给定的表，有效的最大值可能更少。确切的列限制取决于几个因素:

• 表的最大行大小限制了列的数量(以及可能的大小)，因为所有列的总长度不能超过这个大小

• 单个列的存储需求限制了给定最大行大小内所能容纳的列数。某些数据类型的存储要求取决于存储引擎、存储格式和字符集等因素.

• 存储引擎可能会施加额外的限制，限制表列计数。例如:InnoDB每个表的列数限制为1017。

• 每个表都有一个包含表定义的.frm文件,如果对frm文件做了限制也会影响

行的限制

---

给定表的最大行大小由下面几个因素决定:

• MySQL表的内部表示的最大行大小限制为65,535字节，即使存储引擎能够支持更大的行。BLOB和TEXT列只对行大小限制贡献9到12个字节，因为它们的内容与行的其余部分分开存储。

• InnoDB中，对于4KB、8KB、16KB和32KB的页，最大行大小(不包括存储在页外的任何可变长度的列)应略小于页大小的一半。然而对于64KB的页来说，最大行大小约为1/4页。

• InnoDB中如果一行小于半页长，那么它全部存储在本地页中。如果它超过半页，那么将选择可变长度的列进行外部页外存储，直到行能适应半页的大小。

举例1

下面的InnoDB和MyISAM示例演示了MySQL最大行大小限制为65,535字节。无论存储引擎是什么，都会强制执行此限制，即使存储引擎可能能够支持更大的行：

InnoDB:

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),

c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),

f VARCHAR(10000), g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET latin1;

ERROR 1118 (42000): 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

MyISAM:

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),

c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),

f VARCHAR(10000), g VARCHAR(6000)) ENGINE=MyISAM CHARACTER SET latin1;

ERROR 1118 (42000): 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

注意：这两个例子中要注意的是编码用的是latin1，如果用的是utf8则大小要除以3，用的是utf8mb4，大小要除以4，后面的例子中也是一样。

上面两个例子中，如果将最后一列g改为text则可以执行成功：

InnoDB:

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),

c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),

f VARCHAR(10000), g TEXT(6000)) ENGINE=InnoDB CHARACTER SET latin1;

Query OK, 0 rows affected (0.02 sec)

MyISAM:

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),

c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),

f VARCHAR(10000), g TEXT(6000)) ENGINE=MyISAM CHARACTER SET latin1;

Query OK, 0 rows affected (0.02 sec)

举例2

虽然最大限制为65535，但是实际上每一列还要2个字节来存储大小，所以实际上能存储的是65535-(列数*2)，注意这个前提是没有其他列：

mysql> CREATE TABLE t1

(c1 VARCHAR(32765) NOT NULL, c2 VARCHAR(32766) NOT NULL)

ENGINE = InnoDB CHARACTER SET latin1;

Query OK, 0 rows affected (0.02 sec)

上面这个加起来是65535-(2*2)=65531，如果列可以为NULL那么长度每8列还要再减1。

下面这个建立一个65535的列(65533就可以成功)是失败的：

mysql> CREATE TABLE t2

(c1 VARCHAR(65535) NOT NULL)

ENGINE = InnoDB CHARACTER SET latin1;

ERROR 1118 (42000): 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

举例3

上面举的都是变长varchar的例子，这里再来一个定长char的例子：

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=COMPACT DEFAULT CHARSET latin1;

ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using

ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768

bytes is stored inline.

如果把上面的例子中ENGINE换成MYISAM就会成功，这是为什么呢？

原因上面介绍过了，因为InnoDB引擎限制了一行最大也应略小于页的一半。16KB的一半应该是8192，除去一些其他信息占用的空间和提示中的8126差不多能匹配上，而建表语句中总长度为：255*33=8415，已经超过一半了，故而报错。

最后

面试前一定少不了刷题，为了方便大家复习，我分享一波个人整理的面试大全宝典

• Java核心知识整理

Java核心知识

• Spring全家桶（实战系列）

• 其他电子书资料

Step3：刷题

既然是要面试，那么就少不了刷题，实际上春节回家后，哪儿也去不了，我自己是刷了不少面试题的，所以在面试过程中才能够做到心中有数，基本上会清楚面试过程中会问到哪些知识点，高频题又有哪些，所以刷题是面试前期准备过程中非常重要的一点。

以下是我私藏的面试题库：

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取

存中…(img-pEpBjDHJ-1715331401298)]

• 其他电子书资料

[外链图片转存中…(img-ZYgnlocK-1715331401298)]

Step3：刷题

既然是要面试，那么就少不了刷题，实际上春节回家后，哪儿也去不了，我自己是刷了不少面试题的，所以在面试过程中才能够做到心中有数，基本上会清楚面试过程中会问到哪些知识点，高频题又有哪些，所以刷题是面试前期准备过程中非常重要的一点。

以下是我私藏的面试题库：

[外链图片转存中…(img-ybiM0Oev-1715331401298)]

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录

需要这份系统化的资料的朋友，可以点击这里获取