Vertiefendes Verständnis des MySQL-Datenzeilenüberlaufs

Dieser Artikel vermittelt Ihnen ein tiefgreifendes Verständnis des MySQL-Datenzeilenüberlaufs. Ich hoffe, er wird Ihnen als Referenz dienen.

1. Beginnen wir mit häufigen Fehlern

Zu Beginn der Geschichte schauen wir uns eine häufige SQL-Fehlermeldung an:

Ich glaube, Sie müssen auf diese Art von Fehler schon oft gestoßen sein, insbesondere bei BGs wie OMG, deren Hauptaufgabe darin besteht, Inhaltszeilen, die Daten, zu erstellen muss groß sein. Es ist ein unvermeidbares Thema. Die „Größe“ der Daten geht hier weit über den großen belegten Speicherplatz hinaus. Dazu gehören auch der große Speicherplatz eines einzelnen (Tabellen-)Felds, die lange Datenaufbewahrungszeit, die große Datenredundanz und das durch die Inkonsistenz verursachte große Volumen von heißen und kalten Daten und die Zugriffsprobleme ändern sich erheblich mit Hotspots, und komplexe logische Verarbeitung führt zu erhöhtem Datenspeicherdruck usw. Zurück zum Problem der Fehlerberichterstattung. Werfen wir zunächst einen Blick auf die Struktur dieser Tabelle:

Wenn ich das sehe, glaube ich, dass es bei jedem anders sein wird Meinungen Wir werden hier nicht die Vor- und Nachteile verschiedener Verarbeitungsmethoden vergleichen, sondern nur die beiden häufiger verwendeten Verarbeitungsmethoden beschreiben.

• Ändern Sie gemäß der Fehleranleitung die beiden großen Varchar (22288) in Text und Blob

• Reduzieren Sie entsprechend den Geschäftsmerkmalen die Größe von Varchar Speicherlänge oder Aufteilung in mehrere kleine Vachar und Char

Diese beiden Verarbeitungsmethoden haben auch ihre eigenen Vor- und Nachteile Nicht nur erhöht Mit der erhöhten Datenspeicherkapazität kann für die Indexseite dieses Feldes nur die Präfix- oder Volltextindizierung verwendet werden. Wenn die Geschäftsseite Daten im JSON-Format speichert, ist es eine gute Wahl für 5.7, die JSON-Daten zu unterstützen Typ, der für eine einzelne Unterkategorie abgefragt und ausgegeben werden kann. Wenn es reduziert und aufgeteilt wird, hängt es ebenfalls stärker von den Geschäftsszenarien und logischen Anforderungen ab. Die vom Unternehmen verwendete Logik muss geändert werden, und auch das Engineering-Volumen muss bewertet werden.

2. Eingehende Erkundung

Dann werfen wir eine detaillierte Analyse einiger verwirrender Konzepte zur Grenzgröße „65535“.

1. „65535“ ist nicht die maximale Grenze von N in einem einzelnen Varchar(N), sondern die Gesamtbytes der nicht großen Feldtypen in der gesamten Tabelle.

------------------------------------------------------- -------------------------------------------------- --

Jede Tabelle (unabhängig von der Speicher-Engine) hat eine maximale Zeilengröße von 65.535 Byte. Speicher-Engines können dieser Grenze zusätzliche Einschränkungen auferlegen, wodurch die effektive maximale Zeilengröße verringert wird.

-- -------------------------------------------------- ---------------------------------------

2. Anders Der Zeichensatz hat Auswirkungen auf die maximale Speicherkapazität des Feldes. Beispielsweise benötigen UTF8-Zeichen 3 Bytes zum Speichern. Für VARCHAR (255) CHARACTER SET UTF8-Spalten werden 255×3 =765 Bytes belegt. Daher darf die Tabelle nicht mehr als 65.535/765=85 Spalten enthalten. GBK ist Doppelbyte und so weiter.

3. Spalten mit variabler Länge sollten bei der Auswertung der Feldgröße auch die Anzahl der Bytes berücksichtigen, die die tatsächliche Länge der Spalte speichern. Beispielsweise benötigt die Spalte VARCHAR (255) CHARACTER SET UTF8 zusätzliche zwei Bytes zum Speichern der Wertlängeninformationen, sodass die Spalte bis zu 767 Bytes zum Speichern benötigt. Tatsächlich kann sie bis zu 65533 Bytes speichern, und die restlichen zwei Bytes speichern die Längeninformationen.

