Wie geht MySQL mit großen Datentabellen um? Lösungsaustausch

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.

Szenario:

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

• Einfügung von Daten, The Die Abfragezeit ist länger
• Die Erweiterung der nachfolgenden Geschäftsanforderungen wird sich stärker auf die neuen Felder in der Tabelle auswirken
• Nicht alle Daten in der Tabelle sind gültige Daten. Es ist erforderlich, die Daten nur innerhalb des Zeitbereichs abzufragen.

Bewerten Sie das Datenvolumen der Tabelle

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:

Tabellenkapazität Hängt hauptsächlich von der Anzahl der Datensätze und der durchschnittlichen Länge der Tabelle ab. Es werden Wachstumsvolumen, Lese- und Schreibvolumen und Gesamtgröße ausgewertet. Im Allgemeinen wird für OLTP-Tabellen empfohlen, dass eine einzelne Tabelle 20 Millionen Datenzeilen nicht überschreiten sollte und die Gesamtgröße innerhalb von 15 GB liegen sollte. Besuchsvolumen: Das Lese- und Schreibvolumen einer einzelnen Tabelle liegt innerhalb von 1600/s

So fragen Sie Zeilendaten ab: Die klassischen SQL-Anweisungen, die wir im Allgemeinen verwenden, wenn wir abfragen, wie viele Daten in einer Tabelle vorhanden sind, lauten wie folgt:

select count(*) from table

select count(1) from table Wenn die Datenmenge jedoch zu groß ist, kann es bei einer solchen Abfrage zu einer Zeitüberschreitung kommen. Daher müssen wir die Abfragemethode ändern. Verwenden Sie den Bibliotheksnamen. Zeigen Sie den Tabellenstatus wie „Tabellenname“ an wie 'Tabellenname'G ;
• Die obige Methode kann nicht nur die Daten der Tabelle abfragen, sondern auch die detaillierten Informationen der Tabelle ausgeben. Fügen Sie G hinzu, um die Ausgabe zu formatieren. Einschließlich Tabellenname, Speicher-Engine-Version, Anzahl der Zeilen, Anzahl der Bytes pro Zeile usw. Sie können es selbst ausprobieren einzelne Datenbank

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 Abfrageergebnisse lauten wie folgt:
• Es wird empfohlen, dass das Datenvolumen weniger als 70 % der Festplattennutzung ausmacht. Gleichzeitig können Sie für einige Daten, die schnell wachsen, die Verwendung einer großen, langsamen Festplatte für die Datenarchivierung in Betracht ziehen (Informationen zur Archivierung finden Sie in Plan 3).
  Instanzkapazität

MySQL ist ein Thread-basiertes Dienstmodell In einigen Szenarien mit hoher Parallelität kann eine einzelne Instanz die CPU-Ressourcen des Servers nicht vollständig nutzen und der Durchsatz bleibt auf der MySQL-Ebene hängen. Sie können Ihren eigenen Instanzmodus basierend auf der Geschäftsursache in Betracht ziehen

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?

Wenn die Datenmenge in einer Tabelle Dutzende oder Hunderte Millionen erreicht, ist der Effekt des Hinzufügens von Indizes nicht so offensichtlich. Der Grund dafür, dass die Leistung schlechter wird, liegt darin, dass die Ebene der 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 .

Erinnern Sie sich noch daran, wie viele Daten ein B+-Baum speichern kann?

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

Angenommen, die Höhe des B+-Baums beträgt 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.

Wenn die Datengröße einer Datensatzzeile 1.000 beträgt, beträgt die Anzahl der Datensätze, die ein einzelner Blattknoten speichern kann = 16.000/1.000 = 16.

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 Bytes

(Der Interviewer hat Sie nach dem Typ int gefragt, ein int ist 32 Bit, 4 Bytes) und die Zeigergröße auf 6 eingestellt ist Bytes im InnoDB-Quellcode, also Das sind 8+6=14 Bytes, 16k/14B =16*1024B/14B = 1170

Daher kann ein B+-Baum mit einer Höhe von 2 1170 * 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+树的存放总记录数为=根结点指针数*单个叶子节点记录行数。

• 如果一行记录的数据大小为1k，那么单个叶子节点可以存的记录数 =16k/1k =16.
• 非叶子节点内存放多少指针呢？我们假设主键ID为bigint类型，长度为8字节(面试官问你int类型，一个int就是32位，4字节)，而指针大小在InnoDB源码中设置为6字节，所以就是8+6=14字节，16k/14B =16*1024B/14B = 1170

因此，一棵高度为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.

So lösen Sie das Problem zu vieler Daten in einer einzelnen Tabelle und langsamer Abfrage

Nachdem wir die Grundursache kennen, müssen wir überlegen, wie wir die Datenbank optimieren können, um das Problem zu lösen

这里提供了三种解决方案，包括数据表分区，分库分表，冷热数据归档 了解完这些方案之后大家可以选取适合自己业务的方案

方案一：数据表分区

为什么要分区：表分区可以在区间内查询对应的数据，降低查询范围 并且索引分区 也可以进一步提高命中率，提升查询效率

