Ottimizzazione delle query MySQL per elevate prestazioni

Versione di riferimento: MySQL 8.4 con tabelle InnoDB. L’ottimizzazione non parte dall’aggiunta di indici “a intuito”: parte da una query reale, da un piano di esecuzione e da una misura sul volume di dati che l’applicazione usa davvero.

Un esempio riproducibile

CREATE TABLE customers (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  city VARCHAR(100) NOT NULL,
  registration_date DATE NOT NULL,
  email VARCHAR(255) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_customers_email (email)
) ENGINE=InnoDB;

CREATE INDEX idx_city_registration
  ON customers (city, registration_date);

L’indice composto è coerente con questa query: prima confronta l’uguaglianza su city, poi applica un intervallo su registration_date. L’ordine non è intercambiabile: un indice composto può essere usato anche dai suoi prefissi a sinistra, quindi va progettato sulle query effettive, non sulla sola lista delle colonne disponibili.

Leggere EXPLAIN senza indovinare

EXPLAIN ANALYZE
SELECT id, email, registration_date
FROM customers
WHERE city = 'Milano'
  AND registration_date >= '2025-01-01';

EXPLAIN descrive il piano scelto; EXPLAIN ANALYZE esegue la query e aggiunge righe e tempi osservati. Su dati compatibili con il filtro ci si aspetta un accesso per intervallo sull’indice idx_city_registration, non una scansione completa. Il piano non è un voto: confronta righe stimate e reali, controlla l’indice scelto e misura prima e dopo una modifica. EXPLAIN ANALYZE esegue l’istruzione, quindi va usato con prudenza su query costose o in produzione.

Rendere la condizione cercabile

Una funzione applicata alla colonna può impedire l’uso efficace di un indice. Invece di:

WHERE YEAR(registration_date) = 2025

usa un intervallo equivalente:

WHERE registration_date >= '2025-01-01'
  AND registration_date <  '2026-01-01'

Non esiste però una regola universale. Un indice aggiuntivo occupa spazio e aumenta il lavoro di INSERT, UPDATE e DELETE. Query poco selettive, tabelle piccole o un piano basato su statistiche non aggiornate possono rendere corretta una scansione. Se un indice atteso non viene scelto, verifica prima il piano e le statistiche, ad esempio con ANALYZE TABLE, invece di forzarlo alla cieca.

Procedura pratica

1. Individua una query lenta con metriche o log dell’applicazione.
2. Salva EXPLAIN e, quando sicuro, EXPLAIN ANALYZE.
3. Modifica un solo elemento: predicato, indice o selezione delle colonne.
4. Ripeti la misura con dati rappresentativi e controlla l’impatto sulle scritture.

La struttura delle tabelle influenza direttamente le query e gli indici disponibili: per il modello relazionale e i casi in cui una denormalizzazione è motivata, leggi anche la guida al database design con MySQL. Per i dettagli del piano di esecuzione, consulta il manuale MySQL 8.4 su EXPLAIN.