Risoluzione dei problemi di query lente in MySQL: una guida passo passo

La risoluzione dei problemi di query lente in MySQL inizia con una regola scomoda: non indovinare dal solo testo della query. Una query che sembra brutta potrebbe essere innocua perché viene eseguita una volta al giorno. Una query che sembra semplice potrebbe rovinare il database perché viene eseguita migliaia di volte al minuto, scansiona troppe righe o attende dietro i lock.

Il flusso di lavoro utile è noioso nel modo migliore. Cattura le query lente reali, raggruppale per costo, ispeziona il piano di esecuzione, cambia una cosa e misura di nuovo. Questo ti impedisce di aggiungere indici a caso, modificare le impostazioni globali alla cieca o incolpare MySQL quando l'applicazione sta inviando un pattern di query evitabile.

Di solito inizio con tre domande:

• Quale query sta danneggiando gli utenti, non solo sembrando sospetta?
• Il tempo viene speso per leggere righe, ordinare, attendere lock o attendere l'applicazione?
• Posso dimostrare la correzione con EXPLAIN, tempi e nuovi dati del log lento?

Inizia con il log delle query lente

Il log delle query lente di MySQL registra le istruzioni che superano la soglia configurata. Secondo il manuale di MySQL, il log è disabilitato per impostazione predefinita, long_query_time è impostato a 10 secondi e un'istruzione normalmente deve essere eseguita per almeno quel tempo ed esaminare almeno min_examined_row_limit righe prima di essere registrata. Se log_queries_not_using_indexes è abilitato, MySQL può anche registrare le istruzioni che non utilizzano indici per la ricerca di righe. Questa opzione è utile durante la diagnosi, ma può produrre molto rumore su sistemi occupati.

Una configurazione iniziale pratica è simile a questa:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_output = FILE

Su molti sistemi di produzione, 1 secondo è un primo passaggio ragionevole. Per un'API sensibile alla latenza, potresti abbassarlo temporaneamente a 0,5 o 0,2. Fallo con un piano e un controllo dello spazio su disco. Un database ad alto traffico può scrivere una quantità sorprendente di dati di log lenti una volta che la soglia scende.

Puoi controllare le impostazioni attive da una sessione MySQL:

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_output';
SHOW VARIABLES LIKE 'min_examined_row_limit';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

Per un'indagine temporanea, puoi abilitare il log senza modificare il file di configurazione:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

Ricorda che le modifiche SET GLOBAL potrebbero non sopravvivere a un riavvio a meno che non le persista anche attraverso il tuo normale processo di configurazione. Su MySQL 8, alcuni team usano SET PERSIST, ma preferisco ancora impegnare l'impostazione desiderata nella gestione della configurazione in modo che il prossimo operatore possa vederla.

Se attivi log_queries_not_using_indexes, considera anche di impostare log_throttle_queries_not_using_indexes in modo che un endpoint rumoroso non inondi il log. MySQL supporta questa limitazione proprio perché la registrazione senza indici può crescere rapidamente.

Raggruppa il log prima di leggere le singole query

I log lenti grezzi sono ripetitivi. Potresti vedere la stessa query centinaia di volte con ID diversi. Leggere il file dall'inizio alla fine perde tempo e fa sembrare le query rare e spaventose più importanti di quelle comuni e costose.

Inizia con mysqldumpslow, che è incluso nelle installazioni MySQL in molti ambienti:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

Questo chiede i primi dieci pattern ordinati per tempo di query. I flag esatti variano in base alla versione e alla piattaforma, quindi controlla mysqldumpslow --help se il tuo comando si comporta diversamente. Gli ordinamenti utili includono tempo totale, tempo medio, tempo di lock e righe esaminate.

Per indagini in produzione, pt-query-digest di Percona Toolkit è spesso migliore perché fornisce un raggruppamento più ricco e dettagli in stile percentile. Lo strumento non è magico; ti salva semplicemente dal fare calcoli a mano. La cosa importante è classificare per impatto. Una query che impiega otto secondi una volta a notte potrebbe essere meno urgente di una query che impiega 120 millisecondi ma viene eseguita 600 volte al secondo.

Quando leggi l'output raggruppato, cerca pattern:

• Tempo totale elevato: probabilmente visibile all'utente o pesante in termini di risorse.
• Conteggio elevato: spesso un ciclo dell'applicazione o una cache mancante.
• Righe esaminate elevate con poche righe inviate: di solito un problema di indicizzazione o filtraggio.
• Tempo di lock elevato: possibilmente una transazione, contesa di scrittura, lock di metadati o problema DDL.

Non dare per scontato che Rows_examined elevato sia sempre negativo. Le query di report e i job batch a volte scansionano intenzionalmente. La domanda è se la scansione corrisponde al lavoro e se avviene al momento giusto.

