PolarDB：Penguraian subquery

Prasyarat

Versi kluster adalah PolarDB untuk MySQL 8.0, dengan versi revisi kluster 8.0.2.2.1 atau lebih baru. Untuk informasi tentang cara memeriksa versi kluster Anda, lihat Kueri versi mesin.

Latar Belakang

Subquery banyak digunakan dalam kueri analitik. Lebih dari sepertiga dari 22 kueri di TPC-H berisi subquery. Subquery yang tidak diurai dari kueri luar dieksekusi setiap kali kueri luar diproses pada baris data. Jika kueri luar menghasilkan sejumlah besar data dan subquery tidak terkait dengan indeks, eksekusi subquery akan sangat memakan waktu. Penguraian subquery mentransformasikan subquery menjadi pernyataan JOIN yang setara, sehingga menghindari eksekusi subquery berkali-kali dan memungkinkan pengoptimal untuk lebih mengoptimalkan pernyataan JOIN.

Uraikan kueri dengan menggunakan fungsi jendela

Overview

Use the subquery unnesting feature

• Gunakan parameter loose_polar_optimizer_switch untuk mengaktifkan penguraian subquery. Untuk informasi lebih lanjut, lihat Konfigurasikan Parameter Kluster dan Node.

  Parameter	Level	Deskripsi
  loose_polar_optimizer_switch	Global dan sesi	Menentukan apakah akan mengaktifkan fitur optimasi kueri PolarDB. unnest_use_window_function: menentukan apakah akan menguraikan subquery dengan menggunakan fungsi jendela. Nilai default: ON. Nilai valid: ON OFF unnest_use_group_by: menentukan apakah akan menguraikan subquery dengan menggunakan klausa GROUP BY. Metode penguraian subquery ini diimplementasikan berdasarkan optimasi kueri berbasis biaya. Nilai default: ON. Nilai valid: ON OFF derived_merge_cost_based: menentukan apakah fitur penggabungan turunan diimplementasikan berdasarkan optimasi kueri berbasis biaya. Nilai default: OFF. Nilai valid: ON OFF

  Dalam contoh berikut, Minimum Cost Supplier Query (Q2) dalam TPC-H digunakan untuk mendemonstrasikan fitur tersebut. Kueri digunakan untuk mendapatkan pemasok yang membebankan harga terendah dari semua pemasok di wilayah tertentu yang menjual komponen jenis dan ukuran tertentu. Di Edisi Komunitas MySQL, kueri luar pertama-tama dieksekusi untuk mendapatkan informasi pemasok yang menjual komponen jenis dan ukuran tertentu. Kemudian, subquery dieksekusi pada setiap baris data untuk mendapatkan pemasok yang menjual komponen dengan harga terendah. Terakhir, harga yang terdapat dalam hasil kueri luar dibandingkan dengan harga yang terdapat dalam hasil subquery untuk menentukan apakah kedua nilai tersebut konsisten.

  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;

  Gunakan fungsi jendela untuk mengeksekusi fungsi agregat dalam kelompok dan tambahkan hasilnya ke baris. Untuk Q2 dari TPC-H, informasi pemasok yang berada di wilayah tertentu dan menjual komponen ukuran dan jenis tertentu diperoleh. Pemasok dikelompokkan berdasarkan informasi komponen. Fungsi agregat menggunakan informasi yang dikelompokkan untuk mendapatkan harga terendah. Kemudian, harga dalam baris dan harga terendah dalam kelompok itu dibandingkan untuk menentukan nilai yang akan digunakan. Kueri sebelumnya diubah menjadi 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 untuk menguraikan subquery melalui fungsi jendela

  Petunjuk UNNEST dapat digunakan untuk mengontrol penguraian. Format:

  UNNEST([@query_block_name] [strategy [, strategy] ...])   # Fungsi jendela atau klausa GROUP BY digunakan untuk menguraikan subquery, terlepas dari nilai polar_optimizer_switch.
  NO_UNNEST([@query_block_name] [strategy [, strategy] ...])  # Fungsi jendela atau klausa GROUP BY tidak digunakan untuk menguraikan subquery, terlepas dari nilai polar_optimizer_switch.

  strategy dapat diatur ke WINDOW_FUNCTION atau GROUP_BY.

  Contoh:

  # Uraikan subquery melalui fungsi jendela.
  SELECT ... FROM ... WHERE ... = (SELECT /*+UNNEST(WINDOW_FUNCTION)*/ agg FROM ...)
  SELECT /*+UNNEST(@`select#2` WINDOW_FUNCTION)*/ ... FROM ... WHERE ... = (SELECT agg FROM ...)
  
  # Jangan uraikan subquery melalui fungsi jendela.
  SELECT ... FROM ... WHERE ... = (SELECT /*+NO_UNNEST(WINDOW_FUNCTION)*/ agg FROM ...)
  SELECT /*+NO_UNNEST(@`select#2` WINDOW_FUNCTION)*/ ... FROM ... WHERE ... = (SELECT agg FROM ...)

