Explication détaillée de la façon dont PHP optimise MySQL pour des dizaines de millions de tables

Tout d'abord, utiliser Mysql pour stocker des centaines de milliards de données est en effet un très grand défi. Une seule table Mysql peut en effet stocker 1 milliard de niveaux de données, mais les performances sont actuellement très médiocres. Un grand nombre d'expériences dans le projet ont prouvé que la capacité d'une seule table Mysql est d'environ 5 millions et que les performances sont au niveau. c'est le meilleur.

L'optimisation des grandes tables est principalement résolue via la sous-base de données et la sous-table. Il existe actuellement trois solutions courantes : 分区, 分库分表, NoSql/NewSql. Dans les projets réels, ces trois solutions sont combinées. À l'heure actuelle, les données de base de la plupart des systèmes sont principalement le stockage RDBMS, complété par le stockage NoSql/NewSql.

Partition

Tout d'abord, comprenons le schéma de partition.

Les tables partitionnées sont implémentées par plusieurs tables sous-jacentes liées. Ces tables sous-jacentes sont également représentées par des objets handle, nous pouvons donc également accéder directement à chaque partition. Le moteur de stockage gère les tables sous-jacentes des partitions de la même manière qu'il gère les tables ordinaires (toutes les tables sous-jacentes doivent utiliser le même moteur de stockage). L'index de la table de partition consiste simplement à ajouter un index identique à chaque table sous-jacente. Cette solution protège les utilisateurs des détails du partitionnement Même si les conditions de requête n'ont pas de colonne de partitionnement, elle peut toujours fonctionner normalement (mais les performances sont moyennes pour le moment).

Cependant, ses défauts sont évidents : de nombreuses ressources sont limitées par une seule machine, comme le nombre de connexions, le débit du réseau, etc. La manière de partitionner est l’un des éléments clés des applications pratiques.

Commençons par un exemple : en prenant les informations client comme exemple, la quantité de données client est de plus de 50 millions. Le contexte du projet nécessite de sauvegarder la relation contraignante de la carte bancaire du client, la relation contraignante du document du client et celle du client. informations commerciales contraignantes.

Dans ce contexte commercial, comment devrions-nous concevoir la base de données. Au cours de la première phase du projet, nous avons établi une table de relations commerciales client, qui contient des informations commerciales redondantes liées à chaque client.

La structure de base est à peu près la suivante :

Lors de l'interrogation, indexez la carte bancaire et le Numéro d'entreprise Index, le numéro de document est utilisé comme index. À mesure que la demande augmente, les indices de ce tableau atteindront plus de 10. De plus, lorsque le client résilie le contrat et signe ensuite un autre contrat, deux données y seront enregistrées, mais le statut contraignant est différent.

En supposant que nous ayons 50 millions de clients, 5 types d'entreprises et une moyenne de 2 cartes par client, alors le volume de données de ce tableau atteindra un étonnant 500 millions. En fait, le nombre d'utilisateurs de notre système. est toujours Cela ne fonctionnera pas tant qu'il n'atteindra pas un million. Une telle conception n’est absolument pas possible, qu’il s’agisse d’une insertion ou d’une requête, cela ferait planter le système.

Les données de la base de données mysql sont stockées sur le disque sous forme de fichiers par défaut, elles sont placées sous /mysql/data (peuvent être visualisées via le datadir dans my.cnf One principalement). correspond à trois Un fichier est frm pour stocker la structure de la table, l'un est myd pour stocker les données de la table et l'autre est myi pour stocker l'index de la table. Ces trois fichiers sont très volumineux, notamment le fichier .myd, qui fait presque 5G. La première optimisation de partition est effectuée ci-dessous. Il existe quatre méthodes de partitionnement prises en charge par Mysql :

Dans notre projet, il n'y a aucun scénario d'utilisation pour le partitionnement par plage et le partitionnement par liste If. il est basé sur la liaison Personnalisez le numéro pour créer une partition de plage ou de liste. Le numéro de liaison n'a aucune signification commerciale réelle et ne peut pas être interrogé via celui-ci. Par conséquent, nous nous retrouvons avec la partition HASH et la partition HASH ne prend en charge que les partitions int. tapez des colonnes, et c’est l’une d’entre elles.

Le partitionnement KEY peut prendre en charge plusieurs colonnes, mais il nécessite également que l'une des colonnes soit de type int. En regardant la structure de notre table de bibliothèque, nous constatons qu'aucune des colonnes n'est de type int. ? Ajoutez une colonne, la colonne d'heure de liaison, définissez cette colonne sur le type int, puis partitionnez-la en fonction de l'heure de liaison et divisez les utilisateurs liés chaque jour dans la même zone.

Après cette optimisation, notre insertion est beaucoup plus rapide, mais la requête est toujours très lente. Pourquoi ?

Parce que lors de la requête, nous interrogeons uniquement en fonction de la carte bancaire ou du numéro d'identification, et non en fonction de l'heure. C'est équivalent à chaque requête, mysql interrogera toutes les tables de partition.

La deuxième optimisation de la solution est effectuée. Puisque le partitionnement HASH et le partitionnement KEY nécessitent qu'une des colonnes soit de type int, est-il possible de créer une liste de type int pour le partitionnement ?

