Antwort auf das Interview: Es ist am besten, wenn jede MySQL-Tabelle nicht mehr als 20 Millionen Daten enthält, oder?

Wie viele Daten kann jede Tabelle in MySQL speichern? In tatsächlichen Situationen ist aufgrund der unterschiedlichen Felder und des unterschiedlichen Speicherplatzes, den jede Tabelle einnimmt, auch die Datenmenge, die sie bei optimaler Leistung speichern können, unterschiedlich, was eine manuelle Berechnung erfordert.

Das Ding ist so

Das Folgende ist der Interviewbericht meines Freundes:

Interviewer: Erzählen Sie mir, was Sie während Ihres Praktikums gemacht haben.

Freund: Während meines Praktikums habe ich eine Funktion zum Speichern von Benutzerbetriebsdatensätzen erstellt. Sie ruft hauptsächlich Benutzerbetriebsinformationen ab, die vom Upstream-Dienst von MQ gesendet wurden, und speichert diese Informationen dann in MySQL zur Verwendung durch Kollegen im Data Warehouse.

Freund: Da die Datenmenge relativ groß ist, gibt es jeden Tag etwa 40 bis 50 Millionen Elemente, daher habe ich auch Untertabellenoperationen dafür durchgeführt. Jeden Tag werden regelmäßig drei Tabellen generiert. Anschließend werden die Daten in diesen drei Tabellen modelliert und gespeichert, um zu verhindern, dass übermäßige Daten in den Tabellen die Abfragegeschwindigkeit verlangsamen.

Interviewer: Warum teilen Sie ihn dann nicht in drei Tabellen auf? Würden vier Tische nicht funktionieren?

Freunde: Da jede MySQL-Tabelle 20 Millionen Daten nicht überschreiten sollte, wird sonst die Abfragegeschwindigkeit verringert und die Leistung beeinträchtigt. Unsere täglichen Daten belaufen sich auf etwa 50 Millionen Teile, daher ist es sicherer, sie in drei Tabellen aufzuteilen.

Interviewer: Noch mehr?

Freund: Nicht mehr...

Was machst du? Ups

Interviewer: Dann geh zurück und warte auf die Benachrichtigung.

Vorwort

Aber tatsächlich sind diese 20 Millionen oder 5 Millionen nur eine ungefähre Zahl und gelten nicht für alle Szenarien. Wenn Sie blind glauben, dass es kein Problem geben wird, solange die Tabellendaten 20 Millionen nicht überschreiten, ist dies der Fall Dies führt wahrscheinlich zu einem erheblichen Leistungsabfall des Systems.

Um diesen Artikel zu lesen, benötigen Sie eine gewisse MySQL-Grundlage. Es ist am besten, über ein gewisses Verständnis von InnoDB- und B+-Bäumen zu verfügen Jahr MySQL-Lernerfahrung (ungefähr ein Jahr?) Kennen Sie das theoretische Wissen „Im Allgemeinen ist es besser, die Höhe des B + -Baums in InnoDB innerhalb von drei Ebenen zu halten“.

Mindmap dieses Artikels

Kurzer Überblick über das Grundwissen

Wie wir alle wissen, ist die Speicherstruktur von InnoDB in MySQL ein B+-Baum. Jeder kennt den B+-Baum , Rechts? Die Funktionen sind ungefähr wie folgt, lassen Sie uns sie schnell gemeinsam durchgehen!

Hinweis: Der folgende Inhalt ist das Wesentliche. Schülern, die ihn nicht lesen oder verstehen können, wird empfohlen, diesen Artikel zuerst zu speichern und ihn dann erneut zu lesen, wenn sie über eine Wissensdatenbank verfügen.

Eine Datentabelle entspricht im Allgemeinen der Speicherung eines oder mehrerer Bäume. Die Anzahl der Bäume hängt von der Anzahl der Indizes ab.

• Clusterierter Index und nicht gruppierter Index:

