Detailliertes Beispiel zum Beheben der Tablespace-Fragmentierung in MySQL

Detailliertes Beispiel zum Beheben der Tablespace-Fragmentierung in MySQL

Ursachen der Fragmentierung

(1) Der Speicher der Tabelle wird fragmentiert. Immer wenn eine Zeile gelöscht wird, wird der Speicherplatz leer und bleibt leer. Eine große Anzahl von Löschvorgängen über einen bestimmten Zeitraum hinweg führt dazu, dass der leere Speicherplatz größer wird als der Speicherplatz, der zum Speichern des Tabelleninhalts verwendet wird.

(2) Bei der Ausführung von Einfügevorgängen versucht MySQL, leeren Speicherplatz zu verwenden. Wenn ein leerer Speicherplatz jedoch nicht mit Daten der entsprechenden Größe belegt ist, kann er dennoch nicht vollständig belegt werden, was zu einer Fragmentierung führt.

(3) Wenn MySQL Daten scannt, scannt es tatsächlich die Obergrenze des Kapazitätsbedarfs der Tabelle, dh den Spitzenbereich des Bereichs, in den die Daten geschrieben werden.

Zum Beispiel:

Eine Tabelle hat 10.000 Zeilen, jede Zeile ist 10 Byte groß und belegt 100.000 Byte Speicherplatz. Wenn ein Löschvorgang ausgeführt wird, bleibt nur eine Zeile übrig und der eigentliche Inhalt beträgt nur 10 Byte. Wenn MySQL sie jedoch liest, behandelt es sie immer noch als 100.000-Byte-Tabelle. Je mehr Fragmente vorhanden sind, desto stärker wird die Abfrageleistung beeinträchtigt.

Überprüfen Sie die Größe von Tabellenfragmenten

(1) Überprüfen Sie die Fragmentgröße einer Tabelle

mysql> ZEIGE TABELLENSTATUS WIE 'Tabellenname';

Der Wert der Spalte „Data_free“ im Ergebnis ist die Fragmentgröße

(2) Liste aller Tabellen, die fragmentiert wurden

mysql> wähle table_schema db, table_name, data_free, engine   
aus information_schema.tables 
wobei table_schema nicht in ('information_schema', 'mysql') und data_free > 0 ist;

Klare Tabellenfragmentierung

(1) MyISAM-Tabelle

mysql> Tabelle Tabellenname optimieren

(2) InnoDB-Tabelle

mysql> Tabelle ändern Tabellenname Engine = InnoDB

Die Funktionsweise von OPTIMIZE ist bei verschiedenen Engines unterschiedlich. Da Index und Daten von MyISAM getrennt sind, kann OPTIMIZE die Datendateien sortieren und den Index neu anordnen.

Der OPTIMIZE-Vorgang sperrt die Tabelle vorübergehend. Je größer die Datenmenge, desto länger dauert es. Schließlich handelt es sich nicht um einen einfachen Abfragevorgang. Daher ist es nicht sinnvoll, den Optimize-Befehl in das Programm einzufügen. Unabhängig davon, wie niedrig die Trefferquote eingestellt ist, steigt mit zunehmender Anzahl der Besuche auch die Gesamttrefferquote, was sich definitiv stark auf die Ausführungseffizienz des Programms auswirkt. Eine bessere Möglichkeit besteht darin, eine Shell zu erstellen und regelmäßig das Feld information_schema.TABLES in MySQL zu überprüfen. Überprüfen Sie das Feld DATA_FREE. Wenn es größer als 0 ist, bedeutet dies, dass eine Fragmentierung vorliegt.

Anregung

Der Löschvorgang sperrt die Tabelle vorübergehend. Je größer die Datenmenge, desto länger dauert es. Sie können ein Skript erstellen und es regelmäßig während der Zeiten mit geringem Zugriff ausführen. Überprüfen Sie beispielsweise jeden Mittwoch im Morgengrauen das Feld DATA_FREE. Wenn es größer ist als der von Ihnen vermutete Warnwert, bereinigen Sie es einmal.

Wenn Sie Fragen haben, hinterlassen Sie bitte eine Nachricht oder kommen Sie zur Diskussion in die Community. Vielen Dank fürs Lesen und ich hoffe, es kann Ihnen helfen. Vielen Dank für Ihre Unterstützung dieser Site!