Bonnes pratiques SQL : des requêtes correctes, rapides et maintenables

SQL est un langage déclaratif : vous décrivez le résultat voulu et l'optimiseur de la base décide comment le produire. C'est libérateur, et c'est aussi la raison pour laquelle tant de requêtes lentes ont l'air parfaitement raisonnables. L'optimiseur ne peut choisir un bon plan que si la requête lui en laisse la possibilité, si le schéma lui fournit les index et si l'application ne ruine pas tout en appelant la requête mille fois dans une boucle. Les pratiques qui suivent valent pour n'importe quel moteur relationnel ; les exemples utilisent la syntaxe de PostgreSQL 16, et les différences avec Oracle sont signalées quand elles comptent.

Demandez exactement les lignes et les colonnes dont vous avez besoin

La ligne la moins chère est celle que vous ne récupérez jamais. Deux habitudes en découlent directement.

Nommez vos colonnes. SELECT * transfère des colonnes que vous n'utiliserez pas, empêche les index-only scans (l'index ne peut pas servir une requête qui touche des colonnes qu'il ne contient pas) et lie votre code à l'ordre des colonnes, ce qui vous rattrape le jour où quelqu'un ajoute une colonne au milieu de la table.

Filtrez et agrégez dans la base, pas dans l'application. Charger 200 000 lignes en mémoire pour additionner une colonne en Java est plus lent, consomme plus de mémoire et monopolise une connexion plus longtemps que SELECT SUM(amount) FROM payment WHERE .... Le même raisonnement vaut pour le tri et le comptage : ORDER BY et COUNT(*) ont leur place en SQL, où ils peuvent s'appuyer sur les index et sur des workers parallèles.

Les ORM rendent cette erreur facile à commettre. Avec une collection chargée en lazy, parcourir 1 000 commandes en appelant order.getLines() sur chacune déclenche 1 001 requêtes. C'est le problème N+1, et le remède est toujours le même : une seule requête qui joint ou récupère les lignes filles en un seul ensemble.

-- One round trip instead of 1,001
SELECT o.id, o.placed_at, l.sku, l.quantity, l.unit_price
FROM   customer_order o
JOIN   order_line l ON l.order_id = o.id
WHERE  o.customer_id = $1
ORDER  BY o.placed_at DESC, o.id, l.line_no;

En JPA, cela passe par un JOIN FETCH ou un entity graph ; avec Spring Data, par @EntityGraph ou une projection. Le guide Spring Data JPA montre les deux.

Écrivez des prédicats exploitables par l'optimiseur

Un index sur email ne sert à rien pour la requête WHERE LOWER(email) = '[email protected]'. La fonction transforme la colonne, si bien que les valeurs stockées dans l'index ne correspondent plus à ce que la comparaison attend. Un prédicat auquel on peut répondre par une recherche dans un index est dit sargable (de « search argument »). La règle est simple : laissez la colonne nue d'un côté de l'opérateur et placez le calcul de l'autre.

La conversion de type implicite est la variante sournoise du même problème. Si customer_ref est une colonne VARCHAR et que vous écrivez WHERE customer_ref = 12345, PostgreSQL lève une erreur, mais Oracle convertit silencieusement le customer_ref de chaque ligne avec TO_NUMBER et parcourt toute la table. Liez les paramètres avec le bon type JDBC et comparez les dates à des littéraux de date plutôt qu'à des chaînes.

Les prédicats de plage méritent une dernière remarque : un index sur (status, placed_at) répond efficacement à WHERE status = 'PAID' AND placed_at > :since, mais un index sur (placed_at, status) ne peut pas exploiter l'égalité sur status après la plage sur placed_at. L'ordre des colonnes d'un index composite est une décision de conception ; le guide sur les index et les plans d'exécution explique comment le choisir.

Traitez NULL pour ce qu'il est : une valeur inconnue

SQL utilise une logique à trois valeurs. Toute comparaison avec NULL n'est ni vraie ni fausse, mais UNKNOWN, et WHERE ne conserve que les lignes dont la condition vaut TRUE. Chacune des surprises suivantes découle de cette règle :

