MySQLでは、オプティマイザーヒントを使用してオプティマイザーを制御することで、実行計画を変更することができます。このオプティマイザーヒントはステートメントに適用できるため、ステートメント単位で最適化が可能になります。MySQL 5.7とそれ以降から使用可能です。
今回は、MySQL 8.0から追加されたオプティマイザーのヒントを主に紹介したいと思います。
オプティマイザーヒント構文
オプティマイザーのヒントは/*+ ... */
をステートメント内に記述します。SELECT
、UPDATE
やDELETE
などのDMLのキーワードの後にヒントを記述します。ヒントの内容をパーサーが認識して処理します。以下のように記載します。
指定したヒントが有効か確認するには、EXPLAIN
後にSHOW WARNINGS
実施します。たとえば、以下はNO_RANGE_OPTIMIZATION
ヒントを追加して実施した結果です。このヒントは指定したテーブルおよびインデックスを使用したレンジスキャンで解決しないようにオプティマイザーを制御します。ヒントが有効であれば、SHOW WARNINGS
にて使用されたヒントが表示されます。
無効なヒントを指定した場合は、以下のように表示されます。
このようにして、ヒントが適用されているか確認することができます。
MySQL 8.0のオプティマイザーヒント
ここからは、MySQL 8.0から追加されたオプティマイザーヒントについて紹介したいと思います。
JOINヒント
JOINヒントを使用して、JOINの結合順序をコントロールできるようになりました。こちらは過去の連載で紹介しておりますので、第97回 JOIN_ORDERを使ってJOINの順番を決める をご確認ください。
SET_VARヒント
SET_VARヒントは、ステートメントの間のみシステム変数のセッション値を一時的に設定します。個人的にはこのヒントが追加されてとても嬉しいです。構文は、SET_VAR(var_name = value)
というように記述します。
このヒントの使用例を紹介します。
従来であれば、レンジオプティマイザーの使用可能なメモリサイズを制御するシステム変数range_optimizer_max_mem_size
や、オプティマイザーの動作を制御するシステム変数optimizer_switch
などのシステム変数を特定のステートメントに値を変更したいときには、以下のようにしなければなりませんでした。
SET SESSION
を使用して値を変更
- ステートメント実行
SET SESSION
を使用して値を戻す
セッション値の変更はステートメント間やトランザクション間ではなく、そのセッションが閉じられるまで有効です。よってコネクションプーリングなど使用している場合、ステートメント終了後にセッション値をデフォルトまたは変更前の値に戻さなければ、セッションが生きている間は以降のステートメントすべてに影響があります。
以下のようにヒントを使用すると、そのステートメントのみにセッション値が有効になるので、それらの問題は解決できます。
INDEX_MERGE・NO_INDEX_MERGEヒント
指定されたテーブル、またはインデックスのインデックスマージアクセス方法を、有効または無効にするヒントです。
MERGE・NO_MERGEヒント
指定されたテーブル、ビューや、共通テーブル式(Common Table Expressions)のマージを有効または無効にするヒントです。
- SHOW WARNINGSにエラー情報はでなくても、ヒントの効果がないとオプティマイザーが判断すると無視されることもあります。
- optimizer_switchシステム変数のderived_mergeフラグよりもヒントが優先されます。
- ビューの場合は、ヒントよりもビュー定義の
ALGORITHM = {MERGE | TEMPTABLE}
句が優先されます。
SKIP_SCAN・NO_SKIP_SCANヒント
MySQL 8.0.13から追加されたインデックススキップスキャンを制御します。指定したテーブル、またはインデックススキップスキャンのアクセス方法を、有効または無効にします。
HASH_JOIN・NO_HASH_JOINヒント
MySQL 8.0.18から追加されたHASH JOINのアクセス方法を有効または無効にするヒントです。しかし、このヒントはMySQL 8.0.18のみで有効で、以降のMySQL 8.0.19からは効果がありませんので注意です。筆者がMySQL 8.0.19で試したところ、Hash Joinを無効化するにはNO_BNLヒントを使用すると無効になることは確認できました。
RESOURCE_GROUPヒント
MySQL 8.0から追加されたリソースグループのためのヒントです。ヒントにリソースグループ名を指定してステートメントを実行すると、ステートメントの実行の間のみスレッドをそのリソースグループに割り当てます。
まとめ
今回紹介した内容は、MySQL 8.0のリファレンスマニュアル 8.9.3 Optimizer Hints を元にしています。もっと詳細が知りたい場合はこちらをご確認ください。