2024-07-12
한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina
Der Inhalt von MySQL-Protokollen ist sehr wichtig und wird häufig in Interviews gefragt. Gleichzeitig hilft uns die Beherrschung protokollbezogener Kenntnisse auch dabei, die zugrunde liegenden Prinzipien von MySQL zu verstehen und bei Bedarf Probleme zu beheben und zu lösen.
Zu den gängigen Protokolltypen in MySQL gehören hauptsächlich die folgenden Kategorien (für die InnoDB-Speicher-Engine):
Binärprotokoll (Binlog) und Transaktionsprotokoll (Redo-Log und Undo-Log) sind wichtiger und erfordern unseren Fokus.
Das langsame Abfrageprotokoll zeichnet alle Abfrageanweisungen auf, deren Ausführungszeit long_query_time überschreitet (Standard ist 10 s, normalerweise auf 1 s eingestellt). Es wird häufig zur Lösung des Problems der langsamen SQL-Abfrage (SQL-Ausführungszeit ist zu lang) verwendet.
Das Auffinden langsamer SQL ist der erste Schritt zur Optimierung der Leistung von SQL-Anweisungen. Verwenden Sie dann den Befehl EXPLAIN, um die langsame SQL zu analysieren und Informationen über den Ausführungsplan zu erhalten.
Sie können den Befehl „slow_query_log“ verwenden, um zu überprüfen, ob das langsame Abfrageprotokoll aktiviert ist.
Kann durch SET GLOBAL slow_query_log=ON aktiviert werden
Der Parameter long_query_time definiert, wie lange eine Abfrage dauert, bevor sie als langsame Abfrage definiert werden kann. Sie können sie über den Befehl SHOW VARIABLES LIKE'%long_query_time%' anzeigen.
Es kann auch geändert werden: set global long_query_time = 12
In tatsächlichen Projekten kann das langsame Abfrageprotokoll relativ groß sein und es ist unpraktisch, es direkt zu analysieren. Wir können das offizielle MySQL-Tool zur Analyse und Optimierung langsamer Abfragen verwenden. mysqldumpslow . Mein Blog ist auch einfach mit dem Tool mysqldumpslow verbunden:
[MySQL] mysqldumpslow-Tool – Zusammenfassung langsamer Abfrageprotokolldateien – CSDN-Blog
In MySQL gibt es eine Variable, die die aktuelle Anzahl langsamer Abfrageanweisungen aufzeichnet. Sie können den globalen Status anzeigen wie „% slo“.
w_queries%'; Befehl zum Anzeigen.
MySQL bietet unsERKLÄRENBefehl, um Informationen über den Ausführungsplan zu erhalten.
Der Ausführungsplan bezieht sich auf die spezifische Ausführungsmethode einer SQL-Anweisung nach der Optimierung durch den MySQL-Abfrageoptimierer. Ausführungspläne werden normalerweise in Szenarien wie der SQL-Leistungsanalyse und -Optimierung verwendet. Durch die Ergebnisse von EXPLAIN können Sie Informationen wie die Abfragesequenz der Datentabelle, den Operationstyp der Datenabfrageoperation, welche Indizes getroffen werden können, welche Indizes tatsächlich getroffen werden und wie viele Datensatzzeilen in den einzelnen Daten vorhanden sind Tabelle abgefragt werden, und andere Informationen. Insbesondere können wir SQL mit den folgenden gängigen Methoden optimieren:
1. Vermeiden Sie die Verwendung von SELECT *
SELECT * verbraucht mehr CPU.
SELECT *Unnötige Felder erhöhen den Ressourcenverbrauch der Netzwerkbandbreite und die Datenübertragungszeit, insbesondere große Felder (wie varchar,
Blob, Text)。
SELECT * kann den MySQL-Optimierer nicht zur Abdeckung der Indexoptimierung verwenden (die auf dem MySQL-Optimierer basierende Strategie „Covering Index“ ist extrem schnell und effizient und eine in der Branche dringend empfohlene Methode zur Abfrageoptimierung).
SELECT <Feldliste> kann die Auswirkungen von Änderungen der Tabellenstruktur reduzieren.
2. Paginierungsoptimierung
Gewöhnliches Paging dauert relativ kurz, wenn die Datenmenge gering ist.
Wenn die Datenmenge groß wird und Millionen oder sogar mehrere zehn Millionen erreicht, dauert das normale Paging sehr lange.
Wie kann ich optimieren? Sie können die obige SQL-Anweisung in eine Unterabfrage ändern.
Wir fragen zuerst den Primärschlüsselwert ab, der dem ersten Parameter des Grenzwerts entspricht, und filtern und begrenzen dann basierend auf diesem Primärschlüsselwert, sodass die Effizienz schneller ist. Diese Methode funktioniert jedoch nur, wenn die IDs in positiver Reihenfolge vorliegen.
Das Ergebnis der Unterabfrage generiert jedoch eine neue Tabelle, was sich auf die Leistung auswirkt. Eine übermäßige Verwendung von Unterabfragen sollte vermieden werden. Darüber hinaus ist diese Methode nur anwendbar, wenn die ID in positiver Reihenfolge vorliegt. In komplexen Paging-Szenarien ist es häufig erforderlich, IDs, die die Bedingungen erfüllen, durch Filterbedingungen herauszufiltern. Zu diesem Zeitpunkt sind die IDs diskret und diskontinuierlich.
3. Machen Sie weniger Verknüpfungen
Alibaba-Entwicklungshandbuch:
Sie können die Diskussion auf Zhihu lesen:
https://www.zhihu.com/question/68258877
https://www.zhihu.com/question/682588774. Es wird empfohlen, keine Fremdschlüssel und Kaskaden zu verwenden
Alibaba Java-Entwicklungshandbuch:
5. Wählen Sie den entsprechenden Feldtyp
6. Versuchen Sie, UNION ALL anstelle von UNION zu verwenden
UNION legt alle Daten der beiden Ergebnismengen in einer temporären Tabelle ab und führt dann den Deduplizierungsvorgang durch, der zeitaufwändiger ist und mehr CPU-Ressourcen verbraucht.
UNION ALL dedupliziert die Ergebnismenge nicht mehr und die erhaltenen Daten enthalten doppelte Elemente.
Wenn jedoch im tatsächlichen Geschäftsszenario keine doppelten Daten zulässig sind, kann UNION dennoch verwendet werden.
7. Batch-Operationen
Wenn für Datenaktualisierungen in der Datenbank Stapelvorgänge verwendet werden können, verwenden Sie diese so weit wie möglich, um die Anzahl der Datenbankanforderungen zu reduzieren und die Leistung zu verbessern.
8. Verwenden Sie Indizes richtig
Dieser Abschnitt enthält viele Inhalte, die später in einem separaten Blog vorgestellt werden.
binlog (Binärprotokoll ist eine binäre Protokolldatei) zeichnet hauptsächlich alle Vorgänge auf, die in der MSQL-Datenbank geändert wurden (alle von der Datenbank ausgeführten DDL- und DML-Anweisungen), einschließlich Änderungen der Tabellenstruktur (CREATE, ALTER, DROP TABLE.) und Tabellendaten Änderungen ( INSERT.UPDATE, DELETE..), umfasst jedoch nicht SELECT, SHOW und andere Vorgänge, die keine Änderungen an der Datenbank verursachen.
Mit dem Befehl show Binary Logs können Sie eine Liste aller Binärprotokolle anzeigen:
Es gibt drei Arten binärer Aufzeichnungsmethoden:
Im Vergleich zum Zeilenmodus ist die Protokolldatei im Anweisungsmodus kleiner, der E/A-Druck der Festplatte ist ebenfalls geringer und die Leistung ist besser. Allerdings ist seine Genauigkeit schlechter als im Zeilenmodus.
Vor MySQL 5.1.5 war das Format von Binlog nur STATEMENT. 5.1.5 begann, Binlog im ROW-Format zu unterstützen. Ab Version 5.1.8 begann MySQL, Binlog im MIXED-Format zu unterstützen. Vor MySQL 5.7.7 wurde standardmäßig der Anweisungsmodus verwendet. MySQL5.7.7 verwendet standardmäßig den Zeilenmodus.
Sie können Show-Variablen wie „%binlog format%“ verwenden, um das von binlog verwendete Format anzuzeigen
Das Hauptanwendungsszenario von Binlog ist die Master-Slave-, Master-Master- und Master-Slave-Replikation, auf die sich Binlog verlassen muss, um Daten zu synchronisieren und Datenkonsistenz sicherzustellen.
Das Prinzip der Master-Slave-Replikation ist in der folgenden Abbildung dargestellt:
1. Die Hauptbibliothek schreibt die Datenänderungen in der Datenbank in binlog
2. Verbinden Sie die Slave-Bibliothek mit der Hauptbibliothek
3. Die Slave-Bibliothek erstellt einen I0-Thread, um das aktualisierte Binlog von der Hauptbibliothek anzufordern.
4. Die Hauptbibliothek erstellt einen Binlog-Dump-Thread zum Senden des Binlogs, und der I/0-Thread in der Slave-Bibliothek ist für den Empfang verantwortlich. 5. Der I/0-Thread der Slave-Bibliothek schreibt das empfangene Binlog in das Relay Protokoll.
6. Lesen Sie das Relay-Protokoll aus dem SQL-Thread der Bibliothek und synchronisieren Sie die Daten lokal (d. h. führen Sie die SQL erneut aus).
Bei der InnoDB-Speicher-Engine wird das Protokoll während der Ausführung einer Transaktion zunächst in den Binlogcache geschrieben. Erst wenn die Transaktion übermittelt wird, wird das Protokoll im Binlogcache in der Binlog-Datei auf der Festplatte gespeichert. Das Schreiben in den Speicher erfolgt schneller, und dies geschieht auch aus Effizienzgründen.
Da das Binlog einer Transaktion nicht aufgeteilt werden kann, muss es unabhängig von der Größe der Transaktion einmal geschrieben werden, sodass das System jedem Thread einen Speicherblock als Binlog-Cache zuweist. Wir können die Binlogcache-Größe eines einzelnen Threads über den Parameter binlog_cache_size steuern. Wenn der Speicherinhalt diesen Parameter überschreitet, muss er vorübergehend auf der Festplatte gespeichert werden (Swap).
Wann wird das Binlog auf die Festplatte geleert? Sie können den Zeitpunkt des Biglog-Löschens über den Parameter sync_binlog steuern. Der Wertebereich ist 0-N und der Standardwert ist 0.
·0: Keine zwingende Anforderung, das System entscheidet, wann auf die Festplatte geschrieben wird.
·1: Jedes Mal, wenn eine Transaktion übermittelt wird, muss das Binlog auf die Festplatte geschrieben werden:
·N: Binlog wird alle N Transaktionen auf die Festplatte geschrieben.Verlustrisiko
Vor MySQL5.7 war der Standardwert von sync_binlog 0. Nach MySQL5.7 ist der Standardwert von sync_binlog 1. Im Allgemeinen wird nicht empfohlen, den Wert von sync_binlog auf 0 zu setzen. Wenn die Leistungsanforderungen relativ hoch sind oder ein Festplatten-IO-Engpass auftritt, kann der Wert von sync_binlog entsprechend erhöht werden. Dies erhöht jedoch das Risiko eines Datenverlusts.
Wenn die folgenden drei Situationen auftreten, generiert MySQL eine neue Protokolldatei neu und die Seriennummer der Datei wird erhöht.
Wir wissen, dass die InnoD8-Speicher-Engine den Speicherplatz in Seiteneinheiten verwaltet. Die Daten, die wir in MySQL einfügen, sind letztendlich auf der Seite vorhanden. Um den Festplatten-IO-Overhead zu reduzieren, gibt es auch einen Bereich namens Buffer Pool, der im Speicher vorhanden ist. Wenn die unseren Daten entsprechende Seite nicht im Pufferpool vorhanden ist, speichert MSQL die Seite auf der Festplatte zunächst im Pufferpool zwischen, sodass wir die Seite später direkt im Pufferpool betreiben, was die Lese- und Schreibleistung erheblich verbessert .
Nachdem eine Transaktion festgeschrieben wurde, werden unsere Änderungen an der entsprechenden Seite im Pufferpool möglicherweise nicht auf der Festplatte gespeichert. Wenn MySQL zu diesem Zeitpunkt plötzlich abstürzt, verschwinden die Änderungen in dieser Transaktion dann direkt?
Offensichtlich nicht, wenn ja, würde dies offensichtlich die Dauerhaftigkeit der Transaktion beeinträchtigen.
Die MySQLInnoDB-Engine verwendet Redo-Log, um die Haltbarkeit der Transaktionen sicherzustellen. Die Hauptaufgabe des Redo-Logs besteht darin, Seitenänderungen aufzuzeichnen, z. B. wie viele Bytes an einem bestimmten Offset auf einer bestimmten Seite geändert wurden und um welchen spezifischen Inhalt es sich handelt. Jeder Datensatz im Redo-Log enthält die Tabellenplatznummer, die Datenseitennummer, den Offset und bestimmte geänderte Daten und zeichnet möglicherweise sogar die Länge der geänderten Daten auf (abhängig vom Redo-Log-Typ).
Wenn die Transaktion festgeschrieben wird, leeren wir das Redo-Protokoll gemäß der Flush-Strategie auf die Festplatte. Auf diese Weise können die Daten, die nicht auf die Festplatte geschrieben werden konnten, nach dem Neustart wiederhergestellt werden, selbst wenn MySQL abstürzt, wodurch die Haltbarkeit sichergestellt wird der Transaktion. Mit anderen Worten: Redo Log bietet MySQL-Absturzwiederherstellungsfunktionen.
Redo Log zeichnet alle Änderungsvorgänge in der Datenbank auf. Wenn die Datenbank Schreibvorgänge ausführt (INSERT, UPDATE, DELETE), werden diese Vorgänge zunächst im Redo-Log aufgezeichnet und dann auf die Datendatei angewendet. Auf diese Weise kann Redo Log sicherstellen, dass die Daten nicht verloren gehen, selbst wenn das System ausfällt, bevor der Datenänderungsvorgang vollständig auf die Festplatte geschrieben wurde. Während der Wiederherstellung wiederholt die Datenbank diese nicht abgeschlossenen Änderungsvorgänge aus dem Redo-Protokoll, um die Datenkonsistenz sicherzustellen.
Redo Log hilft der Datenbank, nach einem Systemabsturz oder einem unerwarteten Stromausfall einen konsistenten Zustand wiederherzustellen. Während des Wiederherstellungsprozesses überprüft die Datenbank die Datensätze im Redo-Protokoll und wendet alle übermittelten, aber nicht persistenten Datenänderungen erneut auf die Datendateien an, um die Daten wiederherzustellen.
Um die Leistung von Schreibvorgängen zu verbessern, verwenden Datenbanken häufig Caching-Mechanismen (z. B. Pufferpools), um Änderungsvorgänge vorübergehend im Speicher zu speichern, anstatt sie sofort auf die Festplatte zu schreiben. Das Vorhandensein von Redo Log macht diesen Caching-Mechanismus möglich, denn solange sichergestellt ist, dass das Redo Log dauerhaft erhalten bleibt, besteht kein Risiko eines Datenverlusts, selbst wenn die Daten im Cache nicht auf die Festplatte geschrieben wurden.
Beim Festschreiben einer Transaktion wird das Redo-Protokoll im Protokollpuffer auf die Festplatte geleert. Sie können innodb_flush_log_at verwenden
Commit-Parametersteuerung. Wir müssen darauf achten, die richtige Flush-Richtlinie innodb_flush_log_at_trx_commit festzulegen. Abhängig von der in MySQL konfigurierten Flush-Strategie kann es nach dem Ausfall von MySQL zu geringfügigen Datenverlustproblemen kommen.
innodb_flush_log_at_trx_commit Es handelt sich um einen wichtigen Konfigurationsparameter in der MySQL InnoDB-Speicher-Engine. Er bestimmt die Flush- (Flush) und Schreibstrategien (Write) des Protokolls, wenn eine Transaktion übermittelt wird, und wirkt sich somit auf die Datenhaltbarkeit und -leistung aus. Es hat drei Werte, nämlich 0, 1 und 2. Jeder Wert repräsentiert eine andere Putzstrategie.
innodb_flush_log_at_trx_commit = 0
innodb_flush_log_at_trx_commit = 1
innodb_flush_log_at_trx_commit = 2
Zusammenfassung der Pinselstrategie
1. Das Redo-Protokoll wird in den Protokollpuffer geschrieben, wurde jedoch noch nicht in den Seitencache geschrieben. Zu diesem Zeitpunkt stürzt die Datenbank ab und es kommt zu Datenverlust (dieser Datenverlust kann auftreten, wenn der Wert der Flush-Richtlinie innodb_flush log_at trx_commit ist). 0);
2. Das Redo-Protokoll wurde in den Seitencache geschrieben, aber noch nicht auf die Festplatte. Das Betriebssystem stürzt ab und es kann zu Datenverlust kommen (dieser Datenverlust kann auftreten, wenn der Wert der Flush-Richtlinie innodb2 „flush log_at trx_commit“ lautet). 2).
Das Rückgängig-Protokoll (Rollback-Protokoll) ist ein Protokoll, das im Datenbanksystem zur Aufzeichnung von Datenänderungsvorgängen verwendet wird. Es zeichnet die umgekehrten Vorgänge (d. h. Rückgängigmachungen) aller Änderungsvorgänge an Daten während der Transaktionsausführung auf. Das Rückgängig-Protokoll spielt eine Schlüsselrolle beim Transaktions-Rollback. Mithilfe des Rückgängig-Protokolls können Daten in den Zustand vor Beginn der Transaktion zurückversetzt werden, wodurch die Atomizität der Transaktion sichergestellt wird.
Die Atomizität einer Transaktion bedeutet, dass alle Vorgänge der Transaktion entweder alle ausgeführt werden oder keine ausgeführt werden. Undo Log stellt die Atomizität von Transaktionen durch die folgenden Mechanismen sicher:
Rückgängigmachen-Vorgänge aufzeichnen Während der Ausführung der Transaktion zeichnet jeder Änderungsvorgang an den Daten vor der eigentlichen Änderung den entsprechenden Rückgängig-Vorgang im Rückgängig-Protokoll auf. Wenn beispielsweise eine Transaktion den Wert einer Datensatzzeile aktualisiert, wird der alte Wert vor der Aktualisierung im Rückgängig-Protokoll aufgezeichnet.
Rollback-Transaktion Wenn die Transaktion aus irgendeinem Grund fehlschlägt (z. B. aufgrund eines Fehlers oder eines expliziten Rollbacks), liest das Datenbanksystem das Rückgängig-Protokoll und stellt die Daten gemäß dem aufgezeichneten Rückgängig-Vorgang in dem Zustand vor Beginn der Transaktion wieder her. Auf diese Weise können Sie sicherstellen, dass die fehlgeschlagene Transaktion keine Auswirkungen auf die Datenbank hat, und somit die Atomizität der Transaktion sicherstellen.
Zitat:
Detaillierte Erläuterung der Lese-/Schreibtrennung sowie der Unterdatenbank und Tabelle JavaGuide
Er widmet sich seit mehr als 30 Jahren der Technologieforschung und beherrscht verschiedene Sprachen wie Java, Linux, Javascript, PHP, CSS usw. Er hat viele Beiträge im Open-Source-Bereich geleistet, Entwicklerdokumentationsstation, um einige Themen in der Technologieentwicklung als zukünftige Referenz zu teilen
