MySQL InnoDB 有哪些限制？

阅读完本篇文章需要10分钟

🚀 单表最多可以有`1017`个字段（包括虚拟列在内）
🚀 单表最多可以包含`64`个二级索引
🚀 复合索引最多允许 `16`个字段。
🚀 dynamic 和 compressed 行格式下索引前缀长度限制为`3072`字节
🚀 redundant 和 compact 行格式下索引前缀长度限制为`767`字节
🚀 一个 InnoDB 实例最多支持`4294967296`个表空间
🚀 所有字段的总长度不能超过`65535`字节
🚀 InnoDB 中所有的日志文件大小之和不能超过`512GB`
🚀 单表大小限制
🚀 不能有`DB_ROW_ID`, `DB_TRX_ID`, 和 `DB_ROLL_PTR`等字段名

在 MySQL 8.0 版本之后，MySQL主力发展 InnoDB 存储引擎，其他存储引擎基本很少使用，但是作为一款优秀的数据库引擎，它具体有哪些限制呢？

🚀 单表最多可以有`1017`个字段（包括虚拟列在内）

其实在开发过程中一般达不到这个规模，宽表对性能的影响显而易见。

🚀 单表最多可以包含`64`个二级索引

随着索引数的增多，表会变得越来越大，在查询性能提高的同时，由于索引维护，也会对写入性能造成一些影响。

但是我们经常能够看到各种军规，单表索引限制在几个之内，其实我认为不必遵循，只要业务需要，增加即可，在现在硬件设施的规模下谈这个有些过时了。

🚀 复合索引最多允许 `16`个字段。

🚀 dynamic 和 compressed 行格式下索引前缀长度限制为`3072`字节

比如，我们执行下面的脚本

-- 当前数据库字符集为utf8mb4
create table user_01
(
 id int primary key,
 u_name_1 varchar(768),
 u_name_2 varchar(769)
);

create index idx_uname1 on user_01(u_name_1);

create index idx_uname2 on user_01(u_name_2);

你会发现索引idx_uname1可以创建成功，但是u_name_2上创建索引会失败。不同字符集下具体长度限制如下。

select'utf8mb4' as charset,3072/4 len
 union select'utf8' ,3072/3
 union select 'gbk',3072/2
 union select 'latin1',3072/1

![[Pasted image 20231207154119.png]]

还需要注意的是，上面示例是在 InnoDB page大小为 16 KB 下的情况，页大小为其他情况，等比例推算即可。

🚀 redundant 和 compact 行格式下索引前缀长度限制为`767`字节

这两种格式其实很少用到，之前有遇到开发同学错误设置表格式为compact而导致创建索引总是报错。

🚀 一个 InnoDB 实例最多支持`4294967296`个表空间

即2的32次方个表空间, 但是需要注意实际使用的比这个要少一些，因为临时表和undo也要占用一部分。

🚀 所有字段的总长度不能超过`65535`字节

🐛 我们继续用之前的例子，创建如下的user_02表，会发生什么呢？

create table user_02
(
 id int primary key,
 u_name_1 varchar(768),
 u_name_2 varchar(769),
 u_field varchar(7000),
 u_field_2 varchar(7000),
 u_field_3 varchar(845)
);

会由于长度超过限制而报错

![[Pasted image 20231207164930.png]]

这是由于 4+2*5+(7000+7000+769+768+845)*4= 65542，超过了 65535。

这是怎么计算的呢？

int占用 4 个字节；5 个varchar字段都是超过了255，所以本身需要2个字节来存储长度；然后就是5个varchar类型字段的长度(7000+7000+769+768+845)*4。

接下来，我们修改下表结构，把u_field_3 字段类型修改为 varchar(843)

create table user_03
(
 id int primary key,
 u_name_1 varchar(768),
 u_name_2 varchar(769),
 u_field varchar(7000),
 u_field_2 varchar(7000),
 u_field_3 varchar(843)
);

这时便不会有问题，因为4+2*5+(7000+7000+769+768+843)*4 = 65534，小于65535。

需要注意的是，这是 MySQL 本身的限制，其实 InnoDB 可以超过65535。

🚀 InnoDB 中所有的日志文件大小之和不能超过`512GB`

🚀 单表大小限制

我们经常会听到，MySQL大表大小不能超过64TB。和前面索引前缀长度类似，这是因为我们一般保持 InnoDB page 为16KB，所以不能超过64，但是如果page大小不是16的话，按比例计算即可。

Page Size	Tablespace Size
4KB	16TB
8KB	32TB
16KB	64TB
32KB	128TB
64KB	256TB

🚀 不能有`DB_ROW_ID`, `DB_TRX_ID`, 和 `DB_ROLL_PTR`等字段名

因为这几个是内部字段，用于隐式主键、事务ID和回滚指针。

还是用之前的例子，只是我们这次不创建主键，发现超过长度限制了，这是为什么呢？

🍄 因为加了隐式主键DB_ROW_ID，占用了6字节，65540>65535，所以创建不起了。

create table user_04
(
 id int,
 u_name_1 varchar(768),
 u_name_2 varchar(769),
 u_field varchar(7000),
 u_field_2 varchar(7000),
 u_field_3 varchar(843)
);