Eine kurze Erläuterung zum eleganten Löschen großer Tabellen in MySQL

Mit der Zeit oder mit zunehmendem Geschäftsvolumen steigt die Auslastung des Datenbankspeicherplatzes stetig an. Wenn der Datenbankspeicherplatz einen Engpass erreicht, stellen wir möglicherweise fest, dass die Datenbank eine oder zwei sehr große Tabellen enthält! Sie haben vom Beginn des Geschäfts bis heute alle Daten gesammelt, aber 90 % der Daten haben keinen Geschäftswert. Wie sollen sie zu diesem Zeitpunkt mit diesen großen Tabellen umgehen?

Da es sich um wertlose Daten handelt, löschen wir sie normalerweise direkt oder nach der Archivierung. Die Vorgehensweise zum Löschen von Daten kann in zwei Kategorien unterteilt werden:

• Löschen Sie alle Daten in der Tabelle direkt durch Truncate
• Löschen Sie durch Löschen die Datensätze in der Tabelle, die die Bedingungen erfüllen

1. Truncate-Operation

Logisch gesehen löscht der Truncate-Vorgang alle Zeilen in der Tabelle, er unterscheidet sich jedoch vom Vorgang „Löschen aus Tabellenname, wobei 1=1 ist“. Um die Leistung beim Löschen der gesamten Tabellendaten zu verbessern, besteht die Truncate-Operation von MySQL tatsächlich darin, zuerst die Tabelle zu löschen und sie dann neu zu erstellen. Aus diesem Grund handelt es sich bei der Truncate-Operation um eine DDL-Operation ohne Rollback.

1.1 Welche Operationen führt MySQL Truncate aus?

• Der Truncate-Vorgang ist eigentlich in zwei Schritte unterteilt: Löschen und Neuerstellen
• Die erste Phase des Löschvorgangs besteht darin, die Pufferpoolseiten zu löschen und die tabellenbezogenen Datenseiten aus der Flush-Kette zu löschen, ohne dass ein Flush-Vorgang erforderlich ist. Der Engpass dieses Schritts besteht darin, dass der Löschvorgang der Flush-Warteschlange die Sperre der entsprechenden Pufferpoolinstanz halten und eine Durchlaufsuche durchführen muss. Wenn die Pufferpoolinstanz groß ist und in der Flush-Kette viele zu löschende Datenseiten vorhanden sind, führt dieser Vorgang dazu, dass andere Transaktionen beim Erwerb der Sperre der Pufferpoolinstanz blockiert werden, was sich auf die Leistung der Datenbank auswirkt.
• Die zweite Phase des Drop-Vorgangs ist das Löschen der IBD-Datenträgerdatei. Je größer die physische Datei der Datenbank ist, desto höher ist der E/A-Ressourcenverbrauch und desto länger dauert der Löschvorgang.
• Während der Neuerstellungsphase kann die Tabelle nach dem Löschen der gelöschten Tabelle gemäß den ursprünglichen Tabellenstrukturinformationen neu erstellt werden, solange die .frm-Datei der gelöschten Tabelle intakt ist. Der auto_increment-Wert der neu erstellten Tabelle wird zurückgesetzt.

1.2 Wie kann der durch den Truncate-Vorgang verursachte Ressourcenverbrauch optimiert werden?

• Wenn in der ersten Phase der Drop-Tabelle im Truncate-Vorgang die der MySQL-Instanz zugewiesene innodb_buffer_pool_size 1 GB überschreitet, legen Sie den Parameter innodb_buffer_pool_instances entsprechend fest, um die Parallelität zu verbessern und gleichzeitig die zum Sperren von Ressourcen beim Scannen der Pufferpoolinstanz aufgewendete Zeit zu reduzieren.
• Für die zweite Phase der Löschtabelle im Truncate-Vorgang wird vor dem Löschen der entsprechenden Tabelle ein Hardlink für die .ibd-Datei der geänderten Tabelle erstellt, um die Ausführungseffizienz des Löschvorgangs auf MySQL-Ebene zu beschleunigen und Leistungsverluste auf Datenbankebene zu verringern. Bereinigen Sie dann manuell die Hardlinks, die wir auf Betriebssystemebene erstellt haben

