Login
Accedi

Galleria mappe mentale Lezione pratica MySql 45

Lezione pratica MySql 45

Questa è una mappa mentale di 45 lezioni pratiche su MySql Attraverso l'apprendimento e la pratica continui, puoi utilizzare meglio MySQL per soddisfare le esigenze aziendali e migliorare l'efficienza di archiviazione ed elaborazione dei dati.

Modificato alle 2023-12-21 17:35:18

VisioneCreativa

Lavori recenti Visualizza più lavori>>

暗黑模板-個人求職簡歷模板
個人求職簡歷模板的暗黑配色方案，包括個人簡介、職業規劃、行業經驗、自我評價等多個部分，讓你的簡歷更出彩。使用模板可以極大地提高效率，用戶不需要從頭開始設計結構和內容，只需在模板的基礎上填寫或添加自己的信息即可，這樣可以節省大量的時間和精力，歡迎參考使用!持續分享給大家……
Strategia di saggio sulla gestione del rischio di progetto
Se non sai come scrivere un articolo, sarai nei guai come manager dei sistemi informativi. Una guida passo passo su come scrivere un documento sulla gestione del rischio. Se ne hai bisogno, ritiralo velocemente!
piano pasto
Il programma dietetico formula un programma dietetico scientifico e ragionevole per soddisfare i nutrienti e l'energia richiesti dal corpo, mantenendo così una buona salute e una buona postura.

Lezione pratica MySql 45

VisioneCreativa

Lavori recenti Visualizza più lavori>>

Consigliato per te
Profilo

Dermatologia e Venereologia 007 Eritema, papule e malattia della pelle squamosa
- 1
VisioneCreativa
risoluzione del conflitto
- 1
WSZUS4lF
zuccheri nelle cellule
- 11
WSCoUtCI
tecnologia di rete
- 3
WSZUS4lF
Medicina Interna
- 1
VisioneCreativa
Mappa mentale della crescita permanente
- 1
WSCoUtCI
Introduzione e interpretazione essenziale del tema Crescita Permanente
- 2
WSCoUtCI
Recupero delle informazioni di rete
- 1
WSZUS4lF
Appunti di lettura Crescita permanente.
- 2
WSCoUtCI
Appunti di lettura Crescita permanente.
- 4
WSCoUtCI

Lezione pratica MySql 45

8. Le transazioni sono isolate oppure no?

Il concetto di visualizzazioni in MySql

Uno è la vista. È una tabella virtuale definita con un'istruzione di query Quando viene richiamata, l'istruzione di query viene eseguita e vengono generati i risultati. La sintassi per creare una vista è create view... e il suo metodo di query è lo stesso di una tabella

L'altra è la vista di lettura coerente utilizzata da InnoDB durante l'implementazione di MVCC, utilizzata per supportare l'implementazione dei livelli di isolamento RC (Read Committed) e RR (Repeatable Read).

InnoDB sfrutta la funzionalità "tutti i dati hanno più versioni" (MVCC) per ottenere la capacità di "creare istantanee in pochi secondi".

1. I dati di aggiornamento vengono prima letti e poi scritti e questa lettura può leggere solo il valore corrente, chiamato "lettura corrente" 2.select genererà un'istantanea letta

RC (lettura commessa), RR (lettura ripetibile)

Nel livello di isolamento read-commit, viene generata una vista prima dell'esecuzione di ogni istruzione e la query riconosce solo i dati che sono stati inviati prima dell'avvio dell'istruzione;

Nel livello di isolamento di lettura ripetibile, verrà generata una vista all'avvio della transazione e verrà utilizzata successivamente. Solo quando viene eseguita un'istruzione DML, verrà generata la lettura corrente. La query riconoscerà solo i dati che sono stati inviati in precedenza la transazione è avviata.

10.Perché MySQL a volte sceglie l'indice sbagliato?

motivo

L'ottimizzatore ha analizzato erroneamente il numero di righe da scansionare

Per problemi causati da statistiche dell'indice imprecise, è possibile utilizzare la tabella di analisi per rianalizzare la tabella.

Eccezioni e gestione della selezione dell'indice

Controlla le frasi

selezionare * da mdm_company dove un valore compreso tra 1 e 100 e b tra 1.000 e 50.000 ordinati per b limite 1;

