前言
" 又要开始新项目了，一顿操作猛如虎，梳理流程加画图。这不，开始对流程及表结构了。
我：吧啦吧啦吧啦 ……
老大：这个建表为啥还设置个自增 id ?直接用流水号(用户号/产品号)当主键不就行了?
我：这个是 DBA 规定的，创建表 id、create_time、update_time 这三个字段都要有。《Java 开发规范》也是这么规定的。
小伙伴：(附和)是的，规定的是这样的!
老大：流水号在你这是唯一索引吧?设置成主键，这样就不用 id 了，还减少一次回表查询?
我：…… (说的好像很有道理，咱也不敢说话。)
老大：既然他们规定了，那你回去查一下为什么要设计个自增 id ?
我：掏出小本本(回去查资料~)。"
1.建表规约
Java 开发手册-嵩山版
在工作中，创建表的时候，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 呢?
下面开始通过查阅相关资料，一步一步的了解是为什么?
2.主键
什么是主键?
MySQL 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 语句后面添加主键，不过官方建议在创建表时就指定。
为什么要添加主键
- 主键可以唯一标识这一行数据，从而保证在删除更新操作时，只是操作这一行数据。
- 索引需要，每个 InnoDB 表又有一个特殊的索引，即聚簇索引，用来存储行数据。通常，聚簇索引和主键同义。
- 声明主键，InnoDB 会将主键作为聚簇索引。
- 未声明时，会在 UNIQUE 所有键列所在位置找到第一个索引，NOT NULL 并将其作为聚簇索引
- 未声明且找不到合适的 UNIQUE 索引，则内部生成一个隐藏的聚簇索引 GEN_CLUST_INDEX，这个隐藏的行 ID 是 6 字节且单调增加。
3.索引
这里仅介绍 InnoDB 引擎，具体可以参考官方文档，并且介绍的相对比较简单。
索引的分类
聚簇索引：表存储是根据主键列的值组织的，以加快涉及主键列的查询和排序。在介绍主键时也对聚簇索引进行了介绍。
二级索引：也可以叫辅助索引，在辅助索引中会记录对应的主键列以及辅助索引列。根据辅助索引进行搜索的时候，会先根据辅助索引获取到对应的主键列，然后再根据主键去聚簇索引里面搜索。一般不建议主键很长，因为主键很长辅助索引就会使用更多的空间。
" 补充：
回表：先在二级索引查询到对应的主键值，然后根据主键再去聚簇索引里面取查询。
索引覆盖：二级索引记录了主键列和二级索引列，如果我只查询主键列的值和二级索引列的值，那就不需要回表了。 "
索引的物理结构
InnoDB 使用的 B+ 数数据结构，根据聚簇索引值(主键/UNQIUE/或者自己生成)构建一颗 B+ 树，叶子节点中存放行记录数据，所以每个叶子节点也可以叫数据页。每个数据页大小默认为 16k，支持自定义。
图：《MySQL 技术内幕 InnoDB 存储引擎》
数据的插入
当数据插入时，InnoDB 会使页面 1/16 空闲，以备将来插入和更新索引记录。
- 顺序插入(升序或降序)：会将索引页剩余的大约 15/16 装满
- 随机插入：只会使用容量的 1/2 到 15/16
在随机插入中，会频繁的移动、分页，从而造成大量的碎片，并且使索引树不够紧凑。而使用顺序插入的方式，则数据比较紧凑，有更高的空间利用率。
4.总结
Q&A
Q: 什么是回表和索引覆盖?
A:
回表：先在二级索引查询到对应的主键值，然后根据主键再去聚簇索引里面取查询。
索引覆盖：二级索引记录了主键列和二级索引列，如果我只查询主键列的值和二级索引列的值，那就不需要回表了。
Q: 为什么要设置自增主键 id ?
A:
可以唯一标识一行数据，在 InnoDB 构建索引树的时候会使用主键。
自增 id 是顺序的，可以保证索引树上的数据比较紧凑，有更高的空间利用率以及减少数据页的分裂合并等操作，提高效率。
一般使用手机号、身份证号作为主键等并不能保证顺序性。
流水号一般相对较长，比如 28 位，32 位等，过长的话会二级索引占用空间较多。同时为了业务需求，流水号具有一定的随机性。
结束语
本文主要通过查阅资料，了解为什么要设置一个和业务无关的自增 id 用来当做主键，很多内容比较浅显，比如 InnoDB 的 B+ 树，页分裂及页合并，插入过程等都没有进行深入研究，有兴趣的小伙伴可以更深入的研究下。
同时在建表时除了要设置一个自增 id 用来当做主键，小伙伴们在业务开发过程中是否也会遇到一种情况：用户的注销，数据的删除等都是进行的逻辑删除，而不是物理删除。
本篇文章介绍比较简陋，不足之处，希望大家多多指正。
相关资料
[1] MySQL 官方文档：
https://dev.mysql.com/doc/refman/8.0/en/
[2] 《MySQL 技术内幕 InnoDB 存储引擎》第二版
本文转载自微信公众号「刘志航」，可以通过以下二维码关注。转载本文请联系刘志航公众号。
