PolarDB：Tentukan pengurutan ulang join untuk pengoptimal IMCI

SQL adalah bahasa kueri deklaratif dan tidak secara spesifik menggambarkan rencana kueri dari pernyataan SQL. Beberapa rencana kueri dapat digunakan untuk mendapatkan hasil yang benar dari pernyataan SQL. Contoh:

SELECT * FROM t0, t1, t2, t3 WHERE t0.a = t1.a AND t1.a = t2.a AND t2.a = t3.a AND t3.b = t1.b;

Untuk pernyataan SQL di atas, Anda bisa mendapatkan hasil kueri yang benar menggunakan dua rencana kueri berikut. Rencana A dan Rencana B adalah equivalent query plans. Sebagai kerangka pencarian, pengoptimal kueri mencari rencana kueri yang setara sesuai dengan pernyataan SQL saat ini dengan mentransformasikan satu rencana kueri ke rencana kueri lain yang setara. Misalnya, t1 INNER JOIN t2 dan t2 INNER JOIN t1 adalah rencana kueri yang setara. Pengoptimal kueri dapat mengubah t1 INNER JOIN t2 menjadi t2 INNER JOIN t1 melalui transformasi rencana kueri yang setara. Transformasi oleh pengoptimal ini disebut query transformation rule.

Pengoptimal kueri bekerja sebagai berikut:

1. Memasukkan rencana kueri awal yang diperoleh dengan menguraikan pernyataan SQL.

2. Menghasilkan rencana kueri yang setara dari rencana kueri status awal menggunakan aturan transformasi kueri.

3. Memilih rencana kueri dengan biaya eksekusi terendah dari rencana kueri yang setara dengan menggabungkan statistik dan model biaya, lalu menyerahkannya ke lapisan eksekusi sebagai rencana eksekusi akhir.

Fitur optimasi kueri bergantung pada statistik untuk melakukan estimasi kardinalitas dan perhitungan biaya guna menentukan rencana kueri terbaik. Dalam IMCI, statistik tabel mencakup item-item berikut:

• Histogram, yang menunjukkan distribusi nilai kolom berbeda dan terutama digunakan untuk memperkirakan rentang nilai pada tabel tunggal serta laju seleksi predikat ekuivalen.

• Jumlah nilai khusus dalam kolom, yang terutama digunakan untuk memperkirakan jumlah grup dalam Group By, dan juga dapat digunakan untuk membantu memperkirakan laju seleksi predikat ekuivalen.

• Kendala lainnya, seperti apakah kolom memiliki indeks unik atau apakah kolom memiliki kendala kunci asing dengan kolom lain.

Pengoptimal kueri menghitung biaya setiap operator node dalam rencana kueri berdasarkan item-item berikut:

• Jumlah total baris yang diproses oleh operator dalam rencana kueri. Statistik dapat digunakan untuk memperkirakan jumlah total baris.

• Kompleksitas algoritma yang digunakan oleh setiap operator dalam rencana kueri.

Kluster PolarDB Anda harus menggunakan salah satu versi berikut:

• Kluster PolarDB for MySQL 8.0.1 dengan versi revisi 8.0.1.1.31 atau lebih baru.

• Kluster PolarDB for MySQL 8.0.2 dengan versi revisi 8.0.2.2.12 atau lebih baru.

Kondisi berikut dapat menyebabkan kesalahan besar dalam estimasi kardinalitas, sehingga menyebabkan pengoptimal memilih rencana kueri suboptimal. Anda dapat menggunakan petunjuk untuk menginstruksikan pengoptimal agar menghasilkan rencana kueri ideal.

• Untuk kueri yang mengandung predikat, operator perbandingan digunakan untuk menanyakan kolom berbeda dari sebuah tabel. Contoh: t1.c1>t1.c2.

• Untuk kueri yang mengandung predikat, operator yang digunakan dalam pernyataan kueri tidak dapat menggunakan statistik untuk estimasi. Contoh: t1.c1 MOD 2=1 dan t1.c2 LIKE '%ABC%'.

• Untuk kueri yang mengandung predikat, pernyataan kueri memiliki ekspresi dan fitur optimasi tidak dapat digunakan untuk perhitungan. Contoh: t1.c1+t1.c3>100.

• Kolom yang terlibat dalam operator dalam pernyataan kueri tidak memiliki statistik yang tersedia untuk memperkirakan laju seleksi predikat. Contoh: SELECT a, SUM(b) FROM t1 HAVING SUM(b) > 10.

• Beberapa predikat digabungkan menggunakan operator AND. Contoh: t1.c1>10 AND t1.c3<5.

• Pernyataan kueri mengandung banyak lapisan bersarang.

• Pernyataan kueri menggabungkan banyak tabel. Anda dapat mengubah nilai parameter loose_imci_max_enum_join_pairs untuk menentukan jumlah join yang dapat digunakan oleh pengoptimal IMCI.

Contoh berikut menggunakan TPCH-Q8. Pernyataan kueri melibatkan beberapa tabel dan berisi fungsi agregat.

SELECT
  o_year,
  SUM(
    CASE
      WHEN nation = 'BRAZIL' THEN volume
      ELSE 0
    END
  ) / SUM(volume) AS mkt_share
FROM
  (
    SELECT
      EXTRACT(
        year
        FROM
          o_orderdate
      ) AS o_year,
      l_extendedprice * (1 - l_discount) AS volume,
      n2.n_name AS nation
    FROM
      lineitem,
      orders,
      part,
      supplier,
      customer,
      nation n1,
      nation n2,
      region
    WHERE
      p_partkey = l_partkey
      AND s_suppkey = l_suppkey
      AND l_orderkey = o_orderkey
      AND o_custkey = c_custkey
      AND c_nationkey = n1.n_nationkey
      AND n1.n_regionkey = r_regionkey
      AND r_name = 'AMERICA'
      AND s_nationkey = n2.n_nationkey
      AND o_orderdate BETWEEN DATE '1995-01-01'
      AND DATE '1996-12-31'
      AND p_type = 'ECONOMY ANODIZED STEEL'
  ) AS all_nations
GROUP By
  o_year
ORDER BY
  o_year;

• Gambar berikut menunjukkan rencana kueri ketika fitur optimasi kueri IMCI dinonaktifkan. Rencana kueri melibatkan beberapa join dan menghasilkan set hasil besar. Ini meningkatkan jumlah baris data yang diproses oleh operator dan biaya pemrosesan serta memperpanjang latensi. Kluster 32-core dan data TPCH SF100 digunakan dalam pengujian. Durasi kueri adalah 7.017 ms.

• Gambar berikut menunjukkan rencana kueri ketika fitur optimasi kueri IMCI diaktifkan. Pengoptimal kueri mengurutkan ulang join untuk mengurangi data keluaran hampir semua operator join menjadi jutaan baris. Ini mengurangi biaya pemrosesan untuk operator berikutnya. Kluster 32-core dan data TPCH SF100 digunakan dalam pengujian. Durasi kueri adalah 1.900 ms. Durasi kueri 73% lebih pendek daripada ketika fitur optimasi kueri IMCI dinonaktifkan.