MySQL 主键和自增 ID

必备的三个字段

通常情况下，我们每个表必备三个字段：id，create_time，update_time。

其中 id 为主键，类型为 BIGINT UNSIGNED，自增、步长为 1。

create_time 和 update_time 均为 DATETIME 类型。

为什么用自增 ID 做主键

MySQL 官方推荐创建主键的时候尽量使用自增主键而不是使用业务生成的值当做主键 —— dev.mysql.com/doc/refman/… (opens new window)

很多时候我们存储的对象自身都会有个唯一值，比如身份证号、流水单号、GUID，那为什么不直接用这些唯一值当做主键，而是要设置一个和业务无关的自增 id 用来当做主键？

先说答案：

• 自增 id 可以唯一标识一行数据，在 InnoDB 构建索引树的时候会使用主键。
• 自增 id 是顺序的，可以保证索引树上的数据比较紧凑，有更高的空间利用率以及减少数据页的分裂合并等操作，提高效率。
• 一般使用身份证号、流水单号作为主键等并不能保证顺序性。
• 流水号一般相对较长，比如 32 位，过长的话二级索引占用空间较多。

这里涉及到几个知识点：索引、索引树、二级索引。仅介绍针对 InnoDB 引擎进行介绍（现在一般都是用这种引擎）：

索引分类

索引有两种：

• 聚簇索引：表存储是根据主键列的值组织的，以加快涉及主键列的查询和排序。
• 二级索引：也可以叫辅助索引，在辅助索引中会记录对应的主键列以及辅助索引列。根据辅助索引进行搜索的时候，会先根据辅助索引获取到对应的主键列，然后再根据主键去聚簇索引里面搜索。一般不建议主键很长，因为主键很长辅助索引就会使用更多的空间。

每个 InnoDB 表有一个特殊的索引，即聚簇索引，用来存储行数据。通常，聚簇索引和主键同义：

• 如果声明主键，InnoDB 会将主键作为聚簇索引。
• 如果未声明主键，会在 UNIQUE 所有键列所在位置找到第一个索引，NOT NULL 并将其作为聚簇索引。
• 如果未声明且找不到合适的 UNIQUE 索引，则内部生成一个隐藏的聚簇索引 GEN_CLUST_INDEX，这个隐藏的行 ID 是 6 字节且单调递增。

自增 ID 用完会发生什么

如果主键设置成自增 ID，在 MySQL 中 INT 类型是 4 个字节，有符号位的话取值范围就是 [-2³¹ , 2³¹ - 1]，最大值为 2147483647，也就是说能存储大约 21 亿数据。

这个时候表里有一条 id 达到有符号位的最大值上限的数据，再次执行插入语句时就会报错：

1062 - Duplicate entry '2147483647' for key 'PRIMARY', Time: 0.000000s。

也就是说，如果设置了主键并且自增的话，达到自增主键上限就会报错重复的主键。

自增 ID 用完的解决方案

InnoDB 系统自增 row_id

如果没有手动设置主键的话，InnoDB 会自动创建一个 6 个字节的 row_id（代码上使用的是 BIGINT UNSIGNED 类型，但实际上只给 row_id 留 了6 字节），由于 row_id 是无符号的，所以能写入表中的值范围是 [0 , 2⁴⁸-1]，即最大值为 281474976710655。

这个 row_id 是由 InnoDB 维护在全局的（dict_sys.row_id），所有无主键的 InnoDB 表，每插入一行数据，都将当前的 dict_sys.row_id 值作为要插入数据的 row_id，然后加 1。

当达到上限时，如果再有插入数据的行为要来申请 row_id，拿到的就是 0，存在主键冲突的可能性。

BIGINT

将 MySQL 主键改为 BIGINT，也就是 8 个字节，如果是无符号 BIGINT，那么自增长范围是 [0 , 2⁶⁴ – 1]。

这个范围对我们正常项目开发而言已经非常非常大了，正常使用基本不存在超出范围问题（非正常使用：创建表时指定自增长值接近越界值）。

退一万步讲，就算一张表里的数据真能达到这么多，那么其实在自增 ID 用完前就已经有数据库性能问题了，此时不等自增长值接近越界值，早就应该做分库分表操作了。

所以不要再去纠结要是这个自增长值超出最大值咋办，基本遇不到这样的场景。

Redis 自增主键

外部自增主键的生成方式有很多，Redis 是常用的一种，因为它有很多优点：

Redis 自身是原子性的，因此高并发也是线程安全的。假设主键字段长度 20，我们以「时间 + 自增数」来构成主键，例如：「8位日期 + 12位自增数」。根据业务性质可以决定时间取年月日或者到毫秒级，那么在毫秒之间自增数的重复概率是极小极小的，基本的业务都能适用。

（完）