← Nicola VitraniScrivimi

Note dal lavoro · Backend

Leggere EXPLAIN in MySQL senza paura (e scegliere l’indice giusto)

Backend · 5 min di lettura

Quando una query MySQL è lenta, EXPLAIN dice come il database intende eseguirla. All’inizio sembra una tabella criptica, ma poche colonne bastano per capire il problema.

EXPLAIN SELECT id, title
FROM posts
WHERE author_id = 12 AND status = 'published'
ORDER BY created_at DESC
LIMIT 20;

Le colonne che contano

  • type: come vengono lette le righe. ALL significa scansione completa della tabella (quasi sempre da evitare su tabelle grandi); ref, range e const indicano che usa un indice.
  • key: l’indice scelto davvero. Se è NULL non ne usa nessuno.
  • rows: una stima delle righe da leggere. Più è vicina al risultato atteso, meglio è.
  • Extra: dettagli importanti. Using filesort e Using temporary segnalano ordinamenti o tabelle temporanee costosi; Using index indica che bastano i dati dell’indice.

Perché un indice non viene usato

  • Una funzione sulla colonna, come WHERE DATE(created_at) = ..., impedisce di sfruttare l’indice: meglio un intervallo (created_at >= ... AND created_at < ...).
  • LIKE '%testo' con il carattere jolly all’inizio non può usare un indice normale.
  • In un indice composto conta l’ordine delle colonne: viene sfruttato a partire dalla prima.
  • Tipi diversi tra colonna e valore (una stringa confrontata con un numero) possono forzare conversioni.

Un indice composto per la query sopra

CREATE INDEX posts_author_status_created
  ON posts (author_id, status, created_at);

Le colonne di uguaglianza (author_id, status) vengono per prime, quella usata per l’ordinamento (created_at) per ultima: così il database trova le righe giuste già in ordine, senza ordinarle a parte.

Misurare davvero

Da MySQL 8.0.18 esiste EXPLAIN ANALYZE, che esegue la query e mostra i tempi reali oltre alle stime. Confronta sempre prima e dopo con dati realistici: un indice in più rende più lente le scritture e occupa spazio, quindi va aggiunto per una ragione misurata.

Parliamo del tuo progettoTutte le note