Condivisione della tecnologia

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

---

1. Infrastruttura: come viene eseguita un'istruzione di query SQL?

Panoramica

In generale, MySQL può essere diviso in due livelli

• Livello server
  Copre la maggior parte delle funzioni principali del servizio MySQL
  • Connettore
  • Interroga la cache
  • Analizzatore
  • ottimizzatore
  • Attuatore
  • Tutte le funzioni integrate (come data, ora, funzioni matematiche e crittografiche, ecc.)
  • Funzionalità tra motori di archiviazione
    • procedura memorizzata
    • grilletto
    • visualizzazione
    • ……
• livello del motore di archiviazione
  Architettura plug-in, responsabile dell'archiviazione e del recupero dei dati
  • Innodb
  • Il mio ISAM
  • Memoria

Connettore

mysql -h$ip -P$port -u$user -p
1

Il mysql nel comando di connessione è uno strumento client utilizzato per stabilire una connessione con il server.Dopo aver completato il classico handshake TCP, il connettore
Sta per iniziare l'autenticazione della tua identità. A questo punto verranno utilizzati il ​​nome utente e la password che hai inserito.

• Se il nome utente o la password non sono corretti, riceverai un errore "Accesso negato per l'utente" e quindi il programma client
  Termina l'esecuzione.
• Se l'autenticazione del nome utente e della password viene superata, il connettore lo faràTabella delle autorizzazioni Scopri quali autorizzazioni hai lì.Successivamente, a questo proposito
  La logica di giudizio delle autorizzazioni dipenderà dalle autorizzazioni lette in questo momento.

Se il client rimane inattivo per troppo tempo, il connettore lo disconnetterà automaticamente. Questo tempo è controllato dal parametro wait_timeout e il valore predefinito è 8 ore.

Se il client invia nuovamente una richiesta dopo che la connessione è stata disconnessa, riceverà un promemoria di errore: Lost connection to MySQL server during query . Se vuoi continuare in questo momento, devi riconnetterti e quindi eseguire la richiesta.

Nel database, una connessione lunga significa che dopo che la connessione è riuscita, se il client continua a effettuare richieste, verrà utilizzata sempre la stessa connessione. Una connessione breve significa che la connessione viene disconnessa dopo l'esecuzione di alcune query e ne viene ristabilita una nuova per la query successiva.

Il processo di creazione di una connessione è solitamente complicato, quindi ti suggerisco di provare a ridurre al minimo le azioni di creazione di una connessione durante l'uso, ovvero di provare a utilizzare connessioni lunghe.

Ma dopo aver utilizzato tutte le connessioni lunghe, potresti scoprire che a volte la memoria occupata da MySQL aumenta molto rapidamente. Questo è perchéLa memoria utilizzata temporaneamente da MySQL durante l'esecuzione viene gestita nell'oggetto connessione. . Queste risorse verranno rilasciate quando la connessione viene disconnessa.Quindi seL'accumulo di connessioni lunghe può comportare un utilizzo eccessivo della memoria., è stato ucciso con la forza dal sistema (OOM). A giudicare dal fenomeno, MySQL è stato riavviato in modo anomalo.

Come risolvere questo problema? Puoi considerare le due opzioni seguenti.

• Disconnettere periodicamente le connessioni lunghe . Dopo averlo utilizzato per un periodo di tempo o dopo che il programma determina che è stata eseguita una query di grandi dimensioni che occupa memoria, la connessione viene disconnessa, quindi la query viene richiesta e quindi ricollegata.
• Se stai utilizzando MySQL 5.7 o versione successiva, puoi eseguire mysql_reset_connection per reinizializzare le risorse di connessione. Questo processo non richiede la riconnessione e la verifica dei permessi, ma ripristinerà la connessione allo stato in cui è stata appena creata.

Interroga la cache

Dopo che MySQL ha ricevuto una richiesta di query, andrà prima alla cache delle query per vedere se questa istruzione è stata eseguita in precedenza. Le istruzioni eseguite in precedenza e i relativi risultati possono essere memorizzati nella cache direttamente in memoria sotto forma di coppie chiave-valore. La chiave è l'istruzione della query e il valore è il risultato della query. Se la tua query riesce a trovare la chiave direttamente in questa cache, il valore verrà restituito direttamente al client.

