1 / 55

OPTIMISATION

OPTIMISATION. Institut National des Sciences Appliquées – Rouen Département Architecture des Systèmes d’Information. SGBD RELATIONNEL. LE CHOIX DU CHEMIN D'ACCES AUX DONNEES EST FAIT PAR LE SYSTEME. QUESTIONS SIMPLES : * CHOIX PEU SIGNIFICATIF * FAIBLE COUT QUESTIONS COMPLEXES :

howe
Download Presentation

OPTIMISATION

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. OPTIMISATION Institut National des Sciences Appliquées – Rouen Département Architecture des Systèmes d’Information

  2. SGBD RELATIONNEL • LE CHOIX DU CHEMIN D'ACCES AUX DONNEESEST FAIT PAR LE SYSTEME. • QUESTIONS SIMPLES : * CHOIX PEU SIGNIFICATIF * FAIBLE COUT • QUESTIONS COMPLEXES : * TEMPS DE REPONSE DEPENDANT DU BON CHOIX * COUT ELEVE POUR REALISER LE BON CHOIX • FAIRE LE BON CHOIX : TEMPS D'EXECUTION QUESTION OPTIMISEE + TEMPS D'OPTIMISATION ==> INFERIEUR A QUESTION NON OPTIMISEE

  3. TRAITEMENT D'UNE REQUETE • ANALYSE DE LA QUESTION - SYNTAXIQUE - SEMANTIQUE • MODIFICATION DE QUESTION - LOGIQUE - FACITITES UTILISATEURS (VUES, INTEGRITE, DROITS)‏ • OPTIMISATION(PLAN D'EXECUTION)‏ - ALGEBRIQUE - MODIFICATION DE QUESTION - EVALUATION DE PLAN

  4. TRAITEMENT D'UNE REQUETE (2)‏

  5. OBJECTIFS • TRADUCTION DES REQUETES UTILISATEURS (EXPRIMEES DANS UN LANGAGE ASSERTIONNEL) DANS UNE EXPRESSION OPTIMISEE DU PLAN D'EXECUTION • TROIS TECHNIQUES DE BASE * RESTRUCTURATION ALGEBRIQUE --> CLASSIQUE * MODIFICATION DE QUESTIONS --> INGRES * EVALUATION DU COUT --> SYSTEM-R

  6. ACTEURS • DEUX INTERVENANTS L'EVALUATEUR :TRADUIRE LA REQUETE UTILISATEUR EN UNE SUITE D'OPERATIONS ELEMENTAIRES DE L'ALGEBRE RELATIONNELLE. L'OPTIMISEUR :ASSOCIER A CETTE SUITE UN PLAN D'EXECUTIONAFIN QUE LE COUT D'EXECUTION DE LA REQUETE SOIT MINIMUM.  • MOMENT DE L'OPTIMISATION COMPILATION - Optimisation a priori - Stockage dans une bibliothèque INTERPRETE : - Optimisation a chaque requête

  7. 1. ANALYSE DE LA QUESTION • ANALYSE SYNTAXIQUE A/ VERIFIER LA COHERENCE DE LA QUESTION PARRAPPORT AU SCHEMA DE LA BD : => EXISTENCE DES RELATIONS, ATTRIBUTS, ...  B/ MISE SOUS FORME STANDARD DE LA REQUETE SOUSFORME NORMALE CONJONCTIVE (OU DISJONCTIVE)‏ FNC : (A1 OR A2 OR ... AN) AND (B1 OR B2 OR ... BN) FND : (A1 AND A2 AND ... AN) OR (B1 AND B2 AND ... BN)‏

  8. 1. ANALYSE DE LA QUESTION (2)‏ • ANALYSE SEMANTIQUE VERIFIER LA CORRECTION DE LA QUESTION • GRAPHE DE CONNECTION DES RELATIONS(PAS DE RELATIONS ISOLEES): Un sommet pour chaque relation Une jointure est représentée par un arc Une sélection par une boucle sur la relation • GRAPHE DE CONNECTION DES ATTRIBUTS NORMALISES(PAS DE CRITERES CONTRADICTOIRES) : Un sommet est associe a chaque référenced'attribut ou de constante, Une jointure est représentée par un arc entreles attributs participants, Une sélection par un arc entre un attribut et une constante

  9. VERIFICATION DE LA CORRECTION DE LA QUESTION • UNE REQUETE EST INCORRECTE SI 1. SON GRAPHE DE CONNECTION DES RELATIONSN'EST PAS CONNEXE => IL EXISTE AU MOINS UNE RELATION ISOLEE NON RATTACHEE AUX AUTRES OU BIEN 2. SON GRAPHE DE CONNECTION DES ATTRIBUTSPRESENTE UN CYCLE DONT LA SOMME DESVALUATIONS EST NEGATIVE => LA QUESTION EST CONTRADICTOIRE

  10. VERIFICATION DE LA CORRECTION D'UNE QUESTION • BUVEURS (NB, NOM, PRENOM, VILLE, REGION)‏ • VINS (NV, CRU, REGION, MILL, DEGRE)‏ • ABUS (NV, NB, DATE, QUANTITE)‏ SELECT * FROM VINS V, ABUS A WHERE V.MILL = 1978 AND A.DATE < 1976 LES RELATIONS ABUS ET VINS NE SONT PAS RELIEES DANS LE GRAPHE DES RELATIONS ==> CETTE SELECTION N'EST PAS SATISFAISABLE

  11. VERIFICATION (2)‏

  12. 2. MODIFICATION DE QUESTIONS •  DEFINITION UNE REQUETE PEUT ETRE TRANSFORMEE EN PLUSIEURS ARBRES ALGEBRIQUES (IL Y PLUSIEURS FACONS DE DEFINIR UNE REQUETE EN SQL ET EN ALGEBRE)‏ • BUT 1. METTRE LA REQUETE SOUS UNE FORME STANDARD 2. SIMPLIFIER EN ELIMINANT LES REDONDANCES 3. CONSTRUIRE UNE EXPRESSION EQUIVALENTE PLUS PERFORMANTE A EVALUER

  13. 2. MODIFICATION DE QUESTIONS (2)‏ • PRINCIPE : QUESTIONS EQUIVALENTES DEUX QUESTIONS SONT EQUIVALENTES SI ELLES DONNENT LE MEME RESULTAT POUR TOUTE EXTENSION DE LA BD • 2 MOYENS :  1. CRITERES = PREDICATS DE LA LOGIQUE DU PREMIER ORDRE => LOGIQUE 2. UTILISATION DES FACILITES UTILISATEURS

  14. FACILITES UTILISATEURS(Exemple : CONTRAINTES D'INTEGRITE)‏ • DEPART UNE QUESTIONAVEC QUALIFICATION UN ENSEMBLE DE CONTRAINTES D'INTEGRITE I1, I2, ... In • PRINCIPE : NON CONTRADICTION QUESTION /CONTRAINTE D'INTÉGRITÉ (DETECTION IMMEDIATE)‏ • EXEMPLE : SELECT * FROM VINS WHERE CRU = 'JURANCON' AND DEGRE < 10

  15. FACILITES UTILISATEURS (2)‏ • CONTRAINTE D'INTEGRITE SUR VINS : CRU = 'JURANCON' => DEGRE >= 12 • REQUETE MODIFIEE : SELECT * FROM VINS WHERE CRU = 'JURANCON' AND DEGRE < 10 AND DEGRE > 12 => REPONSE VIDE

  16. 3. OPTIMISATION DE QUESTION • LORSQUE LA REQUETE EST CORRECTE, LEBUT EST D'ASSOCIER UN PLAND'EXECUTION DONT LE COUT EST MINIMUM ET DONT LA DETERMINATION NE NECESSITE PAS TROP DE TEMPS. • 3 METHODES 1. Restructuration algébrique 2. Décomposition de questions 3. Evaluation de plans REMARQUE : CES METHODES NE SONT PAS EXCLUSIVES

  17. ARBRE ALGEBRIQUE • ARBRE ayant pour NOEUDS lesOPERATIONS de L'ALGEBRE RELATIONNELLE, pour FEUILLE des RELATIONS et dont les ARCS représentent les FLUX DES DONNEES Remarque : Plusieurs arbres différents permettentde modéliser la même requête

  18. Restructuration algébrique • "DONNER LES NOMS ET LES PRENOMS DES BUVEURS HABITANT Paris QUI ONT COMMANDE DU Mâcon 1977 AVANT LE 15/05/88"

  19. Restructuration algébrique (2)‏

  20. Restructuration algébrique (3)‏ • PRINCIPE : Suivant L'ORDRE DES OPERATEURS ALGEBRIQUES dans un arbre, LE COUT D'EXECUTION est DIFFERENT • POURQUOI ?  1. LE COUT DES OPERATEURS VARIE EN FONCTION DU VOLUMEDES DONNEES QU'ILS TRAITENT(PLUS LE NOMBRE DE TUPLE DES RELATIONS TRAITEES EST PETIT, PLUS LES COUTS CPU ET D'E/S SONT MINIMISES)‏ 2. CERTAINS OPERATEURS DIMINUENT LE VOLUME DES DONNEES • IL FAUT DONC : POUR CHAQUE OPERATEUR, EVALUER LA TAILLE DE LA RELATION RESULTAT, APRES APPLICATION DES OPERATEURS ALGEBRIQUES

  21. FACTEURS • LA TAILLE D'UNE RELATION R EST EGALE AU NOMBRE DE TUPLES DE R QUE MULTIPLE LA LONGUEUR DE SES TUPLES TAILLE (R) = Nb_tuples (R) * Lg (R)‏ • 1. SELECTION : a (R)‏ TAILLE (a(R)) = p(a) * TAILLE (R)‏ p (a) = probabilité que a soit vérifiée  => PUISSANT REDUCTEUR • 2. PROJECTION :  x (R)‏ TAILLE ( x (R)) = L(x) * TAILLE (R)‏ Avec L(x) = Lg (x) / Lg (R)‏ Sans élimination de doubles => PUISSANT REDUCTEUR

  22. FACTEURS (2)‏ • 3. JOINTURE : R1 R2 J * TAILLE (R1) * TAILLE (R2)‏ Avec : J dépends de chaque jointure J = 0 Si aucun tuple ne joint J = 1 Si produit cartésien  Nb_tuple (R1) * Nb_tuple (R2) * (Lg (R1) + Lg (R2))‏ => PEUT AUGMENTER LA TAILLE DE LA RELATION RESULTAT

  23. HEURISTIQUE • APPLIQUER D'ABORD TOUTES LES SELECTIONS ET PROJECTIONS • MOYEN : UTILISATION DES REGLES DE RESTRUCTURATION D'OPERATEURS ALGEBRIQUES • 1. PROPRIETES DES JOINTURES : (1) COMMUTATIVITE (2) ASSOCIATIVITE  • 2. PROPRIETES DU PRODUIT CARTESIEN : (3) COMMUTATIVITE (4) ASSOCIATIVITE

  24. HEURISTIQUE (2)‏ • 3. PROPRIETES DES  :  (5) CONCATENATION DES  (6) CONCATENATION DES  • 4. COMMUTATION DES  AVEC (7) LA JOINTURE (8) LE PRODUIT CARTESIEN (9) LA PROJECTION (10) L'UNION (11) LA DIFFERENCE • 5. COMMUTATION DES  AVEC (12) LA JOINTURE (13) LE PRODUIT CARTESIEN (14) L'UNION

  25. Graphiques

  26. Graphiques (2)‏

  27. Graphiques (3)‏

  28. Graphiques (4)‏

  29. Graphiques (5)‏

  30. DECOMPOSITION DE QUESTION • OUTIL : GRAPHE DES RELATIONS • PRINCIPE : LE DETACHEMENT DE QUESTIONS : TRANSFORMATION CONSISTANT A DIVISER LE GRAPHE DES VARIABLES EN DEUX COMPOSANTES CONNEXES PAR DEDOUBLEMENT D'UN NOEUD. • DETACHEMENT POSSIBLES : SELECTIONS, SEMI-JOINTURES

  31. DECOMPOSITION DE QUESTION (2)‏ • SELECTIONS TOUTE SELECTION PEUT ETRE DETACHEE • EXEMPLE SELECT Ai FROM R1, R2, ... Rm WHERE Rj. Ai = Rk. Ai and R1. An = VALEUR  INSERT INTO T SELECT * FROM R1 WHERE R1. An = VALEUR SELECT Ai FROM T, R2, ... Rm WHERE Rj. Ai = Rk. Ai

  32. DECOMPOSITION DE QUESTION (3)‏ • REQUETE :  "Buveurs habitant Macon ayant bu du Maconaprès le 1/1/83" : Q1 : INSERT INTO V' SELECT NV FROM VINS WHERE CRU = 'MACON' Q2 : INSERT INTO A' SELECT NB FROM ABUS WHERE DATE >= "1/1/83" Q3 : INSERT INTO T SELECT NB FROM A', V' WHERE A'.NV = V'.NV Q4 : INSERT INTO B' SELECT NOM, PRENOM, NB FROM BUVEURS WHERE VILLE = 'MACON‘ Q5 : SELECT NOM, PRENOM FROM B', T WHERE BUVEUR.NB = T'.NB

  33. DECOMPOSITION DE QUESTION (4)‏ • SEMI-JOINTURES LA SEMI-JOINTURE DE R PAR S, NOTEE R S, EST LA JOINTURE DE R PAR S, PROJETEE SUR LES ATTRIBUTS DE R R S = r (R S)‏ • TOUTE SEMI-JOINTURE PEUT ETRE DETACHEE

  34. DECOMPOSITION DE QUESTION (5)‏ • EXEMPLE SELECT R.B FROM R,S WHERE R.A = S.A and PRED (R)‏ => INSERT INTO T SELECT R. *   FROM R, S WHERE R.A = S.A SELECT T.B FROM T WHERE PRED (T)

  35. EVALUATION DE PLANS • METHODES D'EVALUATION DE COMPOSANTS DE REQUETE DE COMPLEXITE VARIABLE AVEC UN PRINCIPE DE BASE : L'EVALUATION DE LA TAILLE DES RESULTATS • PROBLEME POUR CHAQUE RELATION, CHOISIR LE MEILLEUR CHEMIND'ACCES ET LE MEILLEUR ALGORITHME DE JOINTURE

  36. EVALUATION DE PLANS (2)‏ • ELEMENTS DISPONIBLES : 1. METHODES D'ACCES - SEQUENTIEL - INDEXE - PLACANT - NON PLACANT 2. ALGORITHMES DE JOINTURE - BOUCLES IMBRIQUEES - TRI-FUSION - HACHAGE 3. STATISTIQUES SUR LES RELATIONS ET LES INDEX

  37. EVALUATION DE PLANS (3)‏ METHODE D'ACCES CAS SANS INDEX - FILTRAGE EN SEQUENTIEL DU FICHIER - TRIER LA RELATION (GARDEE TRIEE) ASCENDANTE, DESCENDANTE, DICHOTOMIQUE Ex : "RANGE QUERIES" CAS AVEC INDEX - UNI-DIMENSIONNEL (ISAM, Arbre-B)‏ - MULTI-DIMENSIONNEL (Grid File, Arbres de Prédicats)‏

  38. STRUCTURE DE STOCKAGE • DONNEES STOCKEES DANS DES SEGMENTS : • RELATIONS STOCKEES ENTIEREMENT DANS UN SEGMENT. • PLUSIEURS RELATIONS PAR SEGMENT. • SEGMENTS TYPES : TEMPORAIRES, PERMANENTS. • ALLOCATIONS REGROUPEES DE PAGES.

  39. CHEMINS D'ACCES • INDEX TRIES MONO OU MULTI-ATTRIBUT. • INDEX BASES SUR LES ARBRES-B. • EXISTANCE DES TIDs : Nro PAGE, ADR. Indirecte/Page • POSSIBILITE D'AVOIR DES INDEX PLACANTS : 2 TYPES DE SEGMENTS - SEQUENTIELS AUCUN ORDRE LOGIQUE DES TUPLES - CLUSTERS LES TUPLES SONT INSERES DANS L'ORDRE D'UN INDEX (Triés ou pré- jointure)‏

  40. METHODE • GENERATION DE PLUSIEURS PLANS COUT COMBINATOIRE ==> TROP COUTEUX. • SOLUTION ELIMINER DES PLANS A PRIORI-HEURISTIQUES- * OPTIMUM LOCAL * COMPILATION DU PLAN D'EXECUTION OBTENU * INSERER DANS UN CATALOGUE DE PLANS. • PROBLEME * PLAN STATIQUE. NE TIENT PAS COMPTE DU CHANGEMENT DE TAILLES DES DONNEES. * SI LA METHODE D'ACCES CHANGE EN - : RECOMPILER LA QUESTION EN +: ON NE PEUT PAS EN PROFITER

  41. CHOIX DES ALGORITHMES(JOINTURE)‏ • PAR BOUCLE IMBRIQUEE Pour CHAQUE PAGE DE R1 Pour CHAQUE PAGE DE R2  JOINDRE (P.R1, P.R2)‏ Fpour Fpour COUT =  R1  *  R2  • PAR TRI-FUSION TRIER (R1); TRIER (R2)‏ FUSIONNER (R1, R2)  COUT = 2  R1  LOG  R1  +  R2  LOG  R2  +  R1 + R2

  42. CHOIX DES ALGORITHMES (2)‏ • JOINTURE (AVEC INDEX) SUR R1  POUR CHAQUE PAGE DE R2 LIRE (R2)  POUR CHAQUE TUPLE LU  ACCEDER A R1 EN INDEX JOINDRE SI SUCCES Fpour Fpour • SUR R1 ET R2  -> FUSIONNER LES INDEX

  43. CHOIX DES ALGORITHMES (3)‏ • PAR HACHAGE HACHER (R1) (+ PETITE RELATION)‏ POUR CHAQUE PAGE DE R2 LIRE (R2)  POUR CHAQUE TUPLE LU  ACCEDER AU FICHIER ALEATOIRE JOINDRE SI SUCCES  Fpour Fpour   COUT =  R1  + A  R1  +  R2  + A  R2  • PAR SEMI-JOINTURE  EFFECTUER LES SEMI-JOINTURES R1 R2 ET R2 R1 AVANT JOINTURE

  44. CHOIX DES ALGORITHMES (4)‏ • EXECUTION PARALLELE (EVITER L'ACCES REPETITIF AUX MEMES DONNEES)‏ - EVALUATION DE TOUTES LES SELECTIONSSUR UNE RELATION EN UNE SEULE PASSE - JOINTURE EN PARALLELE A.NV = V.NV ET A.NB = B.NB LA RELATION A N'EST LUE QU'UNE SEULE FOIS - PIPELINE D'OPERATIONS: ENCHAINER SELECTION ET PROJECTION SUR UNE MEME RELATION

  45. CHOIX DU PLAN D'EXECUTION • POUR CHAQUE SOLUTION UN COUT EST CALCULE : COUT = NOMBRE D'ACCES PAGES + W * Nombre d'appels internes (W : IMPORTANCE DU TEMPS CPU PAR RAPPORT AUX E/S OU LE TEMPS DES E/S EST FONCTION DU NOMBRE DE PAGE ACCEDEES)‏

  46. CHOIX DU PLAN D'EXECUTION (2)‏ • STATISTIQUES * NBTUP : Nombre de tuples * NBPAG : Nombre de pages * NBCLE : Nombre de clés * NCARD (R) : Cardinalité * TCARD (R) : Nombre de pages * MIN (attribut) : Valeur minimum d'un attribut numérique * MAX (attribut) : Valeur maximum d'un attribut numérique * ICARD (I): Nombre clés distinctes dans index * NBINDX (I) : Nombre pages de l'index * CLUSTER (I): Index plaçant ou non * RISCARD = F(PRED) * NBTUP

  47. CALCUL DU FACTEUR DE SELECTIVITE • ATTRIBUT = VALEUR • ATTRIBUT > VAL • VAL 1 < ATTRIBUT < VAL 2 • P1 OR P2 : F = F (P1) + F (P2) – F (P1) * F (P2)‏ • P1 AND P2 : F = F (P1) * F (P2)‏ • NOT P : F = 1 - F (P)‏

  48. CALCUL DU COUT TOTAL D'UNE SELECTION • ACCES SUR CLE PRIMAIRE : 1 + 1 + W • ACCES SUR CLE SECONDAIRE VIA UN PREDICAT P : - INDEX CLUSTERISE : F (P) * (NBINDX + TCARD) + W * RSICARD - INDEX NON CLUSTERISE : F (PRED) * (NBINDX + NCARD) + W * RSICARD • ACCES SEQUENTIEL (SCAN) : TCARD + W * RSICARD • TRI EVENTUEL

  49. EXEMPLE

More Related