Comment Mysql gère-t-il les grandes tables de données ? Partage de solutions

Comment Mysql gère-t-il les grandes tables de données ? L'article suivant vous présentera la solution de traitement de tables Big Data Mysql, j'espère qu'il vous sera utile.

Scénario :

Quand il y a de plus en plus de données dans notre table de base de données d'entreprise, si vous et moi avons rencontré les scénarios similaires suivants, alors résolvons ce problème ensemble

• Insertion de données, Le le temps de requête est plus long
• L'expansion des exigences commerciales ultérieures aura un impact plus important sur les nouveaux champs de la table
• Toutes les données de la table ne sont pas des données valides. Il est nécessaire d'interroger uniquement les données dans la plage de temps
.

Évaluer le volume de données de la table

Nous pouvons évaluer le volume de données sous trois aspects : capacité de la table/espace disque/capacité de l'instance. Examinons ensuite chacun séparément

Capacité de la table :

Capacité de la table. dépend principalement du nombre d'enregistrements et de la longueur moyenne de la table, du volume de croissance, du volume de lecture et d'écriture et de la taille totale qui sont évalués. Généralement, pour les tables OLTP, il est recommandé qu'une seule table ne dépasse pas 20 millions de lignes de données et que la taille totale soit inférieure à 15 Go. Visites : le volume de lecture et d'écriture d'une seule table est inférieur à 1 600/s

Comment interroger les données de ligne : Les instructions SQL classiques que nous utilisons généralement pour demander la quantité de données contenues dans une table sont les suivantes :

• select count(*) from table
• select count(1) from table Mais lorsque la quantité de données est trop importante, une telle requête peut expirer, nous devons donc changer la méthode de requête

• utiliser le nom de la bibliothèque

• afficher l'état de la table comme 'nom de la table' ou afficher l'état de la table ; comme 'nom de la table'G ;

La méthode ci-dessus peut non seulement interroger les données de la table, mais également afficher les informations détaillées de la table. Ajoutez G pour formater la sortie. Y compris le nom de la table, la version du moteur de stockage, le nombre de lignes, le nombre d'octets par ligne, etc. Vous pouvez l'essayer vous-même

Espace disque

Afficher la capacité de la base de données spécifiée

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;

Interroger l'utilisation du disque de toutes les tables d'un base de données unique

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
where table_schema='mysql'
order by data_length desc, index_length desc;

Les résultats de la requête sont les suivants :

Il est recommandé que le volume de données représente moins de 70 % de l'utilisation du disque. Dans le même temps, pour certaines données qui croissent rapidement, vous pouvez envisager d'utiliser un grand disque lent pour l'archivage des données (pour l'archivage, veuillez vous référer au Plan 3)

Capacité de l'instance

MySQL est un modèle de service basé sur les threads, donc dans Dans certains scénarios avec une concurrence élevée, une seule instance ne peut pas utiliser pleinement les ressources CPU du serveur et le débit sera bloqué au niveau de la couche MySQL. Vous pouvez envisager votre propre mode d'instance en fonction de l'entreprise

Cause du problème

.

Nous avons déjà trouvé la taille de notre table de données ci-dessus. Alors, quelle est la raison fondamentale pour laquelle plus la quantité de données dans une seule table est grande, plus l'efficacité d'exécution de l'entreprise est lente ?

Lorsque la quantité de données dans une table atteint des dizaines de millions ou des centaines de millions, l'effet de l'ajout d'index n'est pas si évident. La raison pour laquelle les performances se détériorent est que le niveau de la structure arborescente B+ qui maintient l'index devient plus élevé. Lors de l'interrogation d'une donnée, davantage d'E/S disque doivent être expérimentées, de sorte que les performances de la requête deviennent plus lentes. . 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 =21902400

Vous souvenez-vous encore de la quantité de données qu'un arbre B+ peut stocker ?

La plus petite unité de stockage du moteur de stockage InnoDB est une page, et la taille d'une page est 16k.