分区是指将一个表的数据按照条件分布到不同的文件上面，未分区前都是存放在一个文件上面的，但是它还是指向的同一张表，只是把数据分散到了不同文件而已。

我们首先看一下分区有什么优缺点：

表分区有什么好处？

• 与单个磁盘或文件系统分区相比，可以存储更多的数据。

• 对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。

• 一些查询可以得到极大的优化，这主要是借助于满足一个给定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之类的操作

知道了两个知识后，我们来看一下分库分表的方案

1. Modulplan:

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.

2.Bereichsbereichsschema

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?

3. Kombination aus Hash-Modul und Bereichsschema

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.

Zusammenfassung: Die Verwendung einer Kombination aus Hash-Modul und Bereichsschema kann nicht nur das Problem heißer Daten vermeiden, sondern auch die Erweiterung von Daten in der Zukunft erleichtern

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.

2. Um die Leistung zu verbessern

Der Schwerpunkt der Untertabelle liegt auf der Verbesserung der MySQL-Parallelität beim Zugriff auf Daten Zweck der Verbesserung der MySQL-Leistung.

3. In Bezug auf die Schwierigkeit der Implementierung
• 1. Es gibt viele Möglichkeiten, Tabellen zu teilen. Diese Methode ist etwa genauso schwierig wie die Root-Partitionierung und kann für den Programmcode transparent sein. Wenn Sie andere Tabellenpartitionierungsmethoden verwenden, ist dies problematischer als die Partitionierung. 2. Die Implementierung der Partitionierung ist relativ einfach. Es gibt keinen Unterschied zwischen der Erstellung einer Partitionstabelle und der Erstellung einer normalen Tabelle. Die Beziehung zwischen Partitionierung und Partitionierungstabellen kann verbessert werden Leistung von MySQL Hoch, es hat eine gute Leistung unter Bedingungen hoher Parallelität.

2. Untertabellen und Partitionen stehen nicht im Widerspruch zueinander und können bei Tabellen mit großem Zugriffsvolumen und relativ großen Tabellendaten kombiniert werden Tabellendaten sind groß. Bei Tabellen können wir sie partitionieren usw.

Probleme mit Datenbank- und Tabellen-Sharding

• 1. Transaktionsprobleme

Nach der Ausführung von Datenbank- und Tabellen-Sharding wird die Verwaltung von Datenbanktransaktionen schwierig, da die Daten in verschiedenen Datenbanken gespeichert sind. Wenn Sie sich bei der Ausführung von Transaktionen auf die verteilte Transaktionsverwaltungsfunktion der Datenbank selbst verlassen, zahlen Sie einen hohen Leistungspreis. Wenn die Anwendung bei der Steuerung hilft und eine Programmlogiktransaktion bildet, verursacht dies auch einen Programmieraufwand.

2. Probleme mit datenbankübergreifenden und tabellenübergreifenden Verknüpfungen

Nach der Durchführung des Datenbank- und Tabellen-Shardings ist es unvermeidlich, dass die ursprünglich stark logisch verknüpften Daten in verschiedene Tabellen und verschiedene Bibliotheken aufgeteilt werden Der Betrieb von Tabellen ist nicht möglich, und wir können auch keine Tabellen mit unterschiedlichen Untertabellen-Granularitäten verknüpfen. Daher kann es sein, dass für den Abschluss mehrerer Abfragen mehrere Abfragen erforderlich sind.

3. Zusätzlicher Datenverwaltungsaufwand und Datenberechnungsdruck

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.

Option drei: Hot- und Cold-Archivierung

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.

Lassen Sie uns über den Archivierungsprozess sprechen

• Erstellen einer Archivtabelle Grundsätzlich sollte die erstellte Archivtabelle mit der ursprünglichen Tabelle übereinstimmen.

Wie wählen wir die oben genannten drei Optionen aus? 2 Nur in einem bestimmten Bereich 3. Es gibt kein gemeinsames Abfrageszenario

Bei der Partitionierung und Tabellenpartitionierung werden die der Datentabelle entsprechenden Dateien physisch aufgeteilt. Der entsprechende Tabellenname bleibt unverändert, sodass die vorherige Geschäftslogik SQL nicht beeinträchtigt wird Abfragen nach der Tabellenpartitionierung erzeugen entsprechende Objekte, was ebenfalls einen gewissen Overhead verursacht. Auch die Aggregation partitionierter Daten wird lange dauern; der Anwendungsbereich ist nicht für Datenmengen von mehr als mehreren zehn Millionen geeignet Untertabelle

Die Datenmenge ist groß und es ist unmöglich, offensichtliche heiße und kalte Bereiche zu unterscheiden. Und die Daten können vollständig nach Intervallen unterteilt werden.

• 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.

Der Prozess der Datenmigration hat weniger Auswirkungen auf das Geschäft, weniger Entwicklungsvolumen und weniger Kosten.

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.

【Verwandte Empfehlung:

MySQL-Video-Tutorial

】

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!