关于mysql自增ID何时重置引发的思考

背景

mysql有一个表因为数据量太大,单表查询很慢,决定做表分区. 但是做表分区的时候, 分区键如果不是主键的话, 要把原来的主键(id)取消重新将原来的主键(id)和分区键绑定做一个复合主键.
但是原来的主键(id)是自增的, 取消它需要先取消自增. 然后复合主键建立之后再把id设为自增

问题

取消id自增, 然后又设置id自增, 那么id的值是会重置还是接着之前的值继续增加呢?

参考

参考1:mysql主键自增基本知识 https://blog.csdn.net/qq_35254185/article/details/104477745
参考1:mysql主键自增如何增长及高并发下如何保证主键不重复 https://blog.csdn.net/weixin_45701550/article/details/106751381

试验

一. 试验准备

  1. 表结构: 主键自增从5开始
DROP TABLE IF EXISTS `id_increament_test`;
CREATE TABLE `id_increament_test`  (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `age` int NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT=5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

image
2. 插入5条数据

insert into id_increament_test (name,age) VALUES 
('张五',1),
('张六',1),
('张七',2),
('李八',2),
('李九',3)

image

二. 测试去掉id自增,然后再设置id和age复合主键.再设置id自增, 看看id从几开始

-- 去掉主键和自增
ALTER TABLE id_increament_test CHANGE id id BIGINT;
ALTER TABLE id_increament_test DROP PRIMARY KEY;
-- 重新创建复合主键
ALTER TABLE id_increament_test ADD PRIMARY KEY (id, age);
ALTER TABLE id_increament_test CHANGE id id BIGINT AUTO_INCREMENT;

image

  1. 插入5条数据看看
insert into id_increament_test (name,age) VALUES 
('张十',11),
('张十一',11),
('张十二',12),
('李十三',12),
('李十四',13)

image

结果

可以看到新的5条数据的id也是自增, 并且接着上次5条的id最大值9,从10开始的, 说明id的自增值在取消了id自增后仍然保存起来的

扩展

mysql自增列基本知识:

  1. 通过auto_increment关键字来指定自增的列，并指定自增列的初始值为1,语法如下。
Create table t(id int auto_increment,name varchar(10),primary key(id))auto_increment=1;
  1. 自增列上必须有索引，将t表的主键索引删除掉，会报错
[root@localhost][test1]> alter table t drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

  1. 设定auto_increment_increment参数，可以调整自增步长，该参数有session级跟global级，在分库分表以及双主or多主的模式下比较有用。可以让不同的slave用不同的起始值,而步长相等,这样的话每个slave的自增值就不会重复了.

  2. 一个表上只能有一个自增列

  3. Mysql5.7及以下版本，innodb表的自增值保存在内存中，重启后表的自增值会设为max(id)+1，而myisam引擎的自增值是保存在文件中，重启不会丢失。Mysql8.0开始，innodb的自增id能持久化了，重启mysql，自增ID不会丢。

  4. 自增列的上限是根据自增列的字段类型来定的,到了上限之后会不再自增,一直使用最大值

  5. innodb引擎的表, 使用自增id跟UUID相比能有效增加索引节点保存的id的数量, 降低查询复杂度,增加查询效率

自增id有什么问题?

  1. 会出现不连续的值
    以下操作会使id出现不连续值
    a.物理删除数据
    删除了之前的数据,对应的id值的数据就没了, 看起来就像不连续数据了
    b.插入数据时指定了id的值
    这个分两种情况
    (1)如果指定的id的值大于或等于auto_increament的当前值, 则auto_increament的值会从指定的值开始自增.
    (2)如果指定的id值小于auto_increament的当前值,auto_increament的值不会改变,还是从当前值自增, 另外,如果指定的id值在表中已经有这条记录了, 会报主键重复错误,插入失败.
    c. 事务回滚
    插入的时候没问题,自增值也已经分配出去了,但是由于后面的操作出错导致整个事务回滚,插入数据的语句虽然回滚了,但是自增值已经变化了.

  2. 并发场景下,如何保证值的正确性? 自增锁
    当多个线程同时插入数据到数据库中，是否存在两个记录有相同的主键值呢？答案是不会的。mysql提供了一个保证自增主键值正确性的机制–自增锁(一种排他锁)。它让申请自增主键的操作由并行变成串行，保证了每个插入操作都能申请到一个唯一的主键值。
    mysql的innodb_autoinc_lock_mode参数控制着自增锁的上锁机制。该参数有0、1、2三种模式：
    0：语句执行结束后释放自增锁，MySQL5.0时采用这种模式，并发度较低。
    1：mysql的默认设置。普通的insert语句申请后立马释放，insert select、replace insert、load data等批量插入语句要等语句执行结束后才释放，并发读得到提升
    2：所有的语句都是申请后立马释放，并发度大大提升！但是在binlog为statement格式时，主从数据会发生不一致。这一块网上有很多例子。

大家生产上该参数设为2，然后binlog设为row格式就行

posted on 2023-07-19 15:10  一贯可乐  阅读(184)  评论(0)  编辑  收藏  举报



123