专栏首页个人开发面试官:mysql如何重置自增id

面试官:mysql如何重置自增id

面试官:咱们聊聊mysql的自增id。mysql自增id给我们的自增主键定义带来了很大的方便,但是经常mysql的自增id会有不连续情况,能说说什么场景下mysql的id会产生不连续吗?

:我以一张表为例来解释一下,我先创建一张表zh_person,这张表包括4个字段,自增id,姓名name,性别sex和身份证号id_no,id_no上有唯一索引,sql如下

CREATE TABLE `zh_person` (
  `id` MEDIUMINT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(64) COLLATE utf8_bin NOT NULL,
  `sex` VARCHAR(1) COLLATE utf8_bin NOT NULL,
  `id_no` VARCHAR(64) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_no_unique` (`id_no`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

如果我在插入记录的时候,sql不带id的值,比如下面sql,id会自增

INSERT INTO zh_person(NAME, sex,id_no) VALUES('张三', '1','12345');

面试官:打断一下,如果sql指定了id的值,什么给值能保证id自增呢?

:如果id的值是0或者null的话,id也会自增的。如下面2个sql

INSERT INTO zh_person(id,NAME, sex,id_no) VALUES(NULL,'李四', '1', '12346');
INSERT INTO zh_person(id,NAME, sex,id_no) VALUES(0,'王五', '1', '12347');

如果上面的3个sql执行成功了,这时候3条记录id依次是1、2、3

如果这是我再插入一条,id_no赋值“'12347'”,这时候唯一键冲突,sql如下:

INSERT INTO zh_person(NAME, sex,id_no) VALUES('赵六', '1', '12347');

这时候虽然插入失败了,但是id的值还是增加了1,为什么这么说呢,我们修改上面的语句如下,插入成功后表里面虽然有4条记录,但是id是1、2、3、5

INSERT INTO zh_person(NAME, sex,id_no) VALUES('赵六', '1', '12348');

以上是id不连续的一种情况。

面试官:等一下,mysql的自增id在唯一索引冲突的时候为什么不会回滚回去呢?

:mysql在获取id时为了保证一致性,是加锁的,比如2个并发事务申请自增id,上面例子的情况,假如一个申请了4,一个申请了5,加入申请4的事务成功了,申请到5的事务唯一键冲突,这时候如果id回退到4,下一次插入必定是主键冲突。

面试官:那还有id不连续的原因吗,除了经常性的删除操作?

:执行insert into table select这种语句的时候,也会出现自增id不连续的情况,因为mysql申请批量id的策略是对于同一条sql中的申请id,第一次分配一个,如果第一次分配后这个sql还会来申请,就会给2个,一次类推,下一次总是上一次的2倍。

还是以上面的例子来说,上面的zh_person表中有4条数据,我们新建一张表zh_person2,然后执行插入语句,sql如下:

create table zh_person2 like zh_person;
INSERT INTO zh_person2(NAME, sex, id_no) SELECT NAME, sex, id_no FROM zh_person;

上面的insert语句有4条记录,第一次申请id时分配了1个,不够用,第二次分配了2个,还是不够用,第三次申请3个,够用了,这是zh_person2表的自增id已经是8了,所以我们执行如下sql,插入的记录,id是8

INSERT INTO zh_person2(NAME, sex,id_no) VALUES('马七', '1', '12349');

这时zh_person2表里面总共有5条记录,id是1、2、3、4、8

面试官:那id在mysql中是怎么存储的呢?

:您知道,mysql有2种主流存储引擎,MyISAM和InnoDB,MyISAM自增id存储在数据文件上,而InnoDB在mysql8.0之前存储在内存中,8.0之后存储在redolog里。

面试官:存储在内存中,那mysql 服务重启了怎么记录自增id呢?

:每次mysql重启都都会查找当前表的最大id值,然后加1存储到内存中作为当前id值

面试官:对这种自增id不连续的情况,对生产有什么影响吗?你有什么好的建议?

:大家都知道,mysql的主备同步是通过binlog来进行的,binlog的格式有3种,statement格式及记录sql,row格式即记录数据,还有一种是上面2个混合使用。如果使用statement格式来记录binlog,那在备库那儿执行的只是成功的sql,备库的表自增id值会跟主库不一致,这种情况还是非常危险的,如果我们用id来做一些业务上的查询,会查到不一样的结果。

为了应对这种情况,我建议把binlog记录为row格式,同时把系统参数

innodb_autoinc_lock_mode设置为2,这个参数默认是1

面试官:能讲一讲这个参数的含义吗?

:这个参数是记录申请id获取锁后释放锁的策略,如果设置为0,则申请id的语句结束后才释放锁;如果是1,则对单条insert语句,申请到id后马上释放,对批量插入语句像上面的例子,则是语句执行结束后释放;如果设置成2,则所有语句都是申请到id后马上释放,效率最高。

面试官:回到我最想问的问题,如果我有一张表,我想要把这种不连续的id改成连续,有什么方法吗?

:方法有3种

1)备份数据,然后truncate,然后把备份写回原表,相当于删除重建,因为truncate = drop + create

2)执行下面语句

ALTER  TABLE  `zh_user` DROP `id`;
ALTER  TABLE  `zh_user` ADD `id` MEDIUMINT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT FIRST;

3)执行下面语句

ALTER TABLE zh_user AUTO_INCREMENT=1

面试官:能说说这3种方法的区别吗?

:本质上是一样的,都是备份、删除、回写数据,针对刚刚的zh_person表,我们可以分别执行上面的语句,然后查询表的创建时间,发现每次时间都会改变

SELECT table_name,create_time FROM information_schema.TABLES WHERE table_name = 'zh_user';

面试官:如果是删除重建的话,对于生产环境的业务表,还是不太可行的,你有什么可行的办法吗?

:这方面我经验不是很丰富,

如果允许低峰期短时间停服,可以考虑在交易最小的时间段评估执行时间,来完成

如果不允许停服,可以建一张备份表,把数据备份过去,让应用同时写2张表,数据一致后停止写原表,这种方法的问题是需要改代码测试上线

面试官:还有别的方法吗?

:一时想不出来,我回去问问DBA吧

面试官:哈哈,回去研究研究,等我叫下一位面试官

本文分享自微信公众号 - jinjunzhu(gh_1f109b82d301),作者:jinjunzhu

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-06-06

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • springcloud+eureka整合阿里seata-xa模式

    XA协议是X/Open组织管理的一种分布式协议规范,它采用2阶段提交来管理分布式事务,目前主流的数据库都支持xa协议。

    jinjunzhu
  • guava学习(一):观察者模式

    观察者模式是很常见的一种行为型设计模式。在Java原生态的实现方式中,观察者实现Observer接口,被观察者继承Observable。

    jinjunzhu
  • 行为型设计模式:模板模式

    模板模式是常用的一种行为型设计模式,主要思想是在模板中定义一套流程骨架代码,并且不实现骨架代码中使用的一些方法,这些方法留给子类去实现。假如我们业务开发中...

    jinjunzhu
  • 详细分析Python可变对象和不可变对象

    在 Python 中一切都可以看作为对象。每个对象都有各自的 id, type 和 value。

    砸漏
  • Mybatis之ResultMap

    爱撒谎的男孩
  • 约束

    一 介绍 约束条件与数据类型的宽度一样,都是可选参数 作用:用于保证数据的完整性和一致性 主要分为: PRIMARY KEY (PK) 标识该字段为该表的...

    用户1214487
  • 浅谈python中的多线程和多进程(二)

    前文《浅谈python中的多线程和多进程》中我们分享过一个例子,就是分别利用python中的多线程和多进程来解决高运算量的任务,从中看出二者的一些区别。其中一点...

    一只羊
  • 「数据库」sql刷题(No.6)

    Hello 各位 ,我是公号「八点半技术站」的创作者 - Bruce.D (姓氏:豆)。

    八点半的Bruce、D
  • Mybatis系列第7篇:各种查询详解

    Mybatis系列目标:从入门开始开始掌握一个高级开发所需要的Mybatis技能。

    路人甲Java
  • springmvc实例之修改雇员相关信息(四)

    首先是在EmployeeHandler.java中编写toEditEmployeePage方法:

    绝命生

扫码关注云+社区

领取腾讯云代金券