le mie informazioni di contatto
Posta[email protected]
2024-07-12
한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina
Il contenuto dei log MySQL è molto importante e viene spesso chiesto durante le interviste. Allo stesso tempo, padroneggiare le conoscenze relative ai log ci aiuterà anche a comprendere i principi alla base di MySQL e ad individuare e risolvere i problemi quando necessario.
I tipi di registro comuni in MySQL includono principalmente le seguenti categorie (per il motore di archiviazione InnoDB):
Il log binario (binlog) e il log delle transazioni (redo log e undo log) sono più importanti e richiedono la nostra attenzione.
Il registro delle query lente registra tutte le istruzioni delle query il cui tempo di esecuzione supera long_query_time (il valore predefinito è 10 s, solitamente impostato su 1 s). Viene spesso utilizzato per risolvere problemi di query SQL lente (il tempo di esecuzione SQL è troppo lungo).
Trovare l'SQL lento è il primo passo per ottimizzare le prestazioni delle istruzioni SQL. Quindi utilizzare il comando EXPLAIN per analizzare l'SQL lento e ottenere informazioni sul piano di esecuzione.
Puoi utilizzare le variabili show come il comando "slow_query_log" per verificare se il registro delle query lente è attivato. È disattivato per impostazione predefinita.
Può essere attivato da SET GLOBAL slow_query_log=ON
Il parametro long_query_time definisce quanto tempo impiega una query prima che possa essere definita come query lenta. Il valore predefinito è 10 s. Puoi visualizzarlo tramite il comando SHOW VARIABLES LIKE'%long_query_time%';
Può anche essere modificato: imposta global long_query_time = 12
Nei progetti reali, il registro delle query lente può essere relativamente grande ed è scomodo analizzarlo direttamente. Possiamo utilizzare lo strumento ufficiale di analisi e ottimizzazione delle query lente MySQL. mysqldumpslento . Il mio blog è anche semplicemente collegato allo strumento mysqldumpslow:
[MySQL] strumento mysqldumpslow: riepilogo dei file di registro delle query lente - Blog CSDN
C'è una variabile in MySQL che registra il numero corrente di istruzioni di query lente. Puoi usare show global status come'%slo
w_queries%'; comando da visualizzare.
MySQL ci fornisceSPIEGAREcomando per ottenere informazioni sul piano di esecuzione.
Il piano di esecuzione si riferisce al metodo di esecuzione specifico di un'istruzione SQL dopo essere stata ottimizzata dall'ottimizzatore di query MySQL. I piani di esecuzione vengono generalmente utilizzati in scenari quali l'analisi e l'ottimizzazione delle prestazioni SQL. Attraverso i risultati di EXPLAIN, puoi apprendere informazioni come la sequenza di query della tabella di dati, il tipo di operazione dell'operazione di query di dati, quali indici possono essere colpiti, quali indici verranno effettivamente colpiti, quante righe di record in ciascun dato vengono interrogate le tabelle e altre informazioni. Nello specifico, possiamo ottimizzare SQL attraverso alcuni metodi comuni di seguito:
1. Evitare di utilizzare SELECT *
SELECT * consuma più CPU.
SELEZIONA *I campi inutili aumentano il consumo di risorse della larghezza di banda della rete e il tempo di trasmissione dei dati, in particolare i campi di grandi dimensioni (come varchar,
blob、testo)。
SELECT * non può utilizzare l'ottimizzatore MySQL per coprire l'ottimizzazione dell'indice (la strategia di "indice di copertura" basata sull'ottimizzatore MySQL è estremamente veloce ed efficiente ed è un metodo di ottimizzazione delle query altamente raccomandato nel settore)
SELECT <elenco campi> può ridurre l'impatto delle modifiche alla struttura della tabella.
2. Ottimizzazione dell'impaginazione
Il paging ordinario richiede un tempo relativamente breve quando la quantità di dati è piccola.
Se la quantità di dati diventa grande, raggiungendo milioni o addirittura decine di milioni, il paging ordinario richiederà molto tempo.
Come ottimizzare? È possibile modificare l'istruzione SQL precedente in una sottoquery.
Per prima cosa interroghiamo il valore della chiave primaria corrispondente al primo parametro del limite, quindi filtriamo e limitiamo in base a questo valore della chiave primaria, in modo che l'efficienza sia più rapida. Tuttavia, questo metodo funziona solo se gli ID sono in ordine positivo.
Tuttavia, il risultato della sottoquery genererà una nuova tabella, che influirà sulle prestazioni. È opportuno evitare un uso estensivo delle sottoquery. Inoltre, questo metodo è applicabile solo quando l'ID è in sequenza positiva. In scenari di paging complessi, è spesso necessario filtrare gli ID che soddisfano le condizioni tramite condizioni di filtraggio. Al momento, gli ID sono discreti e discontinui.
3. Fai meno join
Manuale di sviluppo di Alibaba:
Puoi leggere la discussione su Zhihu:
https://www.zhihu.com/question/68258877
https://www.zhihu.com/question/682588774. Si consiglia di non utilizzare chiavi esterne e cascate
Manuale di sviluppo Java di Alibaba:
5. Scegli il tipo di campo appropriato
6. Prova a utilizzare UNION ALL invece di UNION
UNION inserirà tutti i dati dei due set di risultati in una tabella temporanea e quindi eseguirà l'operazione di deduplicazione, che richiede più tempo e consuma più risorse della CPU.
UNION ALL non deduplicherà più il set di risultati e i dati ottenuti contengono elementi duplicati.
Tuttavia, se nello scenario aziendale attuale non sono consentiti dati duplicati, è comunque possibile utilizzare UNION.
7. Operazioni batch
Per gli aggiornamenti dei dati nel database, se è possibile utilizzare operazioni batch, utilizzarle il più possibile per ridurre il numero di richieste del database e migliorare le prestazioni.
8. Utilizzare gli indici correttamente
Ci sono molti contenuti in questa sezione, che verranno introdotti in un blog separato in seguito.
binlog (il log binario è un file di log binario) registra principalmente tutte le operazioni che sono state modificate sul database MSQL (tutte le istruzioni DDL e DML eseguite dal database), comprese le modifiche alla struttura della tabella (CREATE, ALTER, DROP TABLE.), i dati della tabella modifiche ( INSERT.UPDATE, DELETE..), ma non include SELECT, SHOW e altre operazioni che non causano modifiche al database.
È possibile utilizzare il comando show Binary Logs per visualizzare un elenco di tutti i log binari:
Esistono 3 tipi di metodi di registrazione binaria:
Rispetto alla modalità Riga, il file di registro in modalità istruzione è più piccolo, anche la pressione di I/O del disco è inferiore e le prestazioni sono migliori. Tuttavia, la sua precisione è peggiore rispetto alla modalità Riga.
Prima di MySQL 5.1.5, il formato di binlog era solo STATEMENT. 5.1.5 ha iniziato a supportare binlog in formato ROW. A partire dalla versione 5.1.8, MySQL ha iniziato a supportare binlog in formato MIXED. Prima di MySQL 5.7.7, la modalità Statement veniva utilizzata per impostazione predefinita. MySQL5.7.7 utilizza la modalità Riga per impostazione predefinita.
Puoi utilizzare variabili show come'%binlog format%'; per visualizzare il formato utilizzato da binlog
Lo scenario di applicazione principale di binlog è la replica master-slave, master-master e master-slave sono tutti inseparabili da binlog su cui è necessario fare affidamento per sincronizzare i dati e garantirne la coerenza.
Il principio della replica master-slave è mostrato nella figura seguente:
1. La libreria principale scrive le modifiche ai dati nel database su binlog
2. Collegare la libreria slave alla libreria principale
3. La libreria slave creerà un thread I0 per richiedere il binlog aggiornato dalla libreria principale.
4. La libreria principale creerà un thread di dump binlog per inviare il binlog e il thread I/0 nella libreria slave è responsabile della ricezione. 5. Il thread I/0 della libreria slave scriverà il binlog ricevuto nel relè tronco d'albero.
6. Leggere il registro di inoltro dal thread SQL della libreria e sincronizzare i dati localmente (ovvero eseguire nuovamente l'SQL)
Per il motore di archiviazione InnoDB, durante l'esecuzione di una transazione, il log verrà prima scritto nella binlogcache. Solo quando la transazione viene inviata, il log nella binlogcache verrà persistente nel file binlog sul disco. La scrittura in memoria è più veloce e ciò avviene anche per ragioni di efficienza.
Poiché il binlog di una transazione non può essere suddiviso, non importa quanto sia grande la transazione, deve essere scritto una volta, quindi il sistema allocherà un blocco di memoria a ciascun thread come cache binlog. Possiamo controllare la dimensione binlogcache di un singolo thread tramite il parametro binlog_cache_size Se il contenuto di archiviazione supera questo parametro, deve essere temporaneamente archiviato sul disco (swap).
Quindi, quando viene scaricato il binlog sul disco? Puoi controllare i tempi di scaricamento del biglog tramite il parametro sync_binlog. L'intervallo di valori è 0-N e il valore predefinito è 0:
·0: Nessun requisito obbligatorio, il sistema deciderà quando scrivere sul disco.
·1: Ogni volta che viene inviata una transazione, il binlog deve essere scritto sul disco:
·N: Binlog verrà scritto sul disco ogni N transazioni.Rischio di perdita
Prima di MySQL5.7, il valore predefinito di sync_binlog era 0. Dopo MySQL5.7, il valore predefinito di sync_binlog è 1. In genere, non è consigliabile impostare il valore di sync_binlog su 0. Se i requisiti di prestazioni sono relativamente elevati o si verifica un collo di bottiglia dell'I/O del disco, il valore di sync_binlog può essere aumentato in modo appropriato. Tuttavia, ciò aumenterà il rischio di perdita di dati.
Quando si verificano le tre situazioni seguenti, MySQL rigenererà un nuovo file di registro e il numero di serie del file verrà incrementato.
Sappiamo che il motore di archiviazione InnoD8 gestisce lo spazio di archiviazione in unità di pagine. I dati che inseriamo in MySQL alla fine esistono nella pagina. Per essere precisi, è il tipo di pagina di dati. Per ridurre il sovraccarico di I/O del disco, esiste anche un'area denominata Buffer Pool, che esiste in memoria. Quando la pagina corrispondente ai nostri dati non esiste nel Buffer Pool, MSQL prima memorizzerà nella cache la pagina sul disco nel Buffer Pool, in modo che in seguito possiamo gestire direttamente la pagina nel Buffer Pool, il che migliora notevolmente le prestazioni di lettura e scrittura .
Dopo il commit di una transazione, le nostre modifiche alla pagina corrispondente nel Buffer Pool potrebbero non essere persistenti su disco. A questo punto, se MySQL si arresta improvvisamente in modo anomalo, le modifiche apportate a questa transazione scompariranno direttamente?
Ovviamente no, se così fosse violerebbe ovviamente la durabilità della transazione.
Il motore MySQLInnoDB utilizza il registro di ripristino per garantire la durabilità delle transazioni. La cosa principale che fa il redo log è registrare le modifiche della pagina, ad esempio quanti byte sono stati modificati con un determinato offset su una determinata pagina e qual è lo specifico contenuto modificato. Ogni record nel registro di ripristino contiene il numero del tablespace, il numero della pagina di dati, l'offset, i dati modificati specifici e può anche registrare la lunghezza dei dati modificati (a seconda del tipo di registro di ripristino).
Una volta confermata la transazione, scaricheremo il redo log sul disco secondo la strategia di flushing. In questo modo, anche se MySQL si blocca, i dati che non sono stati scritti sul disco possono essere recuperati dopo il riavvio, garantendo così la durabilità. della transazione. In altre parole, il redo log offre funzionalità di ripristino in caso di arresto anomalo di MySQL.
Redo Log registra tutte le operazioni di modifica nel database. Quando il database esegue operazioni di scrittura (INSERT, UPDATE, DELETE), queste operazioni verranno prima registrate nel Redo Log e poi applicate al file di dati. In questo modo, anche se il sistema fallisce prima che l'operazione di modifica dei dati sia stata completamente scritta sul disco, Redo Log può garantire che i dati non vadano persi. Durante il ripristino, il database rifarà queste operazioni di modifica non completate dal Redo Log per garantire la coerenza dei dati.
Redo Log aiuta il database a ripristinare uno stato coerente dopo un arresto anomalo del sistema o un'interruzione di corrente imprevista. Durante il processo di ripristino, il database controllerà i record nel registro di ripristino e riapplicherà tutte le modifiche dei dati inviate ma non persistenti ai file di dati per ripristinare i dati.
Per migliorare le prestazioni delle operazioni di scrittura, i database spesso utilizzano meccanismi di memorizzazione nella cache (come i pool di buffer) per archiviare temporaneamente le operazioni di modifica in memoria invece di scriverle immediatamente su disco. L'esistenza del Redo Log rende possibile questo meccanismo di memorizzazione nella cache, poiché finché è garantita la persistenza del Redo Log, non vi è alcun rischio di perdita di dati anche se i dati nella cache non sono stati scritti sul disco.
Quando si effettua una transazione, il registro di ripristino nel buffer di registro verrà scaricato sul disco. È possibile utilizzare innodb_flush_log_at
commit del controllo dei parametri. Dobbiamo prestare attenzione all'impostazione della corretta policy di flush innodb_flush_log_at_trx_commit A seconda della strategia di flush configurata in MySQL, potrebbero verificarsi piccoli problemi di perdita di dati dopo che MySQL si è inattivo.
innodb_flush_log_at_trx_commit È un parametro di configurazione importante nel motore di archiviazione MySQL InnoDB. Determina le strategie di flush (flush) e write (scrittura) del log quando viene inviata una transazione, influenzando così la durabilità e le prestazioni dei dati. Ha tre valori, vale a dire 0, 1 e 2. Ogni valore rappresenta una diversa strategia di spazzolatura.
innodb_flush_log_at_trx_commit = 0
innodb_flush_log_at_trx_commit = 1
innodb_flush_log_at_trx_commit = 2
Riepilogo della strategia del pennello
1. Il registro di ripristino viene scritto nel buffer del registro ma non è stato ancora scritto nella cache della pagina. In questo momento, il database si arresta in modo anomalo e si verifica una perdita di dati (questa perdita di dati può verificarsi quando il valore della politica di svuotamento innodb_flush log_at trx_commit è). 0);
2. Il registro di ripristino è stato scritto nella cache della pagina ma non è stato ancora scritto sul disco. Il sistema operativo si bloccherà e potrebbe verificarsi una perdita di dati (questa perdita di dati può verificarsi quando il valore della policy di svuotamento innodb2 flush log_at trx_commit è). 2).
Il registro di annullamento (registro di rollback) è un registro utilizzato nel sistema di database per registrare le operazioni di modifica dei dati. Registra le operazioni inverse (ovvero le operazioni di annullamento) di tutte le operazioni di modifica sui dati durante l'esecuzione della transazione. Il registro di annullamento svolge un ruolo chiave nel rollback delle transazioni. Attraverso il registro di annullamento, i dati possono essere ripristinati allo stato precedente all'avvio della transazione, garantendo così l'atomicità della transazione.
L'atomicità di una transazione significa che tutte le operazioni della transazione vengono eseguite tutte oppure nessuna viene eseguita. Undo Log garantisce l'atomicità delle transazioni attraverso i seguenti meccanismi:
Registra le operazioni di annullamento Durante l'esecuzione della transazione, qualsiasi operazione di modifica ai dati registrerà la corrispondente operazione di annullamento nell'Undo Log prima della modifica effettiva. Ad esempio, se una transazione aggiorna il valore di una riga di record, il vecchio valore verrà registrato nel registro annullamenti prima dell'aggiornamento.
Transazione di ripristino Se la transazione fallisce per qualche motivo (come un errore o un rollback esplicito), il sistema del database legge il registro di annullamento e ripristina i dati allo stato prima dell'inizio della transazione in base all'operazione di annullamento registrata. In questo modo è possibile garantire che la transazione fallita non abbia alcun impatto sul database, garantendo così l'atomicità della transazione.
Citazione:
Si dedica alla ricerca tecnologica da più di 30 anni ed è esperto in vari linguaggi come Java, Linux, Javascript, php, css, ecc. Ha dato numerosi contributi nel campo dell'open source stazione di documentazione per gli sviluppatori per condividere alcuni problemi nello sviluppo della tecnologia per riferimento futuro
Posta[email protected]