L'ottimizzatore penserà che l'indice b sia stato ordinato, quindi scegliere di utilizzare l'indice b eviterà l'ordinamento e prenderà semplicemente i valori in ordine. Pertanto, anche se il numero di righe scansionate è elevato, sarà giudicato meno costoso .

Utilizzare forza indice per selezionare forzatamente un indice

seleziona * from mdm_company force indice (a) dove a è compreso tra 1 e 100 e b tra 1.000 e 50.000 ordinati per b limite 1;

Supponendo che sia a che b abbiano indici, è necessario eseguire solo la scansione di 100 righe per indicizzare a, quindi spingere verso il basso l'indice b per filtrare i dati.

discordanza:

Se il nome dell'indice viene modificato, è necessario modificare l'SQL.

Modificare l'istruzione per indirizzare MySQL a utilizzare l'indice che desideriamo

Elimina direttamente gli indici utilizzati in modo improprio

12. Perché il mio MySQL "trema"?

Pool di buffer in innoDB Le pagine di memoria nel pool di buffer hanno tre stati

Il primo tipo: inutilizzato

Secondo tipo: pagine pulite che sono state utilizzate e scritte su disco

Il terzo tipo: pagine sporche che sono state utilizzate ma non scritte su disco

Cosa sono le pagine sporche?

Quando la pagina dei dati della memoria non è coerente con la pagina dei dati del disco, chiamiamo questa pagina della memoria una "pagina sporca". Dopo che i dati della memoria sono stati scritti sul disco, i contenuti delle pagine di dati nella memoria e sul disco sono coerenti, il che viene chiamato "pagina pulita"

Quando il database trema, le pagine sporche vengono svuotate. Esistono quattro situazioni in cui il database elimina le pagine sporche.

Situazione uno:

Il registro di ripristino è pieno A questo punto, il sistema interromperà l'operazione di aggiornamento ed eseguirà il registro di ripristino su disco.

Questa situazione è qualcosa che InnoDB dovrebbe cercare di evitare. Perché quando ciò accade, l'intero sistema non può più accettare aggiornamenti e tutti gli aggiornamenti devono essere bloccati. Se guardi il monitoraggio, il numero di aggiornamenti scenderà a 0 in questo momento

Situazione 2:

Il sistema non dispone di memoria sufficiente ed è necessario eliminare la memoria. A questo punto è necessario scrivere sul disco i dati delle pagine sporche.

Situazione tre:

Anche le pagine sporche verranno cancellate quando il sistema è inattivo.

Situazione quattro:

Quando Mysql viene spento normalmente, anche le pagine sporche verranno scaricate.

14.count(*) è molto lento, cosa devo fare?

count() è una funzione aggregata. Il set di risultati restituito viene valutato riga per riga. Se il parametro della funzione count non è NULL, il valore cumulativo viene aumentato di 1, altrimenti non viene aggiunto. Infine restituisci il valore cumulativo

Ordina per conteggio efficienza(campo)<conteggio(id chiave primaria)<conteggio(1)≈conteggio(*)

Implementazione di MySQL

Il motore MyISAM memorizza il numero totale di righe di una tabella sul disco, quindi quando si esegue count(*), restituirà direttamente questo numero, il che è molto efficiente;

Il motore InnoDB è in difficoltà. Quando esegue count(*), deve leggere i dati dal motore riga per riga e quindi accumulare il conteggio.

Perché InnoDB non utilizza Count(*) per contare il numero di record come MyISAM?

Prima di tutto, il motore di archiviazione MyISAM non supporta le transazioni, ma InnoDB supporta le transazioni. InnoDB utilizza il controllo della concorrenza multiversione MVCC. Il numero di record ottenuti da transazioni diverse dovrebbe essere diverso, quindi non è possibile utilizzare record variabili come MyISAM per registrare il numero di record.

Ottimizzazione del numero totale di record nelle statistiche InnoDB

Gli indici non cluster non memorizzano dati. Una pagina IO può ottenere più record, quindi l'ottimizzatore Mysql troverà l'albero più piccolo da attraversare.

Posso utilizzare show tables invece di count(*)?

No, perché sebbene il comando show tables abbia un parametro TABLE_ROWS utilizzato per visualizzare quante righe ha attualmente la tabella, questo comando conta gli indici e sono presenti errori nelle statistiche degli indici.

Il motivo per cui l'inserimento del conteggio in Redis non può garantire che il conteggio sia accurato e coerente con i dati nella tabella MySQL è che i sistemi composti da questi due diversi sistemi di archiviazione non supportano transazioni distribuite e non possono ottenere visualizzazioni accurate e coerenti.

