Detaillierte Erläuterung mehrerer praktischer Lösungen zum schnellen Löschen großer Datenmengen (zig Millionen) in MySQL

Der Autor stieß kürzlich bei seiner Arbeit auf ein Leistungsengpassproblem. Der MySQL-Tabelle werden täglich etwa 7,76 Millionen neue Datensätze hinzugefügt, und die Speicherdauer beträgt 7 Tage. Daten, die älter als 7 Tage sind, müssen gealtert werden, bevor neue Datensätze hinzugefügt werden. Nach einem Betrieb an 9 aufeinanderfolgenden Tagen dauert das Löschen der Daten eines Tages etwa 3,5 Stunden (Umgebung: 128 G, 32 Kerne, 4 T-Festplatte), was inakzeptabel ist. Wenn Sie die gesamte Tabelle löschen möchten, verwenden Sie natürlich

Einfach TRUNCATE TABLE.

Die erste Lösung (da nicht erwartet wurde, dass das Löschen so langsam sein würde) ist wie folgt (die einfachste und naivste Methode):

Löschen aus Tabellenname, wobei cnt_date <= Zieldatum

Nach weiteren Recherchen haben wir schließlich das Ziel erreicht, mehr als 7,7 Millionen Datensätze in blitzschneller Geschwindigkeit (ca. 1 Sekunde) zu löschen. Die Gesamtmenge der Daten in einer einzigen Tabelle betrug rund 46 Millionen. Der Optimierungsprozess wurde schichtweise durchgeführt und die Details werden wie folgt aufgezeichnet:

• Stapellöschen (jedes Mal auf eine bestimmte Zahl begrenzen) und dann in einer Schleife löschen, bis alle Daten gelöscht sind; gleichzeitig wird die Schlüsselpuffergröße von den standardmäßigen 8 MB auf 512 MB erhöht.

Operationseffekt: Die Löschzeit wurde von ca. 3,5 Stunden auf 3 Stunden erhöht

(1) Verwenden Sie ein Limit (die spezifische Größe sollte entsprechend festgelegt werden), um die auf einmal gelöschte Datenmenge zu begrenzen, und bestimmen Sie dann, ob die Daten gelöscht wurden. Der Quellcode lautet wie folgt (Python-Implementierung):

def delete_expired_data(mysqlconn, Tag):
 mysqlcur = mysqlconn.cursor()
 delete_sql = "LÖSCHEN aus table_name wobei cnt_date<='%s' Limit 50000" % Tag
 query_sql = "Wählen Sie srcip aus table_name, wobei cnt_date <= '%s' Limit 1" % Tag
 versuchen: 
  df = pd.read_sql(query_sql, mysqlconn)
  während True:
   wenn df None oder df.empty ist:
    brechen
   mysqlcur.execute(delete_sql)
   mysqlconn.commit()

   df = pd.read_sql(query_sql, mysqlconn)
 außer:
  mysqlconn.rollback()

(2) Erhöhen Sie die key_buffer_size

mysqlcur.execute("GLOBAL key_buffer_size festlegen = 536870912")

key_buffer_size ist eine globale Variable. Weitere Einzelheiten finden Sie in der offiziellen MySQL-Dokumentation: https://dev.mysql.com/doc/refman/5.7/en/server-configuration.html

• SCHNELL LÖSCHEN + OPTIMIERBAR

Anwendbares Szenario: MyISAM-Tabellen

Warum: MyISAM verwaltet gelöschte Daten in einer verknüpften Liste und die Leerzeichen und Zeilenpositionen werden später durch Einfügen von Daten wiederverwendet. Nach einem direkten Löschen führt MySQL die Indexblöcke zusammen, was das Kopieren und Verschieben einer großen Speichermenge erfordert, während OPTIMIZE TABLE den Index direkt neu erstellt, d. h. direkt eine neue Kopie des Datenblocks erstellt (denken Sie an den Garbage Collection-Algorithmus der JVM).

Operationseffekt: Die Löschzeit wurde von 3,5 Stunden auf 1 Stunde und 40 Minuten erhöht

Der spezifische Code lautet wie folgt:

def delete_expired_data(mysqlconn, Tag):
 mysqlcur = mysqlconn.cursor()
 delete_sql = "SCHNELL LÖSCHEN aus Tabellenname, wobei cnt_date<='%s' Limit 50000" % Tag
 query_sql = "Wählen Sie srcip aus table_name, wobei cnt_date <= '%s' Limit 1" % Tag
 optimize_sql = "Tabelle g_visit_relation_asset optimieren"
 versuchen: 
  df = pd.read_sql(query_sql, mysqlconn)
  während True:
   wenn df None oder df.empty ist:
    brechen
   mysqlcur.execute(delete_sql)
   mysqlconn.commit()

   df = pd.read_sql(query_sql, mysqlconn)
  mysqlcur.execute(optimize_sql)
  mysqlconn.commit()
 außer:
  mysqlconn.rollback()

• Tabellenpartition, Partition mit Ablaufdatum direkt löschen (endgültige Lösung - Flash-Sale)

Es gibt mehrere Möglichkeiten, eine MySQL-Tabelle zu partitionieren, darunter RANGE, KEY, LIST und HASH. Einzelheiten finden Sie in der offiziellen Dokumentation. Da sich das Datum des Anwendungsszenarios hier ändert, ist es nicht geeignet, RANGE zu verwenden, um einen festen Partitionsnamen festzulegen. Die HASH-Partition ist für dieses Szenario besser geeignet.

