Detaillierte Erklärung, wie PHP Dutzende Millionen Tabellen in MySQL optimiert

Zuallererst ist die Verwendung von MySQL zum Speichern von Hunderten Milliarden Daten in der Tat eine sehr große Herausforderung. Eine einzelne MySQL-Tabelle kann zwar 1 Milliarde Datenebenen speichern, aber die Leistung ist derzeit sehr schlecht. Eine große Anzahl von Experimenten im Projekt hat gezeigt, dass die Kapazität einer einzelnen MySQL-Tabelle etwa 5 Millionen beträgt, und die Leistung beträgt von seiner besten Seite.

Die Optimierung großer Tabellen wird hauptsächlich durch Datenbankunterdatenbanken und Untertabellen gelöst. Derzeit gibt es drei gängige Lösungen: 分区, 分库分表, NoSql/NewSql. In tatsächlichen Projekten werden diese drei Lösungen kombiniert. Derzeit sind die Kerndaten der meisten Systeme hauptsächlich RDBMS-Speicher, ergänzt durch NoSql/NewSql-Speicher.

Partition

Lassen Sie uns zunächst das Partitionsschema verstehen.

Partitionierte Tabellen werden durch mehrere verwandte zugrunde liegende Tabellen implementiert. Diese zugrunde liegenden Tabellen werden auch durch Handle-Objekte dargestellt, sodass wir auch direkt auf jede Partition zugreifen können. Die Speicher-Engine verwaltet die zugrunde liegenden Tabellen der Partitionen auf die gleiche Weise wie normale Tabellen (alle zugrunde liegenden Tabellen müssen dieselbe Speicher-Engine verwenden). Der Index der Partitionstabelle besteht lediglich darin, jeder zugrunde liegenden Tabelle einen identischen Index hinzuzufügen. Diese Lösung schützt Benutzer vor den Details des Shardings. Auch wenn die Abfragebedingungen keine Sharding-Spalte enthalten, kann sie dennoch normal funktionieren (die Leistung ist derzeit jedoch durchschnittlich).

Die Mängel liegen jedoch auf der Hand: Viele Ressourcen werden durch eine einzelne Maschine begrenzt, beispielsweise die Anzahl der Verbindungen, der Netzwerkdurchsatz usw. Die Partitionierung ist eines der Schlüsselelemente in der praktischen Anwendung.

Beginnen wir mit einem Beispiel: Am Beispiel der Kundeninformationen beträgt die Menge an Kundendaten mehr als 50 Millionen. Der Projekthintergrund erfordert die Speicherung der Bankkartenbindungsbeziehung des Kunden, der Dokumentenbindungsbeziehung des Kunden verbindliche Geschäftsauskünfte.

Wie sollten wir vor diesem geschäftlichen Hintergrund die Datenbank gestalten? In der ersten Phase des Projekts haben wir eine Tabelle mit Kunden-Geschäftsbindungsbeziehungen erstellt, die redundante Geschäftsinformationen enthält, die an jeden Kunden gebunden sind.

Die Grundstruktur ist ungefähr wie folgt:

Bei der Abfrage indizieren Sie die Bankkarte und die Geschäftsnummer Index, Dokumentennummer wird als Index verwendet. Wenn die Nachfrage steigt, werden die Indizes dieser Tabelle mehr als 10 erreichen. Darüber hinaus werden bei einem Widerruf des Vertrags durch den Kunden und einer erneuten Vertragsunterzeichnung zwei Daten gespeichert, deren Bindungsstatus jedoch unterschiedlich ist.

Angenommen, wir haben 50 Millionen Kunden, 5 Geschäftstypen und durchschnittlich 2 Karten pro Kunde, dann wird das Datenvolumen dieser Tabelle tatsächlich erstaunliche 500 Millionen erreichen ist immer noch Es wird nicht funktionieren, bis es eine Million erreicht. Ein solches Design ist absolut nicht möglich. Unabhängig davon, ob es eingefügt oder abgefragt wird, führt es zum Absturz des Systems.

Die Daten in der MySQL-Datenbank werden in Form von Dateien auf der Festplatte gespeichert. Standardmäßig werden sie unter /mysql/data abgelegt (hauptsächlich über das Datenverzeichnis in my.cnf). entspricht drei. Eine Datei ist frm zum Speichern der Tabellenstruktur, eine ist myd zum Speichern der Tabellendaten und die andere ist myi zum Speichern des Tabellenindex. Diese drei Dateien sind sehr groß, insbesondere die .myd-Datei, die fast 5 GB groß ist. Führen wir die erste Partitionsoptimierung durch. Es gibt vier Partitionierungsmethoden, die von MySQL unterstützt werden:

In unserem Projekt gibt es keine Verwendungsszenarien Bei der Bindung passen Sie die Nummer an, um eine Bereichs- oder Listenpartition zu erstellen. Die Bindungsnummer hat keine tatsächliche geschäftliche Bedeutung und kann nicht über sie abgefragt werden. Daher bleiben uns die HASH-Partition und die KEY-Partition, die nur Partitionen vom Typ int unterstützt. und ist eine davon.

KEY-Partitionierung kann mehrere Spalten unterstützen, erfordert aber auch, dass eine der Spalten vom Typ int sein muss. Wenn wir uns die Struktur unserer Bibliothekstabelle ansehen, stellen wir fest, dass keine der Spalten vom Typ int ist ? Fügen Sie eine Spalte hinzu, die Bindungszeitspalte, legen Sie diese Spalte auf den Typ int fest, unterteilen Sie sie dann entsprechend der Bindungszeit und teilen Sie die täglich gebundenen Benutzer in denselben Bereich auf.

Nach dieser Optimierung ist unser Einfügen viel schneller, aber die Abfrage ist immer noch sehr langsam. Warum?

Da wir bei der Abfrage nur die Bankkarten- oder ID-Nummer und nicht die Uhrzeit abfragen, entspricht dies jeder Abfrage. MySQL fragt alle Partitionstabellen ab.

Die zweite Lösungsoptimierung wird durchgeführt. Da für die HASH-Partitionierung und die KEY-Partitionierung eine der Spalten vom Typ int sein muss, ist es möglich, eine Liste vom Typ int für die Partitionierung zu erstellen.

Die Analyse ergab, dass in der Zahlenfolge der Bankkarte ein Geheimnis steckt. Bankkarten sind im Allgemeinen eine Zahlenfolge mit 16 bis 19 Ziffern. Ist es für uns möglich, eine der Zahlen als Tabellenpartition zu verwenden? Durch Analyse haben wir herausgefunden, dass eine davon in dieser Zahlenfolge enthalten ist Tatsächlich handelt es sich um eine Zufallszahl von 0 bis 9. Bei der Generierung führen wir eine KEY-Partitionierung basierend auf der Bankkartennummer + Zufallsziffern durch. Bei jeder Abfrage fangen wir die Zufallsziffern durch Berechnung ab, addieren die Kartennummer und führen gemeinsame Abfragen durch, um den Zweck zu erreichen Bei der Partitionsabfrage ist zu beachten, dass nach der Partitionierung der erstellte Index auch eine Partitionsspalte sein muss, andernfalls fragt MySQL weiterhin Daten in allen Partitionstabellen ab.

Das Problem der Überprüfung der Bindungsbeziehung anhand der Bankkartennummer wurde gelöst. Wie kann die Bindungsbeziehung anhand der ID-Nummer überprüft werden?

Wie bereits erwähnt, muss die Indizierung für den Partitionsschlüssel erfolgen, andernfalls wird ein vollständiger Tabellenscan durchgeführt. Wir haben eine neue Tabelle erstellt, um die ID-Nummernbindungsbeziehung jedes Kunden zu speichern. In der neuen ID-Nummernbindungsbeziehungstabelle wird die ID-Nummer als Primärschlüssel verwendet. Die Dokumenteninformationen des Kunden sind relativ komplex, einschließlich Ausweisnummer, Pass für Hongkong, Macao und Taiwan, Führerschein für Kraftfahrzeuge usw. So finden Sie den Partitionsschlüssel in der ungeordneten Dokumentennummer.

Um dieses Problem zu lösen, teilen wir die ID-Nummern-Bindungsbeziehungstabelle in zwei Tabellen auf und speichern andere ID-Nummerntypen im Dokument In der Bindungsbeziehungstabelle des ID-Kartentyps teilen wir die Monatsnummer in die ID-Nummer als Partitionsschlüssel auf und speichern die ID-Nummer des im selben Monat geborenen Kunden im selben Bereich und teilen sie so in 12 auf. Für jeden Bereich, Wenn das Datenvolumen anderer Zertifikatstypen 100.000 nicht überschreitet, ist keine Partitionierung erforderlich.

