Vai al contenuto 
Registro delle query lente di MySQL mi mostra in hosting quali query consumano tempo, con quale frequenza e perché rallentano il lavoro. Vi mostro i passaggi specifici per attivare il log, analizzarlo e ricostruire le query in modo che le pagine vengano caricate più velocemente e le risorse del server lavorino in modo più efficiente.
Punti centrali
- Attivazione e impostare i valori di soglia in modo sensato
- Valutazione con pt-query-digest e mysqldumpslow
- Metriche interpretare: Tempo_di_query, Tempo_di_blocco, Righe_esaminate
- Sintonizzazione tramite indici, EXPLAIN e riscritture
- Automazione e monitoraggio in hosting
Cosa fa il log delle query lente nell'hosting?
Ospitare significa risorse condivise, quindi ogni millisecondo per ogni query conta. Uso il log per trovare le query che durano più a lungo di un limite definito e vedo cifre chiave come Query_time, Lock_time, Rows_sent e Rows_examined per ogni query. Queste cifre mi mostrano se dietro c'è un indice mancante, un join sfavorevole o una scansione completa della tabella. Soprattutto sui server con più siti, una singola query errata può mettere a dura prova CPU e I/O. Dò quindi la priorità alle query con il tempo totale più elevato, perché è qui che si ha il maggiore impatto sui tempi di caricamento e sul carico del server.
Valori di attivazione e soglia sensibile
InizioPosso farlo in tempo reale o in modo permanente tramite my.cnf, a seconda dell'accesso all'hosting. Per i test veloci, attivo il log temporaneamente e imposto long_query_time a un valore che corrisponda al traffico e all'hardware. Spesso vado a 0,1 secondi per i siti molto utilizzati, ma tengo d'occhio la dimensione del log in modo che l'I/O non cresca inutilmente. Se gli accessi diretti ai file sono limitati, uso le opzioni dello schema delle prestazioni della shell di MySQL per generare rapporti. Dopo la messa a punto, scrivo le impostazioni finali nel file di configurazione e riavvio il servizio.
SET GLOBALE slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
SET GLOBALE log_queries_not_using_indexes = 'ON';
MOSTRA VARIABILI COME 'slow_query%';
MOSTRA VARIABILI COME 'long_query_time';
Permanente Imposto opzioni come log_throttle_queries_not_using_indexes e log_slow_admin_statements in modo che il log rimanga utile e non esploda. Documento ogni valore, ad esempio perché long_query_time è 0,5 o 0,1 secondi. Questo mi permette di perfezionarlo in seguito. Negli ambienti condivisi, spesso discuto l'attivazione con il fornitore o utilizzo il suo pannello. Collego ogni attivazione con una data di inizio per poter confrontare in modo pulito gli effetti nel monitoraggio e nelle metriche.
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 10
log_slow_admin_statements = 1
Valutare efficacemente il registro lento
Dati grezzi sono rumorosi, quindi li riassumo con pt-query-digest e li ordino in base al tempo totale su un periodo di tempo significativo. In questo modo riconosco gli schemi, le query altamente variabili e le famiglie di query che variano solo in base ai parametri. Controllo la distribuzione, non solo la media, perché i valori anomali causano problemi reali agli utenti. Per una rapida panoramica, mysqldumpslow mi aiuta a vedere i dieci gruppi più lenti. Per una visione più approfondita, utilizzo finestre temporali, filtri di database e un'esportazione in un'analisi di testo.
pt-query-digest /var/log/mysql/slow-query.log
pt-query-digest --since '24h' /var/log/mysql/slow-query.log
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log
Utile è anche uno sguardo ad altri log quando entrano in gioco applicazioni o funzioni PHP. A tale scopo, utilizzo i flussi di lavoro dei log esistenti e raggruppo i risultati. Questa guida mi fornisce spesso un'introduzione: Analizzare i log. Sincronizzo i timestamp in modo da poter confrontare i picchi di traffico con i picchi delle query. Questo mi permette di vedere se le missioni della cache, i cron job o i lavori di importazione stanno utilizzando il database nello stesso momento.
Interpretare correttamente le metriche
Tempo_di_query mostra il tempo di esecuzione puro; do priorità alle query superiori a un secondo. Lock_time indica i tempi di attesa dovuti ai blocchi, che spesso derivano da transazioni inutilmente lunghe o da grandi batch. Il rapporto tra righe_esaminate e righe_inviate indica se le query stanno visualizzando troppe righe e se mancano gli indici. Se il log contiene molte voci „Nessun utilizzo di indici“, imposto il throttling e analizzo più da vicino le tabelle interessate. È sempre importante affrontare la causa piuttosto che il sintomo: Un indice sulla colonna corretta batte qualsiasi aggiornamento hardware.
| Metriche | Cosa vedo | Misura |
|---|---|---|
| Tempo_di_query alto | Tempo di esecuzione lungo per ogni versione | Controllare EXPLAIN, riscrivere la query, aggiungere un indice |
| Tempo_di_blocco alto | Tempo di attesa per le serrature | Accorciare le transazioni, ridurre le dimensioni del batch, isolamento adeguato |
| Righe_esaminate ≫ Righe_inviate | Scansionato troppo, restituito troppo poco | Indicizzare le colonne del filtro, creare la sargibilità |
| Nessun indice utilizzato | Scansione completa della tabella | Creare un indice, evitare l'espressione in WHERE |
Valori limite Lo regolo dopo la prima settimana in modo da non perdermi nel rumore. Abbasso long_query_time per gradi fino a quando non ho abbastanza riscontri per ottenere miglioramenti sistematici. Documento ogni regolazione con la data e il motivo. In questo modo la valutazione rimane focalizzata. I risultati validi mi evitano di duplicare il lavoro in seguito.
Pratica: Sintonizzazione delle query passo dopo passo
SPIEGARE è il mio inizio prima di modificare il codice. Cerco „tipo: ALL“, „righe“ con numeri grandi e „Utilizzo di filesort“ o „Utilizzo di temporanei“. Le funzioni sulle colonne in WHERE o JOIN spesso impediscono l'uso degli indici. Invece, formulo delle condizioni di accettabilità e poi verifico il nuovo piano. Ogni fase deve eseguire la riduzione delle righe in anticipo e in modo mirato.
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- Meglio:
CREARE INDICE idx_ordini_creati SU ordini(data_creazione);
SELEZIONARE * DA ORDINI
DOVE created_at >= '2026-01-01' AND created_at < '2027-01-01';
Giunti Ottimizzo controllando l'ordine di unione e facendo corrispondere gli indici alle chiavi di unione. Verifico se un indice composito copre WHERE + ORDER BY per evitare il filesort. Imposto LIMIT quando è necessaria solo un'anteprima. Conservo la cache dei risultati a livello di applicazione per le query identiche e ripetute con un basso tasso di modifica. Un'introduzione più approfondita agli indici e ai lock è disponibile qui: Indici e chiusura.
Strategie di indicizzazione per CMS e negozi
WordPress, I sistemi di WooCommerce o di negozio creano schemi tipici: molta lettura, scrittura selettiva, spesso con tabelle di meta o di prodotto. Analizzo i percorsi più comuni - home page, categoria, ricerca, cassa - e inserisco indici specifici sulle colonne di filtro, ordinamento e unione. Gli indici di copertura (ad esempio (status, created_at, id)) risparmiano un sacco di ricorsi alla tabella. Per cercare i prefissi, uso forme di indice adatte o il testo completo invece di LIKE ‚%wort%‘. Misuro ogni variazione dell'indice prima e dopo l'esecuzione dal vivo con gli stessi profili di carico.
Crescita Uso la cardinalità e gli istogrammi per controllare i set di dati in modo da non indicizzare su valori rari. Mantengo basso il numero di indici per tenere sotto controllo il carico di scrittura e i requisiti di memoria. Gli indici compositi consolidati sostituiscono diversi indici individuali. Regolo le attività di tipo autovacuum in MySQL analizzandole regolarmente e ricostruendole solo quando necessario. In questo modo l'ottimizzatore rimane affidabile.
Impostazioni del server, cache e memoria
InnoDB Determino la dimensione del pool di buffer in base ai record di dati attivi e alle dimensioni degli indici, non in base a valori generici. La aumento fino a quando la dimensione dell'insieme di lavoro è in gran parte in memoria e il tasso di page miss diminuisce. Imposto tmp_table_size e max_heap_table_size in modo che meno tabelle temporanee finiscano su disco. Per quanto riguarda la sicurezza e la latenza di scrittura, bilanciamento innodb_flush_log_at_trx_commit in modo appropriato per l'applicazione. A livello di applicazione, metto in cache i risultati frequenti e uso la cache HTTP in modo che il database veda meno richieste.
Hardware e gli effetti di rete sono inclusi nella diagnosi: L'I/O lento dello storage o il sovraccarico della CPU vengono immediatamente riconosciuti dalle query. Pertanto, misuro l'IO-wait in parallelo con le metriche del database. Se avete bisogno di più riserve, pianificate uno scaling verticale o orizzontale con un obiettivo misurabile. Questa guida fornisce una panoramica compatta dei colli di bottiglia, della messa a punto e delle risorse: Hardware e cache. In questo modo mi assicuro di non girare alla cieca la manopola sbagliata.
Concorrenza e blocco nell'hosting
Tempo_di_blocco cresce quando le transazioni lunghe toccano molte righe o quando i lavori di pulizia vengono eseguiti in prima serata. Accorcio le operazioni di scrittura, divido gli aggiornamenti di grandi dimensioni in lotti più piccoli e riduco così il tempo di mantenimento dei blocchi. Livelli di isolamento adeguati riducono i conflitti senza compromettere la coerenza dei dati. Alleggerisco gli hotspot con indici secondari e condizioni WHERE adeguate, in modo che il numero di righe interessate sia minore. Programmo i lavori in background in finestre temporali a basso traffico, in modo da dare priorità alle azioni degli utenti.
Deadlock Li analizzo in base a schemi ricorrenti: stesse tabelle, cambio di sequenza, linee identiche. Standardizzo la sequenza di accesso nel codice e nelle stored procedure. La logica di ripetizione con jitter risolve le collisioni temporanee. Ove possibile, isolo le operazioni più costose nelle code di lavoro. Questo riduce sensibilmente la varianza e aumenta le prestazioni percepite.
Allarmi e flussi di lavoro automatizzati
Routine batte l'azionismo: analizzo il log quotidianamente o settimanalmente, a seconda del traffico e della frequenza di rilascio. Un piccolo script conta le nuove visite negli ultimi minuti e mi invia un'e-mail se il valore di soglia aumenta. Genero anche rapporti periodici su pt-query-digest e tengo sempre d'occhio la top 10. Seguo più da vicino i giorni di rilascio. Questo mi permette di riconoscere le regressioni prima che gli utenti le notino.
#!/bin/bash
LOG_FILE="/var/log/mysql/slow-query.log"
THRESHOLD=100
RECENT_COUNT=$(awk -v cutoff="$(date -d '5 minuti fa' '+%Y-%m-%dT%H:%M')" '/^# Time:/ { if ($3 >= cutoff) count++ } END { print count+0 }' "$LOG_FILE")
se [ "$RECENT_COUNT" -gt "$THRESHOLD" ]; allora
echo "ALERT: $RECENT_COUNT query lente" | mail -s "MySQL Alert" [email protected]
fi
Trasparenza Creo responsabilità chiare: Chi reagisce ai picchi, chi regola gli indici, chi testa le release. Riassumo i risultati in brevi changelog. In questo modo, ogni membro del team capisce perché è stata apportata una modifica e quali effetti ha avuto. Un processo strutturato fa risparmiare tempo e previene i falsi allarmi.
Immagini di errore e correzioni rapide
Completo Le scansioni delle tabelle generano un carico sproporzionato. Verifico innanzitutto se manca un indice adeguato nella colonna del filtro o se un'espressione blocca l'indice. Elimino i tempi di blocco elevati accorciando le transazioni e uniformando le operazioni concorrenti. Disinnesco i log traboccanti con log_throttle_queries_not_using_indexes e un long_query_time realistico. Misuro immediatamente ogni correzione rispetto ai dati originali, in modo che i successi rimangano visibili.
Immagazzinamento-Riconosco i colli di bottiglia aumentando l'IO-wait e l'alta latenza del disco durante i picchi di query. Riduco quindi le operazioni di scrittura non necessarie, ad esempio aggiornando meno frequentemente i campi non modificabili. Quando le tabelle crescono, pianifico strategie di archiviazione o partizione in modo che i dati caldi rimangano in memoria. Per le istruzioni di amministrazione nei momenti di picco, attivo log_slow_admin_statements per identificare i fattori di costo silenziosi. Le piccole correzioni mirate sono più efficaci delle grandi riorganizzazioni.
Caratteristiche speciali in ambienti gestiti e cloud
hosting gestito o i servizi cloud spesso limitano l'accesso ai file. In questi casi, imposto log_output su TABLE e valuto il log lento direttamente dal database. In MySQL 8.0, uso anche SET PERSIST per impostare in modo permanente le impostazioni senza accedere direttamente a my.cnf. Nei gruppi di parametri cloud (ad esempio per i servizi gestiti), inserisco le stesse variabili e pianifico una finestra di manutenzione per il riavvio.
-- Se consentito: impostazioni persistenti senza riavvio
SET PERSIST slow_query_log = ON;
SET PERSIST long_query_time = 0,5;
SET PERSIST log_output = 'TABLE'; -- Alternativa a FILE per accesso limitato ai file
-- Valutazione con log_output=TABELLA
SELECT start_time, user_host, query_time, lock_time, rows_sent, rows_examined, sql_text
DA mysql.slow_log
ORDINATO PER tempo_di_query DESC
LIMIT 50;
SuggerimentoIn caso di traffico intenso, log_output=FILE può essere più performante, poiché la registrazione delle tabelle genera un overhead aggiuntivo. In ambienti restrittivi, tuttavia, TABLE è spesso l'unica strada da percorrere. In tal caso, imposto limiti più severi (ad esempio, min_examined_row_limit) per mantenere il volume controllabile.
Rotazione, archiviazione e protezione dei dati
Rotazione impedisce ai registri di riempire il disco. Eseguo una rotazione giornaliera o per dimensione, comprimo i vecchi file e mantengo una chiara politica di conservazione (ad esempio, 14 giorni). Dopo la rotazione, attivo un flush dei log in modo che MySQL scriva in modo pulito sul nuovo file. In questo modo l'analisi e il funzionamento rimangono stabili.
# /etc/logrotate.d/mysql-slow
/var/log/mysql/slow-query.log {
giornaliero
rotazione 14
dimensione 100M
comprimere
missingok
notifempty
creare 640 mysql adm
postrotate
test -x /usr/bin/mysqladmin || exit 0
/usr/bin/mysqladmin flush-logs
endscript
}
Protezione dei dati è obbligatorio: i log lenti possono contenere valori di parametri. Limito rigorosamente l'accesso (permessi dei file, gruppi) e verifico se vengono registrati dati sensibili. Se necessario, lavoro con il binding dei parametri nell'applicazione, in modo che nel log non compaia testo in chiaro personalizzato. Per la condivisione in team, preferisco condividere report aggregati piuttosto che log grezzi.
Utilizzare lo schema delle prestazioni e lo schema di sistema
Schema di prestazione fornisce metriche anche senza un registro lento attivato. Attivo i consumatori rilevanti per le dichiarazioni e poi analizzo le viste di sistema. Vantaggio: posso vedere i top digest e la distribuzione della latenza quasi in tempo reale, raggruppati per query simili.
-- Attivare il consumatore per lo storico delle dichiarazioni (per quanto possibile in fase di esecuzione)
AGGIORNARE performance_schema.setup_consumatori
SET ENABLED = 'YES'
DOVE NAME IN ('events_statements_history', 'events_statements_history_long');
-- Una rapida panoramica dei gruppi di query più costosi
SELECT nome_schema, digest_text, count_star,
ROUND(sum_timer_wait/1e12, 3) AS total_s,
ROUND(avg_timer_wait/1e9, 3) COME avg_ms,
ROUND(max_timer_wait/1e9, 3) COME pmax_ms
DA sys.statement_analysis
ORDINATO PER somma_timer_attesa DESC
LIMITE 10;
Combinazione dal log della lentezza (outlier lenti) e dallo schema delle prestazioni (ampiezza, frequenza) mi mostra sia i singoli casi che i fattori di costo sistematici. Confronto entrambe le visualizzazioni con gli schemi di traffico per creare dei to-dos prioritari.
EXPLAIN ANALYZE e Optimiser Trace
SPIEGAZIONE ANALISI (a partire da MySQL 8.0.18) integra le stime con i tempi misurati. Confronto le stime delle righe con i valori reali e scopro gli errori di valutazione dell'ottimizzatore. Nel caso di piani contraddittori, analizzo la traccia dell'ottimizzatore per capire perché un indice non è stato selezionato.
-- Piano con valori misurati
SPIEGARE ANALIZZARE
SELEZIONARE o.id, o.created_at
DA ORDINI o
JOIN clienti c ON c.id = o.customer_id
DOVE c.country = 'DE' AND o.status = 'paid'
ORDINAMENTO PER o.created_at DESC
LIMIT 50;
-- Tracciare le decisioni dell'ottimizzatore
SET optimizer_trace="enabled=on";
SELECT ...; -- query da analizzare
SELECT TRACE FROM information_schema.OPTIMIZER_TRACE\G
SET optimiser_trace="enabled=off";
RisultatoSe le stime sono molto lontane, aggiorno le statistiche (ANALYZE TABLE), aggiungo istogrammi o rimodello indici/query in modo che la selettività abbia effetto in anticipo.
Riscrivere gli schemi che funzionano quasi sempre
OPPURE all'UNIONE DI TUTTILe condizioni OR multiple su colonne diverse spesso impediscono l'uso dell'indice. Le separo in due query selettive e unisco i risultati se è possibile escludere i duplicati.
-- Prima:
SELECT * FROM t WHERE a = ? O b = ?
-- Meglio:
(SELECT * FROM t WHERE a = ?)
UNIONE TUTTI
(SELECT * FROM t WHERE b = ? AND a ?);
PaginazioneOFFSET/LIMIT diventa costoso con l'aumentare di OFFSET. Passo alla paginazione a tasti e utilizzo una chiave di ordinamento adeguata (idealmente indicizzata e monotona).
-- Costoso:
SELECT id, title FROM posts ORDER BY created_at DESC LIMIT 50 OFFSET 5000;
-- Meglio (keyset):
SELEZIONARE id, titolo
dai messaggi
DOVE created_at < :cursor
ORDINATO PER data di creazione DESC
LIMITE 50;
Indici compositi: Conteggio dell'ordine. Ordino le colonne nell'indice in base alla selettività e al modello di query (prima il filtro WHERE, poi l'ordinamento delle colonne). L'obiettivo è un indice di copertura che eviti fileort e ricerche di tabelle.
Indici funzionali e generati in MySQL 8
Espressioni in WHERE/JOIN spesso bloccano gli indici. In MySQL 8.0, indicizzo in modo specifico le espressioni o lavoro con le colonne generate per creare la sargabilità. Questo è particolarmente utile per i CAST per i metavalori numerici o i campi JSON.
-- Esempio: ordinamento numerico su un campo di testo
ALTER TABLE product ADD COLUMN price_num DECIMAL(10,2)
GENERATO SEMPRE COME [CAST(prezzo COME DECIMALE(10,2)]) MEMORIZZATO;
CREARE INDICE idx_prodotto_prezzo_num SU prodotto(prezzo_num);
-- Query senza CAST e con indice
SELEZIONARE * DA PRODOTTO
DOVE prezzo_num è compreso tra 10 e 50
ORDINARE PER prezzo_num;
PraticaVerifico se il nuovo indice funziona davvero (EXPLAIN) e misuro l'effetto nel log lento. Le colonne generate aiutano anche a filtrare in modo efficiente i prefissi o le varianti normalizzate (LOWER(email)).
Un approccio più mirato ai modelli di CMS/negozio
Meta tabelle (ad esempio wp_postmeta) traggono vantaggio dagli indici combinati su (post_id, meta_key) o (meta_key, meta_value). Per i filtri frequenti su meta_valore_numerico, utilizzo le colonne generate come sopra invece di eseguire il CAST in ogni query. Velocizzo le pagine di ricerca scartando le ridondanze (denormalizzazione leggera) e rendendo l'accesso in lettura facile da indicizzare.
-- Tipico di WordPress: accesso rapido ai meta-dati di un post
CREARE INDICE idx_postmeta_postid_metakey SU wp_postmeta(post_id, meta_key);
CREARE INDICE idx_postmeta_postid_metakey_metavalue SU wp_postmeta(meta_key, meta_value(100));
Cassa-Ottimizzo i percorsi per ottenere tempi di blocco minimi: transazioni brevi, solo le righe necessarie e indici esattamente per le condizioni WHERE utilizzate. Per i report, pianifico l'aggregazione asincrona (tabelle intermedie) in modo da non rallentare i flussi degli utenti.
Limiti del registro lento e metriche supplementari
Molte query piccole e veloci non si notano nel log lento, ma contribuiscono al carico. Pertanto, tengo traccia anche del throughput (query/sec), del 95°/99° percentile e della percentuale di query senza indice. Con gli strumenti di Performance Schema o APM, riconosco i pattern N+1, che poi risolvo in modo specifico con join, processi di caricamento batch o caching.
Campionamento è utile quando i log diventano troppo grandi. Aumentare leggermente long_query_time o impostare min_examined_row_limit per includere solo le query rilevanti. Importante: annotare sempre le modifiche in modo che le serie temporali rimangano confrontabili.
Metodo di lavoro: Dai risultati al miglioramento sostenibile
Linea di base Primo: salvo un rapporto precedente (finestra temporale, traffico, configurazione). Poi ottimizzo una famiglia di query dopo l'altra e confronto finestre temporali identiche. Ogni correzione viene documentata nel repository (cosa? perché? valore misurato prima/dopo?). In questo modo, i successi rimangono tracciabili e a prova di regressione.
# Procedura approssimativa (esempio)
1) pt-query-digest --since '7d' slow-query.log > baseline.txt
2) Selezionare i primi 3 digest di query (per tempo totale)
3) EXPLAIN/EXPLAIN ANALYZE, elaborare proposte di indicizzazione e riscrittura
4) Generazione di dati di test, simulazione del profilo di carico
5) Rollout con monitoraggio (limiti più severi per 48 ore)
6) Rapporto di confronto: pt-query-digest --since '48h' > after.txt
7) Documentare i risultati, pianificare la prossima tranche
Stabilità del piano Monitoro l'ottimizzatore nel tempo: se i piani cambiano (nuove versioni, statistiche modificate), controllo gli istogrammi, ANALYZE TABLE e il panorama degli indici. Imposto i suggerimenti solo in modo selettivo e documentato, per non bloccare l'ottimizzatore in modo permanente.
Riepilogo in passi chiari
Inizio significa: attivare il log, impostare valori limite ragionevoli, raccogliere la prima settimana di dati. Poi faccio un riepilogo con pt-query-digest, stabilisco una priorità in base al tempo totale e alla varianza e seleziono i driver migliori. Ottimizzo le query con EXPLAIN, le condizioni di sargable e gli indici adatti e controllo il blocco con transazioni più brevi. Sul lato server, imposto buffer, tabelle temporanee e strategie di flush in modo appropriato. Infine, automatizzo gli allarmi e ripeto il ciclo regolarmente: in questo modo il database rimane veloce anche quando il traffico e i volumi di dati crescono.
