前言
" 又要开始新项目了,一顿操作猛如虎,梳理流程加画图。这不,开始对流程及表结构了。
我:吧啦吧啦吧啦 ……
老大:这个建表为啥还设置个自增 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 语句后面添加主键,不过官方建议在创建表时就指定。
为什么要添加主键
3
索引
这里仅介绍 InnoDB 引擎,具体可以参考官方文档,并且介绍的相对比较简单。
索引的分类
" 补充: 回表:先在二级索引查询到对应的主键值,然后根据主键再去聚簇索引里面取查询。 索引覆盖:二级索引记录了主键列和二级索引列,如果我只查询主键列的值和二级索引列的值,那就不需要回表了。 "
索引的物理结构
InnoDB 使用的 B+ 数数据结构,根据聚簇索引值(主键/UNQIUE/或者自己生成)构建一颗 B+ 树,叶子节点中存放行记录数据,所以每个叶子节点也可以叫数据页。每个数据页大小默认为 16k,支持自定义。
图:《MySQL 技术内幕 InnoDB 存储引擎》
数据的插入
当数据插入时,InnoDB 会使页面 1/16 空闲,以备将来插入和更新索引记录。
在随机插入中,会频繁的移动、分页,从而造成大量的碎片,并且使索引树不够紧凑。而使用顺序插入的方式,则数据比较紧凑,有更高的空间利用率。
4
总结
Q&A
Q: 什么是回表和索引覆盖?
A:
Q: 为什么要设置自增主键 id ?
A:
相关资料
[1] MySQL 官方文档:
https://dev.mysql.com/doc/refman/8.0/en/
[2] 《MySQL 技术内幕 InnoDB 存储引擎》第二版