Un article pour parler de la clé primaire auto-incrémentée dans MySQL

Cet article vous donnera une compréhension approfondie de la clé primaire à incrémentation automatique dans MySQL. J'espère qu'il vous sera utile !

1. Où est stockée la valeur auto-augmentée ?

Différents moteurs ont des stratégies différentes pour enregistrer les valeurs auto-incrémentées

1 La valeur auto-croissante du moteur MyISAM est stockée dans le fichier de données

2 La valeur auto-croissante du moteur InnoDB, dans MySQL 5.7. et les versions antérieures, la valeur auto-croissante est stockée dans le fichier de données, il n'y a pas de persistance. Après chaque redémarrage, lorsque vous ouvrez la table pour la première fois, vous trouverez la valeur maximale d'auto-incrémentation max(id), puis utiliserez max(id) + step size comme valeur d'auto-incrémentation actuelle de la table

select max(ai_col) from table_name for update;

Dans MySQL version 8.0, enregistrez les modifications de la valeur d'auto-incrémentation dans le journal de rétablissement et comptez sur le journal de rétablissement pour restaurer la valeur avant le redémarrage lors du redémarrage

2. Mécanisme de modification de la valeur d'auto-incrémentation

Si le champ id est défini comme AUTO_INCREMENT, lors de l'insertion d'une ligne de données, le comportement de l'auto-incrémentation est le suivant :

1 Si le champ id est spécifié comme 0, valeur nulle ou non spécifiée lors de l'insertion de données, remplissez le champ. valeur AUTO_INCREMENT actuelle de cette table dans le champ d'incrémentation automatique

2. Si lors de l'insertion de données, id Si le champ spécifie une valeur spécifique, utilisez simplement la valeur spécifiée dans l'instruction. Supposons que la valeur à insérer est X, et. la valeur actuelle d'incrémentation automatique est Y. 1. Si Change

2. Si long, continuez à superposer jusqu'à la première valeur supérieure à Les instructions de création d'index et de table sont les suivantes :

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

Supposons qu'il existe déjà un enregistrement ( 1,1,1) dans le tableau t. Exécutez ensuite une commande d'insertion de données :

insert into t values(null, 1, 1);

Le processus d'exécution est le suivant :

1 Le processeur appelle l'interface du moteur InnoDB pour écrire une ligne. in est (0,1,1)2. InnoDB trouve une valeur qui ne spécifie pas d'identifiant d'auto-incrémentation et obtient la valeur actuelle d'auto-incrémentation 2 du tableau t

3. Modifiez la valeur de la ligne entrante. à (2,1,1)4. Changez la valeur d'incrémentation automatique de la table en 3

5. Continuez à insérer des données puisqu'il y a déjà un enregistrement de c=1, donc erreur de clé en double (clé unique. conflit) est signalé et l'instruction renvoie

. L'organigramme d'exécution correspondant est le suivant :

Après cela, lors de l'insertion d'une nouvelle ligne de données, l'ID auto-incrémenté obtenu est 3. Il existe une situation dans laquelle la clé primaire auto-croissante est discontinue

Un conflit de clé unique et l'annulation de la transaction conduiront à la situation où l'identifiant de clé primaire auto-croissante est discontinu

Quatre Optimisation du verrou auto-croissant.

Le verrouillage d'identification auto-augmentation n'est pas un verrouillage de transaction est libéré immédiatement après chaque application, afin de permettre à d'autres transactions de s'appliquer à nouveau

Mais dans la version MySQL 5.0, la portée du verrouillage auto-augmentation est la niveau de la déclaration. En d'autres termes, si une instruction demande un verrouillage par incrémentation automatique de table, le verrou ne sera pas libéré tant que l'instruction n'est pas exécutée. MySQL version 5.1.22 introduit une nouvelle stratégie, le nouveau paramètre innodb_autoinc_lock_mode, la valeur par défaut est 1
. 1. Ce paramètre est défini sur 0, ce qui signifie que la stratégie de la version précédente de MySQL 5.0 est adoptée, c'est-à-dire que le verrou est libéré après l'exécution de l'instruction

