Warum die Tabellendateigröße nach dem Löschen von Daten in MySQL unverändert bleibt

Bei Datenbanken, die schon lange laufen, besteht häufig das Problem, dass die Tabelle zu viel Speicherplatz belegt. Nach dem Löschen vieler nutzloser Tabellen hat sich die Größe der Tabellendatei jedoch nicht geändert. Um dieses Problem zu lösen, müssen Sie verstehen, wie InnoDB Tabellenspeicherplatz zurückgewinnt.

Bei einer Tabelle ist der belegte Speicherplatz hauptsächlich in zwei Teile unterteilt: Tabellenstruktur und Tabellendaten. Im Allgemeinen nehmen Tabellenstrukturdefinitionen sehr wenig Platz ein. Daher betrifft das Platzproblem hauptsächlich die Tabellendaten.

Vor MySQL 8.0 wurden Tabellenstrukturen in Dateien mit der Endung .frm gespeichert. In 8.0 ist es erlaubt, Tabellenstrukturen in Systemdatentabellen zu definieren.

Informationen zur Speicherung von Tabellendaten

Tabellendaten können in einem gemeinsam genutzten Tabellenbereich oder in einer separaten Datei gespeichert werden, gesteuert durch innodb_file_per_table .

• Wenn es ausgeschaltet ist, bedeutet dies, dass es zusammen mit dem Datenwörterbuch im gemeinsam genutzten Tabellenbereich des Systems vorhanden ist.
• Wenn ON, wird jede InnoDB-Tabellenstruktur in einer Datei mit der Endung .idb gespeichert.

Nach 5.6.6 ist der Standardwert EIN.

Es wird empfohlen, diesen Parameter auf „ON“ zu setzen, damit das System die Datei bei Nichtbedarf direkt über den Befehl „Drop Table“ löscht.

Aber in einem gemeinsam genutzten Tabellenbereich wird der Speicherplatz nicht wiederhergestellt, selbst wenn die Tabelle gelöscht wird.

abschneiden = löschen + erstellen

Datenlöschungsprozess

Manchmal werden beim delete von Daten mit der Funktion „delete“ jedoch nur einige Zeilen gelöscht, was jedoch dazu führen kann, dass der Tabellenspeicherplatz nicht wiederhergestellt wird.

Wir wissen, dass MySQL InnoDB einen B+-Baum als Struktur zum Speichern von Daten verwendet, der oft als indexorganisierte Tabelle bezeichnet wird, und dass die Daten in Seiten gespeichert werden.

Beim Löschen von Daten gibt es zwei Situationen:

• Löschen Sie einige Datensätze auf der Datenseite
• Löschen Sie den Inhalt der gesamten Datenseite
  

Wenn Sie beispielsweise den Datensatz R4 löschen möchten:

InnoDB markiert den Datensatz R4 direkt als gelöscht, was als wiederverwendbarer Speicherort bezeichnet wird. Wird nachträglich ein Datensatz mit einer ID zwischen 300 und 700 eingefügt, wird die Position wiederverwendet. Es ist ersichtlich, dass die Größe der Datenträgerdatei nicht reduziert wird.

Darüber hinaus ist die Wiederverwendung von Datensätzen auf Daten beschränkt, die den Geltungsbereichsbedingungen entsprechen. Wenn Sie später einen Datensatz mit der ID 800 einfügen möchten, kann die Position von R4 nicht wiederverwendet werden.

Ein weiteres Beispiel: Angenommen, der Inhalt der gesamten Datenseite wird gelöscht. Dabei handelt es sich um die Datenseite A, also R3, R4 und R5.

Zu diesem Zeitpunkt markiert InnoDB die gesamte Seite A als gelöscht und die gesamten Daten können ohne Umfangsbeschränkungen wiederverwendet werden. Wenn Sie beispielsweise Inhalt mit der ID=50 einfügen möchten, können Sie ihn direkt wiederverwenden.

Und wenn die Auslastungsraten zweier benachbarter Datenseiten sehr niedrig sind, werden die Daten der beiden Seiten auf einer der Seiten zusammengeführt und die andere Seite als wiederverwendbar markiert.

Zusammenfassend lässt sich sagen, dass eine Datenzeile oder eine Datenseite, unabhängig davon, ob sie gelöscht wird, zur Wiederverwendung als gelöscht markiert wird, sodass die Dateigröße nicht reduziert wird. Die entsprechende spezifische Operation besteht in der Verwendung des Löschbefehls.

