MySQL主键自增会遇到的坑怎么解决

1. 为什么不用 UUID

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

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

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

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

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

2. 主键自增的问题

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

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

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

为了解决这个问题，我们就需要选择适合自己的 innodb_autoinc_lock_mode。

2.1 数据插入的三种形式

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

• insert into user(name) values('j*aboy') 或者 replace into user(name) values('j*aboy') ，这种没有嵌套子查询并且能够确定具体插入多少行的插入叫做 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,'j*aboy'),(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: 这个表示 interle*ed，这种情况下不存在 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('j*aboy',uuid())，那么最终记录到 binlog 中的 SQL 是 insert into user(username,uuid) values('j*aboy',‘0212cfa0-de06-11ed-a026-0242ac110004’)。

• statement：binlog 中记录的就是原始的 SQL 了，以 row 中的为例，最终 binlog 中记录的就是 insert into user(username,uuid) values('j*aboy',uuid())。

• mixed：在这种模式下，MySQL 会根据具体的 SQL 语句来决定日志的形式，也就是在 statement 和 row 之间选择一种。

  Mootion

  Mootion是一个革命性的3D动画创作平台，利用AI技术来简化和加速3D动画的制作过程。

  232 查看详情

对于这三种不同的模式，很明显，在主从复制的时候，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,'j*aboy'),(null,'江南一点雨'),(3,'www.j*aboy.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 开始了。

以上就是MySQL主键自增会遇到的坑怎么解决的详细内容，更多请关注其它相关文章！

# 主键  # 镜像  # MySQL  # 电脑版商城网站建设  # 网站排名优化夆还是宙z斯推选  # 沈阳seo软件案例公司  # 五金网站推广维护  # 网站自然排名优化练习  # 吉林网站优化排名定制  # 360营销推广怎么做  # 信管家网站TDK优化案例分析  # 苏州建设银行网站  # 怎样欣赏视频网站推广  # 三种  # 可以看到  # 在这种  # 值为  # 都是  # 的是  # 自己的  # 就会 

相关栏目： 【 Google疑问12 】 【 Facebook疑问10 】 【 优化推广96088 】 【 技术知识133117 】 【 IDC资讯59369 】 【 网络运营7196 】 【 IT资讯61894 】

相关推荐： sublime怎么快速在浏览器中预览HTML_sublime配置View in Browser教程  163邮箱登录入口官网 163.com邮箱登录入口  快递优选如何查优选物流_快递优选专属物流渠道查询与配送时效  花生壳内网映射新方案  申通快件单号查询平台 申通包裹物流动态跟踪  Flexbox布局实践：实现底部页脚与顶部粘性导航条的完美结合  《绝区零》2.3前瞻|直播|内容介绍  《战地6》反作弊已成功拦截240万次作弊 发售第一周98%比赛没有作弊  C++ bind函数使用教程_C++参数绑定与函数适配器的应用  如何使用 composer 和 aop-php 实现 AOP 编程？  PHP安全加载非公开目录图片与动态内容类型处理指南  C++ static关键字作用_C++静态成员变量与静态函数  《漫蛙manwa2》防走失网页版链接2025  《花瓣》创建专辑方法  抖音赚钱快速入门_新手必看的抖音赚钱步骤  word怎么将图片设置为页面背景并不影响打印_Word图片背景设置方法  《火花chat》搜索好友方法  mysql如何管理数据库账户_mysql数据库账户管理技巧  高德地图导航路线偏差报警频繁怎么办 高德地图路线偏差修复与优化方法  曝《丝之歌》DLC有望开发！开发商还有神秘新企划  Win10如何关闭开机锁屏界面_Windows10跳过锁屏直接登录设置  《火影忍者：木叶高手》快速升级攻略  《雅迪智行》用手机开锁方法  《全民k歌》网页版最新登录入口一览  Leaflet地图弹出窗口图片动态显示：避免缺失图标的专业指南  oppo手机如何通过下拉通知栏截图_oppo手机通知栏快捷截图方法  Golang中的rune与byte类型区别是什么_Golang字符与字节处理详解  b站怎么设置动态仅粉丝可见_b站动态粉丝可见设置方法  电脑没有声音了怎么办 电脑声音问题的全面排查与修复指南【详解】  邦丰播放器频道搜索设置  《搜书吧》阅读书籍方法  《优志愿》修改手机号方法  优酷官网登录入口电脑版 优酷官网网址入口  使用Python和GBGB API高效抓取指定日期范围和赛道比赛结果教程  qq邮箱格式填写示例 qq邮箱标准填写规范  Excel如何制作月度销售统计图_Excel动态图表制作与控件应用  C++ cast类型转换总结_C++ reinterpret_cast与const_cast的使用  iPhone 13 Pro Max如何设置桌面小组件_iPhone 13 Pro Max小组件添加指南  wps文字怎么设置文字环绕图片的方式_wps文字如何设置文字环绕图片方式  windows10怎么设置电源按钮_windows10按下电源键功能修改  iPhone16Plus参数配置如何调整声音_iPhone16Plus参数配置声音调整详细方法  毒蘑菇VOLUMESHADER_BM官网首页登录入口 毒蘑菇VOLUMESHADER_BM官网首页登录入口说明  苹果17 Pro如何启用分屏浏览_iPhone 17 Pro分屏浏览设置步骤  《崩坏：星穹铁道》3.6版本异相仲裁打法及配队推荐  掌握Go App Engine项目结构与GOPATH：包管理与导入实践  支付宝登录刷脸不是本人如何解决  获取WooCommerce产品在后台编辑页面的分类ID  J*aScript大数运算_BigInt使用指南  12306不能订票的时间段是固定的吗？ | 节假日购票时间有无变化  口腔诊所管理软件推荐 

 2023-05-30