(1) Partitionstabellendefinition, die SQL-Anweisung lautet wie folgt:

ALTER TABLE Tabellenname PARTITION BY HASH(TO_DAYS(cnt_date)) PARTITIONS 7;

TO_DAYS wandelt das Datum (muss ein Datumstyp sein, sonst wird ein Fehler gemeldet: Konstante, zufällige oder zeitzonenabhängige Ausdrücke in der (Unter-)Partitionierungsfunktion sind nicht zulässig) in Tage (die Gesamtzahl der Tage im Jahr, Monat und Tag) und anschließend HASH um; erstellt 7 Partitionen. Eigentlich sind es Tage MOD 7.

(2) Fragen Sie die Partition ab, in der sich das zu alternde Datum befindet. Die SQL-Anweisung lautet wie folgt:

"Partitionen erklären select * from g_visit_relation_asset where cnt_date = '%s'" % expired_day

Die Ausführungsergebnisse sind wie folgt (die Spalte „Partitionen“ gibt die Partition an):

+----+----------+------------------+---------+------+----------------+------+---------+------+---------+----------+----------+-------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+------------------+---------+------+----------------+------+---------+------+---------+----------+----------+-------------+
| 1 | SIMPLE | Tabellenname | p1 | ALL | cnt_date_index | NULL | NULL | NULL | 1325238 | 100,00 | Verwenden von „where“ |
+----+----------+------------------+---------+------+----------------+------+---------+---------+---------+----------+----------+-------------+
1 Zeile im Satz, 2 Warnungen (0,00 Sek.)

(3) OPTIMIZE oder REBUILD der Partition. Die SQL-Anweisung lautet wie folgt:

"ALTER TABLE g_visit_relation_asset OPTIMIZE PARTITION '%s'" % Partition

Der vollständige Code sieht wie folgt aus [Python-Implementierung] und durchläuft eine Schleife, in der Daten gelöscht werden, die vor dem angegebenen Datum liegen:

def clear_partition_data(mysqlconn, Tag):
 mysqlcur = mysqlconn.cursor()
 abgelaufener_Tag = Tag
 query_partition_sql = "Partitionen erklären select * from table_name where cnt_date = '%s'" % expired_day
 # OPTIMIEREN oder NEU ERSTELLEN nach Partitionsabschneiden
 versuchen: 
  während True:
   df = pd.read_sql(query_partition_sql, mysqlconn)
   wenn df None oder df.empty ist:
    brechen
   partition = df.loc[0, 'partitions']
   wenn die Partition nicht Keine ist:
    clear_partition_sql = "Tabelle ändern Tabellenname PARTITION TRUNCATE %s" % Partition
    mysqlcur.execute(clear_partition_sql)
    mysqlconn.commit()

    optimize_partition_sql = "ALTER TABLE Tabellenname OPTIMIEREN PARTITION %s" % Partition
    mysqlcur.execute(partition_sql_optimieren)
    mysqlconn.commit()
   
   abgelaufener_Tag = (abgelaufener_Tag - Zeitdelta(Tage = 1)).strftime("%Y-%m-%d")
   df = pd.read_sql(query_partition_sql, mysqlconn)
 außer:
  mysqlconn.rollback()

• andere

Wenn die gelöschten Daten 50 % der Tabellendaten überschreiten, wird empfohlen, die erforderlichen Daten in eine temporäre Tabelle zu kopieren, dann die ursprüngliche Tabelle zu löschen und die temporäre Tabelle dann in die ursprüngliche Tabelle umzubenennen. MySQL lautet wie folgt:

 INSERT INTO Neu
  Wählen Sie * aus Main
   WHERE ...; -- nur die Zeilen, die Sie behalten möchten
 BENENNEN SIE DIE TABELLE „main“ IN „Alt“, „Neu“ IN „Main“ UM;
 DROP TABLE Alt; -- Hier wurde Platz freigegeben

Sie können Partitionen löschen, indem Sie: ALTER TABLE table_name REMOVE PARTITIONING ausführen, ohne die entsprechenden Daten zu löschen.

siehe:

1) https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html Spezifische Partitionierungsanweisungen

2) http://mysql.rjweb.org/doc.php/deletebig#solutions Lösungen zum Löschen großer Daten

Das Urheberrecht dieses Artikels liegt beim Autor und bei Blog Garden. Nachdrucke sind willkommen, allerdings muss diese Erklärung ohne Zustimmung des Autors beibehalten werden und der Originallink muss an prominenter Stelle auf der Artikelseite angegeben werden. Andernfalls behalten wir uns das Recht vor, rechtliche Schritte einzuleiten.

************************************************************************

Die Energie ist begrenzt und es gibt zu viele Ideen. Konzentrieren Sie sich darauf, eine Sache gut zu machen.

Ich bin nur ein Programmierer. Schreiben Sie innerhalb von 5 Jahren guten Code, polieren Sie jedes Wort technischer Blogs auf und bestehen Sie auf Null-Kopie und Originalität. Die Bedeutung des Bloggens besteht darin, Ihren Schreibstil zu polieren, Ihre Logik und Ordnungssinn zu trainieren und Ihr systematisches Verständnis von Wissen zu vertiefen. Wenn es anderen hilft, ist es wirklich eine glückliche Sache.

Damit ist dieser Artikel über mehrere praktische Lösungen zum schnellen Löschen großer Datenmengen (zig Millionen) aus MySQL abgeschlossen. Weitere Informationen zum schnellen Löschen großer Datenmengen aus MySQL finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!