サブクエリ関連の最適化
この記事では主にサブクエリ関連の最適化について紹介します。
サブクエリは通常、次の状況で使用されます。
NOT IN (SELECT ... FROM ...)NOT EXISTS (SELECT ... FROM ...)IN (SELECT ... FROM ..)EXISTS (SELECT ... FROM ...)... >/>=/</<=/=/!= (SELECT ... FROM ...)
サブクエリには、 select * from t where t.a in (select * from t2 where t.b=t2.b)などの非サブクエリ列が含まれる場合があります。サブクエリのt.b列はサブクエリに属さず、サブクエリの外部から導入されます。この種のサブクエリは通常「相関サブクエリ」と呼ばれ、外部から導入された列は「相関列」と呼ばれます。相関サブクエリの最適化については、 相関サブクエリの相関解除を参照してください。この記事では、相関列を含まないサブクエリに焦点を当てます。
デフォルトでは、サブクエリは実行方法としてTiDB 実行計画を理解するで説明したsemi joinを使用します。一部の特殊なサブクエリでは、TiDB はパフォーマンスを向上させるために論理的な書き換えを行います。
... < ALL (SELECT ... FROM ...)または... > ANY (SELECT ... FROM ...)
この場合、 ALLとANY MAXとMINに置き換えることができます。テーブルが空の場合、 MAX(EXPR)とMIN(EXPR)の結果は NULL になります。 EXPRの結果にNULL含まれる場合も同様に機能します。 EXPRの結果にNULLが含まれるかどうかは、式の最終結果に影響を与える可能性があるため、完全な書き換えは次の形式で行われます。
t.id < all (select s.id from s)はt.id < min(s.id) and if(sum(s.id is null) != 0, null, true)に書き換えられますt.id < any (select s.id from s)はt.id < max(s.id) or if(sum(s.id is null) != 0, null, false)に書き換えられます
... != ANY (SELECT ... FROM ...)
この場合、サブクエリの値がすべて異なる場合、クエリとそれらの値を比較するだけで十分です。サブクエリ内の異なる値の数が複数ある場合は、不等号が存在する必要があります。したがって、そのようなサブクエリは次のように書き換えることができます。
select * from t where t.id != any (select s.id from s)はselect t.* from t, (select s.id, count(distinct s.id) as cnt_distinct from s) where (t.id != s.id or cnt_distinct > 1)に書き換えられます
... = ALL (SELECT ... FROM ...)
この場合、サブクエリ内の異なる値の数が複数である場合、この式の結果は false でなければなりません。したがって、このようなサブクエリは TiDB で次の形式に書き換えられます。
select * from t where t.id = all (select s.id from s)はselect t.* from t, (select s.id, count(distinct s.id) as cnt_distinct from s ) where (t.id = s.id and cnt_distinct <= 1)に書き換えられます
... IN (SELECT ... FROM ...)
この場合、サブクエリINはSELECT ... FROM ... GROUP ...に書き換えられ、さらに通常の形式のJOINに書き換えられます。
たとえば、 select * from t1 where t1.a in (select t2.a from t2)はselect t1.* from t1, (select distinct(a) a from t2) t2 where t1.a = t2. The form of aに書き換えられます。ここでのDISTINCT属性は、 t2.aにUNIQUE属性があれば自動的に削除できます。
explain select * from t1 where t1.a in (select t2.a from t2);
+------------------------------+---------+-----------+------------------------+----------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------+---------+-----------+------------------------+----------------------------------------------------------------------------+
| IndexJoin_12 | 9990.00 | root | | inner join, inner:TableReader_11, outer key:test.t2.a, inner key:test.t1.a |
| ├─HashAgg_21(Build) | 7992.00 | root | | group by:test.t2.a, funcs:firstrow(test.t2.a)->test.t2.a |
| │ └─IndexReader_28 | 9990.00 | root | | index:IndexFullScan_27 |
| │ └─IndexFullScan_27 | 9990.00 | cop[tikv] | table:t2, index:idx(a) | keep order:false, stats:pseudo |
| └─TableReader_11(Probe) | 7992.00 | root | | data:TableRangeScan_10 |
| └─TableRangeScan_10 | 7992.00 | cop[tikv] | table:t1 | range: decided by [test.t2.a], keep order:false, stats:pseudo |
+------------------------------+---------+-----------+------------------------+----------------------------------------------------------------------------+
この書き換えは、サブクエリINが比較的小さく、外部クエリが比較的大きい場合にパフォーマンスが向上します。これは、書き換えなしではindex joinと t2 を駆動テーブルとして使用することが不可能であるためです。ただし、リライト中に集計を自動的に削除できず、 t2が比較的大きい場合、このリライトがクエリのパフォーマンスに影響を与えるという欠点があります。現在、変数tidb_opt_insubq_to_join_and_aggはこの最適化を制御するために使用されます。この最適化が適切でない場合は、手動で無効にすることができます。
EXISTSサブクエリと... >/>=/</<=/=/!= (SELECT ... FROM ...)
現時点では、このようなシナリオのサブクエリの場合、サブクエリが相関サブクエリでない場合、TiDB は最適化段階で事前にサブクエリを評価し、結果セットに直接置き換えます。下図のように、 EXISTSサブクエリはあらかじめ最適化段階でTRUEと評価されているため、最終的な実行結果には反映されません。
create table t1(a int);
create table t2(a int);
insert into t2 values(1);
explain select * from t1 where exists (select * from t2);
+------------------------+----------+-----------+---------------+--------------------------------+
| id | estRows | task | access object | operator info |
+------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_12 | 10000.00 | root | | data:TableFullScan_11 |
| └─TableFullScan_11 | 10000.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+------------------------+----------+-----------+---------------+--------------------------------+
前述の最適化では、オプティマイザーはステートメントの実行を自動的に最適化します。さらに、 SEMI_JOIN_REWRITEヒントを追加してステートメントをさらに書き直すこともできます。
このヒントを使用してクエリを書き換えない場合、実行プランでハッシュ結合が選択されている場合、セミ結合クエリはサブクエリを使用してハッシュ テーブルを構築することしかできません。この場合、サブクエリの結果が外側のクエリの結果よりも大きい場合、実行速度が予想より遅くなる可能性があります。
同様に、実行プランでインデックス結合が選択されている場合、準結合クエリは駆動テーブルとして外部クエリのみを使用できます。この場合、サブクエリの結果が外側のクエリの結果よりも小さい場合、実行速度が予想より遅くなる可能性があります。
SEMI_JOIN_REWRITE()を使用してクエリを書き換えると、オプティマイザは選択範囲を拡張して、より適切な実行プランを選択できます。