Darüber hinaus können wir auch feststellen, dass beim ersten Löschen von Datensätzen aufgrund der Bereichsbeschränkung bei der Wiederverwendung viele Lücken auftreten, z. B. wenn R4 gelöscht, aber ID=800 eingefügt wird.

Auch Einfügungsvorgänge erzeugen Lücken

Wenn beim Einfügen von Daten die Daten in aufsteigender Reihenfolge des Index eingefügt werden, ist die Struktur des Index kompakt. Wenn es jedoch zufällig eingefügt wird, führt es wahrscheinlich dazu, dass die Indexdatenseite geteilt wird.

Fügen Sie beispielsweise Daten in Seite A ein, die voll ist.

Da Seite A voll ist, müssen wir Seite B beantragen. Der Vorgang des Anpassens von Seite A an Seite B wird auch als Seitenaufteilung bezeichnet.

Nach dem Ende entsteht auf Seite A eine Lücke.

Darüber hinaus entstehen bei Aktualisierungsvorgängen auch Lücken, wenn zuerst gelöscht und dann eingefügt wird.

Darüber hinaus können in Tabellen, in denen viele Hinzufügungen, Löschungen und Änderungen vorgenommen werden, Lücken auftreten. Werden die Löcher entfernt, wird natürlicher Raum freigegeben.

Tabelle neu erstellen verwenden

Um die Lücken in der Tabelle zu entfernen, können Sie eine Tabelle B mit derselben Struktur wie Tabelle A neu erstellen und die Daten dann in aufsteigender Reihenfolge der Primärschlüssel-ID in Tabelle B einfügen.

Da das Einfügen sequentiell erfolgt, gibt es in Tabelle B natürlich keine Lücken und auch die Auslastung der Datenseiten ist höher. Anschließend wurde Tabelle B anstelle von Tabelle A verwendet, was den Effekt zu haben schien, dass der Platz an Tabelle A kleiner wurde.

Konkret durch:

Tabelle ändern A Engine=InnoDB

Nach Version 5.5 ähnelt der Befehl dem oben genannten Vorgang, und MySQL führt die Vorgänge zum Datenaustausch, Tabellennamenaustausch und Löschen alter Tabellen selbstständig durch.

Es gibt jedoch ein Problem. In DDL kann Tabelle A nicht aktualisiert werden. Wenn zu diesem Zeitpunkt Daten in Tabelle A geschrieben werden, kommt es zu Datenverlust.

Online-DDL wurde nach Version 5.6 eingeführt.

Online-DDL

Online DDL hat darauf basierend folgende Aktualisierungen vorgenommen:

Der Vorgang zum Neuaufbau der Tabelle läuft wie folgt ab:

1. Erstellen Sie eine temporäre Datei und scannen Sie alle Datenseiten des Primärschlüssels der Tabelle A.
2. Generieren Sie mit den generierten Datenseiten einen B+-Baum und speichern Sie ihn in einer temporären Datei.
3. Wenn beim Generieren einer temporären Datei eine Operation an A stattfindet, wird diese in der Protokolldatei aufgezeichnet, was dem Status 2 in der Abbildung entspricht.
4. Nachdem die temporäre Datei generiert wurde, wird die Protokolldatei auf die temporäre Datei angewendet, um dieselbe Datendatei wie Tabelle A zu erhalten, entsprechend Status 3.
5. Ersetzen Sie die Datendatei der A-Tabelle durch eine temporäre Datei.
   

Da die Zeilenprotokolldatei vorhanden ist, können Sie während der Rekonstruktion DML-Operationen an Tabelle A durchführen.

Es ist zu beachten, dass vor der Ausführung der ALTER-Anweisung zunächst eine MDL-Schreibsperre angefordert wird, diese jedoch vor dem Kopieren von Daten zu einer MDL-Lesesperre degeneriert und so DML-Operationen unterstützt.

Der Grund, warum das MDL nicht entfernt wird, besteht darin, zu verhindern, dass andere Threads gleichzeitig DDL-Operationen an dieser Tabelle ausführen.

