Methoden zum Defragmentieren und Freigeben von Speicherplatz in MySQL-Tabellen

Ursachen der MySQL-Tabellenfragmentierung

Lassen Sie uns die Ursachen der Tabellenfragmentierung in MySQL kurz zusammenfassen. Die Ursachen der Fragmentierung können je nach MySQL-Engine unterschiedlich sein. Diese Unterschiede werden hier nicht näher verstanden und analysiert. Dieser Artikel konzentriert sich nur auf die InnoDB-Engine. Sollten in der Zusammenfassung Mängel oder Fehler vorhanden sein, weisen Sie bitte darauf hin.

Die Daten der InnoDB-Tabelle werden in Seiten gespeichert und jede Seite kann mehrere Datensätze speichern. Diese Datensätze sind in einer Baumstruktur organisiert, die als B+-Baumindex bezeichnet wird. Sowohl die Daten in der Tabelle als auch der Hilfsindex verwenden die B+-Baumstruktur. Dieser B+-Baumindex, der alle Daten in der Tabelle verwaltet, wird als gruppierter Index bezeichnet und ist nach Primärschlüssel organisiert. Die Blattknoten eines gruppierten Indexes enthalten die Werte aller Felder in der Zeile, und die Blattknoten eines sekundären Indexes enthalten die Indexspalten und die Primärschlüsselspalten.

Wenn in InnoDB einige Zeilen gelöscht werden, werden sie nur als „gelöscht“ markiert, anstatt physisch aus dem Index gelöscht zu werden. Der Speicherplatz wird also nicht wirklich freigegeben und zurückgewonnen. Der Purge-Thread von InnoDB bereinigt diese ungenutzten Indexschlüssel und Zeilen asynchron. Der freigegebene Speicherplatz wird jedoch dennoch nicht zur erneuten Verwendung an das Betriebssystem zurückgegeben, was zu zahlreichen Löchern in den Seiten führt. Wenn die Tabellenstruktur Felder mit dynamischer Länge enthält, können diese Lücken von InnoDB möglicherweise nicht einmal zum Speichern neuer Zeilen wiederverwendet werden, da nicht genügend Speicherplatz vorhanden ist. Weitere Informationen hierzu finden Sie im Blog „Übersicht über fragmentierte MySQL InnoDB-Tabellen“.

Darüber hinaus führt das Löschen von Daten dazu, dass auf der Seite Leerräume erscheinen. Eine große Anzahl zufälliger Löschvorgänge führt zwangsläufig zu diskontinuierlichen Leerräumen in der Datendatei. Beim Einfügen von Daten werden diese Leerzeichen verwendet, wodurch es zu nicht zusammenhängenden Speicherorten für die Daten kommt. Die physische Speicherreihenfolge unterscheidet sich von der logischen Sortierreihenfolge, bei der es sich um eine Datenfragmentierung handelt.

Eine große Anzahl von UPDATEs führt auch zu einer Dateifragmentierung. Die kleinste physische Speicherzuordnungseinheit von Innodb ist eine Seite, und UPDATEs können auch zu Seitenaufteilungen führen. Häufige Seitenaufteilungen führen dazu, dass Seiten spärlich und unregelmäßig gefüllt werden, sodass die Daten schließlich fragmentiert werden.

Zunächst einmal müssen Sie verstehen, dass Mysql-Tabellen fragmentiert werden, wenn eine Zeile aktualisiert wird. Das ist also eine normale Situation. Wenn eine Tabelle erstellt wird, beispielsweise importiert mit einem Dump mit Daten, werden alle Zeilen ohne Fragmentierung auf vielen Seiten mit fester Größe gespeichert. Wenn Sie eine Zeile mit variabler Länge aktualisieren, wird die Seite, die diese Zeile enthält, in zwei oder mehr Seiten aufgeteilt, um die Änderungen zu speichern. Diese neuen zwei (oder mehr) Seiten enthalten Leerzeichen, die den ungenutzten Platz füllen.

