MySqlサブクエリINの実装と最適化

IN が遅いのはなぜですか?

アプリケーションでサブクエリを使用すると、SQL ステートメントのクエリ パフォーマンスが非常に悪くなります。例えば：

SELECT driver_id FROM driver where driver_id in (SELECT driver_id FROM driver where _create_date > '2016-07-25 00:00:00');

独立したサブクエリは、条件を満たす driver_id を返します。この問題は解決されましたが、6 秒かかります。EXPLAIN を通じて SQL ステートメントの実行プランを表示できます。

上記の SQL ステートメントが相関サブクエリになることがわかります。EXPLAIN EXTENDED コマンドと SHOW WARNINGS コマンドを実行すると、次の結果が表示されます。

MySql オプティマイザが IN 句を EXISTS の相関サブクエリに直接変換していることがわかります。相関 IN サブクエリを次に示します。

SELECT driver_id FROM driver where driver_id in (SELECT driver_id FROM user where user.uid = driver.driver_id);

SQL ステートメントの実行プランを表示します。

これは相関サブクエリです。EXPLAIN EXTENDED コマンドと SHOW WARNINGS コマンドを実行すると、次の結果が表示されます。

MySQL 5.5 より前のオプティマイザは、独立サブクエリか相関サブクエリかに関係なく、IN を EXISTS ステートメントに変換していることがわかります。サブクエリと外部クエリがそれぞれ M 行と N 行を返す場合、サブクエリは O(N+M) ではなく O(N+N*M) でスキャンされます。これがINが遅い理由です。

INとEXISTSのどちらが速いでしょうか?

Baidu は、IN と EXISTS が同等に効率的であると言うのは間違いであると主張する多くの記事をオンラインで発見しました。

クエリ対象の 2 つのテーブルのサイズが同じであれば、in と exists の使用にほとんど違いはありません。
2 つのテーブルのうち 1 つが小さく、もう 1 つが大きい場合、大きい方のサブクエリ テーブルには が使用され、小さい方のサブクエリ テーブルには が使用されます。
例: テーブル A (小さいテーブル)、テーブル B (大きいテーブル)
1:
select * from A where cc in (select cc from B) は、テーブル A の cc 列のインデックスを使用するため、非効率的です。
select * from A where exists(select cc from B where cc=A.cc) は、テーブル B の cc 列のインデックスを使用するため効率的です。

逆に

2:
select * from B where cc in (select cc from A) は、テーブル B の cc 列のインデックスを使用するため効率的です。
select * from B where exists(select cc from A where cc=B.cc) は、テーブル A の cc 列のインデックスを使用するため、非効率的です。

上記の説明をまとめると、主な理由はインデックスの使用にあると個人的には思います。いずれにしても、大きなテーブルのインデックスを使用すれば、効率を向上できます。

しかし、この記事を編集する際に何度もテストしましたが、上記にまとめた結果は得られませんでした。以下はテスト SQL ステートメントです。まず、外部テーブルは大きなテーブルで、内部テーブルは小さなテーブルです。 （例１）

SELECT count(driver_id) FROM driver where driver_id in (SELECT uid FROM user);
SELECT count(driver_id) FROM driver where exists (SELECT 1 FROM user where uid = driver.driver_id);

実行結果は次のとおりです。

すると外側は小さなテーブル、内側は大きなテーブルになります。 （例2）

user から count(uid) を選択します。uid は (SELECT driver_id FROM driver) にあります。
存在するユーザーから count(uid) を選択します (driver.driver_id = user.uid の場合、SELECT 1 FROM driver)。

実行結果は次のとおりです。

いずれの場合でも、IN と EXISTS の実行効率はまったく同じであることがわかります。これに基づいて、例 1 の最初と 2 番目の SQL ステートメントの実行プランを次のように確認し続けます。

IN と EXISTS の実行プランは同じであることがわかり、このことから、2 つの実行効率は同じになるはずだという結論が導き出されます。

「MySql Technology Insider: SQL プログラミング」: この本では、多くの DBA が EXISTS の方が IN よりも効率的であると考えていることが説明されています。当時のオプティマイザーがあまり安定していなかったか、十分に優れていなかった可能性があります。ただし、ほとんどの場合、IN と EXISTS の実行プランは同じです。

効率を向上させるにはどうすればいいでしょうか?

上記の例 2 の SQL ステートメントは、実行に約 8 秒かかります。クエリが遅いのは M*N の存在によるものですが、最適化することは可能です。遅い理由は、内部クエリを外部クエリと比較するたびに、テーブルを 1 回トラバースする必要があるためです。別の方法として、サブクエリのレイヤーをネストして、複数のトラバース操作を回避することもできます。ステートメントは次のとおりです。

SELECT count(driver_id) FROM driver where exists (SELECT uid FROM (SELECT uid from user) as b where b.uid = driver.driver_id);

実行効果は以下のようになります。

最適化により実行時間が 6 秒以上短縮されることがわかります。以下は SQL 実行プランです。

これは依然として相関サブクエリですが、内部のトラバーサル クエリ操作は削減されます。したがって、事前クエリを使用すると、トラバーサル操作を減らし、効率を向上させることができます。

実際、実際のプログラミングでは、多くの開発者は結合テーブルクエリを使用せず、まず 1 つのテーブルからデータを取得してから、別のテーブルで WHEREIN 操作を実行することを選択します。原理は、上記の SQL ステートメントで実装されているものと同じです。

MySQL 5.6 はサブクエリを最適化しますか?

セミジョイン戦略

オプティマイザーは、IN ステートメントがリージョン テーブルから各リージョン キーのインスタンスを 1 つ返すサブクエリを必要とすることを認識します。これにより、MySQL は SELECT ステートメントを半結合方式で実行するため、レコードに一致するグローバル テーブルの各リージョンのインスタンスは 1 つだけになります。

準結合と通常の結合には、2 つの非常に重要な違いがあります。

• セミ結合では、内部テーブルの結果は重複しません。
• この操作では、内部テーブル内のフィールドは結果に追加されません。

したがって、セミ結合の結果は、多くの場合、外部テーブルのレコードのサブセットになります。有効性の観点から見ると、セミ結合の最適化は、内部テーブルから重複項目を効果的に排除することです。MySQL は、重複項目を排除するために 4 つの異なるセミ結合実行戦略を適用します。

テーブルプルアウトの最適化

サブクエリを結合に変換するか、テーブル プルアウトを使用して、サブクエリ テーブルと外部テーブル間の内部結合としてクエリを実行します。テーブル プルアウトは、サブクエリから外部クエリにテーブルをプルします。サブクエリを結合に変換するか、テーブル プルアウトを使用して、サブクエリ テーブルと外部テーブル間の内部結合としてクエリを実行します。テーブル プルアウトは、外部クエリのサブクエリからテーブルを抽出します。

場合によっては、サブクエリを JOIN として書き換えることもできます。次に例を示します。

SELECT OrderID FROM Orders where EmployeeID IN (select EmployeeID from Employees where EmployeeID > 3);

OrderID が一意であること、つまり主キーまたは一意のインデックスであることがわかっている場合、SQL ステートメントは Join として書き換えられます。

SELECT OrderID FROM Orders join Employees where Orders.EmployeeID = Employees.EmployeeID and Employees.EmployeeID > 3;

テーブル プルアウトの目的は、一意のインデックスに基づいてサブクエリを JOIN ステートメントに書き換えることです。MySQL 5.5 では、上記の SQL ステートメントの実行プランは次のようになります。

EXPLAIN EXTENDED コマンドと SHOW WARNINGS コマンドを使用すると、次の結果が表示されます。

まさに上記のように遅いのはなぜでしょうか?

MySQL 5.6 では、オプティマイザは SQL ステートメントを書き換えて、次の実行プランを取得します。

MySQL 5.6 では、オプティマイザは独立したサブクエリを相関サブクエリに書き換えません。オプティマイザの実行モードは、EXPLAIN EXTENDED コマンドと SHOW WARNINGS コマンドを通じて取得されます。

明らかに、オプティマイザーは上記のサブクエリを JOIN ステートメントに書き換えます。これがテーブル プルアウトの最適化です。

重複排除の最適化

セミ結合を結合と同様に実行し、一時テーブルを使用して重複レコードを削除します。

上記の内部テーブルにある列は一意であるため、オプティマイザーはサブクエリを JOIN ステートメントに書き換えて、SQL 実行の効率を向上させます。重複排除最適化とは、外部クエリ条件の列が一意であり、MySql オプティマイザーが最初にサブクエリによって見つかった結果を重複排除することを意味します。たとえば、次の SQL ステートメント:

SELECT ContactName FROM Customers where CustomerID in (select CustomerID from Orders where OrderID > 10000 and Customers.Country = Orders.ShipCountry);

CustomerID は主キーであるため、サブクエリから取得された結果は重複排除される必要があります。 MySql 5.6 の実行プラン:

「追加」オプションの「一時を開始」は重複排除用の一時テーブルを作成することを意味し、「一時を終了」は一時テーブルを削除することを意味します。 EXPLAIN EXTENDED コマンドと SHOW WARNINGS コマンドを使用すると、オプティマイザの実行モードは次のように取得されます。

Table Pullout 最適化とは異なり、join ではなく semi join が表示されます。これは、重複排除作業が増えるためです。上記の実行プランの場合、スキャンコストは約 830+830*1=1660 回になります。
MySql 5.5 の実行プランは次のとおりです。

ご覧のとおり、MySql 5.5 では、ステートメントは依然として相関サブクエリに変換され、スキャン コストは約 93+93*9=930 回になります。

最適化後、MySql 5.6 のスキャン コストは 5.5 よりも高いことがわかります。実際、これは 2 つのテーブルが小さい場合の結果です。テーブルが非常に大きい場合は、最適化の効果が非常に明白になります。

マテリアライゼーションの最適化

サブクエリをインデックス付きの一時テーブルにマテリアライズし、一時テーブルを使用して結合を実行します。インデックスは重複を削除するために使用されます。インデックスは、後で一時テーブルを外部テーブルに結合するときに検索に使用される場合もあります。そうでない場合は、テーブルがスキャンされます。

上記のサブクエリは相関サブクエリです。サブクエリが独立サブクエリの場合、オプティマイザーは、図に示すように、独立サブクエリの結果を単一のマテリアライズド一時テーブルに書き込むことを選択できます。

JOIN の順序に応じて、マテリアライゼーションの最適化は次のように分けられます。

• マテリアライゼーション スキャン: JOIN は、マテリアライズされた一時テーブルをテーブルに関連付けます。
• マテリアライゼーション検索: JOIN は、テーブルをマテリアライズされた一時テーブルに関連付けます。

次のサブクエリは、マテリアライゼーションを使用して最適化できます。

SELECT OrderID FROM Orders where OrderID in (select OrderID from `Order Details` where UnitPrice < 50 );

SQL ステートメントの実行プラン:

JOIN を実行するとき (つまり、ID 1 のステップ)、最初にスキャンされるテーブルは Orders であり、次に subquery2 であることがわかります。これは、マテリアライゼーション ルックアップの最適化です。次の SQL の場合:

select * FROM driver where driver_id in (select uid from user);

SQL ステートメントの実行プラン:

最初にサブクエリ 2 がスキャンされ、次にドライバー テーブルがスキャンされます。これがマテリアライゼーション スキャンの最適化です。

FirstMacth 最適化

内部テーブルをスキャンして行の組み合わせを探すときに、特定の値グループのインスタンスが複数ある場合は、すべてを返すのではなく、1 つを選択します。これにより、スキャンが「ショートカット」され、不要な行が生成されなくなります。これにより、テーブル スキャンの早期終了メカニズムが提供され、不要なレコードの生成が排除されます。

セミ結合の FirstMatch 戦略がサブクエリを実行する方法は、以前の MySQL バージョンの IN-TO-EXISTS と非常によく似ています。外部テーブル内の一致するレコードごとに、MySQL は内部テーブル内の一致をチェックします。一致が見つかった場合、外部テーブルからレコードを返します。一致するものが見つからない場合にのみ、エンジンはフォールバックして内部テーブル全体をスキャンします。

LooseScan の最適化

各サブクエリの値グループから単一の値を選択できるようにするインデックスを使用して、サブクエリ テーブルをスキャンします。

セミジョイン変数

Duplicate Weedout を除くこれらの各戦略は、optimizer_switch システム変数を使用して有効または無効にできます。semijoin フラグは、準結合が使用されるかどうかを制御します。これがオンに設定されている場合、firstmatch、looscan、および materialization フラグにより​​、許可された準結合戦略をより細かく制御できます。これらのフラグはデフォルトでオンになっています。Duplicate Weedout を除く各戦略は、optimizer_switch システム変数を使用して有効または無効にできます。semijoin フラグは、準結合の最適化が有効かどうかを制御します。これがオンに設定されている場合、他の戦略でも独立した変数制御が可能になります。 5.6 ではすべての変数がデフォルトで有効になっています。

mysql> @@optimizer_switch\G を選択します。
************************** 1. 行 ****************************
@@optimizer_switch: index_merge=on、index_merge_union=on、index_merge_sort_union=on、index_merge_intersection=on、engine_condition_pushdown=on、index_condition_pushdown=on、mrr=on、mrr_cost_based=on、block_nested_loop=on、batched_key_access=off、materialization=on、semijoin=on、loosescan=on、firstmatch=on、duplicateweedout=on、subquery_materialization_cost_based=on、use_index_extensions=on、condition_fanout_filter=on、derived_merge=on
セット内の 1 行 (0.00 秒)

EXPLAINビュー戦略

• 半結合テーブルは外部選択に表示されます。EXPLAIN EXTENDED と SHOW WARNINGS は、半結合構造を表示する書き換えられたクエリを表示します。これにより、半結合からどのテーブルが取り出されたかを把握できます。サブクエリが半結合に変換された場合、サブクエリ述語がなくなり、そのテーブルと WHERE 句が外部クエリ結合リストと WHERE 句にマージされたことがわかります。
• 重複除去のための一時テーブルの使用は、Extra 列の Start temporary と End temporary で示されます。抽出されず、Start temporary と End temporary でカバーされる EXPLAIN 出力行の範囲内にあるテーブルには、一時テーブルに rowid が設定されます。
• 追加列(列)のFirstMatch(tbl_name)は結合のショートカットを示します。
• Extra 列の LooseScan(m..n) は、LooseScan 戦略の使用を示します。m と n はキー部品番号です。
• MySQL 5.6.7 以降、マテリアライゼーション用の一時テーブルの使用は、select_type 値が MATERIALIZED の行とテーブル値が の行によって示されます。
• MySQL 5.6.7 より前では、マテリアライズ用の一時テーブルの使用は、単一のテーブルが使用されている場合は Extra 列の Materialize によって示され、複数のテーブルが使用されている場合は Start materialize と End materialize によって示されました。Scan が存在する場合、テーブル読み取りに一時テーブル インデックスは使用されません。それ以外の場合は、インデックス検索が使用されます。
  

上記の紹介では、FirstMacth 最適化と LooseScan 最適化の具体的な効果を示す良い例はありません。コミュニケーションと学習の機会があります。

参照する

「MySql テクノロジー インサイダー: SQL プログラミング」

http://dev.mysql.com/doc/refman/5.6/en/サブクエリ最適化.html

http://tech.it168.com/a2013/0506/1479/000001479749.shtml

これで、MySql サブクエリ IN の実行と最適化に関するこの記事は終了です。MySql サブクエリ IN に関するその他の関連コンテンツについては、123WORDPRESS.COM で以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。