PolarDB：Pembangkitan predikat transitif (penurunan kondisi yang ditingkatkan)

Prasyarat

Fitur ini didukung di kluster yang menggunakan mesin basis data MySQL 8.0.2 dengan versi revisi 8.0.2.2.23 atau lebih baru. Untuk informasi lebih lanjut tentang cara memeriksa versi mesin basis data, lihat Memeriksa versi mesin.

Skenario

Penurunan predikat ditingkatkan oleh pembangkitan predikat transitif di PolarDB. Ini mencakup implementasi berikut:

• HAVING diturunkan ke WHERE penurunan kondisi: Mengubah kondisi HAVING yang cocok untuk pra-agregasi menjadi kondisi WHERE, mengurangi volume data untuk agregasi. Untuk detail lebih lanjut, lihat penurunan kondisi (HAVING ke WHERE).

• WHERE penurunan kondisi ke tabel turunan: Kondisi WHERE dari kueri luar diturunkan ke tabel turunan untuk penyaringan data lebih awal. Untuk detail lebih lanjut, lihat penurunan kondisi (penurunan kondisi WHERE ke tabel turunan).

• WHERE klausul diturunkan ke IN subquery: Kondisi WHERE dari kueri luar diturunkan ke subquery IN untuk meningkatkan efisiensi eksekusi subquery. Untuk detail lebih lanjut, lihat penurunan kondisi (penurunan klausul WHERE ke subquery IN).

• Penurunan kondisi penggabungan ke tabel turunan yang dimaterialisasi: Kondisi penggabungan diterapkan langsung ke tabel turunan yang dimaterialisasi, meminimalkan pemindaian data yang tidak perlu. Untuk detail lebih lanjut, lihat penurunan kondisi (penurunan kondisi penggabungan ke tabel turunan yang dimaterialisasi).

Pembangkitan predikat transitif dapat diterapkan dalam skenario berikut:

• Pembangkitan predikat transitif sederhana untuk hubungan non-kesetaraan dan kesetaraan. Contoh:

  -- SQL Asli
  SELECT * FROM t1, v1 WHERE v1.a > t1.a AND t1.a > 1
  -- SQL Terderivasi
  SELECT * FROM t1, v1 WHERE v1.a > t1.a AND t1.a > 1 AND v1.a > 1
  
  -- SQL Asli
  SELECT * FROM t1, v1 WHERE v1.b < t1.c AND t1.c < t2.c AND t2.c = 1;
  -- SQL Terderivasi
  SELECT * FROM t1, v1 WHERE v1.b < t1.c AND t1.c < t2.c AND t2.c = 1 AND v1.b < 1 AND t1.c < 1;

• Ekspresi yang mematuhi hukum komutatif juga memenuhi syarat untuk pembangkitan predikat transitif. Contoh:

  -- SQL Asli
  SELECT * FROM t1, v2 WHERE v2.c1 < t1.c2 + t1.c1 AND t1.c1 + t1.c2 < 2;
  -- SQL Terderivasi
  SELECT * FROM t1, v2 WHERE v2.c1 < t1.c2 + t1.c1 AND t1.c1 + t1.c2 < 2 AND v2.c1 < 2;

• Pembangkitan predikat transitif kompleks berdasarkan hubungan kesetaraan. Contoh:

  -- SQL Asli
  SELECT * FROM t3, v3 WHERE v3.c2 = t3.c2 AND t3.c2 LIKE "%00%";
  -- SQL Terderivasi
  SELECT * FROM t3, v3 WHERE v3.c2 = t3.c2 AND t3.c2 LIKE "%00%" AND v3.c2 LIKE "%00%";
  
  -- SQL Asli
  SELECT * FROM t1, v2 WHERE v2.c1 = t1.c2 AND t1.c2 IN (1,5,7);
  -- SQL Terderivasi
  SELECT * FROM t1, v2 WHERE v2.c1 = t1.c2 AND t1.c2 IN (1,5,7) AND  v2.c1 IN (1,5,7);

• Derivasi MIN/MAX dalam klausa HAVING. Contoh:

  -- SQL Asli
  SELECT * FROM t1 GROUP BY a,b,c HAVING MAX(b) > 20;
  -- SQL Terderivasi
  SELECT * FROM t1 WHERE b > 20 GROUP BY a,b,c;
  
  -- SQL Asli
  SELECT * FROM t1 GROUP BY a,b,c HAVING MIN(b) < 20;
  -- SQL Terderivasi
  SELECT * FROM t1 WHERE b < 20 GROUP BY a,b,c;

