Une analyse approfondie de la façon de résoudre le problème de MySQL à court d'ID auto-incrémentés

Cet article vous apporte des connaissances pertinentes sur mysql, qui présente principalement les problèmes liés à l'identification automatique. Il existe une limite supérieure pour l'identification. Puisqu'il y a une limite supérieure, il y aura toujours un moment où elle sera épuisée. . Si l'ID Que dois-je faire une fois que j'ai fini de l'utiliser ? Jetons-y un coup d'œil. J'espère que cela sera utile à tout le monde.

Apprentissage recommandé : Tutoriel vidéo MySQL

J'ai récemment vu une question d'entretien comme celle-ci. MySQL n’a plus d’identifiants auto-incrémentés, que dois-je faire ? Voici les solutions à cette question d'entretien.

Si vous avez utilisé ou compris MySQL, vous devez connaître les clés primaires à incrémentation automatique. Chaque ID à incrémentation automatique a une valeur initiale définie, puis augmente en fonction de la taille de pas spécifiée (la taille de pas par défaut est 1). Bien qu'il n'y ait pas de limite supérieure pour les nombres naturels, lorsque nous concevons la structure du tableau, nous spécifions généralement la longueur du champ. Ensuite, il existe une limite supérieure pour l'identifiant. Puisqu'il y a une limite supérieure, il y aura toujours un moment où elle sera épuisée. Et si l'ID est épuisée ? Apprenons ensemble aujourd’hui.

Identifiant à incrémentation automatique

En parlant d'identifiant à incrémentation automatique, je pense que votre première réaction doit être de personnaliser un champ d'identifiant à incrémentation automatique lors de la conception de la structure de la table. Il peut alors y avoir un problème avec une clé primaire unique. lors de l'insertion de données, l'annulation de la transaction SQL, l'insertion par lots, la valeur d'incrémentation automatique de l'application par lots et d'autres raisons entraînent une discontinuité de l'ID d'incrémentation automatique.

La logique de la valeur d'auto-incrémentation définie dans le tableau après sa mise en ligne est la suivante : lors de la demande du prochain identifiant, la même valeur (valeur maximale) sera obtenue. Vous pouvez insérer SQL pour définir l'ID sur la valeur maximale, puis insérer une instruction qui ne définit pas activement l'ID pour vérifier cette conclusion. Si vous insérez à nouveau à ce moment-là, un conflit de clé primaire sera signalé~

Voici un rappel : 232-1 (4294967295) n'est pas un nombre particulièrement grand. Pour une table qui insère et supprime fréquemment des données, il peut être utilisé. en haut. Par conséquent, lors de la création d'une table, vous devez vérifier si votre table est susceptible d'atteindre cette limite supérieure. Si possible, vous devez la créer en tant que bigint de 8 octets non signé.

Le système InnoDB incrémente automatiquement row_id

Si la table InnoDB que vous créez ne spécifie pas de clé primaire, InnoDB créera pour vous un row_id invisible d'une longueur de 6 octets. InnoDB maintient une valeur globale dict_sys.row_id Pour toutes les tables InnoDB sans clés primaires, chaque fois qu'une ligne de données est insérée, la valeur actuelle de dict_sys.row_id est utilisée comme row_id des données à insérer, puis la valeur de dict_sys. .row_id est augmenté de 1.

En fait, lorsque le code est implémenté, row_id est un entier long non signé (bigint non signé) d'une longueur de 8 octets. Cependant, lors de la conception d'InnoDB, il ne restait que 6 octets de longueur pour row_id. De cette façon, seuls les 6 derniers octets ont été placés lors de l'écriture dans la table de données. Par conséquent, deux valeurs row_id peuvent être écrites dans le. table de données. Caractéristiques :

row_id La plage de valeurs écrite dans le tableau est de 0 à 248-1 ;

Lorsque dict_sys.row_id=2^48, s'il existe un autre comportement d'insertion de données, vous devez appliquer pour row_id et récupérez-le. Si vous prenez les 6 derniers octets plus tard, ce sera 0.

Bien que le nombre 2 ^ 48 soit déjà très grand, tout le monde doit savoir qu'un système peut fonctionner pendant une longue période, il peut donc encore atteindre la limite supérieure. À ce stade, une nouvelle application écrasera l'enregistrement d'origine. Par conséquent, essayez de ne pas choisir cette option !

Xid

Lorsque redo log et binlog dans MySQL sont combinés, ils ont un champ commun appelé Xid. Il est utilisé pour correspondre aux transactions dans MySQL.

