PolarDB:Penguraian subquery

Prasyarat

Kluster Anda harus berupa kluster PolarDB for MySQL 8.0 dengan versi revisi 8.0.2.2.1 atau lebih baru. Anda dapat menanyakan nomor versi untuk memastikan versi kluster Anda.

Latar Belakang

Subquery berkorelasi banyak digunakan dalam beban kerja analitis. Sebagai contoh, lebih dari sepertiga dari 22 kueri dalam benchmark TPC-H mengandung subquery berkorelasi. Tanpa penguraian, subquery dieksekusi untuk setiap baris yang diproses oleh query luar. Eksekusi berulang ini dapat menyebabkan waktu eksekusi kueri yang panjang, terutama ketika query luar mengembalikan jumlah baris yang besar atau ketika subquery tidak memiliki indeks yang sesuai. Penguraian subquery mengubah subquery berkorelasi menjadi pernyataan JOIN yang ekuivalen. Transformasi ini menghindari eksekusi subquery berulang dan memungkinkan pengoptimal menerapkan optimasi join lebih lanjut.

Penguraian dengan fungsi jendela

Ikhtisar

Penggunaan

• Gunakan parameter sistem loose_polar_optimizer_switch untuk mengaktifkan penguraian subquery dengan fungsi jendela. Untuk petunjuknya, lihat Setel parameter kluster dan node.

  Parameter	Lingkup	Deskripsi
  loose_polar_optimizer_switch	Global, Session	Mengontrol fitur optimasi kueri. Opsi-opsinya meliputi: unnest_use_window_function: Mengontrol penguraian subquery yang menggunakan fungsi jendela. ON (Default): Mengaktifkan fitur ini. OFF: Menonaktifkan fitur ini. unnest_use_group_by: Mengontrol penguraian subquery yang menggunakan klausa GROUP BY. Transformasi kueri ini tunduk pada optimasi kueri berbasis biaya (cost-based). ON (Default): Mengaktifkan fitur ini. OFF: Menonaktifkan fitur ini. derived_merge_cost_based: Menentukan apakah fitur derived merge tunduk pada optimasi kueri berbasis biaya. OFF (Default): Fitur derived merge tidak tunduk pada optimasi kueri berbasis biaya. ON: Fitur derived merge tunduk pada optimasi kueri berbasis biaya.

  Contoh: Contoh berikut menggunakan kueri TPC-H Q2, yang mencari pemasok yang menawarkan biaya pasokan terendah untuk jenis dan ukuran suku cadang tertentu dalam wilayah tertentu. Dalam Edisi Komunitas MySQL, mesin eksekusi pertama-tama menjalankan query luar untuk menemukan pemasok untuk suku cadang yang ditentukan. Kemudian, untuk setiap baris yang dikembalikan, mesin tersebut mengeksekusi subquery untuk menghitung biaya pasokan minimum untuk suku cadang tersebut dari semua pemasok di wilayah yang ditentukan. Terakhir, mesin membandingkan biaya pasokan pemasok dengan biaya pasokan minimum dari subquery.

  SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr,
   s_address, s_phone, s_comment
  FROM part, supplier, partsupp, nation, region
  WHERE p_partkey = ps_partkey
     AND s_suppkey = ps_suppkey
     AND p_size = 30
     AND p_type LIKE '%STEEL'
     AND s_nationkey = n_nationkey
     AND n_regionkey = r_regionkey
     AND r_name = 'ASIA'
     AND ps_supplycost = (
         SELECT MIN(ps_supplycost)
         FROM partsupp, supplier, nation, region
         WHERE p_partkey = ps_partkey
             AND s_suppkey = ps_suppkey
             AND s_nationkey = n_nationkey
             AND n_regionkey = r_regionkey
             AND r_name = 'ASIA'
     )
  ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
  LIMIT 100;

  Fungsi jendela memungkinkan kueri menghitung fungsi agregat pada partisi tertentu dan menambahkan hasilnya ke baris asli. Untuk TPC-H Q2, hal ini memungkinkan sistem mengambil pemasok untuk suku cadang yang ditentukan sekaligus menghitung biaya pasokan minimum yang dikelompokkan berdasarkan suku cadang. Hasil kemudian difilter dengan membandingkan biaya pasokan pada setiap baris dengan nilai minimum yang dihitung untuk kelompoknya. Setelah transformasi kueri ini, kueri Q2 menjadi ekuivalen dengan kueri berikut:

  SELECT s_acctbal, s_name, n_name, p_partkey, p_mfgr,
    s_address, s_phone, s_comment
  FROM (
      SELECT MIN(ps_supplycost) OVER(PARTITION BY ps_partkey) as win_min,
        ps_partkey, ps_supplycost, s_acctbal, n_name, s_name, s_address,
        s_phone, s_comment
      FROM part, partsupp, supplier, nation, region
      WHERE p_partkey = ps_partkey
        AND s_suppkey = ps_suppkey
        AND s_nationkey = n_nationkey
        AND n_regionkey = r_regionkey
        AND p_size = 30
        AND p_type LIKE '%STEEL'
        AND r_name = 'ASIA') as derived
  WHERE ps_supplycost = derived.win_min
  ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
  LIMIT 100;