2. Löschvorgang

2.1 Welche Vorgänge führt MySQL Delete aus?

• Führen Sie einen Index-/Volltabellenscan für die Löschtabelle basierend auf der Where-Bedingung durch, um zu prüfen, ob die Where-Bedingung erfüllt ist. In dieser Phase werden alle Zeilen im Scan gesperrt. In dieser Phase besteht die größte versteckte Gefahr des Ressourcenverbrauchs. Wenn die Tabelle eine große Datenmenge enthält und der Löschvorgang den Index nicht effektiv nutzen kann, um die Menge der gescannten Daten zu reduzieren, führt dieser Schritt zu enormen Sperrkonflikten und einem hohen CPU-/IO-Ressourcenverbrauch für die Datenbank.
• Die Sperren für die Zeilen, die nicht mit der Where-Bedingung übereinstimmen, werden nach Überprüfung der Bedingung aufgehoben und InnoDB sperrt nur die Zeilen, die gelöscht werden müssen. Dadurch können Sperrkonflikte effektiv reduziert werden. Wir müssen jedoch noch darauf achten, dass durch das gleichzeitige Löschen einer großen Datenmenge ein riesiges Binlog-Transaktionsprotokoll generiert wird, das weder für MySQL selbst noch für die Slave-Datenbank in der Master-Slave-Architektur geeignet ist und zu einer langen Replikationsverzögerung führen kann.

2.2 Wie lassen sich Löschvorgänge optimieren?

• Beim Löschen aller Tabellen müssen Sie vorsichtig sein. Sie können die Verwendung der Truncate-Operation in Betracht ziehen.
• Bei „delete … where …“ sollte die Where-Filterbedingung sicherstellen, dass der Index effektiv genutzt werden kann, um die Menge des Datenscans zu reduzieren und einen vollständigen Tabellenscan zu vermeiden.
• Beim Löschen großer Datenmengen und wenn die Bedingungen keinen Index haben, kann der Löschvorgang einen zusätzlichen automatisch inkrementierenden Primärschlüssel oder ein indiziertes Zeitfeld hinzufügen, um Löschvorgänge im Stapelbetrieb durchzuführen. Dabei wird jedes Mal eine kleine Datenmenge gelöscht und die Vorgänge werden in mehreren Stapeln ausgeführt.
• Für das klassische Szenario, bei dem aktuelle Daten beibehalten und historische Daten gelöscht werden, können Sie eine xxx_tmp-Tabelle mit derselben Struktur erstellen und die Operation insert xxx_tmp select ... verwenden, um die erforderlichen Daten in der tmp-Tabelle beizubehalten. Verwenden Sie dann die Operation rename, um die aktuelle Geschäftstabelle xxx durch die Tabelle xxx_bak zu ersetzen, und ersetzen Sie die Tabelle xxx_tmp durch den aktuellen Geschäftstabellennamen xxx. Löschen Sie dann manuell die nutzlose große Tabelle xxx_bak.

2.3 Zwei häufige Löschszenarien

2.3.1 Löschen, wenn die Bedingung keine gültige Indexfilterung hat

Ein häufiges Szenario ist, dass das Unternehmen den Wert von t1 condition1=xxx löschen muss. Das Bedingungsfeld kann den Index nicht effektiv verwenden. In diesem Fall gehen wir normalerweise wie folgt vor:

• Überprüfen Sie die Indizes, die in der aktuellen Tabellenstruktur effektiv verwendet werden können, und versuchen Sie, den selbstinkrementierenden Primärschlüssel oder das Zeitindexfeld der Tabelle zu verwenden
• Nutzen Sie den selbstinkrementierenden Primärschlüsselindex oder Zeitindex effektiv, fügen Sie dem Löschvorgang eine Bereichsfilterung des Indexfelds hinzu, löschen Sie jedes Mal eine kleine Datenmenge und führen Sie ihn in mehreren Stapeln aus. Die spezifische Stapelverarbeitung muss anhand der tatsächlichen Geschäftssituation ausgewertet werden, um zu vermeiden, dass große Datenmengen auf einmal gelöscht werden.

