Introduction au traitement et optimisation des requêtes

IGE487 Modélisation des bases de données
Semaine 4
Traitement et optimisation des
requêtes
Domingo Palao
Plan de la semaine
S
e
m
a
i
n
e
Traitement et optimisation des requêtes
Architecture de la base de données
Architecture de PostgreSQL
Architecture d’Oracle
IGE487 Modélisation des bases de données
4
Algorithmes pour le traitement des requêtes
Implémentation des opérations de l’algèbre relationnelle sur
la base de données
Règles de transformation des requêtes
Algorithme heuristique d’optimisation
Optimisation de la base de données
Quelques tips
2010-05-24
© Domingo Palao
2
IGE487 Modélisation des bases de données
Semaine 4
Architecture de la base de
données
IGE487 Modélisation des bases de données
Semaine 4
Architecture de PostgreSQL
PostgreSQL
S
e
m
a
i
n
e
PostgreSQL est un système de gestion de
bases de données relationnelles objet fondé sur
POSTGRES Version 4.2.
Ce dernier a été développé à l’université de Californie
au département des sciences informatiques de
Berkeley.
IGE487 Modélisation des bases de données
4
Quelques fonctionnalités de PostgreSQL:
Requêtes complexes
Clés étrangères
Déclencheurs (triggers)
Vues
Intégrité des transactions
Contrôle d’accès concurrents
2010-05-24
© Domingo Palao
5
Architecture de PostgreSQL
S
e
m
a
i
n
e
Postgres utilise un modèle client/serveur simple nommé
« process per-user ».
Une session Postgres a les processus UNIX suivants :
Un démon de supervision (postmaster),
L’application « frontend » des utilisateurs (par exemple, le
programme psql)
Un ou plusieurs serveurs de « backend » (le processus postgres luimême).
Un seul postmaster contrôle une collection de bases de données
IGE487 Modélisation des bases de données
4
sur un seul serveur
Cette collection de bases de données est nommée une installation
ou site
Les applications « frontend » qui veulent accéder à une base de
données font l’appel à une librairie
La librairie envoie les requêtes des utilisateurs sur le réseau au
« postmaster »
2010-05-24
© Domingo Palao
6
Architecture de PostgreSQL
S
e
m
a
i
n
e
1.
Un programme d’application doit établir une connexion avec le
serveur.
2.
4
IGE487 Modélisation des bases de données
Une requête dans PosgreSQL passe par les étapes suivantes :
3.
4.
Le parser doit vérifier que la requête envoyée par le programme
d’application est correcte (point de vue de la syntaxe) et il doit créer
l’arbre de la requête.
Le processus rewriter prends l’arbre de la requête crée par le parser,
il cherche les règles (qui sont stockées dans les catalogues du
système) pour les appliquer à l’arbre de requête et faire des
transformations.
Le planner/optimizer prend l’arbre de la requête et il doit créer un
plan.
5.
D'abord, il doit créer tous les chemins possibles pour résoudre la requête.
Il calcule le coût de chaque plan d’exécution et le moins cher est choisi et
envoyé à l’exécutor.
L’executor doit traiter le plan de la requête pour récupérer les tuples.
2010-05-24
Il doit transmettre la requête au serveur.
Il doit recevoir le résultat de la requête.
L’executor utilise le système de stockage pour scanner les relations. Il fait
les tris et les jointures et finalement doit envoyer les tuples résultants de
la requête.
© Domingo Palao
7
Architecture de PostgreSQL
S
e
m
a
i
n
e
IGE487 Modélisation des bases de données
4
©PostgreSQL
2010-05-24
© Domingo Palao
8
IGE487 Modélisation des bases de données
Semaine 4
Architecture d’Oracle
Architecture d’Oracle
S
e
m
a
i
n
e
IGE487 Modélisation des bases de données
4
© Oracle Inc.
2010-05-24
© Domingo Palao
10
Architecture d’Oracle
S
e
m
a
i
n
e
Oracle a deux grandes structures de
mémoire :
System Global Area (SGA)
IGE487 Modélisation des bases de données
4
Réservé au moment de démarrer une instance.
C’est le composant fondamental d’une instance.
Elle contient les structures de mémoire :
Shared Pool
Redo Log Buffer
Buffer cache
Program Global Area (PGA)
Réservé lors de la création d’un processus.
2010-05-24
© Domingo Palao
11
Architecture d’Oracle
S
e
m
a
i
n
e
La section de mémoire connue comme Shared
Pool est utilisé pour :
Stocker les commandes SQL récemment utilisées
Les données des utilisateurs plus récemment utilisés
IGE487 Modélisation des bases de données
4
Dans le Data Dictionary Cache
Une collection des définitions récemment utilisées
Information sur les fichiers de la base de données,
tables, indexes, utilisateurs, privilèges et autres
objets.
Elle est très utilisée lors de l’étape d’analyse
syntaxique (parsing)
2010-05-24
© Domingo Palao
12
Architecture d’Oracle
S
e
m
a
i
n
e
Progam Global Area
C’est la mémoire réservée pour chaque
processus d’utilisateur qui est connecté à la
base de données.
Réservée quand le processus est créé.
Supprimée quand le processus est fini.
Il y a un PGA par processus par utilisateur
connecté à la base de données.
IGE487 Modélisation des bases de données
4
2010-05-24
© Domingo Palao
13
Architecture d’Oracle
S
e
m
a
i
n
e
Les processus
Database Writer (DBWR)
Fait l’écriture des données qui se trouvent dans les
buffers de mémoire vers les fichiers de la base de
données.
IGE487 Modélisation des bases de données
4
Log Writer (LGWR)
Garder le log des transactions.
System Monitor (SMON)
Surveillance de la base de données.
Nettoyer les transactions non terminées.
Ouvrir la base de données pour l’accès des
usagers.
2010-05-24
© Domingo Palao
14
Architecture d’Oracle
S
e
m
a
i
n
e
Les processus :
Process Monitor (PMON)
Surveillance des processus.
Responsable de faire les rollback.
Contrôle des verrous.
IGE487 Modélisation des bases de données
4
Checkpoint (CKPT)
Signaler au DBWR un checkpoint.
Mettre à jour les fichiers de données avec l’information du
checkpoint.
Mettre à jour les fichiers de contrôle avec l’information du
checkpoint.
Archiver (ARC)
Processus optionnel.
Automatiquement enregistre un log des transactions si le
mode ARCHIVELOG est activé.
2010-05-24
© Domingo Palao
15
IGE487 Modélisation des bases de données
Semaine 4
Algorithmes pour traitement de
requêtes
Processus général de traitement des
requêtes
S
e
m
a
i
n
e
Une requête exprimée en un langage de haut
niveau comme SQL doit être :
Explorée (scanned)
L’exploration identifie les jetons (tokens) du langage
IGE487 Modélisation des bases de données
4
Analysée syntaxiquement (parsed)
L’analyse syntaxique vérifie la syntaxe de la requête pour
déterminer si elle est construite selon les normes du langage.
Validée (validated)
La validation consiste à vérifier que tous les noms des
attributs et des relations existent dans le schéma de la base
de données interrogée.
2010-05-24
© Domingo Palao
17
Traitement d’une requête
S
e
m
a
i
n
e
IGE487 Modélisation des bases de données
4
2010-05-24
© Domingo Palao
18
Traduire les requêtes SQL en algèbre
relationnelle
S
e
m
a
i
n
e
Chaque RDBMS a un module
d’optimisation de requêtes.
L’algorithme général de traitement d’une
requête demande de décortiquer la requête
complexe en expressions simples de l’algèbre
relationnelle.
IGE487 Modélisation des bases de données
4
2010-05-24
© Domingo Palao
19
Traduire les requêtes SQL en algèbre
relationnelle
S
e
m
a
i
n
e
IGE487 Modélisation des bases de données
4
Prenons la requête :
select lname, fname
from employe
where salary > (select max (salary)
from employe
where dno = 5)
Il faudra la décomposer en deux requêtes :
select max (salary)
from employe
where dno = 5
Le bloc interne
ℱMAX Salary σ dno=5 (EMPLOYEE)
et
select lname, fname
from employe
where salary > c
2010-05-24
Le bloc externe
c représente le résultat du bloc interne
Π(lname, fname)( σ salary>c (EMPLOYEE)
© Domingo Palao
20
Traduire les requêtes SQL en algèbre
relationnelle
S
e
m
a
i
n
e
IGE487 Modélisation des bases de données
4
Une fois que les expressions en algèbre
relationnelle sont construites,
l’optimisateur de requêtes doit choisir le
plan d’exécution de la requête.
2010-05-24
© Domingo Palao
21
IGE487 Modélisation des bases de données
Semaine 4
Algorithmes pour traiter
l’opération SELECT
Algorithmes pour traiter l’opération SELECT
Une requête simple est celle qui a une seule condition
de sélection
σ ssn=‘12345’ (EMPLOYEE)
Méthodes de recherche pour une sélection simple
4
S1 Recherche linéal (Linear search (force brute))
IGE487 Modélisation des bases de données
S
e
m
a
i
n
e
Récupère chaque enregistrement du fichier et vérifie si la valeur de
l’attribut satisfait la condition de sélection.
S2 Recherche binaire
Si la condition de sélection utilise une comparaison avec une
égalité sur un attribut clé sur lequel le fichier est trié, nous pouvons
appliquer la recherche binaire.
S3 Utiliser un index pour extraire un seul
enregistrement
Si la condition de sélection utilise un attribut clé sur lequel il y a un
index, l’optimisateur peut choisir d’utiliser l’index pour récupérer
l’enregistrement.
2010-05-24
© Domingo Palao
23
Algorithmes pour traiter l’opération SELECT
S
e
m
a
i
n
e
Méthodes de recherche pour une sélection simple (2)
S4 Utiliser un index pour récupérer plusieurs
enregistrements
Si la condition de comparaison est >, ≥, <, ou ≤ sur un clé avec un
index, l’optimisateur peut utiliser l’index pour trouver le premier
enregistrement et après récupérer tous les enregistrements
suivants.
IGE487 Modélisation des bases de données
4
S5 Utiliser un index multiple (cluster index) pour
récupérer plusieurs enregistrements
Si la condition demande la comparaison d’égalité sur un attribut non
clé qui est dans un index composé, l’optimisateur peut s’en servir
de cet index.
S6 Utiliser un index secondaire (un arbre B+)
Sur une comparaison d’égalité, l’optimisateur peut décider d’utiliser
cette méthode pour récupérer un seul enregistrement.
2010-05-24
© Domingo Palao
24
Algorithmes pour traiter l’opération SELECT
S
e
m
a
i
n
e
IGE487 Modélisation des bases de données
4
Une requête complexe, est celle qui a plusieurs
conditions de sélection
σ dno=5 AND salary>3000 and sex=‘F’(EMPLOYEE)
Méthodes de recherche pour une sélection
complexe
S7 Sélection conjointe sur un index individuel
Si un des attributs utilisés dans la condition complexe peut
être traité comme une condition simple et la résoudre en
appliquant une des méthodes de S2 à S6, l’optimisateur le
fait, et ensuite vérifie chaque enregistrement pour le restant
des conditions
S8 Sélection conjointe sur un index composé
Si deux ou plus attributs sont dans les conditions d’égalité
d’une condition composée et il y a un index composé qui
combine les champs, l’optimisateur peut utiliser l’index
directement.
2010-05-24
© Domingo Palao
25
Algorithmes pour traiter l’opération SELECT
S
e
m
a
i
n
e
Méthodes de recherche pour une sélection complexe
S9 Sélection conjointe par intersection d’enregistrements
d’un ensemble de pointeurs
Cette méthode est applicable s’il y a des indexes secondaires sur
tous ou quelques champs qui apparaissent sur la condition de
comparaison et si les indexes ont de pointeurs vers les
enregistrements (pas sur les blocs).
Chaque index peut être utilisé pour récupérer les pointeurs vers les
enregistrements pour satisfaire une condition individuelle.
Ensuite, l’intersection de ces ensembles de pointeurs donne les
pointeurs vers les enregistrements qui satisfaisant la condition
complexe. Avec cet ensemble on peut récupérer les
enregistrements de manière directe.
Si seulement quelques conditions ont les indexes secondaires,
chaque enregistrement récupéré peut être testé pour vérifier s’il
satisfait les conditions restantes.
IGE487 Modélisation des bases de données
4
2010-05-24
© Domingo Palao
26
IGE487 Modélisation des bases de données
Semaine 4
Implémentation de l’opération
JOIN
Implémentation de l’opération JOIN
S
e
m
a
i
n
e
IGE487 Modélisation des bases de données
4
L’opération JOIN est une de plus coûteuse
à traiter.
La jointure (EQUIJOIN, NATURAL JOIN)
peut être classée de deux manières :
Jointure à deux voies, c’est à dire, une jointure
avec deux fichiers
R A=B S
Jointure à plusieurs voies, c’est-à-dire, les
jointures avec plus que deux fichiers
R A=B S C=D T
2010-05-24
© Domingo Palao
28
Implémentation de l’opération JOIN
S
e
m
a
i
n
e
Méthodes pour traiter le JOIN
J1 Boucle imbriquée (force brute)
Pour chaque enregistrement r dans R (outer loop),
récupérer chaque enregistrement s de la relation S
(inner loop) et vérifier si les deux enregistrements
comblent la condition de jointure r [A] = s [B].
IGE487 Modélisation des bases de données
4
J2 Boucle simple, en utilisant un index
pour récupérer les enregistrements ciblés
S’il y a un index sur un de deux attributs de la
jointure, disons B dans S, récupérer chaque
enregistrement de R, et utiliser l’index pour
récupérer tous les enregistrements qui satisfassent
la condition
2010-05-24
© Domingo Palao
29
Implémentation de l’opération JOIN
S
e
m
a
i
n
e
Méthodes pour traiter le JOIN
J3 Sort-Merge
Si les enregistrements de R et de S sont triés de
manière physique par la valeur de la jointure, nous
pouvons implémenter une des meilleures
manières de résoudre une jointure:
Les deux fichiers sont balayés pour chercher les
enregistrements ayant les mêmes valeurs pour A
et B.
Les enregistrements résultants sont stockés en
mémoire et ensuite comparés.
De cette manière, les enregistrements sont lus une
seule fois.
IGE487 Modélisation des bases de données
4
2010-05-24
© Domingo Palao
30
Implémentation de l’opération JOIN
S
e
m
a
i
n
e
Méthodes pour traiter le JOIN
J4 Hash
Les enregistrements de R et de S sont « hashes » dans le
même fichier de hash, en utilisant la même fonction sur les
attributs de jointure A de R et B de S.
Il faut faire un premier balayage d’un des fichiers (celui qui a
le moins d’enregistrements) pour emmagasiner les
enregistrements, cette étape est connue comme « patitioning
phase ».
Ensuite un seul balayage de l’autre fichier pour placer les
enregistrements dans le conteneur approprié, le conteneur a
déjà l’enregistrement de l’autre relation.
IGE487 Modélisation des bases de données
4
2010-05-24
© Domingo Palao
31
Implémentation de l’opération JOIN
S
e
m
a
i
n
e
Quelques facteurs qui affectent la
performance de l’opération JOIN
Espace disponible pour les buffers.
Choix de la relation interne et de la relation
externe.
IGE487 Modélisation des bases de données
4
Pour quelques SGBD moins sophistiqués le temps
pour effectuer l’opération JOIN peut changer
dramatiquement si on change l’ordre des relations
dans la clause FROM.
2010-05-24
© Domingo Palao
32
IGE487 Modélisation des bases de données
Semaine 4
Implémentation des opérations
d’ensemble et d’agrégation
Implémentation des opérations d’ensemble
S
e
m
a
i
n
e
UNION
Trier les deux relations sur les mêmes attributs.
Parcourir les deux fichiers triés de manière concurrente pour les
fusionner. Si la même tuple existe dans les deux relations,
seulement une est gardée dans le résultat.
IGE487 Modélisation des bases de données
4
INTERSECTION
Trier les deux relations sur les mêmes attributs.
Parcourir les deux fichiers triés de manière concurrente pour les
fusionner. Garder dans le résultat seulement les tuples qui se
trouvent dans les deux relations
SET DIFFERENCE R-S
Trier les deux relations sur les mêmes attributs.
Garder dans le résultat seulement les tuples qui se trouvent
dans la relation R et non dans la relation S.
2010-05-24
© Domingo Palao
34
Implémentation des opérations d’agrégation
S
e
m
a
i
n
e
Il y a deux manières d’implémenter les
opérations d’agrégation
Full table scan.
Index.
IGE487 Modélisation des bases de données
4
Les opérateurs à implémenter sont:
MIN
MAX
COUNT
AVERAGE
SUM
2010-05-24
© Domingo Palao
35
Implémentation des opérations d’agrégation
S
e
m
a
i
n
e
IGE487 Modélisation des bases de données
4
select max (salary)
from employee;
S’il y a un index ascendant sur salary,
l’optimisateur peut décider d’utiliser cet
index pour chercher la valeur la plus
grande et répondre avec un seul accès.
Si la requête utilise le GROUP BY,
l’opérateur d’agrégation doit être appliqué
à chaque groupe de tuples.
Il faut trier les résultats pour diviser en groupes.
Calculer la fonction d’agrégation sur les tuples de
chaque groupe.
2010-05-24
© Domingo Palao
36
IGE487 Modélisation des bases de données
Semaine 4
Combinaison de résultats
Combinaison de résultats
S
e
m
a
i
n
e
Le problème
Pour traiter une requête, elle est divisée dans une
séquence d’opérations.
L’exécution de chaque opération produit un ensemble
de résultats temporaires.
La création et la sauvegarde de fichiers
temporaires sur le disque est très cher.
IGE487 Modélisation des bases de données
4
Alternative :
Si possible, éviter la construction de résultats
temporaires.
Utiliser la stratégie du pipeline pour envoyer les
résultats d’une opération à une autre.
La limitante
L’espace de mémoire RAM est limité.
2010-05-24
© Domingo Palao
38
IGE487 Modélisation des bases de données
Semaine 4
Optimisation des requêtes
Optimisation des requêtes
S
e
m
a
i
n
e
Chaque RDBMS implémente une stratégie pour
optimiser les requêtes.
Le processus heuristique d’optimisation plus utilisé:
1. Analyser la requête de haut niveau pour générer une
représentation interne.
2. Appliquer les règles heuristiques pour optimiser la
représentation interne.
3. Générer un plan d’exécution basé sur les options disponibles à
propos des fichiers participant dans la requête.
IGE487 Modélisation des bases de données
4
L’heuristique principale est d’appliquer une opération
capable de réduire la taille des résultats intermédiaires.
Par exemple, appliquer les opérations SELECT et PROJECT
avant d’appliquer l’opération JOIN.
2010-05-24
© Domingo Palao
40
Optimisation des requêtes
S
e
m
a
i
n
e
Arbre de requête
Une structure d’arbre correspond à un ensemble
d’expressions de l’algèbre relationnelle.
Les relations d’entrée sont les feuilles de l’arbre.
Les opérations de l’algèbre relationnelle sont
représentées par les nœuds internes de l’arbre.
IGE487 Modélisation des bases de données
4
L’exécution d’un arbre de requête est
l’exécution des opérations (les nœuds
internes) sur les relations.
Ensuite on remplace le nœud interne par les
résultats d’exécuter l’opération.
2010-05-24
© Domingo Palao
41
Optimisation des requêtes
S
e
m
a
i
n
e
IGE487 Modélisation des bases de données
4
Exemple :
Pour chaque projet localisé en ‘Stafford’ trouver le numéro du projet, le
numéro du département, ainsi que le nom l’adresse et la date de
naissance du manager du département
Requête SQL :
SELECT P.NUMBER,P.DNUM,E.LNAME,
E.ADDRESS, E.BDATE
FROM
PROJECT AS P,DEPARTMENT AS D,
EMPLOYEE AS E
WHERE
P.DNUM=D.DNUMBER AND
D.MGRSSN=E.SSN AND
P.PLOCATION=‘STAFFORD’;
Algèbre relationnelle
πPNUMBER, DNUM, LNAME, ADDRESS, BDATE (((σPLOCATION=‘STAFFORD’(PROJECT))
DNUM=DNUMBER
2010-05-24
(DEPARTMENT))
© Domingo Palao
MGRSSN=SSN
(EMPLOYEE))
42
Optimisation des requêtes
S
e
m
a
i
n
e
IGE487 Modélisation des bases de données
4
πPNUMBER, DNUM, LNAME, ADDRESS, BDATE
( ( (σPLOCATION=‘STAFFORD’(PROJECT) )
DNUM=DNUMBER (DEPARTMENT) )
MGRSSN=SSN
(EMPLOYEE))
2010-05-24
© Domingo Palao
43
Optimisation des requêtes
S
e
m
a
i
n
e
Optimisation heuristique des arbres de requêtes
La même requête peut être représentée par
différentes expressions de l’algèbre relationnelle, et
pourtant, par plusieurs arbres de requête.
Le but ultime de l’optimisation heuristique est de
trouver l’arbre de requête plus performant au moment
de l’exécution.
IGE487 Modélisation des bases de données
4
Exemple:
SELECT
FROM
WHERE
2010-05-24
LNAME
EMPLOYEE, WORKS_ON, PROJECT
PNAME = ‘AQUARIUS’ AND
PNMUBER=PNO AND ESSN=SSN
AND BDATE > ‘1957-12-31’;
© Domingo Palao
44
Optimisation des requêtes
S
e
m
a
i
n
e
Arbre en forme canonique
C’est l’arbre d’optimisation plus simple à construire.
Il prendre les produits cartésiens des relations et
applique des opérations de sélection et projection
après.
Normalement n’est pas le meilleur choix
d’optimisation.
IGE487 Modélisation des bases de données
4
2010-05-24
© Domingo Palao
45
Règles de transformation de l’algèbre
relationnelle
S
e
m
a
i
n
e
IGE487 Modélisation des bases de données
4
1. Cascade de s:
Une condition de sélection complexe
peut être divisé dans une cascade (c’està-dire) une séquence d’opérations σ
individuelles :
σ c1 AND c2 AND ... AND cn(R) ≡ σc1 (σc2
(...(σcn(R))...) )
2. Commutativité de σ:
L’opération σ est commutative :
σc1 (σc2(R)) = σc2 (σc1(R))
2010-05-24
© Domingo Palao
46
Règles de transformation de l’algèbre
relationnelle
S
e
m
a
i
n
e
IGE487 Modélisation des bases de données
4
3. Cascade de π:
Dans une séquence en cascade d’opérations
π toutes, sauf la dernière peuvent être
ignorées:
πList1 (πList2 (...(πListn(R))...) ) ≡ πList1(R)
4. Commutation de σ avec π:
Si la condition de sélection utilise seulement
les attributs A1, ..., An dans la liste de
projection, les deux opérations peuvent être
commutées:
πA1, A2, ..., An (σc (R)) ≡ σc (πA1, A2, ..., An (R))
2010-05-24
© Domingo Palao
47
Règles de transformation de l’algèbre
relationnelle
5. Commutativité de
et x :
Les opérations et x sont commutatives :
4
6. Commutativité de σ avec ou x :
Si tous les attributs dans la condition de sélection c
utilisent seulement les attributs d’une des relations,
disons R, les deux opérations peuvent être commutées
IGE487 Modélisation des bases de données
S
e
m
a
i
n
e
R CS≡S CR
Rx S≡Sx R
σc ( R
S ) ≡ (σc (R))
S
De manière alternative, si la condition de sélection c
peut être écrit comme (c1 and c2), où la condition c1
utilise seulement attributs de R et la condition c2 utilise
seulement attributs de S, l’opération peut être commutée
comme suit :
σc ( R
2010-05-24
S ) ≡ (σc1 (R))
(σc2 (S))
© Domingo Palao
48
Règles de transformation de l’algèbre
relationnelle
S
e
m
a
i
n
e
IGE487 Modélisation des bases de données
4
7. Commutation de π avec ou avec x:
Supposons que la liste de projection est L = {A1,
..., An, B1, ..., Bm}, où A1, ..., An sont attributs
de R et B1, ..., Bm sont attributs de S. Si la
condition de jointure c utilise seulement des
attributs de L, les deux opérations peuvent être
commutées de la manière suivante :
πL ( R C S ) ≡ (πA1, ..., An (R)) C (π B1, ..., Bm (S))
8. Commutativité des opérations d’ensembles :
Les opérations υ et ∩ sont commutatives, mais “–” ne
l’est pas.
2010-05-24
© Domingo Palao
49
Règles de transformation de l’algèbre
relationnelle
S
e
m
a
i
n
e
IGE487 Modélisation des bases de données
4
9. Associativité de , x, υ, et ∩ :
Ces quatre opérations sont associatives de
manière individuelle.
C’est à dire, si nous prenons θ pour n’importe
quelle de ces opérations, nous avons:
( R θ S ) θ T ≡ R θ ( S θ T )
10. Commutativité entre σ et les opérations
d’ensemble:
L’opération σ est commutative avec υ , ∩, et –.
Si nous prenons θ comme n’importe quelle de
ces trois opérations, nous avons
σc ( R θ S ) ≡ (σc (R)) θ (σc (S))
2010-05-24
© Domingo Palao
50
Règles de transformation de l’algèbre
relationnelle
S
e
m
a
i
n
e
IGE487 Modélisation des bases de données
4
11. L’opération π commutable avec υ.
πL ( R υ S ) ≡ (πL (R)) υ (πL (S))
12. Conversion d’une séquence (σ, x) en :
Si la condition c d’une σ corresponds à la
condition de jointure, nous pouvons
convertir la séquence (σ, x) dans une
jointure comme suit :
(σC (R x S)) = (R C S)
2010-05-24
© Domingo Palao
51
IGE487 Modélisation des bases de données
Semaine 4
L’algorithme heuristique pour
optimisation
L’algorithme heuristique pour optimisation
S
e
m
a
i
n
e
IGE487 Modélisation des bases de données
4
1. Placer la requête à analyser dans la forme
canonique.
2. Utiliser la règle 1 pour diviser toute opération
SELECT avec condition complexe dans
opérations SELECT en cascade.
3. Utiliser la règle 2, 4, 6 et 10 touchant la
commutativité de SELECT avec d’autres
opérations. Il faut envoyer les opérations
SELECT le plus bas possible dans l’arbre de
requête.
4. Utiliser la règle 9 pour réarranger les feuilles
de l’arbre pour exécuter les opérations plus
restrictives en premier.
2010-05-24
© Domingo Palao
53
L’algorithme heuristique pour optimisation
S
e
m
a
i
n
e
IGE487 Modélisation des bases de données
4
5. Utiliser la règle 12, combiner l’opération
produit cartésien avec un SELECT pour
trouver une jointure
6. Utiliser les règles 3, 4, 7 et 11 (cascade et
commutation du PROJECT) pour diviser et
déplacer les attributs des listes de projection le
plus bas possible
7. Identifier les sous arbres pour représenter
groups d’opérations qui peuvent être exécutés
comme un seul algorithme
2010-05-24
© Domingo Palao
54
L’algorithme heuristique pour optimisation
S
e
m
a
i
n
e
En résumé,
Réaliser les sélections le plus tôt possible.
Effectuer les projections le pus tôt possible.
Effectuer les jointures les plus restrictives en
premier.
Effectuer les jointures supportées par une
structure d’index, de hachage ou de cluster
en premier.
IGE487 Modélisation des bases de données
4
2010-05-24
© Domingo Palao
55
Optimisation des requêtes
S
e
m
a
i
n
e
SELECT LNAME
FROM EMPLOYEE,
WORKS_ON,
PROJECT
WHERE PNAME = ‘AQUARIUS’
AND PNMUBER=PNO
AND ESSN=SSN
AND BDATE > ‘1957-12-31’;
IGE487 Modélisation des bases de données
4
a) La forme canonique
2010-05-24
© Domingo Palao
56
Optimisation des requêtes
S
e
m
a
i
n
e
IGE487 Modélisation des bases de données
4
b) On descend les
opérations SELECT
SELECT LNAME
FROM EMPLOYEE,
WORKS_ON,
PROJECT
WHERE PNAME = ‘AQUARIUS’
AND PNMUBER=PNO
AND ESSN=SSN
AND BDATE > ‘1957-12-31’;
2010-05-24
© Domingo Palao
57
Optimisation des requêtes
S
e
m
a
i
n
e
c) On applique le
SELECT plus restrictif
en premier
IGE487 Modélisation des bases de données
4
SELECT LNAME
FROM EMPLOYEE,
WORKS_ON,
PROJECT
WHERE PNAME = ‘AQUARIUS’
AND PNMUBER=PNO
AND ESSN=SSN
AND BDATE > ‘1957-12-31’;
2010-05-24
© Domingo Palao
58
Optimisation des requêtes
S
e
m
a
i
n
e
d) On remplace les SELECT
avec un PRODUIT
CARTESIEN par une
JOINTURE
IGE487 Modélisation des bases de données
4
SELECT LNAME
FROM EMPLOYEE,
WORKS_ON,
PROJECT
WHERE PNAME = ‘AQUARIUS’
AND PNMUBER=PNO
AND ESSN=SSN
AND BDATE > ‘1957-12-31’;
2010-05-24
© Domingo Palao
59
Optimisation des requêtes
S
e
m
a
i
n
e
d) On applique les
PROJECTIONS
IGE487 Modélisation des bases de données
4
SELECT LNAME
FROM EMPLOYEE,
WORKS_ON,
PROJECT
WHERE PNAME = ‘AQUARIUS’
AND PNMUBER=PNO
AND ESSN=SSN
AND BDATE > ‘1957-12-31’;
2010-05-24
© Domingo Palao
60
Exercice
S
e
m
a
i
n
e
IGE487 Modélisation des bases de données
4
Pour la requête suivante, dessiner un
arbre pour traiter la requête de la manière
plus optimale possible.
select fname, lname,addres
from employee, department
where dname = ‘Research’
and dnumber = dno;
2010-05-24
© Domingo Palao
61
IGE487 Modélisation des bases de données
Semaine 4
L’optimisation dans la base de
données
L’optimisation dans la base de données
S
e
m
a
i
n
e
IGE487 Modélisation des bases de données
4
L’optimisation de la base de données peut
être fait (et doit être fait) au plusieurs
moments:
Pendant la modélisation.
Pendant la configuration du RDBMS.
Le système d’exploitation joue un rôle déterminant.
Pendant la création de l’application.
Il y a plusieurs outils pour aider
l’optimisation de la base de données.
2010-05-24
© Domingo Palao
63
L’optimisation en pratique
S
e
m
a
i
n
e
La modélisation
La normalisation est bonne, mais…
La seule manière de valider un modèle de
données est de l’implémenter et d’exécuter
l’application.
Le même modèle de données peut
fonctionner très bien dans quelques cas et ne
pas marcher dans autres.
La dénormalisation est une option, mais il faut
toujours savoir pourquoi denormaliser.
IGE487 Modélisation des bases de données
4
2010-05-24
© Domingo Palao
64
L’optimisation en pratique
S
e
m
a
i
n
e
La configuration du RDBMS
Le choix du système d’exploitation est très important.
Le fournisseur de la base de données peut dire sur quelle
plateforme le RDBMS est plus performant.
Il faut choisir en premier le RDBMS et ensuite le système
d’exploitation et finalement le matériel.
Plus la base de données est évoluée plus options de
configuration elle a.
Quelques tips de configuration:
IGE487 Modélisation des bases de données
4
Les fichiers d’indexes dans un disque différent du disque de
données.
Avez-vous des arrays RAID ? Quel type?
Les fichiers temporaires dans un disque différent du disque de
données et du disque d’indexes.
La mémoire allouée à chaque processus est-elle suffisante? Y-a-t’il
beaucoup de pagination ?
Vérifier le comportement de la base de données fréquemment
En Oracle les vues V$...
2010-05-24
© Domingo Palao
65
L’optimisation en pratique
S
e
m
a
i
n
e
Dans la programmation de l’application
Utiliser les variables bind
SELECT *
FROM customers
WHERE customer_id= :cust_id;
IGE487 Modélisation des bases de données
4
Si on utilise cet type de variables, la requête peut être
réutilisée.
C’est-à-dire, c’est pas nécessaire de la re-scanner ni de la réanalyser.
Dans Java on préfère l’utilisation des
PreparedStatement:
PreparedStatement pstmt =
connexion.prepareStatement (« select * from
customers where customer_id = ? »);
pstmt.setInt (1,5);
2010-05-24
© Domingo Palao
66
Aqua Data Studio
S
e
m
a
i
n
e
IGE487 Modélisation des bases de données
4
2010-05-24
© Domingo Palao
67
SQL Developer
S
e
m
a
i
n
e
IGE487 Modélisation des bases de données
4
2010-05-24
© Domingo Palao
68
IGE487 Modélisation des bases de données
Semaine 4
Questions?
IGE487 Modélisation des bases de données
Semaine 4
Merci
Domingo Palao