Der Primärschlüsselindex ist ebenfalls ein Clustered-Index, und der Nicht-Primärschlüsselindex ist ein Nicht-Clustered-Index.
• Mit Ausnahme der Formatinformationen speichern die Nicht-Blattknoten beider Indizes nur Indexdaten. Wenn der Index beispielsweise eine ID ist, speichert der Nicht-Blattknoten die ID-Daten.
  Die Unterschiede zwischen Blattknoten sind wie folgt:
  • Die Blattknoten des Clustered-Index speichern im Allgemeinen alle Feldinformationen dieser Daten. Wenn wir also select * from table where id = 1, gehen wir immer zu den Blattknoten, um die Daten abzurufen.
  • Die Blattknoten des nicht gruppierten Index speichern die Primärschlüssel- und Indexspalteninformationen, die diesem Datenelement entsprechen. Wenn dieser nicht gruppierte Index beispielsweise „Benutzername“ lautet und der Primärschlüssel der Tabelle „id“ ist, speichern die Blattknoten des nicht gruppierten Index „Benutzername“ und „id“, jedoch keine anderen Felder. Dies entspricht dem Ermitteln des Werts des Primärschlüssels aus dem nicht gruppierten Index und der anschließenden Überprüfung des Dateninhalts anhand des Primärschlüsselindex. Im Allgemeinen muss er zweimal überprüft werden (es sei denn, der Index ist abgedeckt). wird auch Tabellenrückgabe genannt, was ein Bit ist. Es ähnelt dem Speichern eines Zeigers und zeigt auf die tatsächliche Adresse, an der die Daten gespeichert sind.
• B+-Baumabfragen werden Schicht für Schicht von oben nach unten abgefragt. Im Allgemeinen halten wir es für besser, die Höhe des B+-Baums innerhalb von drei Schichten zu halten, d. h. die oberen beiden Schichten sind Indizes letzte Ebene Speichern Sie Daten, sodass Sie beim Nachschlagen der Tabelle nur dreimal Festplatten-E/A durchführen müssen (eigentlich ein Mal weniger, da sich der Stammknoten im Speicher befindet), und die Datenmenge, die gespeichert werden kann, ist ebenfalls vorhanden beträchtlich.
  Wenn die Datenmenge zu groß ist und die B+-Zahl auf 4 Ebenen ansteigt, erfordert jede Abfrage 4 Festplatten-E/As, wodurch die Leistung verringert wird.

  Deshalb berechnen wir die maximale Anzahl an Daten, die der dreischichtige B+-Baum von InnoDB speichern kann.

• Die Standardgröße jedes MySQL-Knotens beträgt 16 KB, was bedeutet, dass jeder Knoten bis zu 16 KB Daten speichern kann, mit einem Maximum von 64 KB und einem Minimum von 4 KB.
  Erweiterung: Was passiert, wenn die Daten in einer bestimmten Zeile besonders groß sind und die Größe des Knotens überschreiten?
  MySQL5.7-Dokumentation erklärt:
  • Bei den Einstellungen 4 KB, 8 KB, 16 KB und 32 KB beträgt die maximale Zeilenlänge etwas weniger als die Hälfte der Datenbankseite. Beispiel: Bei der Standardseitengröße von 16 KB beträgt die maximale Zeilenlänge etwas weniger als 8 KB, und bei der Standardseitengröße von 32 KB beträgt die maximale Zeilenlänge etwas weniger als 16 KB.

  • Und für eine 64-KB-Seite beträgt die maximale Zeilenlänge etwas weniger als 16 KB.

  • Wenn eine Zeile die maximale Zeilenlänge überschreitet, wird die Spalte mit variabler Länge auf externen Seiten gespeichert, bis die Zeile die maximale Zeilenlängenbeschränkung erreicht. Das heißt, Varchar und Text mit variabler Länge werden auf externen Seiten gespeichert, um die Datenlänge dieser Zeile zu reduzieren.