Se l'istruzione non è nella cache delle query, la fase di esecuzione continua. Al termine dell'esecuzione, i risultati dell'esecuzione verranno archiviati nella cache delle query. Puoi vedere che se la query raggiunge la cache, MySQL può restituire direttamente il risultato senza eseguire operazioni complesse successive, il che è molto efficiente.

Ma la maggior parte delle volte lo faròSi consiglia di non utilizzare la memorizzazione nella cache delle query ,Perché? Perché la memorizzazione nella cache delle query spesso fa più male che bene.

La cache delle query viene invalidata molto frequentemente finché è presente un aggiornamento in una tabella, tutte le cache delle query su questa tabella verranno cancellate. Quindi è possibile che tu ti sia preso la briga di salvare i risultati e, prima ancora di utilizzarli, siano stati cancellati da un aggiornamento. Per i database con una forte pressione di aggiornamento, il tasso di successo della cache delle query sarà molto basso. A meno che la tua azienda non abbia una tabella statica che verrà aggiornata solo una volta a lungo. Ad esempio, se si tratta di una tabella di configurazione del sistema, la query su questa tabella è adatta per la cache delle query.

Fortunatamente, MySQL fornisce anche questo metodo di "utilizzo su richiesta". È possibile impostare il parametro query_cache_type su DEMAND in modo che la cache delle query non venga utilizzata per le istruzioni SQL predefinite. Per le istruzioni per cui sei sicuro di voler utilizzare la cache delle query, puoi utilizzare SQL_CACHE per specificarla esplicitamente, come la seguente istruzione:

select SQL_CACHE * from T where ID=10；
1

bisogna essere consapevoli è,La versione MySQL 8.0 elimina direttamente l'intera funzione di cache delle query, il che significa che questa funzione non sarà più disponibile a partire dalla versione 8.0.

Analizzatore

Se la cache delle query non viene raggiunta, inizia l'effettiva esecuzione dell'istruzione. Innanzitutto, MySQL deve sapere cosa vuoi fare, quindi deve analizzare l'istruzione SQL.

ottimizzatore

Attuatore

2. Sistema di logging: come viene eseguita un'istruzione SQL di aggiornamento?

registro di ripetizione

Non so se ricordi ancora l'articolo "Kong Yiji". Il direttore dell'hotel ha una lavagna rosa appositamente utilizzata per registrare i dati di credito degli ospiti. Se non ci sono molte persone che pagano a credito, può scrivere sulla lavagna il nome e il conto del cliente. Ma se ci sono troppe persone con conti di credito, ci saranno sempre momenti in cui il fan board non potrà tenerne traccia. In questo momento, il negoziante deve avere un registro specifico per la registrazione dei conti di credito.

Se qualcuno vuole estinguere un credito o estinguere un debito, il negoziante ha generalmente due opzioni:

• Un modo è aprire direttamente il registro e aggiungere o sottrarre il conto credito;
• Un altro approccio èPrima scrivi i conti questa volta sulla lavagna rosa, poi tira fuori i libri contabili dopo l'orario di chiusura e calcolali.。

Quando gli affari vanno a gonfie vele e il bancone è pieno, il negoziante sceglierà sicuramentequest'ultimo , perché la prima operazione è troppo problematica. Innanzitutto, devi trovare il record del conto di credito totale di questa persona. Pensaci, ci sono dozzine di pagine fitte. Per trovare il nome, il negoziante potrebbe dover indossare gli occhiali da lettura e cercare lentamente. Dopo averlo trovato, tirerà fuori l'abaco per calcolare e infine riscriverà il risultato il registro.

L’intero processo è problematico a cui pensare. Al contrario, è più facile scriverlo prima sulla lavagna rosa. Pensateci, se il negoziante non ha l'aiuto della lavagna rosa, deve girare il registro ogni volta che registra i conti, l'efficienza non è insopportabilmente bassa?

