INSIA Bases de données SRT 3 Optimisation des requêtes

INSIA - BASES DE DONNÉES – SRT 3 – 1 : Optimisation - 2007-2008 - page 1 - Bertrand LIAUDET
INSIA
Bases de données
SRT 3
Optimisation des requêtes
0 : Présentation générale
Bertrand LIAUDET
SOMMAIRE
SOMMAIRE 1
PRESENTATION GENERALE 1
Bibliographie 1
Généralités 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 2 - 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 3 - 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 4 - 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