开发者

MySQL主键自增会遇到的坑及解决方法

开发者 https://www.devze.com 2023-04-21 09:09 出处:网络 作者: 江南一点雨
目录1. 为什么不用 UUID2. 主键自增的问题2.1 数据插入的三种形式2.2 innodb_autoinc_lock_mode2.3 实践3. 小结在上篇文章中,松哥和小伙伴们分享了 mysql 的聚簇索引,也顺便和小伙伴们分析了为什么在 MySQL 中主键
目录
  • 1. 为什么不用 UUID
  • 2. 主键自增的问题
    • 2.1 数据插入的三种形式
    • 2.2 innodb_autoinc_lock_mode
    • 2.3 实践
  • 3. 小结

    在上篇文章中,松哥和小伙伴们分享了 mysql 的聚簇索引,也顺便和小伙伴们分析了为什么在 MySQL 中主键不应该使用随机字符串。但是主键不用随机字符串用什么?主键自增?主键自增就是最佳方案吗?有没有其他坑?今天我们就来讨论下这个话题。

    1. 为什么不用 UUID

    经过上篇文章的介绍,我们知道在 MySQL 中,主键索引就是聚簇索引,MySQL 表中的数据是根据主键值聚集在一起的,聚簇索引是一棵 B+Tree,这棵树中的数据是有序的。

    所以,如果我们使用 UUID 字符串作为主键,那么就会导致每次数据插入的时候,都需要在 B+Tree 中寻找到适合它自己的位置,找到之后就有可能要挪动后面的节点(就像在数组中插入一条记录),挪动后面的节点,就有可能涉及到页分裂,插入效率就会降低。

    另一方面,在非聚簇索引中,叶子结点保存的是主键值,主键如果是一个很长的 UUID 字符串,就会占据较大的存储空间(相对 int 而言),那么同一个叶子结点能够保存的主键值数量就会减少,进而可能会导致树变高,树变高,意味着查询的时候 IO 次数增加,查询效率降低。

    基于上面的分析,我们在 MySQL 中尽量不使用 UUID 作为主键,不用 UUID,可能会有小伙伴想到,那我使用主键自增行不行?

    对于上面提到的两个使用 UUID 作为主键的问题,使用主键自增显然都可以解决。主键自增,每次只需要往树的末尾添加就行了,基本上不会涉及到页分裂问题;主键自增意味着主键是数字,占用的存储空间相对来说就比较小,对非聚簇索引的影响也会小一些。

    python么主键自增就是最佳方案吗?主键自增有没有一些需要注意的问题?

    2. 主键自增的问题

    以下内容,有一个共同的大前提,开发者_Mysql就是我们的表设置了主键自增。

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

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

    为了解决这个问题,我们就需要选择适合自己的 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-a026http://www.devze.com-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主键自增会遇到的坑及解决方法

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

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

    MySQL主键自增会遇到的坑及解决方法

    改完之后再重启查看,如下:

    MySQL主键自增会遇到的坑及解决方法

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

    现在假设我有如下表:

    CREATE TABLE `user` (
      `id` int unsigned NOT NULL AUTO_INCREMENT,
      `username` varchar(255) CHARACTER SET utf8mb4 COLLpythonATE 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');
    

    插入完成之后,我们来看查询结果:

    MySQL主键自增会遇到的坑及解决方法

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

    接下来,我把 innodb_autoinc_lock_mode 取值改为 1,如下:

    MySQL主键自增会遇到的坑及解决方法

    还是上面相同的 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主键自增会遇到的坑及解决方法的详细内容,更多关于MySQL主键自增的资料请关注我们其它相关文章!

    0

    精彩评论

    暂无评论...
    验证码 换一张
    取 消

    关注公众号