Eine kurze Erläuterung der richtigen Vorgehensweise bei der Wiederherstellung von MySQL-Tabellenbereichen

Ich weiß nicht, ob Sie schon einmal in eine solche Situation geraten sind. Online-Unternehmen führen Additions-, Lösch-, Änderungs- und Abfragevorgänge an MySQL-Tabellen durch. Mit der Zeit werden die Daten in der Tabelle immer mehr, die Tabellendatendatei wird immer größer und der von der Datenbank belegte Speicherplatz nimmt natürlich allmählich zu.

Um den von der Tabellendatendatei auf der Festplatte belegten Speicherplatz zu reduzieren, haben wir den Löschbefehl verwendet, um die Hälfte der alten Daten in der größten Geschäftstabelle zu löschen. Nach dem Löschen wurde die Tabellendatendatei auf der Festplatte nicht kleiner. Selbst wenn die Daten der gesamten Tabelle gelöscht wurden, wurde die Datei trotzdem nicht kleiner. Warum ist das so?

In diesem Artikel werden die oben genannten Probleme im Detail analysiert und die richtige Methode zum Zurückgewinnen von Tabellenspeicherplatz beschrieben.

Vorbemerkungen

Derzeit verwenden die meisten MySQL-Datenbanken die InnoDB-Engine. Sofern nicht anders angegeben, basieren die Beispiele in diesem Artikel auf der InnoDB-Engine.

In der MySQL-Konfiguration gibt es ein Konfigurationselement namens innodb_file_per_table . Nachdem Sie es auf 1 gesetzt haben,
Die Daten jeder Tabelle werden separat in einer Datei mit der Endung .ibd gespeichert.

Wenn innodb_file_per_table nicht aktiviert ist,
Die Daten der Tabelle werden im gemeinsam genutzten Tabellenbereich des Systems gespeichert. Selbst wenn die Tabelle gelöscht wird, gibt der gemeinsam genutzte Tabellenbereich diesen Speicherplatz nicht frei.

Daher wird die Option innodb_file_per_table normalerweise auf 1 gesetzt. Um gleichzeitig die Größenänderungen der Tabellendatendatei intuitiv zu erkennen, werden die Beispiele in diesem Artikel auch anhand der Aktivierung dieser Option erläutert.

Reproduktion des Problems

Erstellen Sie eine neue Tabelle ta , die Struktur der Tabelle ist wie folgt

mysql> zeigen erstellen Tabelle ta\G
*************************** 1. Reihe ***************************
       Tisch:
Tabelle erstellen: CREATE TABLE `ta` (
  `id` int(11) NICHT NULL,
  `ia` int(11) NICHT NULL,
  PRIMÄRSCHLÜSSEL (`id`)
) ENGINE=InnoDB STANDARD-CHARSET=utf8
1 Zeile im Satz (0,00 Sek.)

Verwenden Sie die folgende gespeicherte Prozedur, um Daten stapelweise in ta einzufügen

Trennzeichen //
Prozedur „multinsert“ erstellen (in beg int, in cnt int)
beginnen
	Deklariere icnt int als Standard 0;
	Deklariere temporären Int als Standardwert 0;
	während icnt < cnt do
		setze icnt = icnt + 1;
		setze tmp = beg + icnt;
		in ta(id,ia) Werte(tmp,tmp) einfügen;
	Ende während;
Ende//

Trennzeichen ;

Führen Sie den Befehl call multinsert(0,100000) in der MySQL-Konsole aus, um 100.000 Datensätze in die ta Tabelle einzufügen.

mysql> rufe multinsert(0,100000) auf;
mysql> wähle count(*) aus ta;
+----------+
| Anzahl(*) |
+----------+
| 100000 |
+----------+
1 Zeile im Satz (0,02 Sek.)

Überprüfen Sie die Größe der ta.ibd Datendatei ta Tabelle auf der Festplatte