Außerdem kann es zu einer Fragmentierung des Datenspeichers der Tabelle kommen. Die Fragmentierung der Datenspeicherung ist jedoch komplizierter als die Indizierung. Es gibt drei Arten der Datenfragmentierung. ##Der folgende Teil ist ein Auszug aus [High Performance MySQL]##

Zeilenfragmentierung

Diese Fragmentierung bedeutet, dass die Datenzeilen in mehreren Teilen an mehreren Stellen gespeichert werden. Auch wenn die Abfrage nur auf eine Zeile aus dem Index zugreift. Auch die Zeilenfragmentierung kann zu einer Verschlechterung der Leistung führen.

Zeileninterne Fragmentierung

Eine Zeilenfragmentierung tritt auf, wenn logisch aufeinanderfolgende Seiten oder Zeilen nicht sequenziell auf der Festplatte gespeichert werden. Die Zeilenfragmentierung wirkt sich erheblich auf Vorgänge wie vollständige Tabellenscans und Clustered-Index-Scans aus, die von der sequenziellen Speicherung der Daten auf der Festplatte profitieren.

Fragmentierung des freien Speicherplatzes

Die verbleibende Speicherplatzfragmentierung bezieht sich auf eine große Menge an freiem Speicherplatz auf einer Datenseite. Dies kann dazu führen, dass der Server viele unnötige Daten liest. Dies führt zu Abfall.

Bei MyISAM-Tabellen können alle drei Arten der Fragmentierung auftreten. Aber InnoDB verfügt nicht über eine Fragmentierung kurzer Zeilen. InnoDB verschiebt kurze Zeilen und schreibt sie in ein Fragment. InnoDb verschiebt kurze Zeilen und schreibt sie in ein Fragment neu.

Im offiziellen Dokument 14.15.4 „Defragmentieren einer Tabelle“ wird wie folgt beschrieben, wie die Tabellenfragmentierung reduziert werden kann (sehr prägnant, offizielle MySQL-Dokumente sind oft prägnant und enthalten viele Informationen, aber keine ausführliche Einführung):

Zufällige Einfügungen oder Löschungen in einem sekundären Index können dazu führen, dass der Index fragmentiert wird. Fragmentierung bedeutet, dass die physische Reihenfolge der Indexseiten auf der Festplatte nicht annähernd der Indexreihenfolge der Datensätze auf den Seiten entspricht oder dass die dem Index zugewiesenen 64-Seiten-Blöcke viele unbenutzte Seiten enthalten.

Ein Symptom der Fragmentierung ist, dass eine Tabelle mehr Platz einnimmt, als sie „einnehmen sollte“. Wie viel das genau ist, lässt sich nur schwer bestimmen. Alle InnoDB-Daten und -Indizes werden in B-Bäumen gespeichert und ihr Füllfaktor kann zwischen 50 % und 100 % variieren.

Zufällige Einfügungen oder Löschungen aus einem sekundären Index können zu einer Fragmentierung des Index führen. Fragmentierung bedeutet, dass die physische Reihenfolge der Indexseiten auf der Festplatte nicht annähernd der auf den Seiten aufgezeichneten Indexreihenfolge entspricht oder dass in den dem Index zugewiesenen 64-Seiten-Blöcken viele ungenutzte Seiten vorhanden sind.

Ein Symptom der Fragmentierung ist eine Tabelle, die mehr Platz einnimmt, als sie „einnehmen sollte“. Wie viel genau, ist schwer zu bestimmen. Alle InnoDB-Daten und -Indizes werden in B-Bäumen gespeichert, deren Füllfaktor zwischen 50 % und 100 % variieren kann. Ein weiteres Symptom der Fragmentierung ist, dass ein Tabellenscan wie dieser viel länger dauert, als er "dauern sollte".

So finden Sie stark fragmentierte Tabellen in MySQL

