• 自动秒收录
  • 软件:1973
  • 资讯:56821|
  • 收录网站:208189|

IT精英团

数据库主键一定要自己增加吗?有哪些不推荐自我增加的场景?

数据库主键一定要自己增加吗?有哪些不推荐自我增加的场景?

浏览次数:
评论次数:
编辑: 阳煦
信息来源: ITPUB
更新日期: 2022-06-20 21:25:56
摘要

我们平时建表的时候,一般会像下面这样。CREATETABLE`user`(`id`intNOTNULLAUTO_INCREMENTCOMMENT'主键',`name`char(10)NOTNULLDE

  • 正文开始
  • 相关阅读
  • 推荐作品

我们平时制作手表的时候,一般是这样的。

CREATETABLE`user `(

` id`intnotnullauto _ increment注释'主键',

` name`char (10) not null default ' '注释'名字',

主键(` id `)

)ENGINE=InnoDBDEFAULTCHARSET=utf8mb 4;

出于习惯,我们通常会添加一列id作为主键,这个主键通常在旁边有一个AUTO_INCREMENT,这意味着这个主键是自增的。自增为I,即每次加一。

但问题来了。

主键id不自增行不行?

为什么要用自增id做主键?

离谱点,没有主键可以吗?

什么情况下不应该自增?

经过这么一波质疑,你还不能理解你的想法?

在本文中,我将尝试回答这些问题。

主键不自增行不行

是的,当然。例如,我们可以在构建表的sql中删除AUTO_INCREMENT。

CREATETABLE`user `(

Id`intNOTNULLCOMMENT '主键',

` name`char (10) not null default ' '注释'名字',

主键(` id `)

)ENGINE=InnoDBDEFAULTCHARSET=utf8mb 4;

然后执行。

INSERTINTO`user`(`name `)值(“debug”);

此时,将报告错误字段“id”没有默认值。也就是说,如果不允许主键自己增加,那么在写数据的时候就需要自己指定id的值。主键id想写多少就写多少,不写就报错。

改成下面的就行了。

INSERTINTO`user`(`id `,` name `)值(10,' debug ');

为什么要用自增主键

我们保存在数据库中的数据就像一个excel表格,一行一行。

用户表,在底部,这一行数据存储在16k大小的页.

每次我们遍历所有的行时,性能都会很差,所以为了加快搜索速度,我们可以在根据主键id,从小到大排列这些行数据,以双向链表的形式组织这些数据页,然后从这些页中提取一些信息,放入一个新的16kb的数据页中,然后加入层级的概念.结果,这些数据页被组织起来,变成了一个B+树索引.

PG/2022-06-20/29BAE0EBB62E95F0AB64F1A6D035363B.jpg" width="1462" title="B+树结构" src="https://image.z.itpub.net/zitpub.net/JPG/2022-06-20/29BAE0EBB62E95F0AB64F1A6D035363B.jpg">
B+树结构

而当我们在建表sql里声明了PRIMARY KEY (id)时,mysql的innodb引擎,就会为主键id生成一个主键索引,里面就是通过B+树的形式来维护这套索引。

到这里,我们有两个点是需要关注的:

  • 数据页大小是固定16k

  • 数据页内,以及数据页之间,数据主键id都是从小到大排序

由于数据页大小固定了是16k,当我们需要插入一条新的数据,数据页会被慢慢放满,当超过16k时,这个数据页就有可能会进行分裂

针对B+树叶子节点如果主键是自增的,那它产生的id每次都比前一次要大,所以每次都会将数据加在B+树尾部,B+树的叶子节点本质上是双向链表,查找它的首部和尾部,时间复杂度O(1)。而如果此时最末尾的数据页满了,那创建个新的页就好。

主键id自增的情况

如果主键不是自增的,比方说上次分配了id=7,这次分配了id=3,为了让新加入数据后B+树的叶子节点还能保持有序,它就需要往叶子结点的中间找,查找过程的时间复杂度是O(lgn),如果这个页正好也满了,这时候就需要进行页分裂了。并且页分裂操作本身是需要加悲观锁的。总体看下来,自增的主键遇到页分裂的可能性更少,因此性能也会更高。

主键id不自增的情况


没有主键可以吗

mysql表如果没有主键索引,查个数据都得全表扫描,那既然它这么重要,我今天就不当人了,不声明主键,可以吗?

嗯,你完全可以不声明主键。

你确实可以在建表sql里写成这样。

CREATE TABLE `user` (
  `name` char(10) NOT NULL DEFAULT '' COMMENT '名字'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

看起来确实是没有主键的样子。然而实际上,mysql的innodb引擎内部会帮你生成一个名为ROW_ID列,它是个6字节的隐藏列,你平时也看不到它,但实际上,它也是自增的。有了这层兜底机制保证,数据表肯定会有主键和主键索引

跟ROW_ID被隐藏的列还有trx_id字段,用于记录当前这一行数据行是被哪个事务修改的,和一个roll_pointer字段,这个字段是用来指向当前这个数据行的上一个版本,通过这个字段,可以为这行数据形成一条版本链,从而实现多版本并发控制(MVCC)。有没有很眼熟,这个在之前写的文章里出现过。

隐藏的row_id列



有没有建议主键不自增的场景

前面提到了主键自增可以带来很多好处,事实上大部分场景下,我们都建议主键设为自增。

那有没有不建议主键自增的场景呢?


mysql分库分表下的id

聊到分库分表,那我就需要说明下,递增和自增的区别了,自增就是每次都+1,而递增则是新的id比上一个id要大就行了,具体大多少,没关系。

之前写过一篇文章提到过,mysql在水平分库分表时,一般有两种方式。

一种分表方式是通过对id取模进行分表,这种要求递增就好,不要求严格自增,因为取模后数据会被分散到多个分表中,就算id是严格自增的,在分散之后,都只能保证每个分表里id只能是递增的。

根据id取模分表

另一种分表方式是根据id的范围进行分表(分片),它会划出一定的范围,比如以2kw为一个分表的大小,那0~2kw就放在这张分表中,2kw~4kw放在另一张分表中,数据不断增加,分表也可以不断增加,非常适合动态扩容,但它要求id自增,如果id递增,数据则会出现大量空洞。举个例子,比如第一次分配id=2,第二次分配id=2kw,这时候第一张表的范围就被打满了,后面再分配一个id,比如是3kw,就只能存到2kw~4kw(第二张)的分表中。那我在0~2kw这个范围的分表,也就存了两条数据,这太浪费了。

根据id范围分表

但不管哪种分表方式,一般是不可能继续用原来表里的自增主键的,原因也比较好理解,原来的每个表如果都从0开始自增的话,那好几个表就会出现好几次重复的id,根据id唯一的原则,这显然不合理。


所以我们在分库分表的场景下,插入的id都是专门的id服务生成的,如果是要严格自增的话,那一般会通过redis来获得,当然不会是一个id请求获取一次,一般会按批次去获得,比如一次性获得100个。快用完了再去获取下一批100个。

但这个方案有个问题,它严重依赖redis,如果redis挂了,那整个功能就傻了。

有没有不依赖于其他第三方组件的方法呢?


雪花算法

有,比如Twitter开源的雪花算法。

雪花算法通过64位有特殊含义的数字来组成id。

雪花算法

首先第0位不用。

接下来的41位时间戳。精度是毫秒,这个大小大概能表示个69年左右,因为时间戳随着时间流逝肯定是越来越大的,所以这部分决定了生成的id肯定是越来越大的。

再接下来的10位是指产生这些雪花算法的工作机器id,这样就可以让每个机器产生的id都具有相应的标识。

再接下来的12位序列号,就是指这个工作机器里生成的递增数字。

可以看出,只要处于同一毫秒内,所有的雪花算法id的前42位的值都是一样的,因此在这一毫秒内,能产生的id数量就是 2的10次方✖️2的12次方,大概400w,肯定是够用了,甚至有点多了。


但是!

细心的兄弟们肯定也发现了,雪花算法它算出的数字动不动就比上次的数字多个几百几万的,也就是它生成的id是趋势递增的,并不是严格+1自增的,也就是说它并不太适合于根据范围来分表的场景。这是个非常疼的问题。

还有个小问题是,那10位工作机器id,我每次扩容一个工作机器,这个机器怎么知道自己的id是多少呢?是不是得从某个地方读过来。

那有没有一种生成id生成方案,既能让分库分表能做到很好的支持动态扩容,又能像雪花算法那样并不依赖redis这样的第三方服务。

有。这就是这篇文章的重点了。


适合分库分表的uuid算法

我们可以参考雪花算法的实现,设计成下面这样。注意下面的每一位,都是十进制,而不是二进制。

适合分库分表的uuid算法

开头的12位依然是时间,但并不是时间戳,雪花算法的时间戳精确到毫秒,我们用不上这么细,我们改为yyMMddHHmmss,注意开头的yy是两位,也就是这个方案能保证到2099年之前,id都不会重复,能用到重复,那也是真·百年企业。同样由于最前面是时间,随着时间流逝,也能保证id趋势递增。

接下来的10位,用十进制的方式表示工作机器的ip,就可以把12位的ip转为10位的数字,它可以保证全局唯一,只要服务起来了,也就知道自己的ip是多少了,不需要像雪花算法那样从别的地方去读取worker id了,又是一个小细节。

在接下来的6位,就用于生成序列号,它能支持每秒钟生成100w个id。

最后的4位,也是这个id算法最妙的部分。它前2位代表分库id,后2位代表分表id。也就是支持一共100*100=1w张分表。


举个例子,假设我只用了1个分库,当我一开始只有3张分表的情况下,那我可以通过配置,要求生成的uuid最后面的2位,取值只能是[0,1,2],分别对应三个表。这样我生成出来的id,就能非常均匀的落到三个分表中,这还顺带解决了单个分表热点写入的问题。

如果随着业务不断发展,需要新加入两张新的表(3和4),同时第0张表有点满了,不希望再被写了,那就将配置改为[1,2,3,4],这样生成的id就不会再插入到对应的0表中。同时还可以加入生成id的概率和权重来调整哪个分表落更多数据。

有了这个新的uuid方案,我们既可以保证生成的数据趋势递增,同时也能非常方便扩展分表。非常nice。


数据库有那么多种,mysql只是其中一种,那其他数据库也是要求主键自增吗?


tidb的主键id不建议自增

tidb是一款分布式数据库,作为mysql分库分表场景下的替代产品,可以更好的对数据进行分片。

它通过引入Range的概念进行数据表分片,比如第一个分片表的id在0~2kw,第二个分片表的id在2kw~4kw。这其实就是根据id范围进行数据库分表

它的语法几乎跟mysql一致,用起来大部分时候是无感的。

但跟mysql有一点很不一样的就是,mysql建议id自增,但tidb却建议使用随机的uuid。原因是如果id自增的话,根据范围分片的规则,一段时间内生成的id几乎都会落到同一个分片上,比如下图,从3kw开始的自增uuid,几乎都落到range 1这个分片中,而其他表却几乎不会有写入,性能没有被利用起来。出现一表有难,多表围观的场面,这种情况又叫写热点问题。

写热点问题

所以为了充分的利用多个分表的写入能力,tidb建议我们写入时使用随机id,这样数据就能被均匀分散到多个分片中。


用户id不建议用自增id

前面提到的不建议使用自增id的场景,都是技术原因导致的,而下面介绍的这个,单纯是因为业务。

举个例子吧。

如果你能知道一个产品每个月,新增的用户数有多少,这个对你来说会是有用的信息吗?

对程序员来说,可能这个信息价值不大。

但如果你是做投资的呢,或者是分析竞争对手呢?

那反过来。

如果你发现你的竞争对手,总能非常清晰的知道你的产品每个月新进的注册用户是多少人,你会不会心里毛毛的?

如果真出现了这问题,先不要想是不是有内鬼,先检查下你的用户表主键是不是自增的。


如果用户id是自增的,那别人只要每个月都注册一个新用户,然后抓包得到这个用户的user_id,然后跟上个月的值减一下,就知道这个月新进多少用户了。

同样的场景有很多,有时候你去小店吃饭,发票上就写了你是今天的第几单,那大概就能估计今天店家做了多少单。你是店家,你心里也不舒服吧。

再比如说一些小app的商品订单id,如果也做成自增的,那就很容易可以知道这个月成了多少单。

类似的事情有很多,这些场景都建议使用趋势递增的uuid作为主键。

当然,主键保持自增,但是不暴露给前端,那也行,那前面的话,你当我没说过


总结

  • 建表sql里主键边上的AUTO_INCREMENT,可以让主键自增,去掉它是可以的,但这就需要你在insert的时候自己设置主键的值。

  • 建表sql里的 PRIMARY KEY 是用来声明主键的,如果去掉,那也能建表成功,但mysql内部会给你偷偷建一个 ROW_ID的隐藏列作为主键。

  • 由于mysql使用B+树索引,叶子节点是从小到大排序的,如果使用自增id做主键,这样每次数据都加在B+树的最后,比起每次加在B+树中间的方式,加在最后可以有效减少页分裂的问题。

  • 在分库分表的场景下,我们可以通过redis等第三方组件来获得严格自增的主键id。如果不想依赖redis,可以参考雪花算法进行魔改既能保证数据趋势递增,也能很好的满足分库分表的动态扩容。

  • 并不是所有数据库都建议使用自增id作为主键,比如tidb就推荐使用随机id,这样可以有效避免写热点的问题。而对于一些敏感数据,比如用户id,订单id等,如果使用自增id作为主键的话,外部通过抓包,很容易可以知道新进用户量,成单量这些信息,所以需要谨慎考虑是否继续使用自增主键。

 



标签:主键 数据 算法
数据治理组织:不易建立 但太难转向?
« 上一篇 2022-06-20
  • 数据治理组织:不易建立 但太难转向?
    0阅读 0条评论 个赞
    公司在2022年工作会议上提出了“5141”数智驱动的商业智能总体框架(如下图所示),要求围绕顶层设计、组织保障、流程运营、IT支撑、数据安全治理、数据消费六个方面构建企业级数据治理体系,确保数据从产……
  • 为实战构建数据仓库指标体系!
    0阅读 0条评论 个赞
    作者:曹雷来源:滴滴技术01指标体系1.痛点分析主要从业务、技术、产品三个视角来看:业务视角业务分析场景指标、维度不明确;频繁的需求变更和反复迭代,数据报表臃肿,数据参差不齐;用户分析具体业务问题找……
  • Arch Linux超越Ubuntu成功登顶
    5阅读 0条评论 个赞
    Steam5月调查结果发布啦,本月Linux平台的用户百分比基础略有下降,而且最受欢迎的Linux发行版从UbuntuLTS转向了ArchLinux。Steam5月份的调查结……
  • 你还不明白分布式会话怎么解决?就看这个吧!
    14阅读 0条评论 个赞
    平常做的项目都是在一台应用系统,并且所有的操作都在一台Tomcat服务器上,并不会引发Session共享的问题,所以并不会对我们的系统产生影响,但是当我们部署多个微服务的时候,再搭配Nginx进行负载……
  • vivo集装箱集群监控系统的架构与实践
    0阅读 0条评论 个赞
    vivo互联网服务器团队-YuanPeng一、概述从容器技术的推广以及Kubernetes成为容器调度管理领域的事实标准开始,云原生的理念和技术架构体系逐渐在生产环境中得到了越来越广泛的应用实践。……
发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表
  • 深入了解前端路由哈希和历史的区别
    1阅读 0条评论 个赞
    前言没了解这两种路由前,不管是vue还是react在项目创建时难免会进行路由之间选择,在hash和history难免会纠结一番,或者是直接稀里糊涂用了默认带#的hash路由,看完这篇分享,保准让你之后……
  • 如何在Mac上愉快地使用Docker
    0阅读 0条评论 个赞
    一、目标任务首先要明确的是,作为了一个每天在LinuxServer上rm-rf的人来说,如果想在Mac上使用Docker,最舒服的也是兼容所有dockercli命令行操作……
  • 数据治理组织:不易建立 但太难转向?
    0阅读 0条评论 个赞
    公司在2022年工作会议上提出了“5141”数智驱动的商业智能总体框架(如下图所示),要求围绕顶层设计、组织保障、流程运营、IT支撑、数据安全治理、数据消费六个方面构建企业级数据治理体系,确保数据从产……
  • 谈谈MySQL的10大经典错误
    0阅读 0条评论 个赞
    今天就给大家列举MySQL数据库中,最经典的十大错误案例,并附有处理问题的解决思路和方法,希望能给刚入行,或数据库爱好者一些帮助,今后再遇到任何报错,我们都可以很淡定地去处理。学习任何一门技术的同……
  • 是时候告别Linux 5.17内核系列了
    0阅读 0条评论 个赞
    是时候告别Linux5.17内核系列了,因为它现在在kernel.org网站上被标记为EOL(End-of-Life),这意味着它将不再接收维护更新。LinuxKernel5.17于……
  • 收藏!四种异常检测方法综述
    0阅读 0条评论 个赞
    作者丨Ai,来源丨宅码编辑丨极市平台本文收集整理了公开网络上一些常见的异常检测方法(附资料来源和代码)。不足之处,还望批评指正。一、基于分布的方法1.3sigma基于正态分布,3sigma准则认为超……
  • Linux程序编译过程的来龙去脉
    1阅读 0条评论 个赞
    大家肯定都知道计算机程序设计语言通常分为机器语言、汇编语言和高级语言三类。高级语言需要通过翻译成机器语言才能执行,而翻译的方式分为两种,一种是编译型,另一种是解释型,因此我们基本上将高级语言分为两大类……
  • 为什么NodeJS是构建微服务的最佳选择?
    8阅读 0条评论 个赞
    作者|RonFybish译者|Sambodhi策划|闫园园什么是微服务微服务是一种应用架构,它将每个应用功能都放在自己的服务中,与其他服务隔离。这些服务是松散耦合的,可独立部署。这种架构……
  • 数据库主键一定要自己增加吗?有哪些不推荐自我增加的场景?
    0阅读 0条评论 个赞
    我们平时建表的时候,一般会像下面这样。CREATETABLE`user`(`id`intNOTNULLAUTO_INCREMENTCOMMENT'主键',`name`char(10)NOTNULLDE……
  • 用Docke r构建自己的dns服务器
    0阅读 0条评论 个赞
    在开发运维过程中经常需要自定义一个域名来管理服务,通常的方法是修改hosts文件,但还有一种更便捷的方法,从源头上解决问题,也就是使用DNS来实现。1.搭建搭建依然使用docker,安装前请安装好do……
  • 代码注释的艺术 优秀的代码真的不需要注释吗?
    0阅读 0条评论 个赞
    前言前天回家路上,有辆车强行插到前面的空位,司机大哥吐槽“加塞最可恶了”,我问“还有更可恶的吗”,司机大哥淡定说道“不让自己加塞的”。似乎和我们很类似,我们程序员届也有这2件相辅相成的事:最讨厌别人不……
  • 夯实基本功 深刻理解TLB原则
    0阅读 0条评论 个赞
    今天分享一篇TLB的好文章,希望大家夯实基本功,让我们一起深入理解计算机系统。TLB是translationlookasidebuffer的简称。首先,我们知道MMU的作用是把虚拟地址转换成物理地……
  • Java代码技巧将效率提高一千倍
    0阅读 0条评论 个赞
    前言代码优化,一个很重要的课题。可能有些人觉得没用,一些细小的地方有什么好修改的,改与不改对于代码的运行效率有什么影响呢?这个问题我是这么考虑的,就像大海里面的鲸鱼一样,它吃一条小虾米有用吗?没用,……
  • 太好了 一些高性能使用Python的技巧!
    1阅读 0条评论 个赞
    1.易混淆操作本节对一些Python易混淆的操作进行对比。1.1有放回随机采样和无放回随机采样importrandomrandom.choices(seq,k=1)#长度为k的lis……
  • 优雅地处理重复请求(并发请求)
    1阅读 0条评论 个赞
    利用唯一请求编号去重业务参数去重计算请求参数的摘要作为参数标识继续优化,考虑剔除部分时间因子请求去重工具类,Java实现总结对于一些用户请求,在某些情况下是可能重复发送的,如果是查询类操作并无大碍,但……
  • 2022年大数据五大发展趋势
    12阅读 0条评论 个赞
    2021年,我们看到围绕现代数据栈的兴起出现了相当大的加速效应。我们现在有一个海啸般的通讯、影响者、投资者、专门的网站、会议和活动来宣扬它。围绕现代数据栈的概念(尽管仍处于早期阶段)与云中数据工具的爆……
  • 好的架构不是设计出来的 是进化出来的~
    0阅读 0条评论 个赞
    大家好,我是飘渺。各位肯定都听过这样一句话:"好的架构不是设计出来的,而是演进出来的,没有完美的架构,只有不断演变、不断完善的架构。"今天我们来看一下1号店App服务端架构改造的例子,来具……
  • 有效的数据治理 如何管理元数据?
    5阅读 0条评论 个赞
    本期作者沈汪洋哔哩哔哩资深开发工程师负责B站数据平台工具侧元数据、数据运营、数据管理等业务方向,专注于元数据采集、血缘应用、数据地图、建模工具、治理工具等工具或产品功能的落地和推广。背景介绍元数据是数……
  • MySQL为什么不能用uuid作为主键?
    12阅读 0条评论 个赞
    前言在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一,单机递增),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为……
  • 架构进化 本文终于说清楚了~
    1阅读 0条评论 个赞
    相信大家都听说过单体和我服务,那么,单体到微服务架构的数据是如何演进的呢?这是个有意思的话题。今天,我们一起来看看,希望大家都有收获和启发。京东白条的快速发展满足了当前人们日益增长的消费需求。在京东商……
最近发布资讯
更多