Transcript OPTIMISATION DE QUESTIONS

‹#›
Optimisation de requêtes







1. Introduction
2. Arbres relationnels
3. Restructuration algébrique
4. Le cas de l'objet
5. Modèle de coût
6. Choix du meilleur plan
7. Conclusion
J. PHILIPP d'après G. Gardarin
1. Architecture type d'un SGBD
META-BASE
ANALYSEUR
SYNTAXE
SEMANTIQUE
SCHEMA
CONTROLE
VUES
INTEGRITE
AUTORISATIONS
OPTIMISEUR
ORDONNANCEMENT
ELABORATION
D'UN PLAN
EXECUTABLE
EXECUTION
METHODES D'ACCES
J. PHILIPP d'après G. Gardarin
‹#›
Optimisation selon la requête

‹#›
Requête statique (Static Query)
 Une requête SQL, généralement intégrée à un programme
d'application dont le code SQL est connu à l'avance et fixé, souvent
exécuté plusieurs fois. Le gain d'une bonne optimisation peut être
très important vu les répétitions.

Requête dynamique
 Une requête SQL généralement composée en mode interactif, dont
le code n'est pas connu à l'avance, souvent exécutée une seule fois.

L'optimiseur doit savoir gérer les deux types situations selon
le type de la requête.
J. PHILIPP d'après G. Gardarin
‹#›
Analyse des requêtes

L'analyse syntaxique se décompose en deux phases :
 Vérification de l'existence des noms et des relations par rapport au
schéma.
 Mise en forme canonique de la requête à savoir décomposition en
forme normale conjonctive (opérateurs ET et OU) ou disjonctive
(OU et ET), selon son traitement ultérieur par des opérateurs
élémentaires ou simplement décomposée en plusieurs questions.

La mise en forme canonique est réalisée par la génération
d'un arbre d'opérations de l'algèbre relationnelle (projection,
jointure, union, différence, intersection) appelé arbre
algébrique ou arbre relationnel, ou encore arbre de
traitement.
J. PHILIPP d'après G. Gardarin
‹#›
Etapes de l'optimisation


(1) Recherche d'une représentation canonique de la requête.
(2) Réécriture : transformation logique de la requête par :
 Simplification,
 ordonnancement des opérations élémentaires.

(3) Planning : c'est la phase d'optimisation consistant à ordonner les
opérateurs algébriques et à choisir algorithmes et mode d'exécution.
C'est la construction des plans d'exécution candidats qui se décompose
donc comme suit :
 choix des algorithmes pour chaque opérateur,
 calcul du coût de chacun des plans envisagés,
 choix du meilleur plan.


Les étapes 1 et 2 sont indépendantes des données.
L'étape 3 en est dépendante.
J. PHILIPP d'après G. Gardarin
‹#›
2. Arbres relationnels




Un arbre relationnel (arbre algébrique ou arbre de
traitement) représente une question.
Les nœuds terminaux représentent les relations, les nœuds
intermédiaires des opérations de l'algèbre relationnelle, le
nœud racine le résultat d'une question et les arcs les flux de
données entre les opérations.
Un parcours de l'arbre des feuilles vers la racine permet de
générer un plan d'exécution.
Une opération peut être exécutée soit par ensembles de tuples
soit tuple à tuple, dès la disponibilité des opérandes.
J. PHILIPP d'après G. Gardarin
‹#›
Représentation des opérateurs de l'algèbre étendue
RESTRICTION
V. CRU
=
"BEAUJOLAIS"
V
DIFFERENCE
JOINTURE
—
V. NV
=
A
V
V.CRU, V.MILL
V.NV, V.CRU
V
A. NV
TRI
PROJECTION
AGREGAT
B2
B1
PRODUIT CARTESIEN
V
UNION
COUNT(*),AVG(DEGRE)
V.CRU, V.MILL
U
A
V
B1
B2
V
J. PHILIPP d'après G. Gardarin
‹#›
Exemple d'arbre

Coût d'exécution:
RESULTAT
 107 buveurs dont 106 à Paris
