innodb_large_prefixを使ってERROR 1071を回避する

MySQLのInnoDBストレージエンジンのテーブルの長いvarcharカラムを含むカラムにインデックスを生成しようとしたことがあれば、このエラーを見たことがあるだろう。

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

文字数制限は、使っている文字コードに依存する。例えば latin1 であればインデックスを生成できる最大カラムは varchar(767)であるが、 utf8 の場合は varchar(255) までである。 インデックスあたり、3072バイトという別の制限もある。767バイトはカラムごとの制限なので、複数のカラムを(それぞれが767バイト以下で)3072バイトまで格納できる。 (MyISAMは少々異なり、インデックスに対し1000バイトの制限があり、カラムごとの制限はない)。

この制限に対する1つのワークアラウンドは、長いカラムの接頭部分だけにインデックスを作成することであるが、InnoDBの767バイトを超えるカラムにインデックスをはりたい場合どうしたらよいだろうか？

この場合、innodb_large_prefix を検討すると良いだろう。 これは、MySQL 5.5.14から導入され、マニュアル記載の通りInnodbインデックスに3072バイトまで許容するものだ。

InnoDBの内部の最大キー長は3500バイトであるが、MySQL自体が3072バイトに制限している。この制限は、マルチカラムインデックスの複合キーの長さにも適用される。

詳細と実例に関しては、innodb_large_prefixをご覧いただきたい。

ここにいくつかinnodb_large_prefixを利用する上での前提条件を記載する。

• データベースでinnodb_file_format=BARRACUDAを利用すること
• テーブルでROW_FORMAT=DYNAMICかROW_FORMAT=COMPRESSEDとなっていること

後方互換を考えて標準のファイルフォーマットはAntelope、標準の列フォーマットはCOMPACTである。

innodb_file_format と innodb_large_prefixは両方とも動的に設定可能であるが、再起動した際にも値が保持されるようにmy.cnfも設定をしていただきたい。

ここに例を示す。innodb_large_prefix が無効化されている状態でテーブルを作成しようとするときはエラーが発生する。

mysql> create table if not exists utf8_test (
    ->   day date not null,
    ->   product_id int not null,
    ->   dimension1 varchar(500) character set utf8 collate utf8_bin not null,
    ->   dimension2 varchar(500) character set utf8 collate utf8_bin not null,
    ->   unique index unique_index (day, product_id, dimension1, dimension2)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

innodb_large_prefix を有効にすればテーブルは正常に作ることが出来る。

mysql> set global innodb_file_format = BARRACUDA;
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_large_prefix = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> create table if not exists utf8_test (
    ->   day date not null,
    ->   product_id int not null,
    ->   dimension1 varchar(500) character set utf8 collate utf8_bin not null,
    ->   dimension2 varchar(500) character set utf8 collate utf8_bin not null,
    ->   unique index unique_index (day, product_id, dimension1, dimension2)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.02 sec)

latin1の時の例も同様だが、シングルバイトのキャラクタセットであるため、3倍のカラムが利用できる。

mysql> create table if not exists latin1_test (
    ->   day date not null,
    ->   product_id int not null,
    ->   dimension1 varchar(1500) not null,
    ->   dimension2 varchar(1500) not null,
    ->   unique index unique_index (day, product_id, dimension1, dimension2)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

mysql> set global innodb_file_format = BARRACUDA;
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_large_prefix = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> create table if not exists latin1_test (
    ->   day date not null,
    ->   product_id int not null,
    ->   dimension1 varchar(1500) not null,
    ->   dimension2 varchar(1500) not null,
    ->   unique index unique_index (day, product_id, dimension1, dimension2)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.02 sec)

3072バイトを超えるインデックスを作ろうとすると下記のようになる。

mysql> create table if not exists long_index_test (
    ->   day date not null,
    ->   product_id int not null,
    ->   dimension1 varchar(1500) not null,
    ->   dimension2 varchar(1500) not null,
    ->   dimension3 varchar(1500) not null,
    ->   unique index unique_index (day, product_id, dimension1, dimension2, dimension3)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes