Ottimizzare MySQL e velocizzare le query SQL

MySQL resta oggi uno degli RDBMS (Relational database management system) più utilizzati a livello mondiale. Anche i principali fornitori di servizi cloud ne offrono il supporto diretto (si pensi, ad esempio, a Microsoft Azure) e database MySQL sono utilizzabili con la stragrande maggioranza dei CMS disponibili in Rete.

Ottimizzare MySQL e velocizzare le query SQL, però, non è un’operazione banale, almeno se non si posseggono gli strumenti giusti per comprendere ciò che non funziona al meglio.

Se il database MySQL è lento, le query SQL si accodano l’una dopo l’altra e i tempi di risposta si allungano enormemente, c’è di sicuro un problema di ottimizzazione del database.

Uno dei problemi di fondo, che ancor’oggi vengono sottovalutati, è lo scorretto o, addirittura, il mancato utilizzo degli indici sui database MySQL.
Quando le query SQL vengono eseguite lentamente in MySQL e la CPU comincia a “urlare pietà”, spesso si guarda a quella che – all’apparenza – pare la soluzione più semplice: effettuare un upgrade hardware. CPU particolarmente sollecitata? Soluzione? Passaggio a un server più potente, sia esso cloud oppure di tipo tradizionale. Con un conseguente aumento delle spese da affrontare.

In realtà questo approccio è intrinsecamente sbagliato.

Per risolvere le problematiche di performance con MySQL e ottimizzare database e query, di solito basta concentrarsi sull’attenta costruzione e sull’utilizzo ragionato degli indici. Lavorare sugli indici generalmente consente di trasformare query lente in interrogazioni velocissime, dalle performance fenomenali.

Ottimizzare database MySQL e velocizzare le query: un esempio concreto

Si supponga, come “esempio estremo”, di avere la seguente tabella MySQL:

CREATE TABLE dipendenti ( matricola char(10) NOT NULL, nome varchar(40), cognome varchar(40), indirizzo text, telefono varchar(25), salario int(11), salario_straordinario int(10) NOT NULL );

Per estrarre dal database il dato relativo al salario del dipendente contraddistinto dall’identificativo 693369, si utilizzerà la query che segue:

SELECT salario FROM dipendenti WHERE matricola = '693369';

MySQL, però, di base non dispone di alcun “appoggio” per trovare rapidamente l’informazione cercata: dovrà quindi esaminare il contenuto dell’intera tabella (potenzialmente migliaia di record) per restituire il dato sul salario.

Utilizzando un indice, MySQL potrà viceversa trovare le informazioni richieste molto più rapidamente.
Il “segreto” consiste nel creare l’indice sul campo o sui campi per i quali si attiva la clausola WHERE di SQL.

Nell’esempio, se si crea un indice per il campo matricola, i record cercati saranno trovati da MySQL molto velocemente.

Gli indici, in MySQL, funzionano in maniera molto simile agli indici di un qualunque libro. Si immagini un testo tecnico sprovvisto di indice: per trovare il capitolo Ottimizzare MySQL bisognerà necessariamente sfogliare tutte le pagine; con un indice, invece, ci si potrà recare rapidamente alla pagina giusta.

Un aiutante spesso troppo dimenticato: EXPLAIN

Passano gli anni ma lo statement EXPLAIN è spesso, a torto, dimenticato.

Si tratta infatti della prima ancora di salvezza quando si deve necessariamente ottimizzare le query SQL e velocizzare il database.
Utilizzando EXPLAIN prima di una query SELECT è possibile accertare se gli indici sono usati correttamente, se non lo sono affatto e controllare che tipi di join vengono posti in essere.

Si prenda in esame la query seguente:

EXPLAIN SELECT matricola,nome,cognome FROM dipendenti WHERE matricola = '693369';

Verrà restituito un output composto da diverse colonne.
La prima colonna (table) indica a quale tabella si riferisce l’output (molto utile nel caso di join tra molteplici tabelle).
Utilissima è type che fa presente quale tipo di join viene utilizzato: dalla situazione migliore a quella peggiore le possibilità sono system, const, eq_ref, ref, range, index, all.
La colonna possible_keys riferisce quali indici possono applicarsi alla tabella e key quale di essi è effettivamente usato (NULL indica che nessun indice è in uso).
Nella colonna key_len è riportata la lunghezza della chiave (più corta è, meglio è) mentre rows suggerisce quante righe devono essere esaminate per trovare il dato cercato.
Infine, extra mostra delle informazioni addizionali: il caso peggiore è quando qui figurano using temporary o using filesort.

Le query lente, che non usano un indice, mostreranno le indicazioni ALL/NULL: semplicemente aggiungendo un indice (nel caso precedente sul campo matricola la situazione cambierà radicalmente).

