Interroger et optimiser

Un entrepôt bien modélisé rend les requêtes prévisibles : elles ont toutes la même forme, une table de faits jointe à quelques dimensions, filtrée puis regroupée. Cette régularité est ce qui permet à un métier d'écrire ses propres analyses, et à un moteur de les optimiser.

Elle ne suffit pas. Une requête impeccable sur cinq milliards de lignes reste lente si rien n'a été prévu pour lui éviter de les lire toutes.

Traduire une demande en requête

Quatre questions, dans cet ordre, et la requête s'écrit toute seule. Quelle mesure ? Ce qu'on additionne ou compte : elle vient de la table de faits. Une moyenne s'en déduit, à partir d'une somme et d'un compte.

À quel grain de restitution ? Les mots qui suivent « par ». Ils deviennent le GROUP BY et les colonnes de dimension à joindre.

Quels filtres, et sur quoi portent-ils ? Un filtre sur une dimension va dans le WHERE. Un filtre sur la mesure agrégée va dans le HAVING, et nulle part ailleurs.

Quel tri, quelle limite ? Souvent implicites dans la demande, et toujours utiles à expliciter.

Le bloc suivant construit un petit entrepôt en étoile et pose la question de l'introduction. Il s'exécute dans la page, et se modifie.

La forme est toujours la même : la table de faits, autant de jointures que de dimensions citées, un WHERE qui filtre sur les dimensions, un GROUP BY qui reprend exactement les colonnes affichées non agrégées. C'est le SQL du chapitre sur les agrégats, les sous-requêtes et les vues, sans une clause de plus : ce qui change ici n'est pas le langage, c'est le volume auquel il s'applique et la façon dont les données sont rangées en dessous.

Les opérations d'analyse

Le métier ne demande presque jamais une requête isolée : il explore, en partant d'un chiffre global et en descendant vers le détail. Ces mouvements portent des noms.

Ces cinq mouvements se traduisent tous par des variations minimes de la même requête : changer une colonne du GROUP BY, ajouter une condition au WHERE, permuter deux colonnes d'affichage. C'est cette régularité qui permet aux outils de restitution de les offrir par un clic, et c'est un bon test du modèle : si une exploration exige de réécrire la requête entièrement, le modèle est mal découpé.

Le bloc exécutable du début s'y prête. Remplacer t.trimestre par t.mois dans le SELECT, le GROUP BY et le ORDER BY est un forage : le mobilier se sépare en janvier (178), avril (249) et mai (445). Ajouter AND t.trimestre = 2 au WHERE est une tranche : il ne reste que le deuxième trimestre.

Un tableau croisé dynamique offre ces mouvements par des menus. Celui-ci part d'une table de faits déjà jointe à ses dimensions, une étoile aplatie, et montre d'abord le chiffre d'affaires par année, tous magasins confondus.

Aucun de ces gestes n'invente un calcul : chacun est une variante de la même requête sur la table de la figure. Le tableau de départ est SELECT annee, SUM(montant) FROM ventes GROUP BY annee. Le forage ajoute trimestre au GROUP BY, et l'agrégation l'en retire. La tranche ajoute region = 'Ouest' au WHERE, et une découpe y ajouterait une seconde condition, famille = 'Mobilier' par exemple. La rotation ne change même pas la requête, seulement la disposition de son résultat, ce qui explique que ses totaux ne bougent pas. Les opérations d'analyse ne sont donc que des changements de GROUP BY et de WHERE, et l'outil qui les offre en un clic ne fait que réécrire ces deux clauses.

Ce qui rend une requête lente

Le temps de réponse est dicté par le volume lu et déplacé, pas par la complexité écrite. Sur de gros volumes, une requête passe l'essentiel de son temps à lire des données depuis le disque ou le réseau, puis à faire circuler entre les machines celles qu'elle doit rapprocher. La longueur du texte ne compte presque pas, une jointure vers une petite dimension non plus. Une jointure entre deux grandes tables compte, par le mélange qu'elle impose : c'est l'objet du regroupement par hachage, plus bas. Ce qui compte est le nombre d'octets que le moteur est obligé de parcourir, puis de déplacer.

Trois leviers réduisent le volume lu :

Ne lire que les colonnes utiles, ce que permet un stockage en colonnes.

