共计 2497 个字符，预计需要花费 7 分钟才能阅读完成。
又要开始新我的项目了，一顿操作猛如虎，梳理流程加画图。这不，开始对流程及表构造了。
我：吧啦吧啦吧啦 ……
老大：这个建表为啥还设置个自增 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/ref…
这段定义咱们次要关注最初一句：
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 字节且枯燥减少。
图 -> 那什么是索引
索引
这里仅介绍 InnoDB 引擎，具体能够参考官网文档，并且介绍的绝对比较简单。
索引分类
- 聚簇索引：表存储是依据主键列的值组织的，以放慢波及主键列的查问和排序。在介绍主键时也对聚簇索引进行了介绍。
- 二级索引：也能够叫辅助索引，在辅助索引中会记录对应的主键列以及辅助索引列。依据辅助索引进行搜寻的时候，会先依据辅助索引获取到对应的主键列，而后再依据主键去聚簇索引外面搜寻。 个别不倡议主键很长，因为主键很长辅助索引就会应用更多的空间。
补充：
回表：先在二级索引查问到对应的主键值，而后依据主键再去聚簇索引外面取查问。
索引笼罩：二级索引记录了主键列和二级索引列，如果我只查问主键列的值和二级索引列的值，那就不须要回表了。
索引的物理构造
InnoDB 应用的 B+ 数数据结构，依据聚簇索引值（主键 /UNQIUE/ 或者本人生成）构建一颗 B+ 树，叶子节点中寄存行记录数据，所以每个叶子节点也能够叫数据页。每个数据页大小默认为 16k，反对自定义。
数据的插入
当数据插入时，InnoDB 会使页面 1/16 闲暇，以备未来插入和更新索引记录。
- 程序插入（升序或降序）：会将索引页残余的大概 15/16 装满
- 随机插入：只会应用容量的 1/2 到 15/16
在随机插入中，会频繁的挪动、分页，从而造成大量的碎片，并且使索引树不够紧凑。而应用程序插入的形式，则数据比拟紧凑，有更高的空间利用率。
总结
Q&A
Q: 什么是回表和索引笼罩？
A:
- 回表：先在二级索引查问到对应的主键值，而后依据主键再去聚簇索引外面取查问。
- 索引笼罩：二级索引记录了主键列和二级索引列，如果我只查问主键列的值和二级索引列的值，那就不须要回表了。
Q: 为什么要设置自增主键 id？
A:
- 能够惟一标识一行数据，在 InnoDB 构建索引树的时候会应用主键。
- 自增 id 是程序的，能够保障索引树上的数据比拟紧凑，有更高的空间利用率以及缩小数据页的决裂合并等操作，提高效率。
- 个别应用手机号、身份证号作为主键等并不能保障程序性。
- 流水号个别绝对较长，比方 28 位，32 位等，过长的话会二级索引占用空间较多。同时为了业务需要，流水号具备肯定的随机性。
结束语
本文次要通过查阅材料，理解为什么要设置一个和业务无关的自增 id 用来当做主键，很多内容比拟通俗，比方 InnoDB 的 B+ 树，页决裂及页合并，插入过程等都没有进行深入研究，有趣味的小伙伴能够更深刻的钻研下。
同时在建表时除了要设置一个自增 id 用来当做主键，小伙伴们在业务开发过程中是否也会遇到一种状况：用户的登记，数据的删除等都是进行的逻辑删除，而不是物理删除。
本篇文章介绍比拟简陋，不足之处，心愿大家多多斧正。
