文字列型カラム(varchar型やchar型など)に対してインデックスを作成する場合に最大キー長があり、それはバイト数で管理されています。今回はいくつかのオプションやパラメータが、InnoDBのインデックスの最大キー長に対してどのように影響するかを紹介します。
InnoDBのファイルフォーマットによるインデックスの最大キー長の違い
基本的には単一カラムインデックスの最大キー長は767バイトまで作成できます。特定の条件ではインデックスの最大キー長を3072バイトまで拡張することができます。その条件は以下のとおりです。
- テーブル作成時に行フォーマットを
DYNAMIC
またはCOMPRESSED
に指定する。
innodb_file_per_table
パラメータをON
に設定して、テーブルデータを個別のibdファイルに格納するようにする。
innodb_large_prefix
パラメータを有効にする。
はじめにテーブルの行フォーマットをDYNAMIC
、またはCOMPRESSED
に指定します。そのためにはInnoDBのファイルフォーマットをBarracuda
に設定しておく必要があります。InnoDBのファイルフォーマットは旧フォーマットのAntelope
と新しいフォーマットのBarracuda
があり、innodb_file_format
パラメータで指定します。デフォルトはMySQL5.6とそれ以前ではAntelope
でMySQL5.7以降ではBarracuda
となっており、変更するにはMySQLの再起動が必要となります。
そして、テーブルの作成または変更時にROW_FORMAT
句を使用して、DYNAMIC
またはCOMPRESSED
に行フォーマットを設定します。
ここでは詳細な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_file_format | 5.6とそれ以前はAntelope、5.7とそれ以降はBarracuda(このパラメータは将来廃止予定です) |
innodb_default_row_format | 5.6とそれ以前は存在しない(5.7でCompactを指定した時と同じ)、5.7とそれ以降はDynamic |
innodb_file_per_table | 5.5とそれ以前はOFF, 5.6とそれ以降はON |
innodb_large_prefix | 5.6とそれ以前はOFF, 5.7とそれ以降はON |
Innodbページサイズによるインデックスの最大キー長の違い
先ほど説明したインデックスの最大キー長は3072バイトまで拡張可能ですが、このバイト数で作成できるのはInnoDBのページサイズがデフォルトの16Kバイト、またはそれ以上の場合です。innodb_page_size
パラメータを使用してInnoDBのページサイズを8Kバイトまたは4Kバイトまで小さくすると、インデックスの最大キー長も短くなります。InnoDBページサイズが8Kバイトのときは1536バイト、4Kバイトのときは768バイトとなります。
これまでの結果を表にすると以下のようになります。
InnoDBページサイズ | 拡張したインデックスの最大キー長 | 拡張なしのインデックスの最大キー長 |
16K | 3072バイト | 767バイト |
8k | 1536バイト | 767バイト |
4k | 768バイト | 767バイト |
文字コードの違いによる注意点
MySQLは、文字コードにより1文字に対して使用する可能性のある最大バイト数が変わります。たとえば、文字コードがutf8の場合は1文字あたり最大で3バイト使用し、utf8mb4の場合は1文字あたり最大で4バイト使用します。
そして、テーブル作成時にvarcharで指定するカラムのサイズはバイト数ではなく文字数となるため、utf8とutf8mb4で3072バイトまで拡張したインデックスを作成できるカラムサイズに違いが発生します。
utf8の場合
varchar(1024)までインデックスの作成が可能です(3バイト×1024文字=3072バイト)。
varchar(1025)は最大バイト数を超えてしまうため、エラーが発生します(3バイト×1025文字=3075バイト)。
utf8mb4の場合
varchar(768)までインデックスの作成が可能です(4バイト×768文字=3072バイト)。
varchar(769)は最大バイト数を超えてしまうため、エラーが発生します(4バイト×769文字=3076バイト)。
これにより、マスターとスレーブ間で文字コードが違った場合にレプリケーションエラーが発生するケースがあります。
たとえば、マスターとスレーブでテーブル構成は同じであるが文字コードがマスターは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バイトに切り詰められたデータでインデックスが作成されます。
SHOW CREATE TABLE
構文で確認すると、切り詰められたデータでインデックスが作成されたことがわかります。
まとめ
InnoDBのインデックスの最大キー長は最大3072バイトまで拡張可能です。
MySQL5.7以降であればデフォルト設定で3072バイトまで設定でき、MySQL5.6とそれ以前のデフォルト設定では767バイトまでとなっているので、拡張するためには設定変更が必要です。そして、文字コードにより1文字に対して使用するバイト数は違うため、インデックスを作成する予定のvarcharカラムのサイズを決める際には注意してください。