Beispiel zum schnellen Löschen einer 2T-Tabelle in MySQL in Innodb

Vorwort

Dieser Artikel stellt hauptsächlich den relevanten Inhalt zum schnellen Löschen einer 2T großen Tabelle in MySQL in Innodb vor. Er wird zu Ihrer Information und zum Lernen freigegeben. Werfen wir einen Blick auf die ausführliche Einführung.

Kommen Sie, schauen wir uns die Comics an, um unsere Gefühle zu kultivieren.

OK, das ist alles. Angenommen, Sie haben eine Tabelle ERP, wenn Sie den folgenden Befehl direkt ausführen

Drop-Tabelle ERP

Zu diesem Zeitpunkt werden alle MySQL-bezogenen Prozesse angehalten, bis der Löschvorgang abgeschlossen ist. Anschließend wird die Ausführung von MySQL fortgesetzt. Der Grund hierfür ist, dass InnoDB beim Löschen der Tabelle eine globale Sperre aufrechterhält und die Sperre nach Abschluss des Löschens aufgehoben wird.

Dies bedeutet, dass, wenn Sie tagsüber bei sehr hohem Datenverkehr den Befehl zum Löschen einer großen Tabelle ausführen, ohne etwas zu unternehmen, der gesamte MySQL-Server hängen bleibt. Während der Tabellenlöschung sinkt die QPS stark, und dann kommt der Produktmanager zum Tee zu Ihnen. Aus diesem Grund gibt es im Comic eine Szene, die man um zwölf Uhr abends löschen kann, wenn es ruhig ist.

Natürlich sind einige Leute möglicherweise anderer Meinung und sagen: „Sie können eine gespeicherte Prozedur zum Löschen der Tabelle schreiben und sie einmal nachts ausführen, wenn nicht viel Verkehr herrscht.“
Ich war schockiert und konnte nach einigem Nachdenken nur sagen: „Hört bitte alle auf zu streiten und hört mir zu, wenn ich über die gängigen Praktiken in der Branche spreche.“

Eine Hypothese

Lassen Sie mich das zunächst erklären. Es gibt hier eine Prämisse. MySQL hat unabhängige Tablespaces aktiviert, die nach MySQL 5.6.7 standardmäßig aktiviert sind.

Das heißt, in my.cnf gibt es eine solche Konfiguration (dies sind Kenntnisse zur MySQL-Optimierung, die Ihnen später vorgestellt werden).

innodb_file_per_table = 1

Überprüfen Sie den Status des Tabellenbereichs mit dem folgenden Befehl

mysql> Variablen wie „%per_table“ anzeigen; 
+--------------------------+----------+ 
| Variablenname | Wert | 
+--------------------------+----------+ 
| innodb_file_per_table | AUS | 
+--------------------------+----------+

Wenn der Wert von innodb_file_per_table OFF ist, bedeutet dies, dass ein gemeinsam genutzter Tabellenbereich verwendet wird.

Wenn der Wert von innodb_file_per_table ON ist, bedeutet dies, dass ein unabhängiger Tabellenbereich verwendet wird.

Daher wird mich jeder fragen: Was ist der Unterschied zwischen einem unabhängigen Tablespace und einem gemeinsam genutzten Tablespace?

Shared Tablespace: Alle Tabellendaten und Indexdateien einer Datenbank werden in einer Datei abgelegt. Der Standarddateipfad dieses Shared Tablespace befindet sich im Datenverzeichnis. Der Standarddateiname lautet: ibdata1 (diese Datei kann in mehrere Dateien erweitert werden). Beachten Sie, dass die Bedienung und Wartung auf diese Weise äußerst umständlich sind. Sie sehen, alle Daten befinden sich in einer Datei und es ist sehr umständlich, eine einzelne Tabelle zu verwalten. Darüber hinaus bleiben beim Ausführen eines Löschvorgangs viele Lücken in der Datei zurück und die ibdata1-Datei wird nicht automatisch verkleinert. Mit anderen Worten: Wenn Sie einen gemeinsam genutzten Tabellenbereich zum Speichern von Daten verwenden, kann das Problem auftreten, dass der Speicherplatz nach dem Löschen der Tabelle nicht freigegeben werden kann.

Unabhängiger Tabellenbereich: Jede Tabelle wird unabhängig bereitgestellt. Jede Tabelle verfügt über eine .frm-Tabellenbeschreibungsdatei und eine .ibd-Datei.