Allo stesso modo, questo problema esiste anche in MySQL Se ogni operazione di aggiornamento deve essere scritta sul disco e il disco deve anche trovare il record corrispondente prima dell'aggiornamento, il costo di I/O e di ricerca dell'intero processo sarà molto elevato. Per risolvere questo problema, i progettisti di MySQL hanno utilizzato un'idea simile alla lavagna rosa del negoziante dell'hotel per migliorare l'efficienza dell'aggiornamento.

L'intero processo di cooperazione tra la tavola rosa e il registro è in realtà ciò che viene spesso menzionato in MySQL. WAL tecnologia,WAL Il nome completo èWrite-Ahead Logging, il punto chiave èScrivere prima il registro, quindi scrivere sul disco, cioè scrivi prima la lavagna rosa, e poi scrivi il libro dei conti quando non sei occupato.

Nello specifico, quando è necessario aggiornare un record, il motore InnoDB scriverà prima il record nel registro di ripristino (scheda rosa) e aggiornerà la memoria. A questo punto, l'aggiornamento è completato. Allo stesso tempo, il motore InnoDB aggiornerà il record dell'operazione sul disco al momento opportuno e questo aggiornamento viene spesso eseguito quando il sistema è relativamente inattivo, proprio come fa il negoziante dopo la chiusura.

Se oggi i conti attivi non sono molti, il negoziante può aspettare fino all'ora di chiusura per sistemare la merce. Ma cosa dobbiamo fare se in un determinato giorno ci sono molti conti attivi e la scheda rosa è piena? In quel momento, il negoziante non aveva altra scelta se non quella di mettere da parte il suo lavoro, aggiornare alcuni dei record di credito sulla lavagna rosa nel registro, e poi cancellare questi record dalla lavagna rosa per fare spazio a nuovi conti.

Allo stesso modo, il registro di ripetizione di InnoDB ha una dimensione fissa. Ad esempio, può essere configurato come un insieme di 4 file, ogni file ha una dimensione di 1 GB. Quindi questa "scheda rosa" può registrare un totale di operazioni di 4 GB. Inizia a scrivere dall'inizio, quindi torna all'inizio per scrivere in loop, come mostrato nell'immagine qui sotto.

write pos è la posizione del record corrente. Si sposta all'indietro durante la scrittura. Dopo aver scritto fino alla fine del file n. 3, ritorna all'inizio del file n. 0. Il punto di controllo è la posizione corrente da cancellare e inoltre si sposta in avanti e si ripete. Prima di cancellare il record, il record deve essere aggiornato nel file di dati.

Lo spazio tra write pos e checkpoint è la parte vuota della "lavagna rosa" che può essere utilizzata per registrare nuove operazioni. Se la posizione di scrittura raggiunge il checkpoint, significa che la "scheda rosa" è piena e al momento non è possibile eseguire nuovi aggiornamenti. È necessario interrompere e cancellare prima alcuni record per far avanzare il checkpoint.

Con il redo log, InnoDB può garantire che anche se il database si riavvia in modo anomalo, i record inviati in precedenza non andranno persi. Questa funzionalità viene richiamatacrash-safe。

Per comprendere il concetto di sicurezza in caso di incidente, pensa al nostro precedente esempio di record di credito. Finché il conto di credito è registrato sulla lavagna rosa o scritto sul libro mastro, anche se il negoziante lo dimentica in seguito, ad esempio sospendendo improvvisamente l'attività per qualche giorno, può comunque chiarire il conto di credito attraverso i dati nel libro mastro e bordo rosa dopo la ripresa dell'attività.

registro binologico

Come accennato in precedenza, MySQL nel suo insieme è composto in realtà da due parti: una è il livello server, che svolge principalmente attività a livello funzionale di MySQL; l'altra è il livello motore, responsabile di questioni specifiche relative allo storage;La tavola rosa di cui abbiamo parlato soprail registro di ripetizione è un registro univoco per il motore InnoDB,E Anche il livello Server ha un proprio log, chiamato binlog (log di archivio)。