Ne lire que les partitions utiles, ce que permet un partitionnement aligné sur les filtres.

Ne lire que les fichiers utiles, ce que permettent les statistiques minimum et maximum conservées par fichier, à condition que les fichiers soient rangés selon la colonne filtrée.

La même requête, sur seize ventes, sous trois rangements : la figure compte les octets que le moteur lit, et ceux dont le résultat dépend. Rangée « en blocs », la table est découpée en groupes de quatre lignes qui gardent chacun le minimum et le maximum de leurs clients, comme les fichiers du programme plus bas.

Le troisième levier a une condition que les deux autres n'ont pas : des statistiques ne permettent d'écarter un fichier que si ses valeurs sont regroupées. Des fichiers écrits dans l'ordre d'arrivée sont rangés par date, pas par client, et le bloc suivant le mesure.

Sur la date, 13 fichiers sur 146 suffisent pour un mois. Sur un client, chaque fichier couvre presque tous les clients entre son minimum et son maximum, et rien n'est écarté. Trier par client avant d'écrire ramène la même requête à un seul fichier, au prix de l'ordre par date : un rangement sert un filtre, rarement deux. C'est pourquoi l'usage combine les deux : partitionner par date, puis ranger par client à l'intérieur de chaque partition.

Un quatrième levier agit en amont : ne pas recalculer ce qui l'a déjà été. Une table d'agrégats conserve le résultat d'un regroupement fréquent, par exemple le chiffre d'affaires par famille et par mois, calculé une fois à chaque alimentation. Une requête posée à ce grain, ou à un grain plus large quand la mesure s'additionne, y lit quelques milliers de lignes au lieu de plusieurs milliards. PostgreSQL l'automatise sous le nom de vue matérialisée, rafraîchie à la demande ; SQLite, le moteur de la page, n'en a pas, et une table remplie par INSERT INTO ... SELECT en tient lieu. Le prix est double : chaque alimentation doit la recalculer, et elle ne sert que les requêtes qui tombent à son grain ou au-dessus. Le chapitre 7 a montré pourquoi un cube n'est jamais matérialisé en entier : la table d'agrégats n'en garde que le grain que les tableaux de bord demandent.

Partitionner

Le raisonnement suivant applique cette règle à la lettre, sur une colonne que presque toutes les requêtes filtrent.

Regrouper par hachage

C'est là qu'est le gain. Une jointure entre deux grandes tables oblige normalement à un mélange complet, celui du chapitre 3, avec tout le trafic réseau que cela représente. Si les deux tables sont regroupées sur la colonne de jointure avec le même nombre de fichiers, chaque fichier de l'une ne peut se joindre qu'au fichier correspondant de l'autre : la jointure se fait localement, sans mélange.

Mettre en pratique

Ce qui rend une requête lente sur de gros volumes n'est presque jamais sa complexité : c'est la quantité de données qu'elle lit. Le partitionnement se juge donc sur ce seul nombre.

Partitionner et regrouper ne répondent pas à la même question. Le partitionnement sert à ÉCARTER des données sans les lire, et il ne fonctionne que si la requête filtre sur la clé choisie. Un partitionnement par jour n'accélère en rien une requête qui filtre par client.
Le regroupement par hachage sert à rapprocher les lignes qui se joindront, pour éviter de les faire traverser le réseau. Il ne crée aucune partition : il range. Comme toutes les lignes d'une même valeur sont dans le même fichier, un filtre d'égalité sur sa colonne permet aussi à un moteur qui exploite ce rangement de n'ouvrir qu'un fichier par partition ; Spark le fait depuis sa version 2.4.
Choisir la clé de partitionnement revient donc à parier sur le filtre le plus fréquent, et ce pari s'écrit à partir des requêtes réellement posées, jamais à partir de la structure des données.

SELECT p.libelle, SUM(f.quantite) AS volume
FROM Fait_Vente f
JOIN Dim_Produit p ON p.idProduit = f.idProduit
JOIN Dim_Temps   t ON t.idTemps   = f.idTemps
JOIN Dim_Magasin m ON m.idMagasin = f.idMagasin
WHERE t.annee = 2024 AND t.trimestre = 1
  AND m.region = 'Nord'
  AND f.estRetour = 0
GROUP BY p.idProduit, p.libelle
ORDER BY volume DESC
LIMIT 10;