Optimieren Sie die Speichereffizienz von BLOB- und TEXT-Spalten in InnoDB-Tabellen

Lassen Sie uns zunächst einige wichtige Punkte zum Speicherformat der MySQL InnoDB-Engine vorstellen:

1. InnoDB kann einen gemeinsam genutzten oder einen unabhängigen Tabellenbereich verwenden. Zur einfacheren Verwaltung und Wartung wird die Verwendung eines unabhängigen Tabellenbereichs empfohlen. Aktivieren Sie die Option innodb_file_per_table . Ab 5.5 können Sie sie dynamisch online ändern, damit sie wirksam wird, und ALTER TABLE xx ENGINE = InnoDB ausführen, um die vorhandene Tabelle in einen unabhängigen Tablespace umzuwandeln. Bei Versionen vor 5.5 müssen Sie nach der Änderung dieser Option neu starten, damit sie wirksam wird.

2. Die Standardgröße der InnoDB-Datenseite beträgt 16 KB. Nach Version 5.6 kann die neue Option innodb_page_size geändert werden. In Versionen vor 5.6 können Sie nur den Quellcode ändern und neu kompilieren. Es wird jedoch nicht empfohlen, diese Konfiguration zu ändern, es sei denn, Sie sind sich über ihre Vor- und Nachteile im Klaren.

3. Wenn neue Daten auf die InnoDB-Datenseite geschrieben werden, wird 1/16 des Speicherplatzes reserviert. Der reservierte Speicherplatz kann zum nachfolgenden Schreiben neuer Datensätze verwendet werden, wodurch der Aufwand für das häufige Hinzufügen neuer Datenseiten verringert wird.

4. Jede Datenseite muss mindestens zwei Datensatzzeilen speichern. Daher beträgt die maximale Länge eines Zeilendatensatzes theoretisch 8 KB, tatsächlich sollte sie aber kleiner sein, da einige interne InnoDB-Datenstrukturen gespeichert werden müssen.

5. Begrenzt durch die InnoDB-Speichermethode: Wenn die Daten sequenziell geschrieben werden, beträgt die ideale Füllrate der Datenseite 15/16, es ist jedoch im Allgemeinen unmöglich, ein vollständiges sequenzielles Schreiben zu garantieren. Daher beträgt die Füllrate der Datenseite im Allgemeinen 1/2 bis 15/16. Daher ist es für jede InnoDB-Tabelle am besten, eine Auto-Increment-Spalte als Primärschlüssel zu haben, damit neue Datensätze möglichst sequenziell geschrieben werden können.

6. Wenn die Füllrate der Datenseite weniger als 1/2 beträgt, verkleinert InnoDB sie, um freien Speicherplatz freizugeben.

7. Die InnoDB-Engine von MySQL 5.6 unterstützt derzeit vier Formate: COMPACT , REDUNDANT , DYNAMIC und COMPRESSED . Das Standardformat ist COMPACT. COMPRESSED wird selten verwendet und ist nicht zu empfehlen (siehe nächster Punkt). Wenn Sie die Komprimierungsfunktion verwenden müssen, können Sie direkt die TokuDB-Engine in Betracht ziehen.

8. Das Zeilenformat COMPACT kann im Vergleich zu REDUNDANT etwa 20 % Speicherplatz einsparen, und COMPRESSED kann im Vergleich zu COMPACT etwa 50 % Speicherplatz einsparen, führt jedoch zu einem TPS-Abfall um 90 %. Daher wird von der Verwendung des KOMPRIMIERTEN Zeilenformats dringend abgeraten.

9. Wenn das Zeilenformat DYNAMISCH oder KOMPRIMIERT ist, werden lange Spalten wie TEXT/BLOB (lange Spalten, die je nach konkreter Situation auch andere lange Spalten sein können, nicht unbedingt nur TEXT/BLOB-Typen) vollständig auf einer unabhängigen Datenseite gespeichert, und die gruppierte Indexseite verwendet nur einen 20-Byte-Zeiger, um auf die neue Seite zu verweisen. Dies ist die sogenannte Offpage, ähnlich der Zeilenmigration von ORACLE, die viel Speicherplatz verschwendet und eine schlechte E/A-Leistung aufweist. Daher wird dringend empfohlen, keine BLOB-, TEXT- oder VARCHAR-Spaltentypen mit einer Länge von mehr als 255 zu verwenden.

10. Wenn das InnoDB-Dateiformat ( innodb_file_format ) auf Antelope eingestellt ist und das Zeilenformat COMPACT oder REDUNDANT ist, speichern BLOB-, TEXT- oder lange VARCHAR-Spalten nur die ersten 768 Bytes in der gruppierten Indexseite (die maximalen 768 Bytes werden verwendet, um die Erstellung von Präfixindizes zu erleichtern). Der restliche Inhalt wird in zusätzlichen Seiten gespeichert, auch wenn es nur ein Byte mehr ist. Daher sollten alle Spaltenlängen so kurz wie möglich sein.

11. Die außerhalb der Seite gespeicherte Seite mit BLOB-, TEXT- oder langen VARCHAR-Spalten ist exklusiv und kann nicht gemeinsam genutzt werden. Daher wird dringend davon abgeraten, mehrere lange Spalten in einer Tabelle zu verwenden.

Zusammenfassend sind hier einige Vorschläge, wenn Sie im tatsächlichen Geschäftsbetrieb wirklich BLOB-, TEXT- oder lange VARCHAR-Spalten in einer InnoDB-Tabelle speichern müssen:

1. Versuchen Sie, alle Daten zu serialisieren und zu komprimieren und sie in derselben Spalte zu speichern, um mehrere Off-Pages zu vermeiden.

2. Konvertieren Sie Spalten mit einer tatsächlichen maximalen Speicherlänge von weniger als 255 in den Typ VARCHAR oder CHAR (bei Daten variabler Länge besteht zwischen beiden kein Unterschied; bei Daten fester Länge verwenden Sie den Typ CHAR).

3. Wenn es nicht möglich ist, alle Spalten in einer Spalte zu integrieren, können Sie eine zweite Option wählen und sie entsprechend der maximalen Länge jeder Spalte in mehrere Untertabellen aufteilen. Versuchen Sie, die Gesamtzeilenlänge jeder Untertabelle auf weniger als 8 KB zu reduzieren, um die Häufigkeit von Offpage-Verweisen zu verringern.

4. Die obigen Vorschläge basieren auf der Annahme, dass die Datenseite standardmäßig 16 KB groß ist. Wenn Sie sie auf 8 KB oder eine andere Größe ändern, testen Sie sie bitte selbst anhand der obigen Theorie, um den am besten geeigneten Wert zu finden.

5. Wenn die Länge einer Zeichenspalte weniger als 255 beträgt, unabhängig davon, ob CHAR oder VARCHAR zur Speicherung verwendet wird oder die Länge der VARCHAR-Spalte als 255 definiert ist, erhöht sich der tatsächliche Tabellenplatz nicht.

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM. Wenn Sie mehr darüber erfahren möchten, schauen Sie sich bitte die folgenden Links an