セミジョインの概要と使用方法 - PolarDB - Alibaba Cloud ドキュメントセンター

セミジョインを使用すると、サブクエリを最適化し、クエリの実行回数を削減して、パフォーマンスを向上させることができます。このトピックでは、セミジョインの基礎とその使用方法について説明します。

前提条件

PolarDB クラスターは、以下のいずれかのリビジョンバージョンを実行している PolarDB for MySQL 8.0 クラスターである必要があります。

8.0.1.0.5 以降。
8.0.2.2.7 以降。

クラスターのバージョンを確認するには、「エンジンバージョンの照会」をご参照ください。

背景情報

MySQL 5.6.5 では、セミジョイン最適化が導入されました。セミジョインは、外部テーブルの行に対して内部テーブル内で一致するものが見つかった場合、外部テーブルから行を返します。内部テーブルに複数の一致がある場合でも、外部テーブルの行は一度だけ返されます。これは、外部テーブルの該当する行ごとにサブクエリを再実行する可能性がある標準的なサブクエリよりも効率的です。セミジョインは、サブクエリを結合に変換してパフォーマンスを向上させます。これにより、オプティマイザーは内部テーブルと外部テーブルを一緒に処理できるようになり、クエリの実行時間が大幅に短縮されます。

戦略

セミジョインは、以下のいずれかの戦略を使用して実装されます。

DuplicateWeedout 戦略

この戦略は、行 ID に基づいて一意の ID を持つ一時テーブルを作成し、重複を排除します。

explain select * from t1 where a in (select a from t11);
id  select_type  table  partitions  type  possible_keys  key  key_len  ref  rows  filtered  Extra
1 SIMPLE  t11  NULL  ALL  NULL  NULL  NULL  0  0.00  Start temporary
1 SIMPLE  t1  NULL  ALL  NULL  NULL  NULL  3  33.33  Using where; End temporary; Using join buffer (hash join)
Warnings:
Note  1003  /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`  from `test`.`t1` semi join (`test`.`t11`) where (`test`.`t1`.`a` = `test`.`t11`.`a`)

マテリアライゼーション戦略

この戦略は、サブクエリの nested tables を一時テーブルにマテリアライズします。その後、オプティマイザーは、外部テーブルと結合する際に、このマテリアライズされたテーブルを検索またはスキャンして重複を排除します。

explain select * from t1 where a in (select a from t11);
id  select_type table partitions  type  possible_keys key key_len ref rows  filtered  Extra
1 SIMPLE  <subquery2> NULL  ALL NULL  NULL  NULL  NULL 0.00  NULL
1 SIMPLE  t1  NULL  ALL NULL  NULL  NULL  NULL  3 33.33 Using where; Using join buffer (hash join)
2 MATERIALIZED  t11 NULL  ALL NULL  NULL  NULL  NULL  0 0.00  NULL
Warnings:
Note  1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t11`) where (`test`.`t1`.`a` = `<subquery2>`.`a`)

FirstMatch 戦略

この戦略は、順次スキャンを実行します。最初に一致する行が見つかった後、すぐに外部テーブルの次の行に進み、これにより重複を排除します。

EXPLAIN ステートメントを実行してクエリプランを表示すると、Extra 列の FirstMatch(t1) は、オプティマイザーが FirstMatch セミジョイン戦略を選択したことを示します。

