Indici e analisi delle prestazioni in SQL

Nelle lezioni precedenti abbiamo imparato a scrivere query per selezionare, inserire, modificare ed eliminare dati. Quando una tabella contiene poche righe, anche una query non particolarmente ottimizzata può essere sufficientemente veloce.

Con l'aumentare della quantità di dati, però, le prestazioni possono peggiorare. Una query che deve cercare un valore all'interno di milioni di righe può richiedere molto più tempo rispetto alla stessa operazione effettuata su una tabella piccola.

Per migliorare le prestazioni delle query possiamo utilizzare gli indici. In questa lezione vedremo come funzionano, come crearli e gestirli e come utilizzare EXPLAIN e EXPLAIN ANALYZE per capire come MySQL esegue una query.

Cosa sono gli indici

Un indice è una struttura dati che MySQL può utilizzare per trovare più velocemente le righe di una tabella.

Possiamo pensare a un indice come a quello di un libro. Senza indice, per trovare una determinata informazione potremmo essere costretti a controllare una pagina dopo l'altra. Con un indice, invece, possiamo capire più rapidamente dove cercare. Lo stesso principio si applica alle tabelle di un database.

Supponiamo di avere:

SELECT * FROM utenti
WHERE email = '[email protected]';

Se email non dispone di un indice, MySQL potrebbe dover controllare molte o tutte le righe della tabella per trovare quella corrispondente. Se invece email è indicizzata, MySQL può utilizzare l'indice per individuare più rapidamente la riga interessata.

Indici e chiavi primarie

Abbiamo già incontrato gli indici nella lezione su come creare le tabelle SQL senza soffermarci sul loro funzionamento. Quando definiamo una PRIMARY KEY:

CREATE TABLE utenti (
    id INT PRIMARY KEY,
    nome VARCHAR(100)
);

MySQL crea automaticamente un indice associato alla chiave primaria. Questo permette, per esempio, di cercare rapidamente:

SELECT * FROM utenti
WHERE id = 1000;

Non è quindi necessario creare manualmente un altro indice su id.

Lo stesso concetto vale per le colonne definite con un vincolo UNIQUE: MySQL deve mantenere un indice univoco per verificare rapidamente che non esistano duplicati.

Come creare un indice

Possiamo creare un indice utilizzando CREATE INDEX. 

La sintassi è: CREATE INDEX nome_indice ON nome_tabella (colonna);.

Ad esempio, possiamo scrivere:

CREATE INDEX idx_utenti_nome
ON utenti (nome);

In questo esempio, abbiamo creato un indice chiamato idx_utenti_nome sulla colonna nome. A questo punto MySQL può utilizzare l'indice per alcune query che fanno riferimento a quella colonna, ad esempio:

SELECT * FROM utenti
WHERE nome = 'Marco';

Indici UNIQUE

Possiamo creare anche un indice UNIQUE:

CREATE UNIQUE INDEX idx_utenti_email
ON utenti (email);

Questo indice svolge due funzioni:

Un indice UNIQUE può essere creato anche semplicemente attraverso un vincolo durante la creazione della tabella:

CREATE TABLE utenti (
    id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE
);

In questo caso MySQL crea automaticamente l'indice necessario per garantire l'unicità.

Indici composti

Un indice può contenere più colonne: si parla in questo caso di indice composto (in inglese, composite index).

Per esempio:

CREATE INDEX idx_utenti_cognome_nome
ON utenti (cognome, nome);

Nell'esempio, l'indice è composto dalle colonne cognome e nome. Questo tipo di indice è utile quando utilizziamo frequentemente entrambe le colonne nella stessa query:

SELECT * FROM utenti
WHERE cognome = 'Rossi'
  AND nome = 'Marco';

Nota: negli indici composti, l'ordine delle colonne è importante. Dovrebbe essere scelto in base a come le colonne vengono utilizzate più frequentemente nelle query.

Come eliminare un indice

Se un indice non è più necessario, possiamo eliminarlo con DROP INDEX.

La sintassi è: DROP INDEX nome_indice ON nome_tabella;.

Ad esempio:

DROP INDEX idx_utenti_nome
ON utenti;

Nell'esempio, l'indice idx_utenti_nome viene rimosso, ma i dati della tabella non vengono eliminati.

Analizzare una query con EXPLAIN

Per capire come MySQL intende eseguire una query, possiamo utilizzare EXPLAIN. EXPLAIN non esegue normalmente la query per recuperare i risultati: mostra il piano di esecuzione che MySQL prevede di utilizzare.

Ad esempio:

EXPLAIN
SELECT * FROM utenti
WHERE email = '[email protected]';

EXPLAIN ci permette di analizzare aspetti come:

È uno degli strumenti principali per individuare possibili problemi di prestazioni.

Un possibile output dell'esempio precedente potrebbe essere:

type: ref
possible_keys: idx_utenti_email
key: idx_utenti_email
rows: 1

Il campo type

Tra le informazioni restituite da EXPLAIN troviamo type: questo valore descrive il metodo utilizzato per accedere alle righe.

Tra i valori che possiamo incontrare ci sono: const, eq_ref, ref, range, index, ALL. In generale, ALL indica una lettura su tutta la tabella. 

Se viene restituito ALL, non significa necessariamente che la query sia sbagliata o lenta: su una tabella molto piccola, una scansione completa può essere perfettamente appropriata. Su una tabella con milioni di righe, invece, potrebbe essere un segnale che vale la pena analizzare.

Il campo possible_keys

EXPLAIN mostra anche possible_keys. Questo campo indica quali indici potrebbero essere utilizzati per eseguire la query.

Per esempio, potremmo ottenere 

possible_keys: idx_utenti_email

Questo significa che MySQL ha individuato quell'indice come possibile candidato, ma non significa però che lo utilizzerà necessariamente.

Il campo key

Il campo key indica l'indice che MySQL ha effettivamente scelto di utilizzare.

Per esempio:

possible_keys: idx_utenti_email
key: idx_utenti_email

In questo caso MySQL ha individuato e utilizzato l'indice idx_utenti_email. 

Se invece troviamo un valore in possible_keys, ma key: NULL, MySQL ha considerato l'indice possibile, ma ha deciso di non utilizzarlo. Questo non significa necessariamente che MySQL abbia commesso un errore: il suo algoritmo sceglie la procedura che stima essere più conveniente.

Il campo rows

Un'altra informazione importante è rows, che rappresenta una stima del numero di righe che MySQL prevede di dover esaminare. 

Ad esempio:

rows: 1000000

In questo esempio la query richiede l'esame di una quantità molto elevata di dati. Un valore molto più basso indica invece che MySQL prevede di dover esaminare molte meno righe.

Analizzare ed eseguire le query

EXPLAIN mostra la procedura stimata della query. MySQL mette a disposizione anche EXPLAIN ANALYZE che permette di eseguire la query e fornire informazioni sul processo realmente eseguito, inclusi i tempi effettivi e il numero di righe elaborate nei vari passaggi.

Ad esempio, possiamo scrivere:

EXPLAIN ANALYZE
SELECT * FROM utenti
WHERE email = '[email protected]';

Per le query di sola lettura, come SELECT, EXPLAIN ANALYZE è uno strumento molto utile per analizzare concretamente le prestazioni.