Riproduci una query in sicurezza

Scegli un pattern di query e ottieni un campione reale con parametri. Se il log lento ha normalizzato i letterali, trova la query originale nei log dell'applicazione, nelle tracce APM o nella voce del log lento grezzo.

Prima di eseguirla manualmente, controlla il raggio d'azione. Una SELECT lenta su una replica è di solito sicura. Un UPDATE lento in produzione non è qualcosa da eseguire casualmente. Per le query di scrittura, ispeziona prima il piano e il pattern della transazione, oppure testa su una copia di staging con dati realistici.

Una nota utile per ogni query è simile a questa:

Endpoint: GET /customers/123/orders
Pattern query: ordini per cliente e stato, più recenti prima
Osservato: 1,8s medio, 420k righe esaminate, 20 righe inviate
Dimensione tabella: 12M righe
Dimensione risultato attesa: una pagina di ordini
Sospetto: manca indice composito per customer_id, status, created_at

Questa nota mantiene il lavoro legato a un percorso utente reale invece di un frammento SQL casuale.

Usa EXPLAIN, poi leggilo come un operatore

Esegui EXPLAIN sulla query lenta:

EXPLAIN
SELECT id, customer_id, status, created_at, total
FROM orders
WHERE customer_id = 123
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Per MySQL 8, EXPLAIN ANALYZE può eseguire la query e mostrare informazioni temporali effettive. Usalo con attenzione su query costose perché esegue realmente l'istruzione. Per semplici query SELECT in un ambiente controllato, può essere molto utile.

Le colonne che controllo per prime sono type, possible_keys, key, rows, filtered e Extra.

type ti dice il pattern di accesso. const, eq_ref, ref e range sono di solito buoni segni. index significa che MySQL sta scansionando un indice, il che potrebbe essere ancora troppo lavoro. ALL significa una scansione completa della tabella. Una scansione completa non è automaticamente sbagliata su una tabella piccola, ma è sospetta su una tabella calda con milioni di righe.

key mostra l'indice scelto da MySQL. Se possible_keys elenca un indice promettente ma key è diverso, l'ottimizzatore potrebbe pensare che l'altro indice sia più economico. Questo può accadere a causa di scarsa selettività, statistiche obsolete o un indice che non corrisponde al filtro e all'ordinamento insieme.

rows è una stima, non una promessa. Se la stima è enormemente sbagliata, esegui ANALYZE TABLE durante una finestra di manutenzione appropriata o verifica se la distribuzione dei dati è distorta.

Extra spesso racconta la storia. Using filesort significa che MySQL ha bisogno di un passaggio di ordinamento separato; non significa necessariamente ordinamento su disco, ma vale la pena controllarlo quando il set di risultati è grande. Using temporary appare spesso con raggruppamenti, query distinct o ordinamenti complessi. Using index può essere positivo perché la query è soddisfatta dall'indice senza leggere le righe della tabella.

Correggi gli indici tenendo a mente la forma completa della query

La correzione più comune per le query lente non è "aggiungi un indice alla colonna nella clausola WHERE". La regola migliore è: costruisci un indice che corrisponda al modo in cui la query filtra, unisce, ordina e limita le righe.

Per la query degli ordini sopra, un indice a colonna singola su customer_id potrebbe aiutare, ma potrebbe comunque lasciare MySQL a ordinare molte righe per quel cliente. Un indice composito è spesso più utile:

CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at);

Se la query ordina per più recenti prima, MySQL può spesso scansionare l'indice in ordine inverso. In MySQL 8 puoi anche definire indici discendenti quando si adatta a un pattern più ampio:

CREATE INDEX idx_orders_customer_status_created_desc
ON orders (customer_id, status, created_at DESC);

L'ordine delle colonne è importante. Metti prima i filtri di uguaglianza, poi le colonne di intervallo o ordinamento quando corrispondono alla query. Ad esempio, con WHERE customer_id = ? AND status = ? ORDER BY created_at DESC LIMIT 20, customer_id, status, created_at è di solito più utile di created_at, customer_id, status.

Non aggiungere ogni indice che sembra utile. Gli indici accelerano le letture ma rallentano le scritture e consumano spazio di archiviazione. Se la tabella riceve molti inserimenti o aggiornamenti, un nuovo indice composito ha un costo reale. Controlla prima gli indici esistenti:

SHOW INDEX FROM orders;

A volte la risposta giusta è sostituire due indici deboli con un indice composito migliore, non mantenerli tutti e tre.

Riscrivi le query che bloccano l'uso dell'indice

Alcune query lente sono lente perché nascondono i valori indicizzati dietro funzioni o pattern che MySQL non può utilizzare in modo efficiente.

Questa versione è comune e dolorosa:

SELECT *
FROM orders
WHERE YEAR(created_at) = 2026;

Se created_at è indicizzato, avvolgerlo in YEAR() può impedire una normale ricerca per intervallo. Scrivi il predicato come intervallo invece:

SELECT id, customer_id, status, created_at, total
FROM orders
WHERE created_at >= '2026-01-01'
  AND created_at <  '2027-01-01';

La stessa idea si applica alle ricerche con wildcard iniziali:

WHERE email LIKE '%@example.com'

Un normale indice B-tree non può saltare al centro di una stringa. Se le ricerche per suffisso sono importanti, potresti aver bisogno di una colonna generata, un campo normalizzato separato o un sistema di ricerca progettato per quel caso d'uso.

Fai anche attenzione a SELECT *. Sembra innocuo durante lo sviluppo, ma può forzare MySQL a leggere le righe della tabella quando una proiezione più piccola potrebbe utilizzare un indice di copertura. Invia anche dati non necessari attraverso la rete.

Controlla i lock quando il piano della query sembra a posto

Una query può avere un piano decente ed essere comunque lenta perché è in attesa. Il Lock_time del log lento può indicarti quella direzione, ma non spiega ogni tipo di attesa. Se gli utenti segnalano pause casuali, controlla le sessioni attive:

SHOW PROCESSLIST;

Su MySQL 8, le viste Performance Schema e sys schema possono fornire dettagli migliori, a seconda di come è configurato il server. Per una rapida occhiata, controllo spesso le transazioni a lunga esecuzione e le istruzioni bloccate prima di cambiare gli indici.

Un esempio reale: una query UPDATE orders SET status = ? WHERE id = ? dovrebbe essere veloce. Se appare nel log lento con una ricerca per chiave primaria, il problema potrebbe essere una transazione che ha lasciato la riga bloccata mentre faceva lavoro non correlato. La correzione non è un altro indice. La correzione è accorciare la transazione e spostare le chiamate esterne lente al di fuori di essa.

I lock di metadati possono creare una trappola simile. Una migrazione che esegue ALTER TABLE potrebbe attendere una vecchia transazione, mentre nuove query si accumulano dietro il DDL in sospeso. Il log delle query lente mostrerà i sintomi, ma la causa principale è il comportamento di distribuzione.

Ottimizza le impostazioni del server solo dopo il lavoro sulle query

La configurazione è importante, ma è facile abusarne come prima risposta. Se una query scansiona cinque milioni di righe per restituirne dieci, aumentare la memoria potrebbe solo far soffrire meno il piano scadente.

Per i sistemi pesanti su InnoDB, innodb_buffer_pool_size è la prima impostazione da rivedere. Su un server MySQL dedicato è spesso impostato a una grande quota di memoria, ma il valore giusto dipende da cos'altro gira sull'host, dalla dimensione del dataset e dal carico di lavoro. Non copiare una percentuale ciecamente da un post del blog.

Controlla anche se il database è in attesa del disco. Se il working set non entra in memoria, o lo storage è saturo, anche le query ben indicizzate possono bloccarsi. Abbina la revisione delle query con le metriche dell'host: CPU, latenza del disco, IOPS, pressione della memoria e numero di connessioni.

I pool di connessioni possono far sembrare peggiori le query lente. Se un endpoint spara troppe istruzioni lente, il pool si riempie, le richieste non correlate aspettano le connessioni e l'intera app sembra rotta. In quel caso, correggere la query è ancora il lavoro principale, ma i limiti del pool e i timeout determinano quanto elegantemente il sistema fallisce.

Dimostra la correzione

Dopo aver aggiunto un indice o riscritto una query, esegui di nuovo EXPLAIN. Vuoi vedere meno righe stimate, una chiave migliore scelta e meno passaggi extra costosi. Quindi testa la query effettiva con parametri realistici.

Non fermarti a una esecuzione veloce. La cache calda può nascondere problemi. Prova casi comuni, grandi e scomodi:

• Un cliente con molti ordini.
• Un cliente senza ordini corrispondenti.
• Un intervallo di date che copre un periodo intenso.
• Un valore di stato che corrisponde alla maggior parte delle righe.

Quindi guarda il log lento dopo la distribuzione. Il miglior risultato non è "la query sembrava migliore in staging". Il miglior risultato è che il pattern della query scompare dai principali colpevoli, la pressione della CPU o I/O diminuisce e il percorso utente è più veloce.

La risoluzione dei problemi di query lente in MySQL è principalmente una raccolta disciplinata di prove. Abilita il log con soglie ragionevoli, raggruppa i pattern costosi, ispeziona il piano, correggi la forma della query e convalida con dati freschi. Questa abitudine previene sia la sotto-correzione che la sovra-correzione, che è esattamente ciò che vuoi quando il database è già sotto pressione.