专栏首页MySQL技术关于自增id 你可能还不知道

关于自增id 你可能还不知道

导读:在使用MySQL建表时,我们通常会创建一个自增字段(AUTO_INCREMENT),并以此字段作为主键。本篇文章将以问答的形式讲述关于自增id的一切。

注: 本文所讲的都是基于Innodb存储引擎。

1.MySQL为什么建议将自增列id设为主键?
  • 如果我们定义了主键(PRIMARY KEY),那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引,则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增,这个ROWID不像ORACLE的ROWID那样可引用,是隐含的)。
  • 数据记录本身被存于主索引(一颗B+Tree)的叶子节点上。这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放,因此每当有一条新的记录插入时,MySQL会根据其主键将其插入适当的节点和位置,如果页面达到装载因子(InnoDB默认为15/16),则开辟一个新的页(节点)
  • 如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页
  • 如果使用非自增主键(如果身份证号或学号等),由于每次插入主键的值近似于随机,因此每次新纪录都要被插到现有索引页得中间某个位置,此时MySQL不得不为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销,同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构,后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

综上而言:当我们使用自增列作为主键时,存取效率是最高的。

2.自增列id一定是连续的吗?

自增id是增长的 不一定连续。

我们先来看下MySQL 对自增值的保存策略:

InnoDB 引擎的自增值,其实是保存在了内存里,并且到了 MySQL 8.0 版本后,才有了“自增值持久化”的能力,也就是才实现了“如果发生重启,表的自增值可以恢复为 MySQL 重启前的值”,具体情况是: 在 MySQL 5.7 及之前的版本,自增值保存在内存里,并没有持久化。每次重启后,第一次打开表的时候,都会去找自增值的最大值 max(id),然后将 max(id)+1 作为这个表当前的自增值。 举例来说,如果一个表当前数据行里最大的 id 是 10,AUTO_INCREMENT=11。这时候,我们删除 id=10 的行,AUTO_INCREMENT 还是 11。但如果马上重启实例,重启后这个表的 AUTO_INCREMENT 就会变成 10。 也就是说,MySQL 重启可能会修改一个表的 AUTO_INCREMENT 的值。 在 MySQL 8.0 版本,将自增值的变更记录在了 redo log 中,重启的时候依靠 redo log 恢复重启之前的值。

造成自增id不连续的情况可能有:

  • 1.唯一键冲突
  • 2.事务回滚
  • 3.insert ... select语句批量申请自增id
3.自增id有上限吗?

自增id是整型字段,我们常用int类型来定义增长id,而int类型有上限 即增长id也是有上限的。

下表列举下 intbigint 字段类型的范围:

类型

大小

范围(有符号)

范围(无符号)

int

4字节

(-2147483648,2147483647)

(0,4294967295)

bigint

8字节

(-9223372036854775808,9223372036854775807)

(0,18446744073709551615)

从上表可以看出:当自增字段使用int有符号类型时,最大可达2147483647即21亿多;使用int无符号类型时,最大可达4294967295即42亿多。当然bigint能表示的范围更大。

下面我们测试下当自增id达到最大时再次插入数据会怎么样:

create table t(id int unsigned auto_increment primary key) auto_increment=4294967295;insert into t values(null);// 成功插入一行 4294967295show create table t;/* CREATE TABLE `t` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4294967295;*/
insert into t values(null);//Duplicate entry '4294967295' for key 'PRIMARY'

从实验可以看出,当自增id达到最大时将无法扩展,第一个 insert 语句插入数据成功后,这个表的AUTO_INCREMENT 没有改变(还是 4294967295),就导致了第二个 insert 语句又拿到相同的自增 id 值,再试图执行插入语句,报主键冲突错误。

4.关于自增列 我们该怎么维护?

维护方面主要提供以下2点建议:

  • 1.字段类型选择方面:推荐使用int无符号类型,若可预测该表数据量将非常大 可改用bigint无符号类型。
  • 2.多关注大表的自增值,防止发生主键溢出情况。

本文分享自微信公众号 - MySQL技术(MySQL_SRE),作者:MySQL技术

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

原始发表时间:2019-04-07

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL5.7应当注意的参数

    简介: 本篇文章主要介绍 MySQL 初始化应当注意的参数,对于不同环境间实例迁移,这些参数同样应当注意。

    MySQL技术
  • INT类型知多少

    整型是MySQL中最常用的字段类型之一,通常用于存储整数,其中int是整型中最常用的,对于int类型你是否真正了解呢?本文会带你熟悉int类型相关知识,也会介绍...

    MySQL技术
  • 【MySQL】zabbix监控MySQL配置

    MySQL技术
  • mybatis 详解(七)------一对一、一对多、多对多

      前面几篇博客我们用mybatis能对单表进行增删改查操作了,也能用动态SQL书写比较复杂的sql语句。但是在实际开发中,我们做项目不可能只是单表操作,往往会...

    IT可乐
  • MySQL创建计算字段及基本函数

    select concat(vend_name,'(',vend_country,')') from vendors order by vend_name;

    小末快跑
  • 性能优化-Limit查询的优化

    Limit常用于分页处理,时长会伴随order by从句使用,因此大多时候回使用Filesorts这样会造成大量的IO问题。 例子: 需求:查询影片id和描...

    cwl_java
  • Mybatis之ResultMap

    爱撒谎的男孩
  • 超火的个性化推荐你再不会就OUT啦,让飞桨手把手来教你

    导读:随着电子商务规模的不断扩大,电商平台的商品数量和种类呈爆发式增长,用户往往需要花费大量的时间才能找到自己想买的商品,这就是信息超载问题。为了解决这个难题,...

    用户1386409
  • 第26天:js-$id函数、焦点事件

    一、函数return语句 定义函数的返回值,在函数内部用return来设置返回值,一个函数只能有一个返回值。同时,终止代码的执行。 所有自定义函数默认没有返回值...

    半指温柔乐
  • 面试官:mysql如何重置自增id

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

    jinjunzhu

扫码关注云+社区

领取腾讯云代金券