Le SQL, le Green IT grâce aux petits gestes du quotidien

Nous voilà enfin arrivés au cœur de la data, la partie qui concerne tous les acteurs ayant à faire des requêtes. Le SQL, langage très populaire, sert à requêter nos bases de données. Tellement populaire que, peu importe la techno, elle tentera de se rapprocher au plus près de ce langage. Connaître les bonnes pratiques pour limiter la consommation est très utile tant son utilisation est vaste.

La clause LIMIT

Une base simple mais efficace : utilisez le LIMIT quand vous devez récupérer un échantillon de données. Que ce soit pour tester une requête ou pour des extractions partielles, utilisez toujours le LIMIT pour ne pas engendrer de traitements inutiles sur l’ensemble de la base de données. Limiter le nombre de lignes traitées évite d’afficher des milliers, voire des millions de lignes, alors qu’on veut juste voir les deux premières pour contrôler le contenu des colonnes.

Déjà qu’on fait souvent un SELECT * pour vérifier la table, ne pas utiliser la clause LIMIT, c’est comme mettre le paquet de 1 kg de pâtes dans l’eau quand on est seul à manger.

Cela se présente sous cette forme, et c’est vraiment simple à appliquer :

SELECT *  
FROM matable  
LIMIT 10;

Stop au SELECT *

Faire un SELECT *, c’est tentant, mais ça a des conséquences non négligeables sur votre puissance de calcul consommée !
Il est crucial de toujours sélectionner uniquement les colonnes dont vous avez besoin. Auquel cas, vous risquez de surcharger inutilement votre requête, ce qui impacte le temps de traitement, mais aussi la consommation de ressources matérielles et énergétiques.

Cela est d’autant plus vrai dans les bases de données orientées colonnes (donc une bonne partie des data warehouses). Ces bases vont devoir utiliser de la mémoire pour ramener des colonnes inutilisées. Contrairement aux bases de données orientées ligne, qui chargent l’ensemble des données, seules les données affichées représentent un surcoût de consommation.

En bref, soyez précis dans vos sélections :

SELECT colonne_a, colonne_b  
FROM matable;

Clause WHERE dès le début et sans agrégation

Une autre bonne pratique consiste à appliquer vos filtres WHERE au début de la requête, avant de faire toute agrégation. Cela évite de travailler sur des données que vous ne souhaitez pas traiter. Par exemple, filtrez sur les dates ou les catégories pertinentes dès le début afin que seules les données pertinentes soient agrégées. C’est la même logique que dans les articles précédents : on ne va pas tout ramener pour en prendre qu’un dixième.

Par exemple, on ne fera pas :

SELECT categorie, SUM(montant) AS total_ventes  
FROM ventes  
GROUP BY categorie  
HAVING date_vente >= '2024-01-01' AND categorie = 'Électronique';

Mais on fera :

SELECT categorie, SUM(montant) AS total_ventes  
FROM ventes  
WHERE date_vente >= '2024-01-01'  
  AND categorie = 'Électronique'  
GROUP BY categorie;

Car on filtre les données dès le début et pas tout à la fin comme dans le premier exemple.
Autre chose, tant qu’on est avec le WHERE, il est préférable d’éviter toute forme de calcul ou d’agrégation sur la colonne filtrée, car cela oblige la base de données à effectuer des calculs avant de déterminer si la donnée doit être conservée.

Donc on ne fera pas :

SELECT *  
FROM ventes  
WHERE YEAR(date_vente) = 2024;

Mais on fera :

SELECT *  
FROM ventes  
WHERE date_vente >= '2024-01-01' AND date_vente < '2025-01-01';

(Bon, si vous avez suivi, le SELECT * ce n’est pas bien.)

Jointures

Les jointures, ça se travaille avec soin ! Il faut éviter de faire des jointures sur des colonnes non indexées, et surtout, il est important de faire en sorte que les données soient bien préparées pour les jointures avant de les exécuter. Trop souvent, des jointures mal optimisées entraînent des coûts de traitement facilement évitables.

Tout d’abord, avant d’effectuer une jointure, il est important de suivre les conseils précédents pour éviter de joindre des données inutiles.
Je vais ensuite vous donner une liste de conseils pour bien les optimiser :

• Faire toujours des jointures via des JOIN plutôt que des WHERE. Ce type de requête est à bannir :

SELECT v.*, p.*  
FROM ventes v, produits p  
WHERE v.produit_id = p.id;

Au profit de l’utilisation du JOIN :

SELECT v.*, p.*  
FROM ventes v  
JOIN produits p ON v.produit_id = p.id;

• Utiliser des colonnes indexées, donc préchargées pour ce type d’opérations.
• Mettre les colonnes à joindre au bon format. Si nos colonnes à joindre n’ont que des ID sous forme de chiffres, ne mettez pas la colonne au format string par facilité, mais au format au plus proche des valeurs (integer, double… vous êtes grands, à vous de choisir).
• Si vous devez joindre plusieurs tables, vous pouvez créer un sous-ensemble de données à l’aide d’une requête WITH ou d’une Common Table Expression (CTE) pour restreindre la taille des données avant la jointure.

