Verstehen Sie kurz die MySQL-Datenbankoptimierungsphase

Einführung

Haben Sie schon einmal eine Situation erlebt, in der der Interviewer gefragt hat

Wie optimieren Sie Ihre Datenbank?

Wie also sollen wir diese Frage beantworten? Der eigentliche Grund, warum ich dieses Thema geschrieben habe, ist, dass ich in diesen Tagen einen Artikel über Datenbankoptimierungswissen gesehen habe, der von verschiedenen öffentlichen Konten weitergeleitet wurde (ich werde den Link nicht veröffentlichen). Ich habe ihn ein paar Mal durchgeblättert und festgestellt, dass immer wieder gesagt wurde, dass die Datenbank horizontal aufgeteilt werden sollte. Ich möchte alle Leser fragen, wie viele von Ihnen Erfahrungen mit horizontaler Aufteilung haben? Viele Artikel sind heutzutage so praxisfern, dass man nur sagen kann, sie seien reine theoretische Analysen.

Dieser Artikel entstand ursprünglich aus einer Frage auf Zhihu und ich habe ihn auf dieser Grundlage verbessert.

Die erste Phase optimiert SQL und Indizes

Dies ist die erste Phase der Abstimmung. Warum?

Weil dieser Schritt die geringsten Kosten verursacht und keine Middleware erfordert. Sie haben keine Index- und SQL-Optimierung durchgeführt, versuchen aber, eine horizontale Aufteilung durchzuführen. Ist das nicht einfach Betrug?

Wie sind die Schritte? Ich gebe Ihnen einen groben Überblick.

(1) Verwenden Sie langsame Abfrageprotokolle, um SQL-Anweisungen mit geringer Ausführungseffizienz zu lokalisieren

(2) Verwenden Sie „explain“, um den SQL-Ausführungsplan zu analysieren

(3) Identifizieren Sie das Problem, ergreifen Sie entsprechende Optimierungsmaßnahmen, erstellen Sie Indizes usw.

Ich werde keine Beispiele geben, da es so viele Artikel zur Optimierung von SQL gibt, dass es für die Leser ermüdend wäre, sie alle zu lesen.

Die zweite Phase besteht darin, einen Cache zu erstellen

Erwägen Sie die Einrichtung eines Caches nur, wenn sich das Problem durch die Optimierung von SQL nicht lösen lässt. Schließlich besteht der Zweck der Cache-Verwendung darin, komplexe, zeitaufwändige und sich selten ändernde Ausführungsergebnisse zwischenzuspeichern, um den Datenbankressourcenverbrauch zu reduzieren.

Dabei ist zu beachten, dass nach dem Aufbau des Caches die Komplexität des Systems zunimmt. Sie müssen viele Aspekte berücksichtigen, beispielsweise:

Probleme mit dem Cache und der Datenbankkonsistenz? (Zum Beispiel, ob Cache hinzugefügt oder gelöscht werden soll), können Sie sich auf meinen Artikel „Analyse des Dual-Write-Konsistenzschemas für Datenbanken und Caches“ beziehen.
Wie lassen sich die Probleme Cache-Zusammenbruch, Cache-Penetration und Cache-Lawine lösen? Ist es notwendig, den Cache vorzuheizen? Aber ich schätze, die meisten kleinen und mittleren Unternehmen haben darüber wahrscheinlich nicht nachgedacht.

Die dritte Stufe der Lese-Schreib-Trennung

Wenn das Caching nicht funktioniert, verwenden Sie Master-Slave-Replikation und Lese-/Schreibtrennung. Auf der Anwendungsebene wird zwischen Lese- und Schreibanforderungen unterschieden. Oder verwenden Sie vorgefertigte Middleware wie mycat oder altas, um Lesen und Schreiben zu trennen.

Es ist zu beachten, dass Sie sich auf drei Probleme vorbereiten müssen, solange Sie es wagen zu sagen, dass Sie die Master-Slave-Architektur verwenden:

(1) Welche Vorteile bietet eine Master-Slave-Beziehung?

Antwort: Implementieren Sie eine Datenbanksicherung, implementieren Sie einen Datenbanklastenausgleich und verbessern Sie die Datenbankverfügbarkeit

(2) Das Master-Slave-Prinzip?

Antwort: Wie auf dem Bild gezeigt (das Bild habe ich nicht selbst gezeichnet, ich bin faul)

Die Master-Datenbank verfügt über einen Log-Dump-Thread, der das Binärprotokoll an die Slave-Datenbank weiterleitet.

Die Slave-Datenbank hat zwei Threads, einen I/O-Thread und einen SQL-Thread. Der I/O-Thread liest den Binlog-Inhalt aus der Master-Datenbank und schreibt ihn in das Relay-Log. Der SQL-Thread liest den Inhalt aus dem Relay-Log und schreibt ihn in die Slave-Datenbank.

(3) Wie lässt sich das Master-Slave-Konsistenzproblem lösen?

Antwort: Ich empfehle nicht, dieses Problem auf Datenbankebene zu lösen. Gemäß dem CAP-Theorem ist die Master-Slave-Architektur eine Hochverfügbarkeitsarchitektur, die die Konsistenzanforderungen nicht erfüllen kann. Selbst wenn Sie den synchronen Replikationsmodus oder den halbsynchronen Replikationsmodus verwenden, handelt es sich um eine schwache Konsistenz, nicht um eine starke Konsistenz. Daher wird empfohlen, zur Lösung dieses Problems einen Cache zu verwenden.