In Bezug auf die Tabellenfragmentierung in MySQL gibt es im Allgemeinen zwei Möglichkeiten, die Fragmentierung in MySQL herauszufinden.

Methode 1: Verwenden Sie „Tabellenstatus von xxxx anzeigen“, beispielsweise „xxxx“ \G;

Das erste xxx: der Name der Datenbank, in der sich die Tabelle befindet, das zweite xxx: der Name der abzufragenden Tabelle. Eigentlich ist diese Methode nicht sehr praktisch. Sie können beispielsweise nur die Fragmentierung einer einzelnen Tabelle abfragen (müssen Sie dies für jede Tabelle einer Datenbank versuchen?), und Sie können nicht die Fragmentierung aller Tabellen einer Datenbank oder der gesamten Instanz abfragen. Dies ist nur eine Referenzmethode.

mysql> Tabelle frag_tab_myisam erstellen
    -> (
    -> ID int,
    -> Name varchar(63)
    ->) engine=MyISAM;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)
 
mysql> einfügen in frag_tab_myisam
    -> Werte (1, „es ist nur Testzeile 1“);
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)
 
MySQL> 
mysql> einfügen in frag_tab_myisam
    -> values(2, 'es ist nur Testzeile 2');
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)
 
MySQL> 
MySQL> 
mysql> einfügen in frag_tab_myisam
    -> values(3, 'es ist nur Testzeile 3');
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)
 
MySQL> 
mysql> einfügen in frag_tab_myisam
    -> values(4, 'es ist nur Testzeile 4');
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)
 
MySQL>
 
 
mysql> Tabellenstatus von kkk anzeigen wie „frag_tab_myisam“ \G;

Wie im folgenden Screenshot gezeigt, ist die Größe von Data_free 0, wenn kein DML-Vorgang stattfindet.

Dann löschen wir 2 Datensätze aus der Datenbank. Wie unten gezeigt beträgt die Größe von Data_free 64 KB.

mysql> löschen aus frag_tab_myisam, wobei ID = 1;
Abfrage OK, 1 Zeile betroffen (0,00 Sek.)
 
mysql> löschen aus frag_tab_myisam, wo ID = 3;
Abfrage OK, 1 Zeile betroffen (0,00 Sek.) 

Methode 2: Fragen Sie information_schema.TABLES ab, um Informationen zur Tabellenfragmentierung zu erhalten.

Wie unten gezeigt ist dies ein klassisches Skript, das ich zum Abfragen der Tabellenfragmentierung kompiliert habe. Davon lassen sich zahlreiche Ableitungen machen: zum Beispiel die Tabellenfragmentierung einer Datenbank abfragen. Oder die Tabelle verfügt über mehr als 50 MB freien Speicherplatz. Sie können die Abfragebedingungen entsprechend Ihren Bedürfnissen festlegen. Überspringen Sie das hier.

SELECT CONCAT(Tabellenschema, '.', Tabellenname) AS TABLE_NAME
      ,Motor als Tabellenmotor 
      ,Tabellentyp AS TABLE_TYPE
      ,Tabellenzeilen AS TABLE_ROWS
      ,CONCAT(ROUND(Datenlänge / ( 1024 * 1024), 2), 'M') AS TB_DATA_SIZE 
      ,CONCAT(ROUND(Indexlänge / (1024 * 1024), 2), 'M') AS TB_IDX_SIZE 
      ,CONCAT(ROUND((Datenlänge + Indexlänge) 
            / ( 1024 * 1024 ), 2), 'M') AS TOTAL_SIZE
      ,FALL WENN Datenlänge = 0 DANN 0
            ELSE ROUND(Indexlänge / Datenlänge, 2) END AS TB_INDX_RATE
    ,CONCAT(ROUND( daten_frei / 1024 / 1024,2), 'MB') AS TB_DATA_FREE 
    ,FALL WENN (Datenlänge + Indexlänge) = 0 DANN 0
             SONST RUNDEN(freie_Daten/(Datenlänge + Indexlänge),2) 
     ENDE ALS TB_FRAG_RATE
