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

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

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

(0)

相关推荐

  • Mysql更新自增主键id遇到的问题

    目录 为什么要更新自增id 问题 如何解决 本是一个自己知道的问题,还是差点踩坑(差点忘了,还好上线前整理上线点时想起来了),特此记录下来 为什么要更新自增id 我是因为历史业务上的坑,导致必须更新一批id,且为了避免冲突需要将id扩大多少倍进行更新,因为我这个表的数据数量不高,属于高读低写的情况,所以就简单的扩大了1000 问题 MySQL中如果我们把自增主键更新为更大的值(例如现在自增id最大值是1000,你更新id=49这个记录到id=1049),MySQL并不会把表的自增值修改为更新后的

  • 为什么mysql自增主键不是连续的

    目录 一 前言 二 自增值存储说明 三 自增值修改机制 四 自增值修改时机 五 导致自增值不连续的原因 5.1 唯一键冲突 5.2 事务回滚 5.3 批量写库操作 六 参考文档 一 前言 提出这个问题,是因为在工作中发现 mysql 中的 user 表的 id 默认是自增的,但是数据库存储的结果却不是连续的. user 表结构: CREATE TABLE `user` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '递增id

  • 深入谈谈MySQL中的自增主键

    MySQL的主键可以是自增的,那么如果在断电重启后新增的值还会延续断电前的自增值吗?自增值默认为1,那么可不可以改变呢?下面就说一下 MySQL的自增值. 特点 保存策略 1.如果存储引擎是 MyISAM,那么这个自增值是存储在数据文件中的: 2.如果是 InnoDB引擎,1)在 5.6之前是存储在内存中,没有持久化,在重启后会去找最大的键值,举个例子,如果一个表当前数据行里最大 id是10,AUTO_INCREMENT=11.这时候,我们删除 id=10 的行,AUTO_INCREMENT 还

  • 详解MySQL自增主键的实现

    目录 一.自增值保存在哪儿? 二.自增值修改机制 三.自增值的修改时机 四.自增锁的优化 五.自增主键用完了 一.自增值保存在哪儿? 不同的引擎对于自增值的保存策略不同 1.MyISAM引擎的自增值保存在数据文件中 2.InnoDB引擎的自增值,在MySQL5.7及之前的版本,自增值保存在内存里,并没有持久化.每次重启后,第一次打开表的时候,都会去找自增值的最大值max(id),然后将max(id)+步长作为这个表当前的自增值 select max(ai_col) from table_name

  • MySQL中的主键自增机制详情

    目录 主键自增 自增主键保存在哪里 自增值修改机制 自增值的修改时机 如何修改自增主键值 主键自增 MySQL 提供了主键自增机制 AUTO_INCREMENT. 对主键使用, 保证了主键的唯一性. 注意:自增长必须与主键字段配合使用. 默认的主键的起始值为 1, 每次增量为 1, 也可以手动指定其自增起始值 auto_increment_offset 和自增步长 auto_increment_increment. -- 设置主键自增 CREATE TABLE USER( id INT UNSI

  • MySQL的自增ID(主键) 用完了的解决方法

    在 MySQL 中用很多类型的自增 ID,每个自增 ID 都设置了初始值.一般情况下初始值都是从 0 开始,然后按照一定的步长增加(一般是自增 1).一般情况下,我们都是用int(11)来作为数据表的自增 ID,在 MySQL 中只要定义了这个数的字节长度,那么就会有上限. MySQL的自增ID(主键) 用完了,怎么办? 如果用 int unsigned (int,4个字节 ), 我们可以算下最大当前声明的自增ID最大是多少,由于这里定义的是 int unsigned,所以最大可以达到2的32幂

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

    目录 1. 为什么不用 UUID 2. 主键自增的问题 2.1 数据插入的三种形式 2.2 innodb_autoinc_lock_mode 2.3 实践 3. 小结 在上篇文章中,松哥和小伙伴们分享了 MySQL 的聚簇索引,也顺便和小伙伴们分析了为什么在 MySQL 中主键不应该使用随机字符串.但是主键不用随机字符串用什么?主键自增?主键自增就是最佳方案吗?有没有其他坑?今天我们就来讨论下这个话题. 1. 为什么不用 UUID 经过上篇文章的介绍,我们知道在 MySQL 中,主键索引就是聚簇

  • Mysql主键UUID和自增主键的区别及优劣分析

    引言 之前有段时间用postgresql 数据库,在上云之后,从自增主键变为uuid,感觉uuid全球唯一,很方便. 最近用mysql,发现mysql主键都是选择自增主键,仔细比较一下,为什么mysql选择自增主键,有什么不同. 在mysql5.0之前,如果是多个master复制的环境,无法用自增主键,因为可能重复.在5.0以及之后的版本通过配置自增偏移量解决了整个问题. 什么情况下我们希望用uuid 1. 避免重复,便于scale,这就是我们做cloud service的时候选择uuid的主要

  • mysql主键id的生成方式(自增、唯一不规则)

    目录 1.利用uuid函数生成唯一且不规则的主键id 2.id实现自动增长 1.利用uuid函数生成唯一且不规则的主键id sql: CREATE TABLE `test` ( `id` varchar(100) COLLATE utf8_estonian_ci NOT NULL COMMENT '唯一不重复', `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `update_time` timestamp NOT NULL

  • Mybatis-plus实现主键自增和自动注入时间的示例代码

    mybatis-plus依赖导入 <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.3.2</version> </dependency> 建议使用3.3.0后的版本. 导入mybatis-plus就不用导入mybatis了,冲突! 连接数据库 sp

  • Springboot+MybatisPlus+Oracle实现主键自增的示例代码

    上周周一,本来刚过完周末,高高兴兴,老大突然安排了个活,要在一天内把项目的MySQL数据库换成Oracle数据库,我们都知道这是不可能完成的任务,但是,秉承着"没有困难的工作,只有不努力的打工人"的精神,我们马上投入了工作,第一步当然是先配置数据库.oracle建表,这个解决调试了一上午,然后下午卡到oracle主键了,所有人网上找方法,一直到第二天凌晨3点半都还没解决,网上方法很多,试了好多都不管用,终于第二天才找到了满足的方法. 废话不多说,下面贴出. application.ym

  • 浅析MySQL 主键使用数字还是uuid查询快

    在实际开发中mysql的主键不能重复,可能会采用主键自增,为了防止主键重复也可能会采取雪花算法之类的算法保证,这两种主键保存的都是number类型 但是实际开发中可能会生成uuid作为主键那么疑问来了,到底哪种主键的效率高呢? 下面由测试来验证: 1.首先我们先创建一个表,用存储过程生成100w条数据然后分析: 创建表: CREATE TABLE `my_tables` ( `id` VARCHAR(32) NOT NULL , `name` varchar(32) DEFAULT NULL,

  • Oracle表字段的增删改、表的重命名及主键的增删改

    一.表字段的增删改: 添加字段的语法:alter table tablename add (column datatype [default value][null/not null],-.); 修改字段的语法:alter table tablename modify (column datatype [default value][null/not null],-.); 删除字段的语法:alter table tablename drop (column); 注:添加.修改.删除多列的话,用逗

  • Mysql主键和唯一键的区别点总结

    什么是主键? 主键是表中唯一标识该表中每个元组(行)的列.主键对表实施完整性约束.表中只允许使用一个主键.主键不接受任何重复值和空值.表中的主键值很少更改,因此在选择主键是需要小心,要选择很少发生更改的地方.一个表的主键可以被另一个表的外键引用. 为了更好地理解主键,我们创建一个名为Student的表,它具有roll_number.name.batch.phone_number.citizen_id等属性. 在上面的示例中,roll_number属性永远不能具有相同的NULL值,因为在每个大学中

  • django自定义非主键自增字段类型详解(auto increment field)

    1.django自定义字段类型,实现非主键字段的自增 # -*- encoding: utf-8 -*- from django.db.models.fields import Field, IntegerField from django.core import checks, exceptions from django.utils.translation import ugettext_lazy as _ class AutoIncreField(Field): description =

随机推荐