Comment concevoir une clé primaire dans MySQL

Cet article explique le problème de la clé primaire de Mysql, comprend les connaissances liées à la clé primaire Mysql du point de vue du pourquoi et s'étend à la question du schéma de génération de clé primaire. Plus besoin de craindre de connaître CRUD uniquement lorsqu'on lui pose des questions sur Mysql.

1. Pourquoi une clé primaire est nécessaire

Les enregistrements de données doivent être uniques (première forme normale)

Les données doivent être associées à la jointure

L'index sous-jacent de la base de données est utilisé pour récupérer des données

Ce qui suit est beaucoup d'absurdités, vous pouvez passer directement à la section suivante.

« L'information est ce qui est utilisé pour éliminer l'incertitude aléatoire » (Shannon). Les gens peuvent comprendre et transformer le monde en obtenant et en identifiant différentes informations provenant de la nature et de la société pour distinguer différentes choses. Les données sont un enregistrement qui reflète les attributs d’éléments objectifs et constitue une manifestation spécifique d’informations. Une fois les données traitées, elles deviennent des informations ; et les informations doivent être transformées numériquement en données avant de pouvoir être stockées et transmises. Une base de données est utilisée pour stocker des enregistrements de données. Cela étant, les enregistrements sont des informations déterministes (relatives) et leur certitude est leur unicité. Nous arrivons à la première raison :

1. Les enregistrements de données doivent être uniques

Le monde est composé d'existence objective et de ses relations. Les données existent dans des relations numériques et modélisées. Outre la valeur descriptive des données elles-mêmes, leur valeur réside dans leur interconnectivité. Afin d’obtenir l’exactitude de l’association, les données doivent être associées les unes aux autres de manière externe. Par conséquent, reflété dans le stockage des données, le rôle secondaire de la clé primaire est également le deuxième facteur d'existence :

2 Les données doivent être liées

Les données sont utilisées pour décrire la réalité objective, et. cela n’a aucun sens en soi. Ce n'est qu'après s'être organisé en fonction de besoins subjectifs et avoir satisfait les gens d'une certaine manière pour comprendre les choses que cela peut avoir un sens. Les données doivent donc être récupérées et organisées. Ensuite le troisième rôle de la clé primaire :

3. L'index sous-jacent de la base de données est utilisé pour récupérer les données

2. Pourquoi la clé primaire ne doit pas être trop longue

Ceci Le problème est la longueur. Alors, quels sont les avantages d’être plus court que d’être plus long ? (Hé hé hé, connotation) - court et ne prend pas de place. Mais une si petite quantité d'espace disque est insignifiante par rapport à l'ensemble du volume de données, et nous n'utilisons généralement pas beaucoup la colonne de clé primaire. La raison devrait alors être la vitesse, et cela n’a pas grand-chose à voir avec les données d’origine. De là, on conclut naturellement qu'il est lié à l'index et lié à la lecture de l'index. Alors pourquoi les clés primaires longues affectent-elles les performances des index ?

Ce qui précède est la structure de données d'index d'Innodb. Sur la gauche se trouve l'index clusterisé, qui localise les enregistrements de données par clé primaire. Sur la droite se trouve l'index secondaire, qui indexe les données de la colonne et recherche la clé primaire des données via les données de la colonne. Si les données sont interrogées via l'index secondaire, le processus est comme indiqué dans la figure. Tout d'abord, la clé primaire est recherchée dans l'arborescence de l'index secondaire, puis la ligne de données est recherchée via la clé primaire sur l'index cluster. Les nœuds feuilles de l'index secondaire sont des valeurs de clé primaire directement stockées, et non des pointeurs de clé primaire. Par conséquent, si la clé primaire est trop longue, le nombre d'enregistrements d'index pouvant être stockés dans une arborescence d'index secondaire diminuera. De cette manière, dans le tampon d'index limité, le nombre de lectures sur le disque augmentera, donc les performances diminueront. .

3. Pourquoi est-il recommandé d'utiliser l'ID à incrémentation automatique

