深入谈谈MySQL中的自增主键

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,语句返回。

带来的问题

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

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

在 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。这时,就会出现插入语句报错“主键冲突”。

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

方法一、每次申请 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这样的批量插入数据的语句,可能会导致主从不一致的情况发生。

在 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);

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

扫描行数是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条,这时我们查看扫描行数的变化

发现前后变化是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自增主键内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • mysql修改自增长主键int类型为char类型示例

    原来有一个表中的主键是int自增长类型, 因为业务变化需要把int改成char类型的主键.同时因为原来的表中已经存在了数据,不能删除表重建,只能修改表结构. 首先去掉自增长属性: alter table table_name  change indexid indexid int; 然后去掉主键: ALTER TABLE table_name   DROP   primary key; 修改表结构为char类型: alter table table_name change indexid ind

  • MySQL8新特性:自增主键的持久化详解

    前言 自增主键没有持久化是个比较早的bug,这点从其在官方bug网站的id号也可看出(https://bugs.mysql.com/bug.php?id=199).由Peter Zaitsev(现Percona CEO)于2003年提出.历史悠久且臭名昭著. 首先,直观的重现下. mysql> create table t1(id int auto_increment primary key); Query OK, 0 rows affected (0.01 sec) mysql> inser

  • MySQL中的主键以及设置其自增的用法教程

    1.声明主键的方法: 您可以在创建表的时候就为表加上主键,如: CREATE TABLE tbl_name ([字段描述省略...], PRIMARY KEY(index_col_name)); 也可以更新表结构时为表加上主键,如: ALTER TABLE tbl_name ADD PRIMARY KEY (index_col_name,-); /* 创建一个qq表,将qq_id设为主键,且没有对其进行NOT NULl约束 */ create table qq( qq_id int(10), n

  • mysql非主键自增长用法实例分析

    本文实例讲述了mysql非主键自增长用法.分享给大家供大家参考,具体如下: mysql并非只有主键才能自增长,而是设为键的列就可以设置自增长.   如下: CREATE TABLE t1 ( id INT, col1 INT auto_increment NOT NULL ); 结果如下: 如果把col1列设为键,就可以创建自增. CREATE TABLE t1 ( id INT, col1 INT auto_increment NOT NULL, key(col1) ); 结果如下: 如果我们

  • 使用prometheus统计MySQL自增主键的剩余可用百分比

    最近生产环境一套数据库因为疯狂写日志数据,造成主键值溢出的情况出现,因此有必要将这个指标监控起来. mysqld_exporter自带的这个功能,下面是我使用的启动参数: nohup ./mysqld_exporter --config.my-cnf="./my.cnf" --web.listen-address=":9104" --collect.heartbeat --collect.auto_increment.columns --collect.binlog

  • 利用Java的MyBatis框架获取MySQL中插入记录时的自增主键

    第一步: 在Mybatis Mapper文件中添加属性"useGeneratedKeys"和"keyProperty",其中keyProperty是Java对象的属性名! <insert id="insert" parameterType="Spares" useGeneratedKeys="true" keyProperty="id"> insert into spares

  • Mysql主键UUID和自增主键的区别及优劣分析

    引言 之前有段时间用postgresql 数据库,在上云之后,从自增主键变为uuid,感觉uuid全球唯一,很方便. 最近用mysql,发现mysql主键都是选择自增主键,仔细比较一下,为什么mysql选择自增主键,有什么不同. 在mysql5.0之前,如果是多个master复制的环境,无法用自增主键,因为可能重复.在5.0以及之后的版本通过配置自增偏移量解决了整个问题. 什么情况下我们希望用uuid 1. 避免重复,便于scale,这就是我们做cloud service的时候选择uuid的主要

  • MySQL的自增ID(主键) 用完了的解决方法

    在 MySQL 中用很多类型的自增 ID,每个自增 ID 都设置了初始值.一般情况下初始值都是从 0 开始,然后按照一定的步长增加(一般是自增 1).一般情况下,我们都是用int(11)来作为数据表的自增 ID,在 MySQL 中只要定义了这个数的字节长度,那么就会有上限. MySQL的自增ID(主键) 用完了,怎么办? 如果用 int unsigned (int,4个字节 ), 我们可以算下最大当前声明的自增ID最大是多少,由于这里定义的是 int unsigned,所以最大可以达到2的32幂

  • 浅谈MySQL中的自增主键用完了怎么办

    在面试中,大家应该经历过如下场景 面试官:"用过mysql吧,你们是用自增主键还是UUID?" 你:"用的是自增主键" 面试官:"为什么是自增主键?" 你:"因为采用自增主键,数据在物理结构上是顺序存储,性能最好,blabla-" 面试官:"那自增主键达到最大值了,用完了怎么办?" 你:"what,没复习啊!!"    (然后,你就可以回去等通知了!) 这个问题是一个粉丝给我提的,我觉得

  • 深入探寻mysql自增列导致主键重复问题的原因

    废话少说,进入正题. 拿到问题后,首先查看现场,发现问题表的中记录的最大值比自增列的值要大,那么很明显,当有记录进行插入时,自增列产生的值就有可能与已有的记录主键冲突,导致出错.首先想办法解决问题,通过人工调大自增列的值,保证大于表内已有的主键即可,调整后,导数据正常.问题是解决了,接下来要搞清楚问题原因,什么操作导致了这种现象的发生呢? 这里有一种可能,即业务逻辑包含更新自增主键的代码,由于mysql的update动作不会同时更新自增列值,若更新主键值比自增列大,也会导致上述现象:记录最大值比

随机推荐