半結合を使用してサブクエリを最適化し、クエリの数を減らし、クエリのパフォーマンスを向上させることができます。 このトピックでは、半結合に関する基本情報と、並列クエリで半結合を使用する方法について説明します。
前提条件
お使いのクラスターはPolarDB for MySQL 8.0で、リビジョンバージョンは次の要件のいずれかを満たしてい。
8.0.1.0.5以降。
8.0.2.2.7以降。
クラスターのバージョンを表示する方法については、「エンジンバージョン」トピックのエンジンバージョンの照会セクションを参照してください。
背景情報
MySQL 5.6.5以降のバージョンは半結合をサポートしています。 外部テーブルの行が内部テーブルの少なくとも1つの行と一致する場合、半結合は外部テーブルから行を返します。 外部テーブルの行が内部テーブルの複数の行と一致する場合、半結合はその行を1回だけ返します。 サブクエリを含み、半結合を使用しないクエリの場合、外部テーブルの条件に一致するタプルごとにサブクエリが実行されます。 これは、クエリ効率を低下させる。 半結合を使用すると、サブクエリの数を減らし、クエリのパフォーマンスを向上させることができます。 半結合が使用される場合、サブクエリは結合に変換され、内部テーブルは外部クエリに引き出されます。 このように、内側と外側のテーブルは平行になっています。 システムが内部テーブルと外部テーブルの間でタプルの一致を見つけた後、システムは結果を返します。 これにより、クエリ効率が大幅に向上します。
戦略
以下の半結合戦略が使用されます。
DuplicateWeedout戦略
DuplicateWeedout戦略は、一時テーブルを作成し、テーブル内の一意の行IDを使用して重複の発生を回避することで機能します。
マテリアライゼーション戦略
マテリアライゼーション戦略では、
ネストされたテーブルをインデックス付きの一時テーブルにマテリアライズし、その一時テーブルを使用して結合を実行します。 インデックスは重複を削除するために使用され、システムが一時テーブルを外部テーブルに結合するときに後でルックアップに使用することもできます。
ファーストマッチ戦略
FirstMatch戦略は、サブクエリを実行し、最初の一致が見つかるとすぐにその実行を短縮して、重複の生成を回避することによって機能します。
LooseScan戦略
LooseScan戦略は、インデックスに基づいて内部テーブルのデータをグループ化し、サブクエリの各値グループから1つのレコードを選択し、レコードを外部テーブルと結合して、重複のないクエリ結果を取得することで機能します。
構文
ほとんどの場合、semijoinは結合条件としてInまたはEXISTS句を使用します。
IN
SELECT * FROM Employee WHERE DeptName IN ( SELECT DeptName FROM Dept )EXISTS
SELECT * FROM Employee WHERE EXISTS ( SELECT 1 FROM Dept WHERE Employee.DeptName = Dept.DeptName )
半結合を並列に実行してパフォーマンスを向上させる
PolarDBはすべての半結合戦略をサポートし、半結合戦略を使用する並列クエリを高速化します。 このようにして、各半結合タスクは複数のサブタスクに分割され、マルチスレッドモデルを使用してサブタスクを並列に実行します。 PolarDB 8.0.2.2.7以降では、半結合具体化のマルチフェーズ並列クエリがサポートされています。 これにより、半結合のクエリ性能がさらに向上する。 次の例ではQ20を使用します。
SELECT
s_name、
s_address
から
サプライヤー,
国家
どこ
s_suppkey IN
(
SELECT
ps_suppkey
FROM
partsupp
WHERE
ps_partkey IN
(
SELECT
p_partkey
FROM
部分
WHERE
p_name LIKE '[カラー] %'
)
そしてps_availqty > (
SELECT
0.5 * SUM(l_quantity)
FROM
lineitem
WHERE
l_partkey = ps_partkey
AND l_suppkey = ps_suppkey
AND l_shipdate >= date('[DATE]')
AND l_shipdate < date('[DATE]') + interval '1' year)
)
AND s_nationkey = n_nationkey
AND n_name = '[NATION]'
注文によって
s_name; この例では、サブクエリと外部クエリの両方が、DOPが32の並列クエリです。 サブクエリは最初に具体化されたテーブルを並列に生成し、次に外部クエリが並列に処理されます。 これは、CPUの処理能力を最大限に活用し、並列クエリ機能を最大化します。 この例では、スケールが100 GBの標準的なTPC-Hホットデータシナリオでエラスティックパラレルクエリ機能を有効にした後のマルチフェーズパラレルクエリ機能を示します。
この例では、TPC-Hベンチマークに基づくテストが実装されていますが、TPC-Hベンチマークテストのすべての要件を満たしているわけではありません。 そのため、テスト結果は TPC-H のベンチマークテストの公開結果と一致しない可能性があります。
次のエラスティック並列クエリ実行プランが使用されます。
-> 並べ替え: <temporary>.s_name (コスト=5014616.15行=100942)
-> ストリーム結果
-> ネストされたループ内部結合 (コスト=127689.96行=100942)
-> 収集 (スライス: 2; ワーカー: 64; ノード: 2) (コスト=6187.68行=100928)
-> ネストされたループ内部結合 (コスト=1052.43行=1577)
-> フィルター :( nation.N_NAME = 'KENYA) (コスト=2.29行=3)
-> 国のテーブルスキャン (コスト=2.29行=25)
-> SUPPLIER_FK1 (S_NATIONKEY=nation.N_NATIONKEY) 、インデックス条件 :( supplier.S_SUPPKEYがnullではない) 、並列パーティション: 863 (コスト=381.79行=631) を使用したサプライヤでの並列インデックス検索
-> <auto_distinct_key> を使用した <subquery2> の単一行インデックス検索 (ps_suppkey=supplier.S_SUPPKEY)
-> 重複排除によるマテリアライズ
-> 収集 (スライス: 1; ワーカー: 64; ノード: 2) (コスト=487376.70行=8142336)
-> ネストされたループ内部結合 (コスト=73888.70行=127224)
-> フィルター :( part.P_NAME like 'lime % ') (コスト=31271.54行=33159)
-> 並列パーティションを使用した部分の並列テーブルスキャン: 6244 (コスト=31271.54行=298459)
-> フィルター :( partsupp.PS_AVAILQTY > (select #4)) (コスト=0.94行=4)
-> PRIMARYを使用したpartsuppのインデックス検索 (PS_PARTKEY=part.P_PARTKEY) (コスト=0.94行=4)
-> 選択 #4 (条件内のサブクエリ; 依存)
-> 集計: sum(lineitem.L_QUANTITY)
-> フィルター :( (lineitem.L_SHIPDATE >= DATE'1994-01-01 ') および (lineitem.L_SHIPDATE < < <cache>((DATE'1994-01-01' + 間隔「1」年)))) (コスト=4.05行=1)
-> LINEITEM_FK2 (L_PARTKEY=partsupp.PS_PARTKEY、L_SUPPKEY=partsupp.PS_SUPPKEY) (コスト=4.05行=7) マルチノードelastic parallelクエリが有効になっている場合の実行時間は次のとおりです。
結果は、実行時間が43.52秒から2.29秒に短縮され、クエリ速度が19倍速くなったことを示しています。