Quand diviser les tables dans MySQL

Situations adaptées au fractionnement de tables dans MySQL : 1. Lorsque la quantité de données est trop importante et que le fonctionnement et la maintenance normaux affectent l'accès de l'entreprise, par exemple, la sauvegarde de la base de données nécessite une grande quantité d'E/S disque et d'E/S réseau, ainsi qu'une modification DDL. d'une table verrouillera la table entière, des attentes de verrouillage se produisent lors de l'accès et de la mise à jour de grandes tables ; 2. À mesure que l'activité se développe, certains champs doivent être divisés verticalement. 3. La quantité de données dans une seule table augmente rapidement et lorsque les performances approchent ; le goulot d'étranglement, la division horizontale doit être considérée comme un point.

L'environnement d'exploitation de ce tutoriel : système windows7, version mysql8, ordinateur Dell G3.

Tous les tableaux n'ont pas besoin d'être divisés, cela dépend principalement du taux de croissance des données. La segmentation augmentera dans une certaine mesure la complexité de l'entreprise. En plus de transporter les données et les requêtes, la base de données est également l'une de ses tâches importantes pour aider l'entreprise à mieux répondre à ses besoins.

N'utilisez pas l'astuce de la sous-base de données et de la sous-table sauf en cas d'absolue nécessité pour éviter la « surconception » et « l'optimisation prématurée ». Avant de diviser des bases de données et des tables, ne divisez pas simplement pour le plaisir de diviser. Faites de votre mieux pour faire ce que vous pouvez en premier, comme mettre à niveau le matériel, mettre à niveau le réseau, séparer la lecture et l'écriture, optimiser l'index, etc. Lorsque la quantité de données atteint le goulot d'étranglement d'une seule table, envisagez de partitionner les bases de données et les tables.

Alors, quand faut-il envisager les sous-tables dans MySQL ?

1. La quantité de données est trop importante et le fonctionnement et la maintenance normaux affecteront l'accès de l'entreprise

Le fonctionnement et la maintenance mentionnés ici font référence à :

• Sauvegardez la base de données si une seule table est trop volumineuse et qu'une grande quantité d'E/S disque et d'E/S réseau est requise lors de la sauvegarde. Par exemple, si 1 To de données est transmise sur le réseau et occupe 50 Mo, cela prendra 20 000 secondes. Le risque de l'ensemble du processus est relativement élevé

• Lorsque des modifications DDL sont apportées à une grande table, MySQL se verrouille. la table entière. Cela prendra beaucoup de temps. Pendant cette période, l'entreprise ne peut pas accéder à cette table, ce qui aura un grand impact. Si vous utilisez pt-online-schema-change, des déclencheurs et des tables fantômes seront créés pendant le processus d'utilisation, ce qui prend également beaucoup de temps. Durant cette opération, il est compté comme temps à risque. Diviser le tableau de données et réduire le montant total peut aider à réduire ce risque.

• Les grandes tables seront consultées et mises à jour fréquemment, les attentes de verrouillage sont donc plus susceptibles de se produire. Divisez les données, échangez de l'espace contre du temps et réduisez la pression d'accès déguisée

2 À mesure que l'entreprise se développe, certains champs doivent être divisés verticalement

Par exemple, si la table utilisateur conçue au début de la. Le projet est le suivant :

Dans la phase initiale du projet, cette conception répond à des besoins commerciaux simples et facilite également un développement itératif rapide. Lorsque l'entreprise se développe rapidement, le nombre d'utilisateurs passe de 100 000 à 1 milliard et les utilisateurs sont très actifs. Le champ last_login_name est mis à jour à chaque fois qu'ils se connectent, ce qui entraîne une mise à jour constante de la table des utilisateurs, ce qui est très stressant. Les autres champs : id, name, personal_info sont inchangés ou rarement mis à jour. D'un point de vue métier, il est nécessaire de diviser last_login_time et de créer une nouvelle table user_time. L'attribut

personal_info est mis à jour et interrogé moins fréquemment, et le champ de texte prend trop de place. A ce stade, il est nécessaire de diviser verticalement la table user_ext.

3. La quantité de données augmente rapidement

Avec le développement rapide des affaires, la quantité de données dans une seule table continuera de croître lorsque les performances approchent du goulot d'étranglement, il est nécessaire d'envisager le partitionnement horizontal et de créer. bases de données et tables distinctes. À ce stade, vous devez choisir les règles de segmentation appropriées et estimer la capacité des données à l'avance

Analyse de rentabilisation

1. Scénario commercial du centre utilisateur

Le centre utilisateur est une activité très courante qui fournit principalement aux utilisateurs avec Pour des fonctions telles que l'enregistrement, la connexion, la requête/modification, etc., sa table principale est :

Toute conception architecturale séparée de l'entreprise est une sous-base de données voyou et une sous-base de données de table. Les exigences du scénario métier doivent être définies :

• Côté utilisateur : l'accès front-end, avec un grand nombre de visites, doit garantir une haute disponibilité et une haute cohérence. Il existe deux principaux types d'exigences :

• • Connexion utilisateur : requête d'informations utilisateur via nom de connexion/téléphone/e-mail, 1 % des demandes appartiennent à ce type

  • Requête d'informations utilisateur : après la connexion, interroger l'utilisateur via les informations uid, 99% des demandes sont de ce type

