MySQL道普請便り
第43回 MySQLの準結合(セミジョイン)について
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_
まとめ
今回はセミジョインについて紹介しました。この最適化された処理はクエリの性能向上につながりますが,
バックナンバー
MySQL道普請便り
- 第141回 LIMIT句の利用について
- 第140回 オンラインスキーママイグレーションツール gh-ostを使ってみよう[その3]
- 第139回 オンラインスキーママイグレーションツール gh-ostを使ってみよう[その2]
- 第138回 オンラインスキーママイグレーションツール gh-ostを使ってみよう[その1]
- 第137回 MySQLTunerを使ってチューニングの足がかりを見つけてみる
- 第136回 CHECK制約を利用してみよう
- 第135回 MySQL 8.0で追加されたoptimizer_switchのフラグについて
- 第134回 DDLと暗黙的なコミットについて
- 第133回 Partial Revokesによる権限の一部剥奪について
- 第132回 Internal Temporary Table(内部テンポラリテーブル)について[その2]
関連記事
- 第46回 MySQL Cluster 8.0.16 DMRリリースと開発者来日セミナー開催,PostgreSQLエンタープライズ・コンソーシアムが検証成果を公開
- 第43回 MySQL 8.0.15緊急リリースとLINEでの導入事例公開,PostgreSQL 11.2,Pgpool-II 4.0.3リリース
- 第42回 MySQL 8.0.14リリース,PostgreSQL商用ツールのPostgreSQL 11対応
- 第41回 MySQL& PostgreSQLの2018年の主なニュース
- 第38回 LOCONDO.jpやLINEでのMySQL最新導入事例,PostgreSQL 11ベータ4リリース,Cassandra(イベント情報のみ)