MySQL一行记录最大能存储多少数据

MySQL 一行记录最大能存储多少数据。答案是能存储 65535 字节（2^16 - 1）

曾经面试遇到的问题，今天就来好好挖一挖，发现挖着挖着挺有意思的。

Why#

为什么要有这个限制呢？看下官方文档对于行大小限制的描述

•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.
•The maximum row size for an InnoDB table, which applies to data stored locally within a database page, is slightly less than half a page for 4KB, 8KB, 16KB, and 32KB innodb_page_size settings. For example, the maximum row size is slightly less than 8KB for the default 16KB InnoDB page size. For 64KB pages, the maximum row size is slightly less than 16KB.

看官方文档的大致意思是：

MySQL 内部最大一行限制存储 65535 字节，即使存储引擎支持更大的行大小；除了 BOLB 和 TEXT 类型，因为这两个数据类型的内容是分开存放的，只会占用 9 到 12 个字节。说明 MySQL 内部限制一行最大存储 65535 字节，而且跟存储引擎无关。
在 Mysql 中 InnoDB 表中，数据页大小可以设置 4KB,8KB,16K 和 32KB，一个页中最多存放低于一半页大小的数据，比如页大小 16K，1 行最大存储字节必须小于 8K。64K 页大小的最大行大小限制是 16K。

第 2 点说的是 MySQL 数据页最大 1 行存储必须低于 1 半页大小的值。为什么要这样限制呢？因为 B + 树的原因，MySQL 规定一个数据页内最少存放 2 行记录，因为存放一行记录的话，B + 树没有意义，形不成有效索引。

第 1 点和第 2 点看起来好像有点冲突，其实并不是。

因为 1 个数据页里面存储多行记录，1 行记录里面对于大的数据列会存放在溢出页的。

具体的存放规则又跟存储的行格式有关（行格式就是一行记录存储格式）。常用的行存储格式有 Compact 和 Dynamic, MySQL5.7 之后，默认的行格式就是 Dynamic

Compact 存储数据的时候，如果列的数据大于 768 字节，只会存储前面 768 字节，剩余的数据会存放到溢出页里面，并用 20 字节存储溢出页的地址。

Dynamic 格式也是基于 Compact 格式修改过来，他们的区别在于列溢出时的数据处理，Dynamic 会把所有数据都存储在溢出页，只用 20 个字节存储指向溢出页的地址。

行存储大小的计算公式#

在验证行存储大小之前，得先知道行存储计算公式：

列大小 + 变长字段长度 + NULL 值列表 = 字节数

列大小 (字节数)

CREATE TABLE test ( 
    `name` VARCHAR(100) NULL
) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;

列大小是跟数据格式有关，上面 SQL 中，name varchar (100) 其中 varchar 后面括号中的 100，指的是字符数量，也就是说这个字段能存多少个字符。算字节数的话，还要跟表设置的字符集有关，比如上面例子创建表，为了方便演示和计算使用 ascii 字符集，1 个字符占用 1 个字节，那么 name 的列大小就是 100 个字节。假如是 utf8 的话，因为是动态计算，比如数字 1 占用 1 个字节，有些汉字占用 3 个字节，所以比较难以直观计算。

变长字段长度列表

要说清楚变长字段长度和 NULL 值，要先大概了解下 MySQL 一行记录到底是怎么存

compact

从图片中可以看到，MySQL 存储 1 行记录的时候，会存储记录的额外信息，其中就包括变长字段长度列表和 NULL 值列表

为什么存储变长字段长度，因为这个才是 varchar 列存储的真实字节数，读取数据的时候会根据这个字节数去读取。

变长字段长度怎么计算：

如果 1 列允许存储的最大字节数小于 255，那么就用 1 个字节表示
如果 1 列允许存储的最大字节数大于 255，那么就用 2 个字节表示

比如上面例子，name varchar (100) 用的字符集 ascii，那么他最大也就存储 100 字节，所以变长字段长度用 1 个字节即可。如果用的是字符集是 utf8，那么他最大存储字节数就是 300 字节，需要 2 个字节存储，因为 utf8 一个字符最大能占用 3 个字节。

如果表里没有 varchar 字段的话，变长字段长度列表也是不存在的

NULL 列表

MySQL 中 NULL 值并不会存储在真实数据部分，他会用 NULL 值列表来记录。当表中所有字段都定义成 NOT NULL，行格式中就不会有 NULL 值列表。

如果列中有 NULL 值，就会占用 1 个字节，比如上面列子，name 字段允许 null，那他就会占用 1 个字节。

