Lorsqu'un utilisateur soumet une requête, généralement écrite dans un langage de requête structuré (SQL), la base de données évalue plusieurs façons d'extraire les données demandées. Ce processus de prise de décision est géré par un composant appelé optimiseur de requêtes, qui sélectionne la stratégie d’exécution la plus efficace.
Les systèmes de gestion de bases de données (SGBD) modernes utilisent des optimiseurs basés sur les coûts qui estiment le coût des différentes stratégies d'exécution avant de sélectionner l'option la plus efficace. Grâce à ce processus, deux requêtes de base de données produisant des résultats identiques peuvent avoir des temps d’exécution très différents — souvent mesurés en millisecondes — qui impactent la performance et le temps de réponse de la requête.
Restez au fait des tendances les plus étonnantes du secteur dans le domaine de l’IA, de l’automatisation, des données et bien d’autres avec la newsletter Think. Consultez la Déclaration de confidentialité d’IBM.
L’optimisation des requêtes, ou optimisation des requêtes SQL, a une incidence sur bien plus que la performance des requêtes individuelles. Il détermine l’efficacité de systèmes de données entiers, de modèles d’apprentissage automatique et d’initiatives d’intelligence artificielle (IA) en améliorant la manière dont les systèmes évoluent et utilisent les ressources.
Les applications s’appuient sur des bases de données pour récupérer des informations rapidement et de manière cohérente. Lorsque les requêtes sont inefficaces, les bases de données peuvent passer un temps inutile à effectuer des balayages de tables, à trier des enregistrements ou à joindre de grands jeux de données. Ces retards peuvent ralentir les interfaces de programmation d’applications (API) et les workloads d’analytique, créant des goulets d’étranglement qui dégradent l’expérience utilisateur globale.
À mesure que les organisations collectent davantage de données, les bases de données doivent supporter des charges de travail de plus en plus complexes, dues au volume important de données, à la diversité des types de données et à des modèles de requêtes plus exigeants.
La sphère de données mondiale devant atteindre 393,9 zettaoctets d' ici à 2028, les requêtes qui traitaient auparavant des milliers de lignes pourraient à terme en traiter des millions ou des milliards. L'optimisation des requêtes améliore l'évolutivité en permettant des requêtes efficaces, même si la quantité de données et la complexité des Workloads augmentent.
Des plans d’exécution efficaces réduisent également les ressources nécessaires au traitement des requêtes. Chaque opération de base de données nécessite des ressources système pour traiter les données, notamment des cycles de l'unité centrale de traitement (CPU) et des entrées/sorties de disque (I/O).
Les requêtes mal optimisées sont gourmandes en ressources, car elles nécessitent beaucoup plus de traitement que nécessaire pour produire le même résultat. Cette augmentation de la consommation de ressources peut être coûteuse dans les environnements cloud où l'utilisation des ressources influe directement sur le prix.
Les plateformes de données modernes qui prennent en charge le machine learning, l’analytique en temps réel, la génération augmentée de récupération (RAG) et l’IA dépendent d’un accès rapide et fiable à de grands volumes de données. L’optimisation des requêtes permet de garantir que ces systèmes peuvent récupérer les informations pertinentes assez rapidement pour soutenir la prise de décision en temps réel sans compromettre les budgets.
Les optimiseurs de bases de données peuvent utiliser plusieurs approches pour évaluer les stratégies d'exécution potentielles. Les premiers systèmes de base de données utilisaient souvent l'optimisation basée sur des règles, qui appliquait des règles prédéfinies pour déterminer les plans d'exécution en fonction de la structure de la requête.
Les SGBD modernes privilégient généralement l’optimisation basée sur les coûts, qui évalue plusieurs stratégies d’exécution possibles et estime les ressources nécessaires pour chacune. Certains systèmes intègrent également des techniques basées sur l’heuristique, qui appliquent des directives pratiques pour simplifier la planification des requêtes et réduire la charge d’optimisation.
Quelle que soit l'approche d'optimisation utilisée, plusieurs concepts techniques déterminent la manière dont les optimiseurs évaluent les stratégies d'exécution potentielles :
Les optimiseurs de requêtes sont le composant de la base de données responsable de la sélection de plans d’exécution efficaces, souvent en utilisant des techniques d’optimisation basée sur les coûts. Dans les bases de données relationnelles, ce processus aide le moteur de base de données à déterminer le moyen le plus efficace d'exécuter une requête SQL Query.
Au lieu de dépendre de règles fixes, les optimiseurs basés sur les coûts analysent les caractéristiques des données et la structure de la requête pour déterminer l'approche la plus efficace. Cette flexibilité permet aux bases de données d'adapter les stratégies d'exécution à l'évolution des ensembles de données et des charges de travail.
Les optimiseurs s'appuient fortement sur les statistiques de la base de données pour estimer le coût des différents plans d'exécution. Les statistiques décrivent les principales caractéristiques des données stockées, notamment :
Ces statistiques permettent à l'optimiseur d'estimer le nombre de lignes qu'une requête renverra et la quantité de travail requise par les différentes stratégies d'exécution. Si les statistiques deviennent obsolètes ou inexactes, l'optimiseur peut sélectionner des plans d'exécution inefficaces.
L’estimation de la cardinalité consiste à prédire le nombre de lignes qui résulteront de chaque étape d’une requête. Par exemple, si une requête filtre des lignes à l’aide de clauses WHERE telles que :
Région OÙ= « Amérique du Nord »
L’optimiseur doit estimer combien d’enregistrements correspondent à ce filtre.
Ces estimations influencent plusieurs décisions clés. L’optimiseur peut les utiliser pour déterminer l’ordre dans lequel les tables doivent être jointes, les ordres de jointure les plus efficaces, les algorithmes de jointure à utiliser ou si un balayage d’index doit être utilisé au lieu de parcourir une table complète.
Les index permettent aux bases de données de localiser des données spécifiques plus efficacement que de scanner des tableaux entiers. Les optimiseurs utilisent des index pour réduire la charge de travail nécessaire à la récupération des données.
Les chemins d’accès courants incluent les balayages de table complets, qui lisent chaque ligne d’une table ; les balayages d’index, qui lisent les lignes à travers une structure d’index ; les recherches d’index, qui récupèrent des lignes spécifiques à l’aide de recherches d’index ; et les analyses d’index uniquement, qui extraient les données directement de l’index sans accéder à la table sous-jacente.
Le choix du bon chemin d'accès peut réduire de manière significative la quantité de travail nécessaire à l'exécution d'une requête, en particulier lorsque vous travaillez avec des tables de grande taille.
De nombreuses requêtes récupèrent des données à partir de plusieurs tables. Dans ce cas, l’optimiseur doit déterminer comment ces tables doivent être combinées. Les algorithmes de jointure les plus courants sont les suivants :
L’optimiseur sélectionne parmi ces algorithmes en fonction de facteurs tels que la taille des données, les index disponibles et le nombre estimé de lignes.
Pour comprendre comment fonctionne l'optimisation des requêtes, il est utile de considérer le langage SQL comme un langage déclaratif : il décrit les données à extraire plutôt que la manière dont ces données doivent être extraites.
L'optimiseur est chargé de déterminercomment exécuter la demande, et de la manière la plus efficace. Pour y parvenir, la plupart des bases de données suivent plusieurs étapes d’optimisation :
Lorsqu’une requête est soumise, la base de données analyse d’abord l’instruction SQL et valide sa syntaxe. Pendant cette étape, le système confirme l’existence de tableaux, colonnes et index référencés et que la structure de requête est valide.
Il vérifie également que les objets pertinents dans le schéma de la base de données sont disponibles. Cette étape permet de s'assurer que la base de données comprend la requête avant de tenter de l'optimiser ou de l'exécuter.
Après l'analyse, la base de données peut réécrire la requête sous une forme équivalente qui peut être exécutée plus efficacement. Ces transformations préservent les résultats de la requête tout en améliorant sa structure d’exécution. Les techniques courantes de réécriture de requêtes comprennent :
Ces transformations permettent à l’optimiseur d’explorer des stratégies d’exécution plus efficaces sans modifier le résultat final. Ils peuvent également contribuer à limiter le traitement de données inutiles.
Une fois la requête réécrite, l’optimiseur génère plusieurs plans d’exécution potentiels. Chaque plan représente une stratégie différente pour récupérer les données demandées.
Les plans peuvent différer en fonction des index utilisés, de l'ordre dans lequel les tables sont jointes ou de la manière dont les résultats intermédiaires sont traités. Même des requêtes relativement simples peuvent donner lieu à plusieurs stratégies d'exécution possibles.
Par exemple, une seule requête récupérant les commandes de la semaine précédente a plusieurs options : elle peut analyser la table des commandes et filtrer les lignes par la suite, utiliser un index sur la date de commande pour localiser rapidement les enregistrements récents ou réduire le jeu de données avant de rejoindre le client ou le produit associé tables.
L'optimiseur évalue ensuite chaque plan candidat à l'aide d'un modèle de coût. Les modèles de coûts évaluent la quantité de travail que la base de données devra effectuer pour exécuter un plan particulier. Ces estimations prennent généralement en compte des facteurs tels que :
Comme la base de données ne peut pas connaître le coût exact à l’avance, elle s’appuie sur des informations statistiques stockées sur les données. Ces informations aident l'optimiseur à estimer le temps de traitement probable et à déterminer quel algorithme et quelle structure de données sont les plus appropriés.
Après avoir évalué les plans candidats, l’optimiseur sélectionne le plan avec le coût estimé le plus bas. Cette stratégie sélectionnée devient le plan d'exécution de la requête, qui décrit la séquence des opérations effectuées par la base de données lors de l'exécution des requêtes.
Un plan d’exécution efficace inclut généralement des opérations telles que les scans de tables, les jointures, le tri et les agrégations (par exemple, en utilisant GROUP BY ou LEFT JOIN). Les utilisateurs peuvent consulter les plans EXPLAIN pour voir les étapes suivies par l'optimiseur pour récupérer les données demandées.
Malgré la sophistication des optimiseurs de bases de données modernes, plusieurs facteurs peuvent compliquer l'optimisation des requêtes.
Bien que l'optimisation des requêtes se fasse automatiquement, les développeurs, les administrateurs et les ingénieurs de données peuvent améliorer les performances grâce à plusieurs techniques d'optimisation.
Les index peuvent améliorer significativement les performances des requêtes lorsqu’ils supportent les filtres ou conditions de jointure fréquemment utilisés. Des index bien conçus permettent à l’optimiseur de récupérer rapidement des lignes spécifiques sans avoir à scanner des tables entières. Néanmoins, une indexation excessive peut entraîner des surcharges lors des mises à jour des données. Les index doivent donc être conçus avec soin afin d'équilibrer les performances de lecture et l'efficacité d'écriture.
Comme les optimiseurs utilisent les statistiques pour estimer les coûts des requêtes, il est essentiel de maintenir les statistiques à jour pour conserver des plans d'exécution efficaces. La mise à jour régulière des statistiques permet à l'optimiseur de disposer d'informations précises sur la répartition des données et la taille des tables.
Appliquer des filtres plus tôt dans l’exécution de la requête réduit le nombre de lignes à traiter plus tard dans la requête. Des résultats intermédiaires plus petits peuvent accélérer l'exécution de la requête. C’est pourquoi les requêtes qui appliquent des filtres sélectifs dès le début sont souvent plus efficaces.
Les requêtes qui combinent de nombreuses tables peuvent produire des requêtes complexes et des plans d’exécution tout aussi complexes. Lorsque les jointures sont inutiles ou redondantes, leur suppression peut réduire considérablement la complexité de l'exécution. Dans certains cas, la dénormalisation peut également améliorer les performances en réduisant le besoin de jointures, même si elle peut augmenter l'utilisation du stockage et la redondance des données .
Les requêtes qui récupèrent des colonnes inutiles augmentent la quantité de données à lire et à traiter. La limitation des ensembles de résultats aux seuls champs requis réduit l'utilisation de la mémoire et les opérations d'E/S sur disque. Ce petit ajustement peut nettement améliorer la performance dans de grands jeux de données.
Dans certains environnements, le partitionnement peut aider à diviser de très grandes tables en segments plus faciles à gérer, tandis que la mise en cache peut réduire le travail répétitif sur la base de données pour les résultats fréquemment consultés. Ces approches ne sont pas des correctifs universels, mais elles peuvent compléter d’autres stratégies d’optimisation.
De nombreuses plateformes de bases de données fournissent également des outils intégrés qui aident les développeurs et administrateurs à analyser la performance des requêtes et à identifier des plans d’exécution inefficaces.
Par exemple, SQL Server Management Studio (SSMS) permet de contrôler la performance des requêtes et d’identifier les goulots d’étranglement ; lesystème MySQL Workbench fournit des outils pour analyser les plans de requêtes et optimiser leur exécution ; et Oracle SQL Tuning Advisor peuvent générer des recommandations automatisées pour l’amélioration des requêtes SQL.
L’optimisation et le réglage des requêtes sont étroitement liés, mais représentent des processus différents.
L’optimisation des requêtes désigne le processus automatisé utilisé par les bases de données pour déterminer des stratégies d’exécution efficaces.
Le réglage des requêtes, en revanche, fait référence à des efforts manuels visant à améliorer les performances des requêtes. Ces efforts peuvent inclure la réécriture de requêtes inefficaces, la création de nouveaux index, la mise à jour des statistiques ou l'ajustement des paramètres de configuration de la base de données.
En pratique, l’optimisation et le réglage des requêtes fonctionnent souvent en tandem pour améliorer les performances de la base de données. Ensemble, ils constituent un ensemble pratique de stratégies d'optimisation visant à améliorer les performances SQL dans les systèmes de production.
L'optimisation des requêtes évolue au-delà de la planification traditionnelle basée sur les coûts. Les systèmes de bases de données modernes intègrent désormais l'automatisation, l'exécution adaptative et l'intelligence artificielle pour améliorer la façon dont les requêtes sont analysées et exécutées.
L'une des nouvelles orientations est le développement de capacités de bases de données autonomes, dans lesquelles les systèmes surveillent en permanence les performances et réagissent automatiquement aux problèmes. Au lieu de s’appuyer uniquement sur le dépannage réactif, ces systèmes analysent le comportement des charges de travail, interrogent la performance et les signaux système afin d’identifier rapidement les problèmes potentiels de performance et de recommander des actions correctives.
De nombreuses architectures de bases de données autonomes organisent ces capacités en trois domaines opérationnels, souvent alimentés par des agents d’IA.
Ces fonctionnalités d'agence sont conçues pour fonctionner dans le cadre d'un modèle humain intégré, dans lequel l'automatisation gère des tâches opérationnelles bien définies tandis que les équipes chargées des bases de données surveillent les systèmes critiques.
À mesure que les entreprises continuent de dimensionner leurs plateformes de données et d’adopter des applications pilotées par l’IA, les systèmes capables de se surveiller, d’optimiser et de se maintenir joueront un rôle de plus en plus important pour garantir une performance fiable des bases de données.
watsonx.data vous permet d’adapter le dimensionnement des analyses et de l’IA à toutes vos données, où qu’elles se trouvent, grâce à un entrepôt de données ouvert, hybride et gouverné.
Exécutez vos applications, l’analytique et l’IA générative grâce à des bases de données hébergées sur tous types de cloud.
Réussissez le passage à l’échelle de l’IA avec la bonne stratégie, les données, la sécurité et la gouvernance adaptées.