4. BLOB-, TEXT- und JSON-Spalten unterscheiden sich von Feldern wie varchar und char. Die Spaltenlängeninformationen werden unabhängig von der Zeilenlänge gespeichert und können 65535 Bytes realen Speichers erreichen >5. Durch die Definition von NULL-Spalten wird die maximal zulässige Anzahl von Spalten verringert.

InnoDB-Tabelle, NULL- und NOT NULL-Spaltenspeichergrößen sind gleich
MyISAM-Tabelle, NULL-Spalte benötigt zusätzlichen Speicherplatz, um aufzuzeichnen, ob ihr Wert vorliegt ist NULL. Jeder NULL-Wert erfordert ein zusätzliches Bit (auf das nächste Byte gerundet). Die maximale Zeilenlänge wird wie folgt berechnet:
Zeilenlänge = 1 + (Summe der Spaltenlängen) + (Anzahl der NULL-Spalten + delete_flag + 7)/8 + (Anzahl der Variablen -length columns )

Statische Tabelle, delete_flag = 1, die statische Tabelle identifiziert, ob die Zeile gelöscht wurde, indem sie ein Bit in der Zeile aufzeichnet.
Dynamische Tabelle, delete_flag = 0, dieses Flag wird am Anfang der dynamischen Zeile gespeichert, die dynamische Tabelle kann gemäß

• 6. Für InnoDB-Tabellen sind die Speichergrößen NULL und NOT NULL gleich

7 InnoDB ermöglicht es, bis zu 1.000 Spalten zu haben

8 mehr als 767 Bytes oder 768/2=384 Doppelbyte- oder 767/3=255 Dreibyte-Felder, während GBK Doppelbytes und UTF8 Dreibytes sind.

9. Verschiedene Engines haben unterschiedliche Indizes Einschränkungen

Die Länge jeder Spalte von innodb darf nicht größer als 767 Byte sein; die Summe der Längen aller Indexspalten darf nicht größer als 3072 Byte sein

• Die Länge jeder Spalte von Myisam darf nicht größer als 1000 Byte sein, die Summe der Längen aller Indexspalten darf nicht größer als 1000 Byte sein

3. Der eigentliche Fehler

Lassen Sie uns über den heute aufgetretenen Geschäftsfehler sprechen. In der Online-Branche ist eine große Anzahl der folgenden Fehler aufgetreten, die dazu geführt haben, dass das Programm nicht schreiben konnte Daten:

Nach den Anweisungen und normalem Denken ist unsere erste Reaktion, dass das Unternehmen die folgenden Probleme hat:

• Die Felder in der festgelegten Tabellenstruktur überschreiten den Grenzwert

• Die in ein bestimmtes Feld eingefügte Datenlänge überschreitet den für das geänderte Feld festgelegten Maximalwert

Weiter prüfen Die Datenbanktabellenstruktur des Unternehmens ist wie folgt:

Der erste Grund wurde schnell beseitigt, da der Geschäftsfehler vorliegt Wenn es sich um die Summe der nicht großen Felder in der Tabelle handelt, 65535, tritt beim Erstellen der Tabelle ein Fehler auf, und das Unternehmen meldet den Fehler nur beim Schreiben und über die Bibliothekstabelle In der Struktur können wir auch feststellen, dass es eine große Anzahl von Feldern vom Typ „Mediumblob“ gibt. Die Summe der nicht großen Felder beträgt viel weniger als 65535.

Gemäß dem vom Unternehmen bereitgestellten spezifischen SQL überschreiten die nicht großen Felder wie appversion, datadata, elt_stamp und id nicht das Limit. Das Feld vom Typ „Mediumblob“ kann bis zu 16 MB speichern ist weit davon entfernt, diese Größenordnung zu erreichen. Laut Fehlermeldung habe ich die nicht großen Felder wie appversion, datadata, elt_stamp und id in den Blob-Typ geändert, aber das Problem kann immer noch nicht gelöst werden. (Laut der vorherigen Analyse darf es nicht die Ursache des Problems sein).