Performance improvement

Gunakan klausa GROUP BY untuk menguraikan subquery

Overview

Use the subquery unnesting feature

• Gunakan parameter loose_polar_optimizer_switch untuk mengaktifkan penguraian subquery. Untuk informasi lebih lanjut, lihat Konfigurasikan Parameter Kluster dan Node.

  Parameter	Level	Deskripsi
  loose_polar_optimizer_switch	Global dan sesi	Menentukan apakah akan mengaktifkan fitur optimasi kueri PolarDB. unnest_use_window_function: menentukan apakah akan menguraikan subquery dengan menggunakan fungsi jendela. Nilai default: ON. Nilai valid: ON OFF unnest_use_group_by: menentukan apakah akan menguraikan subquery dengan menggunakan klausa GROUP BY. Metode penguraian subquery ini diimplementasikan berdasarkan optimasi kueri berbasis biaya. Nilai default: ON. Nilai valid: ON OFF derived_merge_cost_based: menentukan apakah fitur penggabungan turunan diimplementasikan berdasarkan optimasi kueri berbasis biaya. Nilai default: OFF. Nilai valid: ON OFF

  Dalam contoh ini, kueri digunakan untuk mendapatkan detail pesanan di mana jumlahnya lebih besar dari 10% dari total jumlah.

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

  Jika tidak ada transformasi kueri yang dilakukan, setiap baris tabel sale_lineitem diiterasi untuk mendapatkan nilai sl_objectkey yang digunakan oleh subquery. Setiap kali nilai diperoleh, subquery dieksekusi untuk menghitung hasil dari 10% dari total jumlah dan membandingkan total jumlah dengan jumlah penjualan dalam baris tersebut. Jumlah eksekusi subquery sama dengan jumlah baris dalam tabel sale_lineitem. Bahkan jika kolom pl_objectkey memiliki indeks, pemindaian dan perhitungan replikasi dalam jumlah besar diperlukan pada tabel purchase_lineitem, karena biasanya ada sejumlah besar nilai duplikat dalam kolom sl_objectkey. Subquery yang tidak efisien tersebut diuraikan dalam PolarDB menggunakan klausa GROUP BY. Kueri sebelumnya diubah menjadi kueri 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;

  Informasi setiap produk dihitung dalam kelompok dan digabungkan dengan tabel sale_lineitem. Dalam hal ini, purchase_lineitem hanya perlu dipindai sekali. Kueri dapat lebih dioptimalkan dengan mengubah urutan join untuk meningkatkan efisiensi eksekusi.

• Gunakan petunjuk untuk menguraikan subquery melalui klausa GROUP BY

  Petunjuk UNNEST dapat digunakan untuk mengontrol penguraian. Format:

  UNNEST([@query_block_name] [strategy [, strategy] ...])   # Fungsi jendela atau klausa GROUP BY digunakan untuk menguraikan subquery, terlepas dari nilai polar_optimizer_switch.
  NO_UNNEST([@query_block_name] [strategy [, strategy] ...])  # Fungsi jendela atau klausa GROUP BY tidak digunakan untuk menguraikan subquery, terlepas dari nilai polar_optimizer_switch.

  strategy dapat diatur ke WINDOW_FUNCTION atau GROUP_BY.

  Contoh:

  # Uraikan subquery melalui klausa GROUP BY
  SELECT ... FROM ... WHERE ... = (SELECT /*+UNNEST(GROUP_BY)*/ agg FROM ...)
  SELECT /*+UNNEST(@`select#2` GROUP_BY)*/ ... FROM ... WHERE ... = (SELECT agg FROM ...)
  
  # Jangan uraikan subquery melalui 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 ...)