Penso che ti chiederai: perché ci sono due registri?

Perché all'inizio non esisteva il motore InnoDB in MySQL. Il motore di MySQL è MyISAM, ma MyISAM non dispone di funzionalità anti-crash e i log binlog possono essere utilizzati solo per l'archiviazione. InnoDB è stato introdotto in MySQL sotto forma di plug-in da un'altra società. Poiché basarsi solo su binlog non dispone di funzionalità anti-crash, InnoDB utilizza un altro sistema di registro, ovvero il redo log, per ottenere funzionalità anti-crash.

Questi due log presentano le tre differenze seguenti.

1. Il redo log è unico per il motore InnoDB; il binlog è implementato dal livello server di MySQL e può essere utilizzato da tutti i motori.
2. il registro di ripristino è un registro fisico, registra "quali modifiche sono state apportate su una determinata pagina di dati";binlog è un registro logico, ciò che viene registrato è la logica originale di questa istruzione, ad esempio "aggiungi 1 al campo c della riga con ID=2".
3. il registro di ripetizione è scritto in un ciclo, lo spazio sarà esaurito;binlog può essere scritto in aggiunta . "Aggiungi scrittura" significa che dopo che il file binlog raggiunge una certa dimensione, passerà a quello successivo e non sovrascriverà il registro precedente.

Con una comprensione concettuale di questi due log, diamo un'occhiata ai processi interni dell'esecutore e del motore InnoDB durante l'esecuzione di questa semplice istruzione di aggiornamento.

1. L'esecutore cerca innanzitutto il motore per ottenere la riga ID=2. L'ID è la chiave primaria e il motore utilizza direttamente la ricerca nell'albero per trovare questa riga. Se la pagina di dati in cui si trova la riga con ID=2 è già in memoria, verrà restituita direttamente all'esecutore, altrimenti dovrà essere prima letta in memoria dal disco e poi restituita.
2. L'esecutore ottiene i dati della riga forniti dal motore, aggiunge 1 a questo valore, ad esempio, prima era N, ma ora è N+1, ottiene una nuova riga di dati e quindi chiama l'interfaccia del motore per scriverla nuova riga di dati.
3. Il motore aggiorna questa nuova riga di dati nella memoria e registra l'operazione di aggiornamento nel registro di ripristino in questo momento registro di ripetizione Inpreparare stato. Quindi informa l'esecutore che l'esecuzione è completata e la transazione può essere inoltrata in qualsiasi momento.
4. L'esecutore genera un binlog di questa operazione e inserisce binlog scritto su disco。
5. L'esecutore chiama l'interfaccia della transazione di commit del motore e il motore scrive il file registro di ripetizione Modifica per inviare (commettere), l'aggiornamento è completato.

Qui fornisco il diagramma di flusso di esecuzione di questa istruzione di aggiornamento. La casella luminosa nella figura indica che viene eseguita all'interno di InnoDB e la casella scura indica che viene eseguita nell'esecutore.

processo di esecuzione dell'istruzione di aggiornamento

Potresti aver notato che gli ultimi tre passaggi sembrano un po' "circolari". La scrittura del redo log è divisa in due passaggi: preparazione e commit. Questo è un "commit a due fasi".

impegno in due fasi

Perché è necessaria la "sottomissione in due fasi"?Questo per consentire la differenza tra i due loglogicamente coerente . Per spiegare questo problema, dobbiamo iniziare con la domanda all'inizio dell'articolo: come ripristinare il database allo stato di un secondo qualsiasi entro mezzo mese?

Come abbiamo detto prima, binlog registrerà tutte le operazioni logiche e adotterà la forma di "scrittura in aggiunta". Se il tuo DBA promette che può essere ripristinato entro mezzo mese, il sistema di backup salverà sicuramente tutti i binlog nell'ultima metà del mese e il sistema eseguirà backup regolari dell'intero database. Il "regolare" qui dipende dall'importanza del sistema, che può essere una volta al giorno o una volta alla settimana.

