MySQL-Datenbanktabelle und Datenbankpartitionierungsstrategie

Lassen Sie uns zunächst darüber sprechen, warum wir die Tabelle teilen müssen:

Wenn ein Datenblatt Millionen erreicht, dauert eine einmalige Abfrage länger. Bei einer gemeinsamen Abfrage kann es hier zu einem Fehler kommen. Der Zweck des Shardings von Tabellen besteht darin, die Belastung der Datenbank zu verringern und die Abfragezeit zu verkürzen. Bei der täglichen Entwicklung stoßen wir häufig auf große Tabellen. Die sogenannten großen Tabellen beziehen sich auf Tabellen, in denen Millionen oder sogar Dutzende Millionen Datensätze gespeichert sind. Eine solche Tabelle ist zu groß, wodurch die Abfrage- und Einfügevorgänge in der Datenbank zu lange dauern, was zu einer schlechten Leistung führt. Wenn eine gemeinsame Abfrage beteiligt ist, ist die Leistung noch schlechter. Der Zweck der Tabellensharding und Tabellenpartitionierung besteht darin, die Belastung der Datenbank zu verringern und die Effizienz der Datenbank zu verbessern. Im Allgemeinen soll dadurch die Effizienz beim Hinzufügen, Löschen, Ändern und Überprüfen von Tabellen verbessert werden. Die Datenmenge in der Datenbank ist nicht unbedingt kontrollierbar. Ohne Sharding wird es mit der Zeit und der Entwicklung des Geschäfts immer mehr Tabellen in der Datenbank geben, und die Datenmenge in den Tabellen wird ebenfalls zunehmen. Entsprechend wird auch der Aufwand für Datenoperationen, Hinzufügungen, Löschungen, Änderungen und Abfragen zunehmen. Da außerdem eine verteilte Bereitstellung nicht möglich ist und die Ressourcen eines Servers (CPU, Festplatte, Speicher, IO usw.) begrenzt sind, werden die Datenmenge, die die Datenbank speichern kann, und die Datenverarbeitungskapazitäten irgendwann auf Engpässe stoßen.

Der Vorgang, bei dem MySQL ein SQL ausführt, läuft wie folgt ab:

1. SQL empfangen;

2. Setzen Sie das SQL in die Warteschlange;

3. SQL ausführen;

4. Geben Sie das Ausführungsergebnis zurück.

Wo findet der zeitaufwändigste Prozess statt? Erstens ist es die Wartezeit in der Warteschlange und zweitens die Ausführungszeit von SQL. Tatsächlich sind diese beiden dasselbe. Während des Wartens muss SQL ausgeführt werden. Deshalb müssen wir die Ausführungszeit von SQL verkürzen.

In MySQL gibt es einen Mechanismus namens Tabellensperre und Zeilensperre. Warum gibt es diesen Mechanismus? Er dient dazu, die Integrität der Daten sicherzustellen. Lassen Sie mich Ihnen ein Beispiel geben. Wenn zwei SQLs dieselben Daten in derselben Tabelle ändern möchten, was sollten wir zu diesem Zeitpunkt tun? Können beide SQLs diese Daten gleichzeitig ändern? Offensichtlich behandelt MySQL diese Situation auf zwei Arten: Tabellensperre (Myisam-Speicher-Engine) und Zeilensperre (InnoDB-Speicher-Engine). Tabellensperre bedeutet, dass keiner von Ihnen an dieser Tabelle arbeiten kann. Sie müssen warten, bis ich mit der Arbeit an der Tabelle fertig bin. Dasselbe gilt für die Zeilensperre. Andere SQL-Anweisungen müssen warten, bis ich die Operation an diesen Daten abgeschlossen habe, bevor sie mit diesen Daten arbeiten können. Wenn zu viele Daten vorhanden sind, ist die Ausführungszeit zu lang und die Wartezeit länger. Deshalb müssen wir die Tabelle aufteilen.

2. Der Plan der Untertabelle

1. Cluster