107 abus dont 104 de Volnay
103 vins
107+107
*
106+107*103+
B.NOM, B.PRENOM
107+104+
…
O(1013)comparaisons de tuples !!!
A.DATE
>
01-01-90
V.CRU
=
"MACON"
A.NV
B.VILLE
B.NB
=
=
"MACON"
=
V.NV
VINS V
A.NB
ABUS A
BUVEURS B
J. PHILIPP d'après G. Gardarin
‹#›
Arbre linéaire droit
SELECT V.CRU
FROM PRODUCTEURS P, VINS V, PRODUIT R
V.CRU
WHERE V.MILLESIME = 1976 AND V.DEGRE  14
AND P.REGION = "BORDELAIS" AND P.NP = R.NP
V.MILLESIME = 1976
AND R.NV = V.NV.
V.DEGRE  14
= " Bordelais "
P.REGION
P.NP
=
R.NP
P
R. NV
R
=
V. NV
V
J. PHILIPP d'après G. Gardarin
‹#›
Typologie des arbres
Arbre linéaire droit
Arbre linéaire gauche
Arbre ramifié
J. PHILIPP d'après G. Gardarin
‹#›
Autre exemple
SELECT P.NOM, SUM(L.PRIX * (1-L.DISCOUNT))
RECETTE
FROM CLIENTS C, COMMANDES O, LIGNES L, FOURNISSEUR
F, PAYS P, CONTINENTS T
P.NOM, RECETTE
P.NOM
C.NUMCLI = O.NUMCLI
O.NUMCOM = L.NUMCO
L.NUMFOU = F.NUMFOU
L
C.NUMPAYS = F.NUMPAYS
C
F.NUMPAYS = P.NUMPAYS
F
P.NUMCONT = T.NUMCONT
$D1  O.DATE <$D1+1
P
T.NOM = "EUROPE"
WHERE
C.NUMCLI = O.NUMCLI
AND
O.NUMCOM = L.NUMCO
AND
L.NUMFOU = F.NUMFOU
AND
C.NUMPAYS = F.NUMPAYS
AND
F.NUMPAYS = P.NUMPAYS
AND
P.NUMCONT = T.NUMCONT
AND
T.NOM = " EUROPE "
AND
O.DATE
AND
O.DATE < $D1 + INTERVAL 1 YEAR
 $D1
GROUP BY P.NOM
ORDER BY RECETTE DESC ;
O
T
J. PHILIPP d'après G. Gardarin
Fonctions d'un optimiseur






‹#›
Conception d'un plan d'exécution optimisé.
Un plan d'exécution est un programme d'opérations élémentaires à
exécuter, se déroulant éventuellement en parallèle, pour évaluer le coût
d'une requête.
La réécriture est une phase de l'optimisation consistant à transformer
logiquement la requête pour en obtenir une représentation canonique.
Le planning est une phase de l'optimisation consistant à ordonner les
opérateurs algébriques et choisir les algorithmes et modes d'exécution.
L'exécution ensembliste est un mode d'exécution consistant à calculer
l'ensemble des tuples des relations en entrée d'un opérateur avant
d'évaluer ce dernier. Il est coûteux en temps.
L'exécution pipeline est un mode d'exécution consistant à démarrer une
opération le plus tôt possible, si possible dès qu'un tuple est disponible
pour une opérande au moins.
J. PHILIPP d'après G. Gardarin
3. Restructuration algébrique

‹#›
Problème :
 selon l'ordre des opérateurs algébriques dans un arbre, le coût
d'exécution est diffèrent pour les raisons suivantes :



le coût des opérateurs varient en fonction du volume des données traitées i.e.
plus le nombre de tuples des relations traitées est petit, plus les coûts cpu et
d'E/S sont minimes.
Certains opérateurs permettent de diminuer le volume des données, par
exemple la restriction ou la projection.
Les principales règles de restructuration algébrique sont les
suivantes :
J. PHILIPP d'après G. Gardarin
‹#›
Commutativité des jointures
R
S
S
R
J. PHILIPP d'après G. Gardarin
‹#›
Associativité des jointures


Il existe N!/2 arbres de jointure de N relations.
Parmi les jointures, certaines sont des produits cartésiens.
T
R
S
R
S
T
J. PHILIPP d'après G. Gardarin
Regroupement des restrictions
Ai = a
Ai = a
et
Aj = b
Aj = b
J. PHILIPP d'après G. Gardarin
‹#›
Quasi-commutativité des Projections

‹#›
Il est possible de descendre les projections, mais les attributs
utilisés doivent être conservés par la suite !!!
A1, … Ap
A1, … Ap
Ai = a
Ai = a
Ai,
A1,… Ap
J. PHILIPP d'après G. Gardarin
Récapitulation des règles de restructuration









(1) Commutativité des jointures.
(2) Associativité des jointures.
(3) Groupabilité des restrictions.
(3') Dégroupabilité des restrictions.
(4) Quasi-commutativité des projections et restrictions.
(5) Quasi-commutativité des restrictions et jointures.
(6) Quasi-distributivité des projections/jointures.
(7) Distributivité des restrictions/unions ou différences.
(8) Distributivité des projections/unions.
J. PHILIPP d'après G. Gardarin
‹#›
Heuristiques d'optimisation

Appliquer d'abord les opérations réductrices (restrictions et
projections) en les groupant sur chaque relation.





