Blog

Ottimizzazione query MySql

webmaster: 27/01/09 @ 15:41
Ho passato il weekend a ottimizzare query. Operazione poco divertente ma molto, molto utile. In quest'ultima settimana avrete forse notato un rallentamento del sito: ebbene questo era dovuto prevalentemente da query poco ottimizzate insieme ad un aumento delle visite inaspettato. Uno dei problemi è che alcune query esaminavano oltre mezzo milione di record. E non si trattava di mancanza di indicizzazione delle tabelle, ma semplicemente di query troppo grandi e troppo complesse.

Ho 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:
$time = explode (' ', microtime());
$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


I commenti dei lettori:

PaoloG PaoloG il 03 Febbraio 09 @ 09:50 am

> 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

webmaster webmaster il 03 Febbraio 09 @ 14:05 pm

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?

mosca_san mosca_san il 27 Settembre 11 @ 09:06 am

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

Lascia un commento

Insulti, volgarità e commenti ritenuti privi di valore verranno modificati e/o cancellati.
Nome:

Commento:
Conferma visiva: (ricarica)

Inserisci la targa della città indicata nell'immagine.

Login | Iscriviti

Username:

Password: