Condivisione della tecnologia

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

1. Parliamo in dettaglio dei passaggi per eseguire un'istruzione MySQL.

I passaggi affinché il livello Server esegua SQL in sequenza sono:

Richiesta client -> Connettore (verifica l'identità dell'utente e concede le autorizzazioni) Query cache (restituisce direttamente se la cache esiste, esegue operazioni successive in caso contrario) Analizzatore (esegue analisi lessicale e analisi della sintassi di SQL) Ottimizzatore (esegue principalmente l'esecuzione del metodo di ottimizzazione SQL per selezionare il piano di esecuzione ottimale) Esecutore (durante l'esecuzione, controllerà prima se l'utente dispone dei permessi di esecuzione, quindi utilizzerà l'interfaccia fornita da questo motore) -> Vai al livello del motore per ottenere la restituzione dei dati (se la cache delle query è attivata, memorizzerà nella cache i risultati delle query)

2.Pool buffer

Buffer Pool è una parte importante del motore di archiviazione InnoDB nel database MySQL. Viene utilizzato principalmente per memorizzare nella cache i dati delle tabelle e indicizzare i dati per ridurre le operazioni di I/O del disco e migliorare l'efficienza di elaborazione del database. Quella che segue è un'analisi dettagliata del Buffer Pool:

1. Concetti di base

• definizione: Buffer Pool è un'area di memoria nel motore di archiviazione InnoDB, utilizzata per memorizzare nella cache le pagine di dati e le pagine di indice sul disco per ridurre l'accesso diretto al disco.

• effetto: Migliora la velocità di accesso ai dati e riduce i costi di I/O del disco attraverso il meccanismo di caching.

• composizione : Il pool di buffer è costituito da pagine di dati memorizzate nella cache (Pagina) e dai corrispondenti blocchi di controllo. Il blocco di controllo memorizza le informazioni sui metadati della pagina cache, come il tablespace a cui appartiene, il numero della pagina dati, l'indirizzo della pagina cache nel buffer pool, ecc.

2. Dimensioni e configurazione

• dimensione predefinita: La dimensione predefinita del buffer pool in MySQL è solitamente 128 MB (ma tieni presente che versioni diverse di MySQL o configurazioni diverse possono far sì che la dimensione predefinita sia diversa).

• Parametri di configurazione:passaggioinnodb_buffer_pool_sizeI parametri possono configurare la dimensione del buffer pool. Di solito si consiglia di impostarlo sul 60%-80% della memoria di sistema.

• allocazione della memoria: Il pool di buffer è uno spazio di memoria continuo Quando MySQL viene eseguito per un periodo di tempo, in questo spazio di memoria ci saranno sia pagine di cache libere che pagine di cache utilizzate.

3. Tipi e gestione delle pagine dati

• tipo

  : Le pagine dati nel Buffer Pool possono essere divise in tre tipi in base al loro stato: Pagina libera, Pagina pulita e Pagina sporca.

  • Pagine libere: memorizza nella cache le pagine che non vengono utilizzate.

  • Pagina pulita: una pagina cache che è stata utilizzata ma i dati non sono stati modificati.

  • Pagina sporca: una pagina cache che è stata utilizzata e i dati sono stati modificati e i relativi dati non sono coerenti con i dati sul disco.

• maneggio

  : InnoDB gestisce queste pagine della cache attraverso tre strutture di elenchi collegati:

  • Elenco collegato libero: gestisce le pagine libere e registra le informazioni sul blocco di controllo delle pagine cache libere.

  • Elenco collegato LRU: gestisce pagine pulite e pagine sporche, utilizza un algoritmo LRU migliorato ed è diviso in aree giovani e aree vecchie per ottimizzare il tasso di successo della cache.

  • Elenco collegato a flush: gestisce le pagine sporche che devono essere scaricate su disco, ordinate per ora di modifica.

4. Meccanismo di funzionamento

• accesso ai dati : Quando è necessario accedere a una pagina di dati, InnoDB verifica innanzitutto se la pagina è già nel buffer pool. Se esiste già, la pagina viene utilizzata direttamente; se non esiste, la pagina viene letta dal disco nel Buffer Pool e l'elenco collegato corrispondente viene aggiornato.

• Aggiornamento dati: quando una pagina di dati viene modificata, la pagina verrà contrassegnata come pagina sporca e potrebbe essere aggiunta all'elenco collegato Flush in attesa che il thread in background la scarichi sul disco.

• sfratto dalla cache: Quando lo spazio del buffer pool è insufficiente, la pagina cache utilizzata meno di recente verrà eliminata in base all'algoritmo LRU.

5. Ottimizzazione e precauzioni

• Imposta la dimensione in modo appropriato: Impostazioni ragionevoli basate sulla memoria di sistema e sulle condizioni di caricamento del databaseinnodb_buffer_pool_sizeparametro.

• Monitorare e regolare: monitorare regolarmente gli indicatori di utilizzo e di prestazione del buffer pool e apportare le modifiche necessarie.

• Evita la scansione completa della tabella: la scansione completa della tabella causerà il caricamento di un gran numero di pagine di dati nel pool di buffer, riducendo la percentuale di riscontri nella cache.

Riassumendo, Buffer Pool è uno dei componenti chiave del motore di archiviazione InnoDB nel database MySQL Attraverso una configurazione e una gestione ragionevoli, le prestazioni e l'efficienza del database possono essere notevolmente migliorate.

3.Processo MySQL

Il processo MySQL prevede molteplici collegamenti, a partire dalla connessione tra il client e il server MySQL, fino all'esecuzione, ottimizzazione, lettura dei dati e restituzione dei risultati delle istruzioni SQL. Quella che segue è una panoramica dettagliata del processo MySQL:

1. Connessione e autenticazione

1. Connettore (gestione connessione):

   • Quando un client (come un'applicazione o uno strumento da riga di comando) richiede una connessione a un server MySQL, il connettore MySQL è responsabile della gestione di queste richieste di connessione.

   • Il connettore verifica l'identità e le autorizzazioni del client, che in genere include il controllo della corrispondenza del nome utente e della password.

   • Se la verifica ha esito positivo, il connettore allocherà un thread (o sessione) al client per le successive operazioni SQL.

2. Elaborazione delle query

1. Query Cache (Query Cache, nota: questo modulo è stato rimosso in MySQL 8.0):

   • Per le query SELECT, MySQL controlla innanzitutto se la stessa query e i relativi risultati esistono nella cache delle query.

   • Se presente, MySQL restituirà direttamente i risultati nella cache, evitando così di eseguire l'operazione di query vera e propria.

   • Tuttavia, poiché la memorizzazione nella cache delle query può causare incoerenze nei dati (ad esempio, i dati memorizzati nella cache potrebbero essere stati modificati da altre transazioni), la funzione di memorizzazione nella cache delle query è stata rimossa in MySQL 8.0.

2. Analizzatore:

   • L'istruzione SQL inviata dal client viene prima inviata al parser.

   • Il compito del parser è analizzare l'istruzione SQL, verificare se la sua sintassi è corretta e convertirla in una struttura dati interna (come un albero di analisi o un albero di sintassi).

   • Se è presente un errore di sintassi nell'istruzione SQL, il parser restituirà le informazioni sull'errore al client.

3. Preprocessore:

   • In alcune versioni di MySQL o in alcuni scenari specifici, potrebbe essere presente una fase di preprocessore.

   • Il preprocessore è principalmente responsabile dell'ulteriore elaborazione delle istruzioni SQL, come verificare se la tabella o il campo esiste, espandere * nell'istruzione SELECT a tutte le colonne della tabella, ecc.

4. Ottimizzatore:

   • L'ottimizzatore è responsabile della valutazione dei diversi piani di esecuzione per le istruzioni SQL e della selezione del piano di esecuzione ottimale.

   • L'ottimizzatore considera una serie di fattori, come gli indici disponibili, l'efficienza del metodo di join, il costo della query, ecc.

   • L'ottimizzatore può migliorare in modo significativo le prestazioni delle query tramite operazioni quali l'utilizzo di indici, il riordino delle query o l'unione delle query.

5. Esecutore:

   • L'esecutore esegue le operazioni di query effettive in base al piano di esecuzione generato dall'ottimizzatore.

   • L'esecutore chiamerà l'interfaccia del motore di archiviazione (come InnoDB) per leggere i dati nella tabella dati ed eseguire operazioni come ordinamento, aggregazione e filtraggio.

   • Infine, l'esecutore restituisce i risultati della query al client.

3. Archiviazione e recupero dei dati

• Motore di archiviazione:

  • MySQL supporta più motori di archiviazione e ciascun motore di archiviazione dispone di metodi specifici di archiviazione e recupero dei dati.

  • InnoDB è uno dei motori di archiviazione predefiniti di MySQL e supporta funzionalità di database avanzate come l'elaborazione delle transazioni, il blocco a livello di riga e le chiavi esterne.

  • Quando l'esecutore chiama l'interfaccia del motore di archiviazione, il motore di archiviazione è responsabile della lettura dei dati dal disco o della scrittura dei dati sul disco.

• Riserva di buffer:

  • Il motore di archiviazione InnoDB utilizza il pool di buffer per memorizzare nella cache i dati delle tabelle e indicizzare i dati per ridurre l'accesso diretto al disco.

  • Le pagine di dati nel pool di buffer vengono gestite in base alla frequenza di accesso e allo stato di modifica per migliorare il tasso di riscontro della cache e le prestazioni delle query.

4. Elaborazione delle transazioni

• Transazione:

  • MySQL supporta l'elaborazione delle transazioni, consentendo il commit o il rollback di più operazioni nel loro insieme.

  • Durante l'esecuzione della transazione, MySQL registrerà le informazioni di registro necessarie (come il registro di ripristino e il registro di annullamento) per garantire l'integrità e la coerenza dei dati.

  • Se l'esecuzione della transazione ha esito positivo, tutte le modifiche verranno salvate in modo permanente nel database; se l'esecuzione della transazione fallisce, è possibile utilizzare il registro di annullamento per eseguire un'operazione di rollback e ripristinare i dati allo stato prima dell'avvio della transazione.

5. Riepilogo

Il processo di MySQL prevede la connessione e l'autenticazione, l'elaborazione delle query, l'archiviazione e il recupero dei dati e l'elaborazione delle transazioni. Ottimizzando ogni passaggio di questi collegamenti, le prestazioni e l'affidabilità del database MySQL possono essere notevolmente migliorate. Allo stesso tempo, comprendere il processo di esecuzione di MySQL aiuterà anche a comprendere meglio il suo meccanismo di funzionamento interno, progettando e ottimizzando così meglio il database.

4.Pool di connessioni MySQL

Il pool di connessioni di MySQL è una tecnologia utilizzata per gestire e riutilizzare le connessioni di database. È progettata per migliorare le prestazioni e l'efficienza delle operazioni di database, soprattutto in ambienti ad alta concorrenza. Di seguito è riportata una spiegazione dettagliata sul pool di connessioni MySQL:

1. Concetto

Il pool di connessioni MySQL stabilisce un numero sufficiente di connessioni al database all'avvio del programma e gestisce queste connessioni in modo uniforme per formare un pool di connessioni. Quando il programma deve accedere al database, richiederà dinamicamente una connessione dal pool di connessioni e restituirà la connessione al pool di connessioni dopo l'uso, invece di ricreare e chiudere la connessione per ogni operazione.

2. Perché utilizzare il pool di connessioni?

1. Ridurre il consumo di risorse : La creazione e la chiusura di una connessione al database è un processo relativamente dispendioso in termini di tempo, che coinvolge l'handshake a tre vie e l'onda a quattro vie della connessione TCP, nonché il processo di autenticazione del database. Attraverso il pooling delle connessioni, le connessioni esistenti possono essere riutilizzate per ridurre questi costi generali.

2. Migliorare la prestazione : in uno scenario ad alta concorrenza, se viene creata una nuova connessione al database per ogni richiesta, le prestazioni del server diminuiranno in modo significativo. L'utilizzo di un pool di connessioni può migliorare significativamente la velocità di risposta e la velocità effettiva del database.

3. Evitare perdite di connessione : Senza utilizzare un pool di connessioni, se si verifica un'eccezione quando il programma chiude la connessione, potrebbe causare perdite di connessione, ovvero la connessione non viene chiusa correttamente e occupa le risorse di sistema. Il pool di connessioni può evitare questa situazione attraverso il meccanismo di riciclaggio del timeout.

3. Principio di funzionamento del pool di connessioni

1. inizializzazione: All'avvio del programma, il pool di connessioni creerà un certo numero di connessioni al database in base alla configurazione e inserirà queste connessioni nel pool di connessioni per il backup.

2. Richiedi la connessione : Quando il programma deve accedere al database, richiederà una connessione dal pool di connessioni. Se è presente una connessione inattiva nel pool di connessioni, verrà restituita direttamente al programma per l'utilizzo; se non è presente alcuna connessione inattiva, attenderà per un certo periodo di tempo in base alla configurazione o restituirà un errore.

3. Usa la connessione: Il programma utilizza la connessione richiesta per eseguire operazioni sul database.

4. collegamento di ritorno : Al termine dell'operazione, il programma restituisce la connessione al pool di connessioni. Il pool di connessioni eseguirà alcuni controlli sulla connessione. Se la connessione è ancora valida, verrà reinserita nel pool di connessioni; se la connessione è scaduta, verrà chiusa e rimossa dal pool di connessioni.

5. Chiudere il pool di connessioni: Al termine del programma, tutte le connessioni nel pool di connessioni verranno chiuse e le risorse di sistema occupate verranno rilasciate.

4. Fornitore del pool di connessioni

Esistono molti provider di pool di connessioni MySQL sul mercato, tra i quali i più popolari sono:

• DBCP : Si tratta di un'implementazione del pool di connessioni open source nell'ambito del progetto Apache ed è il pool di connessioni fornito con Tomcat. È più veloce di altri pool di connessioni, ma potrebbe non essere sufficientemente stabile.

• C3P0 : è un pool di connessioni JDBC open source, che implementa l'origine dati e l'associazione JNDI e supporta lo standard JDBC3 e l'estensione standard JDBC2. Il tasso di C3P0 è relativamente lento ma molto stabile.

• druido (Druid): è un pool di connessioni open source fornito da Alibaba. Combina i vantaggi di DBCP e C3P0 e fornisce potenti funzioni di monitoraggio ed espansione. Druid è attualmente uno dei pool di connessioni MySQL più comunemente utilizzati.

5. Configurazione del pool di connessioni

La configurazione del pool di connessioni solitamente include i seguenti aspetti:

• Numero massimo di connessioni: il numero massimo di connessioni che il pool di connessioni può gestire.

• Numero minimo di connessioni: il numero iniziale di connessioni create all'avvio del pool di connessioni.

• Ottieni il timeout della connessione: il tempo massimo di attesa quando si ottiene una connessione dal pool di connessioni.

• Verifica della connessione: verificare la validità della connessione prima di ottenere la connessione o quando si ripristina la connessione.

• Strategia di riciclaggio delle connessioni: ricicla le connessioni in base al tempo di inattività e al tempo di utilizzo.

6. La relazione tra pool di connessioni e pool di thread

Il pool di connessioni e il pool di thread sono due diverse tecnologie di pooling delle risorse, ma esiste una certa relazione tra loro. Il pool di thread viene utilizzato principalmente per gestire le risorse di thread, mentre il pool di connessioni viene utilizzato per gestire le risorse di connessione al database. Quando un thread nel pool di thread deve eseguire un'operazione sul database, richiederà una connessione dal pool di connessioni; una volta completata l'operazione, la connessione verrà restituita al pool di connessioni; Questa relazione aiuta a ottenere un utilizzo efficiente delle risorse e una gestione semplificata.

Per riassumere, il pool di connessioni MySQL è un'importante tecnologia di gestione delle connessioni al database. Fornisce un forte supporto per le operazioni del database riutilizzando le connessioni, riducendo il consumo di risorse e migliorando le prestazioni. Nelle applicazioni reali, è possibile selezionare il provider del pool di connessione e i parametri di configurazione appropriati in base alle esigenze e agli scenari specifici del progetto.

Le domande dell'intervista relative ai log MySQL possono coprire molti aspetti, tra cui il tipo, il ruolo, la configurazione, l'ottimizzazione dei log e l'applicazione dei log nel ripristino dei dati, nella replica dei dati, ecc. Di seguito sono riportate alcune domande comuni nelle interviste relative ai log MySQL e le relative risposte dettagliate:

5. Quali sono i log comuni in MySQL? Qual è la loro rispettiva funzione?

I log più comuni in MySQL includono quanto segue:

• Registro degli errori : registra le informazioni sugli errori quando il server MySQL viene avviato, eseguito o arrestato, nonché eventuali informazioni sugli errori critici. Questo aiuta a diagnosticare il problema.

• Registro delle query (registro generale) : registra ogni richiesta e risposta del client ricevuta dal server MySQL, comprese le attività di accesso dell'utente, le istruzioni SQL eseguite, ecc. In genere utilizzato per il controllo o il debug.

• Registro delle query lento : Registra le istruzioni SQL il cui tempo di esecuzione supera la soglia, nonché il tempo di esecuzione, le tabelle a cui si accede, gli indici utilizzati e altre informazioni di queste istruzioni. Utilizzato per l'ottimizzazione delle prestazioni e l'ottimizzazione delle query.

• Log binario (Binlog in breve): registra tutte le istruzioni che modificano i dati del database (escluse istruzioni come SELECT e SHOW), utilizzate principalmente per la replica e il ripristino dei dati.

• Ripristina registro: Nel motore di archiviazione InnoDB, viene utilizzato per garantire la durabilità delle transazioni. Anche se si verifica un arresto anomalo del sistema, i dati possono essere recuperati tramite i registri di ripetizione.

• Annulla registro: Nel motore di archiviazione InnoDB, viene utilizzato per registrare lo stato dei dati prima dell'inizio della transazione, in modo che quando la transazione fallisce o viene ripristinata, i dati possono essere ripristinati allo stato prima dell'inizio della transazione.

• Registro di inoltro: Nell'architettura di replica MySQL, il log di inoltro sul server slave viene utilizzato per archiviare il contenuto del log binario ricevuto dal server master.

6. Come abilitare e configurare il registro delle query lente?

Il registro delle query lente può essere aperto e configurato tramite il file di configurazione MySQL (come my.cnf o my.ini), oppure può essere impostato dinamicamente tramite comandi SQL.

• Metodo del file di configurazione:

  • Aggiungi o modifica i seguenti parametri nel file di configurazione MySQL:

    [mysqld]  
    slow_query_log = 1  
    slow_query_log_file = /path/to/your/slow-query.log  
    long_query_time = 2

    In,

    slow_query_log

    Utilizzato per abilitare i registri delle query lente,

    slow_query_log_file

    Specificare il percorso del file di registro delle query lente,

    long_query_time

    Imposta il tempo di esecuzione delle istruzioni SQL che supera il numero di secondi da registrare nel registro delle query lente.

  • Dopo aver modificato il file di configurazione, è necessario riavviare il servizio MySQL.

• Modalità comando SQL:

  • Il registro delle query lente può essere abilitato dinamicamente tramite comandi SQL, maslow_query_log_fileElong_query_timePotrebbe essere necessario impostare i parametri tramite un file di configurazione, poiché le impostazioni dinamiche potrebbero non essere supportate o potrebbero non funzionare.

  • Abilita registro query lente:

    sql复制代码
    ​
    SET GLOBAL slow_query_log = 'ON';

  • Si noti che il registro delle query lente aperto dinamicamente utilizzando i comandi SQL potrebbe non essere più valido dopo il riavvio del sistema, pertanto si consiglia di impostarlo tramite il file di configurazione.

7. Quanti formati esistono per il log binario (Binlog)? Qual è la differenza tra loro?

I log binari (Binlog) hanno tre formati:

• DICHIARAZIONE : replica basata su istruzioni SQL (replica basata su istruzioni, SBR). In questo formato, MySQL registrerà le istruzioni SQL eseguite nel binlog. Il suo vantaggio è che il volume del log è piccolo, ma potrebbe riscontrare alcuni problemi di replica, come funzioni, trigger, procedure memorizzate, ecc. che potrebbero causare incoerenze nei dati master-slave.

• RIGA : replica basata su righe (RBR). In questo formato, MySQL registrerà le modifiche ai dati delle righe modificate. Ha il vantaggio di evitare alcuni problemi di replica, ma il volume del registro può essere grande.

• MISTO : replica su base mista (MBR). MySQL sceglierà automaticamente di utilizzare il formato STATEMENT o ROW a seconda della situazione. La modalità mista è la modalità predefinita ed è progettata per combinare il meglio di entrambi i mondi.

8. In che modo Redo Log garantisce la durabilità delle transazioni?

Redo Log garantisce la durabilità delle transazioni nel motore di archiviazione InnoDB nei seguenti modi:

• Quando viene inviata una transazione, il motore InnoDB memorizza innanzitutto nella cache il registro di ripristino della transazione nel buffer del registro di ripristino in memoria e allo stesso tempo aggiorna la pagina di dati corrispondente in memoria.

• Quindi, al momento opportuno, scrivere il registro di ripristino nel buffer del registro di ripristino nel file di registro di ripristino sul disco. Questo processo è asincrono, ma i tempi e la frequenza della spazzolatura del disco possono essere controllati configurando i parametri.

• Se si verifica un arresto anomalo del sistema, il motore InnoDB controllerà il file di registro di ripristino all'avvio e ripristinerà le modifiche apportate dalla transazione inviata più recentemente in base ai record in esso contenuti, garantendo così la durabilità dei dati.

9. Descrivi brevemente come visualizzare ed eliminare i file di registro in MySQL.

Visualizza i file di registro：

• registro degli errori: Di solito questo può essere fatto guardando il file di configurazione MySQLlog_errorparametro per specificare il percorso del file per individuare il file di registro degli errori e utilizzare un editor di testo o uno strumento da riga di comando cometail、catecc.) per visualizzarne il contenuto.