MySQL主键自增值为什么有空洞？

　　GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。
　　1。场景准备2。开始测试3。问题分析4。问题拓展
　　本文在测试insert、insertignore、replaceinto三种数据插入方式的时候，发现插入数据的时候在表内存在带有唯一特性的值重复的情况下三种语句的处理方式。最终发现了MySQL主键自增值空洞了1。场景准备
　　测试场景为MySQL8。0：主键重复场景唯一键重复场景
　　1、建表，包含主键及唯一约束CREATETABLEt1（idint（11）NOTNULLautoincrement，c1varchar（64）DEFAULTNULL，c2int（11）DEFAULTNULL，PRIMARYKEY（id），UNIQUEKEYukc1（c1））ENGINEInnoDBDEFAULTCHARSETutf8；
　　2、写入初始测试数据insertintot1（c1，c2）values（a，1），（b，2），（c，3）；mysqlselectfromt1；idc1c21a12b23c33rowsinset（0。00sec）2。开始测试insertinto测试主键重复mysqlinsertintot1values（1，aaa，111）；ERROR1062（23000）：Duplicateentry1forkeyt1。PRIMARY测试唯一键重复mysqlinsertintot1（c1，c2）values（a，4）；ERROR1062（23000）：Duplicateentryaforkeyt1。ukc1insertignoreinto
　　insert方式插入数据在处理过程中发生主键传统等错误时候，语句会被终止，并告知错误的原因。而使用insertignore的方式进行数据插入，则会忽略插入错误的行继续插入没有问题的行记录，最终以warning进行提示。测试主键重复mysqlinsertignoreintot1values（1，aaa，111）；QueryOK，0rowsaffected，1warning（0。00sec）LevelCodeMessageWarning1062Duplicateentry1forkeyt1。PRIMARY1rowinset（0。01sec）测试唯一键重复mysqlinsertignoreintot1（c1，c2）values（a，4）；QueryOK，0rowsaffected，1warning（0。00sec）LevelCodeMessageWarning1062Duplicateentryaforkeyt1。ukc11rowinset（0。00sec）
　　在测试过程中惊奇地发现测试表中的主键自增列发生了改变，经过之前的操作已经变成了7：mysqlshowcreatetablet1G1。rowTable：t1CreateTable：CREATETABLEt1（idintNOTNULLAUTOINCREMENT，c1varchar（64）DEFAULTNULL，c2intDEFAULTNULL，PRIMARYKEY（id），UNIQUEKEYukc1（c1））ENGINEInnoDBAUTOINCREMENT7DEFAULTCHARSETutf8mb31rowinset（0。00sec）可是表的行数据并没有增加mysqlselectfromt1；idc1c21a12b23c33rowsinset（0。00sec）新写入一条数据后，自增id变成7mysqlinsertintot1（c1，c2）values（d，4）；QueryOK，1rowaffected（0。00sec）mysqlselectfromt1；idc1c21a12b23c37d44rowsinset（0。00sec）replaceinto
　　最后，replaceinto的方式导致如果插入数据是原值的情况，然后主键冲突，就对该主键的内容进行替换，如果唯一键冲突，唯一值所在行就会删除，重新插入新的行，如果都不冲突则正常插入数据。测试主键重复mysqlreplaceintot1values（1，aaa，111）；QueryOK，2rowsaffected（0。00sec）mysqlselectfromt1；idc1c21aaa1112b23c37d44rowsinset（0。00sec）测试唯一键重复mysqlreplaceintot1（c1，c2）values（b，4）；QueryOK，2rowsaffected（0。01sec）mysqlselectfromt1；idc1c21aaa1113c37d48b44rowsinset（0。00sec）
　　上文测试了三种插入数据的方式，可是测试过程中发现插入失败的时候，自增列的自增值居然变大了。3。问题分析
　　为了更好地理解，首先让我们具体认识一下AUTOINCREMENT属性在不同的存储引擎当中，其自增值的保存策略有所不同：MyISAM引擎的自增值是保存在数据文件中的。InnoDB引擎的自增值，其实是保存在了内存里，并且到了MySQL8。0版本后，将自增值的变更记录在了redolog中，当MySQL发生重启的时候依靠redolog恢复重启之前的自增值。在此前，现在该表的自增值是7，重启后又变成4了。
　　可是理解了这个并不能马上理解现在的这个问题，我们知道当数据进行数据插入的时候，如果插入的数据中自增列不指定其值的时候，该列就会以当前自增值作为其值，如果指定其值就会插入指定的值，当然也有满足唯一的原则，同时插入指定值大于自增值时，自增值也会随之改变。而自增值使用的算法是以autoincrementoffset参数决定开始，以autoincrementincrement决定步长来实现的，默认情况都是1：autoincrementoffset11rowinset（0。00sec）autoincrementincrement11rowinset（0。00sec）
　　那么，为什么会出现插入数据未成功，自增值却变大了的情况呢？原因很简单，用插入数据的流程来进行分析：
　　因为自增值的保存是在插入数据真正执行前完成的，因此就会出现这种问题了。
　　这个时候有人就会想了，可以把AUTOINCREMENT值改回去吗？简单测试一下：mysqlshowcreatetablet1G1。rowTable：t1CreateTable：CREATETABLEt1（idintNOTNULLAUTOINCREMENT，c1varchar（64）DEFAULTNULL，c2intDEFAULTNULL，PRIMARYKEY（id），UNIQUEKEYukc1（c1））ENGINEInnoDBAUTOINCREMENT9DEFAULTCHARSETutf8mb31rowinset（0。00sec）mysqlselectfromt1；idc1c21a13c37d48b44rowsinset（0。00sec）自增值修改为15mysqlaltertablet1autoincrement15；QueryOK，0rowsaffected（0。01sec）Records：0Duplicates：0Warnings：0修改成功mysqlshowcreatetablet1G1。rowTable：t1CreateTable：CREATETABLEt1（idintNOTNULLAUTOINCREMENT，c1varchar（64）DEFAULTNULL，c2intDEFAULTNULL，PRIMARYKEY（id），UNIQUEKEYukc1（c1））ENGINEInnoDBAUTOINCREMENT15DEFAULTCHARSETutf8mb31rowinset（0。00sec）未插入任何值，修改回去，修改成功mysqlaltertablet1autoincrement9；QueryOK，0rowsaffected（0。02sec）Records：0Duplicates：0Warnings：0mysqlshowcreatetablet1G1。rowTable：t1CreateTable：CREATETABLEt1（idintNOTNULLAUTOINCREMENT，c1varchar（64）DEFAULTNULL，c2intDEFAULTNULL，PRIMARYKEY（id），UNIQUEKEYukc1（c1））ENGINEInnoDBAUTOINCREMENT9DEFAULTCHARSETutf8mb31rowinset（0。00sec）修改回自增中间的值mysqlaltertablet1autoincrement5；QueryOK，0rowsaffected（0。01sec）Records：0Duplicates：0Warnings：0不会报错但无法修改mysqlshowcreatetablet1G1。rowTable：t1CreateTable：CREATETABLEt1（idintNOTNULLAUTOINCREMENT，c1varchar（64）DEFAULTNULL，c2intDEFAULTNULL，PRIMARYKEY（id），UNIQUEKEYukc1（c1））ENGINEInnoDBAUTOINCREMENT9DEFAULTCHARSETutf8mb31rowinset（0。00sec）
　　显然，如果自增值往大的方向修改是没有问题的，但如果往小的修改就要看目前数据库插入的值是否会将修改后的自增值卡在中间，如果出现这种情况是没办法改回去的，原因显而易见，自增属性与主键配套使用，如果现在表里id4和id6之间差了个5的值，将自增值改回5，当插入数据时，自增值就会插入5的值并且把自增值加1，问题就出现了，此时自增值再进行插入就违背了唯一的原则了4。问题拓展
　　在生产环境中还存在很多类似的问题，如：目前的插入值为8，自增值为9mysqlselectfromt1；idc1c21a13c37d48b44rowsinset（0。00sec）插入数据相当于（9，t1，1）mysqlinsertintot1values（null，t1，1）；QueryOK，1rowaffected（0。00sec）开启事务QueryOK，0rowsaffected（0。00sec）插入数据相当于（10，t2，2）mysqlinsertintot1values（null，t2，2）；QueryOK，1rowaffected（0。00sec）事务回滚QueryOK，0rowsaffected（0。00sec）插入数据相当于（11，t3，3）mysqlinsertintot1values（null，t3，3）；QueryOK，1rowaffected（0。01sec）mysqlselectfromt1；idc1c21a13c37d48b49t1111t336rowsinset（0。00sec）
　　在插入过程中，开启了一个事务，在插入的时候发生了事务的回滚，当回滚后再次插入数据，发现自增值又出现了空洞，那么问题又来了，为什么在插入数据的时候发生了回滚，数据回滚了，自增值却没有回滚呢？为了更直观，继续测试，假设有两个事务。
　　测试前数据：mysqlselectfromt1；idc1c21a13c37d48b49t1111t3313t437rowsinset（0。00sec）
　　进行测试：
　　session1
　　session2
　　
　　insertintot1values（null，s1，1）；
　　insertintot1values（null，s2，2）；
　　
　　
　　测试后数据：mysqlselectfromt1；idc1c21a13c37d48b49t1111t3313t4315s228rowsinset（0。00sec）
　　发现还是空洞了，而且此时答案也十分清楚了，在不同事务在进行写入操作的时候申请自增值，为了避免两个事务申请到相同的自增值，所以需要对其加锁，按照一定顺序进行申请自增值。根据前面的例子来看：首先两个session都开启了事务，session1前面的是id14的自增值，session2则申请到id15的自增值接着当session2插入成功后提交了事务，而此时，session1插入成功或出现插入失败时进行了事务回滚
　　此时就出现了前面说到的问题了，没办法回滚，回滚就会出现自增值卡在中间的情况了，以后有机会再继续聊聊自增锁的问题。
　　EnjoyGreatSQL：）
　　《深入浅出MGR》视频课程
　　戳此小程序即可直达B站
　　https：www。bilibili。commedialistplay1363850082？businessspacecollectionbusinessid343928desc0
　　文章推荐：MySQL8。0有趣的新特性：CHECK约束MySQL启停过程了解一二技术分享微服务架构的数据库为什么喜欢分库分表？MySQL内存管理机制浅析技术分析浅析MySQL与ElasticSearch的组合使用
　　GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。
　　Gitee：
　　https：gitee。comGreatSQLGreatSQL
　　GitHub：
　　https：github。comGreatSQLGreatSQLBilibili：
　　https：space。bilibili。com1363850082video
　　微信QQ群：
　　可扫码添加GreatSQL社区助手微信好友，发送验证信息加群加入GreatSQLMGR交流微信群，亦可直接扫码加入GreatSQLMGR交流QQ群。