MySQL maintient une variable globale global_query_id en interne Chaque fois qu'une instruction est exécutée, elle est affectée à Query_id, puis cette variable est augmentée de 1. Si l'instruction actuelle est la première instruction exécutée par cette transaction, MySQL attribuera également Query_id au Xid de cette transaction. Global_query_id est une variable de mémoire pure et sera effacée après le redémarrage. Par conséquent, dans la même instance de base de données, le XID de différentes transactions peut être le même.

Innodb trx_id

InnoDB maintient en interne une variable globale max_trx_id. Chaque fois qu'un nouveau trx_id doit être demandé, la valeur actuelle de max_trx_id est obtenue, puis max_trx_id est augmentée de 1.

L'idée principale de la visibilité des données InnoDB est la suivante : chaque ligne de données enregistre le trx_id qui la met à jour. Lorsqu'une transaction lit une ligne de données, la façon de déterminer si les données sont visibles est d'utiliser la vue de cohérence du. transaction et la ligne Comparez le trx_id des données. Cependant, ce processus comporte des lectures sales, donc l'ID ne sera pas atomique et il existe une possibilité de duplication.

thread_id

En fait, l'identifiant de thread est l'identifiant auto-croissant le plus courant dans MySQL. Habituellement, lorsque nous vérifions diverses scènes, la première colonne de show processlist est thread_id.

La logique de thread_id est facile à comprendre : le système enregistre une variable globale thread_id_counter Chaque fois qu'une nouvelle connexion est créée, thread_id_counter est affecté à la variable de thread de cette nouvelle connexion.

thread_id_counter a une taille définie de 4 octets, donc une fois qu'il atteint 232-1, il se réinitialise à 0 et continue d'augmenter. Le résultat est le même que row_id et l'enregistrement original sera écrasé.

Ce qui précède présente certains des identifiants d'auto-incrémentation de MySQL. En fait, dans l'application réelle, nous pouvons également choisir des clés primaires externes d'auto-incrémentation, puis les conserver dans la base de données pour remplacer les propres identifiants d'auto-incrémentation de la base de données. Parlons-en ci-dessous.

Clé primaire auto-croissante Redis

En fait, il existe de nombreuses façons de générer des clés primaires externes auto-croissantes. Pourquoi devrais-je introduire Redis ? Parce que j’en ai trouvé de nombreux avantages dans des applications pratiques.

Redis lui-même est atomique, donc une concurrence élevée est également thread-safe. En supposant que la longueur du champ de clé primaire est de 20, nous utilisons l'heure + le numéro à incrémentation automatique pour former la clé primaire, par exemple : date à 8 chiffres + 12 numéros à incrémentation automatique. Ensuite, selon la nature de l'entreprise, l'heure peut être déterminée comme étant le niveau de l'année, du mois, du jour ou de la milliseconde. La probabilité de répéter le nombre d'auto-incrémentation entre les millisecondes est alors extrêmement faible et peut être appliquée à. entreprises de base.

Résumé

Plusieurs types d'ID d'incrémentation automatique sont présentés ci-dessus. Chaque ID d'incrémentation automatique a ses propres scénarios d'application, et ses performances après avoir atteint la limite supérieure sont également différentes :

1, Après l'incrémentation automatique. L'ID de la table atteint la limite supérieure. Sa valeur ne changera pas lorsque vous appliquerez à nouveau, ce qui entraînera une erreur de conflit de clé primaire lorsque vous continuerez à insérer des données.
2, Une fois que row_id atteint la limite supérieure, il est atteint. reviendra à 0 puis augmentera à nouveau. Si le même row_id apparaît, les données écrites plus tard seront Pour écraser les données précédentes
3, Xid n'a besoin que d'aucune valeur en double dans le même fichier binlog. Bien que des valeurs en double apparaissent théoriquement, la probabilité est extrêmement faible et peut être ignorée
4 La valeur d'incrément max_trx_id d'InnoDB sera enregistrée à chaque redémarrage de MySQL, donc l'exemple de lecture sale mentionné dans notre article est un incontournable. Bug, heureusement, nous avons encore beaucoup de temps
5, thread_id est le plus courant que nous utilisons, et c'est aussi la logique d'identification d'auto-incrémentation la mieux gérée
6, redis auto-incrémentation externe, niveau en millisecondes , théoriquement, il y aura des valeurs en double, mais la probabilité est extrêmement faible et peut être ignorée
7, En fait, chaque ID auto-croissant a ses propres scénarios applicables, et vous pouvez choisir en fonction des scénarios spécifiques d'utilisation quotidienne. Mais préparez-vous à un mauvais jour, car la durée de fonctionnement du système et le stockage des données doivent être pris en compte de manière globale, choisissez-en un qui ne se répétera pas immédiatement pendant le fonctionnement du système. L'avez-vous appris ?

Apprentissage recommandé : 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!