• WHERE discount <> 0 écarte les lignes où discount IS NULL. Si vous voulez dire « pas de remise ou une remise nulle », écrivez WHERE discount IS NULL OR discount = 0, ou déclarez la colonne NOT NULL DEFAULT 0 pour que la question ne se pose jamais.
• NULL = NULL vaut UNKNOWN, c'est pourquoi WHERE a.col = b.col ne joint jamais deux NULL. PostgreSQL propose IS NOT DISTINCT FROM quand vous voulez réellement que NULL soit égal à NULL.
• x NOT IN (SELECT y FROM t) ne renvoie absolument rien si un seul y est NULL. Utilisez NOT EXISTS.
• COUNT(col) ignore les NULL ; COUNT(*) compte les lignes. AVG(col) ne fait la moyenne que des valeurs non NULL, ce qui correspond ou non à ce que le rapport attend.
• Oracle traite la chaîne vide '' comme NULL dans les colonnes VARCHAR2. PostgreSQL, non. Du code porté de l'un à l'autre ne doit dépendre d'aucun des deux comportements.

COALESCE(col, fallback) est la manière standard et portable de substituer une valeur ; NVL et DECODE chez Oracle, ou IFNULL chez MySQL, font le même travail avec une moins bonne portabilité. Préférez les expressions CASE pour tout ce qui dépasse une simple valeur de repli.

Jointures, EXISTS et IN

Utilisez la syntaxe de jointure ANSI explicite (JOIN ... ON) plutôt que d'énumérer les tables dans FROM et de joindre dans WHERE. Elle sépare les conditions de jointure des filtres, lève toute ambiguïté sur les jointures externes et correspond à ce qu'attendent tous les optimiseurs modernes et tous les relecteurs de code. La notation (+) d'Oracle pour les jointures externes fonctionne toujours, mais elle relève de l'héritage.

Choisissez le type de jointure en fonction du résultat voulu, pas par habitude :

• INNER JOIN quand une ligne sans correspondance doit disparaître.
• LEFT JOIN quand la ligne de gauche doit subsister sans correspondance. Un filtre sur la table de droite placé dans WHERE (plutôt que dans ON) retransforme la jointure gauche en jointure interne, car les lignes sans correspondance y ont NULL et échouent au filtre.
• EXISTS quand vous posez une question fermée sur des lignes liées sans avoir besoin de leurs colonnes. Il s'arrête à la première correspondance et ne duplique jamais les lignes, ce que ferait une jointure si la table fille contient plusieurs correspondances.
• IN (subquery) pour de petits ensembles de valeurs sans NULL. Les optimiseurs réécrivent de toute façon la plupart des sous-requêtes IN en semi-jointures, si bien que l'écart de performance avec EXISTS est généralement nul ; l'écart sémantique autour de NULL, lui, ne l'est pas.

-- Customers with at least one unpaid invoice: no duplicates, no NULL trap
SELECT c.id, c.name
FROM   customer c
WHERE  EXISTS (
  SELECT 1
  FROM   invoice i
  WHERE  i.customer_id = c.id
  AND    i.status = 'UNPAID'
);

Ajouter DISTINCT pour faire disparaître des doublons est un signal d'alerte : les doublons signifient que la jointure démultiplie les lignes, et DISTINCT masque la cause derrière un tri supplémentaire. De même, UNION dédoublonne et trie ; UNION ALL ne le fait pas, et c'est ce qu'il vous faut quand les deux côtés ne peuvent pas se recouvrir.

Des paramètres liés, toujours

Concaténer des saisies utilisateur dans du SQL est la cause même de l'injection SQL, qui figure encore parmi les risques d'injection du Top 10 de l'OWASP, des décennies après que les requêtes préparées ont rendu cette concaténation inutile. Les paramètres liés (? en JDBC, $1 dans le protocole de PostgreSQL, :name chez Oracle et dans le NamedParameterJdbcTemplate de Spring) envoient séparément l'instruction et les valeurs, de sorte qu'une valeur ne peut jamais modifier la structure de l'instruction.

Il y a aussi une raison de performance. Oracle met en cache les instructions analysées dans la shared pool, indexées par leur texte ; mille variantes truffées de littéraux de la même requête provoquent mille hard parses et de la contention sur les latches. Les requêtes préparées côté serveur de PostgreSQL et le paramètre prepareThreshold du driver JDBC apportent le même bénéfice pour les requêtes répétées.

