PolarDB:Hilangkan GROUP BY/ORDER BY yang Redundan

Metode modifikasi parameter kluster PolarDB berbeda antara Konsol dan sesi database. Perbedaannya adalah sebagai berikut:

• Di PolarDB console

  • Kompatibilitas: Untuk menjaga kompatibilitas dengan file konfigurasi MySQL, beberapa parameter kluster di Konsol PolarDB menggunakan awalan loose_.

  • Prosedur: Temukan dan ubah parameter yang memiliki awalan loose_.

• Di sesi database (menggunakan command line atau client)

  • Prosedur: Saat Anda menggunakan perintah SET untuk mengubah parameter dalam sesi database, hapus awalan loose_ dan gunakan nama parameter aslinya.

Skenario 1: Pengelompokan berdasarkan primary key atau unique key

Saat klausa GROUP BY mencakup primary key atau unique key tabel, pengelompokan berdasarkan kolom lain bersifat redundan karena key tersebut sudah mengidentifikasi baris secara unik.

• Skenario bisnis: Hitung total jumlah pesanan untuk setiap pengguna dan tampilkan username-nya.

• SQL asli:

  -- user_id adalah primary key tabel user dan secara unik menentukan user_name.
  SELECT
      u.user_id,
      u.user_name,
      COUNT(o.order_id)
  FROM user u
  JOIN orders o ON u.user_id = o.user_id
  GROUP BY
      u.user_id,
      u.user_name;

• Analisis optimasi: Karena user_id adalah primary key, nilai tersebut secara unik menentukan user_name. Pengoptimal mendeteksi FD user_id -> user_name dan secara otomatis menyederhanakan GROUP BY u.user_id, u.user_name menjadi GROUP BY u.user_id. Hal ini menghilangkan operasi pengelompokan yang tidak perlu pada user_name.

Skenario 2: Pengelompokan berdasarkan konstanta

Saat semua kolom dalam klausa GROUP BY bersifat konstan, seluruh operasi pengelompokan bersifat redundan karena set hasil berisi paling banyak satu baris.

• Skenario bisnis: Kueri informasi status tertentu untuk pengguna tertentu. Ini merupakan pola SQL yang jarang digunakan, tetapi pengoptimal dapat menanganinya.

• SQL asli:

  SELECT a, b FROM t1 WHERE a = 1 AND b = 1 GROUP BY a, b;

• Analisis optimasi: Pengoptimal mendeteksi bahwa kolom GROUP BY a dan b ditentukan sebagai konstanta dalam klausa WHERE. Pengoptimal menghapus operator GROUP BY dan menambahkan LIMIT 1.

  SELECT a, b FROM t1 WHERE a = 1 AND b = 1 LIMIT 1;

Skenario 3: Pengelompokan kompleks dalam join multi-tabel (TPC-H Q10)

Dalam kueri kompleks dengan JOIN multi-tabel, ketergantungan fungsional dapat dipropagasi melalui kondisi join. Pengoptimal memanfaatkan propagasi ini untuk menghilangkan pengelompokan redundan yang lebih kompleks.

• SQL asli (TPC-H Q10):

  SELECT
      c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment,
      SUM(l_extendedprice * (1 - l_discount)) AS revenue
  FROM customer, orders, lineitem, nation
  WHERE c_custkey = o_custkey
    AND l_orderkey = o_orderkey
    AND c_nationkey = n_nationkey
    -- Other filter conditions...
  GROUP BY
      c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment
  ORDER BY revenue DESC
  LIMIT 20;

• Analisis optimasi:

  1. Ketergantungan primary key: Pada tabel customer, c_custkey adalah primary key. Oleh karena itu, nilai tersebut secara unik menentukan semua kolom lainnya, seperti c_name, c_acctbal, c_phone, c_address, c_comment, dan c_nationkey.

  2. Propagasi ketergantungan: Kondisi JOIN adalah c_nationkey = n_nationkey. Karena n_nationkey adalah primary key tabel nation dan menentukan n_name, pengoptimal dapat menyimpulkan bahwa c_custkey juga secara unik menentukan n_name.

  3. Penyederhanaan akhir: Secara ringkas, semua kolom dalam klausa GROUP BY, seperti c_name dan c_acctbal, bergantung secara fungsional pada c_custkey. Oleh karena itu, pengoptimal menyederhanakan daftar GROUP BY yang kompleks menjadi GROUP BY c_custkey. Hal ini sangat mengurangi kompleksitas perhitungan pengelompokan.

  SELECT 
     c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment,
     SUM(l_extendedprice * (1 - l_discount)) AS revenue
  FROM customer, orders, lineitem, nation
  WHERE c_custkey = o_custkey
    AND l_orderkey = o_orderkey
    AND c_nationkey = n_nationkey
    -- Other filter conditions...
  GROUP BY c_custkey
  ORDER BY revenue DESC
  LIMIT 20;

Skenario 4: Skenario ORDER BY

Saat terdapat ketergantungan fungsional antar kolom dalam klausa ORDER BY, pengoptimal dapat menyederhanakan operasi pengurutan dengan mengurangi jumlah kolom yang diurutkan.

• Skenario bisnis: Urutkan tabel di mana beberapa kolom ditentukan secara unik oleh kolom lainnya.

• Skema tabel:

  CREATE TABLE t1 (
    a int,
    b int,
    c int as (a + b) unique not null
  );

• SQL asli:

  EXPLAIN SELECT a, b FROM t1 ORDER BY a,b,c;

• Analisis optimasi: Karena kolom c dihitung dari kolom a dan b (c = a + b) dan didefinisikan sebagai unique not null, terdapat ketergantungan fungsional: (a, b) -> c. Pengoptimal mengenali ketergantungan ini dan secara otomatis menyederhanakan ORDER BY a, b, c menjadi ORDER BY a, b, sehingga mengurangi kompleksitas operasi pengurutan.

  SHOW warnings;
  +-------+------+-------------------------------------------------------------------------------------------------------------------------------+
  | Level | Code | Message                                                                                                                       |
  +-------+------+-------------------------------------------------------------------------------------------------------------------------------+
  | Note  | 1003 | /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` order by `test`.`t1`.`a`,`test`.`t1`.`b` |
  +-------+------+-------------------------------------------------------------------------------------------------------------------------------+