Velocizzare i risultati delle query MySQL

L'entità delle interrogazioni che vengono lanciate ad un database possono incidere in modo rilevante sulle performance di un server. Per la soluzione degli input sono infatti necessarie risorse, quindi, maggiore sarà il numero delle richieste più elevato sarà il consumo di risorse da parte del sistema per la loro soddisfazione.

In condizioni di alto traffico questo fattore può essere all'origine di rallentamenti e ritardi nel caricamento delle pagine web; lo scopo di questo articolo, è quello di dimostrare con semplici esempi che le soluzioni ai rallentamenti non devono essere ricercate necessariamente nell'acquisto di una configurazione server superiore, ma più economicamente nell'ottimizzazione delle tabelle coinvolte dalle interrogazioni.

MySQL mette a disposizione alcuni strumenti utili a questo fine, nelle pagine che seguiranno concentreremo la nostra attenzione in particolare sugli indici.

Creazione e caratteristiche degli indici

La creazione di indici in un database MySQL permette di evitare che ogni ricerca sia preceduta da una scansione completa delle tabelle utilizzate (full table scan). L'indicizzazione è stata ideata appositamente per velocizzare l'esecuzione delle query di selezione e viene introdotta semplicemente utilizzando l'apposito comando, CREATE INDEX, seguito dal nome del campo o dei campi interessati alla generazione degli indici:

 mysql> CREATE INDEX nome_cognome ON tbl (nome,cognome);

In alternativa è possibile creare un indice anche per alterazione della tabella:

 mysql> ALTER TABLE tbl ADD INDEX nome_cognome (nome,cognome);

MySQL consente di creare fino a 16 indici all'interno di una stessa tabella, sono inoltre supportati indici su più colonne, indici multipli relativi a più colonne e indici per ricerche full-text. In pratica, con gli indici effettueremo le stesse operazioni che si svolgono comunemente in una biblioteca quando si ordinano i vari testi per titolo, autore, argomento ecc., evitando così di dover passare in rassegna tutti i libri ogni volta che si presenta la necessità di consultarne uno solo o una parte di essi.

Utilizzo degli indici

Una volta indicizzata una colonna (o più colonne) di una tabella, sarà possibile utilizzare in modo più efficiente le clausole come WHERE ed HAVING per delimitare il raggio di azione delle nostre ricerche. Se non conosciamo la struttura dei dati su cui stiamo operando e vogliamo sapere quali indici sono stati definiti, dovremo ricorrere al comando SHOW INDEX.

 mysql> SHOW INDEX FROM tbl;

Se l'output non dovesse indicare ulteriori indici oltre a quello relativo alla chiave primaria potremmo prendere in considerazione la possibilità di crearne alcuni; prima di far questo sarà però opportuno analizzare con attenzione il funzionamento e l'entità delle query utilizzate.

Prendiamo per esempio una tabella simile alla seguente:

 mysql> CREATE TABLE tbl ( 
 id INT(11) NOT NULL AUTO_INCREMENT, 
 nome VARCHAR(40) NOT NULL, 
 cognome VARCHAR(50) NOT NULL, 
 anni INT(3) NOT NULL, 
 id_citta INT(3) NOT NULL, 
 PRIMARY KEY (id) 
);

Ora supponiamo di voler ricavare un determinato Id sulla base di alcuni dati di riferimento come il nome, il cognome e l'età della persona a cui è associato l'Id; in questo caso potremmo avere:

 SELECT id FROM tbl 
WHERE nome='Mario' 
AND cognome='Rossi' 
AND anni=101;

Per evitare che MySQL debba scorrere l'intero contenuto della tabella alla ricerca del dato desiderato, sarà opportuno indicare un indice attorno al quale il DBMS possa regolarsi; potremmo per esempio indicizzare la colonna relativa ai nomi:

ALTER TABLE tbl ADD INDEX nome (nome);

In questo modo MySQL utilizzerà l'indice creato per l'accesso ai records che hanno un valore "nome" uguale a "Mario" escludendo tutti gli altri, quindi seguiterà ad eseguire l'interrogazione applicando le altre condizioni espresse nella query fino alla produzione dell'output.

Otterremo così una query più efficace ma non ancora al massimo dell'efficienza; è chiaro infatti che l'interrogazione espressa, il cui scopo è plausibilmente quello di trovare un solo record, in presenza di un numero elevato di dati avrà comunque la necessità di effettuare una scansione che potrebbe anche coinvolgere parecchie righe (quanti "Mario" ci sono in una città?).

Nel nostro caso sarebbe quindi ancora più utile ricorrere ad un indice multiplo coinvolgendo per esempio il campo "anni" (quanti Mario di 101 anni ci possono essere in una città?) e se non bastasse, anche quello relativo al "cognome".

In questo modo MySQL andrà ad estrarre direttamente il record interessato dall'interrogazione senza ulteriori passaggi.

Indici singoli ed indici multipli

Vale la pena di ricordare che la creazione di un indice multiplo e l'introduzione di più indici su più colonne non portano allo stesso risultato; un indice multiplo, che nel nostro caso potrebbe essere ottenuto attraverso la seguente istruzione:

ALTER TABLE tbl ADD INDEX nome_cognome_anni (nome,cognome,anni);