13.Perché la dimensione del file della tabella rimane invariata quando viene eliminata metà dei dati della tabella?

Per evitare la riorganizzazione dell'indice, quando si eliminano i dati, i dati non verranno effettivamente eliminati dal disco, ma lo spazio verrà contrassegnato solo per il riutilizzo. Pertanto, quando esegui il comando delete per eliminare l'intera tabella, tutte le pagine di dati verranno contrassegnate come riutilizzabili, ma il file sul disco non diventerà più piccolo.

Suddivisione della pagina (inserimento fuori ordine)

Esegui il comando alter table A engine=InnoDB per ricostruire la tabella. Questo comando completerà la creazione di tabelle temporanee, trasferirà i dati, scambierà i nomi delle tabelle ed eliminerà le vecchie tabelle. Questo comando bloccherà le istruzioni DML nelle versioni precedenti alla 5.5, ma non dopo la 5.6.

Ovviamente, il passaggio che richiede più tempo è il processo di inserimento dei dati nella tabella temporanea. Se durante questo processo vengono scritti nuovi dati nella tabella A, si verificherà una perdita di dati. Pertanto, non possono esserci aggiornamenti nella Tabella A durante l'intero processo DDL. In altre parole, questo DDL non è online

Fenomeno di unione delle pagine

11.Come aggiungere l'indice al campo stringa?

Come creare un indice per un campo con una lunghezza di caratteri molto lunga, come un indirizzo email

indice del prefisso

I problemi

Ciò aumenterà il numero di righe da sottoporre a scansione, influenzando le prestazioni.

Se la discriminazione non è elevata, il numero di righe da scansionare aumenterà dopo aver abbinato i risultati sull'albero dell'indice È necessario tornare all'indice della chiave primaria per ottenere il record. Se si scopre che non sono uguali, è necessario tornare nuovamente al numero di indice per leggere il record successivo.

Soluzione

Selezionare conta(distinto a sinistra(email,4))come L4, conta(distinto a sinistra(email,5))come L5, conta(distinto a sinistra(email,6))come L6, conta(distinto a sinistra(email,7))come L7, da SUser;

Calcolare la distinzione. Se la distinzione non è inferiore a L*95%, è opportuno stabilire un indice di prefisso.

Influisce sulla copertura dell'indice

seleziona id,email da SUser dove email='zhangssxyz@xxx.com';

Se il campo indice email utilizza la struttura dell'indice dell'intera stringa, è sufficiente eseguire una query sui risultati nell'albero dell'indice dell'email e utilizzare la copertura dell'indice per restituire direttamente il set di risultati. E se il campo emila utilizza un indice di prefisso, è necessario restituire la tabella per determinare il valore del campo email.

Crea direttamente un indice completo, che potrebbe occupare più spazio; Crea un indice di prefisso per risparmiare spazio, ma aumenterà il numero di scansioni delle query e non sarà possibile utilizzare gli indici di copertura; Memorizzare in ordine inverso e poi creare un indice di prefisso per aggirare il problema dell'insufficiente discriminazione dei prefissi della stringa stessa; Crea un indice del campo hash, le prestazioni della query sono stabili e vi è un consumo aggiuntivo di spazio di archiviazione e calcolo. Come il terzo metodo, la scansione dell'intervallo non è supportata.

9. Come scegliere tra indice ordinario e indice unico?

Aggiorna la differenza

Per un indice normale, dopo aver trovato il primo record (5.500) che soddisfa la condizione, è necessario trovare il record successivo finché non si incontra il primo record che non soddisfa la condizione k=5.

Per un indice univoco, poiché l'indice definisce l'unicità, la ricerca verrà interrotta dopo aver trovato il primo record che soddisfa le condizioni.

La differenza di efficienza è trascurabile e può essere ignorata Il motore InnoDB legge una pagina di dati alla volta, ogni pagina è 16KB, se k=5 Sembra che sia l'ultimo record della pagina corrente, quindi è necessaria un'altra operazione di I/O.

Differenze nelle istruzioni di inserimento

Cos'è il buffer di modifica

