MySQL道普請便り

第43回　MySQLの準結合（セミジョイン）について

Twitter リスト

2017年4月18日

北川健太郎

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

MySQLでは一般的にサブクエリは遅いと認識されている方が多いと思います。しかし，MySQLはバージョンアップするごとにオプティマイザが進化し，MySQL5.6とそれ以降からサブクエリが最適化するセミジョインが追加されました。今回はMySQLのセミジョインについて紹介します。

optimaizer_switchオプションについて

はじめに，オプティマイザはSQLの実行計画を作成するためのMySQLの頭脳になる部分です。オプティマイザの制御はoptimaizer_switchオプションで管理されています。このオプションは実行計画を作成するためのさまざまな動作（フラグ）が格納されていて，MySQLがバージョンアップするごとに新しい動作が追加されています。ここにMySQL5.6とそれ以降から，サブクエリの最適化するためのセミジョインに関する動作が追加されました。

セミジョインやサブクエリの最適化に関する追加された動作

最適化	フラグ名	デフォルト	追加されたバージョン
セミジョイン	semijoin	ON	MySQL5.6
セミジョイン	firstmatch	ON	MySQL5.6
セミジョイン	loosescan	ON	MySQL5.6
セミジョイン	duplicateweedout	ON	MySQL5.7
サブクエリ実体化	materialization	ON	MySQL5.6
サブクエリ実体化	subquery_materialization_cost_based	ON	MySQL5.6
派生テーブルマージ	derived_merge	ON	MySQL5.7

semijoinフラグがONの場合は，それに関連するduplicateweedoutフラグ，firstmatchフラグ，materializationフラグ，およびloosescanフラグの制御が可能となります。

準結合（セミジョイン）

セミジョインはOracle Databaseを使用したことある方にはおなじみの動作ですが，MySQL5.6で追加されました。semijoinフラグをONにすることで，WHERE句またはON句内にINを使用したサブクエリに対してセミジョインが行われます。セミジョインとはサブクエリ内のテーブルの重複レコードを取り除き，結合と同じような動きをします。

しかし，このセミジョインが動作するにはいくつかの条件があるため注意が必要です。

INまたは=ANYを使用したサブクエリであること
単一のSELECT文でUNIONを使用していないこと
Group byなどの集約関数を含めないこと
LIMITを使用したORDER BYがないこと
外部テーブルとおよび内部テーブルの合計数が結合で許可されている最大テーブル数より少ないこと

このように制限があるため，セミジョインされているかどうかはmysqlコマンドで，MySQL5.6ではexplain extendedとshow warnings，MySQL5.7ではexplainとshow warningsを使用して，表示されるNOTE部分から確認することができます。

また，このshow warningsは，mysqlコマンドの引数に--show-warningsオプションをつけて起動すると，警告があれば毎回表示されるようになるので，今回はMySQL5.7.17に対してこのオプションを指定して実行しています。

簡単なクエリを作成して実行してみます。

mysql> explain select 1 from t1 where id in (select id2 from t2);
+----+--------------+-------------+--------+---------------+------+---------+-----------------+------+----------+--------------------------+
| id | select_type  | table       | type   | possible_keys | key  | key_len | ref             | rows | filtered | Extra                    |
+----+--------------+-------------+--------+---------------+------+---------+-----------------+------+----------+--------------------------+
|  1 | SIMPLE       | <subquery2> | ALL    | NULL          | NULL | NULL    | NULL            | NULL |     0.00 | Using where              |
|  1 | SIMPLE       | t1          | eq_ref | id            | id   | 8       | <subquery2>.id2 |    1 |   100.00 | Using where; Using index |
|  2 | MATERIALIZED | t2          | ALL    | NULL          | NULL | NULL    | NULL            |    1 |   100.00 | NULL                     |
+----+--------------+-------------+--------+---------------+------+---------+-----------------+------+----------+--------------------------+
3 rows in set, 1 warning (0.06 sec)

Note (Code 1003): /* select#1 */ select 1 AS `1` from `db`.`t1` semi join (`db`.`t2`) where (`db`.`t1`.`id` = `<subquery2>`.`id2`)

このように，わかる人が見ると実行計画の内容だけでわかりますが，NOTE部分にsemi joinの記述があれば，セミジョインが動作しています。

セミジョインの戦略について

MySQLはコストに基いて，セミジョインの戦略を決定します。いくつかの戦略について実行計画の内容と共に説明します。

結合（INNER JOIN）への書き換え動作

INで記述されたサブクエリが通常の結合（INNER JOIN）へ変更できる場合は，MySQL内部で書き換えが行われます。

以下は実行計画の例です。

