解决创建索引时“Specified key was too long; max key length is 767 bytes”错误-云数据库 RDS-阿里云

问题描述

阿里云RDS MySQL版在创建表索引时，可能因索引键长度超出限制而遇到 Error 1071 或 ERROR 1709 错误。

常见错误信息

ERROR 1071: Specified key was too long; max key length is 767 bytes

ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes

典型触发场景

在使用了多字节字符集（如 utf8mb4）的 VARCHAR(255) 字段上创建索引,导致索引长度超出限制。
创建多列组合索引，所有列的长度总和超出限制。
创建 TEXT / BLOB 字段并直接加索引，但未指定前缀长度。
数据表的行格式 ROW_FORMAT 为 COMPACT 或 REDUNDANT。

问题原因

MySQL 索引的最大长度受多方面限制，包括：

版本限制
- RDS MySQL 5.6: 默认 innodb_large_prefix=OFF，最大索引长度 767 字节。
- RDS MySQL 5.7: 默认 innodb_large_prefix=ON（通常指 5.7.7+ 版本），支持 3072 字节大索引。
- RDS MySQL 8.0: 大索引功能为内置标准，不再有 innodb_large_prefix 参数，默认支持 3072 字节索引。
行格式（ROW_FORMAT）
- COMPACT 和 REDUNDANT ：最大索引长度 767 字节。
- DYNAMIC 和 COMPRESSED：最大索引长度 3072 字节，（更适合长文本、多字节字符集）。
字符集
- 多字节字符集（如 utf8mb4 每字符 4 字节）会导致索引占用更多空间，容易超限。

解决方案

解决此问题的流程分为两步：首先诊断当前环境以定位具体原因，然后根据诊断结果和 MySQL 版本选择对应的修复方案。

第一步：诊断当前环境

在进行任何修改前，请先连接到您的数据库，执行以下 SQL 查询，收集关键信息。

-- 将 'your_table_name' 替换为您的表名

-- 1. 查看 MySQL 版本，这决定了参数的默认行为
SELECT VERSION();

-- 2. 查看影响索引长度的关键 InnoDB 参数
-- innodb_large_prefix: 是否开启大前缀索引
SHOW VARIABLES LIKE 'innodb_large_prefix';

-- 3. 查看目标表的行格式(ROW_FORMAT)
-- 重点关注查询结果中的 'Row_format' 字段
SHOW TABLE STATUS LIKE 'your_table_name';

第二步：根据诊断结果执行修复

根据上一步的诊断结果，结合您的 MySQL 版本，选择对应的操作路径。

针对 MySQL 8.0

MySQL 8.0 默认支持 3072 字节的大索引。若仍遇到 767 字节限制的错误，通常是因为表的 ROW_FORMAT 仍然是旧的 COMPACT。

修改表行格式为 DYNAMIC，它使表能够支持大索引，且不影响现有功能。
```
-- 将 'your_table_name' 替换为您的表名
ALTER TABLE `your_table_name` ROW_FORMAT=DYNAMIC;
```
修改成功后，即可重新尝试创建索引。
重要
ALTER TABLE ... ROW_FORMAT=DYNAMIC; 是一个会重构整个表数据的重操作。对于大表，它将消耗大量时间、I/O资源并可能导致表锁定。请务必在业务低峰期或维护窗口执行此操作，并在执行前做好备份。

针对 MySQL 5.6/5.7

MySQL5.6/5.7需要确保 innodb_large_prefix 参数开启，并将表的行格式修改为 DYNAMIC 或 COMPRESSED。

开启 innodb_large_prefix 参数
- 登录您的云数据库RDS管理控制台，进入目标实例的参数设置页面。
- 找到 innodb_large_prefix 参数，将其值修改为ON并提交。
应用 DYNAMIC 行格式
- 对于已存在的表：
```
-- 将 'your_table_name' 替换为您的表名
ALTER TABLE `your_table_name` ROW_FORMAT=DYNAMIC;
```
  修改成功后，即可重新尝试创建索引。
  重要
  ALTER TABLE ... ROW_FORMAT=DYNAMIC; 是一个会重构整个表数据的重操作。对于大表，它将消耗大量时间、I/O资源并可能导致表锁定。请务必在业务低峰期或维护窗口执行此操作，并在执行前做好备份。
- 对于新建的表：
```
CREATE TABLE `your_new_table` ( 
 -- ... 表结构定义 ...
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
```

（可选）第三步：使用前缀索引

如果您不想修改表结构，或只需对超长字段（如 TEXT, BLOB）的部分内容进行索引，可使用前缀索引。

-- 示例：为 'long_column' 字段的前 100 个字符创建索引
CREATE INDEX idx_name ON your_table_name (long_column(100));