深入谈谈MySQL中的自增主键-6ren

深入谈谈MySQL中的自增主键

转载作者：qq735679552 更新时间：2022-09-27 22:32:09

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章深入谈谈MySQL中的自增主键由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

目录。

特点
- 保存策略
- 自增值的确定
- 自增值的修改
- 执行过程
- 带来的问题
自增锁
- 加锁策略
- 批插入的优化
- insert...select前后操作同一个表会用到临时表
- 最后
- 总结

MySQL的主键可以是自增的，那么如果在断电重启后新增的值还会延续断电前的自增值吗？自增值默认为1，那么可不可以改变呢？下面就说一下 MySQL的自增值.

。

特点

。

保存策略。

1、如果存储引擎是 MyISAM，那么这个自增值是存储在数据文件中的；。

2、如果是 InnoDB引擎，1）在 5.6之前是存储在内存中，没有持久化，在重启后会去找最大的键值，举个例子，如果一个表当前数据行里最大 id是10，AUTO_INCREMENT=11。这时候，我们删除 id=10 的行，AUTO_INCREMENT 还是 11。但如果马上重启实例，重启后这个表的 AUTO_INCREMENT 就会变成 10；。

　　　　　　　　　　　 2）在 8.0开始，自增值就保存在 redo log中，重启后会从 redo log中读取之前保存的自增值.

。

自增值的确定。

1、如果插入数据时 id字段指定为0、null或未指定，那么就把这个表当前的 AUTO_INCREMENT值填到自增字段，并且会以auto_increment_offset作为初始值，auto_increment_increment为步长，找出第一个大于当前自增值的值作为新的自增值.

2、如果插入的数据的 id字段指定了具体的值，就直接使用语句里的值.

在一些场景下，使用的就不全是默认值。比如，双 M 的主备结构里要求双写的时候，我们就可能会设置成 auto_increment_increment=2，让一个库的自增 id 都是奇数，另一个库的自增 id 都是偶数，避免两个库生成的主键发生冲突.

。

自增值的修改。

假设某次要输入的值是 X，当前的自增值是 Y。那么:

1、如果 X<Y，那么这个表的自增值不变；。

2、如果X≥Y，那么就把当前自增值修改为新的自增值.

。

执行过程。

假设有表t ，id是自增主键，在已有 (1,1,1)的情况下，插入一条 (null,1,1)，那么执行过程就如下:

1、执行器调用 InnoDB 引擎接口写入一行，传入的这一行的值是 (0,1,1),

2、InnoDB 发现用户没有指定自增 id 的值，获取表 t 当前的自增值 2；。

3、将传入的行的值改成 (2,1,1),

4、将表的自增值改成 3；。

5、继续执行插入数据操作，由于已经存在 c=1 的记录，所以报 Duplicate key error，语句返回.

深入谈谈MySQL中的自增主键

。

带来的问题。

由于上面说得这种特性，在一些场景中会出现主键不连续的现象.

场景1：添加数据时唯一索引重复。

深入谈谈MySQL中的自增主键

在 c列索引重复后，原本要分配的主键值 2就会被丢弃，而下次再次插入就从 2 开始计算，也就变成了 3.

场景2：事务回滚。

 
    ? 
   
         insert 
         into 
         t  
         values 
         ( 
         null 
         ,1,1); 
        
         begin 
         ; 
        
         insert 
         into 
         t  
         values 
         ( 
         null 
         ,2,2); 
        
         rollback 
         ; 
        
         insert 
         into 
         t  
         values 
         ( 
         null 
         ,2,2); 
        
         //插入的行是(3,2,2)

在第二条语句回滚后分配给其的主键 2也会被丢弃.

场景3：特殊批插入优化导致。

这里说得特殊的批插入指的是insert … select、replace … select 和 load data 语句。为什么说这些语句可能会导致？这就要说到自增锁了。首先自增锁是为了避免多线程冲突，因为在多线程下，如果同时有多个线程来获取自增值，那么就可能会导致同一个自增值被分配给多条记录，导致逐渐冲突。所以需要自增锁，而为什么前面说得这些批插入语句会导致主键不连续，在下面自增锁部分会说到.

问题：在说自增锁之前，先思考一个问题，为什么对于前两个场景，不把自增主键值设为可以回滚的？这样不就可以避免不连续了么?

答：因为设计成可回滚的会导致性能下降，看下面这个场景.

1、假设事务 A 申请到了 id=2，事务 B 申请到 id=3，那么这时候表 t 的自增值是 4，之后继续执行.

2、事务 B 正确提交了，但事务 A 出现了唯一键冲突.

3、如果允许事务 A 把自增 id 回退，也就是把表 t 的当前自增值改回 2，那么就会出现这样的情况：表里面已经有 id=3 的行，而当前的自增 id 值是 2.

4、接下来，继续执行的其他事务就会申请到 id=2，然后再申请到 id=3。这时，就会出现插入语句报错“主键冲突”.

而为了解决上面这个问题，就需要从下面两个方法中选一个.

方法1、每次申请 id 之前，先判断表里面是否已经存在这个 id。如果存在，就跳过这个 id。但是，这个方法的成本很高。因为，本来申请 id 是一个很快的操作，现在还要再去主键索引树上判断 id 是否存在.

方法二：把自增 id 的锁范围扩大，必须等到一个事务执行完成并提交，下一个事务才能再申请自增 id。这个方法的问题，就是锁的粒度太大，系统并发能力大大下降.

所以，综合来看，比如取消自增值回滚的功能.

。

自增锁

。

自增锁是为了避免在多线程中多个线程获取到同一个主键值，导致主键冲突.

。

加锁策略。

5.0版本：范围是语句，只有等到语句执行完后才会释放.

5.1.22开始：引入了一个innodb_autoinc_lock_mode参数，根据参数值的不同执行不同的策略。默认是1.

1、参数等于0，表示采用之前的策略，即语句执行结束就会释放.

2、参数等于1，对于普通 insert语句，自增锁在申请之后立马释放；。

　　　　　　　对于 insert...select这样的批量插入数据的语句，会等到语句执行完才会释放。加锁范围是 select所涉及到的范围和间隙.

3、参数等于3，所有的申请自增主键的动作都是申请后就释放锁.

问题：为什么默认情况下， insert...select这样的批操作要使用语句级的锁？为什么参数默认不是2?

答：因为对于 insert...select这样的批量插入数据的语句，可能会导致主从不一致的情况发生.

深入谈谈MySQL中的自增主键

在 sessionB执行完 "create table t2 like t"后，sessionA和 sessionB同时操作 t2。如果没有锁，那么执行过程就可能会出现下面的情况.

session B 先插入了两个记录，(1,1,1)、(2,2,2)；然后，session A 来申请自增 id 得到 id=3，插入了（3,5,5)；之后，session B 继续执行，插入两条记录 (4,3,3)、 (5,4,4).

虽然这样看起来确实没有什么问题，但是如果是在集群中，主机这样执行，提示 binlog是 statement格式的，那么从机执行的顺序就有可能和主机不一致，最终导致主从不一致。所以需要在批量插入时加锁。而如果设置为2，那么如果 binlog不是 row，就会导致主从数据不一致.

所以，要想保证数据一致，也保证系统的并发性，可以有两种方案:

方案一：将 binlog格式设为 statement，innodb_autoinc_lock_mode设为1.

方案二：将 binlog格式设为 row，innodb_autoinc_lock_mode设为2。一般我们为了保证 MySQL的高可用，都将 binlog设为 row，所以一般选择第二种方案.

。

批插入的优化。

在批插入时，由于不知道一次性插入的语句有多少，如果记录多达几千万甚至上亿条，那么每次插入都需要分配一次自增值，这样效率会很慢，所以 MySQL 对批操作进行了优化:

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)。这就是前面说到主键不连续的第三种情况.

。

insert...select前后操作同一个表会用到临时表。

假设有表结构。

 
    ? 
   
         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; 
        
         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+1, d  
         from 
         t  
         force 
         index 
         (c)  
         order 
         by 
         c  
         desc 
         limit 1); 
        
 
     
 
   

如果我们查询慢日志，会发现。

深入谈谈MySQL中的自增主键

扫描行数是1，也就是直接在 t上通过索引找到那一条记录，然后插入 t2表.

如果将这条语句改成。

 
    ? 
   
 
     
       
       
         insert 
         into 
         t(c,d) ( 
         select 
         c+1, d  
         from 
         t  
         force 
         index 
         (c)  
         order 
         by 
         c  
         desc 
         limit 1); 
        
 
     
 
   

那么此时查看慢日志就会发现变成了 5，这是为什么？就算全查出来也只会是4条，这时我们查看扫描行数的变化。

深入谈谈MySQL中的自增主键

发现前后变化是4行，所以确定了是使用了临时表，那么就可以确定过程是:

1、创建临时表，表里有两个字段 c 和 d.

2、按照索引 c 扫描表 t，依次取 c=4、3、2、1，然后回表，读到 c 和 d 的值写入临时表。这时，Rows_examined=4.

3、由于语义里面有 limit 1，所以只取了临时表的第一行，再插入到表 t 中。这时，Rows_examined 的值加 1，变成了 5.

至于为什么需要临时表，这是为了防止在读取时，读到了刚刚插入的值.

优化。

因为select返回的记录数较少，所以可以使用内存临时表来优化，。

 
    ? 
   
 
     
       
       
         create 
         temporary 
         table 
         temp_t(c  
         int 
         ,d  
         int 
         ) engine=memory; 
        
 
         insert 
         into 
         temp_t ( 
         select 
         c+1, d  
         from 
         t  
         force 
         index 
         (c)  
         order 
         by 
         c  
         desc 
         limit 1); 
        
 
         insert 
         into 
         t  
         select 
         *  
         from 
         temp_t; 
        
 
         drop 
         table 
         temp_t; 
        
 
     
 
   

这样扫描的总行数只有 select的 1加上临时表上的 1.

。

最后。

对于唯一索引的冲突，可以使用insert into … on duplicate key update来进行冲突后的更新处理，假设表 t中有(1,1,1)、(2,2,2)两条记录，那么执行:

深入谈谈MySQL中的自增主键

在插入时发现冲突就对冲突的记录进行修改操作.

。

总结。

到此这篇关于MySQL中的自增主键的文章就介绍到这了,更多相关MySQL自增主键内容请搜索我以前的文章或继续浏览下面的相关文章希望大家以后多多支持我！。

原文链接：https://www.cnblogs.com/mengxinJ/p/14352038.html 。

最后此篇关于深入谈谈MySQL中的自增主键的文章就讲到这里了,如果你想了解更多关于深入谈谈MySQL中的自增主键的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！。

文章推荐： oracle 日期操作语句总结

文章推荐： oracle数据库的删除方法详解

文章推荐：详解SQL 通配符

文章推荐：图书管理系统的sqlserver数据库设计示例

