gihyo.jpサイトのロゴ

MySQL道普請便り

第117回MySQL 8.0のオプティマイザーヒント

2020-03-10

MySQLでは、オプティマイザーヒントを使用してオプティマイザーを制御することで、実行計画を変更することができます。このオプティマイザーヒントはステートメントに適用できるため、ステートメント単位で最適化が可能になります。MySQL 5.7とそれ以降から使用可能です。

今回は、MySQL 8.0から追加されたオプティマイザーのヒントを主に紹介したいと思います。

オプティマイザーヒント構文

オプティマイザーのヒントは/*+ ... */をステートメント内に記述します。SELECT、UPDATEやDELETEなどのDMLのキーワードの後にヒントを記述します。ヒントの内容をパーサーが認識して処理します。以下のように記載します。

mysql> SELECT /*+ hint */ ...
mysql> UPDATE /*+ hint */ ...

指定したヒントが有効か確認するには、EXPLAIN後にSHOW WARNINGS実施します。たとえば、以下はNO_RANGE_OPTIMIZATIONヒントを追加して実施した結果です。このヒントは指定したテーブルおよびインデックスを使用したレンジスキャンで解決しないようにオプティマイザーを制御します。ヒントが有効であれば、SHOW WARNINGSにて使用されたヒントが表示されます。

mysql> EXPLAIN SELECT /*+ NO_RANGE_OPTIMIZATION(t PRIMARY) */ * FROM t WHERE id BETWEEN 1 AND 10;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL | 39528 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+

mysql> SHOW WARNINGS;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                           |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select /*+ NO_RANGE_OPTIMIZATION(`t`@`select#1` `PRIMARY`) */ `d`.`t`.`id` AS `id`,`d`.`t`.`k` AS `k`,`d`.`t`.`c` AS `c`,`d`.`t`.`pad` AS `pad` from `d`.`t` where (`d`.`t`.`id` between 1 and 10) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

無効なヒントを指定した場合は、以下のように表示されます。

mysql> EXPLAIN SELECT /*+ NO_RANGE_OPTIMIZATION(t idx_t) */ * FROM t WHERE id BETWEEN 1 AND 10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   10 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                    |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3128 | Unresolved name `t`@`select#1` `idx_t` for NO_RANGE_OPTIMIZATION hint                                                                                      |
| Note    | 1003 | /* select#1 */ select `d`.`t`.`id` AS `id`,`d`.`t`.`k` AS `k`,`d`.`t`.`c` AS `c`,`d`.`t`.`pad` AS `pad` from `d`.`t` where (`d`.`t`.`id` between 1 and 10) |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+

このようにして、ヒントが適用されているか確認することができます。

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などのシステム変数を特定のステートメントに値を変更したいときには、以下のようにしなければなりませんでした。

  1. SET SESSIONを使用して値を変更
  2. ステートメント実行
  3. SET SESSIONを使用して値を戻す
mysql> SET SESSION optimizer_switch='condition_fanout_filter=off';
mysql> SELECT....;
mysql> SET SESSION optimizer_switch='condition_fanout_filter=on';

セッション値の変更はステートメント間やトランザクション間ではなく、そのセッションが閉じられるまで有効です。よってコネクションプーリングなど使用している場合、ステートメント終了後にセッション値をデフォルトまたは変更前の値に戻さなければ、セッションが生きている間は以降のステートメントすべてに影響があります。

以下のようにヒントを使用すると、そのステートメントのみにセッション値が有効になるので、それらの問題は解決できます。

mysql> SELECT /*+ SET_VAR(optimizer_switch='condition_fanout_filter=off') */ ...

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ヒントを使用すると無効になることは確認できました。

mysql> EXPLAIN FORMAT=tree SELECT /*+ NO_HASH_JOIN(t,t2) */ * FROM t JOIN t2 USING(c);
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (t.c = t2.c)  (cost=4105.41 rows=3953)
    -> Table scan on t  (cost=547.54 rows=39528)
    -> Hash
        -> Table scan on t2  (cost=0.35 rows=1)
 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN FORMAT=tree SELECT /*+ NO_BNL(t,t2) */ * FROM t JOIN t2 USING(c);
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                    |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join  (cost=4105.40 rows=3953)
    -> Table scan on t2  (cost=0.35 rows=1)
    -> Filter: (t.c = t2.c)  (cost=547.53 rows=3953)
        -> Table scan on t  (cost=547.53 rows=39528)
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

RESOURCE_GROUPヒント

MySQL 8.0から追加されたリソースグループのためのヒントです。ヒントにリソースグループ名を指定してステートメントを実行すると、ステートメントの実行の間のみスレッドをそのリソースグループに割り当てます。

まとめ

今回紹介した内容は、MySQL 8.0のリファレンスマニュアル 8.9.3 Optimizer Hints を元にしています。もっと詳細が知りたい場合はこちらをご確認ください。

おすすめ記事

記事・ニュース一覧