なぜutf8mb4を使ったときにインデックスが足りなくなるのか？発生原因と対策について

モチベーション

令和という絵文字全盛の時代

絵文字もできなようなサービスはちょっとどうなの？とお達しがあり（いや自分で決めたんですが）…
既存のRails + MySQL構成のアプリで絵文字を使えるよう変更することになりました。

絵文字を使うために、MySQLのcharctersetをutf8mb4に変換すれば良いことは、すぐに分かりました。
その後、他に落とし穴がないか調べると、インデックス足りないよという問題が起きうることがわかりました。

ただ、

• どうしてインデックスが足りなくなるのか？
• 足りなくなるときの条件は自分たちのサービスにも当てはまるのか？

などがわからず、いくつもドキュメントをあたって調査したので、備忘録を残しておきます。

ゴール

• どうしてインデックスが足りなくなるのか？
• 足りなくなるときの条件は自分たちのサービスにも当てはまるのか？

が理解できる。

結論

なぜutf8 -> utf8mb4 にcharctersetを変更したとき、インデックス足りなくなるのか？

• MySQLのversionや設定（※1）によっては、インデックス長が767バイトである。
• utf8は、1文字3バイト。767 / 3 = 255文字分のインデックスが可能。
  • 慣習的にインデックスを255文字にしておくことがある。
• utf8mb4を使うと、1文字4バイトなので、767 / 4 = 191文字分しかインデックスを効かせることができない。
  • 255文字分のインデックスが貼れない
  • 既存のデータもインデックスが効かなくなる

という理屈です。

こんなエラーが出ます。

Index column size too large. The maximum column size is 767 bytes

※1 発生条件は後述

どうしたらいいか？（概念）

インデックスの最大キー長を767バイトから3072バイトに拡張したら良いです。
どうに変更したら良いかは、5.6の公式ドキュメントのsysvar_innodb_large_prefix部分に記述があります。

Enable this option to allow index key prefixes longer than 767 bytes (up to 3072 bytes) for InnoDB tables that use DYNAMIC or COMPRESSED row format. (Creating such tables also requires the option values innodb_file_format=barracuda and innodb_file_per_table=true.) See Section 14.22, “InnoDB Limits” for maximums associated with index key prefixes under various settings.

For tables that use REDUNDANT or COMPACT row format, this option does not affect the permitted index key prefix length.

拡張方法としては、

• ファイルフォーマットを、Barracudaにする
• 行フォーマットがDYNAMICまたはCOMPRESSEDにする
• innodb_file_per_tableパラメータがONである
• innodb_large_prefixパラメータがONである

となります。

どうしたらいいか？（具体的な設定）

↑↑↑のように設定が変えればOKです
※ 環境によってやり方が違うので、また別の記事にまとめます。

概念説明

以上で、対応方法はわかったのですが、

• ファイルフォーマット？
• 行フォーマット？
• DYNAMIC？

などなどわからないことが盛りだくさんなので、1つ1つ見ていきます。

データベースエンジン

まずは、データベースエンジンです。

InnoDBやMyISAMが有名です。代表的にはテーブルロックの仕組みが違います。

• InnoDB（レコード（行）単位のロック）
• MyISAM（テーブル単位のロック）

テーブルロックの場合、レコードをいじるときに、文字通りテーブル全体がロックされてしまい、他のレコードをいじることができなくなります（= パフォーマンスが悪くなる。）
今回の触るサービスが、InnoDBだったので、以下はInnoDBの話になります。

InnoDBのファイルフォーマットとは？ / レコードフォーマットとは？

InnoDBでファイルをどのように保持するか、レコードをどのように保持するか、の設定です。

ファイルフォーマットが2種類、レコードフォーマットがそれぞれ2種類あります。

ファイルフォーマット	利用可能なレコードフォーマット	補足
Antelope	Compact または Redundant	古いファイルフォーマット
Barracuda	DYNAMIC またはCOMPRESSED	新しいファイフォーマット。インデックス問題を回避するためこっちを使うと良い。

innodb_file_formatパラメータで指定可能ですが、変更を反映させるためにはInnoDBの再起動が必要です。

デフォルト値は下記の通りです。

• MySQL5.6以前：Antelope
• MySQL5.7以降：Barracuda

レコードフォーマットの違い（DYNAMIC / COMPRESSED）

詳細わからず、これくらいの理解…

DYNAMIC = 大きいレコードを扱うとき、ポインタで先頭データを保持して、実データを別にもつ
COMPRESSED = DYNAMIC + データ圧縮している

• InnoDBのファイルフォーマット、行フォーマットについてまとめ(Mysql5.6の話)

確認方法

ファイルフォーマットとレコードフォーマット

先述の通り、ファイルフォーマットとレコードフォーマットの組み合わせが決まっているので、SQLを叩くと確認できます。

# 全テーブルを確認する方法
mysql> SHOW TABLE STATUS \G

...一部抜粋...
*************************** 33. row ***************************
           Name: users
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 910
 Avg_row_length: 162
    Data_length: 147456
Max_data_length: 0
   Index_length: 180224
      Data_free: 0
 Auto_increment: 915
    Create_time: 2021-02-02 12:25:29
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
        
...一部抜粋...

        
# 特定のテーブルだけ確認する方法
show table status from your_database_name where name = 'articles' \G                       

この場合、「Row_format: Compact」なので、「ファイルフォーマットはAntelope」だとわかります。

innodb_file_per_table

mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

innodb_large_prefix

mysql> SHOW VARIABLES LIKE 'innodb_large_prefix';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_large_prefix | ON    |
+---------------------+-------+
1 row in set (0.06 sec)

変更方法

Rails環境で設定を変更しました。
このあたりは別記事で。

インデックスが足りなくなるのか？に関するまとめ

• 5.1 / 5.5 / 5.6: 注意が必要。アップデートするときにでも設定を変えよう
• 5.7 / 8.0: デフォルトでそれなりに用意されているので大丈夫っぽい

2021年1月にAWSからアナウンスもあり、
5.6系のサポートもなくなるので、おとなしく5.7や8.0にアップデートするのが良いでしょう。

wikiによれば、5.7のリリースが2015年なので、それ以前のものがサポート対象から外れていくのは仕方ないですね。

参考文献

• sysvar_innodb_large_prefix

  • https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix

• InnoDBの制限とファイルフォーマットAntelopeとBarracudaの違い

  • https://blog.kamipo.net/entry/2014/12/05/235641
  • ファイルフォーマットの違いや、設定の意図が説明されています

• MySQL5.5で🍣絵文字🐟を挿入/検索する

  • https://qiita.com/suzuki_sh/items/f02fc88a8514fd23a47e
  • 実際にSQLを打ち込みつつ、検索するサンプルがあります