MySQL 主键自增也有坑？-六虎

在上篇文章中，松哥和小伙伴们分享了 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 数据刺进的三种方法

首要，咱们在向数据表中刺进数据的时分，一般来说有三种不同的方法，别离如下：

insert into user(name) values('javaboy') 或许 replace into user(name) values('javaboy') ，这种没有嵌套子查询并且能够确认详细刺进多少行的刺进叫做 simple insert，不过需求留意的是 INSERT ... ON DUPLICATE KEY UPDATE 不算是 simple insert。
load data 或许 insert into user select ... from ....，这种都是批量刺进，叫做 bulk insert，这种批量刺进有一个特点便是刺进多少条数据在一开端是未知的。
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 属性取一个合适的值。

MySQL 主键自增也有坑？

1. 为什么不必 UUID