共计 2360 个字符，预计需要花费 6 分钟才能阅读完成。
这篇文章主要为大家展示了“Mysql 自增 id 的示例分析”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让丸趣 TV 小编带领大家一起研究并学习一下“Mysql 自增 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 字段类型的范围：
类型大小范围（有符号）范围（无符号）int4 字节 (-2147483648,2147483647)(0,4294967295)bigint8 字节 (-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 的示例分析”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！