‹#›
1. Dégrouper les restrictions (Règle 3')
2. Descendre les restrictions (Règles 4, 5 et 7)
3. Grouper les restrictions aux feuilles (Règle 3)
4. Descendre les projections (Règles 4, 6 et 8)
L'ordre des unions, différences et jointures reste inchangé.
J. PHILIPP d'après G. Gardarin
‹#›
Exemple d'arbre optimisé

Résultat
107 buveurs dont 106 à Paris
107 abus dont 104 de Volnay
103 vins
B.NOM, B.PRENOM
A.NV
=
V.NV
Coût d'exécution
107+106*105+106*103+ …
O(1011) comparaisons de tuples !
B.NOM, B.PRENOM,A.NV
B.NB
B.NB, B.NOM, B.PRENOM
B.VILLE = "PARIS"
=
A.NB
V.NV
A.NB, A.NV
V.CRU = "VOLNAY"
A.DATE > 01-01-83
V
B
A
J. PHILIPP d'après G. Gardarin
Ordonnancement des jointures

Heuristiques :
 Choix des relations de taille minimum.
 Jointures pré-calculés d'abord (indexes).
 Quasi-jointures plus réductrices.

Ordonnancement des agrégats
 Permutations difficiles.
 Profiter des tris des jointures, dédoublement, etc..
 Gains importants pour MIN et MAX.
J. PHILIPP d'après G. Gardarin
‹#›
‹#›
4. Le cas de l'Objet

Les mêmes règles s'appliquent
 cas dégénéré des objets "plats"

Il faut en plus traiter
 jointures par parcours de références
 méthodes et polymorphisme (?)
 collections (imbriquées)

Il existe peu d'optimiseurs objets puissants
J. PHILIPP d'après G. Gardarin
‹#›
Algèbre d'objets

L'algèbre relationnelle est étendue :
 RESTRICTION : application d'un critère (avec méthodes) à une
classe.
 PROJECTION : application d'attributs ou de méthodes à une classe.
 JOINTURE_REF : jointure par parcours de référence.
 JOINTURE_VAL : jointure par comparaison de valeurs.
 NEST : groupage d'une collection par rapport à d'autres attributs.
 UNNEST : aplatissage d'un attribut en une collection.
 FLATEN : suppression d'un niveau de collections.
 UNION : union d'objets dans une même classe.
 DIFFERENCE : suppression des objets d'une classe dans une autre.

Langage cible d'un optimiseur de requêtes OO.
J. PHILIPP d'après G. Gardarin
Exemple de plan d'exécution

Employé
Groupe
age() < 50
directeur
ville = "Paris"
Véhicule
couleur = "Rouge" 
Fabriquant
‹#›
Exemple :
SELECT V.numéro
FROM V in Vehicule, G in V.Fabriquant,
E in G.directeur
WHERE E.age()) < 50 AND
V.couleur="rouge" AND G.ville= "Paris"
Plusieurs plans candidats




descente des projections,
sélections d'abord (?),
ordonnancement des jointures,
coût des méthodes.
numéro
J. PHILIPP d'après G. Gardarin
Problème de l'ordonnancement




‹#›
Il faut pouvoir ordonner jointures, union, différence, agrégat,
etc., en fonction des tailles des relations arguments.
Il faut pouvoir prendre en compte les algorithmes par index
pour les favoriser (sélection, jointure sur index, parcours).
Il est nécessaire de développer un modèle de coût général
permettant d'évaluer le coût d'un plan, c'est-à-dire d'un arbre
annoté par des choix d'algorithmes.
Annotation
 Marque associée à un noeud indiquant l'algorithme à utiliser pour
l'opérateur avec ses paramètres (index, hachage, …).
J. PHILIPP d'après G. Gardarin
‹#›
5. Modèle de coût

Facteur de sélectivité
 Proportion de tuples du produit cartésien des relations concernées
qui satisfont une condition.

Exemple
SELECT *
FROM R1, R2
==> s =1
SELECT *
FROM R1
WHERE A = valeur
==> s = 1/NDIST(A) avec un modèle uniforme
J. PHILIPP d'après G. Gardarin
Sélectivité des restrictions

‹#›
TAILLE (s(R)) = s * TAILLE(R) avec:
s (A = valeur) = 1 / NDIST(A)
s(A > valeur) = (max(A) - valeur) / (max(A) - min(A))
s(A < valeur) = (valeur - min(A)) / (max(A) - min(A))
s (A IN liste valeurs) = (1/NDIST(A)) * CARD(liste valeurs)
s(P et Q) = s(P) * s(Q)
s(P ou Q) = s(P) + s(Q) - s(P) * s(Q)
s( not P) = 1 - s(P)

