MySQL 索引长度有限制吗？MySQL为什么会提示”Specified key was too long; max key length is 3072 bytes”？

前段时间，我的服务向MySQL插入了一条数据，插入失败，我在日志中看到了这样的信息：

插入失败的原因是，插入的数据中有一个字段太长了。我又去看了一下表的结构和定义语句，这个字段使用的是varchar(512)，刚好我插入的字段约530个字符。

于是，我使用DDL ALTER去修改对应COLUMN的可变字段最大长度，执行语句时，却提示：

Specified key was too long; max key length is 3072 bytes.

在MySQL官方文档中，我看到这样一段说明：

• The index key prefix length limit is 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format.The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACT row format.
• For example, you might hit this limit with a column prefix index of more than 191 characters on a TEXT or VARCHAR column, assuming a utf8mb4 character set and the maximum of 4 bytes for each character.Attempting to use an index key prefix length that exceeds the limit returns an error.If you reduce the InnoDB page size to 8KB or 4KB by specifying the innodb_page_size option when creating the MySQL instance, the maximum length of the index key is lowered proportionally, based on the limit of 3072 bytes for a 16KB page size. That is, the maximum index key length is 1536 bytes when the page size is 8KB, and 768 bytes when the page size is 4KB.
• The limits that apply to index key prefixes also apply to full-column index keys.

解释一下，这里的规则前提是索引键、InnoDB引擎

• 使用DYNAMIC或COMPRESSED行格式存储时，索引键的大小限制在3072字节，如果数据库使用的是utf8mb4字符集，每个字符最多占用4个字节，也就是说索引键最多约3072/4 = 678个字符；
• 使用REDUNDANT或COMPACT行格式存储时，索引键的大小限制在767字节，使用utf8mb4字符集，最多767/4 = 191个字符。

这个限制取决于InnoDB页面大小，适用于前缀索引和列索引。

MySQL行格式

REDUNDANT

最旧的格式，存储效率低，基本不再使用。

‼️ COMPACT

比REDUNDANT节省约20%空间。

第一部分：变长字段长度列表

变长的数据类型，varchar，VARBINARY(M)、各种TEXT类型，各种BLOB，这些都是变长类型。因为他们存储多少字节数据是不确定的，所以存储数据的时候，得把他们占用的字节数存起来。 所以这些变长字段占用需要记录它占用的字节数

在Compact行格式中

• 把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表，
• 各变长字段数据占用的字节数按照列的顺序逆序存放！
• 变长字段长度列表中只存储值为 非NULL 的列内容占用的长度，值为 NULL 的列的长度是不储存的 。
• 只有变长的字符串需要存储长度，固定长度的如int,datetime…都不需要存储
• 如果列字义长度（例如：varchar(100) 在utf8格式的时候定义长度是100*3=300）超过255就用两个字节来存储每个长度

第二部分：NULL值列表

• 某些列可能存储NULL值，如果把这些NULL值都放到记录的真实数据中存储会很占地方，所以Compact行格式把这些值为NULL的列统一管理起来，存储到NULL值列表中
• 二进制位按照列的顺序逆序排列，所以第一个列和最后一个二进制位对应。
• MySQL规定NULL值列表必须用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则在字节的高位补0。

第三部分：记录头信息

它是由固定的5个字节组成。5个字节也就是40个二进制位，不同的位代表不同的意思，

• delete_mask 1 标记该记录是否被删除
• min_rec_mask 1 B+树的每层非叶子节点中的最小记录都会添加该标记
• n_owned 4 表示当前记录拥有的记录数
• heap_no 13 表示当前记录在记录堆的位置信息
• record_type 3 表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录
• next_record 16 表示下一条记录的相对位置

第四部分：记录的真实数据

mysql会给每个记录添加一些隐藏列‼️，

• DB_ROW_ID 字节数不固定,唯一标识一条记录，如果没定义主键，则是系统生成所6个字节的行ID，如果定义了主键，这里是主键，长度按主键的大小
• DB_TRX_ID 6字节 事务ID
• DB_ROLL_PTR 7字节 回滚指针

数据按照顺序存储，因为长度已经记录过了。

DYNAMIC（MySQL 5.7.9之后的默认行格式）

• DYNAMIN和COMPRESSED格式都是基于COMPACT衍生出来的
• 高效处理大型VARCHAR、TEXT和BLOB
• 使用行外页存储大型值，行内仅存20字节指针
• 动态管理溢出数据，减少碎片
• 提高大字段访问性能