1. Beim Erstellen eines MySQL-Clusters fragen sich manche Leute möglicherweise: Welche Beziehung besteht zwischen der Stammtabelle und der partitionierten Tabelle? Obwohl es sich nicht um eine Sharded Table im eigentlichen Sinne handelt, ermöglicht es die Funktion von Sharding-Tabellen. Welche Bedeutung hat Clustering? Um die Belastung einer Datenbank zu verringern, muss man, vereinfacht ausgedrückt, die Anzahl der SQLs in der SQL-Warteschlange verringern. Wenn beispielsweise 10 SQL-Anfragen vorliegen und in die Warteschlange eines Datenbankservers gestellt werden, müssen sie lange warten. Wenn diese 10 SQL-Anfragen auf die Warteschlangen von 5 Datenbankservern verteilt werden, befinden sich nur 2 in der Warteschlange eines Datenbankservers. Wird auf diese Weise die Wartezeit erheblich verkürzt? Dies ist bereits jetzt offensichtlich. Also habe ich es in den Bereich der Untertabelle eingefügt. Ich habe einige MySQL-Cluster erstellt:

Installation, Konfiguration und Lese-/Schreibtrennung des Linux-MySQL-Proxys

MySQL-Replikation, gegenseitige Master-Slave-Installation und -Konfiguration sowie Datensynchronisation

Vorteile: gute Skalierbarkeit, keine komplizierten Operationen nach mehreren Untertabellen (PHP-Code)

Nachteile: Die Datenmenge einer einzelnen Tabelle bleibt unverändert, der Zeitaufwand für eine Operation ist gleich und der Hardware-Overhead ist hoch.

2. Untertabelle

Zwei Möglichkeiten zum Teilen der Tabelle:

Das Aufteilen von Feldern auf verschiedene Tabellen und das Aufteilen von Feldern vom Typ Zeichenfolge in der Originaltabelle auf andere Tabellen kann die Abfrage der Haupttabelle beschleunigen.

2. Die vertikale Segmentierung erfolgt nach Fachgebiet.

Eine Datenbank enthält 30 Millionen Benutzerdatensätze, darunter Dutzende von Feldern wie ID, Benutzer, Passwort, Vorname, Nachname, E-Mail, Adresse usw. Für die Benutzeranmeldung sind Benutzer- und Passwortfelder erforderlich, und die Suche nach Benutzer- und Passwortfeldern ist langsam. Wenn Benutzer und Passwort in einer separaten Tabelle erstellt werden, ist die Geschwindigkeit höher. Erstellen Sie eine weitere Tabelle für andere Felder des Benutzers. Dies ist nur ein Beispiel.

Teilen Sie die Daten in mehrere Tabellen mit derselben Struktur auf.

Horizontal bedeutet Aufteilung nach Datensätzen. Eine Datenbank hat 30 Millionen Benutzerdatensätze und die Verarbeitungsgeschwindigkeit ist langsam. In diesem Fall können die 30 Millionen in fünf Teile mit jeweils 6 Millionen aufgeteilt und auf verschiedenen Maschinen platziert werden.

Horizontaler Tisch:

Das heißt, wir schätzen im Voraus, dass es Tabellen mit großen Datenmengen und häufigen Zugriffen geben wird, und teilen diese auf mehrere Tabellen auf. Diese Schätzung ist ungefähr richtig. Die Tabelle der Beiträge im Forum wird mit der Zeit definitiv sehr groß, Hunderttausende oder sogar Millionen von Daten sind möglich. Im Chatroom gibt es eine Informationstabelle. Dutzende von Leuten chatten eine ganze Nacht lang miteinander. Mit der Zeit müssen die Daten in dieser Tabelle sehr groß sein. Es gibt viele Situationen wie diese. Daher teilen wir diese Art von Tabellen mit großem Datenvolumen, die geschätzt werden können, im Voraus in N Tabellen auf. Der Wert von N hängt von der tatsächlichen Situation ab. Nehmen Sie als Beispiel die Chat-Informationstabelle:

Ich habe im Voraus 100 solcher Tabellen erstellt, message_00, message_01, message_02... message_98, message_99. Dann habe ich anhand der Benutzer-ID bestimmt, in welche Tabelle die Chat-Informationen des Benutzers eingefügt werden sollten. Ich könnte die Restmethode verwenden, um die Informationen abzurufen.

3. In der praktischen Anwendung:

Es ist notwendig, vertikale und horizontale Tabellenpartitionierung zu kombinieren. Wenn eine Datenbank 30 Millionen Benutzer hat, können Sie zuerst die vertikale Partitionierung in Betracht ziehen und dann nach der Partitionierung die horizontale Partitionierung durchführen.

