Warum ist der Speicherplatz nach dem Löschen von Tabellendaten in MySQL immer noch belegt?

Vor kurzem gab es ein Projekt, bei dem der obere Computer Daten erhielt, die vom unteren Computer gemeldet wurden. Aufgrund der häufigen Meldehäufigkeit und der großen Datenmenge wuchsen die Daten zu schnell und belegten viel Speicherplatz.

Um Kosten zu sparen, sichern Sie die Daten regelmäßig und löschen Sie Tabellendatensätze mit delete .

delete wurde ausgeführt, aber die Größe der Tabellendatei hat sich nicht verringert, was rätselhaft ist.

Als Datenbank wird im Projekt Mysql verwendet. Bei Tabellen handelt es sich dabei grundsätzlich um die Tabellenstruktur und die Tabellendaten. Der von der Tabellenstruktur belegte Speicherplatz ist relativ klein und entspricht im Allgemeinen dem von den Tabellendaten belegten Speicherplatz.

Wenn wir delete zum Löschen von Daten verwenden, werden die Datensätze in der Tabelle zwar gelöscht, die Dateigröße der Tabelle ändert sich jedoch nicht.

1. MySQL-Datenstruktur

Jeder, der mysql verwendet hat, hat bestimmt schon einmal vom B+樹gehört. MySQL InnoDB verwendet B+ -Baum als Struktur zur Datenspeicherung, der oft als indexorganisierte Tabelle bezeichnet wird, und die Daten werden seitenweise gespeichert. Daher gibt es beim Löschen von Daten zwei Situationen:

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

2. Die Tabellendateigröße hat sich nicht geändert und hängt mit dem MySQL-Design zusammen

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. Wenn Sie später einen Datensatz mit ID zwischen 300 und 700 einfügen, wird diese Position wiederverwendet.

Es ist ersichtlich, dass die Größe der Datenträgerdatei nicht reduziert wird.

Im Allgemeinen wird beim Löschen einer ganzen Seite mit Daten auch die Datensatzmarkierung gelöscht und die Daten werden an dieser Position wiederverwendet. Dies unterscheidet sich vom Löschen von Diktatdatensätzen darin, dass beim Löschen einer ganzen Seite mit Datensätzen die Position wiederverwendet werden kann, wenn die später eingefügten Daten nicht innerhalb des ursprünglichen Bereichs liegen. Wenn Sie jedoch nur Diktatdatensätze löschen, müssen Sie Daten einfügen, die der gelöschten Datensatzposition entsprechen, bevor Sie sie wiederverwenden können.

Unabhängig davon, ob eine Datenzeile oder eine Datenseite gelöscht wird, wird sie zur Wiederverwendung als gelöscht markiert, sodass die Dateigröße nicht reduziert wird.

3. Wie können wir die Tabellengröße verkleinern?

DELETE löscht nur den Datenmarker und defragmentiert die Datendatei nicht. Beim Einfügen neuer Daten wird der als gelöscht markierte Datensatzplatz wieder verwendet. OPTIMIZE TABLE können Sie ungenutzten Speicherplatz freigeben und die Datendatei defragmentieren.

OPTIMIZE TABLE Tabellenname;

Hinweis: OPTIMIZE TABLE funktioniert nur für MyISAM , BDB und InnoDB -Tabellen.

Alternativ können Sie die Tabelle auch mit ALTER TABLE neu aufbauen.

ALTER TABLE Tabellenname ENGINE=INNODB

Manche Leute fragen sich vielleicht, was der Unterschied zwischen OPTIMIZE TABLE und ALTER TABLE ist?

alter table t engine = InnoDB (das heißt, neu erstellen), während optimize table t gleich recreate+analyze ist

4. Online-DDL

Lassen Sie uns abschließend über Online DDL sprechen. Eine der täglichen Aufgaben dba sind definitiv DDL-Änderungen. DDL-Änderungen sperren die Tabelle, was für dba eine ewige Qual sein kann, insbesondere wenn ddl Änderungen ausgeführt werden, da dadurch eine große Anzahl von Threads in der Bibliothek in den Status „ Waiting for meta data lock “ versetzt wird. Daher wurde nach Version 5.6 Online DDL eingeführt.