所以上面列子的 1 行记录的字节就是：列大小 (100) + 变长字段长度 (1) + NULL 值（1） = 102 字节

验证#

验证 1 行只能存储 65535 字节：

CREATE TABLE test (
`name` VARCHAR(65535)  NULL
) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;
-- 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
-- 创建失败：因为总字节数大于65535 65535+2+1 = 65538

CREATE TABLE test (
`name` VARCHAR(65532)  NULL
) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;
-- 创建成功

验证 1 行记录在数据页只能存储低于 1 半页大小。

因为大的单列会溢出，所以我们创建多列并不让它溢出即可验证。比如页大小 16K，那么他最大 1 行存储的数据在数据页是不能超过 8K，那么我们可以创建 32 列 varchar (255)，

计算公式：(255 + 1 (变长字段长度)) * x < 8K (8192)； x = 32

CREATE TABLE test ( 
`name1` VARCHAR(255)  NOT NULL, `name2` VARCHAR(255)  NOT NULL,
`name3` VARCHAR(255)  NOT NULL, `name4` VARCHAR(255)  NOT NULL,
`name5` VARCHAR(255)  NOT NULL, `name6` VARCHAR(255)  NOT NULL,
`name7` VARCHAR(255)  NOT NULL, `name8` VARCHAR(255)  NOT NULL,
`name9` VARCHAR(255)  NOT NULL, `name10` VARCHAR(255)  NOT NULL,
`name11` VARCHAR(255)  NOT NULL, `name12` VARCHAR(255)  NOT NULL,
`name13` VARCHAR(255)  NOT NULL, `name14` VARCHAR(255)  NOT NULL,
`name15` VARCHAR(255)  NOT NULL, `name16` VARCHAR(255)  NOT NULL,
`name17` VARCHAR(255)  NOT NULL, `name18` VARCHAR(255)  NOT NULL,
`name19` VARCHAR(255)  NOT NULL, `name20` VARCHAR(255)  NOT NULL,
`name21` VARCHAR(255)  NOT NULL, `name22` VARCHAR(255)  NOT NULL,
`name23` VARCHAR(255)  NOT NULL, `name24` VARCHAR(255)  NOT NULL,
`name25` VARCHAR(255)  NOT NULL, `name26` VARCHAR(255)  NOT NULL,
`name27` VARCHAR(255)  NOT NULL, `name28` VARCHAR(255)  NOT NULL,
`name29` VARCHAR(255)  NOT NULL, `name30` VARCHAR(255)  NOT NULL,
`name31` VARCHAR(255)  NOT NULL, `name32` VARCHAR(255)  NOT NULL
) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;
-- 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.
-- 错误提示单行的最大字节数超过8126，这个8126又是怎么来的呢？
-- 我们根据数据页特性，因为最少存储2个行记录，倒推出1行记录在数据页中能存储的最大值
-- 首先数据页头占用132字节，1个数据页最少存储2条记录，
-- 计算公式：132 + 2 * x = 16 * 1024, x = 8126,8126字节就是16K页大小1行在数据页中能存储的最大值
-- 然后在看看我们这个表例子的表结构，计算下第32列应该设置多少字节
-- 因为行格式也会占用一些字节，具体有以下几部分，(行格式可以看上面的图）
-- 1-2个字节用于存储变长字节长度，可能没有
-- 1个字节用于存储NULL值列表，可能没有
-- 5个字节大小的头信息，固定
-- 6个字节的 row_id列 行ID，当表存在主键或者唯一索引约束的时候不存在
-- 6个字节的 transaction_id 事务ID，固定
-- 7个字节的 roll_pointer列 回滚指针 固定
-- 因为变长字节长度我们只占用了1个字节，NULL又没有，所以行格式只固定占用24字节。
-- 24 + 31 * （255 + 1（变长字节长度）） + x (32列的长度) < 8126 , x = 165 = (164 + 1 变长字节长度)

可能有人会说，我知道这些的知识点干什么，实际工作中又不会去计算行的大小。

其实我们知道这些特性，在工作中的时候还是可以应用。比如我们知道行格式 Compact 和 Dynamic 的区别，那么我们建表的时候还是可以使用的，比如是用户表，需要根据用户 nick 查找用户，那么我们可以指定使用行格式 Compact，这样的话数据是存储在数据页，查找速度肯定比 Dynamic 快。

在比如设计表的时候，可以指定字段 not null，设置主键或者唯一性约束可以让你节省一些空间，让数据页能存下更多记录，让查询更快。