L'analyse a révélé qu'il y a un secret dans la chaîne de chiffres sur la carte bancaire. Les cartes bancaires sont généralement une chaîne de nombres allant de 16 à 19 chiffres. Est-il possible de prendre l'un des nombres et de l'utiliser comme partition de table ? Grâce à l'analyse, nous avons constaté que dans cette chaîne de nombres, l'un d'eux est ? en effet un nombre aléatoire de 0 à 9. Généré, nous effectuons un partitionnement KEY basé sur le numéro de carte bancaire + chiffres aléatoires. Chaque fois que nous interrogeons, nous interceptons les chiffres aléatoires par calcul, ajoutons le numéro de carte et effectuons une requête conjointe pour atteindre l'objectif. de requête de partition. Il convient de noter qu'après le partitionnement, l'index créé doit également être une colonne de partition, sinon Mysql interrogera toujours les données dans toutes les tables de partition.

Le problème de la vérification de la relation contraignante via le numéro de carte bancaire a été résolu. Qu'en est-il du numéro d'identification ? Comment vérifier la relation contraignante via le numéro d'identification ?

Comme mentionné précédemment, l'indexation doit être effectuée sur la clé de partition, sinon cela entraînera une analyse complète de la table. Nous avons créé une nouvelle table pour enregistrer la relation de liaison du numéro d'identification du client. Le numéro d'identification de chaque client est unique. Dans la nouvelle table de relation de liaison du numéro d'identification, le numéro d'identification est utilisé comme clé primaire. Alors, comment calculer l'état de la partition ? les informations sur le document du client sont relativement complexes, y compris le numéro d'identification, le laissez-passer de Hong Kong, Macao et Taiwan, le permis de conduire d'un véhicule à moteur, etc. Comment trouver la clé de partition dans le numéro de document désordonné.

Afin de résoudre ce problème, nous divisons la table des relations de liaison des numéros d'identification en deux. L'une des tables est dédiée à l'enregistrement des types de numéros d'identification, et l'autre table enregistre les autres types de numéros d'identification dans le document. table de relation de liaison du type de carte d'identité, nous divisons le numéro de mois dans le numéro d'identification comme clé de partition et enregistrons le numéro d'identification du client né le même mois dans la même zone, le divisant ainsi en 12 si le volume de données d'autres types de documents ne dépasse pas 100 000, il n'est pas nécessaire de les partitionner.

De cette façon, chaque fois que vous effectuez une requête, déterminez d'abord quelle table interroger en fonction du type de certificat, puis calculez la clé de partition pour la requête. Après la conception de la partition, lors de la sauvegarde de 20 millions de données utilisateur, le fichier de sauvegarde des données de la table de carte bancaire a été divisé en 10 petits fichiers et le fichier de sauvegarde des données de la table de certificat a été divisé en 12 petits fichiers. Cela a résolu les deux problèmes de requête. et aussi Une question demeure : que faire du numéro d'entreprise ?

Un client dispose de plusieurs services souscrits, comment les sauvegarder ? À l’heure actuelle, il n’est pas approprié d’utiliser une solution de partitionnement. Elle nécessite une solution de partitionnement de table.

Tables

Nous avons mentionné plus tôt que pour MySQL, ses fichiers de données sont stockés sur le disque sous forme de fichiers. Lorsqu'un fichier de données est trop volumineux, il sera difficile et fastidieux pour le système d'exploitation d'exploiter le fichier volumineux, et certains systèmes d'exploitation ne prennent pas en charge les fichiers volumineux. À ce stade, le tableau doit être divisé.

De plus, le moteur de stockage couramment utilisé pour MySQL est Innodb, et sa structure de données sous-jacente est l'arborescence B+. Lorsque le fichier de données est trop volumineux, l'interrogation d'un nœud peut interroger plusieurs niveaux, ce qui entraînera inévitablement le chargement de plusieurs opérations d'E/S dans la mémoire, ce qui prendra certainement beaucoup de temps.

De plus, il existe le mécanisme de verrouillage d'Innodb pour les arbres B+. En verrouillant chaque nœud, lorsque la structure de la table est modifiée, l'arborescence sera verrouillée. Lorsque le fichier de table est volumineux, cela peut être considéré comme irréalisable. Donc pour résumer, il faut effectuer les opérations de sous-table et de sous-base de données.

Comment effectuer le partitionnement de bases de données et de tables. Actuellement, il existe de nombreuses versions sur Internet. Certaines des solutions les plus connues sont : TDDL, DRDS et cobar d'Alibaba, les organisations privées de JD Finance ; ' MyCAT ; 360's Atlas ; Meituan's Zebra ; d'autres sociétés telles que NetEase, 58, JD.com et d'autres sociétés ont développé un middleware.

Tant de solutions middleware de sous-bases de données et de tables peuvent être résumées en deux catégories : le mode client et le mode proxy.

mode client

Mode proxy

Que ce soit en mode client ou en mode proxy. Plusieurs étapes principales sont les mêmes : analyse SQL, réécriture, routage, exécution et fusion des résultats. Personnellement, je préfère utiliser le mode client. Il présente une architecture simple, une perte de performances relativement faible et de faibles coûts d'exploitation et de maintenance.

Comment diviser les types d'entreprises en bases de données et en tables. L'étape la plus importante du partitionnement des bases de données et des tables est la sélection des colonnes de partitionnement. La qualité de la sélection des colonnes de partitionnement déterminera directement si l'ensemble du schéma de partitionnement de la base de données et des tables est finalement réussi. La sélection de la colonne de partitionnement est fortement liée à l'activité.

Dans notre scénario de projet, le meilleur choix pour la colonne de partitionnement est sans aucun doute le numéro d'entreprise. Grâce au numéro d'entreprise, différents services contractuels contraignants du client sont enregistrés dans différentes tables et sont acheminés vers la table correspondante pour une interrogation en fonction du numéro d'entreprise, de manière à optimiser davantage le SQL.

Pour plus de connaissances sur php, veuillez visiter le tutoriel php !

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!