Vor der Einführung von Online DDL gab es zwei Hauptmethoden zum Ausführen von DDL: copy und inplace . inplace Methode wird auch ( fast index creation ) genannt. Im Vergleich zur copy kopiert inplace Methode keine Daten und ist daher schneller. Allerdings unterstützt diese Methode nur das Hinzufügen und Löschen von Indizes und erfordert wie die Kopiermethode das Sperren der Tabelle während des gesamten Vorgangs, sodass sie nicht sehr praktisch ist. Im Vergleich zu den beiden vorherigen Methoden unterstützt Online -Methode nicht nur Lese-, sondern auch Schreibvorgänge.

Verwenden Sie beim Ausführen von online DDL -Anweisungen die Schlüsselwörter ALGORITHM und LOCK . Diese beiden Schlüsselwörter stehen am Ende unserer DDL Anweisung, getrennt durch ein Komma. Hier ist ein Beispiel:

ALTER TABLE Tabellenname ADD COLUMN Spaltenname Spaltentyp, ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM-Optionen

• INPLACE: Ersetzen: Führen Sie den DDL Vorgang direkt auf der Originaltabelle aus.
• KOPIEREN: Kopieren: Verwenden Sie eine temporäre Tabelle, um eine temporäre Tabelle zu klonen, führen Sie DDL auf der temporären Tabelle aus und importieren Sie dann die Daten in die temporäre Tabelle, benennen Sie sie um usw. Während dieser Zeit wird zur Unterstützung solcher Vorgänge doppelt so viel Speicherplatz benötigt. Während der Ausführung sind keine DML -Operationen auf der Tabelle zulässig.
• DEFAULT: Die Standardmethode, die von MySQL selbst ausgewählt wird, und die INPLACE Methode wird zuerst verwendet.
  
• LOCK-Option
• SHARE: Gemeinsam genutzte Sperre. Die Tabelle, in DDL ausgeführt wird, kann gelesen, aber nicht beschrieben werden.
• KEINE: Es gibt keine Einschränkungen. Die Tabelle, in DDL ausgeführt wird, ist lesbar und beschreibbar.
• EXKLUSIV: Exklusive Sperre. Die Tabelle, in der DDL ausgeführt wird, kann nicht gelesen oder geschrieben werden.
• DEFAULT: Der Standardwert. Dieser wird als Standardwert verwendet, wenn LOCK Klausel in der DDL Anweisung nicht angegeben ist. Wenn der angegebene LOCK Wert

DEFAULT , d. h. es liegt an MySQL , zu entscheiden, ob die Tabelle gesperrt wird oder nicht. Dies wird nicht empfohlen. Wenn Sie sicher sind, dass Ihre DDL Anweisung die Tabelle nicht sperrt, können Sie lock nicht angeben oder den Wert default festlegen. Andernfalls wird empfohlen, den Sperrtyp anzugeben.
Beim Ausführen DDL -Operationen muss die Option ALGORITHM nicht angegeben werden. In diesem Fall wählt MySQL automatisch den entsprechenden Modus in der Reihenfolge INSTANT , INPLACE und COPY aus. Sie können auch ALGORITHM=DEFAULT angeben, was den gleichen Effekt hat. Wenn die Option ALGORITHM angegeben, aber nicht unterstützt wird, wird ein Fehler gemeldet.

OPTIMIZE TABLE und ALTER TABLE table name ENGINE=INNODB unterstützen beide Oline DDL , es wird jedoch trotzdem empfohlen, sie zu verwenden, wenn das Geschäftszugriffsvolumen gering ist.

5. Zusammenfassung

Beim delete von Daten wird die entsprechende Datenzeile nicht tatsächlich gelöscht, sondern lediglich als wiederverwendbar gekennzeichnet, sodass der Tabellenplatz nicht kleiner wird.

Sie können die Tabelle neu erstellen, um die Größe der Tabelle nach delete von Daten schnell zu reduzieren ( OPTIMIZE TABLE oder ALTER TABLE ). Ab Version 5.6 unterstützt die Tabellenerstellung bereits Online Operationen, aber es ist am besten, diese während geschäftsschwacher Spitzenzeiten zu verwenden.

Dies ist das Ende dieses Artikels darüber, warum nach dem Löschen von MySQL-Tabellendaten immer noch Speicherplatz belegt ist. Weitere Informationen zum Löschen von MySQL -Tabellendaten finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!