INSIA Bases de données SRT 3 Optimisation des requêtes
publicité
INSIA Bases de données SRT 3 Optimisation des requêtes 0 : Présentation générale Bertrand LIAUDET SOMMAIRE SOMMAIRE 1 PRESENTATION GENERALE Bibliographie Généralités 1 1 1 Première édition : septembre 2007 PRESENTATION GENERALE Bibliographie Gardarin. Bases de données. Eyrolles 2005. §10 – pp. 301 à 350. Généralités Objectif Accélérer le traitement des requêtes. Accélérer l’accès aux données dans les tables. Etre le plus efficace possible, au moins pour les questions les plus fréquentes. Public concerné INSIA - BASES DE DONNÉES – SRT 3 – 1 : Optimisation - 2007-2008 - page 1 - Bertrand LIAUDET Le programmeur d’applications : mieux connaître les possibilités du SGBD et de l’algèbre relationnelle permet de mieux concevoir la BD et les requêtes. L’administrateur BD : mieux connaître les possibilités du SGBD et de l’algèbre relationnelle permet de mieux administrer la base de données et de mieux donner satisfaction à ses utilisateurs Le programmeur de SGBD : celui qui développe l’application SGBD (le serveur) est le premier concerné par le problème de l’optimisation. L’utilisateur final : il peut avoir des exigences de temps réponse tel que si elles ne sont pas prises en compte, l’application ne répond plus aux besoins de l’utilisateur et devient donc inutile. L’évolution des systèmes Années 75 : quelques requêtes par seconde Aujourd’hui : plusieurs milliers de transactions par seconde 3 causes à cette accélération : • la vitesse du processeur • les méthodes d’accès aux données (les algorithmes de calcul) • l’optimisation des requêtes Les temps d’entrée-sortie disque restent à peu près constant : de l’ordre de la dizaine de millisecondes. Typologie des requêtes d’un point de vue général d’optimisation Informatique de gestion (au sens large) vs. informatique décisionnelle Les requêtes de l’informatique de gestion (au sens large) concernent autant voir plus la création-modification-destruction (CMD) des données que leur interrogation. L’interrogation des données est souvent assez élémentaire. Les requêtes de l’informatique décisionnelle concernent essentiellement l’interrogation des données. Ce sont des requêtes complexes, multi-tables, le plus souvent avec des statistiques et des agrégats. Requêtes statiques (compilées) vs. requêtes ad hoc (interprétées, dynamiques) Les requêtes statiques sont les requêtes programmées une fois pour toutes dans une application. Elles seront probablement utilisées plusieurs fois. Les requêtes dynamiques sont les requêtes construites directement via une application d’exécution des requêtes SQL (AER-SQL : SQL-Plus sous Oracle, SQL Server Management Studio Express sous SQL-Server, etc.) On s’intéresse surtout aux requêtes générales qui peuvent être utilisées des millions de fois. Principe général de l’optimisation des requêtes L’optimisation consiste à passer d’une requête exprimée dans un langage source (le SQL dans le modèle relationnel) à une requête exprimée par un ensemble d’opérations plus élémentaires exprimées dans un langage cible (opérations dites « de bas niveau »). Ces opérations élémentaires dépendent particulièrement : • du schéma interne de la base de données (et particulièrement de la présence d’index), • de la taille des tables INSIA - BASES DE DONNÉES – SRT 3 – 1 : Optimisation - 2007-2008 - page 2 - Bertrand LIAUDET Etapes de l’optimisation des requêtes L’optimisation des requêtes se divise en trois étapes : L’optimisation logique ou réécriture Elle consiste à optimiser les requêtes indépendamment du schéma interne de la BD (donc des index particulièrement) et de la taille des tables. Cette optimisation consiste à restructurer l’écriture de la requête en langage relationnel. C’est la phase de réécriture. L’optimisation logique peut être syntaxique ou sémantique. L’optimisation syntaxique prend en compte uniquement les propriétés de l’algèbre relationnelle. L’optimisation sémantique prend en compte les aspects sémantiques, c’est-à-dire les contraintes d’intégrité en tout genre. L’optimisation physique Elle consiste à optimiser les requêtes en fonction du schéma interne (index, contraintes d’intégrité diverses) et de la taille des tables. Cette optimisation consiste à ordonner les opérations algébriques élémentaires pour que les algorithmes de recherche, de tri, de jointure et d’agrégat soient, en fonction du contexte, les plus efficaces. Plus généralement, l’optimisation physique a à voir avec le problème des méthodes d’accès aux données. On abordera donc à ce sujet deux grand types de méthodes : les méthodes par indexation et les méthodes par hachage. Choix d’un plan d’exécution et calcul de coût Un plan d’exécution d’une requête est une suite possible d’opérations de bas niveau qui permettent de réaliser cette requête. Une requête peut donner lieu à plusieurs plans d’exécution. Le choix du plan d’exécution optimale se fera par un calcul de coût, c’est-à-dire de la performance. Le calcul de coût consiste à calculer le temps de traitement maximum des opérations élémentaires et de leur succession. Dans le calcul de coût (donc dans la performance de la requête), interviennent : • Le nombre d’entrée-sortie • Le parallélisme des opérations élémentaires • Le temps de calcul des opérations élémentaires Optimisation et heuristique L’optimisation logique est une étape algébrique et logique qui relève d’une méthode déductive. L’optimisation physique et de choix du plan d’exécution relèvent d’une méthode heuristique (c’est-à-dire d’une méthode qui procède par hypothèses provisoires et par évaluations successives). L’optimisation physique liée à l’implémentation physique de la BD, mais aussi aux algorithmes de calcul qui sont utilisés. Certains algorithmes sont plus efficaces que d’autres. La qualité de ces algorithmes est un des atouts du SGBD (comme la qualité des algorithmes de recherche est un des atouts des moteurs de recherche). INSIA - BASES DE DONNÉES – SRT 3 – 1 : Optimisation - 2007-2008 - page 3 - Bertrand LIAUDET Plan du chapitre Le plan du chapitre reprend les 3 étapes de l’optimisation, en ajoutant deux chapitres sur les méthodes d’accès aux données : 1. Optimisation logique 2. Indexation 3. Hachage 4. Optimisation physique 5. Plan d’exécution et calcul de coût INSIA - BASES DE DONNÉES – SRT 3 – 1 : Optimisation - 2007-2008 - page 4 - Bertrand LIAUDET
