Menghilangkan Pekerjaan Berulang dan Mengoptimalkan Subquery — Contoh Kerja

Data yang kita pakai di artikel ini — perf_sales dan employee

Di artikel sebelumnya kamu melihat penulisan ulang IN / EXISTS dan subquery berkorelasi.

Artikel ini melanjutkannya dengan penulisan ulang yang menghindari menghitung agregasi yang sama dua kali (melipat agregasi berulang menjadi satu hasil antara) dan memateralisasi tabel turunan (membangun subquery di klausa FROM sekali dan menahannya sementara) — dipraktikkan lewat contoh kerja konkret.

Sebelum masuk ke latihan, lihat definisi kolom dan data sampel dari dua tabel yang dipakai artikel ini — perf_sales dan employee.

① Pakai PRAGMA table_info(perf_sales); dan PRAGMA table_info(employee); untuk memeriksa definisi kolom kedua tabel.

② Pakai SELECT * FROM perf_sales LIMIT 5; dan SELECT * FROM employee LIMIT 5; untuk melihat 5 baris pertama. perf_sales punya 50.000 baris, jadi selalu tambahkan LIMIT saat melihat pratinjau.

Jangan tulis agregasi yang sama dua kali — lipat duplikasi menjadi satu hasil antara

Saat kamu ingin menanyakan "apakah total penjualan masing-masing region di atas total rata-rata di semua region?", menulis total dan rata-rata sebagai dua subquery terpisah men-scan perf_sales dua kali.

Kalau kamu melipat agregasi yang sama ke dalam satu CTE dan menghitungnya sekali, lalu menggunakan ulang hasilnya, kamu hanya men-scan sekali.

Pola penulisan ulang adalah: temukan kasus di mana "agregasi GROUP BY yang sama muncul baik di kueri utama maupun di subquery", angkat agregasi itu menjadi satu CTE, dan minta kueri utama mereferensikan CTE alih-alih.

Hasilnya identik, dan scan duplikat hilang dari plan.

-- Sebelum: total dan "rata-rata di seluruh region" diagregasi secara terpisah (perf_sales di-scan dua kali)
SELECT region, SUM(amount) AS total
FROM perf_sales
GROUP BY region
HAVING SUM(amount) > (
  SELECT AVG(t) FROM (
    SELECT SUM(amount) AS t FROM perf_sales GROUP BY region
  )
);

-- Sesudah: agregasi berdasarkan region sekali di CTE
WITH region_total AS (
  SELECT region, SUM(amount) AS total
  FROM perf_sales
  GROUP BY region
)
SELECT region, total
FROM region_total
WHERE total > (SELECT AVG(total) FROM region_total)
ORDER BY region;

Jalankan pertanyaan "region mana yang punya total penjualan di atas total rata-rata di seluruh region?" dalam dua bentuk — versi agregasi-duplikat dan versi CTE — dan bandingkan plan.

① Tambahkan EXPLAIN QUERY PLAN di depan kueri dan tampilkan plan versi yang menulis agregasi GROUP BY region baik di kueri utama maupun di dalam subquery HAVING.

② Lipat agregasi region yang sama menjadi satu CTE, ambil rata-rata dari CTE itu dengan AVG, dan urutkan region di atasnya dengan ORDER BY region. Tampilkan plan versi ini juga dengan EXPLAIN QUERY PLAN.

③ Konfirmasi bahwa SCAN perf_sales muncul beberapa kali di versi sebelum dan turun menjadi sekali di versi sesudah.

Runtuhkan subquery berkorelasi menjadi agregasi sekali jalan

Saat kondisi seperti "apakah total penjualan masing-masing region di atas total per-karyawan maksimum di dalam region itu?" dihitung sebagai subquery berkorelasi per baris region luar, kueri dalam dievaluasi ulang sekali untuk setiap region.

Ini juga bisa dilipat menjadi agregasi satu kali yang berkunci region, lalu di-join kembali.

Pola penulisan ulang adalah: ambil (SELECT agregat ... WHERE child.key = parent.key) yang dipanggil per baris luar dan ubah menjadi "bangun tabel agregat GROUP BY key sekali, lalu join kembali ke parent".

Evaluasi ulang per region digantikan dengan satu agregasi plus join.

