Ottimizzazione delle Query MySQL: Una Guida Pratica

Le query MySQL lente raramente sono misteriose una volta che si osserva il piano di esecuzione. La parte difficile non è sapere che gli indici sono importanti. La parte difficile è dimostrare quale query è lenta, capire perché MySQL ha scelto un piano e modificare la query o l'indice senza peggiorare le scritture, l'archiviazione o altre query.

Inizia con le prove. Utilizza il log delle query lente, Performance Schema, tracce dell'applicazione o uno strumento di monitoraggio come PMM per trovare le query che effettivamente danneggiano gli utenti. Poi usa EXPLAIN e, quando sicuro, EXPLAIN ANALYZE per vedere cosa sta facendo MySQL.

Comprendere le Prestazioni delle Query

Le cause comuni includono:

• Indici Mancanti o Inefficaci: Senza indici appropriati, MySQL deve eseguire scansioni complete della tabella, che sono molto inefficienti per tabelle grandi.
• SQL Scritto Male: Filtri non sargabili, SELECT * non necessari, cross join accidentali e condizioni di join inefficienti possono degradare le prestazioni.
• Set di Dati Grandi: Più dati significano più pagine da leggere, ordinare, raggruppare e memorizzare nella cache.
• Hardware e Configurazione: Una configurazione del server non ottimale o risorse hardware insufficienti possono anche giocare un ruolo, sebbene questa guida si concentri sull'ottimizzazione a livello di query.

Il Potere di EXPLAIN

EXPLAIN è il primo strumento da utilizzare quando si vuole capire come MySQL pianifica una query. Per un semplice EXPLAIN SELECT, MySQL mostra il piano scelto dall'ottimizzatore senza restituire il set di risultati. EXPLAIN ANALYZE esegue la query e riporta i tempi effettivi, quindi usalo con cautela sui sistemi di produzione.

Come Usare EXPLAIN

Per una query di lettura, anteponi EXPLAIN:

EXPLAIN SELECT * FROM users WHERE username = 'john_doe';

Interpretare l'Output di EXPLAIN

L'output di EXPLAIN è una tabella con diverse colonne importanti:

• id: Il numero di sequenza del SELECT all'interno della query. I numeri più alti vengono generalmente eseguiti per primi.
• select_type: Il tipo di SELECT (ad es., SIMPLE, PRIMARY, SUBQUERY, DERIVED).
• table: La tabella a cui si accede.
• partitions: Le partizioni utilizzate (se il partizionamento è abilitato).
• type: Il tipo di join. Questa è una delle colonne più utili. Punta a const, eq_ref, ref o range quando la forma della query lo consente. Sii sospettoso di index e specialmente ALL su tabelle grandi.
• possible_keys: Mostra quali indici MySQL potrebbe utilizzare.
• key: L'indice che MySQL ha effettivamente scelto di utilizzare.
• key_len: La lunghezza della porzione di indice che MySQL prevede di utilizzare. Più corto non è automaticamente meglio; dipende dalla selettività e dalla query.
• ref: La colonna o costante confrontata con l'indice (key).
• rows: Una stima del numero di righe che MySQL prevede di esaminare.
• filtered: La percentuale di righe filtrate dalla condizione della tabella.
• Extra: Contiene informazioni aggiuntive su come MySQL risolve la query. Valori chiave da tenere d'occhio includono:
  • Using where: Indica che MySQL applica una condizione durante l'elaborazione delle righe. È comune e non sempre negativo.
  • Using index: Significa che la query è coperta da un indice (tutte le colonne richieste sono nell'indice), il che è positivo.
  • Using temporary: MySQL deve creare una tabella temporanea, spesso per operazioni GROUP BY o ORDER BY. Questo può essere lento.
  • Using filesort: MySQL deve eseguire un ordinamento esterno (non utilizzando un indice per l'ordinamento). Questo è spesso un segno di una clausola ORDER BY inefficiente.

Identificare i Colli di Bottiglia con EXPLAIN

Diamo un'occhiata ad alcuni scenari comuni e come EXPLAIN aiuta a identificare i problemi:

Scenario 1: Scansione Completa della Tabella

Considera una query come:

SELECT * FROM orders WHERE order_date = '2023-10-26';

Se la colonna order_date non è indicizzata, EXPLAIN potrebbe mostrare:

+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 1000000 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+

Problema: type: ALL indica una scansione completa della tabella. rows: 1000000 mostra che MySQL deve esaminare ogni riga nella tabella orders. key: NULL significa che non è stato utilizzato alcun indice.

Soluzione: Aggiungi un indice alla colonna order_date:

CREATE INDEX idx_order_date ON orders (order_date);

Dopo aver aggiunto l'indice, esegui di nuovo EXPLAIN. Dovresti vedere un tipo di accesso più selettivo come ref o range, e il numero stimato di righe dovrebbe diminuire se il filtro sulla data è selettivo.

Scenario 2: ORDER BY o GROUP BY Inefficiente

SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id ORDER BY customer_id;

Se customer_id non è indicizzato, EXPLAIN potrebbe mostrare:

+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------+
| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows   | Extra                            |
+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------+
|  1 | SIMPLE      | orders | index | NULL          | NULL | NULL    | NULL | 100000 | Using temporary; Using filesort |
+----+-------------+--------+-------+---------------+------+---------+------+--------+----------------------------------+

Problema: Using temporary e Using filesort indicano che MySQL sta eseguendo operazioni costose per ordinare e raggruppare i dati. Questo è spesso perché nessun indice può soddisfare efficientemente sia i requisiti di raggruppamento che di ordinamento.

Soluzione: Per questa query specifica, un indice su (customer_id) può permettere a MySQL di scansionare le righe in ordine di raggruppamento. Se la query reale filtra prima per data, stato o tenant, un indice composito potrebbe essere migliore, come (tenant_id, status, customer_id).

CREATE INDEX idx_customer_id ON orders (customer_id);

Scenario 3: Usare SELECT * Inutilmente

Quando selezioni tutte le colonne (*) ma ne servono solo alcune, trasferisci più dati e potresti impedire che un indice coprente sia utile. Questo è particolarmente evidente su tabelle larghe con colonne JSON, blob di testo o molti campi nullable.

-- Supponiamo un indice su 'status'
SELECT * FROM tasks WHERE status = 'pending';

EXPLAIN potrebbe mostrare Using where ma se la query richiede colonne non presenti nell'indice utilizzato per il filtraggio, dovrà comunque accedere ai dati della tabella.

Soluzione: Specifica solo le colonne necessarie:

SELECT task_id, description FROM tasks WHERE status = 'pending';

Se esegui frequentemente questa esatta forma di query, considera un indice coprente che includa la colonna del filtro e le colonne restituite:

CREATE INDEX idx_tasks_status_id_description
  ON tasks (status, task_id, description);

Non creare indici coprenti per ogni query. Accelerano le letture a costo di overhead di archiviazione e scrittura.

Riscrivere Query Lente

Oltre all'indicizzazione, il modo in cui strutturi SQL può cambiare la quantità di lavoro che MySQL deve fare.

Evita Subquery Correlate

Le subquery correlate possono essere eseguite una volta per ogni riga elaborata dalla query esterna. MySQL può ottimizzarne alcune, ma se EXPLAIN mostra dipendenze ripetute, un join o una tabella derivata è spesso più chiara e veloce.

Spesso inefficiente:

SELECT o.order_id, o.order_date
FROM orders o
WHERE o.customer_id IN (
    SELECT c.customer_id
    FROM customers c
    WHERE c.country = 'USA'
);

Spesso meglio come join:

SELECT o.order_id, o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.country = 'USA';

Usa EXPLAIN su entrambe le versioni. Il join non è automaticamente più veloce in ogni schema, ma è più facile per molti team da comprendere e indicizzare.

Ottimizza le Clausole LIKE

I caratteri jolly iniziali (%) nelle clausole LIKE di solito impediscono a un normale indice B-tree di essere utilizzato per una ricerca per intervallo.

Inefficiente:

SELECT * FROM products WHERE product_name LIKE '%widget';

Meglio (se possibile):

SELECT * FROM products WHERE product_name LIKE 'widget%';

Se hai bisogno di corrispondenze di tipo "contiene", considera gli indici full-text di MySQL per la ricerca testuale appropriata, approcci n-gram per lingue specifiche o un motore di ricerca quando rilevanza e corrispondenza flessibile sono importanti.

Usa UNION ALL Invece di UNION Quando Possibile

UNION rimuove le righe duplicate, il che richiede un passaggio extra di ordinamento e deduplicazione. Se sai che non ci sono duplicati o non è necessario rimuoverli, UNION ALL è più veloce.

Lento:

SELECT name FROM table1
UNION
SELECT name FROM table2;

Veloce:

SELECT name FROM table1
UNION ALL
SELECT name FROM table2;

Altri Suggerimenti per l'Ottimizzazione

• Mantieni le Statistiche Aggiornate: Assicurati che le statistiche delle tabelle siano aggiornate in modo che l'ottimizzatore di query possa prendere decisioni informate. Questo è spesso gestito automaticamente ma può essere aggiornato manualmente con ANALYZE TABLE.
• Configurazione del Server: L'ottimizzazione delle query non compenserà un pool di buffer InnoDB minuscolo o dischi sovraccarichi. In MySQL 8.0, la vecchia cache delle query è stata rimossa, quindi non pianificare nuove ottimizzazioni intorno a query_cache_size.
• Monitoraggio Regolare: Utilizza strumenti come MySQL Enterprise Monitor, Percona Monitoring and Management (PMM) o viste integrate di performance schema per tracciare le query lente e identificare le tendenze.

Un Flusso di Lavoro Pratico per l'Ottimizzazione

Per i sistemi di produzione, ottimizza partendo dalla query lenta verso l'esterno:

1. Cattura l'SQL esatto, i valori associati, i conteggi delle righe e i tempi.
2. Esegui EXPLAIN FORMAT=TREE o EXPLAIN FORMAT=JSON se la tua versione di MySQL lo supporta.
3. Controlla se l'indice selezionato corrisponde al modello di filtro e join.
4. Testa una riscrittura della query o una modifica dell'indice su dati realistici.
5. Confronta le righe esaminate, le tabelle temporanee, il comportamento di ordinamento e la latenza a muro.

Questo ti impedisce di aggiungere indici perché una query "sembra lenta". Gli indici hanno un costo. Ogni insert, update e delete deve mantenerli. Una tabella con dieci indici sovrapposti può diventare complessivamente più lenta anche se una singola query di lettura migliora.

Per una query comune di applicazione multi-tenant, l'ordine dell'indice spesso conta più del numero di colonne indicizzate:

SELECT id, created_at, total
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
  AND created_at >= '2025-01-01'
ORDER BY created_at DESC
LIMIT 50;

Un indice utile potrebbe essere:

CREATE INDEX idx_orders_tenant_status_created
  ON orders (tenant_id, status, created_at DESC);

Quell'indice inizia con filtri di uguaglianza, poi supporta l'intervallo di date e l'ordinamento. Se metti created_at per primo, MySQL potrebbe scansionare molti tenant prima di trovare quello giusto. Se ometti status, la query potrebbe comunque funzionare ma esaminare molte righe extra.

Attenzione ai Filtri Non Sargabili

Una condizione è sargabile quando MySQL può utilizzare un indice per cercare le righe corrispondenti. Avvolgere una colonna indicizzata in una funzione spesso rompe questo:

-- Più difficile usare un indice su created_at
SELECT * FROM orders
WHERE DATE(created_at) = '2025-01-15';

Riscrivila come un intervallo:

SELECT *
FROM orders
WHERE created_at >= '2025-01-15'
  AND created_at <  '2025-01-16';

La seconda versione permette a MySQL di cercare in un indice su created_at. La stessa idea si applica a LOWER(email), operazioni matematiche su colonne numeriche e conversioni di tipo implicite. Se la colonna è indicizzata, mantieni pulito il lato della colonna del confronto quando possibile.

Fai Attenzione con la Paginazione

La paginazione con offset diventa costosa su pagine profonde:

SELECT id, title
FROM posts
WHERE status = 'published'
ORDER BY published_at DESC
LIMIT 20 OFFSET 200000;

MySQL deve comunque attraversare le righe precedenti prima di restituire la pagina richiesta. Per feed, log di audit e tabelle amministrative, la paginazione con keyset è solitamente migliore:

SELECT id, title, published_at
FROM posts
WHERE status = 'published'
  AND (published_at, id) < ('2025-05-01 12:00:00', 987654)
ORDER BY published_at DESC, id DESC
LIMIT 20;

Abbinala a un indice come (status, published_at, id). Questo cambia leggermente il comportamento del prodotto perché gli utenti si muovono attraverso un cursore invece di saltare alla pagina 10.000, ma può trasformare una query dolorosa in una prevedibile.

Verifica con Dati Reali

I piccoli database di staging mentono. Una query che è istantanea su 20.000 righe può essere terribile su 200 milioni di righe, specialmente quando la distribuzione dei dati è distorta. Testa contro volumi e cardinalità simili alla produzione quando possibile. Se non puoi copiare i dati di produzione, almeno genera dati con dimensioni dei tenant, distribuzione degli stati e intervalli di date simili.

Un ultimo'abitudine aiuta: conserva il vecchio piano e il nuovo piano nel ticket. Il te futuro vorrà sapere perché esiste un indice.

La migliore abitudine di ottimizzazione MySQL è far sì che ogni cambiamento guadagni il suo posto. Cattura la query lenta, ispeziona il piano, modifica una query o un indice, poi confronta la latenza e le righe esaminate. Un piano EXPLAIN pulito è utile, ma la vera vittoria è una latenza di produzione inferiore senza creare nuova pressione di scrittura o gonfiore di archiviazione.