VON information_schema.TABLES  
ORDER BY datenfrei DESC;
SELECT CONCAT(Tabellenschema, '.', Tabellenname) AS TABLE_NAME
      ,Motor als Tabellenmotor 
      ,Tabellentyp AS TABLE_TYPE
      ,Tabellenzeilen AS TABLE_ROWS
      ,CONCAT(ROUND(Datenlänge / ( 1024 * 1024), 2), 'M') AS TB_DATA_SIZE 
      ,CONCAT(ROUND(Indexlänge / (1024 * 1024), 2), 'M') AS TB_IDX_SIZE 
      ,CONCAT(ROUND((Datenlänge + Indexlänge) 
            / ( 1024 * 1024 ), 2), 'M') AS TOTAL_SIZE
      ,FALL WENN Datenlänge = 0 DANN 0
            ELSE ROUND(Indexlänge / Datenlänge, 2) END AS TB_INDX_RATE
    ,CONCAT(ROUND( daten_frei / 1024 / 1024,2), 'MB') AS TB_DATA_FREE 
    ,FALL WENN (Datenlänge + Indexlänge) = 0 DANN 0
             SONST RUNDEN(freie_Daten/(Datenlänge + Indexlänge),2) 
     ENDE ALS TB_FRAG_RATE
VON information_schema.TABLES  
WO RUNDEN(DATA_FREE/1024/1024,2) >=50
ORDER BY datenfrei DESC;
 

SELECT TABLE_SCHEMA
      ,Tabellenname 
      ,MOTOR
      ,ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS SIZE_MB
      ,ROUND(DATA_FREE/1024/1024,2) AS FREE_SIZ_MB
VON information_schema.TABLES 
WO DATENFREI >=10*1024*1024
Bestellen nach Free_Siz_MB Desc;

So reduzieren Sie die Tabellenfragmentierung in MySQL

In MySQL können Sie OPTIMIZE TABLE und ALTER TABLE XXXX ENGINE = INNODB verwenden, um die Fragmentierung zu reduzieren. Eine kurze Einführung in diese beiden Methoden ist wie folgt:

TABELLE OPTIMIEREN

OPTIMIZE TABLE organisiert den physischen Speicher von Tabellen und Indizes neu, reduziert den Speicherplatzverbrauch und verbessert die IO-Effizienz beim Zugriff auf Tabellen. Die genauen an jeder Tabelle vorgenommenen Änderungen hängen von der von dieser Tabelle verwendeten Speicher-Engine ab.

OPTIMIZE TABLE unterstützt die folgenden Tabellentypen: INNODB, MYISAM, ARCHIVE und NDB. Es organisiert die physischen Seiten der Tabellendaten und Indizes neu, wodurch der belegte Speicherplatz effektiv reduziert und die E/A beim Zugriff auf die Tabelle optimiert wird. Der OPTIMIZE-Vorgang sperrt die Tabelle vorübergehend. Je größer die Datenmenge ist, desto länger dauert der Vorgang.

Nach OPTIMIZE TABLE beziehen sich die Änderungen in der Tabelle auf die Speicher-Engine.

Für MyISAM funktioniert PTIMIZE TABLE wie folgt:

• Wenn die Tabelle gelöschte oder geteilte Zeilen aufweist, reparieren Sie die Tabelle.
• Sortieren Sie die Indexseiten, wenn sie nicht sortiert sind.
• Wenn die Statistiken der Tabelle nicht auf dem neuesten Stand sind (und sie nicht durch Sortieren des Index repariert werden können), aktualisieren Sie sie.

Der englische Originaltext lautet wie folgt:

Für MyISAM-Tabellen funktioniert OPTIMIZE TABLE wie folgt:

1. Wenn die Tabelle gelöschte oder geteilte Zeilen enthält, reparieren Sie die Tabelle.

2. Wenn die Indexseiten nicht sortiert sind, sortieren Sie sie.

