让网站建设便宜到底,杭州网站优化效果,seo短视频入口引流,荣县住房和城乡建设厅网站测试环境#xff1a; MySQL版本#xff1a;8.0 数据库表#xff1a;T #xff08;主键id#xff0c;唯一索引c#xff0c;普通字段d#xff09; 如果你的业务设计依赖于自增主键的连续性#xff0c;这个设计假设自增主键是连续的。但实际上#xff0c;这样的假设是错的…测试环境 MySQL版本8.0 数据库表T 主键id唯一索引c普通字段d 如果你的业务设计依赖于自增主键的连续性这个设计假设自增主键是连续的。但实际上这样的假设是错的因为自增主键不能保证连续递增。
一、自增值的属性特征
1. 自增主键值是存储在哪的 MySQL5.7版本 在 MySQL 5.7 及之前的版本自增值保存在内存里并没有持久化。每次重启后第一次打开表的时候都会去找自增值的最大值 max(id)然后将 max(id)1 作为这个表当前的自增值。 MySQL8.0之后版本 在 MySQL 8.0 版本将自增值的变更记录在了 redo log 中重启的时候依靠 redo log 恢复重启之前的值。
可以通过看表详情查看当前自增值以及查看表参数详情AUTO_INCREMENT值(AUTO_INCREMENT就是当前数据表的自增值) 2. 自增主键值的修改机制
在表t中我定义了主键id为自增值在插入一行数据的时候自增值的行为如下 如果插入数据时 id 字段指定为 0、null 或未指定值那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段 如果插入数据时 id 字段指定了具体的值就直接使用语句里指定的值。
根据要插入的值和当前自增值的大小关系自增值的变更结果也会有所不同。假设某次要插入的值是 X当前的自增值是 Y。 如果 XY那么这个表的自增值不变 如果 X≥Y就需要把当前自增值修改为新的自增值。
二、新增语句自增主键是如何变化的
我们执行以下SQL语句来观察自增主键是如何进行变化的
insert into t values(null, 1, 1); 流程图如下所示 流程步骤 AUTO_INCREMENT1 表示下一次插入数据时如果需要自动生成自增值会生成 id1。 insert into t values(null, 1, 1) 执行器调用 InnoDB 引擎接口写入一行传入的这一行的值是 (0,1,1) get AUTO_INCREMENT1 InnoDB 发现用户没有指定自增 id 的值获取表 t 当前的自增值 1 AUTO_INCREMENT2 insert into t values(1, 1, 1) 将传入的行的值改成 (1,1,1)并把自增值改为2 insert 111) 执行插入操作至此流程结束
大家可以发现在这个流程当中是先进行自增值的1在进行新增语句的执行的。大家可以发现这个操作并没有进行原子操作如果SQL语句执行失败那么自增是不是就不会连续了呢
三、自增主键值不连续情况唯一主键冲突
当我执行以下SQL语句时
insert into t values(null, 1, 1); 第一次我们可以进行新增成功根据自增值的修改机制。如果插入数据时 id 字段指定为 0、null 或未指定值那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段
当我们第二次在执行以下SQL语句时就会出现错误。因为我们表中c字段是唯一索引会出现Duplicate key error错误导致新增失败。 例如 AUTO_INCREMENT2 表示下一次插入数据时如果需要自动生成自增值会生成 id2。 insert into t values(null, 1, 1) 执行器调用 InnoDB 引擎接口写入一行传入的这一行的值是 (0,1,1) get AUTO_INCREMENT2 InnoDB 发现用户没有指定自增 id 的值获取表 t 当前的自增值 2 AUTO_INCREMENT3 insert into t values(2, 1, 1) 将传入的行的值改成 (2,1,1)并把自增值改为3 insert 211) 执行插入操作由于已经存在 c1 的记录所以报 Duplicate key error语句返回。
可以看到这个表的自增值改成 3是在真正执行插入数据的操作之前。这个语句真正执行的时候因为碰到唯一键 c 冲突所以 id2 这一行并没有插入成功但也没有将自增值再改回去。所以在这之后再插入新的数据行时拿到的自增 id 就是 3。也就是说出现了自增主键不连续的情况。
四、自增主键值不连续情况事务回滚
其实事务回滚原理也和上面一样都是因为异常导致新增失败但是自增值没有进行回退。
五、自增主键值不连续情况批量插入
批量插入数据的语句MySQL 有一个批量申请自增 id 的策略 语句执行过程中第一次申请自增 id会分配 1 个 1 个用完以后这个语句第二次申请自增 id会分配 2 个 2 个用完以后还是这个语句 第三次申请自增 id会分配 4 个 依此类推同一个语句去申请自增 id每次申请到的自增 id 个数都是上一次的两倍。
执行以下SQL语句在表t中先新增了4条数据在创建表tt把表t数据进行批量新增
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 tt like t;
insert into tt(c,d) select c,d from t;insert into tt values(null, 5,5);第一次申请到了 id1第二次被分配了 id2 和 id3 第三次被分配到 id4 到 id7。当我们再执行 insert into t2 values(null, 5,5)实际上插入的数据就是8,5,5)出现了自增主键不连续的情况。 六、自增主键值的优化
1.什么是自增锁
自增锁是一种比拟非凡的表级锁。并且在事务向蕴含了 AUTO_INCREMENT 列的表中新增数据时就会去持有自增锁假如事务 A 正在做这个操作如果另一个事务 B 尝试执行 INSERT语句事务 B 会被阻塞住直到事务 A 开释自增锁。
2.自增锁有哪些优化
在 MySQL 5.0 版本的时候自增锁的范围是语句级别。也就是说如果一个语句申请了一个表自增锁这个锁会等语句执行结束以后才释放。显然这样设计会影响并发度。在MySQL 5.1.22 版本引入了一个新策略新增参数 innodb_autoinc_lock_mode默认值是 1。
传统模式Traditional
这个参数的值被设置为 0 时表示采用之前 MySQL 5.0 版本的策略即语句执行结束后才释放锁
传统模式他可以保证数据一致性但是如果有多个事务并发的执行 INSERT 操作AUTO-INC的存在会使得 MySQL 的性能略有降落因为同时只能执行一条 INSERT 语句。
间断模式Consecutive
这个参数的值被设置为 1 时普通 insert 语句自增锁在申请之后就马上释放类似 insert … select 这样的批量插入数据的语句自增锁还是要等语句结束后才被释放
间断模式他可以保证数据一致性但是如果有多个事务并发的执行 INSERT 批量操作时就会进行锁等待状态。如果我们业务插入数据量很大时这个时候MySQL的性能就会大大下降。
穿插模式Interleaved
这个参数的值被设置为 2 时所有的申请自增主键的动作都是申请后就释放锁。
穿插模式他没有进行任何的上锁设置。在一定情况下是保证了MySQL的性能但是他无法保证数据的一致性。如果我们在穿插模式下进行主从复制时如果你的binlog格式不是row格式主从复制就会出现不一致。
七、MySQL8.0做了哪些优化
在MySQL8.0之后版本已经默认设置为 innodb_autoinc_lock_mode2 binlog_formatrow.。这样更有利与我们在 insert … select 这种批量插入数据的场景时既能提升并发性又不会出现数据一致性问题。
