小ネタ／MySQL 8.0（Aurora MySQL v3）にバージョンアップしたときの実行計画調整にオプティマイザヒントを使う #AWS

AWS の Aurora MySQL v1 の EoL が迫り、また MySQL 5.7 の EoL もそう遠い話ではなくなった現在、Aurora MySQL v3 や MySQL 8.0 への移行が進んでいるのではないかと思います。

※いや、前者は少ないでしょうね、おそらく。

バージョンアップ時、既存の SQL 文の実行計画が変わってしまい、処理が遅くなるケースがあります。

その対処としてオプティマイザヒントを使うのが今回のネタです。

どんなケースで遅くなる？

MySQL 5.6（Aurora MySQL v1）→ MySQL 5.7（Aurora MySQL v2）のバージョンアップで最も有名なのはこちらでしょう。

MySQLでIN句の中に大量の値の入ったクエリがフルスキャンを起こす話（freee Developers Hub）

こちらは MySQL 5.6（Aurora MySQL v1）→ MySQL 8.0（Aurora MySQL v3）のバージョンアップでも発生しうるものですが、この場合はオプティマイザヒントではなく、

サーバーの設定（パラメータグループ）を調整する
- eq_range_index_dive_limit
- range_optimizer_max_mem_size
アプリケーションの処理を見直す
- 1 つの SQL 文のIN()で列挙する値の数を制限する
- ORM・フレームワークなどで処理を分割する
  - ActiveRecord の例 :
    - ActiveRecordでINの中が一万個とかにならないようにする（kamipo's blog）

といった方法で対処します。

今回のネタはこれではなく、

バージョンアップしたら結合（JOIN）の順番が変わって遅くなった
使用するインデックスの選択が変わって遅くなった
ハッシュ結合など MySQL 8.0 で実装された最適化が適用された結果、かえって遅くなった

ケースの対処法です。

例 : 3 つのテーブルを結合する SQL 文の結合順が変わるケース

例えば、次のような構造の SQL 文があったとします。

SELECT ... FROM a, b, c WHERE ... ORDER BY ...

そして、MySQL 5.6（Aurora MySQL v1）時点のEXPLAIN（EXPLAIN EXTENDED）結果がこんな感じだったとします。

EXPLAIN（バージョンアップ前）

+----+-------------+-------+--------+---------------------+---------+---------+--------+------+----------+-----------------------------+
| id | select_type | table | type   | possible_keys       | key     | key_len | ref    | rows | filtered | Extra                       |
+----+-------------+-------+--------+---------------------+---------+---------+--------+------+----------+-----------------------------+
|  1 | SIMPLE      | a     | ALL    | PRIMARY             | NULL    | NULL    | NULL   | 5000 |   100.00 | Using where; Using filesort |
|  1 | SIMPLE      | b     | eq_ref | idx1,idx2,idx3,idx4 | row1    | 62      | a.row1 |    1 |   100.00 | Using where                 |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY             | PRIMARY | 62      | b.row2 |    1 |   100.00 | Using where                 |
+----+-------------+-------+--------+---------------------+---------+---------+--------+------+----------+-----------------------------+
3 rows in set, 1 warning (0.00 sec)

これを MySQL 8.0（Aurora MySQL v3）でEXPLAINしてみると、

EXPLAIN（バージョンアップ後）

+----+-------------+-------+------------+--------+----------------+---------+---------+--------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys  | key     | key_len | ref    | rows | filtered | Extra                                        |
+----+-------------+-------+------------+--------+----------------+---------+---------+--------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | c     | NULL       | ALL    | PRIMARY        | NULL    | NULL    | NULL   |   50 |     2.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | b     | NULL       | ref    | idx1,idx3,idx4 | idx3    | 63      | c.row2 | 1000 |    50.00 | Using where                                  |
|  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY        | PRIMARY | 62      | b.row1 |    1 |    55.55 | Using where                                  |
+----+-------------+-------+------------+--------+----------------+---------+---------+--------+------+----------+----------------------------------------------+
3 rows in set, 1 warning (0.15 sec)

結合順が逆転し、実際に SQL 文を実行したときにも処理が遅くなってしまいました。

※EXPLAINの結果表示には（比較のため）古い形式を使用しています。

1. 結合順を指定する

この SQL 文にオプティマイザヒントで結合順を指定してみます。

8.9.3 オプティマイザヒント（MySQL 8.0 リファレンスマニュアル）

※オプティマイザヒント自体は MySQL 5.7 でも使えますが、結合順の指定（JOIN_ORDER()）などは MySQL 8.0 で指定できるようになりました。

SELECT /*+ JOIN_ORDER(a, b, c) */ ... FROM a, b, c WHERE ... ORDER BY ...

※EXPLAIN結果は省略します（結果のキャプチャを忘れました・・・）。

これによって結合順はバージョンアップ前と同じになりました。ただし、テーブルcの結合に主キー（等価検索）ではなくハッシュ結合が利用され、実際に SQL 文を実行したときも以前より遅いようです。

2. 使用インデックスを指定する

続いて、テーブルcに対して主キー（PRIMARY）による検索を追加指定します。

SELECT /*+ JOIN_ORDER(a, b, c) INDEX(c PRIMARY) */ ... FROM a, b, c WHERE ... ORDER BY ...

filteredの表示値は異なりますが、テーブルcの結合に主キーが使われるようになり、SQL の処理速度も従来とほぼ変わらないレベルにまで回復しました。

EXPLAIN（オプティマイザヒント指定後）

+----+-------------+-------+------------+--------+---------------------+---------+---------+--------+------+----------+-----------------------------+
| id | select_type | table | partitions | type   | possible_keys       | key     | key_len | ref    | rows | filtered | Extra                       |
+----+-------------+-------+------------+--------+---------------------+---------+---------+--------+------+----------+-----------------------------+
|  1 | SIMPLE      | a     | NULL       | ALL    | PRIMARY             | NULL    | NULL    | NULL   | 5000 |    55.55 | Using where; Using filesort |
|  1 | SIMPLE      | b     | NULL       | eq_ref | idx1,idx2,idx3,idx4 | row1    | 62      | a.row1 |    1 |    50.00 | Using where                 |
|  1 | SIMPLE      | c     | NULL       | eq_ref | PRIMARY             | PRIMARY | 62      | b.row2 |    1 |     5.00 | Using where                 |
+----+-------------+-------+------------+--------+---------------------+---------+---------+--------+------+----------+-----------------------------+
3 rows in set, 1 warning (0.01 sec)

※このケースでは「ハッシュ結合の無効化（BNL）」は機能しませんでした。

その他

インデックスヒントとは違い、オプティマイザヒントはコメント形式で記述します。そのため、古いバージョンでサポートのないヒントは単純に無視されます。

そのため、例えば MySQL 5.6（Aurora MySQL v1）で動作中の環境にオプティマイザヒント付きの SQL 文を実行するアプリケーションを先行リリースしておき、その後 DB を MySQL 8.0（Aurora MySQL v3）にバージョンアップすると、スムーズな移行が実現できます。