MySQL-38：自增主键

待我称王封你为后i 2023-01-01 10:49 162阅读 0赞

**使用自增主键的目的：** 可以让数据插入数据页时尽量保持按递增顺序插入，使得数据页数据更加紧凑，避免页分裂。

我们会讨论一下问题：自增主键如何保存？

我们先来建个表：

create table t3(
     	id int(11) not null auto_increment PRIMARY key,
     	a int(11) not null,
    	b int(11) not null,
    	UNIQUE key a(a)
    )ENGINE = INNODB

#### 38.1 自增主键如何保存 ####

表的结构定义语句保存在.fm的文件中，并不会保存自增主键的值。

不同的引擎保存自增主键的方式也不一样：

*  MyISAM：自增主键和表数据文件保存在一起
 *  InnoDB：分为MySQL 8.0之前和MySQL 8.0
    
     *  MySQL 8.0 之前：自增主键的值保存在内存中，如果我们将表的最大一行删除并重启MySQL，会发现自增主键改变了，使用`show create table tableName`来查看自增主键的值。
     *  MySQL 8.0 ：自增主键的值写入redo log，重启可以依靠redo log来进行恢复。

查看方式如下：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]

#### 38.2 自增值修改机制 ####

如果一个字段被设为auto\_increment ，那么在插入数据的时候会出现如下情况：

*  如果字段的值为0，null或者空，则将表的自增值替换该值。
 *  如果字段不为上述情况，则使用。

下面就是如何去计算一个自增值了，这里涉及到两个参数`auto_increment_offset`和`auto_automent_increment`，分别表示自增值的初始值和自增值的步长，默认为1。

我们将插入的值设为X，自增值设为Y：

*  如果X<Y，那么自增值不变
 *  如果X>=Y，那么会产生新的自增值，新的自增值等于X+步长。

#### 38.3 自增值的修改时机 ####

先假设我们去插入一条数据：

insert into t values(null, 2, 2);

语句的执行流程如下：

*  执行器调用InnoDB引擎接口写入一行数据(0,1,1)
 *  InnoDB发现没有指定自增id的值，将表t的自增值2写入并更新自增值为3，修改后的数据为(2,2,2)
 *  继续执行插入数据操作

#### 38.4 自增值不连续的情况 ####

**1.唯一键冲突：**

接着上面的情况，我们的表t中的a字段为唯一索引，当出现唯一索引出现的时候，再插入下个数据，就会发现自增值已经为3了，这就是第一种情况。

**2.事务回滚：**

在多事务并发情况下，有一个事务在最后关头发生错误，进行了回滚，也会导致自增主键出现断层。

**3.自增主键的批量申请**

当MySQL遇到**insert…select**、**replace…select**和**load data**语句时，它就会使用批量申请。

第一次申请1个，第二次申请2个，第三次申请4个，后面会一直翻倍。

如下情况：

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;
    # 第一次：1，第二次：2~3，第三次：4~7
    insert into t2(c,d) select c,d from t;
    insert into t2 values(null, 5,5);

第三次申请的自增值为47，但是使用了一个57是浪费的，也是空缺的。

#### 38.5 自增值的优化 ####

MySQL在申请自增主键的时候是需要申请自增锁的，MySQL 5.1.22版本引入参数`innodb_autoinc_lock_mode`来控制自增锁的释放时机，它有着三个值可以选择，默认为1：

*  0：语句执行完成后释放
 *  1：
    
     *  普通insert语句：申请完成就释放
     *  类似insert…select语句的批量插入语句：语句执行完成再释放
 *  2：任何语句申请后就会释放

我们来看下，它这么设计的意义何在，看如下情况：

![img][]

现在我们关注到语句`create table like t`之后的事情，session B要插入4行数据，session A要插入一行数据，我们假设是在申请自增锁立即释放的情况下，我们再假设执行的情况如下：

*  Session B 插入 (1,1,1)，(2,2,2)
 *  Session A 插入（3,5,5）
 *  Session B 插入 （4,3,3）,（5,4,4）

如果这个时候binlog\_format = statement，我们都知道binlog是在事务提交的时候来写入的，记录的内容为sql语句，如果拿出来备份恢复，结果必然不一致。

有以下两个解决方式：

*  如果一个事务设计到批量插入数据语句，则 自增锁需要等到语句执行结束才释放 。
 *  将binlog\_format 设置为row，记录前后数据情况。

那也是上面MySQL为什么这么设计的问题。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/7b5be9014ad24eb08bf8838033f02b36.png
[img]: /images/20221120/94c81df1135a454d999c52ff5b8ca8cd.png