Auf diese Weise bestimmen Sie bei jeder Abfrage zunächst anhand des Zertifikattyps, welche Tabelle abgefragt werden soll, und berechnen dann den Partitionsschlüssel für die Abfrage. Nachdem das Partitionsdesign erstellt wurde, wurde beim Speichern von 20 Millionen Benutzerdaten die Datenspeicherdatei der Bankkartentabelle in 10 kleine Dateien und die Datenspeicherdatei der Zertifikatstabelle in 12 kleine Dateien unterteilt. Dadurch wurden die beiden gelöst Abfrageprobleme und auch eine Frage bleibt: Wohin mit der Geschäftsnummer?

Ein Kunde hat mehrere vertraglich vereinbarte Leistungen, wie kann man diese speichern? Derzeit ist die Verwendung einer Partitionierungslösung nicht sinnvoll. Es ist eine Tabellenpartitionierungslösung erforderlich.

Tabellen

Wir haben bereits erwähnt, dass die Datendateien von MySQL in Form von Dateien auf der Festplatte gespeichert werden. Wenn eine Datendatei zu groß ist, ist die Verarbeitung der großen Datei durch das Betriebssystem mühsam und zeitaufwändig, und einige Betriebssysteme unterstützen keine großen Dateien. Zu diesem Zeitpunkt muss die Tabelle geteilt werden.

Darüber hinaus ist Innodb die häufig verwendete Speicher-Engine für MySQL und die zugrunde liegende Datenstruktur ist der B+-Baum. Wenn die Datendatei zu groß ist, kann die Abfrage eines Knotens viele Ebenen abfragen, was unweigerlich dazu führt, dass mehrere E/A-Vorgänge in den Speicher geladen werden, was definitiv zeitaufwändig ist.

Darüber hinaus gibt es den Verriegelungsmechanismus von Innodb für B+-Bäume. Wenn jeder Knoten gesperrt ist, wird der Baum gesperrt, wenn die Tabellenstruktur geändert wird. Wenn die Tabellendatei groß ist, kann dies als nicht erreichbar angesehen werden. Zusammenfassend müssen wir also die Operationen der Untertabelle und der Unterdatenbank ausführen.

So führen Sie das Sharding von Datenbanken und Tabellen durch. Zu den bekannteren Lösungen gehören: Alibabas TDDL, DRDS und Cobar sowie das Sharding-JDBC von JD Finance ' MyCAT; 360's Atlas; Meituan's Zebra; andere Unternehmen wie NetEase, 58, JD.com und andere Unternehmen haben selbst entwickelte Middleware.

So viele Subdatenbank- und Tabellen-Middleware-Lösungen lassen sich in zwei Kategorien zusammenfassen: Client-Modus und Proxy-Modus.

Client-Modus

Proxy-Modus

Ob Client-Modus oder Proxy-Modus. Mehrere Kernschritte sind gleich: SQL-Analyse, Umschreiben, Routing, Ausführung und Ergebniszusammenführung. Persönlich bevorzuge ich den Client-Modus. Er hat eine einfache Architektur, relativ geringe Leistungsverluste und geringe Betriebs- und Wartungskosten.

So unterteilen Sie die Geschäftstypen in Datenbanken und Tabellen. Der wichtigste Schritt beim Sharding von Datenbanken und Tabellen ist die Auswahl der Sharding-Spalten. Die Qualität der Sharding-Spaltenauswahl bestimmt direkt, ob das gesamte Datenbank-Sharding- und Tabellen-Sharding-Schema letztendlich erfolgreich ist. Die Auswahl der Sharding-Spalte hängt stark vom Geschäft ab.

In unserem Projektszenario ist die Geschäftsnummer zweifellos die beste Wahl für die Sharding-Spalte. Über die Geschäftsnummer werden verschiedene verbindliche Vertragsleistungen des Kunden in unterschiedlichen Tabellen gespeichert und entsprechend der Geschäftsnummer zur Abfrage an die entsprechende Tabelle weitergeleitet, um so die SQL weiter zu optimieren.

Weitere PHP-Kenntnisse finden Sie im PHP-Tutorial!

Das obige ist der detaillierte Inhalt vonDetaillierte Erklärung, wie PHP Dutzende Millionen Tabellen in MySQL optimiert. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!