Das heißt, teilen Sie zuerst andere Felder in die Tabelle „user_info“ auf und belassen Sie nur Schlüsselfelder wie Benutzer-ID, Kennwort, Benutzername usw. in der Hauptbenutzertabelle.

Führen Sie dann eine horizontale Aufteilung durch, um die Benutzer- und Benutzerinformationstabellen in mehrere Tabellen mit derselben Struktur aufzuteilen.

Als nächstes schauen wir uns an, wie MySQL beim Speichern von Daten in separaten Tabellen funktioniert:

1. Einfache MySQL Master-Slave-Replikation:

Die Master-Slave-Replikation von MySQL löst die Lese-/Schreibtrennung der Datenbank und verbessert die Leseleistung erheblich, wie in der folgenden Abbildung dargestellt:

Der Master-Slave-Replikationsprozess wird in der folgenden Abbildung dargestellt:

Allerdings bringt die Master-Slave-Replikation auch eine Reihe anderer Leistungsengpässe mit sich:

1. Schreibvorgänge können nicht skaliert werden

2. Schreibvorgänge können nicht zwischengespeichert werden

3. Replikationsverzögerung

4. Erhöhte Sperrrate

5. Die Tabelle wird größer und die Cache-Rate sinkt

Das Problem muss gelöst werden, was zur folgenden Optimierungslösung führt. Schauen wir uns das mal an.

2. Vertikale Partitionierung von MySQL

Wenn das Geschäft ausreichend unabhängig aufgeteilt ist, wäre es eine gute Lösung, die Daten verschiedener Geschäfte auf verschiedenen Datenbankservern zu speichern. Darüber hinaus hat der Ausfall eines der Geschäfte keine Auswirkungen auf den normalen Betrieb anderer Geschäfte. Dies spielt auch eine Rolle bei der Lastverteilung und verbessert den Datenbankdurchsatz erheblich. Das Datenbankarchitekturdiagramm nach der vertikalen Partitionierung sieht wie folgt aus:

Obwohl die Unternehmen unabhängig genug sind, gibt es jedoch immer einige Verbindungen zwischen ihnen, z. B. Benutzer, die grundsätzlich mit jedem Unternehmen verbunden sind. Darüber hinaus kann diese Partitionierungsmethode das Problem des Datenanstiegs in einer einzelnen Tabelle nicht lösen. Warum also nicht eine horizontale Partitionierung versuchen?

3. Horizontales MySQL-Sharding

Das ist eine sehr gute Idee. Benutzer werden nach bestimmten Regeln (per ID-Hash) gruppiert und die Daten dieser Benutzergruppe werden in einem Datenbank-Shard, also einem Sharding, gespeichert. Auf diese Weise muss bei steigender Benutzerzahl nur noch ein Server konfiguriert werden. Das Prinzipdiagramm sieht wie folgt aus:

Wie kann man den Shard bestimmen, in dem sich ein Benutzer befindet? Sie können eine Datentabelle erstellen, die Benutzern und Shards entspricht. Suchen Sie bei jeder Anforderung zuerst in dieser Tabelle nach der Shard-ID des Benutzers und fragen Sie dann die relevanten Daten aus dem entsprechenden Shard ab, wie in der folgenden Abbildung dargestellt:

Einzelne Datenbank und einzelne Tabelle

Die gängigsten Datenbankdesigns sind Einzeldatenbanken und Einzeltabellen. Beispielsweise gibt es in der Datenbank db eine Benutzertabelle, und alle Benutzer sind in der Benutzertabelle in der db-Bibliothek zu finden.

Einzelne Datenbank mit mehreren Tabellen

Mit zunehmender Benutzeranzahl wird die Datenmenge in der Benutzertabelle immer größer. Wenn die Datenmenge eine bestimmte Höhe erreicht, werden die Abfragen der Benutzertabelle allmählich langsamer, was sich auf die Leistung der gesamten Datenbank auswirkt. Wenn Sie MySQL verwenden, gibt es ein ernsteres Problem. Wenn Sie eine Spalte hinzufügen müssen, sperrt MySQL die Tabelle und alle Lese- und Schreibvorgänge müssen warten.

Der Benutzer kann auf irgendeine Weise horizontal aufgeteilt werden, um zwei Tabellen mit genau derselben Tabellenstruktur zu erzeugen, z. B. user_0000 und user_0001. Die Daten von user_0000 + user_0001 + ... sind genau ein vollständiger Datensatz.

