Ottimizzazione query MySql
webmaster: 27/01/09 @ 15:41Ho imparato un paio di trucchi davvero interssanti per tenere le query sott'occhio e vorrei condividerli con voi.
Il primo e più importante strumento a nostra disposizione è sicuramente il MySql Slow Query Log, configurazione di MySql che permette di loggare automaticamente tutte le query che richiedono più di long_query_time per essere eseguite e che hanno esaminato almeno min_examined_row_limit di record (entrambe valori configurabili da voi).
Una semplice occhiata a questo log vi permetterà di individuare immediatamente le query scritte male e di ottimizzarle. Io ho cominciato impostando il limite a 15 secondi. Ho sistemato le query che venivano così loggate e poi ho impostato a 10 secondi. Ho ripetuto l'operazione e sono passato ai 5 secondi. Ora sono stabilmente a 3 secondi. Ogni paio d giorni controllo il log e vedo se mi è sfuggita qualche query o se qualche nuova query che non avevo testato bene crea problemi.
Il secondo strumento è qualcosa che sto sviluppando io. Con questo non voglio dire di esserne l'inventore, sicuramente qualcuno l'avrà già utilizzato prima di me, ma io lo sto sviluppando indipendentemente.
Sicuramente conoscete il seguente codice per misurare il tempo impiegato per generare una pagina PHP:
$page_load_time_start = $time[1] + $time[0];
// codice PHP o query MySql
$time = explode(' ', microtime());
$page_load_time_finish = $time[1] + $time[0];
$total_time = round(($page_load_time_finish - $page_load_time_start), 4);
Solitamente le prime due righe vanno in cima alla pagina mentre le ultime tre vanno in fondo. Semplicmente stampando a video $total_time si ha quindi il tempo impiegato per generare una pagina.
Ho adattato questo script per misurare il tempo di esecuzione di una query. In pratica, al posto di utilizzare il comando PHP mysql_query () ho creato la funzione sql_query (), che non contiene altro che il codice per misurare il tempo di esecuzione e il comando mysql_query (). La query e il suo tempo d'esecuzione vengono poi stampati a video.
Bene, abbiamo visto come raccogliere le informazioni necessarie: ora vediamo come si possono migliorare le query.
La prima cosa da fare è creare degli indici sulle tabelle. Un indice è una mappa che i DB usano per evitare di guardarsi tutta la tabella riga per riga: a seconda di come è disegnata una tabella, un indice può migliorare la velocità di esecuzione di una query di mille o di un milione di volte. Inutile dire che è importantissimo. Scegliere un indice (o degli indici) non è difficile. Come regola possiamo dire che qualunque campo si trovi dopo la condizione WHERE va indicizzato. Se usate phpMyAdmin l'operazione non è altro che un semplice click per ogni indice che volete creare.
Ovviamente gli indici hanno un prezzo, ovvero rallentano di qualche frazione di secondo l'inserimento o l'aggiornamento dei dati (operazioni di INSERT, DELETE e UPDATE), ma il loro vantaggio è innegabile. Nel dubbio create sempre l'indice.
La seconda cosa che ho fatto è stato dividere le query in più sotto-query. Mentre si può essere tentati di fare un uso smoderato dei LEFT JOIN per dover gestire meno query, quando le tabelle cominciano a diventare troppo grandi vale la pena di sacrificare l'eleganza del vostro codice per migliorare le prestazioni.
Infine, ho cercato di limitare l'uso di SELECT DISTINCT e GROUP BY e di far fare queste cose al PHP. Per qualche motivo il PHP è molto più efficiente di MySql in queste operazioni.
Commenti: 3
- [04/07/10] Bug jQuery Cleartype su Internet Explorer
- [20/02/09] Migliorare la funzione "Cerca"
- [13/02/09] MySql query benchmark
- [03/04/09] Programmare in jQuery
I commenti dei lettori:
> Come regola possiamo dire che qualunque campo si trovi dopo la condizione
WHERE va indicizzato.
Esistono comandi per un analisi seria del problema, come ad esempio explain
http://dev.mysql.com/doc/ ...
efman/5.1/en/explain.html
Mostrando come il motore mysql analizza la query, e' possibile indicizzare SOLO
le colonne che servono per poter migliorare (e di molto) il tempo di
esecuzione.
C'era un articolo, a mio parere molto valido, su linux&C di qualche tempo
fa.
Ciao
il comando explain è interessante, ma
non da moltissime informazioni. importante è la lista degli indici non
utilizzati e il numero di row che la query deve guardarsi per trovare le
informazioni corrette, ma non dice ad esempio il tempo di esecuzione medio di
una query. lo script che ho indicato qui sopra fa invece solo quello (sto
sviluppando uno script che facilita il test di varie query e il confrontotra i
tempi di esecuzione).
a proposito di explain: qualcuno sa se esiste un comando che indichi il numero
di row che una query deve guardare per trovare le informazioni corrette e il
modo di esportarlo in php?
secondo me è anche molto importante dare le modalità giuste ai campi:
tinyint anziche int dove possibile, unsigned se i numeri non possono
essere negativi, impostare not null i campi che non saranno mai vuoti.
queste modalità alleggeriscono di molto i tempi di reazione delle
tabelle
www.moscabianca.biz