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

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.

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é.

Ce qui rend une requête lente

Partitionner

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.

Exercices type

Exercice 1 : traduire « les dix produits les plus vendus en volume au premier trimestre 2024, dans les magasins du Nord ».

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.libelle
ORDER BY volume DESC
LIMIT 10;

Exercice 2 : une requête renvoie un chiffre d'affaires trois fois trop élevé depuis qu'une dimension a été historisée. Pourquoi ?

Exercice 3 : une table de 4 téraoctets est partitionnée par jour sur cinq ans, et chaque partition est encore découpée par région, avec vingt régions. Que se passe-t-il ?

Exercice 4 : deux tables de plusieurs milliards de lignes sont jointes chaque nuit sur l'identifiant client, et le traitement passe l'essentiel de son temps en transfert réseau. Que proposer ?

La méthode

1. Poser les quatre questions : quelle mesure, à quel grain, quels filtres, quel tri.
2. Placer chaque filtre selon qu'il porte sur une ligne ou sur un agrégat.
3. Vérifier le nombre de lignes avant et après chaque jointure vers une dimension.
4. Joindre sur la clé de substitution, jamais sur la clé naturelle d'une dimension historisée.
5. Regarder les filtres réellement écrits avant de choisir une clé de partitionnement.
6. Viser des partitions de quelques centaines de mégaoctets, et de l'ordre du millier au total.
7. Regrouper par hachage les tables jointes en permanence, avec le même nombre de fichiers des deux côtés.
8. Mesurer le volume lu, pas la durée seule : c'est lui qui explique la durée.

Synthèse

• Quatre questions suffisent à traduire une demande : mesure, grain, filtres, tri.
• Une requête en étoile a toujours la même forme : faits, jointures, WHERE, GROUP BY.
• WHERE filtre les lignes avant regroupement, HAVING filtre les groupes après.
• Une jointure vers une dimension historisée duplique les faits si elle ne restreint pas à la bonne version.
• Le contrôle qui l'attrape : comparer le nombre de lignes avant et après.
• Cinq mouvements d'analyse : agrégation, forage, tranche, découpe, rotation.
• Si une exploration exige de réécrire la requête, le modèle est mal découpé.
• Le temps de réponse est dicté par le volume lu, pas par la complexité écrite.
• Le partitionnement permet d'ignorer des données sans les ouvrir : c'est l'optimisation la plus rentable.
• Une partition ne sert que si les requêtes filtrent dessus.
• Trop partitionner recrée le problème des petits fichiers.
• Le regroupement par hachage aligne deux tables et supprime le mélange d'une jointure.
• Les deux se combinent : partitionner par date, regrouper par identifiant.