Eine kurze Diskussion über die MySQL-Optimierungslösung für große Tabellen

Hintergrund

Die Menge neuer Daten in der Geschäftstabelle der Alibaba Cloud RDS für MySQL-Datenbank (MySQL Version 5.7) übersteigt 10 Millionen pro Monat. Da die Datenmenge weiter zunimmt, sind in unserem Geschäft die Abfragen großer Tabellen langsam. Während der Hauptgeschäftszeiten dauern langsame Abfragen der Hauptgeschäftstabelle mehrere zehn Sekunden, was das Geschäft ernsthaft beeinträchtigt.

Programmübersicht

1. Datenbankdesign und Indexoptimierung

Die MySQL-Datenbank selbst ist sehr flexibel, was zu unzureichender Leistung führt, die stark vom Tabellendesign und den Indexoptimierungsfähigkeiten des Entwicklers abhängt. Hier sind einige Optimierungsvorschläge

• Konvertieren Sie den Zeittyp in das Zeitstempelformat, speichern Sie ihn im Int-Typ und erstellen Sie einen Index, um die Abfrageeffizienz zu erhöhen
• Es wird empfohlen, das Feld als ungleich null zu definieren. Nullwerte sind schwer abzufragen und belegen zusätzlichen Indexplatz.
• Verwenden Sie den Typ TINYINT anstelle von ENUM
• Um genaue Gleitkommazahlen zu speichern, muss DECIMAL anstelle von FLOAT und DOUBLE verwendet werden.
• Die Länge des Feldes sollte den geschäftlichen Anforderungen entsprechen. Wählen Sie keinen zu großen Wert.
• Versuchen Sie, den Typ TEXT nicht zu verwenden. Wenn Sie ihn verwenden müssen, empfiehlt es sich, die ungewöhnlich großen Felder in andere Tabellen aufzuteilen.
• MySQL hat eine Beschränkung der Länge von Indexfeldern. Die Standardbeschränkung für die Länge jeder Indexspalte der InnoDB-Engine beträgt 767 Byte. Die Gesamtlänge aller Indexspalten kann 3072 Byte nicht überschreiten (ein einzelner Index von MySQL 8.0 kann 1024 Zeichen erstellen).
• Bitte wenden Sie sich an den DBA, wenn DDL für große Tabellen erforderlich ist
  

Übereinstimmungsregel für den Index ganz links

Wie der Name schon sagt, ganz links zuerst. Beim Erstellen eines zusammengesetzten Index sollte die am häufigsten verwendete Spalte in der Where-Klausel je nach Geschäftsanforderungen ganz links platziert werden. Eine sehr wichtige Frage bei zusammengesetzten Indizes ist, wie die Reihenfolge der Spalten angeordnet werden soll. Wenn beispielsweise zwei Felder, c1 und c2, nach wo verwendet werden, sollte die Indexreihenfolge dann (c1, c2) oder (c2, c1) sein? Der richtige Ansatz besteht darin, Spalten mit weniger doppelten Werten an den Anfang zu stellen. Wenn beispielsweise 95 % der Werte in einer Spalte nicht doppelt vorhanden sind, kann diese Spalte im Allgemeinen an den Anfang gestellt werden.

• Zusammengesetzter Index index(a,b,c)
• wobei a=3 nur a verwendet wird
• wobei a=3 und b=5 a,b verwendet
• wobei a=3 und b=5 und c=4 a,b,c verwendet
• wobei b=3 oder c=4 den Index nicht verwenden
• wobei a=3 und c=4 nur a verwendet wird
• wobei a=3 und b>10 und c=7 verwendet a,b
• wobei a=3 und b wie 'xx%' und c=7 a,b verwendet
• Tatsächlich entspricht dies dem Erstellen mehrerer Indizes: Schlüssel (a), Schlüssel (a, b), Schlüssel (a, b, c).

2. Stellen Sie die Datenbank auf PloarDB-Lese-/Schreibtrennung um

PolarDB ist die von Alibaba Cloud selbst entwickelte relationale Cloud-Datenbank der nächsten Generation, die 100 % mit MySQL kompatibel ist und eine Speicherkapazität von bis zu 100 TB hat. Eine einzelne Datenbank kann auf bis zu 16 Knoten erweitert werden, wodurch sie für eine Vielzahl von Datenbankanwendungsszenarien in Unternehmen geeignet ist. PolarDB verwendet eine Architektur, die Speicher und Datenverarbeitung trennt. Alle Datenverarbeitungsknoten teilen sich eine Kopie der Daten und bieten minutengenaue Konfigurationsupgrades und -upgrades, Fehlerbehebung auf zweiter Ebene, globale Datenkonsistenz sowie kostenlose Datensicherungs- und Notfallwiederherstellungsdienste.