Quando è necessario ripristinare un secondo specifico, ad esempio alle due del pomeriggio di un giorno, si scopre che una tabella è stata eliminata accidentalmente a mezzogiorno ed è necessario recuperare i dati, è possibile eseguire questa operazione:

• Innanzitutto, trova il backup completo più recente, se sei fortunato, potrebbe essere un backup della notte scorsa e ripristina da questo backup al database temporaneo;
• Quindi, a partire dal punto temporale del backup, i binlog di backup vengono estratti in sequenza e riprodotti fino all'ora precedente all'eliminazione accidentale della tabella, a mezzogiorno.
  In questo modo, il tuo database temporaneo sarà lo stesso del database online prima che lo eliminassi accidentalmente. Quindi potrai estrarre i dati della tabella dal database temporaneo e ripristinarli nel database online secondo necessità.

Ok, dopo aver parlato del processo di ripristino dei dati, torniamo indietro e parliamo del motivo per cui il log necessita di un "commit a due fasi". Qui potremmo anche usare la prova per assurdo per spiegare.

Poiché il redo log e il binlog sono due logiche indipendenti, se non viene utilizzato il commit a due fasi, è necessario scrivere prima il redo log e poi il binlog, oppure deve essere adottato l'ordine inverso. Vediamo quali problemi ci sono con questi due metodi.

Utilizzare ancora la precedente istruzione di aggiornamento come esempio. Supponiamo che il valore del campo c nella riga corrente con ID=2 sia 0 e che durante l'esecuzione dell'istruzione update si verifichi un arresto anomalo dopo la scrittura del primo log ma prima della scrittura del secondo. Cosa accadrà?

• Scrivi prima il redo log e poi binlog.
  Supponiamo che il processo MySQL si riavvii in modo anomalo quando viene scritto il redo log ma prima che venga scritto il binlog. Come abbiamo detto prima, dopo aver scritto il registro di ripristino, anche se il sistema si blocca, i dati possono ancora essere ripristinati, quindi il valore di c in questa riga dopo il ripristino è 1. Tuttavia, poiché il binlog si è bloccato prima del completamento, questa istruzione non è stata registrata nel binlog in questo momento. Pertanto, quando verrà eseguito il backup del log in un secondo momento, questa istruzione non verrà inclusa nel binlog salvato. Scoprirai quindi che se è necessario utilizzare questo binlog per ripristinare la libreria temporanea, poiché il binlog di questa istruzione viene perso, la libreria temporanea questa volta non verrà aggiornata. Il valore di c nella riga ripristinata è 0, ovvero uguale al valore della libreria originale diverso.
• Scrivi prima binlog e poi ripeti il ​​log.
  Se si verifica un arresto anomalo dopo la scrittura del binlog, poiché il registro di ripristino non è stato ancora scritto, la transazione non sarà valida dopo il ripristino dall'arresto anomalo, quindi il valore di c in questa riga è 0. Ma il registro "Cambia c da 0 a 1" è stato registrato nel binlog. Pertanto, quando binlog viene utilizzato per il ripristino in un secondo momento, verrà generata un'altra transazione. Il valore di c nella riga ripristinata è 1, che è diverso dal valore nel database originale.
  Si può notare che se non viene utilizzato il "commit a due fasi", lo stato del database potrebbe non essere coerente con lo stato della libreria ripristinata utilizzando il relativo registro.

Potresti dire, questa probabilità è molto bassa Non ci sono situazioni in cui la libreria temporanea deve essere ripristinata in qualsiasi momento?

In realtà no, questo processo non è necessario solo per recuperare i dati dopo un utilizzo errato. Quando è necessario espandere la capacità, ovvero quando è necessario creare più database in standby per aumentare la capacità di lettura del sistema, la pratica comune ora è utilizzare il backup completo e applicare binlog per raggiungere questo obiettivo. Questa "incoerenza" causerà il tuo There is un'incoerenza tra i database master e slave online.

In poche parole, sia il redo log che il binlog possono essere utilizzati per rappresentare lo stato di commit di una transazione eLa sottomissione in due fasi serve a mantenere i due stati logicamente coerenti.。