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.
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
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. 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 :
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
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)
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
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+树的存放总记录数为=根结点指针数*单个叶子节点记录行数。
因此，一棵高度为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 ?
16k. 
🎜🎜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之类的操作
知道了两个知识后，我们来看一下分库分表的方案
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.
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 ?
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 ?
Problèmes de partitionnement de base de données et de table
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.
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.
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
1. uniquement dans une certaine zone 3. Il n'y a pas de scénario de requête conjointe
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
| Vous 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!
mysql modifier le nom de la table de données
MySQL crée une procédure stockée
La différence entre MongoDB et MySQL
Comment vérifier si le mot de passe MySQL est oublié
mysql créer une base de données
niveau d'isolement des transactions par défaut de MySQL
La différence entre sqlserver et mysql
mysqlmot de passe oublié
![[Web front-end] Démarrage rapide de Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
