Yakstは、海外の役立つブログ記事などを人力で翻訳して公開するプロジェクトです。
4年以上前投稿 修正あり

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

大きなサイズのインデックスを生成しようとするとERROR 1071が発生することがあり、そんな時にはinnodb_large_prefixパラメータを利用すると良いことがある。

原文
Mechanics of Flite - Using innodb_large_prefix to avoid ERROR 1071 (English)
翻訳依頼者
B5aa4f809000b9147289650532e83932
翻訳者
B5aa4f809000b9147289650532e83932 taka-h
原著者への翻訳報告
未報告


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を利用する上での前提条件を記載する。

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

innodb_file_formatinnodb_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

次の記事
MySQLのデータベース監査
前の記事
色んなログ、読む時間のある人いる? MariaDBのログ概要

Feed small 記事フィード

新着記事Twitterアカウント

新着翻訳リクエストTwitterアカウント