Progetto e Sviluppo di un'Applicazione Web per il Calcolo e la Visualizzazione di Piani di
Accesso a Basi di Dati
Daniel Stoilov
Tesi di Laurea
U
NIVERSITÀ DEGLI STUDI DIM
ODENA ER
EGGIOE
MILIAFacoltà di Scienze Matematiche, Fisiche e Naturali Corso di Laurea in Scienze dell’Informazione
Relatore:
Prof. Riccardo Martoglia
Anno Accademico 2007/2008
Query Optimization
• Scopo di un DB Optimizer è di determinare mediante stime la migliore strategia di accesso per l’esecuzione di un’interrogazione SQL
• Tali moduli effettuano delle stime sulla base degli indici disponibili utili alla soluzioni dell’interrogazione
• La creazione di tutti e soli gli indici utili all’esecuzione delle interrogazioni spetta al progettista della base di
dati, che ha anche la necessità di documentare le proprie
scelte
Obiettivo della Tesi
• Necessità di avere uno strumento didattico in grado di facilitare il compito del progettista e di chi affronta lo studio di questi argomenti
• Scopo di questa tesi è la progettazione e realizzazione di uno strumento in grado di
– operare in un ambiente web dinamico
– eseguire automaticamente il parsing della query
– valutare i piani di accesso ottimali mediante un opportuno modello di costo
– suggerire i migliori indici e piani di accesso
– produrre un resoconto passo-passo, chiaro e dettagliato, dei calcoli effettuati
Progetto e Sviluppo di un Query Optimizer
• Analisi del Problema
• Progetto
• Implementazione
Analisi del problema
Lo scopo del processo di ottimizzazione della query è produrre un "piano di esecuzione" il più efficiente possibile.
Un "ottimizzatore” può produrre un piano di esecuzione
"ottimale" per la maggioranza delle query.
Il query optimizer realizzato dovrà essere in grado di
• effettuare i calcoli per i casi di interrogazioni più comuni
• essere di immediato e facile utilizzo, anche e soprattutto in un’ottica didattica
Ottimizzazione di query
Un indice è utile per una query solo se il costo di accesso con l’indice è minore del costo dell’accesso sequenziale cioè
minore del numero di pagine per file
L'efficienza di una applicazione dipende dall'efficienza del sottostante database. L'efficenza del database dipende
dall'efficenza delle query.
Come ottimizzare una query?
- verificare gli indici utili per migliorare l'efficienza di accesso ai dati
Ottimizzazione di query
Verranno considerati e gestiti indici B+tree di due tipi:
• Indice clustered
- comporta il riordinamento dell'intera tabella in base al campo presente nell'indice. L'indice è più performante dal punto di vista delle richieste ma è il più pesante durante l'aggiornamento e
l'inserimento
• Indice non-clustered
- contengono i riferimenti alle righe della tabella
valorizzata con una determinata n-pla di valori in
corrispondenza dei campi indicizzati
Problemi da risolvere
•
Analizzare le query SQL
• Valutare tutti i piani di accesso più opportuni secondo un modello di costo
• Creare un'interfacca grafica per aiutare i progettisti e chi intende studiare questi argomenti
• Produrre una spiegazione dettagliata passo passo in
output, analoga a quella che si trova nelle soluzioni
dei relativi esercizi sui libri di testo
Scelte tecnologiche
Applicazione dinamica Web-based
•
Web server: Apache
•
Tecnologie e strumenti:
- HTML
- CSS
- PHP
Progetto e Sviluppo di un Query Optimizer
• Analisi del Problema
• Progetto
• Implementazione
Progetto
Fasi della progettazione:
• Raccolta dei requisiti funzionali
• Analisi dello scenario: ottimizzatore di query
• Individuazione dei casi d'uso
• Realizzazione degli activity diagram
Scenario ottimizzatore
di query
Visione globale del ottimizzatore
di query
L’utente specifica le dimensioni del
form, aggiungendo
delle righe Inserimento di
una query nel form appena
creato
In caso di inserimento sbagliato, l’utente ha
la possibilità di resettare il modulo Dopo aver confermato
l'inserimento sarà possibile specificare i dati necessari
per il calcolo del costo Annulla dati
inseriti
Activity Diagram:
Inserimento Querry
Diagramma delle attività per la funzione di inserimento di
una query analizzata in
dettaglio
Activity Diagram:
Inserimento Query
Diagramma delle attività per la funzione di calcolo del costo di accesso analizzata in
dettaglio
Progetto e Sviluppo di un Query Optimizer
• Analisi del Problema
• Progetto
• Implementazione
Studio degli script
•
AggiungiRiga.php: richiede la specifica delle righe del form. InserimentoQuery.php: richiede l'inserimento di una query dall'utente. Controlla il corretto inserimento e in caso di
sintassi sbagliata redireziona l’utente alla pagina AggiungiRiga.php.
InserimentoFrom.php: righiede la specifica dei campi NT (numero di tuple del file) e NB (numero di pagine del file) per ogni tabella. Controlla se i dati richiesti sono
specificati e in caso contrario da messaggio di errore.
Studio degli script
InserimentoCalcolo.php: permette di selezionare per ogni attributo il valore di NK (numero di valori distinti della chiava), NF (numero di foglie dell'indice) ed il tipo di indice utilizzato: clustered, unclustered ordinato o unclustered disordinato. Controlla se i dati sono inseriti, controlla se l'indice clustered è utilizzato al più una volta per relazione.
StampaRisultati.php: visualizza i risultati del calcolo.
Query optimizer: Aggiungi riga
• Query optimizer: specifica del numero di righe
L'utente può richiedere facilmente
ulteriori righe.
Query optimizer: Aggiungi riga
• Query optimizer: specifica del numero di righe
Controllo sul campo
obbligatorio:
Aggiungi riga
Query optimizer: inserimento query
Inserimento di una query Controlli sui
campi
obbligatori:
Select, From, Where
Menu a tendina filtro
AND, OR,
BETWEEN
Query inserita correttamente
Query optimizer: inserimento From
Inserimento valori di NT e NB per ogni tabella
Visualizzazione della query inserita
Inserimento dati indici
Specifica di NK e NF
Specifica del tipo Possibilità di
annullare
il form
Query optimizer: Stampa risultati
Questo script effettua i calcoli e visualizza i risultatiViene mostrata la sequenza di accesso
Visualizzazione del costo di accesso
con indice clustered Visualizzazione del costo della
scansione sequenziale
Con l’accesso a IMP si ottengono Einp.nome=50 tuple che soddisfano imp.nome=“Rossi” quindi per 50 volte si accede a DIP per il predicato imp.qual=dip.qual
Visualizzazione del costo di Join per la Sequenza IMP=>DIP Visualizzazione del costo di Join per la
Sequenza DIP => IMP
La sequenza più conveniente è DIP => IMP
Obiettivi raggiunti
creare un’applicazione web in grado di eseguire dei calcoli per il costo di accesso a interrogazioni
sfruttare un modello di costo per prendere decisioni sull’ordinamento delle relazioni e quali indici costruire
fornire uno strumento didattico in grado di aiutare chi intende studiare o capire meglio questi argomenti, grazie alla semplice interfaccia grafica e alla spiegazione dettagliata passo passo fornita in output, analoga a quella che si trova nelle soluzioni dei relativi esercizi sui libri di testo
Sviluppi futuri
creazione di script PHP per estendere i calcoli di join a più di tre tabelle
specifica in dettaglio dei valori degli attributi nei casi di operatori di minore o maggiore
estensione degli script al fine di ottimizzare anche operazioni di modifica