Cluster-Architektur, Trennung von Computing und Storage
PolarDB verwendet eine Clusterarchitektur mit mehreren Knoten. Im Cluster gibt es einen Writer-Knoten (Masterknoten) und mehrere Reader-Knoten (Nur-Lese-Knoten). Jeder Knoten teilt den zugrunde liegenden Speicher (PolarStore) über ein verteiltes Dateisystem (PolarFileSystem).

Trennung von Lesen und Schreiben Wenn eine Anwendung eine Clusteradresse verwendet, stellt PolarDB externe Dienste über eine interne Proxy-Schicht (Proxy) bereit. Die Anforderungen der Anwendung durchlaufen zuerst den Proxy, bevor sie auf den Datenbankknoten zugreifen. Die Proxy-Schicht kann nicht nur Sicherheitsauthentifizierung und -schutz durchführen, sondern auch SQL analysieren, Schreibvorgänge (wie Transaktionen, UPDATE, INSERT, DELETE, DDL usw.) an den Masterknoten senden und Lesevorgänge (wie SELECT) gleichmäßig auf mehrere schreibgeschützte Knoten verteilen, um eine automatische Lese-/Schreibtrennung zu erreichen. Für Anwendungen ist es so einfach wie die Verwendung einer einzelnen Punktdatenbank.

In Offline-Hybrid-Szenarien: Verschiedene Dienste verwenden unterschiedliche Verbindungsadressen und unterschiedliche Datenknoten, um gegenseitige Auswirkungen zu vermeiden

Bericht zum Sysbench-Leistungsstresstest:

PloarDB 4 Kerne 16G 2 Einheiten

PloarDB 8 Kerne 32G 2 Einheiten

3. Migrieren Sie historische Daten aus Shard-Tabellen zur MySQL 8.0 X-Engine-Speicher-Engine

Die Geschäftstabelle speichert Daten für drei Monate (dies richtet sich nach den Anforderungen des Unternehmens), und die historischen Daten werden in Monatstabellen in die Speichertabelle der historischen Datenbank X-Engine aufgeteilt. Warum sollten wir uns für die Speichertabelle der X-Engine entscheiden? Was sind ihre Vorteile?

Kosteneinsparungen: Die Speicherkosten von X-Engine betragen etwa die Hälfte der von InnoDB

Der mehrstufige Speicher von X-Engine verbessert die QPS. Er verwendet eine hierarchische Speicherstruktur, um heiße und kalte Daten in verschiedenen Schichten zu speichern, und komprimiert standardmäßig die Schicht, in der sich kalte Daten befinden.

X-Engine ist eine Datenbankspeicher-Engine für die Online-Transaktionsverarbeitung (OLTP), die von der Datenbankproduktabteilung von Alibaba Cloud entwickelt wurde.
Die X-Engine-Speicher-Engine ist nicht nur nahtlos mit MySQL kompatibel (dank der Funktion „MySQL Pluginable Storage Engine“), sondern verwendet auch eine mehrstufige Speicherarchitektur. Da das Ziel darin besteht, große, massive Datenmengen zu speichern, hoch parallele Transaktionsverarbeitungsfunktionen bereitzustellen und die Speicherkosten zu senken, wird in den meisten Szenarien mit großen Datenmengen ungleichmäßig auf die Daten zugegriffen, und häufig aufgerufene Hot Data machen tatsächlich nur einen kleinen Anteil aus. X-Engine unterteilt Daten basierend auf der Häufigkeit des Datenzugriffs in mehrere Ebenen. Basierend auf den Zugriffseigenschaften jeder Datenebene entwirft es eine entsprechende Speicherstruktur und schreibt sie auf ein geeignetes Speichergerät.

• X-Engine verwendet LSM-Tree als Architekturbasis für mehrstufigen Speicher und wurde wie folgt neu gestaltet:
• Die Hot-Data-Schicht und die Datenaktualisierungen nutzen den Arbeitsspeicher und die Leistung der Transaktionsverarbeitung wird durch die Speicherdatenbanktechnologie (Lock-Free-Indexstruktur/nur Anhängen) verbessert.
• Der Pipeline-Transaktionsverarbeitungsmechanismus führt mehrere Phasen der Transaktionsverarbeitung parallel aus und verbessert so den Durchsatz erheblich.
• Daten mit geringer Zugriffshäufigkeit werden sukzessive eliminiert oder in einer persistenten Speicherschicht zusammengeführt und im Verbund mit mehrstufigen Speichergeräten (NVM/SSD/HDD) gespeichert.
• Am Komprimierungsprozess wurden zahlreiche Optimierungen vorgenommen, die sich erheblich auf die Leistung auswirken:
• Teilen Sie die Datenspeichergranularität auf, nutzen Sie die Eigenschaften konzentrierter Datenaktualisierungs-Hotspots und verwenden Sie Daten während des Zusammenführungsprozesses so weit wie möglich erneut.
• Durch die detaillierte Steuerung der LSM-Form werden die E/A- und Rechenkosten gesenkt, wodurch der Platzbedarf während des Zusammenführungsprozesses effektiv verringert wird.
• Gleichzeitig werden feinere Zugriffskontroll- und Caching-Mechanismen eingesetzt, um die Leseleistung zu optimieren.
  