• Côté fonctionnement : accès en arrière-plan, prend en charge les besoins opérationnels et effectue des requêtes de pagination en fonction de l'âge, du sexe, de l'heure de connexion, de l'heure d'inscription, etc. Il s'agit d'un système interne avec un faible volume d'accès et de faibles exigences en matière de disponibilité et de cohérence.

2. Méthode de segmentation horizontale

Lorsque la quantité de données devient de plus en plus grande, la base de données doit être segmentée horizontalement. Les méthodes de segmentation décrites ci-dessus incluent « basée sur une plage numérique » et « basée sur un modèle numérique ». ".

"Basé sur la plage numérique" : Sur la base de l'uid de clé primaire, les données sont divisées horizontalement en plusieurs bases de données en fonction de la plage de l'uid. Par exemple : user-db1 stocke les données avec des plages d'uid comprises entre 0 et 1 000 w, et user-db2 stocke les données avec des plages d'uid comprises entre 1 000 w et 2 000 wuid.

• L'avantage est : l'extension est simple, si la capacité n'est pas suffisante, il suffit d'ajouter une nouvelle base de données.

• L'inconvénient est que le volume des requêtes est inégal. Généralement, les utilisateurs nouvellement enregistrés seront plus actifs, donc le nouvel utilisateur-db2 aura une charge plus élevée que l'utilisateur-db1, ce qui entraînera une utilisation déséquilibrée du serveur

" Selon Numerical modulo " : La clé primaire uid est également utilisée comme base de division, et les données sont divisées horizontalement en plusieurs bases de données en fonction de la valeur modulo de l'uid. Par exemple : user-db1 stocke les données uid modulo 1, user-db2 stocke les données uid modulo 0.

• L'avantage est : le volume de données et le volume de requêtes sont répartis uniformément

• L'inconvénient est : l'expansion est gênante. Lorsque la capacité n'est pas suffisante, l'ajout d'une nouvelle base de données nécessite une refonte. Une migration fluide des données doit être envisagée.

Méthode de requête sans UID

Après la segmentation horizontale, la demande de requête par UID peut être bien satisfaite et peut être directement acheminée vers une base de données spécifique. Pour les requêtes basées sur des non-uid, telles que login_name, on ne sait pas à quelle bibliothèque il faut accéder. Dans ce cas, toutes les bibliothèques doivent être parcourues et les performances seront considérablement réduites.

Pour le côté utilisateur, la solution « d'établir une relation de mappage des attributs non-uid vers l'uid » peut être adoptée ; pour le côté opération, la solution de « séparer le front-end et le back-end » peut être adoptée.

1. Établissez une relation de mappage entre les attributs non-uid et l'uid

• Relation de mappage

Par exemple : login_name ne peut pas être directement localisé dans la base de données, vous pouvez établir une relation de mappage depuis login_name→uid, et utilisez une table d'index ou un cache pour le stocker. Lors de l'accès à login_name, interrogez d'abord l'uid correspondant à login_name via la table de mappage, puis localisez la bibliothèque spécifique via l'uid.

La table de mappage ne comporte que deux colonnes et peut contenir beaucoup de données. Lorsque la quantité de données est trop importante, la table de mappage peut également être divisée horizontalement. Ce type de structure d'index au format kv peut utiliser le cache pour optimiser les performances des requêtes, et la relation de mappage ne changera pas fréquemment et le taux de réussite du cache sera très élevé.

• Méthode Gene

Casser le gène : si la bibliothèque est divisée en 8 bibliothèques via uid, le routage est effectué en utilisant uid%8. À ce stade, les 3 derniers bits de uid déterminent les données utilisateur spécifiques de cette ligne. . Sur quelle bibliothèque il tombe, alors ces 3 bits peuvent être considérés comme des gènes de sous-bibliothèque.

2. Séparation du front-end et du back-end

Pour le côté utilisateur, la principale exigence est de se concentrer sur les requêtes sur une seule ligne. Il est nécessaire d'établir une relation de mappage entre login_name/phone/email et uid. , ce qui peut résoudre le problème de requête de ces champs.

En ce qui concerne le côté opérationnel, il existe de nombreuses requêtes avec pagination par lots et diverses conditions. De telles requêtes nécessitent une grande quantité de calculs, renvoient une grande quantité de données et consomment des performances élevées de la base de données. À l'heure actuelle, si le même lot de services ou de bases de données est partagé avec le côté utilisateur, un petit nombre de requêtes en arrière-plan peuvent occuper une grande quantité de ressources de base de données, entraînant une dégradation des performances d'accès côté utilisateur ou un délai d'attente.

Il est préférable d'adopter la solution de « séparation du front-end et du back-end » pour ce type d'entreprise. L'activité back-end côté exploitation extrait des services et des bases de données indépendants pour résoudre le couplage avec l'activité front-end. système. Étant donné que le côté opérationnel n'a pas d'exigences élevées en matière de disponibilité et de cohérence, il est possible de ne pas accéder à la bibliothèque en temps réel, mais de synchroniser de manière asynchrone les données avec la bibliothèque d'opérations via binlog pour y accéder. Lorsque la quantité de données est importante, vous pouvez également utiliser le moteur de recherche ES ou Hive pour répondre aux méthodes de requête complexes en arrière-plan.

【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!