-- Sebelum: korelasi per region untuk menemukan "total maksimum per-karyawan"
SELECT region,
  (SELECT MAX(s) FROM (
     SELECT SUM(amount) AS s FROM perf_sales p2
     WHERE p2.region = p1.region GROUP BY emp_id
  )) AS max_emp_total
FROM perf_sales p1
GROUP BY region;

-- Sesudah: agregasi sekali berdasarkan (region, emp_id), lalu ambil MAX per region
WITH emp_region AS (
  SELECT region, emp_id, SUM(amount) AS s
  FROM perf_sales
  GROUP BY region, emp_id
)
SELECT region, MAX(s) AS max_emp_total
FROM emp_region
GROUP BY region
ORDER BY region;

Jalankan "total penjualan per-karyawan maksimum di dalam setiap region" dalam dua bentuk — versi berkorelasi dan versi agregasi sekali jalan — dan bandingkan plan.

① Tambahkan EXPLAIN QUERY PLAN di depan kueri dan tampilkan plan versi yang memakai subquery berkorelasi untuk menghitung "total maksimum per-karyawan di dalam region ini" per baris region luar.

② Bangun CTE yang melakukan GROUP BY pada `(region, emp_id)` tepat sekali, lalu GROUP BY region di atas CTE itu untuk mengambil max. Tampilkan plan versi ini juga dengan EXPLAIN QUERY PLAN.

③ Konfirmasi bahwa kata CORRELATED di plan berkorelasi hilang di plan yang ditulis ulang.

Memateralisasi tabel turunan — bangun subquery klausa FROM sekali

Tabel turunan — subquery yang ditulis di klausa FROM — yang dirujuk di dua tempat kadang-kadang bisa dihitung ulang dua kali oleh optimizer.

Kalau kamu membangunnya sekali dan menahannya sementara (memateralisasinya), referensi tidak membangun ulang setiap kali.

Di SQLite, mereferensikan CTE beberapa kali membuat EXPLAIN QUERY PLAN menampilkan baris MATERIALIZE — mengonfirmasi bahwa CTE dimateralisasi tepat sekali.

Di latihan terakhir artikel ini kamu akan mengambil tabel turunan berat yang mengagregasi perf_sales berdasarkan emp_id dan dirujuk dua kali, mengubahnya menjadi CTE yang dimateralisasi sekali, dan mengonfirmasi bahwa MATERIALIZE muncul di plan dan scan terjadi hanya sekali.

-- Sebelum: tabel turunan yang sama dirujuk di dua klausa FROM
SELECT a.emp_id, a.tot, b.avg_tot
FROM (SELECT emp_id, SUM(amount) AS tot FROM perf_sales GROUP BY emp_id) a
CROSS JOIN (
  SELECT AVG(tot) AS avg_tot FROM (
    SELECT emp_id, SUM(amount) AS tot FROM perf_sales GROUP BY emp_id
  )
) b
ORDER BY a.emp_id;

-- Sesudah: tabel turunan sebagai CTE yang dirujuk dua kali (dimateralisasi sekali)
WITH emp_total AS (
  SELECT emp_id, SUM(amount) AS tot
  FROM perf_sales
  GROUP BY emp_id
)
SELECT e.emp_id, e.tot, (SELECT AVG(tot) FROM emp_total) AS avg_tot
FROM emp_total e
ORDER BY e.emp_id;

Jalankan "total penjualan setiap karyawan di samping rata-rata keseluruhan di seluruh karyawan" dalam dua bentuk — versi yang mereferensikan tabel turunan dua kali dan versi CTE — dan bandingkan plan. Ini latihan terakhir artikel.

① Tambahkan EXPLAIN QUERY PLAN di depan kueri dan tampilkan plan versi yang mereferensikan tabel turunan yang teragregasi emp_id di dua tempat di klausa FROM.

② Lipat agregasi yang sama menjadi satu CTE, ambil rata-rata dari CTE yang sama via (SELECT AVG(tot) FROM CTE_yang_sama), dan urutkan berdasarkan emp_id. Tampilkan plan versi ini juga dengan EXPLAIN QUERY PLAN.

③ Konfirmasi bahwa MATERIALIZE muncul di plan yang ditulis ulang dan SCAN perf_sales turun menjadi sekali.