MySQL道普請便り

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

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

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

optimaizer_switchオプションについて

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

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

最適化フラグ名デフォルト追加されたバージョン
セミジョインsemijoinONMySQL5.6
セミジョインfirstmatchONMySQL5.6
セミジョインloosescanONMySQL5.6
セミジョインduplicateweedoutONMySQL5.7
サブクエリ実体化materializationONMySQL5.6
サブクエリ実体化subquery_materialization_cost_basedONMySQL5.6
派生テーブルマージderived_mergeONMySQL5.7

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

準結合(セミジョイン)

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

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

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

このように制限があるため,セミジョインされているかどうかは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でクエリを書けるようなテーブル設計を心がけましょう。

著者プロフィール

北川健太郎(きたがわけんたろう)

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

Twitter:@keny_lala

コメント

コメントの記入