3. Wenn die Statistiken der Tabelle nicht auf dem neuesten Stand sind (und die Reparatur nicht durch Sortieren des Indexes durchgeführt werden konnte), aktualisieren Sie sie.

Für InnoDB funktioniert PTIMIZE TABLE wie folgt

Bei InnoDB-Tabellen wird OPTIMIZE TABLE auf ALTER TABLE ... FORCE abgebildet, wodurch die Tabelle neu erstellt wird, um die Indexstatistiken zu aktualisieren und ungenutzten Speicherplatz im gruppierten Index freizugeben. Wenn Sie es auf einer InnoDB-Tabelle ausführen, wird es in der Ausgabe von OPTIMIZE TABLE wie folgt angezeigt:

mysql> TABELLE OPTIMIEREN foo;
 
+----------+----------+----------+-------------------------------------------------------------------+
 
| Tabelle | Op | Nachrichtentyp | Nachrichtentext |
 
+----------+----------+----------+-------------------------------------------------------------------+
 
| test.foo | optimieren | Hinweis | Die Tabelle unterstützt kein Optimieren, stattdessen wird „Neu erstellen + Analysieren“ ausgeführt |
 
| test.foo | optimieren | Status | OK |
 
+----------+----------+----------+-------------------------------------------------------------------+

OPTIMIZE TABLE verwendet Online-DDL für normale und partitionierte InnoDB-Tabellen und reduziert so die Ausfallzeit für gleichzeitige DML-Operationen. Der Neuaufbau der Tabelle wird durch OPTIMIZE TABLE ausgelöst und erfolgt unter dem Deckmantel von ALTER TABLE ... FORCE. Exklusive Tabellensperren werden nur kurzzeitig während der Vorbereitungs- und Festschreibungsphasen des Vorgangs genutzt. Während der Vorbereitungsphase werden Metadaten aktualisiert und Zwischentabellen erstellt. Während der Commit-Phase werden Änderungen an den Tabellenmetadaten festgeschrieben.

OPTIMIZE TABLE erstellt die Tabelle unter den folgenden Bedingungen mit der Tabellenkopiermethode neu:

• Wenn die Systemvariable old_alter_table aktiviert ist.
• Wenn die mysqld --skip-new-Option aktiviert ist.

OPTIMIZE TABLE unterstützt kein Online-DDL für InnoDB-Tabellen, die FULLTEXT-Indizes enthalten. Verwenden Sie stattdessen die Methode zum Kopieren der Tabelle.

InnoDB verwendet eine Methode zur Seitenzuweisung zum Speichern von Daten und leidet nicht unter Fragmentierung wie herkömmliche Speicher-Engines wie MyISAM. Berücksichtigen Sie bei der Entscheidung, ob eine Optimierung ausgeführt werden soll, die Arbeitslast der Transaktionen, die der Server verarbeiten wird:

• Es wird mit einem gewissen Grad an Fragmentierung gerechnet. InnoDB füllt nur 93 % der Seite und lässt so Raum für Aktualisierungen, ohne die Seite aufzuteilen.
• Durch Löschungen können Lücken entstehen, wodurch die Seite weniger ausgefüllt ist als erwartet. Eine Optimierung der Tabelle kann sich daher lohnen.

Bei Aktualisierungen einer Zeile werden die Daten in der Regel auf derselben Seite neu geschrieben, wenn für die Zeile ausreichend Platz vorhanden ist. Dies hängt vom Datentyp und Zeilenformat ab. Siehe Abschnitt 14.9.1.5, „Funktionsweise der Komprimierung für InnoDB-Tabellen“ und Abschnitt 14.11, „InnoDB-Zeilenformate“.

Bei stark gleichzeitig ausgeführten Arbeitslasten können mit der Zeit Lücken in den Indizes entstehen, da InnoDB über seinen MVCC-Mechanismus mehrere Versionen derselben Daten speichert. Siehe Abschnitt 14.3, „InnoDB Multi-Versioning“.