┌────────────┬──────────┬───────────┬───────────┬──────────────┐
│ 变长字段    | NULL标志  │ 记录头     │ 小字段     │ 大字段指针     |
│ 长度列表    | 位图      │ (5字节)    | (完整存储) │ (20字节/列)   │
└────────────┴──────────┴───────────┴───────────┴──────┬───────┘
                                                       │
                                                       ▼
                               ┌──────────────────────────────────┐
                               │        溢出页 (页外存储)           ｜
                               │  存储大型VARCHAR/TEXT/BLOB数据     │
                               └──────────────────────────────────┘

┌────────────┬──────────┬───────────┬───────────┬──────────────┐
│ 变长字段    | NULL标志  │ 记录头     │ 小字段     │ 大字段指针     |
│ 长度列表    | 位图      │ (5字节)    | (完整存储) │ (20字节/列)   │
└────────────┴──────────┴───────────┴───────────┴──────┬───────┘
                                                       │
                                                       ▼
                               ┌──────────────────────────────────┐
                               │        溢出页 (页外存储)           ｜
                               │  存储大型VARCHAR/TEXT/BLOB数据     │
                               └──────────────────────────────────┘

COMPRESSED

• 与DYNAMIC类似但增加了压缩功能
• 数据和索引都被压缩，大幅节省空间
• 需要额外CPU资源进行压缩/解压
• 适合存储大量只读或少写入的大型数据

COMPACT、DYNAMIC、COMPRESSED之间的区别

• compact行格式，会把超过768字节的部分，存储到其他页面中
• dynamic行格式，会把大字段都存储到其他页面，只保留一个指针。
• compressed行格式，会把大字段都存储到其他页面同时压缩它，只保留一个指针。

索引键长度超限解决方案

[ Tip 1 ] 使用前缀索引代替完整索引

alter table Table_Name add key(column_name(prefix_len));
alter table Table_Name add index idx_name(name(100));
-- 或 create index index_name on Table_Name(column_name(prefix_len));

alter table Table_Name add key(column_name(prefix_len));
alter table Table_Name add index idx_name(name(100));
-- 或 create index index_name on Table_Name(column_name(prefix_len));

[ Tip 2 ] 修改MySQL的行格式

-- 修改表格式
ALTER TABLE 表名 ROW_FORMAT=DYNAMIC;

-- 创建表时指定行格式
CREATE TABLE 表名 (...) ROW_FORMAT=DYNAMIC;

-- 查看表的行格式
SHOW TABLE STATUS WHERE Name = 'Table_Name';

-- 修改表格式
ALTER TABLE 表名 ROW_FORMAT=DYNAMIC;

-- 创建表时指定行格式
CREATE TABLE 表名 (...) ROW_FORMAT=DYNAMIC;

-- 查看表的行格式
SHOW TABLE STATUS WHERE Name = 'Table_Name';

⚠️ 如果是联合索引，需要保证联合索引整体的字符数之和在限制之内。

[ Tip 3 ] 可以考虑使用哈希值或摘要替代长字符串

底层原理

InnoDB使用B+树作为索引结构，有严格的物理约束：

• 页面存储机制：InnoDB按固定大小的页（默认16KB）组织数据
• 节点大小限制：B+树每个节点必须能装入一个页内
• 树结构平衡要求：为保持B+树高效，节点中的条目数量必须在一定范围内

数据页内部结构分配：

每个记录包含：

• 记录头信息（约5-6字节）
• 实际索引键值数据
• 行指针或主键值（取决于索引类型）

在InnoDB源代码中，这个限制通过常量定义：

/* dict0dict.h中的定义 */
#define DICT_MAX_INDEX_COL_LEN 3072

/* dict0dict.h中的定义 */
#define DICT_MAX_INDEX_COL_LEN 3072

综上，这个限制的存在是基于以下技术权衡：

• 内存效率：较小的索引项可在内存缓冲池中缓存更多条目
• CPU效率：较短的键值比较操作更快
• I/O性能：适当大小的索引页可减少磁盘读写次数
• 缓存命中率：适当大小的索引有助于提高缓存命中率

因此，这个限制不是任意设置的，而是基于数据结构理论、磁盘I/O特性以及实际性能测试后确定的工程平衡点，目的是在支持合理索引键长度的同时保持数据库的整体性能。