explain select * from t1 where a in (select a from t11);
id  select_type  table  partitions  type  possible_keys  key  key_len  ref  rows  filtered  Extra
1 SIMPLE  t1  NULL  ALL NULL  NULL  NULL  NULL  3 100.00  NULL
1 SIMPLE  t11  NULL  ALL NULL  NULL  NULL  NULL  0 0.00  Using where; FirstMatch(t1); Using join buffer (hash join)
Warnings:
Note  1003  /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t11`) where (`test`.`t11`.`a` = `test`.`t1`.`a`)

LooseScan 戦略

この戦略は、インデックスに基づいて内部テーブルをグループ化します。その後、各グループを外部テーブルと結合して、一致する結合条件を見つけます。一致が見つかった場合、クエリは外部テーブルから行を返し、スキャンは内部テーブルの次のグループに進みます。この方法で、重複する内部テーブルの行の処理を回避します。

EXPLAIN の出力において、テーブル t3 の Extra 列の Using index; LooseScan は、サブクエリが LooseScan 戦略を使用してセミジョインに最適化されたことを示します。
```
explain select count(a) from t2 where a in ( SELECT  a FROM t3);
id  select_type table partitions  type  possible_keys key key_len ref rows   filtered  Extra
1 SIMPLE  t3  NULL  index  a a 5 NULL  30000 3.33  Using where; Using index; LooseScan
1 SIMPLE  t2  NULL  ref  a a 5 test.t3.a 1 100.00  Using index
Warnings:
Note  1003 /* select#1 */ select count(`test`.`t2`.`a`) AS `count(a)` from `test`.`t2` semi join (`test`.`t3`) where (`test`.`t2`.`a` = `test`.`t3`.`a`)
```

構文

IN または EXISTS サブクエリは、通常、セミジョインをトリガーします。

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 
FROM
   supplier,
   nation 
WHERE
   s_suppkey IN 
   (
      SELECT
         ps_suppkey 
      FROM
         partsupp 
      WHERE
         ps_partkey IN 
         (
            SELECT
               p_partkey 
            FROM
               part 
            WHERE
               p_name LIKE '[COLOR]%' 
         )
         AND 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]' 
ORDER BY
   s_name;

この例では、サブクエリと外部クエリの両方が、並列度 (DOP) 32 で並列実行されます。サブクエリは最初に並列でマテリアライズされたテーブルを生成します。その後、外部クエリも並列で実行されます。このアプローチでは、CPU 処理能力を最大限に活用して、クエリの並列処理を最大化します。以下に、100 GB スケールの TPC-H データセットを使用したホットデータシナリオにおけるマルチフェーズ並列処理機能を示します。

説明

この例では、TPC-Hベンチマークに基づくテストが実装されていますが、TPC-Hベンチマークテストのすべての要件を満たしているわけではありません。そのため、テスト結果は TPC-H のベンチマークテストの公開結果と一致しない可能性があります。

並列クエリプランは以下の通りです。

 -> Sort: <temporary>.s_name  (cost=5014616.15 rows=100942)
    -> Stream results
        -> Nested loop inner join  (cost=127689.96 rows=100942)
            -> Gather (slice: 2; workers: 64; nodes: 2)  (cost=6187.68 rows=100928)
                -> Nested loop inner join  (cost=1052.43 rows=1577)
                    -> Filter: (nation.N_NAME = 'KENYA')  (cost=2.29 rows=3)
                        -> Table scan on nation  (cost=2.29 rows=25)
                    -> Parallel index lookup on supplier using SUPPLIER_FK1 (S_NATIONKEY=nation.N_NATIONKEY), with index condition: (supplier.S_SUPPKEY is not null), with parallel partitions: 863  (cost=381.79 rows=631)
            -> Single-row index lookup on <subquery2> using <auto_distinct_key> (ps_suppkey=supplier.S_SUPPKEY)
                -> Materialize with deduplication
                    -> Gather (slice: 1; workers: 64; nodes: 2)  (cost=487376.70 rows=8142336)
                        -> Nested loop inner join  (cost=73888.70 rows=127224)
                            -> Filter: (part.P_NAME like 'lime%')  (cost=31271.54 rows=33159)
                                -> Parallel table scan on part, with parallel partitions: 6244  (cost=31271.54 rows=298459)
                            -> Filter: (partsupp.PS_AVAILQTY > (select #4))  (cost=0.94 rows=4)
                                -> Index lookup on partsupp using PRIMARY (PS_PARTKEY=part.P_PARTKEY)  (cost=0.94 rows=4)
                                -> Select #4 (subquery in condition; dependent)
                                    -> Aggregate: sum(lineitem.L_QUANTITY)
                                        -> Filter: ((lineitem.L_SHIPDATE >= DATE'1994-01-01') and (lineitem.L_SHIPDATE < <cache>((DATE'1994-01-01' + interval '1' year))))  (cost=4.05 rows=1)
                                            -> Index lookup on lineitem using LINEITEM_FK2 (L_PARTKEY=partsupp.PS_PARTKEY, L_SUPPKEY=partsupp.PS_SUPPKEY)  (cost=4.05 rows=7)

100 GB スケールの標準的な TPC-H ホットデータシナリオにおける直列実行時間は以下の通りです。

| Supplier#000999085 | egFwcBv5TkH                                  |
| Supplier#000999105 | 1CKYsKKIxqM                                  |
| Supplier#000999253 | q0nlouqFchhsbmkPq                            |
| Supplier#000999314 | 1MLMPBBnYSnMl1lRRjXiu2B2sxahjItRt0v          |
| Supplier#000999319 | hkc5LIrtAz9clk2Edz8ENngn4PdhcSD02YRxN        |
| Supplier#000999347 | L,CPr2clOoPg91gYxqCsie7DNf                   |
| Supplier#000999357 | tQW7OYPfNDzfqqzHQCx                          |
| Supplier#000999362 | X7 Rxrst808LeHI1sYlVIW5Usqu                  |
| Supplier#000999394 | TZn2ZOsZCxMmW09                              |
| Supplier#000999486 | SMiqFfRyUuXldJp                              |
| Supplier#000999684 | SwmVOJNeJwTdDJcE0                            |
| Supplier#000999814 | Tlh9Z1u5EPk1drhEbiTZpRHJJwTX3FwJoE           |
| Supplier#000999841 | 9e5iYCk2pntVLLKnP5YJ3xT2IY0I7gENyfqy        |
| Supplier#000999850 | XEzRaermdYPO5XX                              |
| Supplier#000999902 | D4XvfAYuocmiUFM1N,EScgAHQcF                  |
| Supplier#000999936 | GkUI05zvDkNpMPlE,AplBgF8PxfEhe               |
| Supplier#000999949 | bRcyGJoAryorYRUKGtYfNt4ZlgvC6vZ              |
| Supplier#000999956 | 5r fovH1Bwu087yF5L7YHitAZWtmK                |
| Supplier#000999969 | 0xHYbgscQREncmbZziaM3dxg51jA,PKhyrAQ         |
+--------------------+----------------------------------------------+
17978 rows in set (43.52 sec)

マルチノード並列実行を有効にした場合の実行時間は以下の通りです。

| Supplier#000999085 | egFwcBv5TkH                                |
| Supplier#000999105 | 1CKYsKKIxqM                                |
| Supplier#000999253 | q0nlouqFchhsbmkPq                          |
| Supplier#000999314 | 1MLMPBBnYSnMl1lRRjXiu2B2sxahjItRt0v        |
| Supplier#000999319 | hkc5LIrtAz9clk2Edz8ENngn4PdhcSD02YRxN      |
| Supplier#000999347 | L,CPr2cl0oPg91gYxqCsie7DNf                 |
| Supplier#000999357 | tQW7OYPfNDzfqqzHQCx                        |
| Supplier#000999362 | X7 Rxrst808LeHI1sYlVIW5Usqu                |
| Supplier#000999394 | TZn2ZOsZCxMmW09                            |
| Supplier#000999486 | SMiqFfRyUuXldJp                            |
| Supplier#000999684 | SwmVOJNeJwTdDJcE0                          |
| Supplier#000999814 | Tlh9Z1u5EPk1drhEbiTZpRHJJwTX3FwJoE         |
| Supplier#000999841 | 9e5iYCk2pntVLLKnP5YJ3xT2IY0I7gENyfqy      |
| Supplier#000999850 | XEzRaermdYPO5XX                            |
| Supplier#000999902 | D4XvfAYuocmiUFM1N,EScgAHQcF                |
| Supplier#000999936 | GkUI0SzvDkNpMPlE,AplBgF8PxfEhe             |
| Supplier#000999949 | bRcyGJoAryorYRUKGtYfNt4ZlgvC6vZ            |
| Supplier#000999956 | 5r fovH1Bwu087yF5L7YHitAZWtmK              |
| Supplier#000999969 | 0xHYbgscQREncmbZziaM3dxg51jA,PKhyrAQ       |
17978 rows in set (2.29 sec)

実行時間は 43.52 秒から 2.29 秒に短縮され、19 倍のパフォーマンス向上を実現しました。