• Gunakan petunjuk (hint) untuk mengontrol penguraian subquery pada kueri tertentu.

  Gunakan hint UNNEST untuk mengontrol transformasi kueri ini. Formatnya adalah sebagai berikut:

  UNNEST([@query_block_name] [strategy [, strategy] ...])   # Menguraikan subquery menggunakan fungsi jendela atau klausa GROUP BY, mengabaikan pengaturan loose_polar_optimizer_switch.
  NO_UNNEST([@query_block_name] [strategy [, strategy] ...])  # Mencegah subquery diuraikan, mengabaikan pengaturan loose_polar_optimizer_switch.

  Opsi strategy mendukung WINDOW_FUNCTION dan GROUP_BY.

  Contoh:

  # Memaksa penguraian dengan fungsi jendela.
  SELECT ... FROM ... WHERE ... = (SELECT /*+UNNEST(WINDOW_FUNCTION)*/ agg FROM ...)
  SELECT /*+UNNEST(@`select#2` WINDOW_FUNCTION)*/ ... FROM ... WHERE ... = (SELECT agg FROM ...)
  
  # Mencegah penguraian dengan fungsi jendela.
  SELECT ... FROM ... WHERE ... = (SELECT /*+NO_UNNEST(WINDOW_FUNCTION)*/ agg FROM ...)
  SELECT /*+NO_UNNEST(@`select#2` WINDOW_FUNCTION)*/ ... FROM ... WHERE ... = (SELECT agg FROM ...)

Kinerja

Penguraian dengan klausa GROUP BY

Ikhtisar

Penggunaan