// JDBC: the value is a parameter, never part of the SQL text
try (PreparedStatement ps = conn.prepareStatement(
        "SELECT id, total FROM customer_order WHERE customer_id = ? AND placed_at >= ?")) {
    ps.setLong(1, customerId);
    ps.setObject(2, since);           // java.time.LocalDate maps to DATE
    try (ResultSet rs = ps.executeQuery()) { /* ... */ }
}

La seule chose que vous ne pouvez pas lier, c'est un identifiant : les noms de tables et de colonnes doivent provenir d'une liste blanche définie dans votre code, jamais d'une saisie.

Pagination et tri

Un ORDER BY sur un gros résultat, sans index fournissant déjà cet ordre, impose un tri qui peut déborder sur disque. Ne triez que si celui qui consomme le résultat a besoin d'un ordre, et quand vous paginez, assurez-vous que cet ordre est total : ORDER BY placed_at seul est ambigu pour des lignes aux horodatages identiques et peut afficher la même ligne sur deux pages. Ajoutez la clé primaire pour départager.

La pagination par OFFSET lit puis jette toutes les lignes qui précèdent la page ; la page 1 000 coûte mille fois la page 1. La pagination par clé (keyset) mémorise la dernière ligne vue et demande les lignes qui la suivent :

-- First page
SELECT id, placed_at, total
FROM   customer_order
WHERE  customer_id = $1
ORDER  BY placed_at DESC, id DESC
LIMIT  20;

-- Next page: pass the last row's (placed_at, id) back in
SELECT id, placed_at, total
FROM   customer_order
WHERE  customer_id = $1
AND    (placed_at, id) < ($2, $3)      -- row-value comparison
ORDER  BY placed_at DESC, id DESC
LIMIT  20;

Avec un index sur (customer_id, placed_at DESC, id DESC), chaque page se résume à un court parcours de plage d'index, quelle que soit la profondeur. Oracle utilise FETCH FIRST 20 ROWS ONLY (à partir de la 12c) au lieu de LIMIT ; le principe de la pagination par clé est identique.

Transactions et traitement par lots depuis le code applicatif

Toute instruction s'exécute dans une transaction ; la question est de savoir si vous en maîtrisez les bornes. L'auto-commit transforme une boucle de 10 000 insertions en 10 000 transactions, chacune attendant un flush du journal. Englobez le lot dans une seule transaction et utilisez l'API batch du driver pour que les instructions voyagent en bloc :

conn.setAutoCommit(false);
try (PreparedStatement ps = conn.prepareStatement(
        "INSERT INTO event (id, occurred_at, payload) VALUES (?, ?, ?::jsonb)")) {
    for (Event e : events) {
        ps.setObject(1, e.id());           // UUID
        ps.setObject(2, e.occurredAt());   // OffsetDateTime -> timestamptz
        ps.setString(3, e.payloadJson());
        ps.addBatch();
        if (++n % 500 == 0) ps.executeBatch();   // keep memory bounded
    }
    ps.executeBatch();
    conn.commit();
}

Le driver JDBC de PostgreSQL réécrit les insertions par lots en instructions INSERT multi-lignes quand reWriteBatchedInserts=true ; celui d'Oracle gère les lots nativement. Dans les deux cas, gardez des transactions courtes : une transaction qui reste ouverte pendant que l'application dialogue avec une API distante conserve ses verrous et, sous PostgreSQL, empêche le vacuum de faire le ménage.

Pour des mises à jour qui touchent une large part d'une table, un seul UPDATE ... WHERE ensembliste bat n'importe quelle boucle ; pour des changements vraiment massifs, envisagez de découper par plage de clés afin que chaque transaction reste assez petite pour ne pas faire gonfler l'undo log ou le WAL.

Les choix de schéma qui façonnent chaque requête

Certains problèmes « de requête » sont en réalité des problèmes de modèle. Trois règles en évitent la plupart :