.frm-Datei: speichert die Metadaten jeder Tabelle, einschließlich der Definition der Tabellenstruktur usw. Diese Datei ist unabhängig von der Datenbank-Engine.

.ibd-Datei: Eine Datei, die die Daten und Indizes jeder Tabelle speichert.

Beachten Sie, dass auf diese Weise jede Tabelle ihren eigenen, unabhängigen Tabellenbereich hat, was den Betrieb und die Wartung vereinfacht und das Verschieben einer einzelnen Tabelle zwischen verschiedenen Datenbanken ermöglicht. Darüber hinaus kann beim Ausführen des Löschvorgangs der Tabellenspeicherplatz automatisch freigegeben werden. Nach dem Ausführen des Löschvorgangs können Sie „alter table TableName engine=innodb“ verwenden, um die Tabelle zu defragmentieren und etwas Tabellenspeicherplatz zurückzugewinnen.

ps: datadir in my.cnf wird verwendet, um das Datenspeicherverzeichnis festzulegen

Okay, ich habe oben schon viel gesagt, ich möchte nur eines sagen:

In den meisten Fällen wird für Betrieb und Wartung definitiv eine unabhängige Tablespace-Speichermethode für MySQL gewählt, da die unabhängige Tablespace-Methode im Hinblick auf Leistungsoptimierung und Betriebs- und Wartungsschwierigkeiten viel leistungsfähiger ist.

Daher ist die eingangs erwähnte Prämisse, dass MySQL einen unabhängigen Tabellenbereich öffnen muss. In 90 % der Fälle trifft diese Annahme zu. Wenn in Ihrem Unternehmen tatsächlich eine Situation auftritt, in der MySQL einen gemeinsam genutzten Tabellenbereich verwendet, sprechen Sie bitte mit Ihrem Betriebs- und Wartungspersonal und fragen Sie, warum ein gemeinsam genutzter Tabellenbereich verwendet wird.

Richtige Haltung

Angenommen, wir haben datadir = /data/mysql/ und eine Datenbank mit dem Namen mytest. In der Datenbank mytest gibt es eine Tabelle namens erp. Führen Sie den folgenden Befehl aus

mysql> system ls -l /data/mysql/mytest/

Ich erhalte die folgende Ausgabe (ich habe sie etwas gefiltert)

-rw-r----- 1 mysql mysql 9023 8 18 05:21 erp.frm
-rw-r----- 1 mysql mysql 2356792000512 8 18 05:21 erp.ibd

Die Funktionen von frm und ibd wurden oben vorgestellt. Jetzt ist die Datei erp.ibd zu groß, sodass der Löschvorgang steckenbleibt.

Wie kann dieses Problem gelöst werden?

Hier müssen wir das Wissen über Hardlinks in Linux nutzen, um eine schnelle Löschung durchzuführen. Lassen Sie mich einige Inhalte aus „Bird Brother’s Private Kitchen“ mit Ihnen teilen.

Tatsächlich können Sie Softlinks als Verknüpfungen in Windows verstehen, daher werde ich sie nicht im Detail vorstellen, sondern hauptsächlich Hardlinks vorstellen.

Was diesen Hardlink betrifft, werde ich nur kurz darauf eingehen. Ich möchte hier nicht viele Worte posten, das wäre zu ermüdend.

Für die tatsächlich gespeicherten Dateien gibt es eine

Dann gibt es einen Dateinamen, der auf den Knotenindex oben verweist

Der sogenannte Hardlink bedeutet also, dass mehr als ein Dateiname auf den Knotenindex verweist und mehrere Dateinamen auf den Knotenindex verweisen.

Nehmen wir an, dass es einen anderen Dateinamen gibt, der auf den obigen Knotenindex verweist, nämlich

Zu diesem Zeitpunkt löschen Sie den Dateinamen (1). Das Linux-System erkennt, dass es einen Dateinamen (2) gibt, der auf den Knotenindex verweist. Daher wird die Datei nicht wirklich gelöscht, aber die Referenz in Schritt (2) wird gelöscht. Dieser Vorgang ist sehr schnell, da nur die Referenz gelöscht wird. So ist das Bild geworden

Als nächstes löschen Sie den Dateinamen (2). Das Linux-System erkennt, dass kein anderer Dateiname auf den Knotenindex verweist, und löscht die eigentliche Speicherdatei. Dieser Vorgang dient zum Löschen der eigentlichen Datei und ist daher relativ langsam.