-- Verwenden Sie den selbstinkrementierenden Primärschlüsselindex, um aus t1 zu löschen, wobei Bedingung1=xxx und ID >=1 und ID < 50000;
Löschen Sie aus t1, wobei Bedingung1=xxx und ID >=50000 und ID < 100000;

-- Verwenden Sie den Zeitindex zum Löschen ab t1, wobei Bedingung1=xxx und Erstellungszeit >= ‚2021-01-01 00:00:00‘ und Erstellungszeit < ‚2021-02-01 00:00:00‘;
Löschen aus t1, wobei Bedingung1=xxx und Erstellungszeit >= ‚2021-02-01 00:00:00‘ und Erstellungszeit < ‚2021-03-01 00:00:00‘;

2.3.2 Aktuelle Daten behalten und historische Daten löschen

Ein häufiges Szenario ist, dass Sie nur die Daten der Tabelle t1 der letzten drei Monate behalten und den Rest der historischen Daten löschen müssen. Unser üblicher Ansatz ist:

Erstellen Sie eine t1_tmp-Tabelle, um Daten, die aufbewahrt werden müssen, vorübergehend zu speichern

Erstellen Sie Tabelle t1_tmp wie t1.

Schreiben Sie die beizubehaltenden Daten gemäß dem indizierten Zeitfeld stapelweise in die Tabelle t1_tmp. Es ist zu beachten, dass der Vorgang des letzten Zeitstapels vorübergehend ignoriert werden kann.

-- Teilen Sie die Daten entsprechend der Anzahl der Instanzunternehmen in Stapel auf und versuchen Sie, in jedem Stapel nicht zu viele Daten zu verarbeiten. Einfügen in t1_tmp. Auswählen * von t1, wobei create_time >= '2021-01-01 00:00:00' und create_time < '2021-02-01 00:00:00';
einfügen in t1_tmp, auswählen * aus t1, wobei create_time >= '2021-02-01 00:00:00' und create_time < '2021-03-01 00:00:00';

-- Der letzte Datenstapel wird noch nicht verarbeitet -- insert into t1_tmp select * from t1 where create_time >= '2021-03-01 00:00:00' and create_time < '2021-04-01 00:00:00';

Verwenden Sie den Umbenennungsvorgang, um die aktuelle Geschäftstabelle t1 durch die Tabelle t1_bak und die Tabelle t1_tmp durch den aktuellen Geschäftstabellennamen t1 zu ersetzen. Wenn häufig DML-Vorgänge auf die gelöschte Tabelle angewendet werden, führt dieser Schritt zu einem kurzfristigen Geschäftszugriffsfehler.

Tabelle t1 ändern und in t1_bak umbenennen;
Tabelle t1_tmp ändern und in t1 umbenennen;

Schreiben Sie den letzten Datensatz in die aktuelle Geschäftstabelle. Der Zweck dieses Schritts besteht darin, den Datenverlust während des Änderungsvorgangs zu reduzieren.

in t1 einfügen, auswählen * aus t1_bak, wobei create_time >= '2021-03-01 00:00:00' und create_time < '2021-04-01 00:00:00';

Im Umbenennungsvorgang müssen wir außerdem darauf achten, ob der Primärschlüssel der Änderungstabelle selbstinkrementierend oder die unternehmensspezifische UUID ist. Wenn es sich um einen selbstinkrementierenden Primärschlüssel handelt, müssen wir auch darauf achten, den selbstinkrementierenden Wert der Tabelle t1_tmp zu ändern, um sicherzustellen, dass der endgültige Einstellungswert die während des Änderungszeitraums geschriebenen Daten enthält.

alter table t1_tmp auto_increment={aktueller Autowert der T1-Tabelle}+{geschätzter Wachstumswert während des Änderungszeitraums}

III. Vergleich der Vor- und Nachteile von Truncate/Delete

Dies ist das Ende dieses Artikels zum eleganten Löschen großer Tabellen in MySQL. Weitere Informationen zum Löschen großer Tabellen in 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!