vertigo/cnam, paris 1

Vertigo/CNAM, Paris 1

Evaluation desrequêtes

Contenudecettepartie:

Intr oduction

Algorithmes pour l’évaluation d’une requête

Optimisation d’une requête


Optimisation

SQLestdéclaratif. L’utilisateur:

indiquecequ’il veutobtenir.

n’indiquepascomment l’obtenir.

Doncle systèmefait le reste:

comprendrela requête:il traduit la requêteenalgèbrerelationnelle

Choisirla meilleurestratégied’évaluation.On obtientunpland’exécutionphysique

Evaluerle pland’exécution


L’optimisation sur un exemple

Considéronsle schéma:� �� !� "#"$� �%� & ��' �(� � )* �,+-+-�� /.(��0 %�� et la requête:� �� 0 & ��' �1� �321� 4�5� �� /�,+-+6�� !.�+879� � � :/;=<>.(+-�,0?��SELECTAdresse

FROM CINEMA, SALLE

WHERE capacité@ 150

AND CINEMA.cinéma= SALLE.cinéma


En algèbre relationnelle

Traduitenalgèbre,on aplusieurspossibilités.En voici deux:

1. A4BDCFEHGIKJML �ON B LQP/L?R CTSUGIWVYXKZ\[ �%� �� ] H� "$"#� �\�2. A4BDCFEHGIKJML �� ] N B LQP/L?R CTSUGI^VYXKZQ[ �� H� "$"#� �\�

La deuxièmeestmeilleure.


Choix d’un plan d’exécutionphysique

Choixd’un ensembled’opérateursphysiques(engrasci-dessous)et choixd’un ordre

d’exécution

AccèsséquentieldeSALLE : fairela sélectionsurSALLE etprojetersurCinéma

trier le résultat

AccèsséquentieldeCINEMA : projeterla tableCINEMA surCinéma

trier le résultat

Fusionner lesdeuxlistestriéesdecinémas

Il nerestequ’àévaluerceplan.


Lesétapesde traitement d’une requête

requete

Optimisation

Evaluation

Traductionalgebrique

Plan d’execution logique

Plan d’execution physique

Requete SQL


Evaluation de requêteefficace

Minimiser le temps

d’évaluation : tempspourtraiterentièrementla requête

de réponse: tempspourdonnerà l’utilisateur le 1ernuplet

ons’intéresseautempsd’évaluation.


Mesure du temps

Tempsd’évaluation= fonctiondu tempsd’évaluationdesopérationsélémentaires(tri,

fusion,accèsséquentiel,etc.)

Tempsd’évaluationd’uneopération=nombredepageslues/écrites;

onnecomptepasl’écrituredu résultatqui nedépendque

dela taille du résultat

maispasdel’algorithmechoisi


Evaluation du plan d’exécutionphysique(PEP)

Le résultatdel’optimisationestunPEPqu’il fautévaluer.

Onutilise le pipelinageentreopérationssuccessivesduplan: onn’attendpasqu’une

opérationsoit terminéeavantdecommencerla suivantesaufpourle tri. Intérêts:

Besoindemoinsdemémoire:pasbesoindestocker le résultatintermédiaire

tempsderéponsemeilleur

OnregardelesalgorithmespourimplanterchacunedesopérationsduPEP


Algorithmes pour lesprincipales opérationsphysiques

tri

sélection

projection(on fait l’impasseparmanquedetemps)

jointure


TRI

Le tri estnécessairepour

éliminerdesdoublons(projection)

fonctionsagrégat etorderby

algorithmedejointure


Les 2 phasesdu tri

Ona�

tamponsenmémoire,detaille unbloc. Soit _ le nombredeblocsdela relation.

tri: on lit la relation�

blocspar�

blocs.On trie lesMblocseton lesécrit sur

disque.Onobtient `-_ a � bpartitionstriées.

on fusionnelespartitionsjusqu‘acequ’onobtienneuneseulepartitiontriée

C’estla 2ephasequi coûtecher: c dfe1g�h i!j�c


phasede fusion dansle tri

A chaqueétape

on fusionnelespartitions� k : par

� k :onsesertdu

�e tamponpourle résultat

onobtient� k : fois moinsdepartitionstriées

� k : fois plusgrandes.

on lit et onécrit _ blocs

Le nombred’étapesest + )'l3m n X _


phasede fusion

fan-in

...o

o

...

...

...


tri: un exemple

Vertigo

Annie Hall

Twin Peaks

Brazil

Psychose

Metropolis

Underground

Greystoke

Shining Annie Hall

Brazil

Easy rider

Greystoke

Jurassic Park

Manhattan

Metropolis

Psychose

Shining

Twin Peaks

Underground

Vertigo

Easy rider

Manhattan

Jurassic Park

VertigoAnnie Hall Brazil

Twin Peaks

Jurassic ParkUnderground

Annie Hall

Brazil

Twin Peaks

Vertigo

MetropolisPsychose

GreystokeShining

Greystoke

Metropolis

Psychose

Shining

Annie HallBrazilJurassic Park

Twin Peaks

VertigoUnderground

Easy RiderGreystokeManhattan

Metropolis

ShiningPsychose

Easy RiderManhattan


algorithmespour la sélection

Ons’intéresseauxalgorithmespourfairesimultanémentuneconjonctiondesélectionset

uneprojection.Il y adeuxalgorithmes:

parbalayageséquentiel

partraverséed’index


sélectionpar accèsséquentiel

un tampondelectureetun tampond’écriture,

onparcourttout le fichier: pourchaquebloc lu, on fait lessélections/projections,

quandle tampondesortieestplein on l’écrit surdisque.

Coût: _ lectures


R

tampon Entree E tampon Sortie Ssel/proj


Sélectionpar traverséed’index

Dansle casoù la table o estindexéesur�

qui apparaitdansundescritèresdesélection,� p � , 2 étapes:q o � r �6� �W� � traversel’index

�avecpourclé � etdonnelesadressesdenuplets

tellesque� p �

pourchaque� �� dansq o � r �6� �W� �

Accèspar adresseaunuplet

de o d’adresse� ��fairelesautressélectionset lesprojectionséventuelleset copierle nuplet

résultants’il y a lieu dansle tampondesortie


Traverséed’index


a

....R

p1 p2 p3

a

R b,a,a,a,a a,a,c,...

E S E S

autres

select.

proj

autres

select.

proj..

(i) (ii)


Estimationdu Coût:� s � t

,�

estle coûtdetraverséed’index�

estle nombredenuplets

dansle casd’un index dense,la sélectivité

d’un attribut�

= : avuwA�x � o � u


Algorithmes de jointur e

Lesalgorithmeslesplusutiliséssont:

Bouclesimbriquées

Tri-fusion (aucunedes2 tablesn’estindexéesurl’attribut dejointure)

Jointureparhachage

Onneregarderaquelesdeuxpremiers.

Certainsalgorithmesnemarchentquepouruneéquijointure


Jointur e par bouclesimbriquées

Le plussimplemaischer: _ y t _ z .

Utilisé quandl’une destablesestpetite.

2 tamponsenlecture,unq

enécriture.

S’adapteauxjointuresavecinégalité.

On lit touslesblocsd’unerelation o directrice. Pourchaquebloc lu, on lit tous lesblocs

del’autre relation

. Pourchaquecoupledeblocslus,on fait la jointuredesnupletsen

mémoire(procédureBIM).


Jointur e par bouclesimbriquées(suite)

Pour chaque p dans pages(R) {

lire p

Pour chaque q dans pages(S) {

lire q

BIM(p,q)}

}

Algorithme BIS: jointure par boucles imbriquées simples


Jointur e par bouclesimbriquées(fin)

Pour chaque r dans R {

Pour chaque s dans S {

si r et s sont joignables pour donner t, alors

si T est plein vider T sur disque,

sinon ajouter t à T

}

}

Procédure BIM (R,S): boucles imbriquées en mémoire


Bouclesimbriquées: un exemple


Spielberg Jurassic ParkHitchcock Psychose

Lang Metropolis Allen Manhattan

Hitchcock VertigoAllen Annie HallKubrik Shining

Spielberg Jurassic ParkHitchcock Psychose

Lang Metropolis Allen Manhattan

Hitchcock VertigoAllen Annie HallKubrik Shining

Comparaison

Association

Vertigo 1958

Annie Hall 1977

Brazil 1984 .......


Bouclesimbriquéeset traverséed’index

Si l’une desdeuxtables

estindexéesurl’attribut dejointure,onutiliseunevariante

efficacedecoût _ y t { � + )'l|� _ z �\� . Soit�

l’attribut d ejointurede o .

Pour chaque p dans pages(R) {

lire p dans tampon E

Pour chaque nuplet r dans p {

adresses = TRAV (I,r.A)

pour chaque a dans adresses {

acces par adresse au nuplet s

t= jointure de r et s

si tampon resultat T plein {

vider T}

ecrire t dans T}}}


Jointur e par tri-fusion

Soit l’expressionA y~} x P=� z3}��Y� � o ] xD�-� �(� ��.

Projeter o sur{� P � � C }

Trier o sur� C

Projeter

sur{ _ � � _�� }Trier

sur _ �

Fusionner lesdeuxlistestriées.

On lesparcourtenparallèleenjoignantlesn-upletsayantmêmevaleurpour� C et _ � .


Annie Hall 1977Brazil 1984Easy Rider 1969Greystoke 1984Jurassic Park 1992

Metropolis 1926Manhattan 1979

Psychose 1960....

Allen Annie HallSpielberg Jurassic Park

Allen ManhattanLang MetropolisHitchcock PsychoseKubrik ShiningHitchcock Vertigo

Fichiers Artistes

TRI

FUSION

TRI

Fichier films

....Spielberg Jurassic Park 1992

Allen Annie Hall 1977


Tri-fusion : performance

Le coûtestdominéparla phasedetri :{ � _ y t �T��9� _ y ��s _ z t ��4� _ z �Q� .Dansla secondephase,unsimpleparcoursenparallèlesuffit.

Cetalgorithmeestparticulièrementintéressantquandlesdonnéessontdéjàtriéesen

entrée.


Pourdesgrandesrelationset enl’absenced’index, la jointurepartri-fusionprésenteles

avantagessuivants:

Efficacité : bienmeilleurequelesbouclesimbriquées.

Manipulation dedonnéestriées: facilite l’élimination dedupliquésou l’affichage

ordonné.


Optimisation

Unerequêteestdécomposéeenblocs

unbloc auneseuleclauseselect-from-where,uneseuleclauseGroupBy, uneseule

clausehaving

Onseconcentresurl’optimisationd’un bloc

Touterequêteayantdesimbricationspeutêtredécomposéeenunecollectiondeblocs


Exemplederequêteimbriquée

Employes (nom, departement, salaire)

Departement(dept-id, directeur)

SELECT directeur

FROMDepartement,Employes

WHEREDepartement.dept-id = Employes.departement

AND salaire = (SELECT MAX (salaire)

FROMEmployes)

etsadécompositionenblocs

SELECT directeur

FROMDepartement,Employes

WHEREDepartement.dept-id = Employes.departement

AND salaire = référence au bloc imbriqué


RequêtesousFNC

TouterequêtepeutêtremisesousFormenormaleconjonctive (FNC)

Sratégiesd’optimisationpourunedisjonction� p � � _ p �

Si index à la fois sur�

etsur _ alorstraverserlesindex et fairel’union

sinonbalayageséquentieldela relation


Traduction enun PEL

La premièreétapedela compilationd’unerequêtecomportedeuxphases

Analysesyntaxique

Générationd’un pland’exécutionlogique(PEL)


Analysesyntaxique

Vérificationdel’existencedesrelationsetdesattributs

Détectiondecontradictions,comme� �?� � �Mp :��^<�<�<��^<�<�< � � �?� � �Mp ;=<�<��^<�<�<

Simplificationstellesque:�� )/� _ � � _ �

estremplacépar� � _ .


Traduction enun PEL

Soit la requête

Select Dept.nom

From Departements, Communes

Where Nbhab > 1,000,000

And Communes.numdept=Dep arte ments. num

L’arbresuivantreprésentantuneexpressionalgébriqueestunPland’exécutionéquivalent


Communes Departements

.Departements.nom

#dept=num

Nbhab>1,000,000


Règlesde réécritur e

Lespropriétésdel’algèbrepermettentdetransformerunPEL enun PELéquivalent:

1. Commutativité desjointur es:o ] � ] o

2. Associativité desjointur es:� o ] ��$] q � o ] �� ] q �3. Regroupementdessélections:N xD�� L �� o �� N xD�� L � ��N � �� o �Q�4. Commutativité de la sélectionet de la projection

A�xY� � x�� } } } x�¡ �ON xD�-� � L � o �Q�5� N xD�6� � L � A�xY� � x�� } } } x�¡ � o �Q� � 5¢ £ :��¤T¤¥¤T��.|¦5. Commutativité de la sélectionet de la jointur e.N xD� � L � � o � ¤¥¤¥¤ � ¤¥¤T¤ �$] ��5� N xD� � L � � o �#] 6. Distrib utivité de la sélectionsur l’union .


N xD� � L � � o � �� N xD� � L � � o � � N x�� L � �� NB : valableaussipourla différence.

7. Commutativité de la projection et de la jointur eA�x � } }§} x ¡ � � } }§}��|¨ � o ] x � � � � ��A�x � } }§} x ¡ � o �#] x � � � �>A � � } } }��|¨ �� \�©$¢ £ :��ª¤¥¤T��.|¦3�¬« ¢ £ :��ª¤¥¤¥¤T�^�¦

8. Distrib utivité de la projection sur l’unionA x � x � }§} } x ¡ � o � ��5� A x � x � } }§} x ¡ � o � � A x � x � } } } x ¡ �& ��


Amélioration d’un PEL

Ontransformegrâceauxrèglesci-dessusun PELenun PEL“meilleur”

fairelessélections,projectionsle plustôt possiblepermetderéduirelestaillesde

relationsetdoncderéduirela complexité desopérationssuivantes(règles4,5et7),

décomposerunesélectionenunecompositiondesélectionspermetdetirer avantage

del’existenced’un index (règle3),

etc.


Le PELci-dessousestmeilleur:

Nbhab>1,000,000

#dept=num


Departements.nom


Un autreexemple:

Cinéma (nom-cinéma, adresse)

Salle (salle,nom-cinéma)

Séance (salle, film, heure-début)

Soit la requête“Quelsfilms passentauREX a20heures?” expriméeparl’ordre SQL

suivant.

SELECT film

FROM Cinéma, Salle, Séance

WHERE Cinéma.nom-cinéma = ’Le Rex’

AND Séance.heure-début = 20

AND Cinéma.nom-cinéma = Salle.nom-cinéma

AND Salle.salle = Séance.salle


Un PEL équivalentà la requête: πfilm

σheure-début=20 nom=’Le REX’

CINEMA SALLE SEANCE


Un PEL meilleur

σ (CINEMA)nom=’Le REX’

πnom

πfilm

σheure-début=20

(SEANCE)SALLE

π

π

πnom,salle

salle

salle,film


Remarques:

Sélectionavantjointureestunebonnerèglemaisil peuty avoir desexceptions.

CeréarrangementdesPELestnécessairemaispassuffisant: il fautensuitechoisir le

meilleuralgorithmepourchaqueopérationduPEL: le PEL esttransforméenPlan

d’exécutionphysique(PEP).


Estimation descoûtset choix d’un PEP

Un PEPcontient

lesopérateursphysiqueschoisispourévaluerla requête

ainsiquel’ordre danslequelexécuterla requête

Un PEPestreprésentésousformed’arbre:

lesfeuillessontlesfichiersoùsontstockéslestableset index

lesnoeudsinternessontlesopérationsphysiques,

lesarcsreprésententlesflots dedonnéesproduitsparuneopérationet consommés

parl’opérationsuivantedansl’arbre


liste d’opérations physiques

Selection

Tri

Filtre

Jointure

ProjectionFusion

Filtre d’un ensemble en fonction d’un autre

Selection selon un critere

Tri sur un attribut Jointure selon un critere

Fusion de deux ensembles tries Projection sur des attributs

Attribut(s)

Critere Critere

Critere

Critere Attribut(s)

OPERATIONS PHYSIQUES

Parcours sequentiel

Acces par adresse

Parcours d’index

CHEMINS D’ACCES

TABLE

INDEX

Attribut(s)

Sequentiel

TABLE

Adresse


Un PEP

Pasd’index surlesattributsdejointure


Projection

Fusion

Tri

Cinema

Sequentiel

Salle

Sequentiel Sequentiel

Seance

Selection

Horaire=20

Selection

’Le Rex’

Tri Tri Tri

ID_cinema ID_cinema ID_seance

Fusion

ID_seance


Un autre PEP

tableindexéepourchaquejointure


Projection

Fusion

Tri

Cinema

Sequentiel

Salle

Sequentiel Sequentiel

Seance

Selection

Horaire=20

Selection

’Le Rex’

Tri Tri Tri

ID_cinema ID_cinema ID_seance

Fusion

ID_seance


Un autreexemple


Nbhab>1,000,000

#dept=num


Tri adresses

Departements.nom

Jointure.

Selection

Nbhab>1,000,000

sequentiel

Communes

Departements

adresse

I_dept

#dept


Choix d’un PEP

Le choixd’un PEPconsisteà

choisirpourchaquenoeudduPELun ouuneséquenced’opérateursphysiques

répartirla mémoiredisponibleentrelesdifférentsopérateursphysiquesetpipeliner

lesopérations

Il dépend:

descheminsd’accèsdisponibles(index)

desstatistiquesrassembléessurlestables

dela placedisponibleenmémoire


Estimation descoûts

Pourchoisirentredeuxopérateursphysiques,le systèmeutiliseuneestimationsimple

descoûtsqui s’appuiesur

lesstatistiquesdisponibles

l’estimationducoûtdechaquechemind’accès


Estimation du coût de la sélection

Prenonscommeexemplel’estimationducoûtdeplusieursstratégiesdesélectionsur�

dela relation o , dansle casoù

le critèredesélectionutilise l’égalité

il y aun index denseetnonuniquesur�

Le systèmeconnait:�

le nombredenupletsde o_ , le nombredeblocsde ola sélectivité

de

�


Balayageséquentiel,estimationducoût: _Stratégieavecindex, estimationducoût:

� s ® s ¯où

�estle coûtdetraversée

d’index,® p �O� t �� a �� a*_ ��p _ t

estle nombredefeuillesenséquenceà

lire, et¯ p � t

estle nombredenupletsqui satisfontle critère.En résuméle

coûtest� s � _ s � �°t

.

vertigo/cnam, paris 1

Documents