解決建立索引時報錯“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));