• Gunakan parameter sistem loose_polar_optimizer_switch untuk mengaktifkan penguraian subquery dengan klausa GROUP BY. Untuk petunjuknya, lihat Setel parameter kluster dan node.

  Parameter	Lingkup	Deskripsi
  loose_polar_optimizer_switch	Global, Session	Mengontrol fitur optimasi kueri. Opsi-opsinya meliputi: unnest_use_window_function: Mengontrol penguraian subquery yang menggunakan fungsi jendela. ON (Default): Mengaktifkan fitur ini. OFF: Menonaktifkan fitur ini. unnest_use_group_by: Mengontrol penguraian subquery yang menggunakan klausa GROUP BY. Transformasi kueri ini tunduk pada optimasi kueri berbasis biaya (cost-based). ON (Default): Mengaktifkan fitur ini. OFF: Menonaktifkan fitur ini. derived_merge_cost_based: Menentukan apakah fitur derived merge tunduk pada optimasi kueri berbasis biaya. OFF (Default): Fitur derived merge tidak tunduk pada optimasi kueri berbasis biaya. ON: Fitur derived merge tunduk pada optimasi kueri berbasis biaya.

  Contoh: Kueri berikut mencari item baris pesanan penjualan di mana kuantitasnya lebih besar dari 10% total kuantitas yang dibeli untuk item yang bersangkutan.

  SELECT *
  FROM sale_lineitem sl
  WHERE sl.sl_quantity >
      (SELECT 0.1 * SUM(pl.pl_quantity)
       FROM purchase_lineitem pl
       WHERE pl.pl_objectkey = sl.sl_objectkey);

  Tanpa transformasi kueri ini, mesin eksekusi melakukan iterasi melalui setiap baris tabel sale_lineitem. Untuk setiap baris, mesin mengambil sl_objectkey, memasukkannya ke dalam subquery, lalu mengeksekusi subquery untuk menghitung 10% dari total kuantitas yang dibeli. Hasil ini kemudian dibandingkan dengan kuantitas pada baris saat ini. Dalam skenario ini, subquery dieksekusi sekali untuk setiap baris dalam tabel sale_lineitem. Bahkan dengan adanya indeks pada kolom pl_objectkey, proses ini menyebabkan pemindaian dan perhitungan berulang pada tabel purchase_lineitem karena kolom sl_objectkey sering kali mengandung banyak nilai duplikat. Untuk mengoptimalkan subquery berkorelasi yang tidak efisien seperti ini, PolarDB menguraikannya menggunakan klausa GROUP BY. Kueri asli ditransformasikan menjadi sebagai berikut:

  SELECT *
  FROM sale_lineitem sl
  LEFT JOIN
    (SELECT (0.1 * sum(pl.pl_quantity)) AS Name_exp_1,
            pl.pl_objectkey AS Name_exp_2
     FROM purchase_lineitem pl
     GROUP BY pl.pl_objectkey) derived ON derived.Name_exp_2 = sl.sl_objectkey
  WHERE sl.sl_quantity > derived.name_exp_1;

  Setelah transformasi, sistem pertama-tama menghitung agregat untuk setiap item yang dibeli, lalu melakukan join hasilnya dengan tabel sale_lineitem. Hal ini memastikan tabel purchase_lineitem hanya dipindai sekali, sehingga menghindari pemindaian dan perhitungan berulang. Pengoptimal dapat lebih lanjut mengoptimalkan pernyataan yang telah ditransformasikan dengan menghilangkan outer join dan menyesuaikan urutan join untuk meningkatkan efisiensi eksekusi.

• Gunakan petunjuk (hint) untuk mengontrol penguraian subquery pada kueri tertentu.

  Gunakan hint UNNEST untuk mengontrol transformasi kueri ini. Formatnya adalah sebagai berikut:

  UNNEST([@query_block_name] [strategy [, strategy] ...])   # Menguraikan subquery menggunakan fungsi jendela atau klausa GROUP BY, mengabaikan pengaturan loose_polar_optimizer_switch.
  NO_UNNEST([@query_block_name] [strategy [, strategy] ...])  # Mencegah subquery diuraikan, mengabaikan pengaturan loose_polar_optimizer_switch.

  Opsi strategy mendukung WINDOW_FUNCTION dan GROUP_BY.

  Contoh:

  # Memaksa penguraian dengan klausa GROUP BY.
  SELECT ... FROM ... WHERE ... = (SELECT /*+UNNEST(GROUP_BY)*/ agg FROM ...)
  SELECT /*+UNNEST(@`select#2` GROUP_BY)*/ ... FROM ... WHERE ... = (SELECT agg FROM ...)
  
  # Mencegah penguraian dengan klausa GROUP BY.
  SELECT ... FROM ... WHERE ... = (SELECT /*+NO_UNNEST(GROUP_BY)*/ agg FROM ...)
  SELECT /*+NO_UNNEST(@`select#2` GROUP_BY)*/ ... FROM ... WHERE ... = (SELECT agg FROM ...)