mysql> explain select 1 from t1 where id in (select id from t2);
+----+-------------+-------+--------+---------------+------+---------+----------+------+----------+-------------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref      | rows | filtered | Extra       |
+----+-------------+-------+--------+---------------+------+---------+----------+------+----------+-------------+
|  1 | SIMPLE      | t2    | index  | id            | id   | 8       | NULL     |    1 |   100.00 | Using index |
|  1 | SIMPLE      | t1    | eq_ref | id            | id   | 8       | db.t2.id |    1 |   100.00 | Using index |
+----+-------------+-------+--------+---------------+------+---------+----------+------+----------+-------------+
2 rows in set, 1 warning (0.05 sec)

Note (Code 1003): /* select#1 */ select 1 AS `1` from `db`.`t2` join `db`.`t1` where (`db`.`t1`.`id` = `db`.`t2`.`id`)

Note部分を確認するとjoinに書き換わっていることがわかります。この動作は，optimizer_switchオプションのsemijoinフラグをOFFにすることで無効化されます。

duplicateweedout動作

サブクエリに対して通常の結合を行い，一時テーブルを作成してその一時テーブル内で重複レコードを除外する動作です。駆動表（外部表）と内部表は，コストに基いて適宜変わります。

以下は実行計画の例です。

mysql> explain  select 1 from t1 where id in (select id2 from t2);
+----+-------------+-------+--------+---------------+------+---------+-----------+------+----------+-----------------------------------------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref       | rows | filtered | Extra                                   |
+----+-------------+-------+--------+---------------+------+---------+-----------+------+----------+-----------------------------------------+
|  1 | SIMPLE      | t2    | ALL    | NULL          | NULL | NULL    | NULL      |    1 |   100.00 | Using where; Start temporary            |
|  1 | SIMPLE      | t1    | eq_ref | id            | id   | 8       | db.t2.id2 |    1 |   100.00 | Using where; Using index; End temporary |
+----+-------------+-------+--------+---------------+------+---------+-----------+------+----------+-----------------------------------------+
2 rows in set, 1 warning (0.09 sec)

Note (Code 1003): /* select#1 */ select 1 AS `1` from `db`.`t1` semi join (`db`.`t2`) where (`db`.`t1`.`id` = `db`.`t2`.`id2`)

Note部分にsemi join，実行計画のExtra列にStart temporary　End temporary が記述されます。この動作はoptimizer_switchオプションのduplicateweedoutフラグをOFFにすることで無効化されます。MySQL5.6ではこのフラグがないため，この動作のみ無効化することはできません。

materialization動作

サブクエリ内のテーブルに対して（ここではt2テーブル）内部的に実体化した一時テーブル作成し，重複レコードを削除するためにインデックスを作成してから結合が行われます。こちらも駆動表（外部表）と内部表は，コストに基いて適宜変わります。

以下は実行計画の例です。

mysql> explain select 1 from t1 where id2 in (select id2 from t2) ;
+----+--------------+-------------+------------+--------+---------------+------------+---------+-----------+------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys | key        | key_len | ref       | rows | filtered | Extra       |
+----+--------------+-------------+------------+--------+---------------+------------+---------+-----------+------+----------+-------------+
|  1 | SIMPLE       | t1          | NULL       | ALL    | NULL          | NULL       | NULL    | NULL      |    5 |   100.00 | Using where |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_key>    | <auto_key> | 5       | ii.t1.id2 |    1 |   100.00 | NULL        |
|  2 | MATERIALIZED | t2          | NULL       | ALL    | NULL          | NULL       | NULL    | NULL      |    6 |   100.00 | NULL        |
+----+--------------+-------------+------------+--------+---------------+------------+---------+-----------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select 1 AS `1` from `db`.`t1` semi join (`db`.`t2`) where (`<subquery2>`.`id2` = `db`.`t1`.`id2`)

Note部分にsemi join，実行計画のselect_type列のMATERIALIZEDが実体化する処理であり，table列の<subqueryN>はその実体化したテーブルです。

また，table列の<subqueryN>のkey列を見ると<auto_key>と記述があります。これは，実体化した一時テーブルに対して作成されたインデックスであり，この実行計画は実体化した一時テーブルが内部表となっているため，このキーを使用して結合されていることがわかります。この動作は，optimizer_switchオプションのmaterializationフラグをOFFにすることで無効化されます。

Fisrtmarch動作

Fisrtmarchは従来のサブクエリの動作と似ています。外部表（t1テーブル）の一致する各行に対して，内部表（t2テーブル）に一致するものが見つかったら，即座に外部表に行を戻します。見つからない場合は，最後に達するまで内部表をすべて走査する動作になります。よって，サブクエリ内のテーブルが必ず内部表となります。

以下は実行計画の例です。