non equivale ad una serie di istruzioni tipo:

ALTER TABLE tbl ADD INDEX nome (nome);

ALTER TABLE tbl ADD INDEX cognome (cognome);

ALTER TABLE tbl ADD INDEX anni (anni);

Nel primo caso infatti MySQL prende in considerazione tutti i campi indicizzati come un unico indice, nel secondo caso invece gli indici verranno presi in considerazione in modo indipendente, infatti quando MySQL esegue una query ha la capacità di utilizzare un solo indice per volta.

Nel caso della SELECT utilizzata come esempio nel paragrafo precedente, dove eravamo presenza di 3 diversi indici, il DBMS avrebbe utilizzato quello in grado di identificare il minor numero di records, una soluzione razionale ma meno efficiente di indice multiplo su più colonne.

EXPLAIN per l'analisi delle query

MySQL ci mette a disposizione uno strumento, EXPLAIN, con il quale analizzare le caratteristiche delle interrogazioni lanciate verso un database; quando questo costrutto viene espresso prima di una query di selezione mostra in output il modo in cui il DBMS intende eseguire l'interrogazione e quanti saranno i records coinvolti:

Prendiamo per esempio la seguente selezione:

EXPLAIN SELECT id FROM tbl 
WHERE nome='Monica' 
AND lastname='Bellucci' AND age='38'; 

+-------+------+------------------+------------------+---------+--------------------+ 
| table | type | possible_keys    | key              | key_len | ref                |
+-------+------+------------------+------------------+---------+--------------------+---  
| tbl   | ref  | nome_cognome_anni| nome_cognome_anni| 93      | const, const, const|
+-------+------+------------------+------------------+---------+--------------------+

  +------+-------------+ 
   | rows | Extra       | 
---+--------------------+ 
   | 1    | Where used  | 
   +------+-------------+ 

Vediamo nel particolare le differenti voci prodotte in output dall'interrogazione:

• table - indica il nome della tabella coinvolta nella query, un dato rilevante in presenza di join trà più tabelle.
• type - mostra il tipo di accesso effettuato sulla tabella: il valore ref indica un accesso tramite chiave univoca.

Nel caso in cui type sia ALL, questo potrebbe indicare la necessità di creare un indice (o l'esistenza di un indice errato) se la finalità della query espressa non è quella di accedere a tutte o alla maggior parte delle righe archiviate.

Type può avere anche altri valori:

• Const e system: indicano una sola lettura della tabella per query, come nel caso in cui sia contenuta una sola riga).
• Eq_ref: indica la possibilità di accesso univoco ad una sola riga della tabella, come nel caso di un indice UNIQUE.
• Range: si presenta quando per la soluzione della query vengono lette soltanto alcune righe della tabella.
• Index: scorre l'intero indice fino alle righe cercate.

• possible_keys - indica i nomi degli indici utilizzabili e risulta molto importante per rilevarne l'effettivo utilizzo.
• key - è relativo al nome dell'indice effettivamente utilizzato nella query; in presenza di un valore NULL sappiamo che il DBMS non è stato in grado di utilizzare gli indici.
• key_len - esprime la lunghezza in byte di un indice; nel caso in cui venga utilizzato un indice multiplo, la lunghezza sarà pari alla somma dei campi indicizzati: nel nostro caso 40 (nome) + 50 (cognome) + (anni) 3 = 93
• ref - indica la colonna utilizzata per la selezione delle righe cercate; const specifica che è possibile identificare direttamente le righe cercate (probabilmente tramite un indice).
• rows - corrisponde alla stima delle righe da esaminare prima di soddisfare l'interrogazione; più siamo vicini ad uno maggiore sarà il grado di efficienza della query.
• Extra - riguarda fattori che hanno la capacità di influire nei tempi di esecuzione, come per esempio la creazione di tabelle temporanee o l'impiego di file esterni.

Pro e contro degli indici

Sia chiaro che un utilizzo indiscriminato degli indici potrebbe portare ad un esito opposto a quello voluto, cioè ad un rallentamento nell'esecuzione delle ricerche e non soltanto di queste. Innanzitutto è bene precisare che nel momento in cui creiamo un campo definendolo come chiave primaria, questo viene indicizzato automaticamente da MySQL, ciò vuol dire che definire nuovamente un indice per esso costituirà unicamente uno spreco di risorse.

In secondo luogo è importante stabilire gli indici solo per le colonne più frequentemente utilizzate nelle interrogazioni di selezione; definire 16 indici in una tabella non porterebbe ad un'accelerazione nella restituzione degli output, semmai all'esito contrario.

Infine, và sottolineato il fatto che se da una parte la definizione degli indici velocizza l'esecuzione delle query, dall'altra è possibile registrare un certo rallentamento per quanto riguarda le operazioni di aggiornamento (UPDATE) e inserimento dei dati (INSERT). In particolare per quanto riguarda le query di aggiornamento dobbiamo tenere conto che MySQL andrà a ricreare gli indici per ogni occorrenza di queste ultime.

In generale è buona norma testare l'efficacia degli indici e i vantaggi apportati in termini di risparmio delle risorse; sarà comunque possibile in ogni momento eliminare un indice grazie al comando DROP INDEX:

DROP INDEX nome_indice ON tbl;