Pourquoi une requête d'analyse lit moins de données
Le stockage en colonnes range côte à côte les valeurs d'une même colonne, au lieu de ranger une ligne après l'autre. Il rend les requêtes d'analyse beaucoup plus rapides, car elles lisent seulement les colonnes dont elles ont besoin.
Une chaîne de papeteries veut le total des quantités vendues. La table de ventes a sept colonnes. La question n'en a besoin que d'une.
À la fin de cette leçon
Remarque
Je peux expliquer pourquoi une requête d'analyse lit une table autrement qu'une recherche d'une seule ligne.
Mots clés
| Mot | Ce que ça veut dire | Exemple |
|---|---|---|
| Requête d'analyse | une question qui résume beaucoup de lignes | |
| Recherche ponctuelle | une question qui cherche une seule ligne | |
| Stockage en lignes | les valeurs d'une même ligne sont rangées ensemble | |
| Stockage en colonnes | les valeurs d'une même colonne sont rangées ensemble | |
| Agrégat | une fonction qui résume plusieurs lignes en une valeur | sum(quantite) |
| DuckDB | un moteur SQL pensé pour l'analyse, qui tourne dans cette page |
Les cellules que les étudiants remplissent restent vides dans cet aperçu.
Une recherche ponctuelle et une analyse diffèrent
Une recherche ponctuelle cherche une seule ligne : la commande numéro 4182, le compte d'un client. Elle veut toute la ligne.
Une requête d'analyse résume des milliers ou des millions de lignes : le total par mois, la moyenne par station. Elle veut peu de colonnes, mais beaucoup de lignes.
Exemple : deux questions sur la même table
| Question | Lignes concernées | Colonnes utilisées | Genre |
|---|---|---|---|
| Quelle est la vente numéro 7 ? | 1 | Les 7 | Recherche ponctuelle |
| Quel est le total des quantités ? | Toutes | 1 (quantite) | Requête d'analyse |
| Quelle est la quantité moyenne par magasin ? | Toutes | 2 (magasin, quantite) | Requête d'analyse |
| Quels produits a acheté le client 12 ? | Quelques-unes | Peu | Recherche ponctuelle |
Les cellules que les étudiants remplissent restent vides dans cet aperçu.
Le stockage en lignes range chaque ligne ensemble
Beaucoup de bases rangent chaque ligne d'un seul bloc : identifiant, date, magasin, produit, et le reste. C'est le stockage en lignes.
Il est parfait pour une recherche ponctuelle, car toute la ligne est au même endroit. Pour un total, il oblige à passer sur les sept valeurs de chaque ligne, même si une seule sert.
Exemple : combien de valeurs sont lues
| Mode de stockage | Table | Valeurs lues pour sum(quantite) | Part utile |
|---|---|---|---|
| En lignes | 1 000 000 lignes de 7 colonnes | 7 000 000 | 1 sur 7 |
| En colonnes | 1 000 000 lignes de 7 colonnes | 1 000 000 | Toutes |
| En colonnes, deux colonnes demandées | 1 000 000 lignes de 7 colonnes | 2 000 000 | Toutes |
Les cellules que les étudiants remplissent restent vides dans cet aperçu.
Le stockage en colonnes lit moins de données
Avec le stockage en colonnes, les valeurs de quantite sont rangées ensemble, celles de magasin ensemble, et ainsi de suite. Pour calculer un total, on lit une seule colonne.
DuckDB range ses tables ainsi. Les valeurs d'une colonne ont le même type et se ressemblent, ce qui les rend aussi faciles à compresser.
À vous : un total sur une colonne
-- Le total des quantités vendues, sur toute la table.
select sum(quantite) as total_quantite from ventes;
-- Remplacez sum par max pour trouver la plus grosse vente en quantité.
EXPLAIN montre les colonnes que DuckDB va lire
Écrivez explain devant une requête : DuckDB n'exécute rien, il montre son plan. Le plan est une suite de boîtes, lue de bas en haut.
La boîte du bas lit la table. Sa ligne Projections liste les colonnes lues. Pour sum(quantite), elle ne cite que quantite.
À vous : lire les colonnes du plan
-- Le plan que DuckDB prépare. Cherchez la ligne Projections.
explain select sum(quantite) from ventes;
-- Remplacez sum(quantite) par sum(quantite * prix_unitaire) et relisez Projections.
Une erreur fréquente
Attention
Beaucoup pensent que select * coûte le même prix qu'un select quantite, puisque les deux gardent toutes les lignes. En stockage en colonnes, select * lit les sept colonnes. Nommez seulement les colonnes utiles.
Un agrégat résume une colonne entière
Un agrégat résume plusieurs lignes en une valeur : sum additionne, avg fait la moyenne, count compte, min et max donnent les extrêmes.
Seul, il résume toute la table. Avec group by, il donne une ligne par valeur différente d'une colonne. C'est le travail de base d'une requête d'analyse.
À vous : un résumé par magasin
-- Une ligne par magasin : le total et le nombre de ventes.
select magasin, sum(quantite) as total_quantite, count(*) as nb_ventes
from ventes
group by magasin
order by magasin;
-- Changez group by magasin en group by produit, sans oublier le select et le order by.