Cours
Manipulation de données en SQL
4 h
335.1K
Devenez certifié SQL
Prouvez que vos compétences en SQL sont prêtes à l'emploi grâce à une certification.
Traitement SQL et exécution des requêtes
Pour améliorer les performances de vos requêtes SQL, il faut d’abord comprendre ce qui se passe en interne lorsque vous lancez leur exécution.
Première étape : la requête est analysée pour produire un « arbre de syntaxe ». Elle est vérifiée pour s’assurer qu’elle respecte les contraintes syntaxiques et sémantiques. Le parseur crée une représentation interne de la requête, ensuite transmise au moteur de réécriture.
Vient alors le rôle de l’optimiseur : trouver le plan d’exécution optimal pour la requête. Le plan définit précisément quel algorithme est utilisé pour chaque opération et comment leur exécution est coordonnée.
Pour identifier le meilleur plan, l’optimiseur énumère les plans possibles, estime le coût de chacun, tient compte de l’état courant de la base, puis choisit le meilleur. Comme les optimiseurs ne sont pas infaillibles, les utilisateurs et administrateurs doivent parfois examiner et régler manuellement les plans proposés pour gagner en performance.
Vous vous demandez sans doute ce qu’est un « bon plan d’exécution ».
Le « coût » d’un plan joue un rôle central : nombre d’entrées/sorties disque nécessaires, coût CPU, temps de réponse côté client et temps d’exécution total. C’est ici qu’intervient la notion de complexité temporelle, que nous aborderons plus loin.
Ensuite, le plan choisi est exécuté par le moteur, évalué, et les résultats sont renvoyés.
Rédiger des requêtes SQL
Ce qui n’apparaît pas toujours clairement, c’est que le principe « Garbage In, Garbage Out » s’applique pleinement au traitement des requêtes : la façon dont vous formulez la requête conditionne largement ses performances. Si l’optimiseur reçoit une requête mal écrite, il ne pourra pas faire de miracles.
Autrement dit, il y a des actions que vous pouvez mener dès l’écriture. La responsabilité est double : écrire des requêtes conformes aux bonnes pratiques et repérer où des problèmes de performance risquent d’apparaître.
Un bon point de départ consiste à identifier les « zones à risque » dans vos requêtes. De manière générale, les débutants rencontrent souvent des problèmes de performance au niveau :
- De la clause
WHERE; - Des mots‑clés
INNER JOINouLEFT JOIN; et - De la clause
HAVING;
C’est une approche simple, voire naive, mais utile quand on débute : ce sont là que les erreurs sont fréquentes et souvent difficiles à détecter.
Gardez toutefois à l’esprit que la performance n’a de sens qu’avec du contexte : dire que ces clauses sont « mauvaises » n’a aucun sens en soi. La présence d’un WHERE ou d’un HAVING n’implique pas qu’une requête soit mauvaise.
Poursuivez ci‑dessous pour découvrir des anti‑patterns et des alternatives pour construire vos requêtes SQL. Ces conseils servent de guide : la nécessité de réécrire dépendra du volume de données, du SGBD, de la fréquence d’exécution, etc. Tout dépend de l’objectif de votre requête ; une bonne connaissance préalable de la base à interroger est cruciale !
1. Ne récupérez que les données nécessaires
La mentalité « plus de données = mieux » n’est pas adaptée à l’écriture de requêtes SQL : vous risquez d’étouffer vos analyses avec du superflu, et vos performances peuvent en souffrir si la requête remonte trop de lignes.
Surveillez donc en priorité l’instruction SELECT, la clause DISTINCT et l’opérateur LIKE.
L’instruction SELECT
Dès qu’une requête est écrite, vérifiez que votre SELECT est aussi concis que possible. Objectif : supprimer les colonnes inutiles du SELECT pour ne remonter que les données utiles à votre besoin.
Si vous avez des sous‑requêtes corrélées avec EXISTS, préférez sélectionner une constante dans le SELECT de la sous‑requête plutôt que la valeur d’une colonne réelle. Particulièrement utile quand vous ne faites que vérifier l’existence.
Rappel : une sous‑requête corrélée utilise des valeurs de la requête externe. Et même si NULL peut faire office de « constante », c’est très ambigu !
Exemple d’usage d’une constante :
SELECT driverslicensenr, name FROM Drivers WHERE EXISTS (SELECT '1' FROM Fines WHERE fines.driverslicensenr = drivers.driverslicensenr);Astuce : une sous‑requête corrélée n’est pas toujours idéale. Vous pouvez souvent l’éviter en la réécrivant avec un INNER JOIN :
SELECT driverslicensenr, name FROM drivers INNER JOIN fines ON fines.driverslicensenr = drivers.driverslicensenr; La clause DISTINCT
SELECT DISTINCT renvoie uniquement des valeurs distinctes. À éviter si possible : comme vous l’avez vu, l’ajout de cette clause allonge généralement les temps d’exécution. Demandez‑vous toujours si vous en avez vraiment besoin pour obtenir le résultat souhaité.
L’opérateur LIKE
Avec LIKE, si le motif commence par % ou _, l’index ne sera pas utilisé (s’il existe). La base ne pourra donc pas s’appuyer sur l’index. En outre, ce type de motif risque d’ouvrir la porte à trop d’enregistrements hors de votre cible.
Votre connaissance des données vous aidera à formuler un motif plus discriminant pour ne remonter que les lignes pertinentes.
2. Limitez vos résultats
Si vous ne pouvez pas davantage filtrer le SELECT, pensez à limiter autrement le résultat. Les clauses LIMIT ou TOP et la gestion des types sont utiles ici.
Clauses TOP, LIMIT et ROWNUM
Ajoutez LIMIT ou TOP pour plafonner le nombre de lignes renvoyées. Exemples :
SELECT TOP 3 * FROM Drivers;Remarque : vous pouvez préciser PERCENT, par exemple SELECT TOP 50 PERCENT *.
SELECT driverslicensenr, name FROM Drivers LIMIT 2;Vous pouvez également utiliser ROWNUM, équivalent à LIMIT :
SELECT *FROM DriversWHERE driverslicensenr = 123456 AND ROWNUM <= 3;Conversions de types
Utilisez toujours les types les plus efficaces, donc les plus petits, possibles. Proposer un type surdimensionné peut poser problème.
En revanche, ajouter des conversions de type dans vos requêtes alourdit l’exécution.
Tentez d’éviter au maximum ces conversions. Quand elles sont nécessaires, soyez parcimonieux et testez leur impact avant d’exécuter à grande échelle.
3. N’ajoutez pas de complexité inutile
Dans le prolongement des conversions de types : évitez la sur‑ingénierie. Des requêtes simples et efficaces font souvent mieux. Cela peut paraître évident, mais les exemples suivants montrent qu’on complexifie très vite l’inutile.
L’opérateur OR
Avec OR, il est probable que l’index ne soit pas utilisé.
Rappel : un index est une structure accélérant la lecture, au prix d’écritures supplémentaires et d’espace de stockage. Les index évitent de balayer toutes les lignes à chaque accès.
Sans index, la requête sera plus lente. Cherchez donc des alternatives à OR :
SELECT driverslicensenr, nameFROM DriversWHERE driverslicensenr = 123456OR driverslicensenr = 678910OR driverslicensenr = 345678;Peut être remplacé par :
- Une condition avec
IN; ou
SELECT driverslicensenr, nameFROM DriversWHERE driverslicensenr IN (123456, 678910, 345678);- Deux
SELECTcombinés parUNION.
Astuce : attention à ne pas multiplier inutilement les UNION, qui relisent plusieurs fois la même table et allongent l’exécution. Des alternatives : reformuler pour tout mettre dans un seul SELECT, ou utiliser un OUTER JOIN plutôt qu’un UNION.
Astuce : même si OR (et d’autres opérateurs cités plus loin) désactive souvent l’index, les recherches via index ne sont pas toujours préférables !
L’opérateur NOT
Comme pour OR, NOT conduit fréquemment à ne pas utiliser l’index, ce qui ralentit la requête. Exemple :
SELECT driverslicensenr, nameFROM DriversWHERE NOT (year > 1980);Cette requête est inutilement complexe. Remplacez NOT par un opérateur de comparaison (>, <>, !>, etc.). Par exemple :
SELECT driverslicensenr, nameFROM DriversWHERE year <= 1980;C’est déjà plus clair, n’est‑ce pas ?
L’opérateur AND
AND peut aussi dégrader les performances si vous l’utilisez de façon trop verbeuse :
SELECT driverslicensenr, nameFROM DriversWHERE year >= 1960 AND year <= 1980;Préférez l’opérateur BETWEEN :
SELECT driverslicensenr, nameFROM DriversWHERE year BETWEEN 1960 AND 1980;Les opérateurs ANY et ALL
ANY et ALL méritent également la prudence : ils désactivent souvent l’index. Des alternatives utiles : les fonctions d’agrégat comme MIN ou MAX.
Astuce : attention, les agrégations massives (SUM, AVG, MIN, MAX sur beaucoup de lignes) peuvent rallonger fortement l’exécution. Réduisez alors le nombre de lignes traitées ou précalculez certaines valeurs. Encore une fois, tout dépend de votre environnement et de votre objectif.
Isoler les colonnes dans les conditions
Si une colonne est impliquée dans un calcul ou une fonction scalaire, l’index ne sera pas utilisé. Une solution consiste à isoler la colonne pour la sortir du calcul ou de la fonction. Exemple :
SELECT driverslicensenr, nameFROM DriversWHERE year + 10 = 1980;Peu orthodoxe, n’est‑ce pas ? Reformulez plutôt ainsi :
SELECT driverslicensenr, nameFROM DriversWHERE year = 1970;4. Évitez la « brute force »
N’essayez pas de restreindre à l’excès : cela peut nuire aux performances, notamment avec les jointures et la clause HAVING.
Jointures
- Ordre des tables
Quand vous joignez deux tables de tailles très différentes, envisagez de placer la plus volumineuse en dernier dans la jointure.
- Conditions redondantes
Trop de conditions sur une jointure forcent le moteur à emprunter un chemin qui n’est pas toujours le plus performant.
La clause HAVING
HAVING a été ajouté car WHERE ne s’applique pas aux fonctions d’agrégat. Utilisée avec GROUP BY, elle filtre les groupes renvoyés. Mais elle n’utilise pas l’index et peut donc dégrader la performance.
Alternative : la clause WHERE. Comparez :
SELECT state, COUNT(*) FROM Drivers WHERE state IN ('GA', 'TX') GROUP BY state ORDER BY stateSELECT state, COUNT(*) FROM Drivers GROUP BY stateHAVING state IN ('GA', 'TX') ORDER BY stateLa première limite les lignes à sommer, la seconde agrège tout puis écarte des résultats via HAVING. La première épargne des ressources.
Ici, il ne s’agit pas de limiter le résultat final, mais de réduire le volume intermédiaire véhiculé par la requête.
Remarque : WHERE filtre des lignes individuelles, tandis que HAVING filtre des agrégations ou des résultats dérivés.
Évaluer la qualité, écrire et réécrire des requêtes performantes n’est pas trivial. Éviter les anti‑patterns et envisager des alternatives fait partie de vos responsabilités en contexte professionnel.
Cette liste n’est qu’un aperçu pour débutants. Pour un regard de développeurs plus expérimentés, consultez cette discussion.
Approches ensemblistes vs procédurales
Derrière ces anti‑patterns se cache souvent l’opposition entre approche ensembliste et approche procédurale.
L’approche procédurale ressemble à de la programmation : vous dites au système quoi faire et comment le faire.
Exemples : conditions redondantes dans les jointures, usage abusif de HAVING, enchaînement de fonctions, logique avec boucles, conditions, UDF, curseurs, … On interroge un sous‑ensemble, puis un autre, et ainsi de suite.
On parle aussi d’approche « étape par étape » ou « ligne par ligne ».
L’approche ensembliste, elle, consiste à décrire le quoi et non le comment. Vous spécifiez les conditions/contraintes du résultat souhaité et laissez le moteur choisir les meilleurs algorithmes.
Puisque le SQL est ensembliste par nature, cette approche est généralement plus efficace — expliquant pourquoi, dans certains cas, le SQL dépasse le code.
Astuce L’approche ensembliste est celle que la plupart des employeurs de référence attendent de vous. Vous devrez souvent naviguer entre les deux.
Remarque : si vous tombez sur une requête trop procédurale, envisagez de la réécrire/refactorer.
De la requête au plan d’exécution
Les anti‑patterns évoluent avec votre expérience, et envisager des alternatives peut s’avérer ardu. Toute aide est bienvenue : une démarche plus structurée, outillée, est souvent payante.
Remarque : certains anti‑patterns précités relèvent de la performance (opérateurs AND, OR, NOT et non‑usage d’index). Raison de plus pour une approche à la fois structurée et approfondie.
Cette démarche repose surtout sur le plan d’exécution, résultat du parsing en « arbre », qui définit les algorithmes utilisés et leur orchestration.
Optimisation de requêtes
Comme indiqué en introduction, il peut être nécessaire d’examiner et de peaufiner manuellement les plans produits par l’optimiseur. Pour cela, vous analyserez le plan d’exécution.
Servez‑vous des outils fournis par votre SGBD. Parmi eux :
- Des outils capables de générer une représentation graphique du plan. Exemple :
- D’autres outils fournissent une description textuelle. Par exemple, l’instruction
EXPLAIN PLANsous Oracle. Selon le SGBD, vous trouverez aussiEXPLAIN(MySQL, PostgreSQL) ouEXPLAIN QUERY PLAN(SQLite).
Remarque : sous PostgreSQL, EXPLAIN décrit l’intention du planificateur sans exécuter la requête, tandis que EXPLAIN ANALYZE exécute la requête et compare attendu vs réel. Un plan réel (avec exécution) est généralement plus instructif car il contient des détails et statistiques supplémentaires.
Dans la suite, vous verrez EXPLAIN et ANALYZE pour explorer le plan et anticiper les performances. Nous travaillerons avec deux tables : one_million et half_million.
Pour obtenir les informations courantes sur one_million avec EXPLAIN, placez‑le en tête de la requête :
EXPLAINSELECT *FROM one_million;QUERY PLAN____________________________________________________Seq Scan on one_million(cost=0.00..18584.82 rows=1025082 width=36)(1 row)Ici, le coût est 0.00..18584.82, le nombre de lignes 1025082, la largeur 36.
Vous pouvez rafraîchir les statistiques via ANALYZE.
ANALYZE one_million;EXPLAINSELECT *FROM one_million;QUERY PLAN____________________________________________________Seq Scan on one_million(cost=0.00..18334.00 rows=1000000 width=37)(1 row)Avec EXPLAIN ANALYZE, vous obtenez le temps d’exécution réel :
EXPLAIN ANALYZESELECT *FROM one_million;QUERY PLAN___________________________________________________________Seq Scan on one_million(cost=0.00..18334.00 rows=1000000 width=37)(actual time=0.015..1207.019 rows=1000000 loops=1)Total runtime: 2320.146 ms(2 rows)Inconvénient évident : la requête est effectivement exécutée, prudence donc !Jusqu’ici, vous avez vu le Seq Scan (lecture séquentielle) ou Full Table Scan : chaque ligne de la table est lue en série et évaluée. Côté performance, ce n’est pas idéal car on balaie toute la table. Cela reste acceptable si la table ne tient pas en mémoire : les lectures séquentielles sont rapides, même sur disques lents.
Nous reviendrons dessus en parlant des index scans.
Il existe d’autres algorithmes. Exemple de plan pour une jointure :
EXPLAIN ANALYZESELECT *FROM one_million JOIN half_millionON (one_million.counter=half_million.counter);QUERY PLAN_________________________________________________________________Hash Join (cost=15417.00..68831.00 rows=500000 width=42)(actual time=1241.471..5912.553 rows=500000 loops=1)Hash Cond: (one_million.counter = half_million.counter) -> Seq Scan on one_million (cost=0.00..18334.00 rows=1000000 width=37) (actual time=0.007..1254.027 rows=1000000 loops=1) -> Hash (cost=7213.00..7213.00 rows=500000 width=5) (actual time=1241.251..1241.251 rows=500000 loops=1) Buckets: 4096 Batches: 16 Memory Usage: 770kB -> Seq Scan on half_million (cost=0.00..7213.00 rows=500000 width=5)(actual time=0.008..601.128 rows=500000 loops=1)Total runtime: 6468.337 msIci, l’optimiseur a choisi un Hash Join. Retenez‑le pour estimer la complexité temporelle. Notez qu’il n’y a pas d’index sur half_million.counter. Ajoutons‑en un :
CREATE INDEX ON half_million(counter);EXPLAIN ANALYZESELECT *FROM one_million JOIN half_millionON (one_million.counter=half_million.counter);QUERY PLAN________________________________________________________________Merge Join (cost=4.12..37650.65 rows=500000 width=42)(actual time=0.033..3272.940 rows=500000 loops=1)Merge Cond: (one_million.counter = half_million.counter) -> Index Scan using one_million_counter_idx on one_million (cost=0.00..32129.34 rows=1000000 width=37) (actual time=0.011..694.466 rows=500001 loops=1) -> Index Scan using half_million_counter_idx on half_million (cost=0.00..14120.29 rows=500000 width=5)(actual time=0.010..683.674 rows=500000 loops=1)Total runtime: 3833.310 ms(5 rows)Après création de l’index, l’optimiseur opte pour un Merge join avec des Index Scan.
Remarque : différence entre index scan et full table scan : le premier parcourt des pages de données ou d’index pour trouver les enregistrements, le second lit chaque ligne de la table.
Le temps total diminue et les performances s’améliorent ; toutefois, deux index scans rendent la mémoire plus critique, surtout si la table ne tient pas en RAM. On effectue d’abord un scan séquentiel rapide de l’index, puis de nombreuses lectures aléatoires pour récupérer les lignes par valeur d’index — ces lectures sont bien plus lentes que les lectures séquentielles. Dans ces cas, un full table scan peut être plus rapide qu’un full index scan.
Complexité temporelle et grand O
Après un premier examen du plan, vous pouvez raisonner plus formellement grâce à la théorie de la complexité calculatoire. Il s’agit de classer des problèmes (algorithmes ou requêtes) selon leur difficulté.
Pour les requêtes, on s’intéresse à la durée d’exécution pour obtenir un résultat : la complexité temporelle, exprimable via la notation grand O.
Le grand O exprime la croissance du temps d’exécution par rapport à la taille de l’entrée, en la faisant tendre vers l’infini, et ignore coefficients et termes de plus bas ordre pour se concentrer sur le taux de croissance. On parle d’analyse asymptotique.
En base de données, cela mesure l’allongement du temps d’exécution quand la taille des tables (donc de la base) augmente.
Remarque : la taille d’une base croît non seulement avec les données mais aussi avec les index présents.
Estimer la complexité de votre plan
Le plan d’exécution précise les algorithmes utilisés ; on peut donc exprimer le temps d’exécution en fonction de la taille des tables impliquées (fonction de complexité). Autrement dit, servez‑vous du grand O et du plan pour estimer la complexité et la performance.
Vous trouverez ci‑dessous une idée générale de quatre catégories de complexité et des exemples montrant leur variabilité selon le contexte.
Indice : les index jouent un rôle clé !
Remarque : types d’index, plans et implémentations varient selon les bases ; les complexités ci‑dessous sont générales.
O(1) : temps constant
Un algorithme est en temps constant si sa durée ne dépend pas de la taille de l’entrée. Pour une requête : même durée quelle que soit la taille de la table.Exemple (peu courant) :
SELECT TOP 1 t.* FROM tLa complexité est constante car on sélectionne une ligne arbitraire.
Temps linéaire : O(n)
Temps proportionnel à la taille d’entrée. En base : plus il y a de lignes, plus la durée augmente linéairement.
Exemple : clause WHERE sur une colonne non indexée : nécessite un full table scan (Seq Scan) donc O(n). Chaque ligne doit être lue.
Autre exemple (sans index sur i_id) :
SELECT i_id FROM item;- Les requêtes de comptage (
COUNT(*) FROM TABLE;) sont aussi en O(n), sauf si le total des lignes est stocké quelque part (alors O(1)).
Pour les jointures :
- Un hash join a une complexité attendue O(M + N). On hache la plus petite table puis on parcourt la plus grande en consultant la table de hachage.
- Les merge joins sont généralement O(M + N), mais dépendent fortement des index et du tri :
- Si les deux tables sont déjà triées sur les clés de jointure : O(M + N).
- Si les deux ont un index sur ces colonnes : O(M + N), pas de tri requis.
- Si aucune n’a d’index : tri des deux, donc O(M log M + N log N).
- Si une seule a un index : tri de l’autre seulement, donc O(M + N log N).
- Les nested loops sont en général O(MN). Efficaces si une table est minuscule (moins de 10 lignes), cas fréquent pour des sous‑requêtes renvoyant une seule ligne.
Rappel : un nested join compare chaque enregistrement d’une table à chaque enregistrement de l’autre.
Temps logarithmique : O(log(n))
Durée proportionnelle au logarithme de la taille d’entrée. En base : execution proportionnelle au logarithme de la taille de la base.
C’est le cas des plans avec Index Scan ou index clusterisé. Un index clusterisé a ses feuilles contenant les lignes elles‑mêmes. Un clustered index scan parcourt la structure pour retrouver la ou les lignes.
Exemple (index sur i_id) avec complexité générale O(log(n)) :
SELECT i_stock FROM item WHERE i_id = N; Remarque : sans index, on retombe sur O(n).
Temps quadratique : O(n^2)
Durée proportionnelle au carré de la taille d’entrée. En base : proportionnelle au carré de la taille de la base.
Exemple possible :
SELECT * FROM item, author WHERE item.i_a_id=author.a_id Complexité minimale O(n log(n)), maximale O(n^2) selon les index sur les attributs de jointure.
Pour résumer, cette antisèche aide à estimer la performance selon la complexité.
SQL tuning
Avec le plan d’exécution et la complexité en tête, vous pouvez affiner vos requêtes. Concentrez‑vous notamment sur :
- Remplacer les lectures complètes inutiles sur les grandes tables par des index scans ;
- Vérifier l’ordre optimal des jointures ;
- S’assurer d’un usage optimal des index ; et
- Mettre en cache les scans complets de petites tables.
Aller plus loin en SQL
Bravo ! Vous êtes arrivé·e au bout de cet article, qui vous a offert un premier aperçu de la performance des requêtes SQL. Vous avez découvert des anti‑patterns, l’optimiseur de requêtes et des outils pour revoir, estimer et interpréter la complexité d’un plan d’exécution. Il reste beaucoup à explorer ! Pour approfondir, lisez « Database Management Systems » de R. Ramakrishnan et J. Gehrke.
Pour finir, une citation d’un utilisateur de StackOverflow :
« Mon anti‑pattern préféré est de ne pas tester ses requêtes.
Cela s’applique lorsque :
- Votre requête implique plus d’une table.
- Vous pensez avoir une conception optimale, mais vous n’allez pas vérifier vos hypothèses.
- Vous conservez la première requête qui fonctionne, sans savoir si elle est ne serait‑ce qu’approchée de l’optimal. »
Si vous souhaitez démarrer avec SQL, suivez ces cours DataCamp :
Devenez ingénieur en données
Devenez un ingénieur de données grâce à l'apprentissage avancé de Python
Comment améliorer les performances de mes requêtes SQL ?
Comment améliorer la lisibilité de mes requêtes SQL ?
Comment éviter les erreurs SQL les plus fréquentes ?
Comment écrire des requêtes SQL plus complexes ?
Sujets