Par exemple :

WITH produits_electroniques AS (  
  SELECT id, nom  
  FROM produits  
  WHERE categorie = 'Électronique'  
)  
SELECT v.*, p.nom  
FROM ventes v  
JOIN produits_electroniques p ON v.produit_id = p.id;

Choix entre vue et table

Lors de notre utilisation des BDD, il peut y avoir le réflexe et la simplicité de créer des tables. Cependant, si vous avez bien suivi, il faut bien cibler notre utilisation pour y trouver un outil adapté. Dans notre cas, cela sera l’utilisation des vues.

Une vue dans une base de données est une requête SQL stockée qui va permettre de faire une requête SQL comme on appelle une table. Son intérêt est que cela ne va pas dupliquer les données dans notre BDD et donc économiser de l’espace, en plus d’être à jour en temps réel, contrairement à une table qui doit être alimentée par un workflow.

Des vues, il en existe différents types, qui existent ou non en fonction de notre base de données. À nous de nous renseigner sur notre système de stockage et de nous interroger sur le meilleur choix. Cependant, voici un petit tableau récapitulatif :

• Vues classiques : utilisées pour les requêtes simples ou lorsque vous souhaitez abstraire la logique sans stockage permanent.
• Vues matérialisées : idéales pour des requêtes complexes fréquentes où les résultats peuvent être stockés et mis à jour périodiquement.
• Vues indexées : optimisent encore plus les vues matérialisées en ajoutant des index pour des performances accrues.
• Vues partitionnées : spécifiques pour les données partitionnées, elles permettent un accès plus rapide aux partitions pertinentes.
• Vues paramétrées : utilisées pour réutiliser la même logique de requête avec des filtres dynamiques.
• Vues de sécurité : restreignent l’accès aux données sensibles, simplifiant la gestion des permissions.
• Vues récursives : parfaites pour les données hiérarchiques, elles permettent de gérer facilement des structures en arbre.
• Vues temporaires : idéales pour les calculs ou données temporaires dans une session, elles disparaissent une fois la session terminée.
• Tables classiques : stockage permanent des données.
• Tables partitionnées : optimisation des accès aux sous-ensembles de données via des partitions physiques (bases volumineuses avec données partitionnables par pays, par exemple).
• Tables temporaires : stockage temporaire pour des données intermédiaires, ne persistant pas au-delà de la session.

Index et partition

Trop souvent oubliés, l’indexation et le partitionnement sont aussi des outils d’optimisation.

L’indexation permet d’accélérer les recherches et de réduire les scans de table complets, ce qui réduit la consommation de ressources et améliore la performance des requêtes. Il faut l’utiliser sur les colonnes où vous avez souvent des requêtes, des WHERE, des JOIN, ORDER BY, etc. Vous constaterez une nette amélioration de la vitesse de la requête, tout en réduisant sa consommation. Attention cependant : si la table est souvent modifiée, l’écriture prendra plus de temps (car il faudra recharger l’index) et cela prend aussi un peu de mémoire. Il est donc essentiel de bien choisir le moment et le contexte pour appliquer ces recommandations.

En résumé, vous avez une table commandes contenant des millions de lignes. Si vous effectuez fréquemment des requêtes pour rechercher les commandes d’un client spécifique, l’ajout d’un index sur la colonne client_id rendra ces requêtes beaucoup plus rapides.

Les partitions, quant à elles, permettent de diviser vos données en segments plus petits et plus gérables, ce qui facilite le traitement et la gestion des données. Comme pour les index, si les partitions sont mal utilisées, cela peut créer l’effet inverse. Il faut les utiliser sur des tables à forte volumétrie où les modifications se font souvent par type de données (pays, données temporelles, etc.).

Pour illustrer, si vous avez une table Transactions contenant plusieurs milliards de lignes réparties sur plusieurs années, partitionner la table par années (ou mois) vous permettrait de limiter les requêtes à une seule partition (par exemple, l’année en cours), améliorant ainsi les performances. Cela facilite également la suppression de données obsolètes stockées dans d’autres partitions.

Nous avons donc pu voir les principaux moyens pour optimiser vos requêtes SQL. Le SQL étant partout dans la data, avoir de bonnes pratiques peut avoir un impact considérable sur la consommation associée. Une requête pouvant être utilisée plusieurs fois par jour, la récurrence d’un défaut d’optimisation ne sera pas négligeable à terme.

Le SQL étant utilisable dans Spark, vous avez à présent une bonne base pour optimiser les spark.sql.

On ira cependant plus loin dans le prochain article (et le dernier) et nous verrons comment optimiser votre code Spark avec PySpark comme base.