前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >面试官:mysql如何重置自增id

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

作者头像
jinjunzhu
发布2020-08-20 16:09:09
7.2K1
发布2020-08-20 16:09:09
举报
文章被收录于专栏:个人开发个人开发

面试官:咱们聊聊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吧

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

本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2020-06-06,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 jinjunzhu 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档