Le coût dépend de l'algorithme (index, hachage ou balayage).
J. PHILIPP d'après G. Gardarin
Sélectivité des Projections

TAILLE(px(R)) = p(x) * (1-d) * TAILLE(R)
avec p(x) = Larg(x) / Larg(R)
d = probabilité de doubles
CARD(X) / CARD(DOM(X)) 2
J. PHILIPP d'après G. Gardarin
‹#›
‹#›
Sélectivité des Jointures

TAILE( R1 |><| R2) = p * TAILLE(R1) * TAILLE(R2)
 p dépend du type de jointure et de la corrélation des colonnes :




p = 0 si aucun tuple ne joint
p = 1 / MAX(NDIST(A),NDIST(B)) si distribution uniforme équiprobable des
attributs A et B sur un même domaine
p = 1 si produit cartésien.
La complexité de l'algorithme modifie les coûts :
 linéaire si index,
 produit des tailles si boucles imbriquées.
J. PHILIPP d'après G. Gardarin
‹#›
Le calcul des tailles


Taille des tables de base dans le catalogue.
Calcul des tailles à la compilation
 application du coefficient de sélectivité,
 hypothèse d'uniformité.

Possibilité d'histogrammes
 RunStat(<Table>, <attribut>)
 Stockage dans le catalogue de l'histogramme de distribution de
l 'attribut.
 Utilisation par le modèle de coût.
J. PHILIPP d'après G. Gardarin
6. CHOIX DU MEILLEUR PLAN
Schéma interne
Arbre d'opérations
Plans d'exécution
Générateur de
Plans
Stratégie de
Recherche
Bibliothèque de
transformations
Heuristiques
de choix
Modèle de coût
Plan d'exécution
Optimal
J. PHILIPP d'après G. Gardarin
‹#›
‹#›
Sélectivité minimum
Rel = liste des relations à joindre ;
p = plus petite relation ;
Tant que Rel non vide
{R = relation de selectivité minimum de Rel ;
p = join(R,p) ;
Relations = Relations - R ;
};
Return(p) ;
J. PHILIPP d'après G. Gardarin
Programmation dynamique
PlanOuverts = {plans mono-relation possibles} ;
Eliminer tous les plans équivalents sauf le moins coûteux ;
Pour chaque PlanOuverts p {
Pour chaque opérateur n'appartenant pas au plan p {
Etendre le plan en lui ajoutant cet opérateur ;
Calculer le coût du plan étendu;
Insérer le nouveau plan dans la liste Nouveaux ; }
Eliminer tous les plans équivalents sauf le moins coûteux ;
Transférer les plans Nouveaux dans PlanOuverts ; }
Retourner le plan optimal ;
J. PHILIPP d'après G. Gardarin
‹#›
Programmation dynamique : exemple
ScanR1
JoinS R3
JoinH R2
JoinS R2
JoinH R3
JoinH R3
JoinS R3 JoinH R2
JoinS R2
J. PHILIPP d'après G. Gardarin
‹#›
‹#›
Différentes stratégies
Stratégie
de recherche
Enumérative
Exhaustive
Augmentation
Aléatoire
Amélioration
itérative
Recuit
simulé
Génétique
J. PHILIPP d'après G. Gardarin
‹#›
Amélioration itérative
Function Iterative(Query)
p:= Parse(Query) ; // Définition d'un plan initial
S := {} ; // S : ensemble des plans localement optimaux
while not StopCond()
{
nmoves := 0;
while nmoves < MaxMoves(Query) and Transformable(p)
{
p' := Transform (p) ; // Application d'une règle de transformation
if Cost(p') < Cost(p) then // comparaison des fonctions coût
{
p ::= p';
nmoves ::= nmoves + 1;
}
Insert (S, p') ; // Maintien de l'ensemble des plans intéressants
p := Random(Parse(Query)); // Génération aléatoire d'un nouveau plan initial
}
}
return Optimal(S) ; // Select best plan among all generated ones }
J. PHILIPP d'après G. Gardarin
‹#›
Illustration II
Parse(Query)
Profitable
r1
Rand(Parse(Query))
Profitable
r'1
Rand(Rand((Parse(Query)))
Profitable
r"1
Profitable
r"2
Profitable
r2
SELECT
MINIMAL COST
PLAN
J. PHILIPP d'après G. Gardarin
‹#›
7. CONCLUSION



L'optimisation étant un problème essentiel des SGBD, il est
nécessaire de définir un modèle de coût.
Il existe plusieurs approches par compilation dans un langage
d'accès (opérateurs avec annotations).
Des stratégies de choix aléatoires existent également.
J. PHILIPP d'après G. Gardarin