mysql> explain select 1 from t1 where id2 in (select id2 from t2 where id2 =1);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where                                                        |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |    16.67 | Using where; FirstMatch(t1); Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select 1 AS `1` from `db`.`t1` semi join (`db`.`t2`) where ((`db`.`t1`.`id2` = 1) and (`db`.`t2`.`id2` = 1))

実行計画のExtra列に，FirstMatch(tablename)と記述されます。optimizer_switchオプションのfirstmatchフラグをOFFにすることで，無効化されます。

LooseScan動作

LooseScanは，サブクエリ内のテーブルの結合するカラムに対してすでにインデックスが作成されている場合に動作します（ここではt2テーブルのid3カラムに対してインデックスを作成済⁠）⁠。外部表の結合するカラムのインデックス（t2テーブルのid3カラム）を走査して重複するレコードを避けて，内部表（t1テーブル）と結合を行う動作になります。よって，サブクエリ内のテーブルが必ず駆動表（外部表）となります。

以下は実行計画の例です。

mysql> explain select 1 from t1 where id2 in (select id3 from t2);
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | index | id3           | id3  | 5       | NULL |    6 |    83.33 | Using index; LooseScan                             |
|  1 | SIMPLE      | t1    | NULL       | ALL   | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select 1 AS `1` from `db`.`t1` semi join (`db`.`t2`) where (`db`.`t1`.`id2` = `db`.`t2`.`id3`)

実行計画のExtra列にLooseScanと記述されます。optimizer_switchオプションのLooseScanフラグをOFFにすることで，無効化されます。

まとめ

今回はセミジョインについて紹介しました。この最適化された処理はクエリの性能向上につながりますが，一時テーブルを作成するなど処理数が多くなります。極力はJOINでクエリを書けるようなテーブル設計を心がけましょう。

MySQL, サブクエリ, セミジョイン, 準結合

著者プロフィール

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

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

Twitter：@keny_lala

バックナンバー

MySQL道普請便り

バックナンバー一覧

ピックアップ

APIゲートウェイとサービスメッシュの違い: APIゲートウェイとサービスメッシュの，それぞれの概要とユースケースを紹介し，いずれを使用するかの判断の指針となるチートシートを提供しています。
LightningChart® Traderによるテクニカル分析と証券取引アプリケーションの開発: 証券取引アプリケーションの開発プロセスを大幅に簡素化するLightningChart® Traderのビルドイン機能について紹介します。
福岡を，もっとエンジニアが働きやすい街へ！～第一回エンジニアフレンドリーシティ福岡アワードレポート: 2020年1月31日と2月1日に開催された『第一回エンジニアフレンドリーシティ福岡アワード』表彰式で表彰された，4団体と2企業の取り組み，受賞者のコメントをお伝えします。
コードの安全性・安定性を高める開発サイクル～テスト管理の効率を上げ，脆弱性診断を自動で行う～: 本連載では，連載「業務を改善する情報共有の仕掛け」を受けつつ，安全性・安定性を加えた開発サイクルについて考えていきます。

その他の連載

CSS3アニメーションでつくるインターフェイス表現: CSS3によるアニメーション表現を紹介していきます。その中でも，幅広い読者に応用してもらえるだろうインターフェイスを主なお題とします。
MySQL道普請便り: 本連載では，MySQLを使ったアプリ開発・運用に関するノウハウをご紹介していきます。
Ubuntu Weekly Recipe: Ubuntuの強力なデスクトップ機能を活用するための，いろいろなレシピをお届けします。
JavaScriptセキュリティの基礎知識: JavaScriptに関するセキュリティ上の問題はどこで発生し，どうすれば防ぐことができるのか？について解説していきます。
Javaでなぜ問題が起きるのか〜システムをきちんと運用するための基礎知識: システムは「作って終わり」ではなく，運用の中でさまざまな問題が発生します。問題の発生に備えて事前にどのような対応をしておくべきなのか，問題発生時に何をしなければならないのか，ポイントを解説していきます。
きたみりゅうじの聞かせて珍プレー: ソフトウェア開発の現場で体験したトホホな失敗，思わずうなる珍プレーをきたみりゅうじ氏が四コママンガで紹介。みなさんからの投稿もお待ちしてます！
玩式草子─ソフトウェアとたわむれる日々: Plamo Linuxのメンテナンスの傍ら，Linuxやオープンソースソフトと日々を過ごす著者が，その魅力とつきあい方を，エッセイ風味でお届けします。
はまちちゃんとわかばちゃんのREADER'S FORUM―読者のページ: WEB+DB PRESS特別編集部員，さわやか笑顔のスーパーハカーはまちちゃんとネット大好き14歳わかばちゃんが，毎号，読者の皆さんから寄せられたおたよりを紹介します。皆さんの日頃の悩みにも答えちゃいますよ。