老大问我:“建表为啥还设置个自增 id ?用流水号当主键不正好么?”

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

[[345532]]

前言

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

我:吧啦吧啦吧啦 ……

老大:这个建表为啥还设置个自增 id ?直接用流水号(用户号/产品号)当主键不就行了?

我:这个是 DBA 规定的,创建表 id、create_time、update_time 这三个字段都要有。《Java 开发规范》也是这么规定的。

小伙伴:(附和)是的,规定的是这样的!

老大:流水号在你这是唯一索引吧?设置成主键,这样就不用 id 了,还减少一次回表查询?

我:…… (说的好像很有道理,咱也不敢说话。)

老大:既然他们规定了,那你回去查一下为什么要设计个自增 id ?

我:掏出小本本(回去查资料~)。"

 

1.建表规约

Java 开发手册-嵩山版

 

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

  1. CREATE TABLE `xxxx` ( 
  2.   `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键'
  3.   `create_time` datetime(3) NOT NULL DEFAULT current_timestamp(3) COMMENT '创建时间'
  4.   `update_time` datetime(3) NOT NULL DEFAULT current_timestamp(3) ON UPDATE current_timestamp(3) COMMENT '更新时间'
  5.   PRIMARY KEY (`id`) USING BTREE, 
  6.   KEY `idx_create_time` (`create_time`) USING BTREE, 
  7.   KEY `idx_update_time` (`update_time`) USING BTREE 
  8. ) 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 语句后面添加主键,不过官方建议在创建表时就指定。

为什么要添加主键

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

 

3.索引

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

索引的分类

聚簇索引:表存储是根据主键列的值组织的,以加快涉及主键列的查询和排序。在介绍主键时也对聚簇索引进行了介绍。

二级索引:也可以叫辅助索引,在辅助索引中会记录对应的主键列以及辅助索引列。根据辅助索引进行搜索的时候,会先根据辅助索引获取到对应的主键列,然后再根据主键去聚簇索引里面搜索。一般不建议主键很长,因为主键很长辅助索引就会使用更多的空间。

" 补充:

回表:先在二级索引查询到对应的主键值,然后根据主键再去聚簇索引里面取查询。

索引覆盖:二级索引记录了主键列和二级索引列,如果我只查询主键列的值和二级索引列的值,那就不需要回表了。 "

索引的物理结构

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

图:《MySQL 技术内幕 InnoDB 存储引擎》

 

数据的插入

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

  1. 顺序插入(升序或降序):会将索引页剩余的大约 15/16 装满
  2. 随机插入:只会使用容量的 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 存储引擎》第二版

本文转载自微信公众号「刘志航」,可以通过以下二维码关注。转载本文请联系刘志航公众号。

 

责任编辑:武晓燕 来源: 刘志航
相关推荐

2009-09-24 13:49:31

Hibernate自增

2023-12-26 01:09:28

MySQL存储释放锁

2023-10-24 15:27:33

Mysql自增主键

2011-08-19 09:45:02

DB4O设置自增ID

2022-11-08 19:30:52

DjangoID自增

2022-06-14 08:01:43

数据库MySQL

2010-03-29 14:09:12

Oracle ID 自

2018-12-14 15:35:20

MySQL索引数据库

2020-08-24 07:19:13

主键自增数据库

2011-08-22 10:47:09

SQL Server流水号

2021-09-28 17:48:20

MySQL主键索引

2022-06-03 08:12:52

InnoDB插入MySQL

2021-08-04 10:34:00

MySQL主键int32

2022-12-06 09:00:11

MySQL自增主键查询

2010-10-08 15:42:39

MySQL设置自增字段

2010-04-26 14:03:02

Oracle使用

2011-01-18 09:47:33

自增ID

2010-06-04 11:15:23

MySQL自增主键

2020-04-21 15:59:50

MySQL自增主键数据库

2010-04-30 12:15:42

Oracle自增ID
点赞
收藏

51CTO技术栈公众号