Quando una pagina di dati deve essere aggiornata, se la pagina di dati è in memoria, verrà aggiornata direttamente. Se la pagina di dati non è ancora in memoria, InooDB memorizzerà nella cache queste operazioni di aggiornamento nel buffer di modifica senza influire sulla coerenza dei dati non è necessario leggere questa pagina di dati dal disco. Quando la query successiva deve accedere a questa pagina di dati, leggere la pagina di dati in memoria, quindi eseguire operazioni correlate a questa pagina nel buffer di modifica. In questo modo è possibile garantire la correttezza della logica dei dati.

Può essere utilizzato solo su indici ordinari

Change Buffer è adatto a scenari con più scrittura e meno lettura, come sistemi di registrazione e sistemi di contabilità. Se viene letto immediatamente dopo la scrittura, il costo di gestione del Change Buffer è troppo elevato e l'unione è costante.

Anche le operazioni del Change Buffer verranno registrate nel registro di ripristino, quindi anche se il database si riavvia in modo anomalo, i dati nel Change Buffer non hanno avuto il tempo di essere eseguiti e i dati non andranno persi.

La differenza tra le operazioni di aggiornamento dell'indice univoco e quelle ordinarie

Se i dati da aggiornare si trovano nella memoria corrente

Gli indici ordinari inseriscono direttamente i dati nella posizione specificata, mentre gli indici univoci devono prima determinare se esiste un conflitto di indici prima di inserire i dati.

Se i dati da aggiornare non sono presenti nella pagina corrente

L'istruzione termina quando l'indice ordinario memorizza il record nel buffer di modifica, ma l'indice univoco deve determinare se esiste un conflitto di indice. È necessario prima leggere la pagina di dati nella memoria, quindi inserire il valore dopo averlo determinato non c'è conflitto.

7. Come ridurre l'impatto dei blocchi di riga sulle prestazioni?

Nelle transazioni InnoDB, i blocchi di riga vengono aggiunti quando necessario, ma non vengono rilasciati immediatamente quando non sono più necessari, ma devono essere rilasciati dopo il commit della transazione.

Se è necessario bloccare più righe nella transazione, è necessario posizionare i blocchi che con maggiore probabilità causano conflitti di blocco e influiscono sulla concorrenza il più indietro possibile (ad esempio, se l'aggiornamento del saldo personale e l'aggiornamento del saldo aziendale vengono eseguiti nella stessa transazione , dovresti eseguire l'aggiornamento per aggiornare il saldo aziendale alla fine, poiché l'impatto dell'aggiunta di un blocco di riga al record di riga della società sarà maggiore rispetto all'aggiunta di un blocco di riga al saldo personale)

Come risolvere lo stallo in InnoDB?

Cause di stallo

Due transazioni occupano le risorse necessarie l'una all'altra

Soluzione

Vai direttamente ad attendere fino al timeout. Questo timeout può essere impostato tramite il parametro innodb_lock_wait_timeout. Il timeout predefinito di InnoDB è 50 secondi

Se si verifica un problema di stallo ed è necessario attendere 50 secondi, l'impatto sull'azienda sarà enorme se si cambia il timeout Se l'impostazione è molto breve, se è necessario bloccare un'altra normale attività, il blocco verrà perso in breve tempo, con ripercussioni sull'attività.

Avvia il rilevamento dei deadlock e, dopo aver scoperto un deadlock, esegui il rollback proattivo di una transazione nella catena di deadlock per consentire ad altre transazioni di continuare l'esecuzione. Imposta il parametro innodb_deadlock_detect su on, indicando che questa logica è attivata La lettura coerente non rileva i deadlock.

Ogni thread appena bloccato deve determinare se la propria aggiunta causerà un deadlock. Questa è un'operazione con una complessità temporale di O(n). Supponendo che siano presenti 1.000 thread simultanei che desiderano aggiornare la stessa riga contemporaneamente, il numero di operazioni di rilevamento dei deadlock è dell'ordine di 1 milione. Anche se il risultato finale del rilevamento è che non vi è alcun deadlock, durante questo periodo verrà consumata una grande quantità di risorse della CPU. Pertanto, vedrai un utilizzo elevato della CPU, ma non molte transazioni al secondo.

Come risolvere i problemi di prestazioni causati da tali aggiornamenti hot row?

Controlla il numero di operazioni simultanee sulla stessa riga

Disattiva il controllo dei deadlock (c'è un problema di sicurezza molto serio e causerà un gran numero di timeout aziendali)

Convertire una modifica a riga singola in una modifica a più righe