Mehrere Datenbanken und mehrere Tabellen

Wenn die Datenmenge zunimmt, reicht der Speicherplatz einer einzelnen Datenbank möglicherweise nicht mehr aus. Wenn die Anzahl der Abfragen zunimmt, kann ein einzelner Datenbankserver diese möglicherweise nicht mehr unterstützen. Zu diesem Zeitpunkt kann die Datenbank horizontal differenziert werden.

Regeln für das Sharding

Beim Entwerfen einer Tabelle müssen Sie die Regeln für die Aufteilung der Tabelle in verschiedene Datenbanken und Tabellen festlegen. Wenn sich beispielsweise ein neuer Benutzer anmeldet, muss das Programm bestimmen, zu welcher Tabelle die Benutzerinformationen hinzugefügt werden sollen. Ebenso müssen wir beim Anmelden über die Kontonummer des Benutzers den entsprechenden Datensatz in der Datenbank finden, und all dies muss nach bestimmten Regeln erfolgen.

Routenplanung

Der Prozess des Suchens der entsprechenden Tabellen und Bibliotheken durch die Bibliotheks- und Tabellenpartitionierungsregeln. Wenn beispielsweise die Regel zum Aufteilen von Datenbanken und Tabellen „user_id mod 4“ lautet und ein Benutzer ein neues Konto mit der Konto-ID 123 registriert, können wir „id mod 4“ verwenden, um festzulegen, dass dieses Konto in der Tabelle „User_0003“ gespeichert werden soll. Wenn sich Benutzer 123 anmeldet, überprüfen wir, ob der Datensatz in User_0003 vorhanden ist, indem wir 123 Mod 4 ausführen.

Probleme und Vorsichtsmaßnahmen durch Unterbibliotheken und Untertabellen

1. Probleme mit der Dimension der Aufteilung von Datenbanken und Tabellen

Wenn ein Benutzer ein Produkt kauft, muss der Transaktionsdatensatz gespeichert und abgerufen werden. Wenn die Tabelle nach dem Breitengrad des Benutzers aufgeteilt ist, wird der Transaktionsdatensatz jedes Benutzers in derselben Tabelle gespeichert, sodass der Kaufstatus eines Benutzers schnell und bequem ermittelt werden kann. Der Kaufstatus eines bestimmten Produkts ist jedoch wahrscheinlich auf mehrere Tabellen verteilt, was die Ermittlung schwieriger macht. Wenn Sie die Tabelle hingegen nach Produktdimensionen aufteilen, können Sie den Kaufstatus dieses Produkts leicht ermitteln. Schwieriger ist es jedoch, den Transaktionsdatensatz des Käufers zu finden.

Die gängigen Lösungen sind also:

a. Lösen Sie das Problem, indem Sie die Tabelle scannen. Diese Methode ist grundsätzlich unmöglich und die Effizienz zu gering.

b. Erfassen Sie zwei Datensätze, einen entsprechend der Benutzerdimension und einen entsprechend der Produktdimension.

c. Lösen Sie es über Suchmaschinen. Wenn jedoch die Echtzeitanforderung sehr hoch ist, hängt dies mit der Echtzeitsuche zusammen.

2. Probleme mit gemeinsamen Abfragen

Union-Abfragen sind grundsätzlich nicht möglich, da die zusammengehörenden Tabellen nicht unbedingt in der gleichen Datenbank liegen.

3. Vermeiden Sie datenbankübergreifende Transaktionen

Vermeiden Sie es, Tabellen in db1 zu ändern, während Sie Tabellen in db0 in einer Transaktion ändern. Dies erschwert den Vorgang und beeinträchtigt die Effizienz.

4. Versuchen Sie, denselben Datensatz auf demselben DB-Server abzulegen

Wenn beispielsweise die Produkte und Transaktionsinformationen von Verkäufer A in db0 abgelegt sind, können die zugehörigen Informationen von Verkäufer A bei einem Ausfall von db1 normal verwendet werden. Damit ist zu verhindern, dass Daten einer Datenbank von Daten einer anderen Datenbank abhängig sind.

Ein Master, mehrere Backups

