最新要闻
- 端午情怀“艾”在心间 天天微资讯
- 22岁小伙挑选私人飞机 预算5.5亿元 马斯克看了都害怕 世界观天下
- 全球看热讯:女生考前3天连续失眠 一查684分落泪:激动到不行 网友祝贺
- Intel Arc显卡为Linux推送新驱动:《CS:GO》帧数提升11%-全球快报
- 首发骁龙8 Gen3!小米14系列代号出炉:后稷、神农
- 惊险!马路中间悬挂一根电缆 摩托车骑手经过刚好被套住脖子
- 女模砸评委后续:正面照曝光,疑全部模特均被潜规则,比赛很公平-全球快播报
- 至少涨价1500元该买还是买!iPhone 15备货量曝光:苹果出手近亿台-天天热推荐
- 【天天播资讯】19岁少年为救陌生姐姐放弃考试 网友点赞:换你会做吗?
- 【播资讯】特斯拉L3能力藏不住了!无需手扶方向盘:966公里0提醒接管
- 当前热议!今天周日调休上班!iPhone闹钟没响用户被坑:苹果本土化服务为啥不行?
- 广东高考今日放榜 26日17时前可提出复查分数申请_全球头条
- 焦点信息:联通2000M宽带有多快?中国联通小姐姐:下200分钟4K电影仅78秒
- 48GB内存笔记本正式上市 插满可扩至96GB
- 清华大学校长呼吁本科毕业生:不要过度关注个人利益 会烦恼焦虑 世界即时看
- 为什么119灭火不直接对准火源射水?消防员专业揭秘 全球关注
手机
iphone11大小尺寸是多少?苹果iPhone11和iPhone13的区别是什么?
警方通报辅警执法直播中被撞飞:犯罪嫌疑人已投案
- iphone11大小尺寸是多少?苹果iPhone11和iPhone13的区别是什么?
- 警方通报辅警执法直播中被撞飞:犯罪嫌疑人已投案
- 男子被关545天申国赔:获赔18万多 驳回精神抚慰金
- 3天内26名本土感染者,辽宁确诊人数已超安徽
- 广西柳州一男子因纠纷杀害三人后自首
- 洱海坠机4名机组人员被批准为烈士 数千干部群众悼念
家电
面试官:MySQL 自增主键一定是连续的吗?大部分人都会答错!
测试环境:
MySQL版本:8.0
(资料图片仅供参考)
数据库表:T (主键id,唯一索引c,普通字段d)
如果你的业务设计依赖于自增主键的连续性,这个设计假设自增主键是连续的。但实际上,这样的假设是错的,因为自增主键不能保证连续递增。
推荐一个开源免费的 Spring Boot 实战项目:
https://github.com/javastacks/spring-boot-best-practice
一、自增值的属性特征:
1. 自增主键值是存储在哪的?
MySQL5.7版本
在 MySQL 5.7 及之前的版本,自增值保存在内存里,并没有持久化。每次重启后,第一次打开表的时候,都会去找自增值的最大值 max(id)
,然后将 max(id)+1
作为这个表当前的自增值。
MySQL8.0之后版本
在 MySQL 8.0 版本,将自增值的变更记录在了 redo log
中,重启的时候依靠 redo log
恢复重启之前的值。
可以通过看表详情查看当前自增值,以及查看表参数详情AUTO_INCREMENT
值(AUTO_INCREMENT
就是当前数据表的自增值)
2. 自增主键值的修改机制?
在表t中,我定义了主键id为自增值,在插入一行数据的时候,自增值的行为如下:
- 如果插入数据时 id 字段指定为 0、null 或未指定值,那么就把这个表当前的
AUTO_INCREMENT
值填到自增字段; - 如果插入数据时 id 字段指定了具体的值,就直接使用语句里指定的值。
根据要插入的值和当前自增值的大小关系,自增值的变更结果也会有所不同。假设,某次要插入的值是 X,当前的自增值是 Y。
- 如果 X
- 如果 X≥Y,就需要把当前自增值修改为新的自增值。
二、新增语句自增主键是如何变化的:
我们执行以下SQL语句,来观察自增主键是如何进行变化的
insert into t values(null, 1, 1);
流程图如下所示
流程步骤:
- AUTO_INCREMENT=1(表示下一次插入数据时,如果需要自动生成自增值,会生成 id=1。)
- insert into t values(null, 1, 1)(执行器调用 InnoDB 引擎接口写入一行,传入的这一行的值是 (0,1,1))
- get AUTO_INCREMENT=1(InnoDB 发现用户没有指定自增 id 的值,获取表 t 当前的自增值 1 )
- AUTO_INCREMENT=2 insert into t values(1, 1, 1)(将传入的行的值改成 (1,1,1),并把自增值改为2)
- insert (1,1,1)执行插入操作,至此流程结束
大家可以发现,在这个流程当中是先进行自增值的+1,在进行新增语句的执行的。大家可以发现这个操作并没有进行原子操作,如果SQL语句执行失败,那么自增是不是就不会连续了呢?
三、自增主键值不连续情况:(唯一主键冲突)
当我执行以下SQL语句时
insert into t values(null, 1, 1);
第一次我们可以进行新增成功,根据自增值的修改机制。如果插入数据时 id 字段指定为 0、null 或未指定值,那么就把这个表当前的 AUTO_INCREMENT
值填到自增字段;
当我们第二次在执行以下SQL语句时,就会出现错误。因为我们表中c字段是唯一索引,会出现Duplicate key error
错误导致新增失败。
例如:
- AUTO_INCREMENT=2(表示下一次插入数据时,如果需要自动生成自增值,会生成 id=2。)
- insert into t values(null, 1, 1)(执行器调用 InnoDB 引擎接口写入一行,传入的这一行的值是 (0,1,1))
- get AUTO_INCREMENT=2(InnoDB 发现用户没有指定自增 id 的值,获取表 t 当前的自增值 2 )
- AUTO_INCREMENT=3 insert into t values(2, 1, 1)(将传入的行的值改成 (2,1,1),并把自增值改为3)
- insert (2,1,1)执行插入操作,由于已经存在 c=1 的记录,所以报
Duplicate key error
,语句返回。
可以看到,这个表的自增值改成 3,是在真正执行插入数据的操作之前。这个语句真正执行的时候,因为碰到唯一键 c 冲突,所以 id=2 这一行并没有插入成功,但也没有将自增值再改回去。所以,在这之后,再插入新的数据行时,拿到的自增 id 就是 3。也就是说,出现了自增主键不连续的情况。
四、自增主键值不连续情况:(事务回滚)
其实事务回滚原理也和上面一样,都是因为异常导致新增失败,但是自增值没有进行回退。
五、自增主键值不连续情况:(批量插入)
批量插入数据的语句,MySQL 有一个批量申请自增 id 的策略:
- 语句执行过程中,第一次申请自增 id,会分配 1 个;
- 1 个用完以后,这个语句第二次申请自增 id,会分配 2 个;
- 2 个用完以后,还是这个语句, 第三次申请自增 id,会分配 4 个;
- 依此类推,同一个语句去申请自增 id,每次申请到的自增 id 个数都是上一次的两倍。
执行以下SQL语句(在表t中先新增了4条数据,在创建表tt把表t数据进行批量新增)
insert into t values(null, 1,1);insert into t values(null, 2,2);insert into t values(null, 3,3);insert into t values(null, 4,4);create table tt like t;insert into tt(c,d) select c,d from t;insert into tt values(null, 5,5);
第一次申请到了 id=1,第二次被分配了 id=2 和 id=3, 第三次被分配到 id=4 到 id=7。当我们再执行 insert into t2 values(null, 5,5)
,实际上插入的数据就是(8,5,5),出现了自增主键不连续的情况。
六、自增主键值的优化
1.什么是自增锁
自增锁是一种比拟非凡的表级锁。并且在事务向蕴含了 AUTO_INCREMENT
列的表中新增数据时就会去持有自增锁,假如事务 A 正在做这个操作,如果另一个事务 B 尝试执行 INSERT语句,事务 B 会被阻塞住,直到事务 A 开释自增锁。
2.自增锁有哪些优化
在 MySQL 5.0 版本的时候,自增锁的范围是语句级别。也就是说,如果一个语句申请了一个表自增锁,这个锁会等语句执行结束以后才释放。显然,这样设计会影响并发度。在MySQL 5.1.22 版本引入了一个新策略,新增参数 innodb_autoinc_lock_mode
,默认值是 1。
传统模式(Traditional)
这个参数的值被设置为 0 时,表示采用之前 MySQL 5.0 版本的策略,即语句执行结束后才释放锁;
传统模式他可以保证数据一致性,但是如果有多个事务并发的执行 INSERT 操作,AUTO-INC
的存在会使得 MySQL 的性能略有降落,因为同时只能执行一条 INSERT 语句。
间断模式(Consecutive)
这个参数的值被设置为 1 时:普通 insert 语句,自增锁在申请之后就马上释放;类似 insert … select
这样的批量插入数据的语句,自增锁还是要等语句结束后才被释放;
间断模式他可以保证数据一致性,但是如果有多个事务并发的执行 INSERT 批量操作时,就会进行锁等待状态。如果我们业务插入数据量很大时,这个时候MySQL的性能就会大大下降。
穿插模式(Interleaved)
这个参数的值被设置为 2 时,所有的申请自增主键的动作都是申请后就释放锁。
穿插模式他没有进行任何的上锁设置。在一定情况下是保证了MySQL的性能,但是他无法保证数据的一致性。如果我们在穿插模式下进行主从复制时,如果你的binlog格式不是row格式,主从复制就会出现不一致。
七、MySQL8.0做了哪些优化
在MySQL8.0之后版本,已经默认设置为 innodb_autoinc_lock_mode=2
, binlog_format=row.
。这样更有利与我们在 insert … select
这种批量插入数据的场景时,既能提升并发性,又不会出现数据一致性问题。
版权声明:本文为CSDN博主「又 欠」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。原文链接:https://blog.csdn.net/qq_48157004/article/details/128356734
近期热文推荐:
1.1,000+ 道 Java面试题及答案整理(2022最新版)
2.劲爆!Java 协程要来了。。。
3.Spring Boot 2.x 教程,太全了!
4.别再写满屏的爆爆爆炸类了,试试装饰器模式,这才是优雅的方式!!
5.《Java开发手册(嵩山版)》最新发布,速速下载!
觉得不错,别忘了随手点赞+转发哦!
关键词:
面试官:MySQL 自增主键一定是连续的吗?大部分人都会答错!
端午情怀“艾”在心间 天天微资讯
22岁小伙挑选私人飞机 预算5.5亿元 马斯克看了都害怕 世界观天下
全球看热讯:女生考前3天连续失眠 一查684分落泪:激动到不行 网友祝贺
Intel Arc显卡为Linux推送新驱动:《CS:GO》帧数提升11%-全球快报
首发骁龙8 Gen3!小米14系列代号出炉:后稷、神农
惊险!马路中间悬挂一根电缆 摩托车骑手经过刚好被套住脖子
一天吃透SpringCloud面试八股文
全球速递!在Linux下搭建Git服务器
【技术积累】算法中的贪心算法【三】_环球速读
女模砸评委后续:正面照曝光,疑全部模特均被潜规则,比赛很公平-全球快播报
至少涨价1500元该买还是买!iPhone 15备货量曝光:苹果出手近亿台-天天热推荐
【天天播资讯】19岁少年为救陌生姐姐放弃考试 网友点赞:换你会做吗?
【播资讯】特斯拉L3能力藏不住了!无需手扶方向盘:966公里0提醒接管
当前热议!今天周日调休上班!iPhone闹钟没响用户被坑:苹果本土化服务为啥不行?
广东高考今日放榜 26日17时前可提出复查分数申请_全球头条
焦点信息:联通2000M宽带有多快?中国联通小姐姐:下200分钟4K电影仅78秒
48GB内存笔记本正式上市 插满可扩至96GB
清华大学校长呼吁本科毕业生:不要过度关注个人利益 会烦恼焦虑 世界即时看
为什么119灭火不直接对准火源射水?消防员专业揭秘 全球关注
当前动态:《上古卷轴5:天际》4代湮灭Mod实机演示
『题解』BZOJ3462 DZY Loves Math II
读发布!设计与部署稳定的分布式系统(第2版)笔记11_无限长的结果集
10万元到1万元 激光电视降价为了什么
全球新消息丨ASML:芯片供应链脱钩自主生产不可能 美国日本也不例外
全球速讯:k8s 深入篇———— pod 深入实战[七]
CAP定理
【环球速看料】强化学习从基础到进阶-常见问题和面试必知必答[4]::深度Q网络-DQN、double DQN、经验回放、rainbow、分布式DQN
《消失的她》票房破5亿:打破44项影史纪录 端午档最强国产悬疑电影诞生 全球最新
五年来最火端午假期正式结束!这些城市最热门 你去了吗?
鸿海加快iPhone 15在印度量产:与中国大陆时间差将缩减至一个月-天天速递
400万播放量 救不了东北偶像
标压i7+32GB仅2999元 这款迷你主机能入手吗?实测来了
党的最大优势是党执政后的最大危险是_党执政后的最大危险是
全球新消息丨韩国yg公司旗下艺人_韩国yg公司
每日动态!使用libavcodec将mp3音频文件解码为pcm音频采样数据【[mp3float @ 0x561c1ec49940] Header missing
小试Blazor——实现Ant Design Blazor动态表单
焦点快看:【解决方法】按键精灵 实现 狂野飙车9 自动领取每日广告卡牌包 (1)
k360次列车硬卧分布图(k360次列车)|天天时讯
安徽唯一盲人考生 高考成绩出来了:311分
因脑溢血开颅3次的少年高考445分 母亲:努力有了结果
k8s 深入篇———— pod 实战[六]
garage两种读音_garage怎么读有几种读法是对的
观焦点:女子醉酒吐车上 20元都不肯赔 司机怒骂不要脸:网友力挺
环球快报:再获11亿美元投资:蔚来“长期主义”的底气
距离今年“召回王”又进一步 本田海外召回超百万辆汽车
法媒:罗马准备听取对贝洛蒂的报价,意甲英超西甲多队有意
腾讯两大国民APP账号又打通了!QQ悄然支持微信登陆 有了微信为啥仍离不开QQ?
2023年端午小长假收官:1.06亿人次出游 满血超越2019-当前热讯
【世界独家】word安装(ppt、excel),以及word的常用设置
【当前独家】Blazor开发小游戏?趁热打铁上!!!
当前热门:火狐主页diy设置以及常用的扩展
DZY Loves Math|全球即时看
女生发烧考出618分:一蹦三尺高 挨个房间报喜
每日消息!ChatGPT漏洞 讲故事送Window11激活Key!
世界快播:刘慈欣谈ChatGPT:人类的无能反而是人类最后的屏障
今日热议:关于高考志愿填报,这些热点问题需要关注
缓存一致性如何保障
推出长达7年:任天堂股东质疑Switch已逼近极限
实时焦点:苹果前总监炮轰App Store存在灰色地带 标准随心所欲
三乙醇胺油酸皂商品报价动态(2023-06-24)
救命一声吼!山洪暴发女子大喊提醒救下多名游客 世界讯息
索尼PS5串流掌机价格曝光:最高2100元能接受么?
全球视点!泰坦号观光潜艇“打破常规留名后世”,老板一语成谶片受热议
DLang 与 C 语言交互
Apollo2.1.0+Springboot使用OpenApI
邮箱:微信企业域名邮箱给gmail或hotmail等域外邮箱发邮件被退回问题如何解决? 环球观焦点
今日热门!Stable Diffusion模型发布新版本:生成图像以假乱真
钻石价格,突发“跳水”!未来还会更便宜?
使用python发送sip协议的OPTIONS 热门
k8s 深入篇———— k8s 的pod[五]-全球播资讯
8. Java-AOP 面向切面编程
文心一言 VS 讯飞星火 VS chatgpt (46)-- 算法导论6.1 4题|全球热点评
泰坦号事故后:加拿大将展开事故调查
车主自曝差点被闷死在特斯拉Model X里 车门锁死 原因揭晓
蔡徐坤巡演新加坡站开票 《Hug me(remix版)》同日上线
来一打自建IP Proxy玩玩之Majora
kafka学习之五_多个磁盘的性能验证 世界快看点
Go——常用函数
每日速递:卷福的十年同学会
每日简讯:4亿票房端午黑马:《消失的她》官宣海外定档
冲入球场拥抱梅西小伙获释后道歉:我真不是没素质的人
世界观天下!新会绿美生态园票价(新会绿美生态园票价多少钱)
腾讯两大国民APP账号又打通了?QQ悄然支持微信登陆 环球精选
环球快报:调查称安卓更易上手:iPhone用户遇到问题概率高出58%
java 异常处理,事务管理,事务共用,事务传递 天天微头条
Go-闭包和defer|最新资讯
环球热资讯!Zen3清库存?突然冒出个很特别的锐龙5 5700
虚幻5打造!腾讯动漫《斗罗大陆2》今日两集首播 霍雨浩初入星斗大森林|每日观察
“空中出租车”亮相巴黎航展:可降落空间直径仅需15米-环球速递
古力娜扎曾遭换脸视频威胁勒索:不给钱就毁了你!
男生单曲循环《好运来》查出593分大哭:比平时多出50分 超常发挥
全球钻石价格较峰值暴跌18%:人造钻石市场规模不断扩大_全球视点
环球最新:甲亢遇到异食癖:法国男子一顿吃15人份 急了还吃石头木塞
全球热点评!公鸡突然从背后“偷袭”萌娃 飞起两脚踹倒在地 第二天端午节就被炖了
泰国和美国两地大量鱼类死亡 或与海洋升温有关 快看点
【技术积累】C语言中的指针【一】_世界百事通
Go-自定义数据类型(函数类型)详解
行业风险管理需求强烈
焦点信息:AMD RX 7800被逼急了!硬塞进去个“大胖子”