1. Déclarez les clés étrangères et indexez la colonne qui référence. Sans la contrainte, des lignes orphelines s'infiltrent et l'optimiseur perd une information qu'il pourrait exploiter pour éliminer des jointures. Sans l'index, chaque suppression dans la table parente parcourt toute la table fille pour vérifier les références.
2. Utilisez le bon type. Les dates dans des colonnes DATE/TIMESTAMP, pas dans des chaînes ; les montants en NUMERIC, pas en virgule flottante ; des identifiants du même type des deux côtés de chaque jointure pour qu'aucune conversion ne soit nécessaire. Si vous générez les identifiants dans l'application, les colonnes UUID les stockent sur 16 octets au lieu de 36 caractères (un générateur d'UUID est pratique pour alimenter des données de test).
3. Anticipez la croissance et la purge. Une table qui ne fait que grossir a besoin d'une politique de rétention dès le premier jour ; effacer 100 millions de lignes plus tard est bien plus difficile que de supprimer une partition.

Le guide sur la normalisation traite en profondeur le volet conception.

Erreurs courantes

• Une fonction sur la colonne indexée. WHERE UPPER(name) = 'SMITH' parcourt toute la table. Stockez les données sous la forme dans laquelle vous les recherchez, ou créez un index sur l'expression UPPER(name).
• Filtrer le côté externe d'un LEFT JOIN dans le WHERE. LEFT JOIN address a ON ... WHERE a.country = 'FR' devient silencieusement une jointure interne. Déplacez la condition dans la clause ON.
• NOT IN avec une sous-requête pouvant renvoyer NULL. Ne renvoie rien. Utilisez NOT EXISTS.
• Tout récupérer, puis filtrer dans le code. Un findAll() suivi d'un filtre sur un stream fait transiter toute la table sur le réseau. Poussez le prédicat dans la requête.
• Un ORDER BY sans critère de départage dans les requêtes paginées. Des lignes se répètent ou disparaissent d'une page à l'autre. Ajoutez la clé primaire.
• Les hints comme première réponse à la lenteur. Un /*+ PARALLEL */ Oracle ou un index forcé masque le mauvais prédicat à l'origine du problème et cesse de fonctionner le jour où la distribution des données change. Corrigez la requête, puis mesurez avec le plan.
• Joindre des vues sur des vues. Les vues génériques embarquent des colonnes et des jointures dont la requête n'a pas besoin, et l'optimiseur ne parvient pas toujours à les élaguer. Interrogez les tables de base ou écrivez une vue dédiée.

Bonnes pratiques

• Formatez le SQL de manière homogène et donnez aux tables des alias courts et parlants (customer_order o, pas t1) ; une requête est lue bien plus souvent qu'elle n'est écrite.
• Préférez le SQL standard (COALESCE, CASE, FETCH FIRST, jointures ANSI) aux fonctions propriétaires, sauf si la fonctionnalité de l'éditeur apporte un vrai gain de performance.
• Examinez le plan d'exécution de chaque requête qui touche une grosse table avant sa mise en production, puis à nouveau quand les données ont décuplé.
• Gardez des transactions courtes aux bornes explicites ; n'en laissez jamais une ouverte pendant une interaction utilisateur ou un appel externe.
• Laissez la base garantir l'intégrité : NOT NULL, CHECK, UNIQUE et les clés étrangères attrapent des bugs que la validation applicative laisse passer en situation de concurrence.
• Ne stockez du JSON dans une colonne jsonb que pour des données réellement variables ; les colonnes relationnelles restent plus rapides à indexer, à contraindre et à interroger. Si vous le faites, validez les documents avec un formateur JSON avant de déboguer une insertion mal formée.

Points clés à retenir

• Ne récupérez que les colonnes et les lignes dont vous avez besoin, et confiez le filtrage, le tri et l'agrégation à la base.
• Un prédicat est sargable quand la colonne indexée est seule d'un côté de la comparaison ; une fonction ou une conversion implicite appliquée à la colonne force un parcours complet.
• NULL signifie inconnu : les comparaisons avec lui donnent UNKNOWN, NOT IN avec une sous-requête contenant un NULL ne renvoie rien, et COUNT(col) l'ignore.
• Utilisez EXISTS pour savoir « s'il existe une ligne liée », JOIN quand vous avez besoin des colonnes liées, et UNION ALL sauf si le dédoublonnage est vraiment nécessaire.
• Liez chaque valeur : la concaténation est à la fois un risque d'injection et un fléau pour le cache d'analyse.
• Paginez par clé avec un ordre total, pas avec un OFFSET profond.
• Regroupez les écritures en lots dans des transactions explicites et courtes, et indexez chaque clé étrangère.