新建表为什么要有自增id

又要开始新项目了，一顿操作猛如虎，梳理流程加画图。这不，开始对流程及表结构了。

我：吧啦吧啦吧啦 …… 老大：这个建表为啥还设置个自增 id ？直接用流水号（用户号/产品号）当主键不就行了？ 我：这个是 DBA 规定的，创建表 id、create_time、update_time 这三个字段都要有。《Java 开发规范》也是这么规定的。 小伙伴：（附和）是的，规定的是这样的！ 老大：流水号在你这是唯一索引吧？设置成主键，这样就不用 id 了，还减少一次回表查询？ 我：…… （说的好像很有道理，咱也不敢说话。） 老大：既然他们规定了，那你回去查一下为什么要设计个自增 id ？ 我：掏出小本本（回去查资料~）。

建表规约

在工作中，创建表的时候，DBA 也会审核一下建表 SQL，检查是否符合规范以及常用字段是否设置索引。

CREATE TABLE `xxxx` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  `create_time` datetime(3) NOT NULL DEFAULT current_timestamp(3) COMMENT '创建时间',
  `update_time` datetime(3) NOT NULL DEFAULT current_timestamp(3) ON UPDATE current_timestamp(3) COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_create_time` (`create_time`) USING BTREE,
  KEY `idx_update_time` (`update_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COMMENT='表注释';

所以在我使用的过程中，流水号都是单独设置了一个字段，比如叫 trans_no，但是这次就遇到了疑问：trans_no 既然是唯一的，那为什么不直接用 trans_no 当做 id 呢？
下面开始通过查阅相关资料，一步一步的了解是为什么？

主键

什么是主键

https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_primary_key
这段定义咱们主要关注最后一句：

When choosing primary key values, consider using arbitrary values (a synthetic key) rather than relying on values derived from some other source (a natural key).

意思是创建主键的时候尽量使用 MySQL 自增主键而不是使用业务生成的值当做主键。

主键的特征

简而言之：
非空、唯一、少更改或不更改 。

如何添加主键

可以在 create 创建表的时候指定，也可以使用 alter 语句后面添加主键，不过官方建议在创建表时就指定。

为什么要添加主键

1. 主键可以唯一标识这一行数据，从而保证在删除更新操作时，只是操作这一行数据。
2. 索引需要，每个 InnoDB 表又有一个特殊的索引，即聚簇索引，用来存储行数据。通常，聚簇索引和主键同义。
   1. 声明主键，InnoDB 会将主键作为聚簇索引。
   2. 未声明时，会在 UNIQUE 所有键列所在位置找到第一个索引，NOT NULL 并将其作为聚簇索引
   3. 未声明且找不到合适的 UNIQUE 索引，则内部生成一个隐藏的聚簇索引 GEN_CLUST_INDEX，这个隐藏的行 ID 是 6 字节且单调增加。

图 -> 那什么是索引

索引

这里仅介绍 InnoDB 引擎，具体可以参考官方文档，并且介绍的相对比较简单。

索引分类

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

   补充： 回表：先在二级索引查询到对应的主键值，然后根据主键再去聚簇索引里面取查询。 索引覆盖：二级索引记录了主键列和二级索引列，如果我只查询主键列的值和二级索引列的值，那就不需要回表了。

索引的物理结构

InnoDB 使用的 B+ 数数据结构，根据聚簇索引值（主键/UNQIUE/或者自己生成）构建一颗 B+ 树，叶子节点中存放行记录数据，所以每个叶子节点也可以叫数据页。每个数据页大小默认为 16k，支持自定义。

数据的插入

当数据插入时，InnoDB 会使页面 1/16 空闲，以备将来插入和更新索引记录。

1. 顺序插入（升序或降序）：会将索引页剩余的大约 15/16 装满
2. 随机插入：只会使用容量的 1/2 到 15/16

在随机插入中，会频繁的移动、分页，从而造成大量的碎片，并且使索引树不够紧凑。而使用顺序插入的方式，则数据比较紧凑，有更高的空间利用率。

总结

Q&A

Q: 什么是回表和索引覆盖？
A:

1. 回表：先在二级索引查询到对应的主键值，然后根据主键再去聚簇索引里面取查询。
2. 索引覆盖：二级索引记录了主键列和二级索引列，如果我只查询主键列的值和二级索引列的值，那就不需要回表了。

Q: 为什么要设置自增主键 id ？
A:

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

   结束语

   本文主要通过查阅资料，了解为什么要设置一个和业务无关的自增 id 用来当做主键，很多内容比较浅显，比如 InnoDB 的 B+ 树，页分裂及页合并，插入过程等都没有进行深入研究，有兴趣的小伙伴可以更深入的研究下。
   同时在建表时除了要设置一个自增 id 用来当做主键，小伙伴们在业务开发过程中是否也会遇到一种情况：用户的注销，数据的删除等都是进行的逻辑删除，而不是物理删除。
   本篇文章介绍比较简陋，不足之处，希望大家多多指正。