Darüber hinaus organisiert OPTIMIZE TABLE für InnoDB-Tabellen mit innodb_file_per_table=1 den physischen Speicher von Tabellen und Indizes neu und gibt freien Speicherplatz für das Betriebssystem frei. Das bedeutet, dass OPTIMIZE TABLE [Tabellenname] nur auf unabhängige Tabellenbereiche anwendbar ist.

Weitere Einzelheiten zu OPTIMIZE TABLE finden Sie unter https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html. Ich finde, dass die offizielle Dokumentation ziemlich detailliert ist.

ALTER TABLE Tabellenname ENGINE = Innodb;

Dies ist eigentlich eine NULL-Operation. Oberflächlich betrachtet tut sie nichts, aber tatsächlich organisiert sie die Fragmente neu. Wenn die Optimierungsoperation ausgeführt wird, wird tatsächlich ein leerer ALTER-Befehl ausgeführt, aber dieser Befehl spielt auch eine Optimierungsrolle. Er erstellt die gesamte Tabelle neu und löscht ungenutzten Leerraum.

Das Ausführen von ALTER TABLE tbl_name ENGINE=INNODB auf einer vorhandenen InnoDB-Tabelle führt eine „null“-ALTER TABLE-Operation aus, die zum Defragmentieren einer InnoDB-Tabelle verwendet werden kann, wie in Abschnitt 15.11.4, „Defragmentieren einer Tabelle“ beschrieben. Das Ausführen von ALTER TABLE tbl_name FORCE auf einer InnoDB-Tabelle führt dieselbe Funktion aus.

Frage 1: Ist es besser, OPTIMIZE TABLE oder ALTER TABLE xxxx ENGINE = INNODB zu verwenden?

Tatsächlich führt ALTER TABLE xxxx ENGINE = INNODB für die InnoDB-Engine eine leere ALTER TABLE-Operation aus. OPTIMIZE TABLE entspricht ALTER TABLE ... FORCE. In Bezug auf die obige Beschreibung ist OPTIMIZE TABLE oder ALTER TABLE xxxx ENGINE= INNODB in einigen Fällen grundsätzlich dasselbe. Aber in manchen Fällen ist ALTER TABLE xxxx ENGINE= INNODB besser. Beispielsweise ist die Systemvariable old_alter_table nicht aktiviert usw. Darüber hinaus ist für Tabellen vom Typ MyISAM die Verwendung von ALTER TABLE xxxx ENGINE = INNODB offensichtlich besser als OPTIMIZE TABLE.

Frage 2: Werden Indexfragmente in der Tabelle ALTER TABLE xxxx ENGINE= INNODB defragmentiert?

ALTER TABLE ENGINE = INNODB organisiert die Daten und Indizes im gruppierten Index neu. Wenn Sie dies experimentell überprüfen möchten, können Sie die Größe von index_length vor und nach der Ausführung dieses Befehls vergleichen.

Andere Tools

Internetnutzer empfehlen die Verwendung von pt-Tools oder gh-ost, um die Tabellenfragmentierung zu reduzieren. Ich habe solche Tools noch nicht verwendet, aber sie kapseln wahrscheinlich die beiden oben genannten Befehle. Darauf wird hier nicht näher eingegangen.

Quellen:

Leistungsstarkes MySQL

https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html

https://dev.mysql.com/doc/refman/8.0/en/innodb-file-defragmenting.html

https://lefred.be/content/ueberblick-ueber-fragmentierte-mysql-innodb-tables/

https://yq.aliyun.com/articles/41166

http://mysql.taobao.org/monthly/2015/08/05/

Dies ist das Ende dieses Artikels über Methoden zur Defragmentierung von MySQL-Tabellen und zur Speicherplatzwiederherstellung. Weitere Informationen zur Defragmentierung von MySQL-Tabellen und zur Speicherplatzwiederherstellung 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!