MySQL :: MySQL 5.7 Referenzhandbuch :: 14.12.2 Dateispeicherverwaltung

• Die MySQL-Abfragegeschwindigkeit hängt hauptsächlich von der Lese- und Schreibgeschwindigkeit der Festplatte ab, denn wann MySQL-Abfragen Es wird jeweils nur ein Knoten in den Speicher eingelesen, der Standort des nächsten zu lesenden Knotens wird anhand der Daten dieses Knotens ermittelt und die Daten des nächsten Knotens werden erneut gelesen, bis die erforderlichen Daten abgefragt werden oder die Daten sind nicht vorhanden.
  Jemand fragt sich bestimmt: Müssen wir nicht die Daten in jedem Knoten abfragen? Warum wird die benötigte Zeit hier nicht berechnet?
  Dies liegt daran, dass die gesamten Knotendaten nach dem Lesen im Speicher gespeichert werden. In Verbindung mit der MySQL-Abfragemethode beträgt die Zeitkomplexität fast

  $(log{2}_{\mathrm{}})O(log\_2N)$, im Vergleich zu Festplatten-E/A im Allgemeinen Sprechen, es kann ignoriert werden.

Speicherinhalt des MySQL InnoDB-Knotens

Im B+-Baum von Innodb heißt der Knoten, den wir oft aufrufen, Seite (Seite), jede Seite speichert Benutzerdaten und alle Seiten werden kombiniert. Zusammen bilden sie einen B+-Baum ( Natürlich wird es in der Realität viel komplizierter sein, aber wir müssen nur berechnen, wie viele Daten gespeichert werden können, damit wir es so verstehen können?).

Seite ist die kleinste Festplatteneinheit, die von der InnoDB-Speicher-Engine zum Verwalten der Datenbank verwendet wird. Wir sagen oft, dass jeder Knoten 16 KB groß ist, was tatsächlich bedeutet, dass die Größe jeder Seite 16 KB beträgt.

In diesem 16-KB-Speicherplatz müssen Informationen zum Seitenformat und zum Zeilenformat gespeichert werden. Die Informationen zum Zeilenformat enthalten auch einige Metadaten und Benutzerdaten. Deshalb müssen wir bei der Berechnung alle diese Daten einbeziehen.

Seitenformat

Das Grundformat jeder Seite, d. usw.

Schematische Darstellung:

Ich habe lange auf der offiziellen Website gesucht und konnte sie nicht finden. . . . Ich weiß nicht, ob es daran liegt, dass ich es nicht geschrieben habe oder weil ich blind bin. Wenn jemand es gefunden hat, hoffe ich, dass er mir helfen kann, es im Kommentarbereich zu veröffentlichen.

Der Tabelleninhalt im Format der obigen Seite basiert also hauptsächlich auf Erkenntnissen und Zusammenfassungen einiger Blogs.

Außerdem versucht InnoDB, wenn ein neuer Datensatz in einen InnoDB-Clusterindex eingefügt wird, 1/16 der Seite für zukünftige Einfügungen und Aktualisierungen von Indexdatensätzen frei zu lassen. Wenn die Indexdatensätze der Reihe nach (aufsteigend oder absteigend) eingefügt werden, verfügt die resultierende Seite über etwa 15/16 des verfügbaren Platzes. Wenn Datensätze in zufälliger Reihenfolge eingefügt werden, steht etwa die Hälfte bis 15/16 des Seitenplatzes zur Verfügung. Referenzdokumentation: MySQL :: MySQL 5.7 Referenzhandbuch :: 14.6.2.2 Die physische Struktur eines InnoDB-Index

Der belegte Speicher außer User Records和Free Space ist $38 + 56 + 26 + 8 = 128$

Jede Datensatzzeile enthält Folgendes Informationen, die größtenteils in offiziellen Dokumenten zu finden sind. Was ich hier geschrieben habe, ist nicht sehr detailliert. Ich habe nur einige Kenntnisse geschrieben, die uns bei der Berechnung des Speicherplatzes helfen können. Für detailliertere Informationen können Sie online nach „MySQL-Zeilenformat“ suchen.

