Das Konzept der MySQL-Tablespace-Fragmentierung und Lösungen für damit verbundene Probleme

Hintergrund

Wenn Sie MySQL häufig verwenden, werden Sie feststellen, dass der Speicherplatz von MySQL-Datendateien im Allgemeinen ständig wächst und dass der Speicherplatz manchmal unverändert bleibt, wenn Sie Daten löschen oder einen Datenstapel einfügen. Die Ursache für dieses seltsame Phänomen ist die Fragmentierung des MySQL-Tabellenbereichs.

Was ist Tablespace-Fragmentierung?

Tablespace-Fragmentierung bezieht sich auf die Existenz von Fragmenten im Tablespace. Um eine anschaulichere Metapher zu verwenden: Es ist wie ein Blatt A4-Papier. „Tablespace-Fragmentierung“ ist, als würde man dieses A4-Papier in Stücke reißen und dann wieder zusammensetzen. Zwischen den Stücken entstehen einige Lücken, und diese Lücken sind „Tablespace-Fragmente“. Die wieder zusammengesetzten Fragmente sind tatsächlich größer als ein ganzes DIN A4-Blatt, was ebenfalls ein Problem darstellt, das bei der Tabellenplatzierung leicht auftreten kann: Platzverschwendung.

Das im Hintergrund beschriebene Phänomen lässt sich anhand eines Bildes erklären:

Die Zahlen in der Abbildung stellen tatsächliche Datenzeilen dar und das abgerundete Rechteck repräsentiert den Tabellenbereich einer Tabelle. Von links nach rechts besteht die erste Operation darin, Daten zu löschen. Da MySQL so konzipiert ist, dass Speicherplatz nicht aktiv freigegeben wird, wird dieser Teil des Speicherplatzes beim Löschen der Datenzeile in der Tabelle zwar „gelöscht“, aber nicht wirklich freigegeben und wird weiterhin von Tabelle A belegt. Daher tritt das folgende Szenario auf: Viele Daten in der Protokolltabelle werden gelöscht, aber der Speicherplatz von MySQL wird nicht reduziert.

PS: Dieses Design, bei dem kein Speicherplatz freigegeben wird, hängt hauptsächlich mit verzögertem Löschen zusammen. Beim Entwurf früherer Datenbanken wurden als IO-Geräte im Allgemeinen mechanische Festplatten verwendet, deren Lese- und Schreibleistung viel schlechter war als die von SSDs, sodass Löschvorgänge im Allgemeinen nicht direkt das Löschen von Daten auf der Festplatte auslösten.

Es ist ersichtlich, dass nach dem Löschen der Daten im ursprünglich zusammenhängenden Raum zwei leere Bereiche erscheinen. Dies wird im Allgemeinen als Tablespace-Loch bezeichnet. Wenn zu viele Löcher vorhanden sind, spricht man von Tablespace-Fragmentierung (der entsprechende Tablespace ist zusammenhängend). Obwohl dieser Teil des Speicherplatzes nicht freigegeben wird, wird er als wiederverwendbar gekennzeichnet. Siehe das Tablespace-Diagramm ganz rechts (das dritte abgerundete Rechteck). Wenn neue Daten eingefügt werden, werden die neuen Daten in die Lücke im Tablespace neu geschrieben. Dies bedeutet auch, dass beim Schreiben von Daten in eine Tabelle, in der viele Daten gelöscht wurden, der Tablespace möglicherweise nicht wesentlich oder überhaupt nicht wächst.

Tatsächlich ist Löschen nicht die einzige Operation, die Tabellenraumlücken verursachen kann. Auch Aktualisieren kann dieses Problem verursachen. Wenn Sie beispielsweise Daten in einer Zeichenspalte mit variabler Länge wie varchar ändern, entsteht eine sehr kleine Lücke, wenn die Spalte gekürzt wird. Wenn die Spalte verlängert wird, können einige Daten in der Datenzeile aufgrund von Platzmangel an andere Stellen migriert werden.

So überprüfen Sie die Tablespace-Fragmentierung

Die Systemtabellen von MySQL zeichnen die Tablespace-Nutzung auf, die mit der folgenden Abfrage überprüft werden kann:

SELECT CONCAT(Tabellenschema,'.',Tabellenname) AS 'Tabellenname',
                table_rows AS 'Anzahl der Zeilen',
                CONCAT(ROUND(Datenlänge/(1024*1024),2),' M') AS 'Datengröße',
                CONCAT(ROUND(Indexlänge/(1024*1024),2),' M') AS 'Indexgröße' ,
                CONCAT(ROUND(datenfrei/(1024*1024),2),' M') AS'datenfrei',
                CONCAT(ROUND(freie_Daten/Datenlänge,2),' %') AS 'prozentualer_Datenanteil',
                ENGINE als „Motor“
