文字列型カラム（varchar型やchar型など）に対してインデックスを作成する場合に最大キー長があり、それはバイト数で管理されています。今回はいくつかのオプションやパラメータが、InnoDBのインデックスの最大キー長に対してどのように影響するかを紹介します。

InnoDBのファイルフォーマットによるインデックスの最大キー長の違い

基本的には単一カラムインデックスの最大キー長は767バイトまで作成できます。特定の条件ではインデックスの最大キー長を3072バイトまで拡張することができます。その条件は以下のとおりです。

1. テーブル作成時に行フォーマットをDYNAMICまたはCOMPRESSEDに指定する。
2. innodb_file_per_tableパラメータをONに設定して、テーブルデータを個別のibdファイルに格納するようにする。
3. innodb_large_prefixパラメータを有効にする。

はじめにテーブルの行フォーマットをDYNAMIC、またはCOMPRESSEDに指定します。そのためにはInnoDBのファイルフォーマットをBarracudaに設定しておく必要があります。InnoDBのファイルフォーマットは旧フォーマットのAntelopeと新しいフォーマットのBarracudaがあり、innodb_file_formatパラメータで指定します。デフォルトはMySQL5.6とそれ以前ではAntelopeでMySQL5.7以降ではBarracudaとなっており、変更するにはMySQLの再起動が必要となります。

そして、テーブルの作成または変更時にROW_FORMAT句を使用して、DYNAMICまたはCOMPRESSEDに行フォーマットを設定します。

mysql> CREATE TABLE testtable ( id int ) ROW_FORMAT=DYNAMIC;

ここでは詳細なInnoDBのファイルフォーマットと行フォーマットの説明はしませんので、詳しくはマニュアルの14.9 InnoDB の行ストレージと行フォーマットをご参照ください。

次に、innodb_file_per_tableをONに設定することで、各テーブル作成時に個別のibdファイルにデータが格納されるようになります。

OFFの場合は、すべてのInnoDBストレージエンジンで作成されたテーブルは共有のibdata1に格納されます。このパラメータは稼働中のMySQLに対してオンラインでの変更が可能ですが、変更以降に作成されたテーブルから有効になります。すでにibdata1に格納されているテーブルはそのままであり、そのテーブルに対してのインデックスの最大キー長は767バイトになります。デフォルトはMySQL5.5とそれ以前ではOFFで、MySQL5.6とそれ以降ではONとなっています。

最後に、innodb_large_prefixパラメータを有効にすることで、インデックスの最大キー長を最長の3072バイトまで拡張します。このパラメータは稼働中のMySQLに対してオンラインで変更可能です。デフォルトはMySQL5.6とそれ以前ではOFFで、MySQL5.7以降ではONとなっています。

必要なパラメータのデフォルト値をまとめると、以下のようになります。

Innodbページサイズによるインデックスの最大キー長の違い

先ほど説明したインデックスの最大キー長は3072バイトまで拡張可能ですが、このバイト数で作成できるのはInnoDBのページサイズがデフォルトの16Kバイト、またはそれ以上の場合です。innodb_page_sizeパラメータを使用してInnoDBのページサイズを8Kバイトまたは4Kバイトまで小さくすると、インデックスの最大キー長も短くなります。InnoDBページサイズが8Kバイトのときは1536バイト、4Kバイトのときは768バイトとなります。

これまでの結果を表にすると以下のようになります。

文字コードの違いによる注意点

MySQLは、文字コードにより1文字に対して使用する可能性のある最大バイト数が変わります。たとえば、文字コードがutf8の場合は1文字あたり最大で3バイト使用し、utf8mb4の場合は１文字あたり最大で4バイト使用します。

そして、テーブル作成時にvarcharで指定するカラムのサイズはバイト数ではなく文字数となるため、utf8とutf8mb4で3072バイトまで拡張したインデックスを作成できるカラムサイズに違いが発生します。

utf8の場合

varchar(1024)までインデックスの作成が可能です（3バイト×1024文字＝3072バイト⁠）⁠。

varchar(1025)は最大バイト数を超えてしまうため、エラーが発生します（3バイト×1025文字＝3075バイト⁠）⁠。

mysql> CREATE TABLE utf8table
    ( id serial ,
      c1 varchar(1024),
      c2 varchar(1025)
    ) ROW_FORMAT=DYNAMIC,
      CHARSET=utf8;

mysql> ALTER TABLE utf8table ADD KEY(c1);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE utf8table ADD KEY(c2);
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

utf8mb4の場合

varchar(768)までインデックスの作成が可能です（4バイト×768文字＝3072バイト⁠）⁠。

varchar(769)は最大バイト数を超えてしまうため、エラーが発生します（4バイト×769文字＝3076バイト⁠）⁠。

mysql> CREATE TABLE utf8mb4table
    ( id serial ,
      c1 varchar(768),
      c2 varchar(769)
    ) ROW_FORMAT=DYNAMIC,
      CHARSET=utf8mb4;

mysql> ALTER TABLE utf8mb4table ADD KEY(c1);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE utf8mb4table ADD KEY(c2);
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

これにより、マスターとスレーブ間で文字コードが違った場合にレプリケーションエラーが発生するケースがあります。

たとえば、マスターとスレーブでテーブル構成は同じであるが文字コードがマスターはutf8でスレーブはutf8mb4であった場合に、マスターでは問題なくインデックスの作成は完了したが、スレーブではインデックスの最大キー長を超えてしまいエラーとなり、レプリケーションが停止してしまうなどの可能性があります。

補足として、最大バイト数を超えたときにエラーが発生してインデックス作成に失敗するのは、sql_modeパラメータにSTRICT_TRANS_TABLESが設定されている場合です。デフォルトはMySQL5.6とそれ以前では設定されていませんが、MySQL5.7以降では設定されています。ただしMySQL5.6の場合は、mysql_install_dbでデータベースを初期化した際に作成されるmy.cnfにはSTRICT_TRANS_TABLESが設定されているので注意が必要です。

このSTRICT_TRANS_TABLESが設定されていない場合は、エラーではなくワーニングが発生し、3072バイトに切り詰められたデータでインデックスが作成されます。

mysql> ALTER TABLE utf8table ADD KEY(c2);
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 1

Warning (Code 1071): Specified key was too long; max key length is 3072 bytes

SHOW CREATE TABLE構文で確認すると、切り詰められたデータでインデックスが作成されたことがわかります。

mysql> SHOW CREATE TABLE utf8table;

CREATE TABLE `utf8table` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `c1` varchar(1024) DEFAULT NULL,
  `c2` varchar(1025) DEFAULT NULL,
  UNIQUE KEY `id` (`id`),
  KEY `c2` (`c2`(1024)),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

まとめ

InnoDBのインデックスの最大キー長は最大3072バイトまで拡張可能です。

MySQL5.7以降であればデフォルト設定で3072バイトまで設定でき、MySQL5.6とそれ以前のデフォルト設定では767バイトまでとなっているので、拡張するためには設定変更が必要です。そして、文字コードにより1文字に対して使用するバイト数は違うため、インデックスを作成する予定のvarcharカラムのサイズを決める際には注意してください。