Optimisation des requêtes

Cours de Bases de Données

Inge2-Info, IFIPS, Orasy

2006-07

Tao-Yuan JEN - tao-yuan.jen@u-cergy.fr

Traitement des requêtes

Requête

Analyseur de requête

Arbre de requête

Optimiseur de requêtes

Evaluateurde plans

Générateurde plans

Plan d’exécution

Opérateurs - Sélection- Projection- Jointure- Agrégations

Composition des opérateurs

Techniques :ItérationIndexPartitionnement

SélectionSans index, non trié : O(N)Sans index, trié : O(log2(N))

- en général, rangement par indexIndex :

groupant non-groupant (coûteux)

Hachage : recherche avec une égalité

Cas général : inefficace pour la disjunction

Projection

- Collection des attributs- Suppression des duplications

coûteux, seulement pour select distinct

TriHachage (double)Index itération

* Clé de recherche : combinaison de tous les attributs

Jointure

Opération la plus coûteuse

Catégories d’implémentation :Double itérationTri-fusionHachage

Evaluation de la jointure

Coût = Nb de pages entrée/sortie

Relation page fichier

R Pr n-uplets M pages

S Ps n-uplets N pages

Exemple R = 1000, Pr = 100S = 500, Ps = 80

* chaque page entrée/sortie 10 ms

Jointure par double itérationNaïve

Pour chaque r dans R pour chaque s dans S

si r.X = s.X alors (r,s) dans Res

Coût : M + Pr*M*N

R : relation externe, S : relation interne

140 heures pour l’exemple !

Jointure par double itérationpage à page

Pour chaque page pr dans R pour chaque page ps dans S

faire pr join ps dans Res

Coût : M + M*N

* Relation externe plus petite

501,500 pages : 1,4 heures pour l’exemple !

Jointure par double itérationbloc à bloc

Pour chaque Bloc Br de B pages dans R pour chaque page ps dans S

faire Br join ps dans Res

Coût : M + N*(M/B)

Si B = 100, alors 6000 pages : 1 minutes pour l’exemple !

RAM contient B+2 pages

Jointure par double itérationavec index

Pour chaque r dans R chercher s par index dans S

si r.X = s.X alors (r,s) dans Res

Coût : type d’indexsélectivité

En général, moins coûteuse que double itération page à page

Applications de tri dans SGBD

- Order by- Création d’index- élimination de duplication- jointure

Tri externe :tri-fusion + tri rapide interne

Jointure par tri-fusion

Trier R sur X; Trier S sur XTri-fusionner R et S sur X

Coût : M*logM + N* logN+ (M + N)

Jointure par hachageEtape 1 : hachage de R sur X et hachage de S sur X avec fonction h

Etape 2 : Pour chaque groupe Gr de R

Hachage de Gr avec une autre fonction ggroupe Gs (correspond Gr)join Gren utilisant g

Coût : 2* (M + N) + (M + N)

Opérations d’agrégat

- Parcours + mise à jour des variables adaptéesSUM : totalAVG : total + comptage…

- GROUP BYTrier sur les attributs de groupementCréer un table de hachage dans RAM

Optimisation

- Analyseur- Arbre de requête - Réécriture de l’arbre

descendre les projectiondescendre les sélection

- Génération de plan d’exécutionchoix des méthodesenchaînement ou matérialisation

Sous-requête imbriquée

Select S.AFrom SWhere S.B In (Select R.B

From R Where R.C = c)

Select S.AFrom S, RWhere S.B = R.B And R.C = c

Sous-requête corrélées

Select S.AFrom SWhere Exists (Select *

From R Where R.C = c

And S.B = R.B)

Select S.AFrom S, RWhere S.B = R.B And R.C = c

Optimisation et sous-requête

- Optimisation locale- Manque de connaissance d’équivalence

Donc, optimisation par l’utilisateur

Réécriture

- traitement des agrégats- relation externe à gauche - gauche- profond style pour la jointure de

plusieurs relations- descendre la sélection et la projection

Traitement des agrégats

Projection S.A, Min(T.C) (Having_Count(*)=2(Group_By S.A(Selection S.B = R.B, T.D = S.D, S.C = c( R*S*T))));

Select S.A, Min(T.C)From T, S, RWhere S.B = R.B And T.D = S.D And S.C = cGroup By S.AHaving Count (*) = 2;

Projection S.A, T.C (Selection S.B = R.B, T.D = S.D, S.C = c( R*S*T));

Optimiseur