4. Parallele Abfrage der Alibaba Cloud PloarDB MySQL 8.0-Version

Nach der Aufteilung der Tabelle ist unser Datenvolumen immer noch groß, was unser Problem der langsamen Abfrage nicht vollständig löst, sondern nur die Größe unserer Geschäftstabelle verringert. Für diesen Teil der langsamen Abfrage müssen wir die parallele Abfrageoptimierung von PolarDB verwenden

PolarDB MySQL 8.0 hat ein paralleles Abfrageframework eingeführt. Wenn die Menge der abgefragten Daten einen bestimmten Schwellenwert erreicht, wird das parallele Abfrageframework automatisch gestartet, was die Abfragezeit exponentiell reduziert. Die Daten werden auf der Speicherebene in verschiedene Threads aufgeteilt, und mehrere Threads berechnen parallel. Die Ergebnispipeline wird zum Hauptthread aggregiert, und schließlich führt der Hauptthread einige einfache Zusammenführungen durch und gibt sie an den Benutzer zurück, wodurch die Abfrageeffizienz verbessert wird.
Parallel Query nutzt die parallelen Verarbeitungsfunktionen von Mehrkern-CPUs. Am Beispiel einer 8-Core-Konfiguration mit 32 GB wird unten das schematische Diagramm angezeigt.

Parallele Abfragen sind auf die meisten SELECT-Anweisungen anwendbar, z. B. auf große Tabellenabfragen, Verknüpfungsabfragen für mehrere Tabellen und Abfragen mit großem Rechenaufwand. Bei sehr kurzen Abfragen ist der Effekt weniger dramatisch.

Verwendung paralleler Abfragen: Die Hint-Syntax kann zur Steuerung einer einzelnen Anweisung verwendet werden. Wenn das System beispielsweise parallele Abfragen standardmäßig deaktiviert, aber eine langsame SQL-Abfrage mit hoher Frequenz beschleunigt werden muss, kann Hint zur Beschleunigung des spezifischen SQL verwendet werden.

Wählen Sie /+PARALLEL(x)/ … FROM …; – x > 0

AUSWÄHLEN /*+ SET_VAR(max_parallel_degree=n) */ * VON … // n > 0

Abfragetest: Datenbankkonfiguration 16 Kerne 32G Einzeltabelle Datenvolumen übersteigt 30 Millionen

Die Zeit vor der parallelen Abfrage betrug 4326 ms, und nachdem die parallele Abfrage hinzugefügt wurde, betrug sie 525 ms, was einer Leistungsverbesserung um das 8,24-fache entspricht.

5. Interaktive Analyse von Hologre

Obwohl wir durch die Nutzung der parallelen Abfrageoptimierung die Effizienz langsamer Abfragen bei großen Tabellen verbessert haben, können wir bestimmte Anforderungen wie Echtzeitberichte und große Echtzeitbildschirme immer noch nicht erfüllen und können uns bei der Verarbeitung nur auf Big Data verlassen.
Hier empfehle ich Alibaba Clouds interaktive Analyse Hologre (
https://help.aliyun.com/product/113622.html)

6. Nachtrag

Die Optimierung großer Tabellen mit mehreren zehn Millionen Daten basiert auf Geschäftsszenarien und wird auf Kosten der Kosten optimiert. Es geht nicht darum, die Datenbank sofort horizontal aufzuteilen und zu erweitern. Dies wird enorme Herausforderungen für Betrieb, Wartung und Geschäft mit sich bringen und die Auswirkungen sind in vielen Fällen möglicherweise nicht gut. Unabhängig davon, ob unser Datenbankdesign, unsere Indexoptimierung und unsere Tabellenpartitionierungsstrategie vorhanden sind, sollten wir die geeignete Technologie auswählen, um sie entsprechend den Geschäftsanforderungen umzusetzen.

Dies ist das Ende dieses Artikels über MySQL-Lösungen zur Optimierung großer Tabellen. Weitere relevante Inhalte zur Optimierung großer MySQL-Tabellen finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!