In praktischen Anwendungen überwiegen die Lesevorgänge in den meisten Fällen die Schreibvorgänge bei weitem. MySQL bietet einen Lese-/Schreibtrennungsmechanismus. Alle Schreibvorgänge müssen dem Master entsprechen. Lesevorgänge können auf den Master- und Slave-Maschinen ausgeführt werden. Die Struktur des Slaves ist genau die gleiche wie die des Masters. Ein Master kann mehrere Slaves haben, und es können sogar Slaves an den Slave angeschlossen werden. Diese Methode kann die QPS des DB-Clusters effektiv verbessern.

Alle Schreibvorgänge werden zuerst auf dem Master ausgeführt und dann mit dem Slave synchronisiert, sodass es bei der Synchronisierung vom Master- zum Slave-Rechner zu einer gewissen Verzögerung kommt. Wenn das System sehr ausgelastet ist, wird das Verzögerungsproblem schwerwiegender, und die Zunahme der Anzahl der Slave-Rechner wird dieses Problem ebenfalls verschärfen.

Darüber hinaus ist ersichtlich, dass der Master der Engpass des Clusters ist. Wenn zu viele Schreibvorgänge stattfinden, wird die Stabilität des Masters ernsthaft beeinträchtigt. Wenn der Master ausfällt, funktioniert der gesamte Cluster nicht richtig.

Daher: 1. Wenn der Lesedruck sehr hoch ist, können Sie das Hinzufügen von Slave-Maschinen in Betracht ziehen, um das Problem zu lösen. Wenn die Anzahl der Slave-Maschinen jedoch einen bestimmten Wert erreicht, müssen Sie eine Aufteilung der Datenbank in Betracht ziehen. 2. Wenn der Schreibdruck sehr hoch ist, ist Datenbank-Sharding erforderlich.

Warum sollte MySQL in Datenbanken und Tabellen unterteilt werden?

Man kann sagen, dass überall, wo MySQL verwendet wird, bei großen Datenmengen sofort ein Problem auftritt, nämlich die Aufteilung der Datenbank in Tabellen.

Hier ist eine Frage: Warum müssen wir Datenbank und Tabelle trennen? Kann MySQL keine großen Tabellen verarbeiten?

Tatsächlich ist es möglich, große Tabellen zu verarbeiten. In den Projekten, die ich erlebt habe, beträgt die physische Dateigröße einer einzelnen Tabelle mehr als 80 G, die Anzahl der Datensätze in einer einzelnen Tabelle beträgt mehr als 500 Millionen, und diese Tabelle

Es gehört zu einer ganz zentralen Tabelle: der Freundschaftsbeziehungstabelle.

Diese Methode ist jedoch nicht die beste. Denn Dateisysteme wie das Ext3-Dateisystem haben ebenfalls viele Probleme bei der Verarbeitung großer Dateien.

Diese Ebene kann durch das xfs-Dateisystem ersetzt werden. Wenn jedoch eine einzelne MySQL-Tabelle zu groß ist, gibt es ein Problem, das schwer zu lösen ist: die Operationsbasis im Zusammenhang mit der Anpassung der Tabellenstruktur

Dies ist nicht mehr möglich. Daher müssen bei großen Projekten bei der Nutzung separate Datenbanken und Tabellen verwendet werden.

Aus der Perspektive von Innodb selbst gibt es nur zwei Sperren für den Btree der Datendatei, die Blattknotensperre und die Kindknotensperre. Wie Sie sich vorstellen können, wenn eine Seitenaufteilung oder ein Seitenzusatz erfolgt,

Wenn ein neues Blatt erstellt wird, können keine Daten in die Tabelle geschrieben werden.

Daher sind Unterbibliotheken und Untertabellen immer noch die bessere Wahl.

Wie viele Unterbibliotheken und Untertabellen sind also angemessen?

Nach dem Testen ist die Schreib- und Leseleistung in einer einzelnen Tabelle mit 10 Millionen Datensätzen relativ gut. Auf diese Weise ist die einzelne Tabelle, wenn ein Puffer übrig bleibt, mit Datenfonts gefüllt.

Weniger als 8 Millionen Datensätze und einzelne Tabellen mit Zeichendatentypen werden unter 5 Millionen gehalten.

Wenn der Plan auf 100 Datenbanken und 100 Tabellen basiert, z. B. Benutzergeschäft:

5 Millionen*100*100 = 50000000 = 500 Milliarden Datensätze.

Wenn Sie erst einmal eine grobe Idee im Kopf haben, ist es relativ einfach, geschäftsorientierte Pläne zu machen.