InnoDB utilise un index clusterisé, comme le montre la figure ci-dessus, l'enregistrement de données lui-même est stocké dans le index principal (un arbre B+) sur les nœuds feuilles. Cela nécessite que chaque enregistrement de données dans le même nœud feuille (la taille d'une page mémoire ou d'une page disque) soit stocké dans l'ordre de la clé primaire, donc chaque fois qu'un nouvel enregistrement est inséré, MySQL l'insérera dans le nœud approprié en fonction de sa clé primaire. . et position, si la page atteint le facteur de charge (la valeur par défaut d'InnoDB est 15/16), une nouvelle page (nœud) est ouverte.

Si la table utilise une clé primaire à incrémentation automatique, alors chaque fois qu'un nouvel enregistrement est inséré, les enregistrements seront ajoutés séquentiellement à la position suivante du nœud d'index actuel. Lorsqu'une page est pleine, un nouveau. La page sera automatiquement ouverte. Il en résulte une structure d'index compacte qui est remplie approximativement de manière séquentielle. Puisqu'il n'est pas nécessaire de déplacer les données existantes à chaque fois qu'elles sont insérées, cela est très efficace et n'ajoute pas beaucoup de temps système à la maintenance de l'index, comme le montre le côté gauche de la figure ci-dessous. Sinon, comme la valeur de la clé primaire insérée à chaque fois est approximativement aléatoire, chaque nouvel enregistrement doit être inséré quelque part au milieu de la page d'index existante, et MySQL doit déplacer les données afin d'insérer le nouvel enregistrement à la position appropriée. , comme indiqué sur le côté droit de la figure ci-dessous, cela entraîne une certaine surcharge. Pour cette raison, Mysql peut avoir besoin d'actualiser fréquemment le tampon pour maintenir l'index, ce qui augmente le nombre d'E/S du disque de méthode, et la structure de l'index doit souvent être réorganisée.

4. Clé commerciale VS clé logique

Clé commerciale, c'est-à-dire utiliser un identifiant ayant une signification commerciale comme clé, par exemple en utilisant le numéro de série de la commande comme clé. clé primaire Clé de la table de commande. La clé logique, c'est-à-dire la clé qui n'a rien à voir avec l'entreprise, génère la clé selon certaines règles, telles que la clé à incrémentation automatique.

Avantages de la clé commerciale

La clé a une signification commerciale et peut être utilisée directement comme mot-clé de recherche lors de l'interrogation

Aucune colonne ni aucun index supplémentaire n'est requis L'espace

peut réduire certaines opérations de jointure.

Inconvénients de la clé métier

Lorsque l'activité change, il faut parfois changer la clé primaire

Il est plus difficile de fonctionner lorsque plusieurs colonnes de clés sont impliquées

Les clés métier sont souvent plus longues et occupent plus d'espace, ce qui entraîne des E/S disque plus importantes

Les données ne peuvent pas être conservées avant que la clé ne soit déterminée. Parfois, lorsque nous n'avons pas déterminé la clé des données, nous souhaitons d'abord ajouter un enregistrement, puis mettre à jour la clé métier

Concevoir une génération de clé qui soit à la fois simple. à utiliser et performant La solution est plus difficile

Avantages de la clé logique

La logique des clés n'a pas besoin d'être modifiée en raison des changements commerciaux

Simple fonctionnement et gestion facile

Les clés logiques ont tendance à être plus petites et à avoir de meilleures performances

Les clés logiques sont plus faciles à garantir l'unicité

Plus faciles à optimiser

Inconvénients de la clé logique

L'interrogation des colonnes de clé primaire et des index de clé primaire nécessite de l'espace disque supplémentaire

Des E/S supplémentaires sont requises lors de l'insertion et de la mise à jour des données

Plus de jointures possibles

S'il n'y a pas de restrictions de politique d'unicité, des clés en double sont susceptibles d'apparaître

L'environnement de test et la clé de l'environnement officiel sont incohérents, ce qui n'est pas propice au dépannage des problèmes

Le la valeur de la clé n'est pas associée aux données et n'est pas conforme aux trois paradigmes

ne peut pas être utilisée pour rechercher des mots-clés

Dépend de la mise en œuvre spécifique des différents systèmes de bases de données, ce qui n'est pas le cas propice au remplacement de la base de données sous-jacente

5. Génération de clé primaire

Dans des circonstances normales, nous utilisons tous l'ID d'incrémentation automatique de Mysql comme clé primaire de la table. . C'est si simple, et d'après ce qui est dit ci-dessus, les performances sont également les meilleures. Cependant, dans le cas des sous-bases de données et des sous-tables, les identifiants auto-incrémentés ne peuvent pas répondre aux besoins. Nous pouvons examiner comment différentes bases de données génèrent des identifiants, ainsi que certaines solutions de génération d'identifiants distribuées. Il nous est utile de réfléchir et même de mettre en œuvre notre propre service de génération d'identifiants distribués.

Implémentation de la base de données

Incrémentation automatique de MySQL

Mysql maintient un compteur d'incrémentation automatique en mémoire et l'accès automatique est effectué à chaque incrémentation du compteur, InnoDB ajoutera un verrou nommé AUTO-INC jusqu'à la fin de l'instruction (notez que le verrou n'est maintenu que jusqu'à la fin de l'instruction, pas la fin de la transaction). Le verrou AUTO-INC est un verrou spécial au niveau de la table utilisé pour améliorer l'insertion simultanée des colonnes contenant auto_increment.

Dans une situation distribuée, vous pouvez en fait utiliser un service et une base de données distincts pour la génération d'identifiants, tout en vous appuyant sur la capacité d'incrémentation automatique des identifiants de table de Mysql pour générer uniformément des identifiants pour les services tiers. Pour des raisons de performances, différentes tables peuvent être utilisées pour différentes entreprises.

Mongodb ObjectId

Pour éviter les conflits de clé primaire, Mongodb conçoit un ObjectId comme identifiant de clé primaire. Il se compose d'un nombre hexadécimal de 12 octets composé des parties suivantes :

Heure : horodatage. 4 octets. Secondes.

Machine : Identification de la machine. 3 octets. Généralement, il s'agit de la valeur de hachage du nom d'hôte de la machine. Cela garantit que différents hôtes génèrent des valeurs de hachage de machine différentes, garantissant ainsi qu'il n'y a pas de conflit dans la distribution et que la même machine a la même valeur.

PID : ID du processus. 2 octets. La machine ci-dessus vise à garantir que l'objectId généré sur différentes machines n'est pas en conflit, et le pid doit garantir que l'objectId généré par différents processus mongodb sur la même machine n'est pas en conflit.

INC : compteur auto-croissant. 3 octets. Les neuf premiers octets garantissent que les identifiants d'objet générés par différents processus sur différentes machines au cours d'une seconde n'entrent pas en conflit. Le compteur à augmentation automatique est utilisé pour garantir que les identifiants d'objet générés au cours de la même seconde n'entreront pas en conflit. Autoriser 256 élevés à la troisième puissance est égal. à 16777216 entrées.

Cassandra TimeUUID

Cassandra utilise les règles suivantes pour générer un identifiant unique : heure + MAC + séquence

Schéma

Zookeeper depuis Augmentation : réalisée grâce au mécanisme d'auto-incrémentation de zk.

Auto-incrémentation Redis : réalisée grâce au mécanisme d'auto-incrémentation de Redis.

UUID : utilisez la chaîne UUID comme clé.

Algorithme de flocon de neige : similaire à l'implémentation de Mongodb, 1 bit de signe + horodatage de 41 bits (niveau milliseconde) + bits de machine de données de 10 bits + séquence de 12 bits en millisecondes.

Implémentation open source

Baidu UidGenerator : basé sur l'algorithme de flocon de neige.

Meituan Leaf : Il implémente également des mécanismes basés sur l'auto-incrémentation (optimisation) Mysql et l'algorithme de flocon de neige.

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!