Progetto e Sviluppo di un'Applicazione Web per il Calcolo e la Visualizzazione di Piani di

Accesso a Basi di Dati

Daniel StoilovTesi di Laurea

UNIVERSITÀ DEGLI STUDIDI MODENA E REGGIO EMILIA

Facoltà di Scienze Matematiche, Fisiche e NaturaliCorso 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 unQuery 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 unQuery 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 globaledel 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 costoAnnulla 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 unQuery 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ò richiederefacilmenteulteriori righe.

Query optimizer: Aggiungi riga

• Query optimizer: specifica del numero di righe

Controllo sul campoobbligatorio: Aggiungi riga

Query optimizer: inserimento query

Inserimento di una queryControlli sui campi obbligatori:Select, From,Where

Menu a tendinafiltroAND,OR,BETWEEN

Query inserita correttamente

Query optimizer: inserimento From

Inserimento valori di NT e NB per ogni tabella

Visualizzazione della query inserita

Inserimentodati indici

Specificadi NK e NF

Specifica del tipo

Possibilità diannullareil form

Query optimizer: Stampa risultatiQuesto script effettuai calcoli e visualizza i risultati

Viene mostrata la sequenza di accesso

Visualizzazione delcosto di accessocon indice clustered

Visualizzazione delcosto della scansionesequenziale

Con l’accesso a IMP si ottengono Einp.nome=50tuple che soddisfano imp.nome=“Rossi” quindiper 50 volte si accedea DIP per il predicatoimp.qual=dip.qual

Visualizzazione delcosto di Join per la Sequenza IMP=>DIP

Visualizzazione delcosto 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