VON information_schema.TABLES
WHERE table_schema = 'Tabellenname' 
ORDER nach data_free desc;

data_free bezieht sich auf die Gesamtgröße des Tabellenbereichs, der fragmentiert ist, und data_free_pct bezieht sich auf den Fragmentierungsprozentsatz dieser Tabelle. Die Auswirkungen sind wie folgt:

mysql> SELECT CONCAT(Tabellenschema,'.',Tabellenname) AS 'Tabellenname',
    -> table_rows AS 'Anzahl der Zeilen',
    -> CONCAT(ROUND(Datenlänge/(1024*1024),2),' M') AS 'Datengröße',
    -> CONCAT(ROUND(Indexlänge/(1024*1024),2),' M') AS 'Indexgröße' ,
    -> CONCAT(ROUND(datenfrei/(1024*1024),2),' M') AS'datenfrei',
    -> CONCAT(ROUND(freie_Daten/Datenlänge,2),' %') AS 'prozentualer_Datenanteil',
    -> ENGINE als „Motor“
    -> VON information_schema.TABLES
    -> WO Tabellenschema = "sbtest"
    -> ORDER nach data_free desc;
+----------------+----------------+-----------+------------+-----------+---------------+--------+
| Tabellenname | Anzahl der Zeilen | Datengröße | Indexgröße | freie Daten | Prozentwert der freien Daten | Engine |
+----------------+----------------+-----------+------------+-----------+---------------+--------+
| sbtest.sbtest5 | 0 | 0,02 M | 0,00 M | 44,00 M | 2816,00 % | InnoDB |
| sbtest.sbtest4 | 986400 | 214,70 M | 15,52 M | 4,00 M | 0,02 % | InnoDB |
| sbtest.sbtest3 | 986400 | 214,70 M | 15,52 M | 4,00 M | 0,02 % | InnoDB |
| sbtest.sbtest2 | 986400 | 214,70 M | 15,52 M | 4,00 M | 0,02 % | InnoDB |
| sbtest.sbtest1 | 987400 | 199,70 M | 15,52 M | 4,00 M | 0,02 % | InnoDB |
+----------------+----------------+-----------+------------+-----------+---------------+--------+
5 Zeilen im Satz (0,00 Sek.)

Die erste Datenzeile wird zum Testen verwendet. Alle Daten in der Tabelle wurden gelöscht, sodass der berechnete data_free_pct über 100 % liegt.

So lösen Sie das Problem der Tabellenbereichsfragmentierung

Derzeit gibt es nur eine Möglichkeit, Tabellenspeicherplatz zurückzugewinnen, nämlich durch den Neuaufbau der Tabelle, einschließlich, aber nicht beschränkt auf Optimieren, Ändern der Tabelle usw. Einige ALTER-Table-Vorgänge können nur durch Neuerstellung der Tabelle abgeschlossen werden. Daher werden Sie manchmal nach der Durchführung einiger Wartungsvorgänge an einer großen Tabelle einen Rückgang der Festplattenspeichernutzung feststellen. Dies liegt daran, dass der durch Tabellenbereichsfragmente freigegebene Speicherplatz zurückgewonnen wurde.

Aus allgemeiner Erfahrung ist es nicht empfehlenswert, die Wiederherstellung der Tabellenbereichsfragmentierung häufig durchzuführen. Einmal im Monat ist ausreichend, da der Neuaufbau der Tabelle größere Auswirkungen auf die Serverressourcen hat und den Schreibvorgang dieser Tabelle beeinträchtigt. Sie müssen sich keine allzu großen Sorgen machen, wenn die Fragmentierungsrate (data_free_pct) unter 20 % liegt, es sei denn, der Speicherplatz ist sehr knapp und die Protokolle sind grundsätzlich gelöscht.

Zum Thema Raumrückgewinnung

Für einige Protokolltabellen oder Tabellen mit regionalen Merkmalen wird empfohlen, MySQL-Partitionstabellen zu verwenden, um sie zu verwalten. Wenn ein Datenstapel bereinigt werden muss, kann die Partitionierung zum Bereinigen verwendet werden, und der Speicherplatz kann direkt freigegeben werden.

Oben finden Sie ausführliche Informationen zum Konzept der MySQL-Tabellenbereichsfragmentierung und zur Lösung verwandter Probleme. Weitere Informationen zur MySQL-Tabellenbereichsfragmentierung finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!