MySQL 8.0からサブクエリに対して最適化する機能のセミジョインが強化されました。
MySQL 5.6とそれ以降からセミジョインは追加され、サブクエリを高速で処理できるようになりました。さらに、MySQL 8.0でその機能が進化しています。今回の記事を読む前に、まず以前の記事の 第43回 MySQLの準結合(セミジョイン)についてからセミジョインの概要や戦略についてご確認ください。
今回は、強化されたMySQL8.0のセミジョインの変更点について、紹介したいと思います。
セミジョインの変更点
MySQL 8.0とそれ以前では、セミジョインを動作させるためには以下のような条件がありました。
よって、EXISTS句を使用したクエリにセミジョインを動作させるために、IN句を使用したクエリに書き換える必要がありました。しかし、MySQL8.0.16からはEXISTS句を使用したクエリでもセミジョインが動作するようになりました。
Note
部分に注目すると、内部で書き換えられたステートメントが表示されます。そこにsemi join
と記述されていることがわかります。
アンチジョイン
さらに、MySQL 8.0.17(2019/08/06現在での最新バージョン)からはアンチジョインが追加されました。アンチジョインとは、NOT EXISTS
やNOT IN
を使用したサブクエリを最適化する機能です。また、アンチジョインはduplicateweedout戦略やmaterialization戦略といった戦略や動作するための条件はセミジョインと同様になります。
たとえば、MySQL8.0.16とそれ以前では、NOT EXISTSを使用したクエリの実行計画は以下のように出力されます。
select_type
がDEPENDENT SUBQUERY
となっています。
この動作は外側のクエリで抽出された件数分を内側のクエリ(exists句内のクエリ)が実行されます。たとえば、内側のテーブルにインデックスがない場合はフルスキャンで実行されてしまい、悲惨な目にあうこともあります。これはMySQLのサブクエリは遅いと言われる要因の動作です。
MySQL 8.0.17からは以下のような実行計画になります。
Note
部分に注目すると、内部で書き換えられたSQLステートメントが表示されます。そこにanti join
と記述されていることがわかります。
上記の実行計画では、materialization
によるセミジョイン戦略が使用されています。この動作はt2テーブルから内部的に実体化した一時テーブル作成し、重複レコードを削除するためにインデックスを作成してから結合しているため、前述のもの比べて大幅に速度が改善されます。
セミジョインとアンチジョインの戦略を調整する方法
セミジョインまたは、アンチジョインはすべての戦略がデフォルトでは有効になっています。オプティマイザーがコストを計算し、戦略を選択します。しかし、たまにこちらが期待した戦略を選択しないこともあります。そのようなときに使用する戦略を調整する方法を紹介します。
optimizer_switch
MySQ L5.6とそれ以降であれば、optimizer_switchオプションを調整することで、グローバルまたはセッション単位で設定可能です。それぞれのフラグに関しては、第43回 MySQLの準結合(セミジョイン)についてに記載しています。
ヒント句
MySQL5.7とそれ以降からは、ヒント句を使ってSQLステートメント単位で調整することが可能です。
強制的にセミジョインまたはアンチジョインを有効にする場合は、SEMIJOINヒント句 /*+ SEMIJOIN(@QB_NAME) */
を使用します。強制的にセミジョインまたはアンチジョインを無効にする場合は、NO_SEMIJOINヒント句 /*+ NO_SEMIJOIN(@QB_NAME) */
を使用します。
@QB_NAMEはサブクエリ部分に記述するもので、クエリブロックの名前となります。
上記のように、まずサブクエリに対して/*+ QB_NAME(test) */
のヒント句を記述して、test
という名前をつけました。外側のクエリで/*+ NO_SEMIJOIN(@test) */
を記述することで、オプティマイザーはセミジョインを使用しないように処理します。
また、このヒント句を使って特定の戦略で処理されるように指定することも可能です。たとえば、以下のクエリはオプティマイザーがmaterialization
戦略を選択しています。
これをduplicateweedout
戦略を選択するように変更したい場合は、SEMIJOINヒント句に戦略名を追加します。以下のように記述します。
NO_SEMIJOINヒント句は選択してほしくない戦略を記述することで、オプティマイザーは記述された戦略を無視するようになります。また、カンマ区切りで複数の戦略を記述することも可能です。
有効な戦略名は以下の通りになっています。
- DUPSWEEDOUT … duplicateweedout戦略
- FIRSTMATCH … Fisrtmarch戦略
- LOOSESCAN … LooseScan戦略
- MATERIALIZATION … materialization戦略