mysql 字符串主键与整数主键的性能 mysql主键可以是字符串吗

转载

feiry 2023-08-01 13:08:22

文章标签 java mysql sql 学习数据库 文章分类 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 的取值：

mysql 字符串主键与整数主键的性能 mysql主键可以是字符串吗_sql

可以看到，我使用的 8.0.32 这个版本目前默认值是 2。

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

mysql 字符串主键与整数主键的性能 mysql主键可以是字符串吗_mysql_02

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

mysql 字符串主键与整数主键的性能 mysql主键可以是字符串吗_java_03

可以看到，现在就已经改过来了。

现在假设我有如下表：

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,'javaboy'),(null,'lisi');

插入完成之后，我们来看查询结果：

mysql 字符串主键与整数主键的性能 mysql主键可以是字符串吗_学习_04

按照我们前文的介绍，这个情况应该是可以解释的通的，我这里不再赘述。

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

mysql 字符串主键与整数主键的性能 mysql主键可以是字符串吗_mysql_05

还是上面相同的 SQL，我们再执行一遍。执行完成之后结果也和上文相同。

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