So nutzen Sie Partitionierung, um die MySQL-Datenverarbeitung für Milliarden von Daten zu optimieren

Wenn MySQL zig Millionen Daten abfragt, können die meisten Abfrageoptimierungsprobleme durch Indizes gelöst werden. Bei der Verarbeitung von Hunderten Millionen Daten sind Indizes jedoch nicht so benutzerfreundlich.

Die Datentabelle (Protokoll) sieht folgendermaßen aus:

• Tabellengröße: 1T, ungefähr 2,4 Milliarden Zeilen;
• Tabellenpartitionierung: Partitionierung nach Zeit, wobei jeder Monat eine Partition ist und eine Partition etwa 200–300 Millionen Datenzeilen (etwa 40–70 G) enthält.

Da die Daten nicht vollständig verarbeitet werden müssen, haben wir nach Gesprächen mit der Nachfrageseite einen Teil der Daten nach Zeiträumen abgetastet, beispielsweise die Daten eines Monats, also etwa 350 Millionen Zeilen.
Ideen zur Datenverarbeitung:

1) Wählen Sie Innodb als Tabellen-Engine. Da die Daten nach Monaten partitioniert sind, kopieren wir die Daten der monatlichen Partition separat. Die Quelltabelle ist die MyISAM-Engine. Da wir möglicherweise einige Daten filtern müssen und die am Filter beteiligten Felder keinen Index haben, ist das Hinzufügen von Indizes mit der MyISAM-Engine langsam.
2) Unterteilung nach Tagen. Nachdem Sie der kopierten Tabelle Indizes hinzugefügt haben (ca. 2–4 Stunden), filtern Sie unbrauchbare Daten heraus, generieren erneut eine neue Tabelle, extrahieren die erforderlichen Felder in JSON und partitionieren die Tabelle nach Tagen.

CREATE TABLE `tb_name` (
  `ich würde_`,
  ...,
  SCHLÜSSEL `idx_1` (`create_user_`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Anwendungsprotokoll'
PARTITION NACH BEREICH(bis_Tage(Logzeit_)) (
    PARTITION p1231 WERTE WENIGER ALS (737425),
    PARTITION p0101 WERTE KLEINER ALS (737426),
    PARTITION p0102 WERTE KLEINER ALS (737427),
    PARTITION p0103 WERTE KLEINER ALS (737428),
    PARTITION p0104 WERTE KLEINER ALS (737429),
......
);

3) Führen Sie täglich Aggregationen oder andere Vorgänge an der oben generierten Tabelle durch und speichern Sie die Ergebnisse in einer temporären Tabelle. Versuchen Sie, gespeicherte Prozeduren zur Datenverarbeitung zu verwenden. Da die Verarbeitung relativ komplex und zeitaufwändig ist (das Ausführen einer gespeicherten Prozedur dauert etwa 1-2 Stunden), sollten die Betriebszeit und die Parameter während des Ausführungsprozesses aufgezeichnet werden, wenn die gespeicherte Prozedur zyklisch aufgerufen wird.

Trennzeichen $$
Prozedur proc_name erstellen(param varchar(50))
beginnen
 deklarieren Sie das Startdatum;
    Enddatum deklarieren;
    Setze Startdatum = „31.12.2018“;
    Enddatum festlegen = „01.02.2019“;
    
    Transaktion starten;
 tmp_talbe abschneiden;
 begehen;
    
    während Startdatum < Enddatum
  Setzen Sie @Partitionsname = Datumsformat (Startdatum, „%m%d“);
        setze @start_time = now(); – Zeichnet die Startzeit der aktuellen Partitionsoperation auf, um die Transaktion zu starten.
  setze @sqlstr = concat(
   "in tmp_talbe einfügen",
   "Feldnamen auswählen",
            "von tb_name partition(p", @partition_name,") t ",
            "wo Bedingungen;"
   );
  -- wähle @sqlstr;
  Anweisung aus @sqlstr vorbereiten;  
  Anweisung ausführen;
  Freigabe, Vorbereitungs-STMT;
  begehen;
        
        -- Protokollsatz einfügen @finish_time = now(); -- Endzeit der Operation in oprerate_log-Werte einfügen (Param, @partition_name, @start_time, @finish_time, timestampdiff(Sekunde, @start_time, @finish_time));
        
  setze Startdatum = Date_Add(Startdatum, Intervall 1 Tag);
    Ende während;
Ende
$$
Trennzeichen ;

4) Sortieren und verarbeiten Sie die oben generierten Ergebnisse.

Im Allgemeinen ist die Verarbeitung relativ umständlich und erzeugt viele Zwischentabellen. Für wichtige Schritte müssen auch Metadaten des Betriebsvorgangs aufgezeichnet werden, was hohe Anforderungen an die SQL-Verarbeitung stellt. Daher wird nicht empfohlen, MySQL für diese Aufgabe zu verwenden (es sei denn, dies ist unbedingt erforderlich). Wenn die Verarbeitung auf einer Big-Data-Plattform erfolgen kann, ist die Geschwindigkeit höher und die Metadatenverwaltung relativ professionell.

Dies ist das Ende dieses Artikels zur Verwendung von Partitionen zur Handhabung der MySQL-Datenoptimierung auf Milliardenebene. Weitere relevante Inhalte zur MySQL-Datenoptimierung auf Milliardenebene finden Sie in den vorherigen Artikeln von 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!