[root@ecs-centos-7 test]# cd /var/lib/mysql/test/
[root@ecs-centos-7 test]# ls -l ta.ibd 
-rw-r----- 1 mysql mysql 11534336 3. Januar 23:14 ta.ibd

Aus den obigen Ergebnissen können wir erkennen, dass nach dem Einfügen von 100.000 Datensätzen in die ta Tabelle die Größe von ta.ibd 11534336 Bytes (ungefähr 11 M)

Nun verwenden wir den delete , um die Hälfte der Tabellendaten (50.000 Zeilen) zu löschen.

mysql> löschen aus ta, wobei die ID zwischen 1 und 50000 liegt;
Abfrage OK, 10000 Zeilen betroffen (0,03 Sek.)
mysql> wähle count(*) aus ta;
+----------+
| Anzahl(*) |
+----------+
| 50000 |
+----------+
1 Zeile im Satz (0,02 Sek.)

Überprüfen Sie nach Abschluss des Löschvorgangs erneut die Größe von ta.ibd auf der Festplatte.

[root@ecs-centos-7 test]# cd /var/lib/mysql/test/
[root@ecs-centos-7 test]# ls -l ta.ibd 
-rw-r----- 1 mysql mysql 11534336 3. Januar 23:14 ta.ibd

Aus den obigen Ergebnissen können wir erkennen, dass nach dem Löschen der Hälfte der ta Tabelle, also 50.000 Datenzeilen, die Größe von ta.ibd 11534336 Bytes (ungefähr 11 M)

Das heißt, die Datendatei auf der Festplatte wird vor und nach dem Löschen der Daten aus ta Tabelle nicht verkleinert.

Um zu verstehen, warum die Datendatei nicht kleiner wird, müssen wir das Prinzip des Datenlöschens verstehen.

Grundsatz der Datenlöschung

Wie wir alle wissen, werden Daten in InnoDB mithilfe von B+-Bäumen organisiert. Weitere Informationen zu B+-Bäumen finden Sie unter „B+-Bäume verstehen“.

Abbildung (1)

Oben sehen Sie ein schematisches Diagramm der InnoDB-Indizes. Die durch gepunktete Linien umrahmten Knoten gehören zur Datenseite Page1. Die Blattknoten speichern die dem Index entsprechenden Daten. Sie bilden ein geordnetes Array in der Reihenfolge des Index von klein nach groß.

Angenommen, wir möchten die Daten mit dem Indexschlüsselwert 13 auf Seite 1 löschen. Dies ist der rote Teil in der obigen Abbildung.

Die InnoDB-Engine markiert den Knoten mit dem Indexschlüsselwert 13 als gelöscht. Sie gibt den tatsächlichen physischen Speicherplatz des Knotens nicht zurück, sondern markiert ihn nur als gelöschten Knoten, der später wiederverwendet werden kann. Daher wird durch das Löschen von Tabellendatensätzen die Datendatei auf der Festplatte nicht verkleinert.

Sie sagen vielleicht, dass oben nur die Daten eines Knotens auf Seite1 gelöscht werden. Wenn also alle Knotendaten auf Seite1 gelöscht werden, sollte der Speicherplatz auf Seite1 zurückgewonnen werden, oder?

Die Antwort ist: kein Recycling

Wenn alle Daten auf Seite 1 gelöscht sind, wird die gesamte Datenseite als gelöscht markiert und kann wiederverwendet werden. Daher wird in diesem Fall die Datendatei auf der Festplatte nicht verkleinert.

Datenwiederverwendung

Die Wiederverwendung von Daten umfasst Vorgänge wie das Einfügen, Löschen und Übertragen von Datenknoten sowie das Zusammenführen von Datenseiten. Einzelheiten zum spezifischen Vorgang finden Sie unter „B+-Bäume verstehen“. Ich werde es hier nicht wiederholen.

• Wiederverwendung von Datenknoten

Wenn in der obigen Abbildung (1) der Knoten mit dem Indexschlüsselwert 13 gelöscht wird, wird dieser Knoten als wiederverwendbar markiert.