OK, wir verwenden das obige Prinzip.

Erstellen Sie zunächst mit dem Befehl ln einen Hardlink für erp.ibd

mysql> system ln /data/mysql/mytest/erp.ibd /data/mysql/mytest/erp.ibd.hdlk

Zu diesem Zeitpunkt sieht das Dateiverzeichnis wie folgt aus

-rw-r----- 1 mysql mysql 9023 8 18 05:21 erp.frm
-rw-r----- 2 mysql mysql 2356792000512 8 18 05:21 erp.ibd
-rw-r----- 2 mysql mysql 2356792000512 8 18 05:21 erp.ibd.hdlk

Sie werden feststellen, dass eine zusätzliche Datei erp.ibd.hdlk vorhanden ist und die Inodes von erp.ibd und erp.ibd.hdlk beide 2 sind.

An dieser Stelle führen Sie die Drop-Table-Operation aus

mysql> Tabelle ERP löschen;
Abfrage OK, 0 Zeilen betroffen (0,99 Sek.)

Sie werden feststellen, dass es in weniger als 1 Sekunde gelöscht wird. Denn zu diesem Zeitpunkt gibt es zwei Dateinamen (erp.ibd und erp.ibd.hdlk), die auf denselben Inode verweisen. Zu diesem Zeitpunkt löscht die Ausführung des Löschvorgangs nur den Verweis, sodass dies sehr schnell geht.

Durch die Löschung zu diesem Zeitpunkt wurde die Tabelle aus MySQL gelöscht. Der Speicherplatz wurde jedoch nicht freigegeben, da noch eine Datei erp.ibd.hdlk übrig ist.

Wie lösche ich erp.ibd.hdlk richtig?

Wenn Sie keine Erfahrung haben, antworten Sie mir auf jeden Fall und verwenden Sie den Befehl rm zum Löschen. Hierbei ist zu beachten, dass in einer Produktionsumgebung die direkte Verwendung des Befehls rm zum Löschen großer Dateien zu einem Anstieg des Festplatten-E/A-Overheads und einer übermäßigen CPU-Auslastung führt, was sich auf den Betrieb anderer Programme auswirkt.

Nun, zu diesem Zeitpunkt sollten Sie den Befehl truncate verwenden, um es zu löschen. Der Befehl truncate befindet sich im Coreutils-Toolset.

Weitere Einzelheiten finden Sie bei Baidu. Jemand hat die Befehle rm und truncate getestet und festgestellt, dass der Befehl truncate fast keine Auswirkungen auf die Festplatten-E/A und die CPU-Auslastung hat.

Das Löschskript lautet wie folgt

TRUNCATE=/usr/local/bin/truncate
für i in „seq 2194 -10 10“; 
Tun 
 Schlaf 2
 $TRUNCATE -s ${i}G /data/mysql/mytest/erp.ibd.hdlk 
Erledigt
rm -rf /data/mysql/mytest/erp.ibd.hdlk;

Beginnen Sie bei 2194 G, reduzieren Sie jedes Mal 10 G, halten Sie für 2 Sekunden an und fahren Sie fort, bis nur noch 10 G in der Datei übrig sind. Verwenden Sie abschließend den Befehl rm, um den Rest zu löschen.

Andere Situationen

Dies bedeutet, was zu tun ist, wenn die Datenbank unter Windows bereitgestellt wird. Meine Antwort auf diese Frage ist eigentlich nicht professionell genug. Seit meinem Debüt bin ich noch nie auf eine Situation gestoßen, in der MySQL in der Produktionsumgebung unter Windows installiert war. Angenommen, Sie stoßen tatsächlich auf dieses Problem, gibt es unter Windows ein Tool namens mklink, das zum Erstellen von Hardlink-Sperren unter Windows verwendet wird und ähnliche Funktionen ausführen können sollte.

Zusammenfassen

Die in diesem Artikel behandelten Inhalte dürften eher im Bereich der Forschung und Entwicklung kleiner und mittelständischer Unternehmen anzutreffen sein. Da kleine und mittelgroße Unternehmen nicht über professionelle Datenbankadministratoren verfügen, müssen die F&E-Leute alles selbst erledigen. Ich hoffe, dass jeder etwas daraus lernen konnte.

Nun, das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Wenn Sie Fragen haben, können Sie eine Nachricht hinterlassen. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.