Kueri GROUP BY standar mengembalikan satu baris agregasi per kelompok. Untuk mendapatkan subtotal pada tingkat yang lebih tinggi—seperti total seluruh produk dalam suatu negara atau total keseluruhan seluruh tahun—Anda harus menjalankan kueri terpisah atau melakukan agregasi hasil secara manual. ROLLUP menghilangkan kebutuhan tersebut dengan menghitung semua subtotal hierarkis dalam satu kueri. Ketika dikombinasikan dengan fitur kueri paralel PolarDB, ROLLUP mendistribusikan pekerjaan agregasi ke beberapa thread, sehingga secara signifikan mengurangi waktu kueri pada set data besar.
Prasyarat
Sebelum memulai, pastikan Anda telah memiliki:
Kluster PolarDB for MySQL 8.0 dengan versi revisi 8.0.1.1.0 atau lebih baru
Untuk memeriksa versi kluster Anda, lihat Query the engine cluster.
Cara kerja
ROLLUP memperluas GROUP BY untuk menghasilkan semua subtotal tingkat lebih tinggi dalam satu set hasil yang sama. Tambahkan WITH ROLLUP setelah kolom-kolom dalam klausa GROUP BY Anda:
SELECT year, country, product, SUM(profit) AS profit
FROM sales
GROUP BY year, country, product WITH ROLLUP;ROLLUP menghitung subtotal dari kanan ke kiri melalui kolom-kolom pengelompokan. Untuk GROUP BY year, country, product WITH ROLLUP, kueri menghasilkan empat tingkat agregasi:
| Level | Effective grouping | What the row represents |
|---|---|---|
| 1 | year, country, product | Profit per produk |
| 2 | year, country | Profit per negara (semua produk digabungkan) |
| 3 | year | Profit per tahun (semua negara dan produk digabungkan) |
| 4 | (none) | Total keseluruhan |
Manfaat
Menggunakan ROLLUP alih-alih beberapa kueri terpisah memberikan keuntungan berikut:
Kueri lebih sederhana: Satu pernyataan menggantikan beberapa kueri
GROUP BYdengan tingkat granularitas berbeda.Beban client berkurang: Server menghitung semua agregasi dan client hanya membaca data sekali, sehingga mengurangi beban pemrosesan dan lalu lintas jaringan.
Eksekusi paralel: PolarDB mendistribusikan pekerjaan agregasi ke beberapa thread ketika
ROLLUPdikombinasikan dengan kueri paralel, sehingga secara signifikan meningkatkan performa pada set data besar.
Performa dengan kueri paralel
PolarDB mempercepat kueri ROLLUP dengan mendistribusikan pekerjaan agregasi ke beberapa thread. Kueri TPC Benchmark H (TPC-H) Q1 berikut, yang dimodifikasi untuk menyertakan WITH ROLLUP, menunjukkan peningkatan tersebut:
Implementasi TPC-H dalam topik ini didasarkan pada pengujian benchmark TPC-H. Hasil pengujian ini tidak dapat dibandingkan dengan hasil benchmark TPC-H yang dipublikasikan karena pengujian ini tidak sepenuhnya memenuhi semua persyaratan TPC-H.
SELECT
l_returnflag,
l_linestatus,
sum(l_quantity) AS sum_qty,
sum(l_extendedprice) AS sum_base_price,
sum(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
avg(l_quantity) AS avg_qty,
avg(l_extendedprice) AS avg_price,
avg(l_discount) AS avg_disc,
count(*) AS count_order
FROM
lineitem
WHERE
l_shipdate <= date_sub('1998-12-01', interval ':1' day)
GROUP BY
l_returnflag,
l_linestatus
WITH ROLLUP
ORDER BY
l_returnflag,
l_linestatus;Hasil:
Tanpa kueri paralel: 318,73 detik
root@localhost:dbt3 8.0.13-rds-dev> select -> l_returnflag, -> l_linestatus, -> sum(l_quantity) as sum_qty, -> sum(l_extendedprice) as sum_base_price, -> sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, -> sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, -> avg(l_quantity) as avg_qty, -> avg(l_extendedprice) as avg_price, -> avg(l_discount) as avg_disc, -> count(*) as count_order -> from -> lineitem -> where -> l_shipdate <= date_sub('1998-12-01', interval ':1' day) -> group by -> l_returnflag, -> l_linestatus -> with rollup -> order by -> l_returnflag, -> l_linestatus; +-------------+--------------+----------------+-------------------+-------------------+---------------------+----------+--------------+----------+-------------+ | l_returnflag | l_linestatus | sum_qty | sum_base_price | sum_disc_price | sum_charge | avg_qty | avg_price | avg_disc | count_order | +-------------+--------------+----------------+-------------------+-------------------+---------------------+----------+--------------+----------+-------------+ | NULL | NULL | 1526902118.00 | 2289567239008.24 | 2175081814581.5635 | 2262103960452.919838 | 25.501098 | 38238.521048 | 0.050000 | 59875936 | | A | NULL | 37683718.00 | 56503738343x.68 | 53672882989857.2197 | 55826126394x.603553 | 25.500331 | 38236.069808 | 0.050005 | 14777601 | | A | F | 37683718.00 | 56503738343x.68 | 53672882989857.2197 | 55826126394x.603553 | 25.500331 | 38236.069808 | 0.050005 | 14777601 | | N | NULL | 77204634.00 | 115915498514.15 | 110131315345.0758 | 114529893971.143253 | 25.497843 | 38237.208251 | xxx | 3617xxx | | N | F | 9839917.00 | 14735826224.79 | 13998714160.5500 | 14559295139.031004 | 25.516888 | 38247.950877 | 0.049977 | 385521 | | N | O | 76321271x.00 | 1144418224289.36 | 108719254040.2584 | 113063160227x.113229 | 25.497601 | 38234.013094 | xxx | 29932xxx | | R | NULL | 37702476x.00 | 56537580506.41 | 53710767035x.2680 | 55853917990x.170252 | 25.508535 | 38251.886895 | 0.049997 | 14780xxx | | R | F | 37702476x.00 | 56537580506.41 | 53710767035x.2680 | 55853917990x.170252 | 25.508535 | 38251.886895 | 0.049997 | 14780xxx | +-------------+--------------+----------------+-------------------+-------------------+---------------------+----------+--------------+----------+-------------+ 8 rows in set, 3 warnings (5 min 18.73 sec)Dengan kueri paralel: 22,30 detik—lebih dari 14 kali lebih cepat
root@localhost:dbt3 8.0.13-rds-dev> select /*+ SET_VAR(max_parallel_degree=32) */ -> l_returnflag, -> l_linestatus, -> sum(l_quantity) as sum_qty, -> sum(l_extendedprice) as sum_base_price, -> sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, -> sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, -> avg(l_quantity) as avg_qty, -> avg(l_extendedprice) as avg_price, -> avg(l_discount) as avg_disc, -> count(*) as count_order -> from -> lineitem -> where -> l_shipdate <= date_sub('1998-12-01', interval '1' day) -> group by -> l_returnflag, -> l_linestatus -> with rollup -> order by -> l_returnflag, -> l_linestatus; | l_returnflag | l_linestatus | sum_qty | sum_base_price | sum_disc_price | sum_charge | avg_qty | avg_price | avg_disc | count_order | | NULL | NULL | 1526902118.00 | 22839567239008.24 | 2175081814581.563 | 2262103960452.919838 | 25.501098 | 38238.521048 | 0.050008 | 59785936 | | A | NULL | 376633718.00 | 5650037383347.68 | 536782889857.2197 | 558261263942.604353 | 25.500331 | 38236.693008 | 0.050005 | 14777601 | | A | F | 376633718.00 | 565003738347.68 | 536782889857.2197 | 558261263942.604353 | 25.500331 | 38236.693008 | 0.050005 | 14777601 | | N | NULL | 775804634.00 | 11591540955214.15 | 11001912541965.18 | 11445508937148.143233 | 25.497847 | 38233.208251 | 0.050008 | 30433757 | | N | F | 9439317.00 | 14174538224.79 | 13399171416810.5908 | 14550093539.110493 | 25.514642 | 38284.468099 | 0.049935 | 369914 | | N | O | 763212717.00 | 11444182242889.36 | 10871925402084.36 | 11306316022279.113229 | 25.49766 | 38233.013698 | 0.050009 | 29932993 | | R | NULL | 377024766.00 | 56535804956.41 | 53710676703959.2680 | 55854919770932.170252 | 25.505586 | 38241.865898 | 0.050009 | 14777338 | | R | F | 377024766.00 | 56535804956.41 | 53710676703959.2680 | 55854919770932.170252 | 25.505586 | 38241.865898 | 0.049997 | 14803338 | 8 rows in set, 3 warnings (22.30 sec)