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

船长☀ 的个人博客 / 222 / 5 / 创建于 10个月前 / 更新于 10个月前

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，设置主键或者唯一性约束可以让你节省一些空间，让数据页能存下更多记录，让查询更快。