Ikhtisar dan penggunaan semi-join - PolarDB - Alibaba Cloud Documentation Center

Semi-join dapat digunakan untuk mengoptimalkan subkueri, mengurangi eksekusi kueri, dan meningkatkan performa. Topik ini mencakup dasar-dasar semi-join serta cara penggunaannya.

Prasyarat

Kluster PolarDB Anda harus berupa kluster PolarDB for MySQL 8.0 yang menjalankan salah satu versi revisi berikut:

8.0.1.0.5 atau yang lebih baru.
8.0.2.2.7 atau yang lebih baru.

Untuk memeriksa versi kluster Anda, lihat Kueri versi engine.

Latar Belakang

MySQL 5.6.5 memperkenalkan optimasi semi-join. Ketika semi-join menemukan kecocokan antara baris pada tabel luar dan tabel dalam, baris dari tabel luar dikembalikan—meskipun terdapat beberapa kecocokan di tabel dalam, baris tersebut hanya dikembalikan sekali. Pendekatan ini lebih efisien dibandingkan subkueri standar, yang mungkin mengeksekusi ulang subkueri untuk setiap baris yang memenuhi syarat di tabel luar. Semi-join meningkatkan performa dengan mengonversi subkueri menjadi join, sehingga pengoptimal dapat memproses tabel dalam dan luar secara bersamaan dan mengurangi waktu eksekusi kueri secara signifikan.

Strategi

Semi-join diimplementasikan menggunakan salah satu strategi berikut:

Strategi DuplicateWeedout

Strategi ini membuat tabel sementara dengan ID unik berdasarkan row id untuk menghilangkan duplikat.

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`)

Strategi Materialization

Strategi ini melakukan materialisasi nested tables dari subkueri ke dalam tabel sementara. Pengoptimal kemudian menghilangkan duplikat dengan melakukan lookup atau pemindaian pada tabel materialisasi saat melakukan join dengan tabel luar.

explain select * from t1 where a in (select a from t11);
id  jenis_pilihan tabel       partisi     jenis   kunci_mungkin   kunci   panjang_kunci   ref   baris   difilter    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 Menggunakan where; Menggunakan join buffer (hash join)
2 MATERIALIZED  t11 NULL  ALL NULL  NULL  NULL  NULL  0 0.00  NULL
Peringatan:
Catatan  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`)

Strategi Firstmatch

Strategi ini melakukan pemindaian sekuensial. Setelah menemukan baris pertama yang cocok, strategi ini langsung melanjutkan ke baris berikutnya dari tabel luar, sehingga menghindari duplikasi.

Saat menjalankan pernyataan EXPLAIN untuk melihat rencana kueri, FirstMatch(t1) pada kolom Extra menunjukkan bahwa pengoptimal telah memilih strategi semi-join 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  NULL  3 100.00  NULL
1 SIMPLE  t11  NULL  ALL NULL  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`)

Strategi LooseScan

Strategi ini mengelompokkan tabel dalam berdasarkan indeks, lalu melakukan join setiap kelompok dengan tabel luar untuk menemukan kondisi join yang cocok. Jika ditemukan kecocokan, kueri mengembalikan baris dari tabel luar dan melanjutkan pemindaian ke kelompok berikutnya di tabel dalam. Metode ini menghindari pemrosesan baris duplikat pada tabel dalam.

Pada output EXPLAIN, Using index; LooseScan pada kolom Extra untuk tabel t3 menunjukkan bahwa subkueri telah dioptimalkan menjadi semi-join menggunakan strategi 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`)
```

Sintaksis

Subkueri IN atau EXISTS biasanya memicu semi-join.

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 )

Performa semi-join paralel

Untuk kueri yang menggunakan strategi semi-join, PolarDB menyediakan akselerasi paralel untuk semua strategi semi-join. Kueri ini membagi tugas semi-join menjadi serangkaian subtugas yang dijalankan secara konkuren menggunakan model multi-threading, sehingga meningkatkan kemampuan deduplikasi dan secara signifikan meningkatkan performa kueri. Mulai dari PolarDB 8.0.2.2.7, kueri paralel multi-fase didukung untuk strategi Materialization, yang semakin meningkatkan performa semi-join. Contoh Q20 berikut mengilustrasikan hal ini.

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;

Pada contoh ini, baik subkueri maupun kueri luar dieksekusi secara paralel dengan tingkat paralelisme (DOP) sebesar 32. Subkueri pertama kali menghasilkan tabel materialisasi secara paralel, lalu kueri luar juga dijalankan secara paralel. Pendekatan ini memanfaatkan sepenuhnya daya pemrosesan CPU untuk memaksimalkan paralelisme kueri. Berikut ini menunjukkan kemampuan pemrosesan paralel multi-fase dalam skenario data panas dengan set data TPC-H berskala 100 GB.

Catatan

Implementasi TPC-H dalam topik ini didasarkan pada pengujian benchmark TPC-H. Hasil pengujian ini tidak dapat dibandingkan dengan hasil benchmark TPC-H yang dipublikasikan karena pengujian ini tidak memenuhi seluruh persyaratan TPC-H.

Rencana kueri paralel adalah sebagai berikut:

 -> 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)

Dalam skenario data panas TPC-H standar berskala 100 GB, waktu eksekusi serial adalah:

| 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)

Dengan eksekusi paralel multi-node diaktifkan, waktu eksekusi menjadi:

| 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)

Waktu eksekusi berkurang dari 43,52 detik menjadi 2,29 detik, peningkatan performa sebesar 19 kali lipat.