MySQLでは一般的にサブクエリは遅いと認識されている方が多いと思います。しかし、
optimaizer_switchオプションについて
はじめに、
セミジョインやサブクエリの最適化に関する追加された動作
| 最適化 | フラグ名 | デフォルト | 追加されたバージョン |
|---|---|---|---|
| セミジョイン | semijoin | ON | MySQL5. |
| セミジョイン | firstmatch | ON | MySQL5. |
| セミジョイン | loosescan | ON | MySQL5. |
| セミジョイン | duplicateweedout | ON | MySQL5. |
| サブクエリ実体化 | materialization | ON | MySQL5. |
| サブクエリ実体化 | subquery_ | ON | MySQL5. |
| 派生テーブルマージ | derived_ | ON | MySQL5. |
semijoinフラグがONの場合は、
準結合（セミジョイン）
セミジョインはOracle Databaseを使用したことある方にはおなじみの動作ですが、
しかし、
- INまたは=ANYを使用したサブクエリであること
- 単一のSELECT文でUNIONを使用していないこと
- Group byなどの集約関数を含めないこと
- LIMITを使用したORDER BYがないこと
- 外部テーブルとおよび内部テーブルの合計数が結合で許可されている最大テーブル数より少ないこと
このように制限があるため、
また、--show-warningsオプションをつけて起動すると、
簡単なクエリを作成して実行してみます。
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`)
このように、
セミジョインの戦略について
MySQLはコストに基いて、
結合（INNER JOIN）への書き換え動作
INで記述されたサブクエリが通常の結合
以下は実行計画の例です。
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に書き換わっていることがわかります。この動作は、
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、
materialization動作
サブクエリ内のテーブルに対して
以下は実行計画の例です。
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、<subqueryN>はその実体化したテーブルです。
また、<subqueryN>のkey列を見ると<auto_と記述があります。これは、
Fisrtmarch動作
Fisrtmarchは従来のサブクエリの動作と似ています。外部表
以下は実行計画の例です。
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列に、
LooseScan動作
LooseScanは、
以下は実行計画の例です。
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_
まとめ
今回はセミジョインについて紹介しました。この最適化された処理はクエリの性能向上につながりますが、