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_limitrange_optimizer_max_mem_size
- アプリケーションの処理を見直す
- 1 つの SQL 文の
IN()で列挙する値の数を制限する - ORM・フレームワークなどで処理を分割する
- ActiveRecord の例 :
- ActiveRecordでINの中が一万個とかにならないようにする（kamipo's blog）
- ActiveRecord の例 :
- 1 つの SQL 文の
といった方法で対処します。
今回のネタはこれではなく、
- バージョンアップしたら結合（
JOIN）の順番が変わって遅くなった - 使用するインデックスの選択が変わって遅くなった
- ハッシュ結合など MySQL 8.0 で実装された最適化が適用された結果、かえって遅くなった
ケースの対処法です。
例 : 3 つのテーブルを結合する SQL 文の結合順が変わるケース
例えば、次のような構造の SQL 文があったとします。
SELECT ... FROM a, b, c WHERE ... ORDER BY ...
そして、MySQL 5.6（Aurora MySQL v1）時点のEXPLAIN（EXPLAIN EXTENDED）結果がこんな感じだったとします。
+----+-------------+-------+--------+---------------------+---------+---------+--------+------+----------+-----------------------------+
| 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してみると、
+----+-------------+-------+------------+--------+----------------+---------+---------+--------+------+----------+----------------------------------------------+
| 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 の処理速度も従来とほぼ変わらないレベルにまで回復しました。
+----+-------------+-------+------------+--------+---------------------+---------+---------+--------+------+----------+-----------------------------+
| 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）にバージョンアップすると、スムーズな移行が実現できます。