Nachdem ich mich beruhigt habe, stellte ich fest, dass es tatsächlich ein weiteres Detail gibt, das ignoriert wurde. Die Fehlerquote des Unternehmens liegt nicht bei 100 %, was bedeutet, dass es immer noch erfolgreiche und fehlgeschlagene SQL-Anfragen gibt. Ich habe festgestellt, dass es tatsächlich einen Unterschied in der Datenmenge oder einem Mediumblob-Feld gibt. Das erste, was mir jetzt in den Sinn kommt, ist, ob der Parameter max_allowed_packet nach unten angepasst wurde. Ja, eine einzelne Anfrage überschreitet die Größe und wird abgelehnt. Ich habe den konfigurierten Wert überprüft (wie unten gezeigt). Die Datenlänge von SQL ist nicht so groß, daher ist dieser Grund ausgeschlossen.

Nachdem wir hier überprüft haben, schließen wir grundsätzlich mehrere häufige Probleme aus und schauen uns dann die Einschränkungen eines anderen Parameters an: innodb_page_size, dieses hier Der Standardwert ist 16 KB, mit zwei Datenzeilen pro Seite, sodass jede Zeile maximal 8 KB Daten enthält.

Nachdem wir die Datentabelle überprüft haben, ist Row_format Compact, dann können wir daraus schließen, dass die Ursache des Problems sein sollte. Das Standardspeicherformat von innodb speichert die ersten 864 Bytes jedes Blobs Feld auf der Seite. Wenn also der Blob eine bestimmte Anzahl überschreitet, überschreitet die Größe einer einzelnen Zeile 8 KB, sodass ein Fehler gemeldet wird. Durch den Vergleich der erfolgreichen und fehlgeschlagenen SQL des Geschäftsschreibens wurde diese Schlussfolgerung auch angewendet. Wie kann dieses Problem nun gelöst werden?

• Business-Split-Tabelle, große Felder werden in separaten Tabellen gespeichert

• Lösen Sie das Problem, indem Sie die Speichermethode von Row_format lösen

  Da die Anzahl der in einer einzelnen Geschäftstabelle gespeicherten Elemente nicht groß ist und die Geschäftslogik nicht für die Aufteilung geeignet ist, müssen wir dieses Problem im Zeilenformat lösen.

Das Barracuda-Dateiformat verfügt über zwei neue Zeilenaufzeichnungsformate: Komprimiert und Dynamisch. Die beiden neuen Formate verwenden eine vollständige Zeilenüberlaufmethode zum Speichern von BLOB-Daten Die Datenseite wird angezeigt und die eigentlichen Daten werden auf der BLOB-Seite gespeichert. Ein weiteres Merkmal des komprimierten Zeilendatensatzformats besteht darin, dass die darin gespeicherten Daten mithilfe des zlib-Algorithmus komprimiert werden.

Zugehörige Änderungsvorgänge sind relativ einfach:

1. Ändern Sie die globalen MySQL-Variablen:

SET GLOBAL innodb_file_format ='Barracuda';

2. Ändern Sie die Attribute der Originaltabelle reibungslos:

ROW_FORMAT=COMPRESSED

4. Weiter lernen

Anhand dieses Falles können wir zwei Punkte extrahieren, die es wert sind, eingehend untersucht zu werden:

Über innodb_page_size

Beginnend mit MySQL 5.6, innodb_page_size kann die Innodb-Datenseite auf 8 KB, 4 KB und den Standardwert auf 16 KB einstellen. Dieser Parameter muss bei der Erstinitialisierung zu my.cnf hinzugefügt werden. Wenn die Tabelle erstellt und dann geändert wurde, wird beim Starten von MySQL ein Fehler gemeldet.

Was soll ich also tun, wenn ich diesen Wert vor Version 5.6 ändern muss? Die einzige Möglichkeit besteht darin, etwas am Quellcode zu arbeiten und dann MySQL neu zu erstellen.

UNIV_PAGE_SIZE ist die Datenseitengröße. Der Standardwert ist 16 KB. Dieser Wert kann auf 4k, 8k, 16k, 32k, 64k eingestellt werden. Gleichzeitig müssen Sie nach dem Ändern von UNIV_PAGE_SIZE den Wert UNIV_PAGE_SIZE hoch 2 ändern, sodass die Einstellungen der Datenseiten wie folgt lauten:

Lassen Sie uns über die Auswirkungen der Einstellung von innodb_page_size auf unterschiedliche Werte auf die MySQL-Leistung sprechen. Die getestete Tabelle enthält 100 Millionen Datensätze und die Dateigröße beträgt 30 GB.

①Lese- und Schreibszenario (50 % Lesen, 50 % Schreiben)

16 KB, weniger Druck auf die CPU, durchschnittlich 20 %

8 KB, der CPU-Druck beträgt 30 % ~ 40 %, aber der ausgewählte Durchsatz ist höher als 16 KB

②Lesenszenario (100 % Lesen)

Der Unterschied zwischen 16 KB und 8 KB ist nicht offensichtlich

InnoDB-Puffer Auch die Pool-Verwaltungsseite selbst ist mit Kosten verbunden. Je mehr Seiten vorhanden sind, desto länger ist die Verwaltungsliste. Wenn unsere Datenzeile selbst relativ lang ist (Einfügung großer Blöcke), sind größere Seiten daher förderlicher für die Verbesserung der Geschwindigkeit, da mehr Zeilen auf einer Seite platziert werden können und die Größe jedes E / A-Schreibvorgangs größer ist und weniger IOPS mehr Daten schreibt . Wenn die Zeilenlänge 8 KB überschreitet und es sich um eine 16 KB große Seite handelt, müssen einige Zeichenfolgentypen zwangsweise in TEXT konvertiert werden, und der Hauptteil der Zeichenfolge wird auf die Erweiterungsseite übertragen, was eine weitere E/A zum Lesen erfordert Spalte, und eine größere Seite wird verwendet. Größere Zeilenlängen werden unterstützt, und 64-KByte-Seiten können Zeilenlängen von etwa 32 KB unterstützen, ohne Erweiterungsseiten zu verwenden. Wenn es sich jedoch um zufälliges Lesen und Schreiben kurzer Zeilenlänge handelt, ist die Verwendung einer so großen Seite nicht geeignet, was zu einer Verringerung der E/A-Effizienz führt und große E/A nur einen kleinen Teil lesen kann.

2. Über Row_format

Innodb-Speicher-Engine speichert Datensätze in Form von Zeilen. Vor der InnoDB-Version 1.0.x stellte die InnoDB-Speicher-Engine zwei Formate bereit: Kompakt und Redundant zum Speichern von Zeilendatensatzdaten. Das innodb_plugin in MySQL 5.1 führt ein neues Dateiformat ein: Barracuda, das über zwei neue Zeilenformate verfügt: komprimiert und dynamisch. Und kompakt und überflüssig werden zusammenfassend Antilope genannt. Sie können den Befehl SHOW TABLE STATUS LIKE 'table_name' verwenden, um das von der aktuellen Tabelle verwendete Zeilenformat anzuzeigen, wobei die Spalte row_format den Typ der aktuell verwendeten Zeilendatensatzstruktur angibt.

In der MySQL 5.6-Version, dem Standard-Compact, in msyql 5.7.9 und späteren Versionen wird das Standardzeilenformat durch die Variable innodb_default_row_format bestimmt. Der Standardwert ist DYNAMIC. Sie können beim Erstellen einer Tabelle auch ROW_FORMAT=DYNAMIC angeben (Dies kann verwendet werden, um das Speicherformat der Tabelle dynamisch anzupassen). Wenn Sie den Zeilenmodus einer vorhandenen Tabelle auf komprimiert oder dynamisch ändern möchten, müssen Sie zunächst das Dateiformat auf Barracuda einstellen (set global innodb_file_format=Barracuda;). Verwenden Sie dann ALTER TABLE tablename ROW_FORMAT=COMPRESSED;, um es zu ändern, damit es wirksam wird. Andernfalls ist die Änderung ungültig und es erfolgt keine Eingabeaufforderung.

①compact

Wenn die Länge des Blob-Spaltenwerts > beträgt, dann befinden sich die ersten 768 Bytes noch auf der Datenseite und die restlichen werden auf der Überlaufseite platziert (Off-Page), wie unten gezeigt:

Die oben genannten Blob- oder Feldtypen mit variabler Länge umfassen Blob, Text und varchar, wobei die Länge des Varchar-Spaltenwerts größer als eine bestimmte Zahl N ist. Unter dem Zeichensatz latin1 kann der N-Wert wie folgt berechnet werden: Die Standardblockgröße von innodb beträgt 16 KB Die Engine-Tabelle ist eine indexorganisierte Tabelle. Die Blattknoten am unteren Rand des Baums sind zweifach verknüpfte Listen, sodass jede Seite mindestens zwei Zeilen mit Datensätzen haben sollte. Dies bestimmt, dass Innodb eine Datenzeile speichert. Sie darf 8 KB nicht überschreiten, abzüglich der Anzahl der von anderen Spaltenwerten belegten Bytes, was ungefähr N entspricht.

②komprimiert oder dynamisch

Verwenden Sie den vollständigen Zeilenüberlauf für Blobs, dh der Clustered-Index-Datensatz (Datenseite) behält nur einen 20-Byte-Zeiger, der auf die Überlaufsegmentadresse zeigt wo es tatsächlich gespeichert wird:

Dynamisches Zeilenformat. Ob die Spaltenspeicherung auf der Off-Page-Seite platziert wird, hängt hauptsächlich davon ab Bei der Zeilengröße wird die längere Spalte außerhalb der Seite platziert, bis die Datenseite die nächsten beiden Zeilen speichern kann. TEXT/BLOB-Spalten werden immer auf der Datenseite gespeichert, wenn

Komprimiert ähnelt dynamisch in der physischen Struktur, die Datenzeilen der Tabelle werden jedoch komprimiert und mithilfe des zlib-Algorithmus gespeichert. Wenn es viele lange Blob-Spaltentypen gibt, kann es die Off-Page-Nutzung und den Speicherplatz reduzieren (ca. 50 %). Weitere Informationen finden Sie im vorherigen Bericht „[Datenbankbewertungsbericht] Ausgabe 3: InnoDB, TokuDB-Komprimierungsleistung“ Test Ergebnisse), erfordert jedoch eine höhere CPU. Der Pufferpool kann sowohl komprimierte als auch unkomprimierte Versionen der Daten speichern, sodass er auch mehr Speicher beansprucht.

Abschließend verwies ich auf „High Performance MySQL“ und gab einige Vorschläge zur Verwendung variabler langer Feldtypen wie BLOB:

① Große Felder können in InnoDB viel Platz verschwenden. Wenn der gespeicherte Feldwert beispielsweise nur ein Byte mehr ist, als die Zeile erfordert, wird die gesamte Seite zum Speichern der verbleibenden Bytes verwendet, wodurch der größte Teil des Seitenplatzes verschwendet wird. Wenn Sie ebenfalls einen Wert haben, der die 32-Seiten-Größe nur geringfügig überschreitet, werden tatsächlich 96 Seiten verwendet.

②Ein zu langer Wert kann dazu führen, dass der Index nicht als WHERE-Bedingung in der Abfrage verwendet wird, was zu einer langsamen Ausführung führt. MySQL muss alle Spalten lesen, bevor die WHERE-Bedingung angewendet wird. Dies kann dazu führen, dass MySQL InnoDB auffordert, viel erweiterten Speicher zu lesen, dann die WHERE-Bedingung zu überprüfen und alle unnötigen Daten zu verwerfen.

③ Es gibt viele große Felder in einer Tabelle. Am besten fasst man sie zusammen und speichert sie in einer separaten Spalte. Es ist besser, wenn sich alle großen Felder einen erweiterten Speicherplatz teilen, als dass jedes Feld eine eigene Seite hat.

④ Verwenden Sie COMPRESS(), um große Felder zu komprimieren und sie dann als BLOBs zu speichern, oder komprimieren Sie sie in der Anwendung, bevor Sie sie an MySQL senden. Sie können erhebliche Platzvorteile und Leistungssteigerungen erzielen.

⑤ Der erweiterte Speicher deaktiviert adaptives Hashing, da die gesamte Länge der Spalte vollständig verglichen werden muss, um herauszufinden, ob die Daten korrekt sind.

Das obige ist der detaillierte Inhalt vonVertiefendes Verständnis des MySQL-Datenzeilenüberlaufs. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!