Ottimizzazione query MySql

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:

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.