Comment résoudre les pièges rencontrés par l'auto-incrémentation de la clé primaire MySQL

1. Pourquoi ne pas utiliser l'UUID

Donc, si nous utilisons la chaîne UUID comme clé primaire, alors chaque fois que des données sont insérées, nous devons trouver sa propre position dans B+Tree. Après l'avoir trouvée, il y aura Cela peut être. Il sera nécessaire de déplacer les nœuds ultérieurs (tout comme l'insertion d'un enregistrement dans un tableau). Le déplacement des nœuds ultérieurs peut impliquer un fractionnement de page et l'efficacité de l'insertion sera réduite.

Le contenu suivant a un principe commun, c'est-à-dire que notre table a une auto-incrémentation de clé primaire.

2.1 Trois formes d'insertion de données

2.1 数据插入的三种形式

首先，我们在向数据表中插入数据的时候，一般来说有三种不同的形式，分别如下：

• insert into user(name) values('javaboy') 或者 replace into user(name) values('javaboy') ，这种没有嵌套子查询并且能够确定具体插入多少行的插入叫做 simple insert，不过需要注意的是 INSERT ... ON DUPLICATE KEY UPDATE 不算是 simple insert。

• load data 或者 insert into user select ... from ....，这种都是批量插入，叫做 bulk insert，这种批量插入有一个特点就是插入多少条数据在一开始是未知的。

• insert into user(id,name) values(null,'javaboy'),(null,'江南一点雨')，这种也是批量插入，但是跟第二种又不太一样，这种里边包含了一些自动生成的值（本案例中的主键自增），并且能够确定一共插入多少行，这种称之为 mixed insert，对于前面第一点提到的 INSERT ... ON DUPLICATE KEY UPDATE 也算是一种 mixed insert。

将数据插入分为这三类，主要是因为在主键自增的时候，锁的处理方案不同，我们继续往下看。

2.2 innodb_autoinc_lock_mode

我们可以通过控制 innodb_autoinc_lock_mode 变量的值，来控制在主键自增的时候，MySQL 锁的处理思路。

innodb_autoinc_lock_mode 变量一共有三个不同的取值：

• 0: 这个表示 traditional，在这种模式下，我们上面提到的三种不同的插入 SQL，对于自增锁的处理方案是一致的，都是在插入 SQL 语句开始的时候，获取到一个表级的 AUTO-INC 锁，然后当插入 SQL 执行完毕之后，再释放掉这把锁，这样做的好处是可以确保在批量插入的时候，自增主键是连续的。

• 1: 这个表示 consecutive，在这种模式下，对 simple insert（能够确定具体插入行数的，对应上面 1、3 两种情况）做了一些优化，由于 simple insertTout d'abord, lorsque nous insérons des données dans la table de données, il existe généralement trois formes différentes, qui sont les suivantes :
• insérer dans les valeurs de l'utilisateur('javaboy') ou remplacer dans les valeurs de l'utilisateur('javaboy'), ceci n'est pas intégré. l'insertion qui utilise une requête imbriquée et peut déterminer le nombre de lignes à insérer est appelée insertion simple, mais il convient de noter que INSERT ... ON DUPLICATE KEY UPDATE ne compte pas comme simple insertion.
  🎜🎜charger des données ou insérer dans la sélection utilisateur ... à partir de ...., ce sont des insertions en masse, appelées insertion en masse, une caractéristique de cette insertion par lots est que le nombre de données à insérer est inconnu au début. 🎜🎜🎜🎜insérer dans les valeurs de l'utilisateur(id,name)(null,'javaboy'),(null,'Jiangnan a little rain'), c'est aussi une insertion par lots, mais c'est différent du second. Ce n'est pas la même chose. Il contient des valeurs générées automatiquement (la clé primaire dans ce cas est auto-incrémentée), et peut déterminer le nombre de lignes insérées au total. C'est ce qu'on appelle insertion mixte. Pour la partie précédente, le INSERT ... ON DUPLICATE KEY UPDATE mentionné précédemment est également considéré comme un insert mixte. 🎜🎜

2.2 innodb_autoinc_lock_mode

🎜🎜0 : Cela représente le mode traditionnel, les trois insertions différentes que nous avons mentionnées ci-dessus, SQL a la même solution pour l'auto-incrémentation. verrouille.Il acquiert un verrou AUTO-INC au niveau de la table au début de l'insertion d'une instruction SQL, puis libère le verrou une fois l'exécution de l'instruction SQL insérée terminée. De cette façon, l'avantage est qu'il peut garantir que l'auto. -l'incrémentation de la clé primaire est continue pendant l'insertion par lots. 🎜🎜🎜🎜1 : Cela signifie consécutif. Dans ce mode, quelques optimisations ont été apportées à l'insertion simple (qui permet de déterminer le nombre spécifique de lignes insérées, correspondant aux deux situations 1 et 3 ci-dessus) , car simple insert Il est facile de calculer le nombre de lignes à insérer, donc plusieurs valeurs consécutives peuvent être générées en même temps et utilisées dans les instructions SQL d'insertion correspondantes. Le verrouillage INC peut être libéré à l'avance et le nombre de verrous peut être réduit en attendant pour améliorer l'efficacité de l'insertion simultanée. 🎜🎜🎜🎜2 : Cela signifie entrelacé. Dans ce cas, il n'y a pas de verrouillage AUTO-INC. Elles sont traitées un par un, il peut y avoir un problème : même si la clé primaire est incrémentée, elle ne l'est pas. continu. 🎜

Comme vous pouvez le voir dans l'introduction ci-dessus, en fait, le troisième type, c'est-à-dire que lorsque la valeur de innodb_autoinc_lock_mode est 2, l'efficacité de la concurrence est la plus forte, devrions-nous donc définir innodb_autoinc_lock_mode=2 ?

Cela dépend de la situation.

Brother Song a déjà écrit un article pour présenter à ses amis les trois formats de fichiers journaux du binlog MySQL :

• row : ce qui est enregistré dans le binlog est la valeur spécifique plutôt que le SQL d'origine. Pour donner un exemple simple. , supposons qu'un champ de la table soit UUID et que le SQL exécuté par l'utilisateur soit insert into user(username,uuid) values('javaboy',uuid()), puis le SQL finalement enregistré dans le journal binaire est insérer dans les valeurs de l'utilisateur (nom d'utilisateur,uuid) ('javaboy',‘0212cfa0-de06-11ed-a026-0242ac110004’). insert into user(username,uuid) values('javaboy',uuid())，那么最终记录到 binlog 中的 SQL 是 insert into user(username,uuid) values('javaboy',‘0212cfa0-de06-11ed-a026-0242ac110004’)。

• statement：binlog 中记录的就是原始的 SQL 了，以 row 中的为例，最终 binlog 中记录的就是 insert into user(username,uuid) values('javaboy',uuid())。

• mixed：在这种模式下，MySQL 会根据具体的 SQL 语句来决定日志的形式，也就是在 statement 和 row 之间选择一种。

对于这三种不同的模式，很明显，在主从复制的时候，statement 模式可能会导致主从数据不一致，所以现在 MySQL 默认的 binlog 格式都是 row。

回到我们的问题：

• 如果 binlog 格式是 row，那么我们就可以设置 innodb_autoinc_lock_mode 的值为 2，这样就能尽最大程度保证数据并发插入的能力，同时不会发生主从数据不一致的问题。

• 如果 binlog 格式是 statement，那么我们最好设置 innodb_autoinc_lock_mode 的值为 1，这样对于 simple insert 的并发插入能力进行了提高，批量插入还是先获取 AUTO-INC 锁，等插入成功之后再释放，这样也能避免主从数据不一致，保证数据复制的安全性。

• 以上两点主要是针对 InnoDB 存储引擎，如果是 MyISAM 存储引擎，都是先获取 AUTO-INC 锁，插入完成再释放，相当于 innodb_autoinc_lock_mode 变量的取值对 MyISAM 不生效。

2.3 实践

接下来我们来通过一个简单的 SQL 来和小伙伴们演示一下 innodb_autoinc_lock_mode 不同取值对应不同结果的情况。

我们可以使用以下 SQL 查询来查看当前 innodb_autoinc_lock_mode 的设置：

可以看到，我使用的 8.0.32 这个版本目前默认值是 2。

我先把它改成 0，修改方式就是在 /etc/my.cnf 文件中添加一行 innodb_autoinc_lock_mode=0：

改完之后再重启查看，如下：

可以看到，现在就已经改过来了。

现在假设我有如下表：

CREATE TABLE `user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

这个自增是从 100 开始计的，现在假设我有如下插入 SQL：

insert into user(id,username) values(1,'javaboy'),(null,'江南一点雨'),(3,'www.javaboy.org'),(null,'lisi');

插入完成之后，我们来看查询结果：

按照我们前文的介绍，这个情况应该是可以解释的通的，我这里不再赘述。

接下来，我把 innodb_autoinc_lock_mode 取值改为 1，如下：

还是上面相同的 SQL，我们再执行一遍。执行完成之后结果也和上文相同。

但是！！！**当上面的 SQL 执行完毕之后，如果我们还想再插入数据，并且新插入的 ID 不指定值，则我们发现自动生成的 ID 值为 104。**这就是因为我们设置了 innodb_autoinc_lock_mode=1，此时，执行 simple insert

2.3 Pratique

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!