乐趣区

关于mysql:MySQL-主键自增也有坑

jiezi

在上篇文章中，松哥和小伙伴们分享了 MySQL 的聚簇索引，也顺便和小伙伴们剖析了为什么在 MySQL 中主键不应该应用随机字符串。然而主键不必随机字符串用什么？主键自增？主键自增就是最佳计划吗？有没有其余坑？明天咱们就来探讨下这个话题。

1. 为什么不必 UUID

通过上篇文章的介绍，咱们晓得在 MySQL 中，主键索引就是聚簇索引，MySQL 表中的数据是依据主键值汇集在一起的，聚簇索引是一棵 B+Tree，这棵树中的数据是有序的。

所以，如果咱们应用 UUID 字符串作为主键，那么就会导致每次数据插入的时候，都须要在 B+Tree 中寻找到适宜它本人的地位，找到之后就 有可能 要移动前面的节点（就像在数组中插入一条记录），移动前面的节点，就有可能波及到页决裂，插入效率就会升高。

另一方面，在非聚簇索引中，叶子结点保留的是主键值，主键如果是一个很长的 UUID 字符串，就会占据较大的存储空间（绝对 int 而言），那么同一个叶子结点可能保留的主键值数量就会缩小，进而可能会导致树变高，树变高，意味着查问的时候 IO 次数减少，查问效率升高。

基于下面的剖析，咱们在 MySQL 中尽量不应用 UUID 作为主键，不必 UUID，可能会有小伙伴想到，那我应用主键自增行不行？

对于下面提到的两个应用 UUID 作为主键的问题，应用主键自增显然都能够解决。主键自增，每次只须要往树的开端增加就行了，基本上不会波及到页决裂问题；主键自增意味着主键是数字，占用的存储空间相对来说就比拟小，对非聚簇索引的影响也会小一些。

那么主键自增就是最佳计划吗？主键自增有没有一些须要留神的问题？

2. 主键自增的问题

以下内容，有一个独特的大前提，就是咱们的表设置了主键自增。

一般来说，主键自增是没有什么问题的。然而，如果在高并发环境下，就会有问题了。

首先最容易想到的就是在高并发插入的时候产生的尾部热点问题，并发插入时，大家都须要去查问这个值而后计算出本人的主键值，那么主键的上界就会成为热点数据，并发插入时这里会产生锁竞争。

为了解决这个问题，咱们就须要抉择适宜本人的 innodb_autoinc_lock_mode。

2.1 数据插入的三种模式

首先，咱们在向数据表中插入数据的时候，一般来说有三种不同的模式，别离如下：

  1. insert into user(name) values('javaboy') 或者 replace into user(name) values('javaboy')，这种没有嵌套子查问并且 可能确定具体插入多少行 的插入叫做 simple insert，不过须要留神的是 INSERT ... ON DUPLICATE KEY UPDATE 不算是 simple insert。
  2. load data 或者 insert into user select ... from ....，这种都是批量插入，叫做 bulk insert，这种批量插入有一个特点就是插入多少条数据在一开始是未知的。
  3. insert into user(id,name) values(null,'javaboy'),(null,'江南一点雨')，这种也是批量插入，然而跟第二种又不太一样，这种里边蕴含了一些主动生成的值（本案例中的主键自增），并且可能确定一共插入多少行，这种称之为 mixed insert，对于后面第一点提到的 INSERT ... ON DUPLICATE KEY UPDATE 也算是一种 mixed insert。

将数据插入分为这三类，次要是因为在主键自增的时候，锁的解决计划不同，咱们持续往下看。

2.2 innodb_autoinc_lock_mode

咱们能够通过管制 innodb_autoinc_lock_mode 变量的值，来管制在主键自增的时候，MySQL 锁的解决思路。

innodb_autoinc_lock_mode 变量一共有三个不同的取值：

  • 0: 这个示意 traditional，在这种模式下，咱们下面提到的三种不同的插入 SQL，对于自增锁的解决计划是统一的，都是在插入 SQL 语句开始的时候，获取到一个表级的 AUTO-INC 锁，而后当插入 SQL 执行结束之后，再开释掉这把锁，这样做的益处是能够确保在批量插入的时候，自增主键是间断的。
  • 1: 这个示意 consecutive，在这种模式下，对 simple insert（可能确定具体插入行数的，对应下面 1、3 两种状况）做了一些优化，因为 simple insert 插入多少行这个很好计算，于是能够一次性生成几个间断的值用在对应的插入 SQL 语句上，这样就能够提前开释掉 AUTO-INC 锁，能够缩小锁期待，进步并发插入效率。
  • 2: 这个示意 interleaved，这种状况下不存在 AUTO-INC 锁，来一个解决一个，批量插入的时候，就有可能呈现主键尽管自增，然而不间断的问题。

