Une requête GROUP BY standard retourne une seule ligne agrégée par groupe. Pour obtenir des sous-totaux à des niveaux supérieurs, comme le total de tous les produits d'un pays ou le total général sur plusieurs années, vous devez exécuter des requêtes distinctes ou agréger manuellement les résultats. La clause ROLLUP évite cette contrainte en calculant tous les sous-totaux hiérarchiques dans une seule requête. Associée à la fonctionnalité de requête parallèle de PolarDB, ROLLUP répartit la charge d'agrégation sur plusieurs threads et réduit considérablement le temps d'exécution sur les jeux de données volumineux.
Prérequis
Avant de commencer, assurez-vous de disposer :
D'un cluster PolarDB for MySQL 8.0 dont la version de révision est 8.0.1.1.0 ou ultérieure.
Pour vérifier la version de votre cluster, consultez la rubrique Interroger le cluster du moteur.
Fonctionnement
La clause ROLLUP étend GROUP BY afin de générer tous les sous-totaux de niveau supérieur dans le même jeu de résultats. Ajoutez WITH ROLLUP après les colonnes de votre clause GROUP BY :
SELECT year, country, product, SUM(profit) AS profit
FROM sales
GROUP BY year, country, product WITH ROLLUP;ROLLUP calcule les sous-totaux de droite à gauche selon les colonnes de regroupement. Pour l'instruction GROUP BY year, country, product WITH ROLLUP, la requête produit quatre niveaux d'agrégation :
| Niveau | Regroupement effectif | Signification de la ligne |
|---|---|---|
| 1 | year, country, product |
Bénéfice par produit |
| 2 | year, country |
Bénéfice par pays (tous produits confondus) |
| 3 | year |
Bénéfice par année (tous pays et produits confondus) |
| 4 | (aucun) | Total général |
Avantages
L'utilisation de ROLLUP plutôt que de multiples requêtes distinctes offre les avantages suivants :
Simplification des requêtes : Une seule instruction remplace plusieurs requêtes
GROUP BYde granularités différentes.Réduction de la charge client : Le serveur calcule toutes les agrégations et le client ne lit les données qu'une seule fois, ce qui diminue la surcharge de traitement et le trafic réseau.
Exécution parallèle : PolarDB répartit la charge d'agrégation sur plusieurs threads lorsque
ROLLUPest combiné aux requêtes parallèles, améliorant ainsi significativement les performances sur les grands jeux de données.
Performances avec les requêtes parallèles
PolarDB accélère les requêtes ROLLUP en répartissant le travail d'agrégation sur plusieurs threads. La requête Q1 du benchmark TPC-H (TPC Benchmark H) ci-dessous, modifiée pour inclure WITH ROLLUP, illustre cette amélioration :
L'implémentation TPC-H présentée dans cette rubrique s'inspire du benchmark TPC-H. Ces résultats de test ne sauraient être comparés aux résultats officiels publiés, car les tests ne satisfont pas intégralement à toutes les exigences de la spécification 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;Résultats :
-
Sans requêtes parallèles : 318,73 secondes
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) -
Avec requêtes parallèles : 22,30 secondes, soit plus de 14 fois plus rapide
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)