Sfruttare al massimo le prestazioni di MySQL: ottimizzare le query
I database svolgono un ruolo fondamentale nelle prestazioni di qualsiasi applicazione. Il database MySQL non fa eccezione. È quindi importante comprendere i vari modi in cui l'ottimizzazione, la progettazione e la configurazione dei database possono aiutare a migliorare le prestazioni dell'applicazione. Ecco alcuni modi in cui MySQL può essere ottimizzato per ottenere prestazioni eccellenti.
Articoli correlati
Panoramica
Ottimizzare le query
L'ottimizzazione delle prestazioni del database inizia dall'applicazione. Il modo in cui l'applicazione traduce i requisiti aziendali in query del database incide in modo significativo sulla complessità e sull'efficienza dell'applicazione. La misura reale delle prestazioni è l'efficienza con cui ogni istanza di database contribuisce alle esigenze aziendali.
Progettare lo schema
Il modo in cui le entità e le relazioni sono definite in un database relazionale determina quanto sarà semplice o complessa la risposta a una query di database. Anche il modo in cui vengono definiti la chiave primaria e gli indici secondari svolge un ruolo importante.
Configurare il server
La configurazione del server è responsabile dell'ottimizzazione e della massimizzazione dell'utilizzo delle risorse di sistema. Le risorse di sistema sono le CPU (Core Processing Unit), la memoria della macchina fisica o virtuale (VM), il sistema di archiviazione sottostante e la rete.
Regolare il server in modo dinamico
Continuo monitoraggio, perfezionamento e ottimizzazione delle prestazioni per adattare i carichi di lavoro del database alle dinamiche reali.
Questo articolo è incentrato sull'ottimizzazione delle query. Gli altri argomenti verranno trattati negli articoli successivi.
Spesso iniziamo il processo di ottimizzazione delle query esaminando la query nel database. Un modo migliore è iniziare con una valutazione dell'efficienza con cui il requisito aziendale è stato convertito in una query di database. L'interpretazione e l'elaborazione di una esigenza aziendale in una query determina la portata dei costi.
Progettare le query
Il primo passaggio consiste nell'assegnare una priorità alle query da ottimizzare:
- Identifica le domande del database che richiedono il tempo di risposta migliore
- Identifica le domande del database che vengono eseguite di frequente
Queste due categorie devono essere ottimizzate durante la progettazione dello schema del database.
Contenere la durata delle transazioni
Una transazione è un'unità logica in cui tutte le istruzioni contenute devono eseguire il commit completo o il rollback. La transazione è la funzionalità che fornisce atomicità, coerenza, isolamento e durabilità (ACID) per MySQL.
In InnoDB, il motore di archiviazione per MySQL, tutte le attività degli utenti avvengono all'interno di una transazione. Per impostazione predefinita, è abilitata la modalità di commit automatico, il che significa che ogni istruzione SQL forma una singola transazione a sé stante. Per eseguire una transazione con più istruzioni quando è abilitato il parametro autocommit, avvia la transazione in modo esplicito con START_TRANSACTION o BEGIN e terminala con COMMIT o ROLLBACK. Se la modalità autocommit è disabilitata, la sessione ha sempre una transazione aperta fino a quando non viene completato un COMMIT o un ROLLBACK e ne inizia uno nuovo.
La best practice relativa alle transazioni consiste nel mantenerle il più brevi possibile. Ciò è dovuto al fatto che le transazioni lunghe presentano diversi svantaggi, come descritto più avanti in questo articolo.
Svantaggi delle transazioni lunghe
- Contese di blocco prolungate, che causano query più lente
e potenziali errori delle query
- Il blocco a livello di riga InnoDB viene mantenuto per tutta la durata della transazione
- Aumenta potenzialmente le attese del blocco, il timeout del blocco e i deadlock, che rendono la query più lenta o con esito negativo
- Prestazioni ridotte del server a causa di un numero elevato di log
di annullamento
- A causa del controllo della contemporaneità multi-versione (MVCC) di InnoDB, le versioni precedenti delle righe modificate vengono archiviate nei log di annullamento per operazioni di lettura e rollback coerenti. Con il livello di isolamento in lettura continua predefinito, i log di annullamento non vengono eliminati definitivamente fino al completamento delle transazioni già avviate. Pertanto, una transazione a lunga esecuzione accumula log di annullamento. Questa operazione può essere osservata e monitorata tramite l'elenco della cronologia nella funzione della riga di comando SHOW Engine INNODB STATUS.
- Quando l'elenco della cronologia supera milioni di voci, le prestazioni del server subiscono ripercussioni a causa della contesa mutex sui segmenti di rollback, dell'aumento del volume di lettura dei log di annullamento e del maggior tempo di trasferimento dell'elenco collegato dei log di annullamento. Crea inoltre più lavoro per l'eliminazione definitiva dei thread.
- Maggiore utilizzo del disco
- Aumento dei log di annullamento archiviati su disco, nello spazio delle tabelle di sistema o di annullamento
- Tempo di arresto prolungato
- Durante il normale arresto, verrà eseguito il rollback delle transazioni in corso. Il tempo di rollback è spesso più lungo del tempo impiegato per arrivare al punto. Pertanto, l'arresto del server potrebbe richiedere molto tempo per il rollback.
- Tempo di ripristino dagli arresti anomali prolungato
- Durante il ripristino in seguito a un arresto anomalo, InnoDB ripete le transazioni dall'ultimo checkpoint e sblocca le transazioni non impegnate. In caso di una transazione lunga, il passaggio corrispondente richiederà più tempo.
Considerazione sulle transazioni son una sola query
- Query SELECT
- I blocchi di riga non vengono mantenuti
- Potrebbero causare l'accumulo di log non annullati
- Consulta la sezione sull'ottimizzazione delle query di seguito
- Query UPDATE/INSERT/DELETE
- La query in batch funziona meglio di molte modifiche apportate a una singola riga
- Suddividi e limita il tempo di esecuzione in batch a un paio di secondi
Considerazione sulle transazioni con più istruzioni
- Valuta la possibilità di separare le query SELECT
- Se è presente una logica dell'applicazione tra le query del database, valuta la possibilità di suddividere la transazione
- Stima il numero di blocchi di righe previsto per ogni istruzione
- Valuta l'ordine di esecuzione per ridurre al minimo il blocco delle righe
- Cerca opportunità per ridurre le dimensioni delle transazioni
Acquisire le query
Le query possono essere acquisite sul lato applicazione o sul lato database.
Lato applicazione
È buona prassi di sviluppo registrare le query di database e il tempo di esecuzione delle query. Il logging laterale delle applicazioni consente di valutare facilmente l'efficacia e l'efficienza delle query nel contesto aziendale. Ad esempio, gli utenti possono registrare il tempo di risposta di ogni query o il tempo di risposta dei log per determinate funzionalità. Questo è anche un modo semplice per ottenere un tempo di esecuzione totale per le transazioni con più istruzioni.
Inoltre, il tempo di risposta alle query misurato dal logging lato applicazione è una misurazione end-to-end, che include il tempo di rete. Integra il tempo di esecuzione delle query registrato dal database e semplifica l'identificazione della presenza di problemi relativi alla rete o al database.
Lato database
Query Insights di MySQL di Cloud SQL
Lo strumento Query Insights di Cloud SQL consente l'acquisizione, il monitoraggio e la diagnosi delle query.
Query Insights consente di trovare facilmente le query principali in base al tempo e alla frequenza di esecuzione.
Lo strumento offre opzioni di filtro come intervallo di tempo, database, account utente e indirizzo client. Include grafici per mostrare l'utilizzo della CPU e la suddivisione su attese IO e di blocco. La tabella "Query e tag principali" elenca le query principali per tempo di esecuzione con le query che vengono normalizzate. Oltre al tempo di esecuzione, include statistiche su "righe medie scansionate" e "righe medie restituite" che forniscono insight sull'efficienza delle query.
Consulta la documentazione per vedere tutto ciò che offre lo strumento e come attivarlo.
Utilizzare performance_schema
Su Cloud SQL per MySQL, la funzionalità performance_schema è abilitata per impostazione predefinita per MySQL 8.0.26 e versioni successive con oltre 15 GB di memoria. L'abilitazione o la disabilitazione della funzionalità richiede il riavvio dell'istanza.
Quando performance_schema=ON, gli strumenti di istruzione query sono abilitati per impostazione predefinita. La tabella sys.statement_analysis fornisce statistiche aggregate per le query normalizzate. Risponde a domande come:
- Quali query eseguono la scansione completa della tabella?
- full_scan/exec_count: per scoprire se le query eseguono di frequente la scansione completa della tabella, che è spesso una pratica inefficiente
- Quali query vengono eseguite lentamente?
- avg_latency: tempo medio di esecuzione della query
- Quali query sono inefficienti?
- rows_examined_avg/rows_sent_avg: per le query di lettura. Il rapporto ideale è 1. Maggiore è il rapporto, più la query è inefficiente.
- rows_examined_avg/rows_affected_avg: per le query di scrittura. Il rapporto ideale è 1. Maggiore è il rapporto, più la query è inefficiente.
- Quali query utilizzano le tabelle temporanee, che devono essere convertite in tabelle temporanee su disco?
- tmp_disk_tables/tmp_tables: per verificare se il valore tmp_table_size/max_heap_table_size è sufficiente
- Quali query utilizzano Filesort?
- rows_sorted/exec_count, sort_merge_passes/exec_count: per identificare le query con grandi quantità di ordinamento. Potrebbero utilizzare un campo sort_buffer_size più grande
Se utilizzi MySQL Workbench, è dotato di report sugli schemi delle prestazioni basati sulla visualizzazione del sistema. Il report include una sezione relativa alle istruzioni SQL ad alto costo, che fornisce insight sulle prestazioni delle query.
Utilizza lo slow log + strumenti
Lo slow log acquisisce tutte le query in esecuzione più a lungo di long_query_time. Registra inoltre i tempi di esecuzione delle query, il tempo di blocco, le righe di dati esaminate e le righe di dati inviate. Le statistiche aggiuntive sull'esecuzione lo rendono un candidato preferito per l'analisi delle query del database rispetto all'utilizzo del log generale.
È consigliabile attivare gli slow log. Generalmente, il valore long_query_time deve essere mantenuto a una soglia ragionevole per acquisire le query che intendi esaminare e ottimizzare.
log_output=FILE
slow_query_log=ON
long_query_time=2
Di tanto in tanto, è opportuno impostare long_query_time=0 in modo da acquisire tutte le query per un breve periodo e ottenere una panoramica del volume e delle prestazioni delle query.
Esistono strumenti, come mysqldumpslow e pt-query-digest, che estraggono le firme delle query e generano un report per mostrare le statistiche sulle query.
Esistono altri strumenti di monitoraggio di terze parti che generano report sulle statistiche delle query, ad esempio Monitoraggio e gestione Percona, Monitoraggio delle prestazioni del database di SolarWinds (in precedenza VividCortex) e altri ancora.
Ottimizzare le query
Dopo aver acquisito le query nelle transazioni, il passaggio successivo è ottimizzarle.
SPIEGAZIONE: cosa cercare
Il comando EXPLAIN fornisce il piano di esecuzione della query e, a partire dalla versione 8.0.18, il comando EXPLAIN ANALYZE esegue un'istruzione e produce un output EXPLAIN insieme ai tempi dell'esecuzione.
Query Insight di MySQL fornisce un accesso utile al piano EXPLAIN.
Che cosa cerchiamo nell'output?
- Il campo "righe" mostra la quantità di righe da leggere
- L'IO è la parte che richiede più tempo. Se una query deve leggere una grande quantità di dati, è probabile che sia lenta. Per avere un'idea approssimativa, moltiplica le "righe" tra le tabelle unite. Nell'esempio precedente, questo corrisponde a 858 * 23523. Per leggere 23523 righe da t2 per ciascuna delle 858 righe da t1 non sembra ottimale. Detto ciò, l'ottimizzazione significherebbe ridurre la quantità di accesso ai dati da t2 per ogni iterazione.
- Il campo "tipo" descrive il tipo di join della tabella
- Il tipo "index" corrisponde all'indicizzazione dell'indice. Se l'indice soddisfa tutti i dati richiesti dalla tabella, il campo Extra mostrerà "Utilizza indice".
- Il tipo "intervallo" significa che non solo viene utilizzato un indice, ma viene anche fornita una condizione di intervallo per limitare la scansione dei dati.
- Per le tabelle successive in ordine di unione, il tipo "eq_ref" indica che viene letta una riga da questa tabella per ogni combinazione di righe delle tabelle precedenti, che è la soluzione più efficiente.
- Il tipo "ref" significa che la corrispondenza dell'indice è 1:m anziché 1:1. Da questa tabella verrà letta più di una riga per ogni combinazione di righe delle tabelle precedenti.
- Il tipo da evitare è "ALL", che significa che viene eseguita una scansione completa della tabella per ogni combinazione di righe dalle tabelle precedenti.
- Il campo "key" mostra l'indice effettivo in uso.
- La selezione dell'indice da utilizzare si basa sulla cardinalità dell'indice che potrebbe non essere aggiornata. Pertanto, è importante verificare che venga utilizzato l'indice più selettivo.
- Il campo "key_len" indica la lunghezza della chiave in byte.
- Nel caso di un indice a più colonne, key_len suggerisce la parte dell'indice utilizzata. Ad esempio, se un indice ha (col1, col2, col3) e la condizione della query è "col1 = n e col2 come '%string%'", viene usato solo col1 per il filtro degli indici. Se la query potesse essere modificata in "col1 = n e col2 come 'stringa%", allora entrambe (col1, col2) verrebbero utilizzate per il filtro dell'indice. Questa piccola modifica potrebbe fare una notevole differenza nelle prestazioni delle query.
- Il campo "Extra" contiene ulteriori informazioni sul piano di query
- "Using temporary" indica la creazione di una tabella temporanea interna che potrebbe generare una tabella temporanea sul disco
- "Using filesort" significa che l'ordinamento non è riuscito a utilizzare alcun indice e richiede un buffer di ordinamento e potenzialmente file disco temporanei
- "Using index" indica che tutti i dati richiesti da questa tabella sono contenuti nell'indice; non è necessario leggere le righe di dati
Profilare le query
Le variabili di stato della sessione possono essere utilizzate per ottenere i dettagli sull'esecuzione della query.
Innanzitutto, cancella le variabili di sessione, quindi esegui la query ed esamina i contatori. Ad esempio, lo stato Handler_* mostra il pattern di accesso ai dati e la quantità di righe. Created_* viene visualizzato se viene creata una tabella temporanea e/o una tabella temporanea sul disco. Sort_* mostra il numero di pass di unione per l'ordinamento e il numero di righe ordinate. Ulteriori variabili di sessione sono illustrate nella documentazione.
L'istruzione SHOW PROFILE fornisce il tempo di esecuzione della query in base alla fase di esecuzione, un'informazione che potrebbe essere utile.
Ottimizzare il piano di esecuzione delle query
Una volta compreso il piano di esecuzione della query, esistono diversi modi per influenzarlo e ottimizzarlo.
- Aggiungere o aggiornare la definizione dell'indice
- Per un filtro migliore, con meno accesso ai dati
- Per l'ordinamento, evita il filesort
- Aggiornare le statistiche dell'indice se l'opzione non è attiva
- ANALYZE TABLE <tbl>;
- Quindi ricontrolla l'output del piano EXPLAIN
- Usare i suggerimenti per l'indice
- Per suggerire o forzare l'utilizzo di un determinato indice per i filtri, unisci o ordina per/raggruppa per
- Utilizzare STRAIGHT_JOIN per definire l'ordine di join della tabella
- Utilizzare i suggerimenti dello strumento per l'ottimizzazione
Ottimizzare l'esecuzione per la sessione
Per ottimizzare la configurazione del server per alcune query, consigliamo vivamente di utilizzare le variabili a livello di sessione anziché modificare il valore globale che interessa tutte le sessioni.
I valori più utilizzati per le sessioni sono:
Riepilogo
In breve, per l'ottimizzazione delle query, abbiamo parlato di tre aspetti:
- Prendere decisioni consapevoli quando si scrivono le query. Queste decisioni sono il fattore trainante per le prestazioni delle query, la velocità effettiva complessiva del server e le prestazioni del server.
- Monitorare i dati dell'esecuzione della query sia sul lato applicazione sia sul lato database. Il logging lato applicazione è importante. Potrebbe essere configurato in base agli interessi aziendali e rispecchiare le operazioni aziendali.
- Infine, esistono diversi strumenti che aiutano a comprendere il piano di esecuzione delle query, i costi associati ai diversi passaggi e i modi per ottimizzare le query.
Prodotti e servizi correlati
Google Cloud offre un database MySQL gestito che è stato creato per soddisfare le tue esigenze aziendali, dal ritiro del data center on-premise all'esecuzione di applicazioni SaaS, fino alla migrazione dei sistemi aziendali principali.