Come funzionano gli indici in MySQL

Gli indici, com’è evidente, aiutano moltissimo a velocizzare le query SELECT.
C’è comunque un piccolo prezzo da pagare: ogni volta che si aggiorna una tabella, dovrà essere mantenuto aggiornato anche il corrispondente indice. Purtuttavia, se l’applicazione non effettua un numero di query INSERT INTO molto maggiore delle SELECT e le query di inserimento devono essere velocissime, il prezzo da pagare è assolutamente accettabile.

Una query come quella di seguito può ottenere notevoli benefici creando un indice sul campo cognome:

SELECT nome FROM dipendenti WHERE cognome="Neri";

Si supponga che la tabella dei dipendenti contenga moltissimi record con lo stesso cognome. Buona cosa, per velocizzare la query, è creare quindi un indice anche sul nome del dipendente.
La buona notizia è che MySQL supporta il cosiddetto leftmost prefixing: ciò significa che un ipotetico indice multi-campo A,B,C potrà essere utilizzato per cercare i dati richiesti non soltanto per le combinazioni A,B,C ma anche per A,B o solamente per A.

Nell’esempio, si ipotizzi di usare la query che segue per generare l’indice su cognome e nome:

ALTER TABLE dipendenti ADD INDEX(cognome,nome);

L’indice a doppio campo così creato verrà quindi usato, ad esempio, in entrambe le seguenti due query:

SELECT salario_straordinario FROM dipendenti WHERE cognome="Rossi"; SELECT salario_straordinario FROM dipendenti WHERE cognome="Rossi" and nome="Mario";

La query successiva, invece, non utilizzerà l’indice appena costruito perché il campo nome non figura nella parte sinistra dell’indice:

SELECT salario_straordinario FROM dipendenti WHERE nome="Mario";

Nel caso in cui si avesse la necessità di effettuare query di questo tipo, bisognerà aggiungere separatamente un indice MySQL a sé stante.

Per accertarsi delle differenze e delle prestazioni delle tre query, basterà anteporre lo statement EXPLAIN.

Quando si crea un indice in MySQL, poi, un ulteriore “trucchetto” da tenere a mente ha a che fare con le sue dimensioni.
Si supponga che il campo cognome e nome della tabella dipendenti siano, ciascuno, lunghi 40 caratteri. Ciò implica che l’indice creato poco sopra sarà di 80 caratteri e ciascuna operazione INSERT INTO dovrà aggiungere al database MySQL 80 caratteri addizionali. Allo stesso tempo, ogni SELECT dovrà gestire ulteriori 80 caratteri.

Ridurre la dimensione degli indici, spesso, aiuta moltissimo:

ALTER TABLE dipendenti ADD INDEX(cognome(20),nome(20));

In questo caso, saranno utilizzati – per costruire l’indice – solamente i primi 20 caratteri dei campi cognome e nome riducendo del 50% la dimensione complessiva dell’indice stesso (con un conseguente miglioramento delle prestazioni).

Ovviamente, la lunghezza dell’indice per ciascun campo dovrà essere scelta in maniera ragionata. Che significato può avere creare un indice, ad esempio, di un solo carattere?

Query optimizer, ANALYZE e OPTIMIZE in MySQL

Il componente software che, in MySQL, decide quali chiavi – se disponibili – utilizzare per ciascuna query, si chiama query optimizer.

Usando ANALYZE TABLE nometabella; si potrà memorizzare nel database MySQL la distribuzione delle chiavi mentre con OPTIMIZE TABLE nometabella;, operazione da ripetere periodicamente, si potrà ottimizzare la struttura della tabella indicata rimuovendo gli spazi vuoti lasciati dopo operazioni di cancellazione o aggiornamento dei dati (in particolare campi varchar o text/blob).

Query lente in MySQL: ecco come e dove trovarle

Un valido aiuto (che però non deve prescindere da quanto illustrato in precedenza e da un attento studio delle query con lo statement EXPLAIN) arriva anche dalle direttive slow-query-log e long_query_time del file di configurazione di MySQL (my.cnf o my.ini).

Impostando la prima a 1, MySQL terrà traccia di tutte le query lente ovvero delle interrogazioni che, per essere eseguite, impiegano più del numero di secondi specificato in corrispondenza della direttiva long_query_time (esempio: long_query_time=5 registra le query che richiedono 5 secondi o più per essere eseguite).

Il file di log in cui le query lente (slow query) vengono automaticamente annotate da MySQL è specificato accanto al parametro slow_query_log_file.

Per gestire rapidamente MySQL e le basi dati create, suggeriamo l’utilizzo di MySQL Workbench, disponibile per qualunque piattaforma.