【MySQL】インデックスを張ることのできる VARCHAR の長さには限界がある？

MySQL では長い VARCHAR にはインデックスを張れない？

MySQL では、カラムの長さが 767 バイトを超えるとインデックスが張れないので、 VARCHAR は 255 以下にしろと言うのを見ました。VARCHAR は1文字3バイトなので、255文字で 756 バイトで、限界、というわけです。

MySQL のドキュメントを読んでもよくわからなかったので、検証までしてみました。

MySQL のドキュメントを読む

以下のドキュメントの「Column Prefix Key Parts」を読んで見ます。

https://dev.mysql.com/doc/refman/8.0/en/create-index.html

ドキュメントの内容

文字列のカラムに対しては、カラム名(length) と書くと、先頭 length 文字を使ってインデックスを生成することができます。

• これは CHAR, VARCHAR, BINARY, VARBINARY に対してのみ使えます
• BLOB と TEXT については、必ず length を指定しなければなりません。 BLOB と TEXT にインデックスが張れるのは InnoDB, MyISAM, BLACKHOLE のテーブルのみです。
• length の長さはバイト数によって制限されます。
  • CREATE TABLE するときに CHAR, VARCHAR, TEXT などの、非バイナリ文字列タイプの場合は、文字数で制限されています（VARCHAR(20) なら 20「文字」）
  • BINARY, VARBINARY, BLOB などのバイナリ文字列タイプの場合はバイト数で制限されています
  • バイナリ文字列タイプではない場合は、マルチバイト文字が利用可能なので、それを考慮するようにしてください
    • 例えば、 VARCHAR(20) で、一文字3バイトであれば、最大60バイトまで入ることになります
• length をサポートしているかどうか、また、length の最大の長さがいくつか、は、ストレージエンジンによって異なります
  • REDUNDANT または COMPACT というフォーマットを使っている InnoDB は、length の最大が 767 バイトです
  • DYNAMIC または COMPRESED というフォーマットを使っている InnoDB は、length の最大が 3072 バイトです
  • MyISAM の場合は、length の最大は 1000 バイトです
  • NDB の場合はサポートしていません

最大長を超えた場合は次のように処理されます。

• Unique ではないインデックスの場合
  • strict が有効になっている場合はエラーになります
  • strict が無効になっている場合は、インデックスの長さが最大サイズ内に収まるように短縮されます
    • （一応インデックスはつくられるけど、インデックスの値がかぶる場合もあり、正常に動かないことがあるということ？）
• Unique なインデックスの場合
  • エラーになります
    • インデックスを無理やり短くすると、値が被ってしまう場合が出てきて、Unique にならなくなってしまうため
      • （多分先頭から最大長だけ取ってインデックスにする、という意味だと思う）

例えば name(10) というインデックスを使ったとします。

• 最初の10文字がかぶる人は少ないので、名前全体をインデックスとした場合に比べて性能劣化は少ない
• インデックスに使われる文字数が少なくなるので、インデックス自体が小さくなり、insert の高速化などが見込める

つまり

テーブルのエンジンや、ROW_FORMAT にもよって長さが変わるが、インデックスの長さが制限されているように聞こえます。

しかし、この章は Prefix Key Parts の話であって、Prefix の長さを指定せずにインデックスを張った場合にどういう挙動になるかが、はっきり書かれていない気がします。

実際にやってみる

というわけで実際にやってみました。

Docker で MySQL サーバーを立てます。

version: "3"
services:
  mydb:
    image: "mysql:5.7"
    container_name: "mydb"
    environment:
      - "MYSQL_ROOT_PASSWORD=root"
      - "MYSQL_DATABASE=mydb"
    ports:
      - "3306:3306"

立ち上がったDBに対して、 CREATE TABLE を実行してみます。

mysql> CREATE TABLE hoge (fuga VARCHAR(1024) PRIMARY KEY) ROW_FORMAT=COMPACT;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

PRIMARY KEY はインデックスと同じなので、767バイト制限に引っかかり、CREATEに失敗しました。 インデックスの長さには制限があるみたいです。

今回テーブル作成時に、ROW_FORMAT は明示的に COMPACT を指定しました。 指定しない場合は DYNAMIC になります。先程のドキュメントに書いてある通り、DYNAMIC の場合は上限は3072バイトになります。

インデックスの場合も同様です。これは Unique ではないインデックスですが、デフォルトだと strict らしく、インデックスの生成に失敗します。

mysql> CREATE TABLE hoge (fuga VARCHAR(1024), INDEX idx_fuga(fuga)) ROW_FORMAT=COMPACT;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

試しにインデックス無しで作成しようとすると、作成できます。

mysql> CREATE TABLE hoge (fuga VARCHAR(1024)) ROW_FORMAT=COMPACT;
Query OK, 0 rows affected (0.01 sec)

まとめ

InnoDB の場合、

ROW_FORMAT が REDUNDANT または COMPACT の場合、インデックスが張れる最長のカラム長は 767 バイト。

ROW_FORMAT がDYNAMIC または COMPRESED の場合、インデックスが張れる最長のカラム長は 3072 バイトです。

MySQL 5.7 のデフォルトの場合、 ROW_FORMAT は DYNAMIC です。

Appendix

strict について

sql_mode というのを見るとわかるみたいです。詳しくはぐぐってください＞＜

mysql> show variables like "sql_mode"\G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

僕の環境（デフォルトの設定のまま）だと、以下が指定されています。

• ONLY_FULL_GROUP_BY
• STRICT_TRANS_TABLES
• NO_ZERO_IN_DATE
• NO_ZERO_DATE
• ERROR_FOR_DIVISION_BY_ZERO
• NO_AUTO_CREATE_USER
• NO_ENGINE_SUBSTITUTION

どれがこのインデックスに関係してくるのかはよくわかってないです...

テーブルの ROW_FORMAT の調べ方

mysql> show table status like "hoge"\G
*************************** 1. row ***************************
           Name: hoge
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 2
 Avg_row_length: 8192
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-03-11 13:52:13
    Update_time: 2021-03-11 13:47:50
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

DYNAMIC の場合は当然 CREATE TABLE 成功します。

CREATE TABLE hoge (fuga VARCHAR(1024) PRIMARY KEY);

失敗から学ぶRDBの正しい歩き方 (Software Design plus)

SQLアンチパターン