关于Mysql自增id的这些你可能还不知道

 更新时间:2019-05-16 00:00:25   作者:佚名   我要评论(0)

导读:
在使用MySQL建表时,我们通常会创建一个自增字段(AUTO_INCREMENT),并以此字段作为主键。本篇文章将以问答的形式讲述关于自增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也是有上限的。
下表列举下 int 与 bigint 字段类型的范围:

类型 大小 范围(有符号) 范围(无符号)
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);
// 成功插入一行 4294967295
show 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自增ID起始值修改方法
  • mysql如何让自增id归0解决方案
  • Java获取最后插入MySQL记录的自增ID值的3种方法
  • 两种mysql对自增id重新从1排序的方法
  • MySQL分表自增ID问题的解决方法
  • 利用mysql事务特性实现并发安全的自增ID示例
  • mysql自增id超大问题的排查与解决
  • MySQL自增ID耗尽实例讲解

相关文章

  • 关于Mysql自增id的这些你可能还不知道

    关于Mysql自增id的这些你可能还不知道

    导读: 在使用MySQL建表时,我们通常会创建一个自增字段(AUTO_INCREMENT),并以此字段作为主键。本篇文章将以问答的形式讲述关于自增id的一切。 注: 本文所讲
    2019-05-16
  • JS中使用react-tooltip插件实现鼠标悬浮显示框

    JS中使用react-tooltip插件实现鼠标悬浮显示框

    前段时间遇到的一个需求,要求鼠标悬停显示使用描述, 用到了react-tooltip插件,今天写一个总结 先看效果(为了方便参考,用的是原始样式): 文档参考地址: https
    2019-05-16
  • 详解Python下载图片并保存本地的两种方式

    详解Python下载图片并保存本地的两种方式

    一:使用Python中的urllib类中的urlretrieve()函数,直接从网上下载资源到本地,具体代码: import os,stat import urllib.request img_url="https://tim
    2019-05-16
  • SpringBoot整合MybatisPlus的简单教程实现(简单整合)

    SpringBoot整合MybatisPlus的简单教程实现(简单整合)

    最近在研究springboot,顺便就会看看数据库连接这一块的知识 ,所以当我发现有通用Mapper和MybatisPlus这两款网络上比较火的简化mybatis开发的优秀软件之后。
    2019-05-16
  • MongoDB分片集群部署详解

    MongoDB分片集群部署详解

    一、环境说明 1、我们prod环境MongoDB的集群架构是做的分片集群的部署,但是目前我们没有分片,即所有数据都在一个分片上,后期如果数量大,需要分配,集群随
    2019-05-16
  • python3 property装饰器实现原理与用法示例

    python3 property装饰器实现原理与用法示例

    本文实例讲述了python3 property装饰器实现原理与用法。分享给大家供大家参考,具体如下: 学习python的同学,慢慢的都会接触到装饰器,装饰器在python里是功
    2019-05-16
  • Android不显示开机向导和开机气泡问题

    Android不显示开机向导和开机气泡问题

    修改好的代码下载地址: https://github.com/Vico-H/Launcher •不显示开机向导 --------------------------------------------------------------------
    2019-05-12
  • 如何让PHP编码更加好看利于阅读

    如何让PHP编码更加好看利于阅读

    写出优秀的程序代码是一门艺术,要想如此,就必须在一开始就养成良好的编程习惯。良好的编程习惯不仅有助于项目初期的设计(如模块化),还可以使你编写的代码
    2019-05-12
  • mysql存数组的实例代码和方法

    mysql存数组的实例代码和方法

    在很多的情况下,在编写存储过程中往往会用到数组,但是mysql中存储过程传入参数并没有可以直接传入数组的方法。在这种情况下我们只能退而求之或者说换个方式
    2019-05-12
  • mysql删除关联表的实操方法

    mysql删除关联表的实操方法

    mysql数据库中,表与表之间进行关联之后,就不可随意的进行删除操作,否则会影响所有关联表之间的结构,那么如何安全的删除关联表呢,让我们来了解一下。 删除
    2019-05-12

最新评论