从下面的介绍中小伙伴们能够看到，实际上第三种，也就是 innodb_autoinc_lock_mode 取值为 2 的状况下，并发效率是最强的，那么咱们是不是就应该设置 innodb_autoinc_lock_mode=2 呢？

这得看状况。

松哥之前写过一篇文章和小伙伴们介绍 MySQL binlog 日志文件的三种格局：

  • row：binlog 中记录的是具体的值而不是原始的 SQL，举一个简略例子，假如表中有一个字段是 UUID，用户执行的 SQL 是 insert into user(username,uuid) values('javaboy',uuid())，那么最终记录到 binlog 中的 SQL 是 insert into user(username,uuid) values('javaboy',‘0212cfa0-de06-11ed-a026-0242ac110004’)。
  • statement：binlog 中记录的就是原始的 SQL 了，以 row 中的为例，最终 binlog 中记录的就是 insert into user(username,uuid) values('javaboy',uuid())。
  • mixed：在这种模式下，MySQL 会依据具体的 SQL 语句来决定日志的模式，也就是在 statement 和 row 之间抉择一种。

对于这三种不同的模式，很显著，在主从复制的时候，statement 模式可能会导致主从数据不统一，所以当初 MySQL 默认的 binlog 格局都是 row。

回到咱们的问题：

  • 如果 binlog 格局是 row，那么咱们就能够设置 innodb_autoinc_lock_mode 的值为 2，这样就能尽最大水平保证数据并发插入的能力，同时不会产生主从数据不统一的问题。
  • 如果 binlog 格局是 statement，那么咱们最好设置 innodb_autoinc_lock_mode 的值为 1，这样对于 simple insert 的并发插入能力进行了进步，批量插入还是先获取 AUTO-INC 锁，等插入胜利之后再开释，这样也能防止主从数据不统一，保证数据复制的安全性。
  • 以上两点次要是针对 InnoDB 存储引擎，如果是 MyISAM 存储引擎，都是先获取 AUTO-INC 锁，插入实现再开释，相当于 innodb_autoinc_lock_mode 变量的取值对 MyISAM 不失效。

2.3 实际

接下来咱们来通过一个简略的 SQL 来和小伙伴们演示一下 innodb_autoinc_lock_mode 不同取值对应不同后果的状况。

首先，咱们能够通过如下 SQL 查看以后 innodb_autoinc_lock_mode 的取值：

能够看到，我应用的 8.0.32 这个版本目前默认值是 2。

我先把它改成 0，批改形式就是在 /etc/my.cnf 文件中增加一行 innodb_autoinc_lock_mode=0：

改完之后再重启查看，如下：

能够看到，当初就曾经改过来了。

当初假如我有如下表：

CREATE TABLE `user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

这个自增是从 100 开始计的，当初假如我有如下插入 SQL：

insert into user(id,username) values(1,'javaboy'),(null,'江南一点雨'),(3,'www.javaboy.org'),(null,'lisi');

插入实现之后，咱们来看查问后果：

依照咱们前文的介绍，这个状况应该是能够解释的通的，我这里不再赘述。

接下来，我把 innodb_autoinc_lock_mode 取值改为 1，如下：

还是下面雷同的 SQL，咱们再执行一遍。执行实现之后后果也和上文雷同。

然而！！！当下面的 SQL 执行结束之后，如果咱们还想再插入数据，并且新插入的 ID 不指定值，则咱们发现主动生成的 ID 值为 104。这就是因为咱们设置了 innodb_autoinc_lock_mode=1，此时，执行 simple insert 插入的时候，零碎一看我要插入 4 条记录，就间接给我提前拿了 4 个 ID 进去，别离是 100、101、102 以及 103，后果该 SQL 实际上只用了两个 ID，剩下两个没用，然而下次插入还是从 104 开始了。

3. 小结

好啦，这就是对于主键自增的一个小小知识点，小伙伴们肯定要依据理论状况来为 innodb_autoinc_lock_mode 属性取一个适合的值。

退出移动版