InnoDB自增与insert

最新推荐文章于 2024-01-27 15:41:15 发布

原创最新推荐文章于 2024-01-27 15:41:15 发布 · 301 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#AUTO_INCREMENT #mysql

MySQL 专栏收录该内容

16 篇文章

订阅专栏

本文探讨了MySQL自增锁从5.1到8.0的变化，包括自增锁的类型、并发性能提升、innodb_autoinc_lock_mode设置及其对临界区的影响。重点介绍了insert-select策略在不同版本中的处理方式，并揭示了如何处理最大值更新和自增策略调整。

1、引言

当我们对某个列添加自增AUTO_INCREMENT属性后，那么，该该列面对insert语句时，是遵循什么逻辑呢，下面依次说明；

2、自增锁与MySQL5.1之前版本

MySQL5.1版本之前，当执行一个insert语句时，会对数据表添加一个全表维度的锁，该锁确保每次只有一条insert语句在执行，每插入一行数据，就会生成一个自增数据，所以，我们在查看自增列的时候发现，里面的数据是严格连续自增的；

这种实现方式，优点在于严格保证AUTO_INCREMENT自增，缺点在于并发效果很差，毕竟AUTO_INCREMENT锁是全表加锁，直到insert语句结束，如果有100条insert语句的话，那也只能等这100条insert都执行完毕之后，才会释放AUTO_INCREMENT锁；

3、自增锁与MySQL5.1

这里先列举出2种类型的SQL语句：

SQL1：insert into：SQL1中，需要插入的行数是可以提前确定的，因此，AUTO_INCREMENT不再加全表的AUTO-INC锁，只会在产生自增列数据的时候加一个轻量级的互斥锁，等自增数据分配好，锁就释放了，举个例子：我这里有10个insert语句，此刻，轻量级的互斥锁一次性分配10个自增值，然后立刻释放对数据表的锁定，然后这10个insert分别拿着对应的自增值，依次插入数据表；

对于这种类型的insert语句，其并发情况下的临界区变小了，也不再持有全表的锁，从而提升了并发性能；

注意：如果在尝试加锁的过程中遇到有其他事务持有全表的AUTO-INC锁，还是要等待全表的AUTO-INC锁释放再执行本次插入操作；

SQL2：insert...select：这类插入语句的特点在于：插入行数的不确定性，因此，这类insert-select语句仍然使用全局AUTO-INC锁，其执行过程还是与MySQL5.1之前版本一致，即先获取全表AUTO-INC锁，然后为insert-select中的每个insert语句严格分配连续自增的数值，等这些insert全部执行完毕之后，AUTO-INC锁才释放；

至于原因，则是因为：主从复制，这是因为在MySQL8.0之前，MySQL的主从是基于SQL语句复制的，假如我insert-select中，一共有1000个记录需要insert，就在这1000个insert过程中，又有50个insert来插入，那么问题来了，换成你，你怎么怎么处理从而确保1000个记录的insert-select、50个insert每次执行的结果都是一致的呢？只能说是我这1000个insert-select执行时，把表全部锁定，其他insert都要在后面排队，或则，这50个insert在insert-select执行之前先插入，这样就确保主从库在执行的时候，其最终数据都是一致的；

4、自增锁与MySQL8.0

由于insert-select还是持有全表锁，而根本原因在于主从复制的原理，因此到了MySQL8.0这里，必须把insert执行结果与SQL语句执行的前后顺序进行脱离，MySQL8.0主从策略改成基于数据行实现，因此，insert-select也就不再需要全表加锁来生成自增列数据了，所有的插入语句只有在生成自增列数据的时候要求持有一个轻量级的互斥锁，等到自增数据生成好之后释放锁；

总结就是：用insert的自增列都不能保证连续自增，来换取最高的并发性能；