Wie geht MySQL mit großen Datentabellen um? Der folgende Artikel stellt Ihnen die MySQL-Lösung zur Verarbeitung von Big-Data-Tabellen vor und hoffe, dass er Ihnen hilfreich sein wird.
Wenn unsere Geschäftsdatenbanktabelle immer mehr Daten enthält und Sie und ich auf die folgenden ähnlichen Szenarien gestoßen sind, lassen Sie uns dieses Problem gemeinsam lösen
Wir können das Datenvolumen anhand von drei Aspekten bewerten: Tabellenkapazität/Speicherplatz/Instanzkapazität. Schauen wir uns als nächstes jeden einzelnen Aspekt an:
select count(*) from table
select table_schema as '数据库', table_name as '表名', table_rows as '记录数', truncate(data_length/1024/1024, 2) as '数据容量(MB)', truncate(index_length/1024/1024, 2) as '索引容量(MB)' from information_schema.tables order by data_length desc, index_length desc;
Die Größe unserer Datentabelle haben wir oben bereits ermittelt. Was ist also der grundlegende Grund, warum die Ausführungseffizienz des Unternehmens umso langsamer ist, je größer die Datenmenge in einer einzelnen Tabelle ist?
B+-Baumstruktur, die den Index verwaltet, höher wird. Beim Abfragen eines Datenelements müssen mehr Datenträger-E/A durchgeführt werden, sodass die Abfrageleistung langsamer wird .
Die kleinste Speichereinheit der InnoDB-Speicher-Engine ist eine Seite, und die Größe einer Seite beträgt 16k.
B+-Baumblätter speichern Daten und interne Knoten speichern Schlüsselwerte + Zeiger. Die indizierte Tabelle bestimmt mithilfe der binären Suchmethode von Nicht-Blattknoten und Zeigern, auf welcher Seite sich die Daten befinden, und geht dann zur Datenseite, um die erforderlichen Daten zu finden
2 , das heißt, es gibt einen Wurzelknoten und mehrere Blattknoten. Die Gesamtzahl der in diesem B+-Baum gespeicherten Datensätze beträgt = die Anzahl der Wurzelknotenzeiger * die Anzahl der in einem einzelnen Blattknoten aufgezeichneten Zeilen.
Wie viele Zeiger werden in Nicht-Blattknoten gespeichert? Wir gehen davon aus, dass die Primärschlüssel-ID vom Typ
bigint ist, mit einer Länge von 8 Bytes1170 * 16= speichern 18720 Artikel wie dieser Datensatz. Ebenso kann ein B+-Baum mit einer Höhe von 3 1170 *1170 *16 =21902400 speichern, was bedeutet, dass er etwa 20 Millionen Datensätze speichern kann. Die Höhe des B + -Baums beträgt im Allgemeinen 1 bis 3 Schichten, wodurch die Speicheranforderungen von mehreren zehn Millionen Datenebenen erfüllt werden können. B+树结构层级变得更高了，查询一条数据时，需要经历的磁盘IO变多，因此查询性能变慢。
大家是否还记得，一个B+树大概可以存放多少数据量呢？
InnoDB存储引擎最小储存单元是页，一页大小就是16k。
B+树叶子存的是数据，内部节点存的是键值+指针。索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而再去数据页中找到需要的数据；
假设B+树的高度为2的话，即有一个根结点和若干个叶子结点。这棵B+树的存放总记录数为=根结点指针数*单个叶子节点记录行数。
因此，一棵高度为2的B+树，能存放1170 * 16=18720条这样的数据记录。同理一棵高度为3的B+树，能存放1170 *1170 *16 =21902400Wenn der B+-Baum mehr Daten speichern möchte, ist die Baumstrukturebene höher. Beim Abfragen eines Datenelements muss mehr Festplatten-E/A erlebt werden, sodass die Abfrageleistung langsamer wird.
这里提供了三种解决方案，包括数据表分区，分库分表，冷热数据归档 了解完这些方案之后大家可以选取适合自己业务的方案
为什么要分区：表分区可以在区间内查询对应的数据，降低查询范围 并且索引分区 也可以进一步提高命中率，提升查询效率
分区是指将一个表的数据按照条件分布到不同的文件上面，未分区前都是存放在一个文件上面的，但是它还是指向的同一张表，只是把数据分散到了不同文件而已。
我们首先看一下分区有什么优缺点：
表分区有什么好处？
与单个磁盘或文件系统分区相比，可以存储更多的数据。
对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。
一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。
涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。
通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。
表分区的限制因素
一个表最多只能有1024个分区。
MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。
如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。
分区表中无法使用外键约束。
MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。
在进行分区之前可以用如下方法 看下数据库表是否支持分区哈
mysql> show variables like '%partition%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | have_partitioning | YES | +-------------------+-------+ 1 row in set (0.00 sec)
为什么要分表：分表后，显而易见，单表数据量降低，树的高度变低，查询经历的磁盘io变少，则可以提高效率
mysql 分表分为两种 水平分表和垂直分表
分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库组成 ，将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。
定义：数据表行的拆分，通俗点就是把数据按照某些规则拆分成多张表或者多个库来存放。分为库内分表和分库。 比如一个表有4000万数据，查询很慢，可以分到四个表，每个表有1000万数据
定义：列的拆分，根据表之间的相关性进行拆分。常见的就是一个表把不常用的字段和常用的字段就行拆分，然后利用主键关联。或者一个数据库里面有订单表和用户表，数据量都很大，进行垂直拆分，用户库存用户表的数据，订单库存订单表的数据
缺点：垂直分隔的缺点比较明显，数据不在一张表中，会增加join 或 union之类的操作
知道了两个知识后，我们来看一下分库分表的方案
Schätzen Sie vor der Aufteilung die Datenmenge. Beispielsweise enthält die Benutzertabelle 40 Millionen Daten, und jetzt müssen die Daten in 4 Tabellen unterteilt werden: Benutzer1, Benutzer2, UESR3, Benutzer4. Beispielsweise ist id = 17, 17 modulo 4 gleich 1 plus, daher werden diese Daten in der Tabelle user2 gespeichert.
Hinweis: Auto_increment sollte nach der horizontalen Aufteilung aus der Tabelle entfernt werden. Die ID kann zu diesem Zeitpunkt mithilfe einer sich selbst erhöhenden temporären ID-Tabelle oder mithilfe der Redis-Incr-Methode ermittelt werden.
Vorteile: Die Daten sind gleichmäßig auf verschiedene Tabellen verteilt und die Wahrscheinlichkeit von Hot Issues ist sehr gering.
Nachteile: Es wird in Zukunft schwierig sein, Daten zu erweitern und zu migrieren. Was zuvor in 4 Tabellen unterteilt war, wird nun in 8 Tabellen aufgeteilt. Die Modulo-Wertänderungen und die Datenmigration müssen durchgeführt werden wieder raus.
Daten nach Bereich aufteilen, dh Bestellungen innerhalb eines bestimmten Bereichs werden in einer bestimmten Tabelle gespeichert. Beispielsweise wird ID = 12 in der Tabelle Benutzer1 und ID = 13 Millionen in der Tabelle Benutzer2 gespeichert.
Vorteile: Förderlich für zukünftige Datenerweiterungen
Nachteile: Wenn die heißen Daten in einer Tabelle vorhanden sind, liegt der Druck in einer Tabelle und es gibt keinen Druck auf andere Tabellen.
Wir sehen, dass die beiden oben genannten Lösungen Mängel aufweisen, sich aber ergänzen. Was passiert also, wenn wir diese beiden Lösungen kombinieren?
Wie in der folgenden Abbildung gezeigt, können wir sehen, dass die Gruppengruppe Daten mit IDs von 0 bis 40 Millionen speichert und es dann drei Datenbanken gibt: DB0, DB1, DB2 In DB0 gibt es vier Datenbanken, DB1 und DB2 haben drei Datenbanken in Tabelle_0.
Wir haben bereits etwas über MySQL-Partitionen und Untertabellen gelernt. Werfen wir also einen Blick auf diese beiden. Was sind die Unterschiede zwischen diesen Technologien und den anwendbaren Szenarien? Nach dem Sharding sind alle kleinen Tabellen vollständige Tabellen, die einer .MYD-Datendatei, einer .MYI-Indexdatei und einer .frm-Tabellenstruktur entsprechen Wenn eine große Tabelle partitioniert ist, handelt es sich immer noch um eine Tabelle. Sie wird nicht zu zwei Tabellen, sondern verfügt über mehr Blöcke zum Speichern von Daten.
Zusätzlicher Datenverwaltungsaufwand, das offensichtlichste ist das Problem der Datenpositionierung und der wiederholten Ausführung von Datenhinzufügungen, -löschungen, -änderungen und -abfragen. Diese können durch Anwendungen gelöst werden. Dies führt jedoch zwangsläufig zu zusätzlichen logischen Operationen. Beispielsweise muss das Unternehmen für eine Benutzerdatentabelle, die Benutzerbewertungen aufzeichnet, die 100 besten Bewertungen finden, bevor die Tabelle unterteilt werden kann, jedoch nach der Ausführung Bei der Aufteilung der Tabellen sind n „Order by“-Anweisungen erforderlich, um die Top-100-Benutzerdaten für jede geteilte Tabelle herauszufinden und dann die Daten zu kombinieren, um das Ergebnis zu erhalten.
Warum Hot- und Cold-Archivierung: Tatsächlich ist der Grund ähnlich wie bei der zweiten Option, die darin besteht, die Datenmenge in einer einzelnen Tabelle zu reduzieren und die Höhe des Baums zu verringern niedriger und die bei der Abfrage auftretende Festplatten-E/A wird geringer, was zu einer verbesserten Effizienz führen kann Wenn Ihre Geschäftsdaten beispielsweise eine klare Unterscheidung zwischen „heiß“ und „kalt“ aufweisen, müssen Sie nur Daten der letzten Woche oder des letzten Monats anzeigen. In diesem Fall werden die Daten für diese Woche und einen Monat als heiße Daten bezeichnet, und die restlichen Daten sind kalte Daten. Anschließend können wir die kalten Daten in anderen Datenbanktabellen archivieren, um die Betriebseffizienz unserer heißen Daten zu verbessern.
Erstellen einer Archivtabelle Grundsätzlich sollte die erstellte Archivtabelle mit der ursprünglichen Tabelle übereinstimmen.
Es eignet sich für Daten, bei denen die Grenzen zwischen heißen und kalten Partitionen liegen Nicht sehr offensichtlich. Diese Methode kann für nachfolgende ähnliche Daten verwendet werden. Teilen Sie große Tabellen in kleine Tabellen auf, um die Effizienz von Abfragen, Einfügungen usw. zu verbessern Datenbanktabellen müssen in Tabellen unterteilt werden. Wenn die nachfolgende einzelne Tabelle auch eine größere Komplexität aufweist, ist die Implementierung komplizierter um die Auswirkungen des gesamten Implementierungsprozesses auf die Verarbeitung der Codierungsschicht auf das ursprüngliche Geschäft zu testen; Die Nutzung kalter Daten ist extrem gering.
Die Tabellenaufteilungsregeln müssen bestätigt werden.
Sie können entsprechend Ihren eigenen Geschäftsszenarien Um eine Lösung auszuwählen, die zu Ihrem Unternehmen passt, werde ich Ihnen einige Ideen geben. Jetzt ist das, worüber ich sprechen möchte, fast vorbei. Wenn etwas nicht stimmt oder Sie Zweifel haben, sind Sie herzlich willkommen.
】
Das obige ist der detaillierte Inhalt vonWie geht MySQL mit großen Datentabellen um? Lösungsaustausch. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!
MySQL ändert den Namen der Datentabelle
MySQL erstellt eine gespeicherte Prozedur
Der Unterschied zwischen Mongodb und MySQL
So überprüfen Sie, ob das MySQL-Passwort vergessen wurde
MySQL-Datenbank erstellen
MySQL-Standard-Transaktionsisolationsstufe
Der Unterschied zwischen SQL Server und MySQL
mysqlPasswort vergessen
![[Web-Frontend] Node.js-Schnellstart](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