Wenn später ein Datensatz mit einem Indexschlüsselwert zwischen 7 und 18 eingefügt wird, wird der Datenknoten mit dem ursprünglichen Indexschlüsselwert 13 wiederverwendet.

Liegt der Indexschlüsselwert des später eingefügten Datensatzes jedoch nicht zwischen 7 und 18, darf der Datenknoten mit dem ursprünglichen Indexschlüsselwert 13 nicht wiederverwendet werden.

Mit anderen Worten erfordert die Wiederverwendung von Datenknoten, dass der Indexschlüsselwert bestimmte Bereichsbedingungen erfüllt.

• Wiederverwendung von Datenseiten

In Abbildung (1) ist nach dem Löschen aller Datenknoten von Seite1 die gesamte Seite1 wiederverwendbar. Wenn der eingefügte Datensatz eine neue Seite benötigt, kann Seite1 wiederverwendet werden.

Wenn die Auslastung benachbarter Datenseiten gering ist, können sie zu einer der Datenseiten zusammengeführt werden. Zu diesem Zeitpunkt wird die andere Datenseite frei und die freie Datenseite kann wiederverwendet werden.

Welche Vorgänge verursachen Datenlöcher?

Nachdem wir einen Datensatz mit dem delete gelöscht haben, markiert InnoDB den entsprechenden Datenknoten einfach als gelöscht und wiederverwendbar. Diese leeren Datenknoten, die auf ihre Verwendung warten, können einzeln als Datenlöcher betrachtet werden.

• Löschen von Daten

Beim Löschen von Daten entstehen Datenlöcher. Dies wurde bereits erklärt, daher werde ich es hier nicht wiederholen.

• Einfügen von Daten

Wenn die Daten in der Reihenfolge der Indexgröße eingefügt werden, ist die Datenseite kompakt und es entstehen keine Datenlöcher.

Wenn Sie Daten aus der Mitte eines Indexes einfügen, kann dies zu Seitenumbrüchen führen und auf den Seiten nach den Umbrüchen können Datenlöcher auftreten. Die folgende Abbildung zeigt ein Beispiel für einen Seitenumbruch, der durch eine Einfügung verursacht wurde.

Wie in der Abbildung gezeigt, ist die Blattseite vor der Teilung voll und die Daten sind sehr kompakt angeordnet.

Jetzt werden Daten mit dem Indexschlüsselwert 15 eingefügt. Nach dem Einfügen wird Seite1 in zwei Seiten aufgeteilt Page1，Page2 in der obigen Abbildung.

Nach der Teilung sind in Seite1 zwei Löcher aufgetreten. Diese beiden Datenknoten sind wiederverwendbar, während Seite2 voll ist.

• Daten aktualisieren

Das Aktualisieren von Daten kann als Löschen und anschließendes Einfügen angesehen werden, was ebenfalls zu Datenlöchern führen kann.

Beispiel: id ist der Primärschlüssel der Tabelle ta. Die Anweisung „update ta set update ta set id = 10 where id = 1 ändert id = 1 in „ id = 10 , was gleichbedeutend damit ist, zuerst den Datensatz mit id = 1 zu löschen und dann den Datensatz mit id = 10 einzufügen. Dies führt zu Datenlücken.

Wenn die Anweisung jedoch beispielsweise update ta set ia = ia + 1 where id = 1 lautet und den Primärschlüsselwert nicht ändert, entsteht keine Lücke.

Daher kann die Aktualisierung von Daten zu Datenlücken führen

Zusammenfassend lässt sich sagen, dass das Hinzufügen, Löschen und Ändern von Tabellen zu Datenlücken führen kann und dass Onlinedienste eine große Zahl von Hinzufügen, Löschen und Ändern von Tabellen durchführen, sodass die Möglichkeit von Datenlücken relativ hoch ist.

So verkleinern Sie einen Tablespace

Da eine Tabelle nach vielen unregelmäßigen Hinzufügungs-, Lösch- und Änderungsvorgängen viele Datenlücken aufweist