Schematische Darstellung:

Es gibt noch ein paar weitere Punkte zu beachten:

Speicherung von Überlaufseiten (externe Seiten)

Hinweis: Dies ist eine Funktion von DYNAMIC.

Wenn Sie DYNAMIC zum Erstellen einer Tabelle verwenden, entfernt InnoDB die Werte längerer Spalten variabler Länge (z. B. VARCHAR-, VARBINARY-, BLOB- und TEXT-Typen) und speichert sie nur auf einer Überlaufseite Spalte Reserviert einen 20-Byte-Zeiger auf die Überlaufseite.

Das COMPACT-Zeilenformat (MySQL5.6-Standardformat) speichert die ersten 768 Bytes und den 20-Byte-Zeiger im Datensatz des B+-Baumknotens, der Rest wird auf der Überlaufseite gespeichert.

Ob eine Spalte außerhalb der Seite gespeichert wird, hängt von der Seitengröße und der Gesamtgröße der Zeilen ab. Wenn eine Zeile zu lang ist, wird die längste Spalte für die Off-Page-Speicherung ausgewählt, bis der gruppierte Indexdatensatz auf die B+-Baumseite passt (das Dokument sagt nicht, wie viele?). TEXT und BLOBs, die kleiner oder gleich 40 Byte sind, werden direkt in der Zeile gespeichert und nicht ausgelagert.

Vorteile

Das dynamische Zeilenformat vermeidet das Problem, B+-Baumknoten mit großen Datenmengen zu füllen, was zu langen Spalten führt.

Die Idee des DYNAMIC-Zeilenformats besteht darin, dass es normalerweise am effizientesten ist, den gesamten Wert außerhalb der Seite zu speichern, wenn ein Teil eines langen Datenwerts außerhalb der Seite gespeichert wird.

Mit dem DYNAMIC-Format werden nach Möglichkeit kürzere Spalten in B+-Baumknoten gehalten, wodurch die Anzahl der für eine bestimmte Zeile erforderlichen Überlaufseiten minimiert wird.

Speicherung unter verschiedenen Zeichenkodierungen

char, varchar, text usw. müssen den Zeichenkodierungstyp festlegen. Bei der Berechnung des belegten Speicherplatzes muss der von verschiedenen Kodierungen belegte Platz berücksichtigt werden.

varchar, text und andere Typen verfügen über eine Längenfeldliste, um die Länge aufzuzeichnen, die sie belegen, aber char ist ein Typ mit fester Länge, daher ist die Situation etwas Besonderes. Nehmen Sie dann an, dass der Feldname char(10) ist Die folgende Situation tritt auf:

• Bei der Zeichenkodierung mit fester Länge (z. B. ASCII-Code) wird der Feldname in einem Format mit fester Länge gespeichert. Jedes Zeichen des ASCII-Codes belegt ein Byte, sodass der Name ein Byte einnimmt 10 Byte.

• Für Zeichenkodierungen mit variabler Länge (z. B. utf8mb4) werden mindestens 10 Bytes für den Namen reserviert. Wenn möglich, speichert InnoDB es auf 10 Bytes, indem es abschließende Leerzeichen kürzt.

  Wenn der Platz nach dem Zuschneiden nicht gespart werden kann, kürzen Sie die nachfolgenden Leerzeichen auf die minimale Bytelänge des Spaltenwerts (normalerweise 1 Byte). Die maximale Länge einer Spalte beträgt:

  Wort

  Das des $\text{ist das}größte\mathrm{Zeichen}$Grad ×$\mathrm{}10.$

Um ehrlich zu sein, verstehe ich das Design von char nicht ganz. Obwohl ich es schon lange gelesen habe, einschließlich offizieller Dokumente und einiger Blogs, hoffe ich, dass Studenten, die es verstehen, ihre Zweifel im Kommentarbereich klären können:

Für Zeichen mit variabler Länge Ist char in Bezug auf die Codierung nicht ein bisschen wie ein Typ mit variabler Länge? Das häufig verwendete utf8mb4 belegt 1 bis 4 Bytes, sodass der von char (10) belegte Speicherplatz 10 bis 40 Bytes beträgt. Diese Änderung ist ziemlich groß, lässt jedoch nicht genügend Platz dafür und ist auch nicht besonders Feldliste variabler Länge zum Aufzeichnen des Speicherplatzverbrauchs von Zeichenfeldern?

Berechnung starten

Okay, wir wissen bereits, was auf jeder Seite gespeichert ist, und jetzt haben wir die Möglichkeit zur Berechnung.

Da ich oben bereits den verbleibenden Platz der Seite im Seitenformat berechnet habe, stehen für jede Seite 15232 Bytes zur Verfügung. Berechnen wir direkt unten die Zeilen.

Berechnung des Nicht-Blattknotens

Einzelknotenberechnung

Die Indexseite ist der Knoten, in dem der Index gespeichert ist, dh der Nicht-Blattknoten.

Jeder Indexdatensatz enthält den Wert des aktuellen Index, eine 6-Byte-Zeigerinformation, einen 5-Byte-Zeilenkopf, der verwendet wird, um auf den Zeiger auf die nächste Ebene der Datenseite zu verweisen.

Ich habe den vom Zeiger belegten Platz im Indexdatensatz im offiziellen Dokument nicht gefunden? Ich verweise auf andere Blog-Beiträge für diese 6 Bytes. Sie sagten, dass es im Quellcode 6 Bytes sind, aber ich weiß es nicht. Ich weiß nicht, um welchen Abschnitt des Quellcodes es sich handelt.

Ich hoffe, dass Studierende, die mehr wissen, ihre Zweifel im Kommentarbereich klären können.

Angenommen, unsere Primärschlüssel-ID ist vom Typ Bigint, also 8 Bytes, dann ist der von jeder Datenzeile auf der Indexseite belegte Platz gleich $8 + 6 + 5 = 19$ Bytes. Auf jeder Seite können $15232 div 19 ca. 801$ gespeichert werden

Wenn Sie das Seitenverzeichnis einbeziehen und den Durchschnitt von 6 Daten pro Slot berechnen, sind es mindestens $801 div 6 ca. 134$

Berechnung der Nicht-Blattknoten in den ersten beiden Schichten

Im B+-Baum, wenn ein Knotenindexdatensatz vorhanden ist N $Bar,\; das\; wird\; es\; haben\; untergeordnete\; Knoten.\; Da\; die\; ersten\; beiden\; Ebenen\; unseres\; 3-Ebenen-B+-Baums\; Indexdatensätze\; sind,\; hat\; der\; Wurzelknoten\; der\; ersten\; Ebene$NN$Indizes\; Aufzeichnen,\; dann\; hat\; die\; zweite\; Ebene$NN$Knoten,\; immer\; noch\; konsistent\; mit\; dem\; Stammknoten\; Du\; Wenn\; mehr\; Datensätze\; gespeichert\; werden\; können,\; entspricht\; die\; Anzahl\; der\; Knoten\; in\; der\; dritten\; Ebene\; der\; Anzahl\; der\; Knoten$ N$N$. $$

• Tabellen mit Primärschlüssel bigint können $787 ^ 2 =. 619 speichern 3 69$
• 99$2{\mathrm{=}}^{\mathrm{986049\; speichern}}993\; ^\; 2\; =\; 986049\mathrm{}$9OK Berechnung abgeschlossen.

Mindestanzahl gespeicherter Datensätze

maximale Zeilenlänge etwas weniger als die Hälfte der Datenbankseite beträgt Weniger als die Hälfte liegt daran, dass auf jeder Seite noch etwas Platz für andere Inhalte im Seitenformat

Überlaufseite aufteilen, sodass wir dies nicht berücksichtigen.

