INSIA Bases de données Optimisation – 2 : arbres algébriques et

INSIA - BASES DE DONNÉES – Optimisation 2 – Arbre algébrique - page 1/34 - Bertrand LIAUDET
INSIA
Bases de données
Optimisation – 2 : arbres algébriques
et calcul de coût
Bertrand LIAUDET
SOMMAIRE
SOMMAIRE 1
INTRODUCTION 3
Bibliographie 3
Généralités sur l’optimisation des requêtes 3
Objectif de l’optimisation 3
Typologie des requêtes d’un point de vue général d’optimisation 3
L’optimiseur et optimisation 4
3 approches de l’optimisation 5
Optimisation physique et notion de METABASE 6
Plan d’exécution 6
Bilan de l’optimisation : le plan d’exécution choisi et calcul de coût 6
Optimisation et méthodes (algorithmes) d’accès aux données 7
La question de la performance : l’évolution des SGBD en terme de rapidité 7
OPTIMISATION SYNTAXIQUE 8
1. L’arbre algébrique 8
Exemple traité 8
Rappel de vocabulaire 9
Représentation textuelle des opérateurs de l’algèbre relationnelle 10
Représentation graphique des opérateurs de l’algèbre relationnelle dans les arbres
algébriques 10
Arbre algébrique 11
Notion d’arbres algébriques équivalent 12
2. Théorie sur les règles de transformation des arbres algébriques 14
Règles de base sur les produits cartésiens et les jointures 14
Règles de base sur les restrictions 14
Règles de base sur les projections 15
3. Algorithme d’optimisation syntaxique et arbres algébriques optimisés 17

INSIA - BASES DE DONNÉES – Optimisation 2 – Arbre algébrique - page 2/34 - Bertrand LIAUDET
Algorithme d’optimisation syntaxique 17
Arbres algébriques optimisés 17
CALCUL DE COUT ET OPTIMISATION PHYSIQUE 20
1. Première approche du calcul du coût : approche SQL 20
Quantité de données 20
Principes du calcul 21
Exemple 21
2. Optimisation phyisique et calcul de coût indexé 23
Principe du passage du SQL au langage impératif 23
La jointure indexée 23
Distinction entre jointure indexée unique (EQ_REF) et jointure indexé multiple
(REF) 24
Exemple 24
Pourquoi choisir une solution plutôt qu’une autre ? 26
Calcul de coût et arbre algébrique indexé 26
Les restrictions dans les arbres algébriques indexés 27
EXERCICES 29
1. Les employés 29
2. La bibliothèque 30
3. Les projets 31
Schéma de la BD projets simplifié 31
4. La maison de disques 32
Schéma de la BD Maison de disques 32
5. L’école 33
Schéma de la BD Ecole 33
Première édition : février 2008
Deuxième édition : septembre 2009
Troisième édition : avril 2010

INSIA - BASES DE DONNÉES – Optimisation 2 – Arbre algébrique - page 3/34 - Bertrand LIAUDET
INTRODUCTION
Bibliographie
Gardarin. Bases de données. Eyrolles 2005. §10 – pp. 301 à 350.
Darmaillac, Rigaux. Maîtriser MySQL 5. O’Reilly 2005. §11 – pp. 283 à 318.
Dubois, Hinz, Pedersen. MySQL-Guide officiel. Campus press 2004. §13 – pp. 433 à 475.
MySQL 5-Guide de l’administrateur. Campus press 2005. §6 – pp. 447 à 510.
Harrison. MySQL Stored Procedure. O’Reilly 2006. Part IV – pp. 421 à 582.
Généralités sur l’optimisation des requêtes
Objectif de l’optimisation
L’objectif de l’optimisation est d’accélérer de vitesse de traitement des requêtes.
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 (SI au sens large – informatique transactionnelle)
concernent autant voir plus la création-modification-suppression (CMS : insert, update, delete)
des données que leur interrogation. L’interrogation des données est souvent assez élémentaire.
Les requêtes de l’informatique décisionnelle (OLAP) 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, souvent écrites en PL-SQL.
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 calculette SQL
avec ou sans interface graphique : mysql sous MySQL, psql sous PostgreSQL, SQL-Plus sous
Oracle, SQL Server Management Studio Express sous SQL-Server, etc. Elles sont
probablement utilisées une seule fois.
Bilan
L’optimisation concerne sutout les requêtes d’informatique transactionnelle car l’usage
intensif de l’informatique décisionnelle passe par des applications spécifiques qui complètent ou
sont indépendante du SGBD.
L’optimisation concerne surtout les requêtes statiques. Le but est d’être le plus efficace
possible, au moins pour les questions les plus fréquentes.

