Come velocizzare di 300 volte le tue query MySQL

Print Friendly, PDF & Email

Avere un database lento può significare nella vita reale avere software altrettanto lento, tempi di attesa elevati e relativi tempi morti che possono impattare sia sull’umore delle vostre giornate che diventano frustranti a causa di un software non reattivo, e una esperienza utente sicuramente inadeguata.

Sul business reale di casi noti pensiamo ad un call center in cui con 500 dipendenti al giorno su un turno di 8 ore recuperiamo 2 minuti di attesa per ogni turno di ogni operatore, una buona ottimizzazione del database ci permetterà di abbattere i tempi morti eccessivi e di recuperare 1000 minuti uomo al giorno, che ne sono 5000 a settimana, 20000 al mese e ben 240 mila all’anno ovvero ben 4000 ore di attesa complessive vuote, per un risparmio di costi e aumento di produttività di circa 80 mila euro l’anno al costo medio di 20 euro l’ora.

Questo è solo un esempio ed un tutorial spicciolo che vuol aprire gli occhi su come ottimizzare le query MySQL prendendo ad esempio il DBMS MySQL, ma la teoria qui riportata è alla base di tutti i DBMS moderni come PostgreSQL, Oracle, SQL Server della Microsoft ed essenzialmente tutti i DBMS che fanno utilizzo di sintassi SQL standard ANSI.

Prima di poter ottimizzare le query lente, è necessario trovarle.

MySQL ha un registro delle query lente (o slow query) incorporato. Per usarlo, apri il file my.cnf e imposta la variabile slow_query_log su “On”. Imposta long_query_time sul numero di secondi che una query dovrebbe impiegare per essere considerata lenta, ad esempio 0.2. Impostare slow_query_log_file sul percorso in cui si desidera salvare il file. Quindi esegui il tuo codice e qualsiasi query superiore alla soglia specificata verrà aggiunta a quel file.

Una volta che sai quali sono le query lente e problematiche, puoi iniziare a esplorare cosa li rallenta. Uno strumento offerto da MySQL è la parola chiave EXPLAIN . Funziona con le istruzioni SELECT , DELETE , INSERT , REPLACE e UPDATE . Semplicemente si utilizza per analizzare e spiegare la query in questo modo:

 
EXPLAIN SELECT picture.id, picture.title
FROM picture
LEFT JOIN album ON picture.album_id = album.id
WHERE album.user_id = 1;

Il risultato che otterrai è una spiegazione di come si accede ai dati. Viene visualizzata una riga per ogni tabella coinvolta nella query:

Le parti importanti su cui prestare molta attenzione sono il nome della tabella, la chiave utilizzata e il numero di righe scansionate durante l’esecuzione della query.

Praticamente esegue la scansione di 2.000.000 di immagini, quindi, per ciascuna immagine, esegue la scansione di 20.000 album. Ciò significa che in realtà esegue la scansione di 40 miliardi di righe per la tabella dell’album. Tuttavia, è possibile rendere questo processo molto più efficiente.

Utilizzare gli Indici.

È possibile aumentare in modo significativo le prestazioni utilizzando gli indici. Pensa ai dati come a nomi in una rubrica. Puoi sfogliare tutte le pagine oppure puoi trascinare la scheda della lettera destra per individuare rapidamente il nome che ti serve.

Utilizzare gli indici per evitare passaggi non necessari attraverso le tabelle. Ad esempio, puoi aggiungere un indice su picture.album_id in questo modo:

 
ALTER TABLE picture ADD INDEX(album_id);

Ora se si esegue la query, il processo non comporta più la scansione dell’intero elenco di immagini. Per prima cosa, tutti gli album vengono scansionati per trovare quelli che appartengono all’utente. Successivamente, le immagini vengono rapidamente individuate utilizzando la colonna index_id indicizzata . Questo riduce il numero di righe scansionate a 200.000. La query è anche circa 317 volte più veloce dell’originale.

Puoi assicurarti che entrambe le tabelle utilizzino una chiave aggiungendo il seguente indice:

ALTER TABLE album ADD INDEX(user_id);

Questa volta, la tabella dell’album non viene digitalizzata nella sua interezza, ma gli album giusti vengono rapidamente individuati utilizzando la chiave user_id . Quando questi 100 album vengono scansionati, le immagini associate vengono individuate usando la chiave album_id . Ogni tabella utilizza una chiave per prestazioni ottimali, rendendo la query 380 volte più veloce dell’originale.

Ciò non significa che è necessario aggiungere indici ovunque poiché ogni indice rende più lenta la scrittura nel database. Guadagni in lettura ma poi  si perde sulle scritture nel database. Quindi aggiungi solo indici che aumentino effettivamente le prestazioni di lettura.

Utilizzare EXPLAIN per confermare e rimuovere qualsiasi indice che non viene utilizzato nelle query.

Se anche tu hai un database aziendale o un applicativo che risulta lento e non ti soddisfa, contattaci pure per valutare i margini di miglioramento del tuo database e usufruire del servizio di Ottimizzazione MySQL al fine di avere un database rapido e performante.

17279

Vuoi ricevere i migliori consigli ?

Ogni settimana nuovi consigli e novità !