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道普請便り

バックナンバー一覧

ピックアップ

「杜の都」発の開発ツールベンダーが時代を超えて開発者の生産性向上を支援～「Toolsの杜（ツールのもり）」開催！: A Good Tree Bears Good Fruit-1983年から開発ツールビジネスをスタートしたグレーシティ。開発支援ツール30周年記念フォーラム「Toolsの杜」の様子をレポートします。
業務を改善する情報共有の仕掛け～DevOpsの実現，RPAの導入に向けて～: ソフトウェア開発の業務改善をテーマに，DevOps実現の手順やそれを助けるツールとの連携，RPAによる社内ドキュメントの管理や効率化について解説します。
大人気動画コミュニティアプリの運用の内幕―MixChannel（ミクチャ）を支える技術: 若者世代から圧倒的な人気を誇るライブ配信＆動画投稿コミュニティアプリ「MixChannel」（ミクチャ）の裏側（レコメンドシステムやサーバサイド開発など）について解説していきます。
［特別広報］IPとITを組み合わせて世界へ KADOKAWAだからこそできる新しいコンテンツ発信を: 人気アニメやコミック，小説などのIPを多数抱えるKADOKAWA。今回はIPを活用した新コンテンツ配信サービスの開発を進める若手エンジニア3名を中心に，開発体制や職場体制を伺いました。

その他の連載

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