Die Schritte sind wie folgt:

1. Berechnen Sie die Master-Slave-Verzögerungszeit durch Testen. Es wird empfohlen, MySQL Version 5.7 oder höher zu verwenden, da MySQL seit 5.7 über eine umfassendere Multithread-Replikationsfunktion verfügt, die im Allgemeinen sicherstellen kann, dass die Verzögerung innerhalb von 1 Sekunde liegt. Andererseits ist MySQL jetzt auf Version 8.x, verwendet immer noch jemand die Version 5.x?

2. Schreiben Sie bei Datenbankschreibvorgängen zuerst in die Datenbank und dann in den Cache. Die Gültigkeitsdauer ist jedoch sehr kurz und geringfügig länger als die Master-Slave-Verzögerung.

3. Wenn Sie eine Anforderung lesen, lesen Sie zuerst den Cache. Wenn der Cache nicht vorhanden ist (die Master-Slave-Synchronisierung ist zu diesem Zeitpunkt abgeschlossen), lesen Sie die Datenbank.

Die vierte Stufe verwendet die Partitionstabelle

Ehrlich gesagt können Sie diesen Schritt im Vorstellungsgespräch eigentlich überspringen. Da viele Internetunternehmen die Verwendung von Partitionstabellen nicht empfehlen, empfehle ich selbst auch nicht die Verwendung von Partitionstabellen. Die Verwendung dieser Partitionstabelle birgt zu viele Fallstricke.

Hier einige Antworten aus anderen Artikeln:

Was ist eine Partitionstabelle in MySQL?

Antwort: Alle Daten liegen weiterhin in einer Tabelle, die physikalische Speicherung erfolgt jedoch nach bestimmten Regeln in unterschiedlichen Dateien. Dies ist eine von MySQL unterstützte Funktion und der Geschäftscode muss nicht geändert werden.

Allerdings muss die SQL-Anweisung geändert werden und die SQL-Bedingung muss die Partitionsspalte enthalten.

Mangel

(1) Das Partitionsschlüsseldesign ist nicht flexibel. Wenn der Partitionsschlüssel nicht verwendet wird, kann es leicht zu einer vollständigen Tabellensperre kommen

(2) Bei der Verwendung von ALTER TABLE ... ORDER BY auf einer partitionierten Tabelle kann order by nur innerhalb jeder Partition ausgeführt werden.

(3) Wenn Sie einen Index für den Partitionsschlüssel einer partitionierten Tabelle erstellen, wird der Index ebenfalls partitioniert. So etwas wie einen globalen Index für einen Partitionsschlüssel gibt es nicht.

(4) Sie können die Datenbank und die Tabellen selbst aufteilen, die Geschäftsszenarien und Zugriffsmodi selbst steuern und es ist steuerbar. Für die Partitionstabelle schrieb das F&E-Team eine SQL-Anweisung, war sich jedoch nicht sicher, welche Partition überprüft werden sollte, was nicht sehr kontrollierbar war.
...Nicht aufgeführt, nicht empfohlen

Stufe 5: Vertikale Teilung

Wenn die oben genannten vier Schritte nicht abgeschlossen sind, wird eine vertikale Aufteilung durchgeführt. Die Komplexität der vertikalen Aufteilung ist immer noch geringer als die der horizontalen Aufteilung. Teilen Sie Ihre Tabelle entsprechend den Modulen in verschiedene kleine Tabellen auf. Jeder sollte „Die Entwicklung der Architektur großer Websites“ gelesen haben. In Artikeln oder Büchern dieser Art wird grundsätzlich diese Phase erwähnt.
Wenn Sie das Glück haben, bei einem Betreiber, einer Bank oder einem anderen Unternehmen zu arbeiten, werden Sie feststellen, dass es dort sehr üblich ist, Hunderte von Feldern in einer Tabelle zu haben. Daher sollte es aufgeteilt werden. Die Grundsätze der Aufteilung lauten im Allgemeinen wie folgt:

(1) Platzieren Sie selten verwendete Felder in einer separaten Tabelle.

(2) Platzieren Sie häufig verwendete Felder in einer separaten Tabelle

(3) Spalten, die häufig in Kombination abgefragt werden, werden in einer Tabelle zusammengefasst (gemeinsamer Index).

Stufe 6: Horizontale Teilung

OK, die horizontale Teilung ist die problematischste Phase. Nach der Teilung wird es viele Probleme geben. Ich betone noch einmal, dass die horizontale Teilung die letzte Wahl sein muss. In gewisser Weise denke ich, dass es besser wäre, es vertikal aufzuteilen. Denn wenn Sie nach der Verwendung der vertikalen Aufteilung zum Aufteilen in verschiedene Module feststellen, dass der Druck eines einzelnen Moduls zu groß ist, können Sie das Modul separat vollständig optimieren, z. B. durch Verbessern der Maschinenkonfiguration des Moduls. Wenn es sich um eine horizontale Aufteilung in zwei Tabellen handelt, muss der Code geändert werden. Dann stellt sich heraus, dass zwei Tabellen nicht ausreichen. Also wird der Code erneut geändert und es erfolgt eine Aufteilung in drei Tabellen. Da die Kopplung zwischen horizontal geteilten Modulen zu stark und die Kosten zu hoch sind, wird dies nicht besonders empfohlen.

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, er wird für jedermanns Studium hilfreich sein. Ich hoffe auch, dass jeder 123WORDPRESS.COM unterstützen wird.