Optimisation des Requtes Relationnelles

1
Optimisation des Requêtes Relationnelles

• Chapitre 15

2
Survol de lOptimisation

• Plan Arbre dopérateurs de lalgèbre
  relationnelle, plus un choix dalgorithme pour
  chaque opérateur.
• Lorsquun opérateur est choisi pour calculer les
  prochains tuples, il fait appel à ses propres
  inputs pour calculer ces prochains tuples
  (  pipelining).
• Pour chaque requête, il y a 2 problèmes à
  considérer
• Lespace des plans à considérer
• Algorithme de recherche afin de trouver le plan
  le moins couteux (p.ex., lon évite les produit
  Cartésiens)
• Considère seulement les left-deep plans.
• Le coût estimé du plan
• Idéalement nous cherchons le meilleur plan
  pratiquement, nous évitons les pires plans.

3
Heuristiques pour les Opérations Relationnelles

• Quelles sont les heuristiques utilisées dans
  loptimisation de lalgèbre relationnelle ?
• Traduction de SQL en algèbre relationnelle
• Utilisation des statistiques dans loptimisation
• Génération des plans alternatifs durant
  loptimisation
• Évaluation des requêtes imbriquées
• Exemple
• Sailors(sidinteger, sname string,
  rating integer, age real)
• Boats(bidinteger, bname string, color
  string)
• Reserves(sid integer, bid integer, day date,
  rname string)
• Supposez
• longueur des tuples de Reserves 40 bytes
  tuples/pg 100 pgs 1000
• longueur des tuples de Sailors 50 bytes
  tuples/pg 80 pgs 500

4
Blocs dune Requête Unités dOptimisation

• Une requête SQL est décomposée syntaxiquement en
  une collection de blocs de requêtes qui seront
  traduit en A.R. et optimisées un à la fois.
• Les blocs imbriqués sont dhabitude traités comme
  des appels de procédure, un par tuple de la
  relation externe.

SELECT S.sname FROM Sailors S WHERE S.age IN
(SELECT MAX (S2.age) FROM Sailors
S2 GROUP BY S2.rating)
Bloc imbriqué
Bloc externe

• Pour chaque bloc, les plans considérés sont
• Toutes les méthodes daccès de chaque relation
  de la clause FROM.
• Tous les arbres de join penchés vers la gauche,
  considérant toutes les permutations des relations
  de la clause FROM ainsi que tous les algorithmes
  de join.

5
Equivalences de l Algèbre Relationnelle

• Les équivalences de lA.R. permettent de choisir
  différents ordres de joins et faire les
  sélections et projections avant les joins.
• Sélections
  (Cascade)

(Commutativité)

• Projections

(Cascade)
(Associativité)

• Joins

R (S T) (R S) T
(Commutativité)
(R S) (S R)

• Exemple

R (S T) (T R) S
6
Equivalences de l Algèbre Relationnelle (Suite)

• Une projection est commutable avec une sélection
  qui porte uniquement sur les attributs de la
  projection.
• Une sélection impliquant des attributs de 2
  arguments dun produit Cartésien est convertible
  en un join.
• Une sélection ne mentionnant que les attributs de
  R est réductible à R (i.e., (R S)
  (R) S ).
• De même, si une projection suit un join R S,
  nous pouvons la pousser au niveau de R et S en ne
  retenant que les attributs de R et S dont on a
  besoin pour le join.

7
Enumération des Plans Alternatifs

• Deux cas sont à considérer
• Plans à une seule relation
• Plans à multiples relations
• Les requêtes à une seule relation consistent en
  une combinaison de sélections, projections et
  opérations dagrégat
• Chaque chemin daccès disponible (scannage ou
  index) est considéré et le moins couteux est
  choisi.