深入.net调用webservice的总结分析
最近做一个项目，由于是在别人框架里开发app,导致了很多限制，其中一个就是不能直接引用webservice 。我们都知道，调用webserivice 最简单的方法就是在 "引用"
c++ - SDL_PollEvent 深入
这是SDL2代码的一部分 SDL主函数 int main(int argc,char *argv[]) { ... ... bool quit=false; S
c - 深入 assembly
c 中的函数: PHPAPI char *php_pcre_replace(char *regex, int regex_len, ch
nhibernate - IndexOutOfRangeException 深入 NHibernate
我有以下映射: public class SecurityMap : ClassMap { public SecurityMap() {
javascript - 在Vue中使用/深入/错误
我在vue-lic3中使用了SCSS，但是有一个奇怪的错误，使用/ deep /会报告错误，我不想看到它。代码运行环境 vue-cli3 + vant + scss 的CSS /deep/ .van
C# 深入 : Type Constraint
我在深入阅读 C# 时遇到了这个我能理解的内容: 当它被限制为引用类型时，执行的比较类型完全取决于类型参数被限制为什么。但是不能理解这个: 如果进一步限制派生自重载 == 和 != 运算符的特定类型
c# - 用于制作游戏引擎的C#或C++(深入)
Closed. This question is opinion-based。它当前不接受答案。想改善这个问题吗？更新问题，以便editing this post用事实和引用来回答。 3年前关闭。
c++ - this 和 const 深入
有人可以详细介绍关于自赋值的运算符重载中的 *this 和 const 例如: Class& Class::operator=(const Class& other) { a = other.
php - 深入 MYSQL 和闭包表树
在向树中插入新节点时，如何填充闭包表的深度/长度列？ ancestor 和 descendant 中的值是来自另一个表的 ID，表示要以树结构排列的页面。关闭表: ancestor desce
C# 深入 : Problem set?
现在我正在阅读“深入了解 C#”。缺少的一件事是完成一章后我可以解决的一系列问题。那会帮助我理解我刚刚学到的概念。哪里可以找到适合 C#3.0 的问题集？谢谢最佳答案你可以试试LINQ 101
来做操吧！深入 TypeScript 高级类型和类型体操
TypeScript 给 JavaScript 扩展了类型的语法，我们可以给变量加上类型，在编译期间会做类型检查，配合编辑器还能做更准确的智能提示。此外，TypeScript 还支持了高级类型用
python - 深入——yield from inside yield
是否有一个单行代码来获取生成器并生成该生成器中的所有元素？例如: def Yearly(year): yield YEARLY_HEADER for month in range(1, 13)
c# - 方法组(深入 C#)- 需要帮助更好地理解什么是方法组
所以我阅读了一些与“什么是方法组”相关的 StackOverflow 问题以及其他互联网文章，它们在底线都说了同样的话——方法组是“一组重载方法” ". 但是，在阅读 Jon Skeet 的“C# 深
angular - Angular 6 有没有办法让 ViewChildren 深入？
有什么方法可以从子组件中获取子组件吗？想象一下以下组件树: 应用程序问题问题选项(包含复选框) 问题选项(包含复选框) 问题选项(包含复选框) 我想从 App 访问问题选项以选中所有复选框。参
ruby - 深入 Ruby class_eval 和 instance_eval
class_eval 和 instance_eval 在定义方法等情况下是完全可以预测的。我也理解类的实例和类的单例(又名特征类)之间的区别。但是我无法弄清楚以下唯一的事情:比方说，出于某些策略目
Linux 文件权限(深入)——数字到字符串表示法，反之亦然；附加文件权限
我想出了如何将符号 rwx 部分读取/转换为 421 个八进制部分，这非常简单。但是当涉及到特殊字符时，我感到非常困惑。我们知道 -r-xr---wx 转换为 0543，但 -r-sr---wt 或
java - 如果正确安装了 JDK，如何(深入)测试？
我怀疑我系统的 Java 版本有问题。某些应用程序出现段错误或内存不足或存在链接错误。如果我从源代码安装了 JDK，我会做类似“make test”的事情，看看哪些测试失败了。但是，看起来从源代码构建
objective-c - 克隆一个 git 仓库(深入)
如何克隆一个 repo(使用 libgit2 ) 我想做什么git clone确实，但有 libgit2 .我可能要问的是什么 git clone确实很深入。这是我目前正在做的: 初始化一个repo
JavaScript入门③-函数(2)原理{深入}执行上下文
00、头痛的JS闭包、词法作用域？被JavaScript的闭包、上下文、嵌套函数、this搞得很头痛，这语言设计的，感觉比较混乱，先勉强理解总结一下😂😂😂.
r - 深入 R 包 : Time Zones in lubridate
我开始玩 lubridate R 中的包。我注意到 now(tzone="EST")计算为: [1] "2015-08-25 13:01:08 EST" 而 now(tzone="PST")导致警告:

qq735679552

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

深入谈谈MySQL中的自增主键

特点

自增锁