Wenn jedes Datenelement 8 KB groß ist, kann jeder Blattknoten nur 2 Datenelemente speichern. Wenn der Primärschlüssel Bigint ist, kann eine solche Tabelle nur $2 Mal$

-- 这是一张非常普通的课程安排表，除id外，仅包含了课程id和老师id两个字段
-- 且这几个字段均为 int 型（当然实际生产中不会这么设计表，这里只是举例）。

CREATE TABLE `course_schedule` (
  `id` int NOT NULL,
  `teacher_id` int NOT NULL,
  `course_id` int NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Lassen Sie uns zunächst die Zeilendaten dieser Tabelle analysieren: Es gibt keine Nullwertliste, keine Feldliste mit variabler Länge, die Transaktions-ID und die Zeigerfelder müssen gezählt werden, und der Zeilendatensatzkopf muss gezählt werden, dann muss der belegte Platz gezählt werden für jede Datenzeile ist $4 + 4 + 4 + 6 + 7 + 5 = 30$3 $0$

算上页目录的槽位所占空间，每个叶子节点可以存放 502 条数据，那么三层B+树可以存放的最大数据量就是 $502 \times 986049 = 494,996,598$，将近5亿条数据！没想到吧??。

常规表的存放记录数

大部分情况下我们的表字段都不是上面那样的，所以我选择了一场比较常规的表来进行分析，看看能存放多少数据。表情况如下：

CREATE TABLE `blog` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '博客id',
  `author_id` bigint unsigned NOT NULL COMMENT '作者id',
  `title` varchar(50) CHARACTER SET utf8mb4 NOT NULL COMMENT '标题',
  `description` varchar(250) CHARACTER SET utf8mb4 NOT NULL COMMENT '描述',
  `school_code` bigint unsigned DEFAULT NULL COMMENT '院校代码',
  `cover_image` char(32) DEFAULT NULL COMMENT '封面图',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `release_time` datetime DEFAULT NULL COMMENT '首次发表时间',
  `modified_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `status` tinyint unsigned NOT NULL COMMENT '发表状态',
  `is_delete` tinyint unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `author_id` (`author_id`),
  KEY `school_code` (`school_code`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci ROW_FORMAT=DYNAMIC;

这是我的开源项目“校园博客”（GitHub地址：github.com/stick-i/scb…） 中的博客表，用于存放博客的基本数据。

分析一下这张表的行记录：

• 行记录头信息：肯定得有，占用5字节。

• 可变长度字段列表：表中 title占用1字节，description占用2字节，共3字节。

• null值列表：表中仅school_code、cover_image、release_time3个字段可为null，故仅占用1字节。

• 事务ID和指针字段：两个都得有，占用13字节。

• 字段内容信息：

  • id、author_id、school_code 均为bigint型，各占用8字节，共24字节。

  • create_time、release_time、modified_time 均为datetime类型，各占8字节，共24字节。

  • status、is_delete 为tinyint类型，各占用1字节，共2字节。

  • cover_image 为char(32)，字符编码为表默认值utf8，由于该字段实际存的内容仅为英文字母（存url的），结合前面讲的字符编码不同情况下的存储 ，故仅占用32字节。

  • title、description sind varchar(50) bzw. varchar(250). Diese beiden sollten keine Überlaufseiten erzeugen (nicht sicher). In der tatsächlichen Produktion werden mehr als 70 % davon auf Chinesisch gespeichert Bytes), 25 % sind Englisch (1 Byte) und 5 % sind 4-Byte-Emoticons? Wenn der Speicher voll ist, belegt er $(50+250)\times (\mathrm{0,7}\times 3+\mathrm{0,25}\times 1+\mathrm{0,05}\times 4)=765$

Die Statistiken aller oben genannten Analysen belegen insgesamt 869 Bytes, dann kann jeder Blattknoten $15232div 869 ca 17$6273

$\mathrm{}17\mathrm{}19369$