Bei großen Tabellen verbraucht dieser Vorgang viele E/A- und CPU-Ressourcen. Daher muss bei der Durchführung von Onlinevorgängen die Vorgangszeit kontrolliert werden. Aus Sicherheitsgründen wird empfohlen, für die Migration gh-ost zu verwenden.

Online und vor Ort

Lassen Sie uns zunächst über den Unterschied zwischen Inplace und Kopieren sprechen:

Bei Online-DDL werden die rekonstruierten Daten der Tabelle A in tmp_file abgelegt, einer temporären Datei, die in InnoDB erstellt wird. Das gesamte DDL wird in InnoDB durchgeführt. Darüber hinaus werden für die Serverebene keine Daten in die temporäre Tabelle verschoben. Es handelt sich um eine „In-Place“-Operation, daher wird sie „inplace“ genannt.

Im vorherigen allgemeinen DDL wird die erstellte Tabelle A vom Server in tmp_table erstellt und daher als "Kopie" bezeichnet.

Der entsprechende Satz lautet eigentlich:

-- alter table t engine=InnoDB Der Standard ist alter table t engine=innodb,ALGORITHM=inplace;

-- Der Prozess ist ein Server, der alter table t engine=innodb,ALGORITHM=copy kopiert;

Es ist zu beachten, dass inplace und online nicht in einer entsprechenden Beziehung stehen:

1. Wenn der DDL-Prozess online ist, muss er inplace sein
2. Wenn es sich um ein Inplace-DDL handelt, sollte es nicht online sein, wie z. B. das Hinzufügen von Volltextindizes und räumlichen Indizes in <= 8.0.
   

expandieren

Lassen Sie uns über die Unterschiede zwischen Optimieren, Analysieren und Tabellen ändern sprechen:

1. alter table t engine = InnoDB (d. h. neu erstellen) verwendet standardmäßig das Online-DDL-Verfahren.
2. analyze table t erstellt die Tabelle nicht neu. Es berechnet lediglich die Indexinformationen der Tabelle neu, ohne die Daten zu ändern. Während dieses Vorgangs wird eine MDL-Lesesperre hinzugefügt.
3. Die Optimierung der Tabelle t entspricht den Vorgängen in den beiden vorherigen Schritten.
   

Die Verwendung von „Alter Table“ in einer Transaktion führt standardmäßig automatisch zu einem Commit der Transaktion, um die Transaktionskonsistenz aufrechtzuerhalten

Manchmal wird der Platz nach dem Umbau eines Tisches nicht nur nicht kleiner, sondern sogar etwas größer. Dies liegt daran, dass die neu erstellte Tabelle selbst keine Lücken aufweist. Während des DDL-Zeitraums haben einige DML-Ausführungen zufällig neue Lücken verursacht.

InnoDB füllt nicht die gesamte Tabelle, sondern lässt 1/16 jeder Seite für nachfolgende Aktualisierungen übrig. Daher ist die Tabelle zunächst möglicherweise kompakt, nach dem Neuaufbau bleiben jedoch einige Lücken bestehen.

Zusammenfassen

Jetzt wissen wir, dass beim Löschen von Daten mit „delete“ die entsprechende Datenzeile nicht wirklich gelöscht wird. InnoDB kennzeichnet sie nur als wiederverwendbar, sodass der Tabellenspeicherplatz nicht kleiner wird.

Es gibt grundsätzlich zwei Möglichkeiten, wiederverwendeten Speicherplatz zu kennzeichnen. Eine Möglichkeit besteht darin, nur bestimmte Stellen in Datenseiten als gelöscht zu kennzeichnen. Diese Stellen werden jedoch nur innerhalb eines bestimmten Bereichs verwendet, sodass Lücken entstehen.

Die andere Möglichkeit besteht darin, die gesamte Datenseite als wiederverwendbar zu markieren. Eine solche Datenseite unterliegt keinen Einschränkungen und kann direkt wiederverwendet werden.

Um dieses Problem zu lösen, können wir die Methode zum Neuaufbau der Tabelle verwenden. Nach Version 5.6 unterstützt die Tabellenerstellung bereits Online-Operationen, wird jedoch schließlich während der Nebensaison verwendet.

Oben finden Sie Einzelheiten dazu, warum die Tabellendateigröße unverändert bleibt, nachdem MySQL Daten gelöscht hat. Weitere Informationen zur MySQL-Tabellendateigröße finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!