MySQL道普請便り

第135回　MySQL 8.0で追加されたoptimizer_switchのフラグについて

MySQL, オプティマイザー, optimizer_switch

この記事を読むのに必要な時間：およそ 4.5 分

今回は，MySQL 8.0で追加されたoptimizer_switchの新しいフラグについて紹介したいと思います。MySQLのバージョンは，2020年11月現在で最新のMySQL 8.0.22を使用します。

はじめに

optimizer_switchとは，オプティマイザーの動作を制御するためのシステム変数です。

mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
                    index_merge_sort_union=on,index_merge_intersection=on,
                    engine_condition_pushdown=on,index_condition_pushdown=on,
                    mrr=on,mrr_cost_based=on,block_nested_loop=on,
                    batched_key_access=off,materialization=on,semijoin=on,
                    loosescan=on,firstmatch=on,duplicateweedout=on,
                    subquery_materialization_cost_based=on,
                    use_index_extensions=on,condition_fanout_filter=on,
                    derived_merge=on,use_invisible_indexes=off,skip_scan=on,
                    hash_join=on,subquery_to_derived=off,
                    prefer_ordering_index=on,hypergraph_optimizer=off,
                    derived_condition_pushdown=on
1 row in set (0.00 sec)

値はフラグの集合で，各フラグは対応するオプティマイザーの動作です。その動作が有効か無効かをonまたはoffの値で制御します。ステートメント単位，セッション単位やグローバル単位で設定を変更することができます。

例として，現在のセッションでindex_mergeフラグの動作をoffにする場合は以下のように記述します。

mysql> SET SESSION optimizer_switch='index_merge=off';

また，ステートメント単位で設定するときは，SET_VARヒント句を使用します。SET_VARヒント句については第117回　MySQL 8.0のオプティマイザーヒントをご参照ください。

MySQL 8.0で追加されたフラグ

MySQL 8.0で追加されたフラグは以下になります。

  • prefer_ordering_index
  • derived_condition_pushdown
  • use_invisible_indexes
  • skip_scan
  • hash_join
  • subquery_to_derived
  • hypergraph_optimizer

これらのフラグとその動作について紹介したいと思います。

prefer_ordering_index

このフラグはMySQL 8.0.21で追加されました。デフォルトはONです。

prefer_ordering_indexがONの場合，オプティマイザーはORDER BYとLIMIT句で構成されたクエリに対して，ORDER BYに指定されたカラムにインデックスがあると，優先してそのインデックスを選択する動作になります。これはソート処理をバイパスできるため，オプティマイザーが最適と判断するためです。

しかし，これはWHERE句で十分にレコード数を絞り込めるインデックスがあるにもかかわらず，ORDER BYで指定されたカラムのインデックスが選択されてクエリが遅くなることがありました。そういったときに，prefer_ordering_indexをOFFにすると効果があります。

ちなみに，MySQL 8.0.20以前からこの動作はありました。しかし，常にONで制御することはできなかったのが，MySQL 8.0.21から制御できるようになったのです。

それでは，この動作を例を用いて説明したいと思います。以下のようなarticleテーブルを用意し，countrycodeとcreate_atカラムにそれぞれインデックスが貼ってあります。

mysql> CREATE TABLE `article` (
  `article_no` bigint NOT NULL AUTO_INCREMENT,
  `countrycode` varchar(3) DEFAULT NULL,
  `create_at` datetime DEFAULT NULL,
  PRIMARY KEY (`article_no`),
  KEY `countrycode` (`countrycode`),
  KEY `create_at` (`create_at`)
) ENGINE=InnoDB

countrycodeカラムのデータ分布はJPに大きく偏っています。

mysql> SELECT countrycode,COUNT(*) FROM article GROUP BY 1 ORDER BY 2 DESC;
+-------------+----------+
| countrycode | COUNT(*) |
+-------------+----------+
| JP          |  1000000 |
| CN          |     5000 |
| KR          |     5000 |
| VN          |     5000 |
+-------------+----------+

このテーブルに対して，以下のようなクエリを実行します。

EXPLAIN SELECT * FROM article WHERE countrycode='KR' ORDER BY create_at DESC LIMIT 100\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: index
possible_keys: countrycode
          key: create_at
      key_len: 6
          ref: NULL
         rows: 5392
     filtered: 1.85
        Extra: Using where; Backward index scan

すると，countrycode='KR'で十分に絞り込める（5000レコード）はずが，ORDER BY句のcreate_atのインデックスで解決しています。

create_atを降順に辿って，countrycode='KR'のレコード100件がすぐに見つかれば速いです。しかし，もしcountrycode='KR'のレコードが降順から辿って一番最後の方にあると，ほぼフルスキャンと変わらない実行時間になります。このときのクエリの実行時間は100 rows in set (1.47 sec)でした。

そのため，countrycodeのインデックスで解決するために，prefer_ordering_index=offにしてクエリを実行してみます。

EXPLAIN SELECT /*+ SET_VAR(optimizer_switch='prefer_ordering_index=off') */ * FROM article WHERE countrycode='KR' ORDER BY create_at DESC LIMIT 100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ref
possible_keys: countrycode
          key: countrycode
      key_len: 15
          ref: const
         rows: 19412
     filtered: 100.00
        Extra: Using filesort

countrycodeのインデックスで解決されました。

Extra: Using filesortからソート処理が実施されていますが，件数が少ないためソート処理による負荷影響は少ないといえます。このときのクエリの実行時間は100 rows in set (0.01 sec)でした。countrycodeのインデックスを使用したほうが効果的だとわかります。

また，countrycode='JP'の条件であれば，create_atインデックスのほうが最適な実行計画といえます。以下のような実行時間になりました。

  • prefer_ordering_index=on（create_atインデックスを使用⁠）⁠ → 100 rows in set (0.01 sec)
  • prefer_ordering_index=off（countrycodeインデックスを使用⁠）⁠→ 100 rows in set (3.88 sec)

このように，データ分布の偏りによって最適な実行計画が異なる場合は，prefer_ordering_indexを使って最適な実行計画を選ぶように，オプティマイザーに教えてあげることが必要です。

ちなみに，今回の例ではこの動作をわかりやすく説明するために2つのインデックスを用意しましたが，このクエリにおいては(countrycode,create_at DESC)のマルチカラムインデックスを作成すれば，このような問題は起こりません。

MySQL, オプティマイザー, optimizer_switch

著者プロフィール

北川健太郎（きたがわけんたろう）

LINE株式会社所属のデータベースエンジニア。担当はMySQLとOracle Database。好きなMySQLの機能はレプリケーションで，好きなOracleDatabaseの機能はログオントリガー。

Twitter：@keny_lala