Was wäre, wenn wir eine neue Tabelle mit derselben Struktur wie die Originaltabelle, jedoch mit Datenlücken, erstellen und dann die Daten aus der alten Tabelle in aufsteigender Indexreihenfolge in die neue Tabelle einfügen, nachdem alle Daten aus der alten Tabelle in die neue Tabelle eingefügt wurden, die alte Tabelle löschen und die neue Tabelle anschließend in den Namen der alten Tabelle umbenennen würden?

Da die Blattknotendaten in der neuen Tabelle nacheinander hinzugefügt werden, ist die Seite sehr kompakt und die Seitenauslastung sehr hoch. Es werden viel weniger Seiten benötigt als in der alten Tabelle. Auf diese Weise sind die Lücken in den Indizes der alten Tabelle in der neuen Tabelle nicht vorhanden. Der von der neuen Tabellendatendatei belegte Speicherplatz wird natürlich reduziert, wodurch der Zweck der Verkleinerung des Tabellenspeicherplatzes erreicht wird.

Im Folgenden finden Sie mehrere Methoden zum Verkleinern des Tabellenspeicherplatzes. Obwohl die Methoden unterschiedlich sind, besteht das Grundprinzip darin, das Ziel durch Neuaufbau der Tabelle zu erreichen.

• Truntace-Tabelle Tabellenname

Dieser Vorgang entspricht drop + create . Löschen Sie zuerst die Tabelle und erstellen Sie dann eine neue Tabelle mit demselben Namen. Natürlich müssen Sie eine Kopie der Daten der alten Tabelle speichern, bevor Sie den Befehl truncate table ausführen. Nachdem der Befehl ausgeführt wurde, importieren Sie die Daten in die neue Tabelle.

• Tabelle ändern Tabellenname Engine = InnoDB

Dieser Vorgang besteht darin, die Datenseite des Primärschlüsselindex der alten Tabelle zu durchlaufen, eine B + -Baumstruktur für die Datensätze auf der Datenseite zu generieren und sie in einer temporären Datei auf der Festplatte zu speichern. Nachdem die Datenseitendurchquerung abgeschlossen ist, wird die temporäre Datei verwendet, um die Datendatei der alten Tabelle zu ersetzen.

Ab MySQL 5.6 handelt es sich bei dieser Operation um Online-DDL. Es ist zu beachten, dass diese Methode ein Scannen der Tabellendatendatei erfordert, was bei großen Tabellen sehr zeitaufwändig ist. Wenn es sich um Onlinedienste handelt, müssen Geschäftsspitzenzeiten vermieden und mit Vorsicht vorgegangen werden.

Beachten:
Beim Neuaufbau einer Tabelle belegt InnoDB nicht die gesamte Tabelle. Es reserviert etwa 10 % der Datenknoten auf jeder Seite für spätere Aktualisierungen. Mit anderen Worten: Die Tabelle ist nach dem Neuaufbau nicht die kompakteste.

Angenommen, es gibt einen solchen Prozess: Tabelle t einmal neu erstellen,
Fügen Sie einige Daten ein, aber die eingefügten Daten verbrauchen einen Teil des reservierten Speicherplatzes.
Wenn Sie in diesem Fall die Tabelle t erneut neu erstellen, ist der von der neu erstellten Tabelle belegte Speicherplatz möglicherweise größer als vor der Rekonstruktion.

Zusammenfassung

Dieser Artikel beginnt mit einem praktischen Problem, reproduziert das Problem, analysiert das Problem und löst das Problem. Jeder Schritt wird detailliert analysiert. Aufgrund des begrenzten Platzes sind einige Details nicht ausführlich und die Leser müssen sie selbst verstehen.

Damit ist dieser Artikel über den richtigen Ansatz zur Wiederherstellung von MySQL-Tablespaces abgeschlossen. Weitere relevante Inhalte zur Wiederherstellung von MySQL-Tablespaces 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!