INSIA - BASES DE DONNÉES – Optimisation 2 – Arbre algébrique - page 4/34 - Bertrand LIAUDET
L’optimiseur et optimisation
L’optimiseur
L’optimiseur est un composant du SGBD. Son rôle est de transformer la requête SQL en
opérations dites « de bas niveau » réalisant efficacement l’accès aux données.
Requête SQL
ANALYSEUR
erreur de syntaxe
ok
OPTIMISEUR
Plan d’exécution choisi
EXECUTION
Résultats
En majuscule et dans un ovale : les traitements
En minuscules : les données
Principe général de l’optimisation des requêtes par l’optimiseur
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 :
• de la requête SQL initiale
• des contraintes d’intégrité
• des index
• des statistiques de la BD : taille des tables et connaissances sur les résultats des restrictions.
Les plans d’exécution
Le 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.
Il peut exister plusieurs plans d’exécution pour une requête donnée.

INSIA - BASES DE DONNÉES – Optimisation 2 – Arbre algébrique - page 5/34 - Bertrand LIAUDET
C’est le résultat de l’optimisation.
Le plan d’exécution choisi et le calcul du coût
L’optimisation consiste à choisir le meilleur plan d’exécution (le plus perfomant). Le choix se
fait par un calcul du coût (du temps de traitement).
Commande EXPLAIN
La commande EXPLAIN permet de consulter le plan d’exécution mis en œuvre par le SGBD
pour une requête donné.
En général sa syntaxe se résume à : EXPLAIN requête ;
3 approches de l’optimisation
Il y a 3 approches de l’optimisation des requêtes :
L’optimisation sémantique
• L’optimisation sémantique consiste essentiellement à rechercher une contradiction dans les
restrictions qui conduirait à obtenir 0 tuple en réponse à la question ou à une partie de la
question.
La contradiction peut apparaître directement dans la question. C’est le cas si deux
restrictions sont contradictoires : (degré > 13 et degré <10) ou encore (région = R1 et
région = R2).
La contradiction peut apparaître quand on croise une restriction avec une contrainte
d’intégrité de la BD. Par exemple, une contrainte de valeur peut préciser : (degré <15) et
une question demander (degré >15).
Si une telle contradiction est découverte alors la table concernée ainsi que toutes celles qui
lui sont jointes n’auront pas de tuples.
• L’optimisation sémantique peut aussi traiter certains points particuliers : par exemple
remplacer un like par un « = » si possible (pas de caractères spéciaux dans la restriction).
• L’optimisation sémantique consiste donc à remplacer une formule par une autre à l’intérieur
du select (une requête par la valeur null, un like par un « = », etc.) sans changer la structure
de la requête.
• Cette optimisation est totalement mécanique.
L’optimisation syntaxique
• Elle prend uniquement en compte les propriétés de l’algèbre relationnelle. Autrement dit,
l’ordre des opérations sera changé pour obtenir le meilleur résultat en terme de performance.
Par exemple, une table sera utilisée avant une autre, une restriction sera faite avant une
autre, une jointure sera faite avant une autre, etc.
• L’optimisation syntaxique consiste donc à réorganiser les étapes des différentes opérations
relationnelles élémentaires d’une requête. Elle part d’une requête en SQL pour arriver à une
autre requête en SQL consituées de plusieurs étapes qu’on peut formaliser à travers des
vues.
• Cette optimisation est totalement mécanique. Toutefois, plusieurs résultats sont possibles.