Les feuilles d'arbre B+ stockent les données et les nœuds internes stockent les valeurs clés + les pointeurs. La table organisée en index détermine dans quelle page se trouvent les données grâce à la méthode de recherche binaire de nœuds non-feuilles et de pointeurs, puis accède à la page de données pour trouver les données requises

🎜🎜Supposons que la hauteur de l'arbre B+ est 2 , c'est-à-dire qu'il y a un nœud racine et plusieurs nœuds feuilles. Le nombre total d'enregistrements stockés dans cet arbre B+ est = le nombre de pointeurs de nœud racine * le nombre de lignes enregistrées dans un seul nœud feuille. 🎜🎜🎜Si la taille des données d'une ligne d'enregistrements est de 1 000, alors le nombre d'enregistrements qu'un seul nœud feuille peut stocker = 16 000/1 000 = 16.🎜🎜 Combien de pointeurs sont stockés dans les nœuds non feuilles ? Nous supposons que l'ID de clé primaire est de type 🎜bigint, d'une longueur de 8 octets🎜 (🎜L'intervieweur vous a posé des questions sur le type int, un int est de 32 bits, 4 octets🎜), et la taille du pointeur est définie sur 6. octets dans le code source InnoDB, donc 8+6=14 octets, 16k/14B =16*1024B/14B = 1170🎜🎜🎜Par conséquent, un arbre B+ d'une hauteur de 2 peut stocker 1170 * 16= 18 720 éléments comme ces enregistrements de données. De même, un arbre B+ d'une hauteur de 3 peut stocker 1170 *1170 *16 =21902400, ce qui signifie qu'il peut stocker environ 20 millions d'enregistrements. La hauteur de l'arborescence B+ est généralement de 1 à 3 couches, ce qui peut répondre aux exigences de stockage de dizaines de millions de niveaux de données. 🎜🎜Si l'arborescence B+ souhaite stocker plus de données, le niveau de la structure de l'arborescence sera plus élevé lors de l'interrogation d'une donnée, plus d'E/S disque doivent être expérimentées, de sorte que les performances de la requête ralentiront. 🎜🎜🎜Comment résoudre le problème de trop de données dans une seule table et de requêtes lentes🎜🎜🎜Après avoir connu la cause première, nous devons réfléchir à la manière d'optimiser la base de données pour résoudre le problème🎜

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

方案一：数据表分区

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

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

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

表分区有什么好处？

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

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

• 一些查询可以得到极大的优化，这主要是借助于满足一个给定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. Plan de module :

Avant de diviser, estimez la quantité de données. Par exemple, la table user contient 40 millions de données, et maintenant les données doivent être divisées en 4 tables user1 user2 uesr3 user4. Par exemple, id = 17, 17 modulo 4 vaut 1, plus , donc ces données sont stockées dans la table user2.

Remarque : Auto_increment doit être supprimé du tableau après le fractionnement horizontal. L'ID à ce moment peut être obtenu en utilisant une table temporaire à augmentation automatique d'ID ou en utilisant la méthode redis incr.

Avantages : Les données sont réparties uniformément dans différents tableaux et la probabilité de problèmes chauds est très faible.

Inconvénients : il sera difficile d'étendre et de migrer les données à l'avenir. Lorsque la quantité de données augmente, ce qui était auparavant divisé en 4 tables sera désormais divisé en 8 tables. La valeur modulo change et la migration des données doit être effectuée. à nouveau.

2.range range schéma

Divisez les données par plage, c'est-à-dire que les commandes dans une certaine plage sont stockées dans une certaine table. Par exemple, id=12 est stocké dans la table user1 et id=13 millions est stocké dans la table user2.

Avantages : Propice à l'expansion future des données

Inconvénients : Si les données chaudes existent dans une table, la pression est dans une table et il n'y a aucune pression sur les autres tables.

Nous constatons que les deux solutions ci-dessus présentent des lacunes mais sont complémentaires. Alors que se passera-t-il si nous combinons ces deux solutions ?

3. Combinaison du module de hachage et du schéma de plage

Comme le montre la figure ci-dessous, nous pouvons voir que le groupe stocke des données avec des ID de 0 à 40 millions, puis il y a trois bases de données DB0 DB1 DB2. il y a quatre bases de données dans DB0, DB1 et DB2 ont trois bases de données

Supposons que l'identifiant soit 15000 puis modulo 10 (pourquoi modulo 10 car il y a 10 tables), prends 0 et tombe dans DB_0, puis selon la plage, il tombe dans le tableau_0.

Résumé : L'utilisation du module de hachage combinée à un schéma de plage peut non seulement éviter le problème des données chaudes, mais également faciliter l'expansion des données à l'avenir

Nous avons déjà entendu parler des partitions et des sous-tables MySQL, alors allons-y jetez un oeil à ces deux. Quelles sont les différences entre ces technologies et les scénarios applicables ?

La différence entre le partitionnement et la division des tables :

1 En termes de méthode d'implémentation

• Le sharding de mysql est un véritable sharding. Une fois qu'une table est divisée en plusieurs tables, chacune Les petites tables sont toutes des tables complètes, correspondant à trois fichiers, un fichier de données .MYD, un fichier d'index .MYI et une structure de table .frm Les partitions sont différentes. La table est partitionnée, c'est toujours une table. Elle ne deviendra pas deux tables, mais elle aura plus de blocs pour stocker les données.

2. Pour améliorer les performances

L'objectif du sous-tableau est de savoir comment améliorer la simultanéité MySQL lors de l'accès aux données

• En ce qui concerne les partitions, comment briser les capacités de lecture et d'écriture du disque pour atteindre l'objectif ; dans le but d'améliorer les performances de MySQL.

3. En termes de difficulté de mise en œuvre

1. Il existe de nombreuses façons de diviser les tables. Utiliser la fusion pour diviser les tables est le moyen le plus simple. Cette méthode présente à peu près la même difficulté que le partitionnement racine et peut être transparente pour le code du programme. Si vous utilisez d'autres méthodes de partitionnement de table, cela sera plus gênant que le partitionnement. 2. La mise en œuvre du partitionnement est relativement simple. Il n'y a pas de différence entre créer une table de partition et construire une table ordinaire, et elle est transparente jusqu'à la fin du code. La relation entre le partitionnement et les tables de partitionnement

1. performances de mysql High, a de bonnes performances dans un état de concurrence élevée.

2. Le partitionnement et le partitionnement des tables ne sont pas contradictoires et peuvent coopérer les uns avec les autres. Pour les tables avec un grand volume d'accès et une grande quantité de données de table, nous pouvons combiner le partitionnement et le partitionnement des tables. les données des tables sont volumineuses. Pour les tables, nous pouvons les partitionner, etc.

Problèmes de partitionnement de base de données et de table

1. Problèmes de transaction

Après l'exécution du partitionnement de base de données et de table, la gestion des transactions de base de données devient difficile car les données sont stockées dans différentes bases de données. Si vous comptez sur la fonction de gestion distribuée des transactions de la base de données elle-même pour exécuter les transactions, vous paierez un prix élevé en termes de performances ; si l'application aide au contrôle et forme une transaction logique de programme, cela entraînera également une charge de programmation.

2. Problèmes de jointure entre bases de données et tables croisées

Après l'exécution du partitionnement de la base de données et des tables, il est inévitable que les données initialement très logiquement liées soient divisées en différentes tables et différentes bibliothèques. les opérations d'association de tables seront limitées. Nous ne pouvons pas joindre des tables situées dans différentes sous-bases de données, ni des tables avec des granularités de sous-tables différentes. Par conséquent, l'activité qui peut être effectuée avec une seule requête peut nécessiter plusieurs requêtes. .

3. Charge supplémentaire de gestion des données et pression de calcul des données

Fardeau supplémentaire de gestion des données, le plus évident est le problème du positionnement des données et de l'exécution répétée de l'ajout, de la suppression, de la modification et des requêtes de données. Ceux-ci peuvent être résolus via des applications, mais cela entraînera inévitablement des opérations logiques supplémentaires.Par exemple, pour une table utilisateur qui enregistre les performances de l'utilisateur, l'entreprise nécessite de trouver les 100 meilleurs résultats. Avant de diviser la table, une seule commande par instruction peut être effectuée, mais lors de l'exécution, cela entraînera inévitablement des opérations logiques supplémentaires. tables, n instructions order by seront nécessaires pour connaître les 100 principales données utilisateur pour chaque table fractionnée, puis combiner les données pour obtenir le résultat.

Troisième option : archivage à chaud et à froid

Pourquoi archiver à chaud et à froid : En fait, la raison est similaire à la deuxième option, qui est de réduire la quantité de données dans une seule table, la hauteur de l'arbre devient plus bas, et les E/S disque rencontrées par la requête deviennent moindres, l'efficacité peut être améliorée Si les données de votre entreprise font une distinction claire entre le chaud et le froid, par exemple, vous n'avez besoin d'afficher que les données de la semaine ou du mois écoulé. Dans ce cas, les données de cette semaine et de ce mois sont appelées données chaudes, et le reste des données sont des données froides. Nous pouvons ensuite archiver les données froides dans d'autres tables de base de données pour améliorer l'efficacité opérationnelle de nos données chaudes.

Parlons du processus d'archivage

• Créer une table d'archive En principe, la table d'archive créée doit être cohérente avec la table d'origine. Initialisation des données de la table d'archive

Comment choisir les trois options ci-dessus

1. uniquement dans une certaine zone 3. Il n'y a pas de scénario de requête conjointe

Le partitionnement et le partitionnement de table consistent à diviser physiquement les fichiers correspondant à la table de données. Le nom de la table correspondante reste inchangé, il n'affectera donc pas la logique métier précédente sql

• . les requêtes après le partitionnement de la table créeront des objets correspondants, ce qui entraînera également une certaine surcharge. L'agrégation des données partitionnées prendra également beaucoup de temps ; le champ d'utilisation n'est pas adapté aux volumes de données dépassant des dizaines de millions

Table de données. sous-tableau

La quantité de données est importante et il est impossible de distinguer les zones chaudes et froides évidentes. Et les données peuvent être complètement divisées en fonction des intervalles

Il convient aux données où se trouvent les limites des partitions chaudes et froides. pas très évident.Cette méthode peut être utilisée pour des données similaires ultérieures, diviser la grande table en petites tables pour améliorer l'efficacité de la requête, de l'insertion, etc.

• Si le nombre de grandes tables de données augmente progressivement, de plus en plus correspondantes. les tables de la base de données devront être divisées en tables, la division des intervalles est relativement fixe. Si la table unique suivante Une grande quantité de données aura également un impact sur les performances, la complexité de mise en œuvre est plus compliquée que la troisième solution. pour tester l'impact de l'ensemble du processus de mise en œuvre sur le traitement de la couche d'encodage sur l'entreprise d'origine ;

sous-bibliothèque d'archives chaudes et froides

Les partitions froides et chaudes des données sont évidentes ; l'utilisation des données froides est extrêmement faible ;

Le processus de migration des données a moins d'impact sur l'entreprise, moins de volume de développement et moins de coûts

Besoin de confirmer les règles de fractionnement des tablesVous pouvez selon vos propres scénarios commerciaux, pour choisir une solution qui convient à votre entreprise, je vais vous donner quelques idées~Maintenant, ce dont je veux parler est presque terminé. S'il y a quelque chose qui ne va pas ou si vous avez des doutes, vous êtes les bienvenus. 【Recommandation associée : 】

			tutoriel vidéo mysql

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!