• Les différentes opérations sont exécutées
  ensemble (P.ex. si un index est utilisé pour une
  sélection, une projection est effectuée pour
  chaque tuple extrait et le résultat de cette
  projection est immédiatement utilisé pour
  calculer lagrégat.

8
Estimation des Coûts

• Pour chaque plan considéré, on effectue ce qui
  suit
• Estimer le coût de chaque opération dans larbre
  plan.
• Ce coût dépend des cardinalités des relations
  dentrée.
• Lestimation des coûts des diverses opérations
  (scannage, joins, etc.) a été discutée au
  Chapitre 14 ainsi que dans la Section 8.4.
• Estimer la taille du résultat de chaque opération
  dans larbre.
• Linformation sur les relations dentrée est
  utilisée.
• Pour les sélections et les joins nous supposons
  que les termes mentionnées dans les conditions
  sont indépendants les uns des autres.

9
Coûts des Plans à une Seule Relation

• Existence dindex I sur la clé primaire
  correspondant à la condition de sélection
• Arbre B Height(I)1 Hachage 1.2
• Index groupé I correspondant à un ou plusieurs
  termes de la condition de sélection
• (NPages(I)NPages(R)) produit des FRs des
  termes correspondants.
• Index non-groupé I correspondant à un ou
  plusieurs termes de la condition de sélection
• (NPages(I)NTuples(R)) produit des FRs des
  termes correspondants.
• Scannage séquentiel de la relation
• NPages(R).
• Note par défaut, aucune élimination des
  duplicatas nest effectuée lors des projections.

10
Exemple
SELECT S.sid FROM Sailors S WHERE S.rating8

• Supposez quun index existe sur rating
• (1/NKeys(I)) NTuples(R) (1/10) 40000 tuples
  extraits.
• Index groupé (1/NKeys(I)) (NPages(I)NPages(R))
  (1/10) (50500) pages sont extraites.
• Index non-groupé (1/NKeys(I))
  (NPages(I)NTuples(R)) (1/10) (5040000)
  pages sont extraites.
• Supposez quun index existe sur sid
• Nous navons pas de choix que dextraire tous les
  tuples et toutes les pages. Avec un index
  groupé, le coût serait 50500 avec un index
  non-groupé, il serait 5040000.
• Scannage séquentiel
• Nous extrayons toutes les pages (500).

11
Requêtes à Multiple Relations

• Heuristique fondamentale du Système R
  nutiliser que les plans inclinés vers la gauche
  ( left-deep join trees ).
• Le nombre de plans alternatifs croit très
  rapidement avec le nombre de joins. Doù nous
  devons restreindre lespace de recherche.
• Les plans inclinés vers la gauche permettent de
  faire léconomie des tables temporaires pour
  stocker les résultats intermédiaires.

12
Enumération des Plans Inclinés vers la Gauche

• Ces plans diffèrent dans lordre des relations,
  le chemin daccès des relations et lalgorithme
  de join utilisé.
• Enumération en N passages (Si N relations sont
  jointes)
• Passage 1 Trouver le meilleur plan à une
  relation pour chaque relation.
• Passage 2 Trouver le meilleur moyen de faire le
  join du résultat de chaque plan à 1 relation
  (comme relation externe) avec une autre relation.
  (Tous les plans à 2 relations.)
• Passage N Trouver le meilleur moyen de faire le
  join du résultat de chaque plan à N-1 relations
  (comme relation externe) avec la Nème relation.
  (Tous les plans à N relation.)
• Pour chaque sous-ensemble des relations, ne
  retenir que
• Le plan le moins couteux pour chaque ordre
  intéressant des tuples.

13
Enumération des Plans (Suite)

• ORDER BY, GROUP BY, opérations dagrégats, etc.
  sont traités comme étape finale en utilisant
  soit un plan produisant les tuples dans un ordre
  intéressant, soit un operateur de tri
  additionnel.
• Un plan à N-1 relation nest pas combiné avec une
  relation additionnelle, à moins quil y ait une
  condition de join qui les lie ou que tous les
  termes dans la clause WHERE aient déjà été
  utilisés.
• Ainsi, on évite les produits Cartésiens, dans la
  mesure du possible.
• Bien que lespace des plans est élagué, cette
  approche produit encore un nombre exponentiel de
  plans.

14
Coûts des Plans à Multiples Relations
SELECT attribute list FROM relation list WHERE
term1 AND ... AND termk

• Considérez le bloc
• Le maximal de tuples dans le résultat est le
  produit des cardinalités des relations dans la
  clause FROM.
• Le Facteur de réduction (FR) associé avec chaque
  terme reflète limpact de ce terme dans la
  réduction de la taille du résultat.
• Cardinalité du résultat max de tuples
  produit des FRs.
• Les plans à multiples relations sont construits
  en joignant une seule nouvelle relation à chaque
  fois.
• Le coût de lalgorithme du join, plus
  lestimation de la cardinalité du join nous
  fournissent à la fois lestimation du coût du
  join ainsi que celle de la taille du résultat.

15
Exemple
Sailors Index B sur rating Hachage sur
sid Reserves Index B sur bid

• Passage1
• Sailors Larbre B correspond à ratinggt5
  il est probablement moins
  coûteux. Cependant, si
  lon prévoit que cette
  sélection extraira un nombre élevé de
  tuples et que larbre B est
  non-groupé,
  un scannage serait meilleur.
• Larbre B est choisi car les tuples sont
  ordonnés selon rating).
• Reserves Larbre Bsur bid correspond à
  bid500.

• Passage 2
• Considérez chaque plan retenu au passage 1 comme
  relation externe et le joindre avec la seule
  relation restante. Ainsi, si lon prend Reserves
  comme relation externe, le hachage existant sur
  sid peut être utilisé pour extraire les tuples
  correspondants de Sailors.

16
Requêtes Imbriquées
SELECT S.sname FROM Sailors S WHERE EXISTS
(SELECT FROM Reserves R WHERE
R.bid103 AND R.sidS.sid)

• Le bloc imbriqué est optimisé indépendamment avec
  le tuple de la relation externe qui fournit la
  condition de sélection.
• Le bloc imbriquant est optimisé en prenant en
  considération le coût de lappel du bloc
  imbriqué.
• Lordre implicite de ces deux blocs interdits
  certaines bonnes stratégies doptimisation.
  Souvent, la requête non-imbriqué équivalente est
  mieux optimisable.

Bloc imbriqué à optimiser SELECT FROM
Reserves R WHERE R.bid103 AND S.sid
valeur ext.
Requête non-imbriquée équivalente SELECT
S.sname FROM Sailors S, Reserves R WHERE
S.sidR.sid AND R.bid103
17
Résumé

• Loptimisation fait deux considérations
  importantes
• Un ensemble de plans alternatifs qui forme un
  espace exponentiel à élaguer en ne considérant
  que les plans incliné vers la gauche.
• Une estimation du coût de chaque plan.
• Estimation de la taille du résultat et du coût de
  chaque opérations du plan.
• Utilisation des statistiques, indexes et
  implémentations appropriées des opérateurs.
• Requêtes à une relation
• Considérer tous les chemins daccès et choisir le
  meilleur.
• Considérer les sélections qui correspondent aux
  indexes et combiner les sélections, projections
  et opérations dagrégat.
• Requêtes à multiples relations
• Enumérer les plans à 1 relations en considérant
  les sélections/projections aussitôt que possible.
• Pour chaque plan à i-1 relations (i lt
  relations) considérer la meilleur manière de le
  joindre avec une des N-i relations restantes.
• Ne retenir à chaque niveau que les meilleurs
  plans pour chaque ordre intéressant de tuples.