2 Ce paramètre est défini sur 1

Pour l'ordinaire. insert instructions, le verrou d'incrémentation automatique est libéré immédiatement après l'application

Similaire aux instructions insert...select qui insèrent des données par lots, le verrou d'incrémentation automatique doit toujours attendre que l'instruction soit terminée avant d'être libérée

3. Ce paramètre est défini sur 2 et toutes les actions à appliquer pour les clés primaires à incrémentation automatique sont libérées après l'application Lock

Pour la cohérence des données, le paramètre par défaut est 1

Si sessionB s'applique à la valeur d'incrémentation automatique et libère le verrouillage d'auto-incrémentation immédiatement, alors la situation suivante peut se produire :

sessionB insère d'abord deux lignes de données (1,1,1), (2,2,2)

sessionA appliquée pour l'identifiant d'auto-incrémentation et a obtenu l'identifiant = 3. Après avoir inséré (3,5,5)
• , sessionB a continué à s'exécuter et a inséré deux enregistrements (4, 3,3), (5,4,4)

Lorsque binlog_format=statement, les deux les sessions exécutent la commande insert data en même temps, il n'y a donc que deux situations pour le journal de mise à jour de la table t2 dans binlog : soit l'enregistrer en premier Pour la sessionA, soit enregistrer la sessionB en premier. Quel que soit celui-ci, ce binlog est exécuté à partir de la base de données esclave ou utilisé pour restaurer une instance temporaire dans la base de données de secours et l'instance temporaire, l'instruction sessionB est exécutée et les ID dans les résultats générés sont continus. À ce moment-là, une incohérence des données s'est produite dans cette bibliothèque.

Les idées pour résoudre ce problème :

1) Laissez les instructions d'insertion de données par lots de la bibliothèque d'origine générer des valeurs d'identification continues. Par conséquent, le verrou d'incrémentation automatique n'est pas libéré tant que l'instruction n'est pas exécutée, juste pour atteindre cet objectif
2) Enregistrez fidèlement les opérations d'insertion de données dans le binlog Lorsque la base de données de secours est exécutée, elle ne repose plus sur l'auto-incrémentation. incrémenter la clé primaire pour générer . Autrement dit, définissez innodb_autoinc_lock_mode sur 2 et binlog_format sur row

如果有批量插入数据（insert … select、replace … select和load data）的场景时，从并发插入数据性能的角度考虑，建议把innodb_autoinc_lock_mode设置为2，同时binlog_format设置为row，这样做既能并发性，又不会出现数据一致性的问题

对于批量插入数据的语句，MySQL有一个批量申请自增id的策略：

1.语句执行过程中，第一次申请自增id，会分配1个

2.1个用完以后，这个语句第二次申请自增id，会分配2个

3.2个用完以后，还是这个语句，第三次申请自增id，会分配4个

4.依次类推，同一个语句去申请自增id，每次申请到的自增id个数都是上一次的两倍

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);
create table t2 like t;
insert into t2(c,d) select c,d from t;
insert into t2 values(null, 5,5);

insert … select，实际上往表t2中插入了4行数据。但是，这四行数据是分三次申请的自增id，第一次申请到了id=1，第二次被分配了id=2和id=3，第三次被分配到id=4到id=7

由于这条语句实际上只用上了4个id，所以id=5到id=7就被浪费掉了。之后，再执行insert into t2 values(null, 5,5)，实际上插入了的数据就是(8,5,5)

这是主键id出现自增id不连续的第三种原因

五、自增主键用完了

自增主键字段在达到定义类型上限后，再插入一行记录，则会报主键冲突的错误

CREATE TABLE t ( id INT UNSIGNED auto_increment PRIMARY KEY ) auto_increment = 4294967295;
INSERT INTO t VALUES(NULL);
INSERT INTO t VALUES(NULL);

第一个insert语句插入数据成功后，这个表的AUTO_INCREMENT没有改变（还是4294967295），就导致了第二个insert语句又拿到相同的自增id值，再试图执行插入语句，报主键冲突错误

【相关推荐：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!