Batasan

• Pembangkitan predikat transitif harus seragam ketika jenis kondisi perbandingan yang sama ada. Contoh:

  CREATE TABLE t1(c1 INT, c2 INT);
  CREATE TABLE t2(c1 INT, c2 VARCHAR(64));
  CREATE view v2 AS SELECT * FROM t2;
  SELECT * FROM v2,t1 WHERE v2.c1 > t1.c2 AND t1.c2 > v2.c2;

  Catatan

  Dalam contoh SQL, v2.c2 bertipe VARCHAR. Meskipun tampaknya mungkin untuk mentransmisikan hubungan non-kesetaraan melalui variabel perantara t1.c2, karena v2.c1 bertipe INT dan v2.c2 bertipe VARCHAR, t1.c2 sebagai tipe INT dibandingkan dengan v2.c1 dan v2.c2 menggunakan metode nilai yang berbeda. Oleh karena itu, derivasi paksa tidak sesuai dengan semantik SQL asli.

• Derivasi fungsi MIN/MAX dibatasi pada kueri dengan satu fungsi agregat MIN/MAX. Jika fungsi agregat serupa lainnya hadir, kondisi predikat terderivasi dalam klausa WHERE dapat mengubah hasil fungsi-fungsi tersebut, menyebabkan ketidaksesuaian antara SQL terderivasi dan asli. Contoh:

  SELECT a, MIN(b), AVG(c) FROM t1 GROUP BY a HAVING MIN(b) < 20;

  Catatan

  Dalam contoh SQL, dua fungsi agregat digunakan: AVG(c) dan MIN(b). Menerapkan aturan untuk menyertakan b < 20 dalam klausa WHERE akan menyaring baris yang tidak memenuhi b < 20 selama pemindaian tabel t1. Penyaringan ini tidak memengaruhi perhitungan AVG(c). Namun, untuk kelompok tertentu, ini dapat memengaruhi nilai hasil AVG(c), menyebabkan ketidaksesuaian dengan semantik kueri asli. Akibatnya, skenario yang memengaruhi hasil fungsi agregat lainnya tidak mendukung derivasi MIN/MAX.

Penggunaan

Persiapan

Untuk memanfaatkan pembangkitan predikat transitif, konfigurasikan parameter loose_predicate_deduce_mode untuk mengaktifkan derivasi predikat. Untuk langkah-langkah konfigurasi, lihat Konfigurasi parameter kluster dan node.

Contoh 1

-- Buat tabel contoh
CREATE VIEW v1
AS
SELECT c1, c2, MAX(c3) AS max_c3
    , AVG(c4) AS avg_c4
FROM t2
GROUP BY c1, c2
HAVING max_c3 > 10;

EXPLAIN FORMAT = TREE SELECT * FROM v1, t1 WHERE v1.c1 >= t1.c2 AND t1.c2 = v1.c2;

EXPLAIN
-> Inner hash join (t1.c2 = v1.c2)
    -> Table scan on t1  (cost=0.18 rows=10)
    -> Hash
        -> Table scan on v1
            -> Materialize
                -> Filter: (max_c3 > 10)
                    -> Table scan on <temporary>
                        -> Aggregate using temporary table
                            -> Filter: (t2.c1 >= t2.c2)  (cost=0.75 rows=2)
                                -> Table scan on t2  (cost=0.75 rows=5)

Contoh 2

SELECT a, b, ee
FROM (
    SELECT b, a, MIN(c) AS ee
    FROM t1
    GROUP BY a, b
) dt
GROUP BY a, b
HAVING MIN(ee) < 20;

-- Setelah transformasi derivasi ========>
SELECT a, b, ee
FROM (
    SELECT b, a, MIN(c) AS ee
    FROM t1
  WHERE c < 20
    GROUP BY a, b
) dt
GROUP BY a, b;


-- Rencana eksekusi berikut juga menunjukkan hasil setelah menerapkan derivasi MIN/MAX
EXPLAIN FORMAT = TREE SELECT a, b, ee
FROM (
    SELECT b, a, MIN(c) AS ee
    FROM t1
    GROUP BY a, b
) dt
GROUP BY a, b
HAVING MIN(ee) < 20;

EXPLAIN
-> Table scan on <temporary>
    -> Aggregate using temporary table
        -> Table scan on dt
            -> Materialize
                -> Table scan on <temporary>
                    -> Aggregate using temporary table
                        -> Filter: (t1.c < 20)  (cost=*** rows=***)
                            -> Table scan on t1  (cost=*** rows=***)