Tutorial su Mysql->Ottimizzazione delle interrogazioni

Ottimizzazione delle interrogazioni

Un programma lento può dipendere da diversi fattori, dall'hardware del server, dalla tipo di connessione al server, dalla configurazione del gestore di database, dal volume dei dati o anche dalla complessità delle interrogazioni, e per ultimo dalla struttura del database. Quest'ultimo fattore, è poco conosciuto dai principianti, ed quindi quello che verrà discusso tra poco. Consideriamo le tabelle viste in "Sub-query" e aggiungiamo le tabelle relative agli ordini dei fornitori.

Tabella ordine_cliente_dettaglio id id_ordine rigo id_articolo qta_ordinata qta_evasa prezzo 1 1 1 1 10 0 5 2 1 2 2 5 0 7 3 1 3 4 5 0 8 4 1 4 3 5 0 4 5 2 1 5 10 0 8 6 2 2 7 10 0 5 7 2 3 6 10 0 5

Tabella ordine_cliente id data_ordine id_fornitore nr_ordine 1 14/01/2008 1 1 2 14/01/2008 2 2

Consideriamo sempre l'esempio del semplice programma aventi i seguenti moduli: gestione magazzino, ordini dei clienti e ordini dei fornitori.
Ora effettuiamo ad esempio un'analisi sulle prestazioni nella schermata di visualizzazione degli ordini. Questa schermata viene utilizzata dall'utente per due obbiettivi:

• Visualizzare gli ordini dei clienti con totale importo ordinato e da evadere, nell'arco di un mese;
• Cercare un ordine di un cliente per visionarlo, modificarlo o evaderlo;

La ricerca di un ordine avviene entrando nella schermata della visualizzazione degli ordini, quindi consideriamo questa operazione simile alla visualizzazione, e di conseguenza vanno considerate come unica operazione. Consideriamo inoltre l'esistenza di una paginazione della visualizzazione dei dati pari a 50 elementi, e che ogni ordine ha in media due articoli.
Ciò che si vuole determinare, è se conviene avere due campi ridondanti nella tabella ordini_clienti, rispettivamente totale_importo e totale_da_evadere.
Vediamo l'anali delle operazioni.

Operazioni Operazione Frequenza Inserimento rigo ordine 350 all'anno Visualizzazioni Ordini 1350 all'anno Evasione di un ordine 200 all'anno

A questo punto possiamo calcolare gli accessi nelle tabelle in base alle due ipotesi: la prima con i campi totale_importo e totale_da_evadere nella tabella ordini_clienti, e la seconda senza questi campi.
Nell'operazione di inserimento di un rigo d'ordine abbiamo ovviamente una operazione in scrittura sulla tabella ordine_cliente_dettaglio. Se dobbiamo agiornare anche i due campi della tabella ordini_clienti (totale_importo e totale_da_evadere), occorre ricalcolare i totali, quindi eseguire due operazioni di lettura a ordine_cliente_dettaglio (in media ogni ordine ha due articoli) e una operazione di scrittura a ordine_cliente.

In una operazione di visualizzazzione ci sono 50 accessi alla tabella ordine_cliente e alla tabella cliente, per via della paginazione. In caso non si dispone dei campi con i totali, occorre eseguire 100 accessi alla tabella ordine_cliente_dettaglio (se ogni ordine ha due articoli, il totale è 100 articoli).

In una operazione di evasione di un articolo di un ordine, abbiamo ovviamente una operazione in scrittura sulla tabella ordine_cliente_dettaglio. Se dobbiamo agiornare anche i due campi della tabella ordini_clienti (totale_importo e totale_da_evadere), occorre ricalcolare i totali, quindi eseguire due operazioni di lettura a ordine_cliente_dettaglio (in media ogni ordine ha due articoli) e una operazione di scrittura a ordine_cliente.

Riepilogo accessi in caso di inserimento dei campi totale_importo e totale_da_evadere nella tabella ordini.

Inserimento Tabella Accesso Tipo ordine_cliente_dettaglio 1 S ordine_cliente_dettaglio 2 L ordine_cliente 1 S

Visualizzazione Tabella Accesso Tipo ordine_cliente 50 L cliente 50 L

Evasione Tabella Accesso Tipo ordine_cliente_dettaglio 1 S ordine_cliente_dettaglio 2 L ordine_cliente 1 S articolo 1 S

Riepilogo accessi senza questi campi.

Inserimento Tabella Accesso Tipo ordine_cliente_dettaglio 1 S

Visualizzazione Tabella Accesso Tipo ordine_cliente 50 L cliente 50 L ordine_cliente_dettaglio 100 L

Evasione Tabella Accesso Tipo ordine_cliente_dettaglio 1 S articolo 1 S

Se consideriamo che un accesso in scrittura, ha un costo doppio di un accesso in lettura, possiamo calcolare il costo per anno delle due ipotesi.
Costi in caso di inserimento dei campi totale_importo e totale_da_evadere nella tabella ordini.

Operazione		Tabella		Accesso		Tipo		Frequenza		Costo
Inserimento rigo ordine		ordine_cliente_dettaglio		1		S		350		700
		ordine_cliente_dettaglio		2		L				700
		ordine_cliente		1		S				700
Visualizzazione ordini		cliente		50		L		350		17500
		ordine_cliente		50		L				17500
Evasione rigo ordine		ordine_cliente_dettaglio		1		S		350		700
		ordine_cliente_dettaglio		2		L				700
		ordine_cliente		1		S				700
		articolo		1		S				700
Totale										39900

Costi in caso di non inserimento di questi campi.

Operazione		Tabella		Accesso		Tipo		Frequenza		Costo
Inserimento rigo ordine		ordine_cliente_dettaglio		1		S		350		700
Visualizzazione ordini		cliente		50		L		350		17500
		ordine_cliente		50		L				17500
		ordine_cliente_dettaglio		100		L				35000
Evasione rigo ordine		ordine_cliente_dettaglio		1		S		350		700
		articolo		1		S				700
Totale										72100

Dai risultati si evince che nella visualizzazione degli ordini, il costo del calcolo a run-time del totale degli importi è molto più alto del costo del mantenimento dei due campi da aggiungere alla tabella.
Questo risultato dimostra l'importanza della struttura del database nell'implementazione del programma, e che soprattutto esse non deve essere vista solamente come un modo per archiviare i dati ma tutt'altro deve essere elaborata e concepita per far funzionare meglio il programma.

Riepilogo

• Introduzione
• Differenze tra chiavi e indici
• Operatore JOIN
• Sub-query
• Transazione protette
• Ottimizzazione delle interrogazioni
• View, Function e Stored procedure