PolarDB：Gunakan Semijoins untuk Mempercepat Query Paralel

Prasyarat

Cluster Anda adalah PolarDB for MySQL 8.0 dengan versi revisi yang memenuhi salah satu persyaratan berikut:

• 8.0.1.0.5 atau yang lebih baru.

• 8.0.2.2.7 atau yang lebih baru.

Untuk informasi tentang cara melihat versi cluster, lihat bagian Query versi engine dari topik "Versi engine".

Informasi Latar Belakang

Mulai MySQL 5.6.5, semijoins didukung. Sebuah semijoin mengembalikan baris dari tabel luar jika baris tersebut cocok dengan setidaknya satu baris di tabel dalam. Jika baris di tabel luar cocok dengan beberapa baris di tabel dalam, semijoin hanya mengembalikan baris tersebut sekali. Dalam query yang berisi subquery tanpa semijoins, subquery dievaluasi untuk setiap tuple yang sesuai dengan kondisi di tabel luar, sehingga menurunkan efisiensi query. Dengan semijoins, subquery diubah menjadi join, dan tabel dalam ditarik ke query luar. Hal ini memungkinkan tabel dalam dan luar diproses secara paralel. Setelah sistem menemukan kecocokan antara kedua tabel, hasil langsung dikembalikan, meningkatkan efisiensi query secara signifikan.

Strategi

Berikut adalah strategi semijoin yang digunakan:

• Strategi Penghapusan Duplikat

  Strategi Penghapusan Duplikat bekerja dengan membuat tabel sementara dan menggunakan ID baris unik untuk mencegah duplikat.

  

• Strategi Materialisasi

  Strategi Materialisasi bekerja dengan mematerialisasi tabel bersarang menjadi tabel sementara terindeks. Tabel sementara ini digunakan untuk melakukan join, dengan indeks yang membantu menghapus duplikat dan mendukung pencarian saat menggabungkan tabel sementara dengan tabel luar.

  

• Strategi FirstMatch

  Strategi FirstMatch bekerja dengan mengeksekusi subquery dan memotong jalur eksekusinya segera setelah kecocokan pertama ditemukan untuk mencegah produksi duplikat.

  

• Strategi LooseScan

  Strategi LooseScan bekerja dengan mengelompokkan data di tabel dalam berdasarkan indeks, memilih satu catatan dari setiap grup nilai subquery, dan menggabungkan catatan tersebut dengan tabel luar untuk menghasilkan hasil query tanpa duplikat.

  

Sintaksis

Secara umum, semijoin menggunakan klausa IN atau EXISTS sebagai kondisi join.

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

Jalankan semijoins secara paralel untuk meningkatkan kinerja

PolarDB mendukung semua strategi semijoin dan mempercepat query paralel yang menggunakan strategi semijoin. Setiap tugas semijoin dibagi menjadi beberapa subtugas yang dijalankan secara paralel menggunakan model multi-threading. Mulai PolarDB 8.0.2.2.7, query paralel multi-fase untuk materialisasi semijoin didukung, meningkatkan kinerja query semijoin lebih lanjut. Contoh berikut menggunakan 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;

Dalam contoh ini, baik subquery maupun query luar merupakan query paralel dengan DOP 32. Subquery pertama kali menghasilkan tabel materialisasi secara paralel, kemudian query luar diproses secara paralel. Hal ini sepenuhnya memanfaatkan kemampuan pemrosesan CPU dan memaksimalkan potensi query paralel. Contoh ini menunjukkan kemampuan query paralel multi-fase setelah fitur query paralel elastis diaktifkan dalam skenario data panas TPC-H standar dengan skala 100 GB.

Berikut adalah rencana eksekusi query paralel elastis:

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

Waktu eksekusi ketika query paralel elastis multi-node diaktifkan adalah:

Hasilnya menunjukkan bahwa waktu eksekusi berkurang dari 43,52 detik menjadi 2,29 detik, dengan kecepatan query meningkat hingga 19 kali lebih cepat.