ApsaraDB RDS for MySQL でのインデックス作成時のエラー: 'Specified key was too long; max key length is 767 bytes' - ApsaraDB RDS

問題の説明

RDS インスタンス上のテーブルにインデックスを作成すると、次のエラーメッセージが表示されます。

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 フィールドを作成し、直接インデックスを作成する。
テーブルの行フォーマットが COMPACT または REDUNDANT である。

原因

MySQL インデックスの最大長は、次のような複数の要因によって制限されます。

バージョンによる制限
- MySQL 5.6 を実行している RDS インスタンスでは、innodb_large_prefix パラメーターはデフォルトで OFF に設定されており、インデックスの最大長は 767 バイトです。
- MySQL 5.7 を実行している RDS インスタンスでは、innodb_large_prefix パラメーターはデフォルトで ON に設定されており (通常はバージョン 5.7.7 以降)、最大 3072 バイトの large インデックスをサポートします。
- MySQL 8.0 を実行している RDS インスタンスでは、large インデックス機能が組み込まれています。innodb_large_prefix パラメーターは利用できなくなり、デフォルトで最大 3072 バイトのインデックスがサポートされます。
行フォーマット (ROW_FORMAT)
- COMPACT および REDUNDANT: インデックスの最大長は 767 バイトです。
- DYNAMIC および COMPRESSED: インデックスの最大長は 3072 バイトです。これらのフォーマットは、長いテキストやマルチバイト文字セットに適しています。
文字セット
- 1 文字あたり 4 バイトを使用する utf8mb4 などのマルチバイト文字セットは、インデックスにより多くのストレージ領域を必要とし、長さの制限を容易に超える原因となります。

ソリューション

この問題を解決するには、まず環境を診断して原因を特定します。次に、診断結果と MySQL のバージョンに基づいてソリューションを選択します。

ステップ 1: 環境の診断

変更を加える前に、データベースに接続し、次の SQL クエリを実行して主要な情報を収集します。

-- 'your_table_name' をテーブル名に置き換えます。

-- 1. MySQL のバージョンを確認します。バージョンによってデフォルトのパラメーターの動作が決まります。
SELECT VERSION();

-- 2. インデックス長に影響する主要な InnoDB パラメーターを確認します。
-- innodb_large_prefix: large プレフィックスインデックスが有効になっているかどうかを確認します。
SHOW VARIABLES LIKE 'innodb_large_prefix';

-- 3. ターゲットテーブルの行フォーマット (ROW_FORMAT) を確認します。
-- クエリ結果の 'Row_format' フィールドに注意してください。
SHOW TABLE STATUS LIKE 'your_table_name';

ステップ 2: 診断に基づいた修正の適用

前のステップの診断結果とインスタンスの MySQL バージョンに基づいて、適切なソリューションを選択します。

MySQL 8.0 を実行している RDS インスタンスの場合

デフォルトでは、MySQL 8.0 は最大 3072 バイトの large インデックスをサポートします。それでも 767 バイトの制限エラーが発生する場合は、通常、テーブルの ROW_FORMAT がレガシーな COMPACT フォーマットに設定されていることが原因です。

テーブルの行フォーマットを DYNAMIC に変更します。これにより、既存の機能に影響を与えることなく、テーブルが large インデックスをサポートできるようになります。
```
-- 'your_table_name' をテーブル名に置き換えます。
ALTER TABLE `your_table_name` ROW_FORMAT=DYNAMIC;
```
変更が成功したら、再度インデックスを作成できます。
重要
ALTER TABLE ... ROW_FORMAT=DYNAMIC; は、テーブル内のすべてのデータを再構築するリソース集約型の操作です。大きなテーブルの場合、この操作は多くの時間と I/O リソースを消費し、テーブルロックを引き起こす可能性があります。この操作は、ビジネスのオフピーク時間またはメンテナンスウィンドウ中に実行し、文を実行する前にバックアップを作成してください。

MySQL 5.6/5.7 の場合

MySQL 5.6 および 5.7 の場合、innodb_large_prefix パラメーターを有効にし、テーブルの行フォーマットを DYNAMIC または COMPRESSED に設定する必要があります。

innodb_large_prefix パラメーターを有効にする
- ApsaraDB [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;
```

(オプション) ステップ 3: プレフィックスインデックスの使用

テーブルスキーマを変更したくない場合、または TEXT や BLOB などの特大フィールドのコンテンツの一部のみにインデックスを作成したい場合は、プレフィックスインデックスを使用できます。

-- 例: 'long_column' フィールドの最初の 100 文字にインデックスを作成します。
CREATE INDEX idx_name ON your_table_name (long_column(100));