精华内容
下载资源
问答
  • 比如删除,避免通过主键删除,而是再加一个业务字段业务字段删除。

    比如删除,避免通过主键删除,而是再加一个业务字段用业务字段删除。

    展开全文
  • 1 用自动增长字段作为主键(一般多用int类型定义),也称为非业务主键(逻辑主键或者代理主键)意思就是这列数据与业务无关,仅仅作为主键而设计。  优点:自增长字段往往用integer bigint类型,最多占8个字节。...

    1 用自动增长字段作为主键(一般多用int类型定义),也称为非业务主键(逻辑主键或者代理主键)意思就是这列数据与业务无关,仅仅作为主键而设计。
        优点:自增长字段往往用integer bigint类型,最多占8个字节。索引与外键 所占用的空间连带减少,增删改查 效率高。业务变化,不影响,不需要更新主键。
        缺点:无法转移数据库,比如把表中的一批数据 转移 或 附带到 另一个表中,那么由于是自增长字段,那么会导致无法转移,因为另外一个表可能已经存在部分数据,会造成主键冲突。自增长字段的缺陷。业务数据的完整性,无法保证。
    2.用业务字段做主键。
        优点:可以转移数据库,最大化节省了空间,因为并没有多增加一个非业务字段做主键。可以保证业务逻辑的完整性。避免产生垃圾数据,银行就是用业务字段做主键的,虽然效率低,但是安全。
        缺点:如果业务发生改变,有可能需要修改主键,举例:国家A表用身份证号做主键,然后其他很多表中的身份证号这列都是来自身份证表A中的主键(即外键),那么如果身份证号升级,比如从1代升级到2代,那么连带的表的外键 的索引 通通都得发生变化,效率极低 因为会连带更新一串用到这个外键的表,可见用业务字段做主键的话,要保证主键不经常变化。
        总结:用哪种方式作为主键,还要看业务需求,实际情况,无固定标准。

    展开全文
  • 详细说说业务主键和非业务主键

    千次阅读 2020-08-21 17:50:53
    挖坑待填,先挖个坑,稍后来填。。 数据库设计,为什么要有业务主键和非业务主键,各自的应用场景是什么。

    什么是业务主键和非业务主键

    主键,就是指在数据表中,可以唯一表示一条数据的字段。例如表记录的序号,或者订单表里的订单号等。
    业务主键,就是从业务层面上来反应数据等唯一性,比如订单号,员工编号,商品编号等。这些编号一般都会包含一定的业务含义。比如订单号会拼入下单日期时间,员工编号可能拼入入职时间,商品编号会拼入商品分类缩写等等。并且为了能统一维护,业务主键一般是按照规则生成的定长的字符串。
    非业务主键,就是和业务无关,真正的表数据的唯一标记。一般会采用bigint类型的自增列作为非业务主键。非业务主键是数据表真正意义上的主键。主键索引也是基于这个非业务主键来生成的。

    为什么要有非业务主键,业务主键为什么不能取代非业务主键

    下面我以mysql为例,讲一下我对于这两个问题的看法。

    B+树

    要说明这两个问题,就必须说到索引的存储结构B+树。

    我们都知道,mysql的索引都是以B+树的形式存储在内存中。关于B+树的说明,网上有很多,我这边就只做简单的必要说明。忘记的,可以参照下面这个文章。

    一文彻底搞懂MySQL索引

    B+树是聚簇索引,即所有的数据都会在叶子结点中存储,而枝节点只会存储子节点的引用地址。这是B+树的特地昂,并且每个节点都是存储在磁盘中的同一页中。当我们需要查找数据的时候,其实将磁盘的某一页加载到内容中,然后进行处理。而B+树所有的数据行都是存储在叶子结点,即所有的查找都需要到达某一个叶子结点才能完成。于是对于B+树而言,每一次的查找,深度都是一样的。
    因为数据从磁盘加载到内存的时间,比在内存中查找数据的时间消耗多得多,所以,就必须减少B+树的查找深度。换句话说,如何让枝节点尽量的多存储数据,才是提高查询性能的关键因素。
    在这里插入图片描述

    以上图这个B+树为例,假设枝节点只能存储三个引用,那么存储45个数据需要三层。查找数据时,从根节点到叶子结点需要三次加载磁盘数据到内存中,最后在叶子结点中找到数据。

    但是如果一个枝节点可以存储9个引用,那么B+树的深度就变为2,查找数据只要加载两次磁盘数据到内存即可完成。
    在这里插入图片描述

    或者说,枝节点不变,数据大小减小,使得一个叶子结点可以存储15条数据,也可以将B+树的深度降到两层。
    在这里插入图片描述

    基于上述原理,mysql在设计的时候,使用B+树,在枝节点不存储数据,只存储引用,尽量减少数据大小,增加枝节点的数据个数。到这里,你就该明白,为什么B+树不在枝节点中存储数据了吧。

    主键索引和辅助索引

    讲了索引的数据结构,我们再来说一下主键索引和辅助索引。主键在建立时,就自带了主键索引。但是一个表里面,不可能只有主键索引,还有很多辅助索引。比如用户表,在建立时,除了对主键建立主键索引,我们还会对省市区来做索引,或者对用户的一些属性做索引。那么如果每个索引都按照B+树的原理,在叶子结点存储全量表数据,那么在磁盘中不就有了很多分数据的拷贝?这无疑浪费了内存。

    事实上,mysql在实现的时候,区分了主键索引和辅助索引。只有主键索引的叶子结点存储了真实数据,而辅助索引的叶子结点存储的是主键值。于是,这又引发了另外一个问题,所有的查询,最终都是要走到主键索引上。即使是按辅助索引查找数据,找到了主键值之后,还是要按主键值去主键索引中找到真正的数据,于是主键索引的性能尤为重要。

    解答

    有了上面的知识做铺垫,我们再来解答为什么要有非业务主键,以及为什么不能用业务主键代替非业务主键。

    为什么有非业务主键

    我们在建表的时候,一般都会键一个int(或者Bigint)类型的字段,并且设置为自增,并将这个字段设置为主键。这就是我们说的非业务主键,这个值其实没有业务含义,只是作为数据表的真实主键存在。下面说明几个问题:
    1,为什么是int类型
    int类型占4个字节,表示范围为:-2147483648~2147483647,如果我们只用正数部分,也可以表示21亿以上的数据。
    而如果采用varhcar型,"2147483647"这个字符串占据的字节数远远超过4个字节(具体多少依据字符集而定)。
    根据上面的知识点,我们可以知道,辅助索引的叶子结点存储的都是主键值,如果主键采用varchar类型,那么将大大减少辅助索引的叶子结点数据量,消耗查询性能。

    2,为什么是自增
    关于这个问题,需要了解B+树的插入原理。B+树在插入时,会先按照查找过程,查到这个数据的位置,然后将数据插入到指定位置。插入完成后,如果叶子结点数据达到最大,则会触发分裂逻辑,将叶子结点的数据对半分裂成两个叶子结点,并重新整理枝结点。具体可以参照我的这篇文章

    mysql对B+树插入逻辑的优化

    因为对半分裂会引发存储浪费,所以mysql对B+树的插入做了优化,优化后,新插入的结点引发分裂,如果是最后,则把新插入的结点写到新的叶子结点,否则,把新结点插入到后面一个叶子结点中。插入后,后继叶子结点达到饱和,则继续分裂。最后调整枝结点。

    因为B+树是一个有序树,所以如果每次加入的都是最大值,那么叶子结点达到最大时,只会分裂最后的叶子结点,并调整枝结点引用。

    但是,如果插入的数据不是有序的,而是乱序的,那么下一个结点如果定位到中间的叶子结点,并触发了结点分裂。假设这个叶子结点后面的叶子结点全部达到饱和,则会触发很多的叶子结点的分裂合并,拖慢索引的插入效率。

    因此,索引值采用自增,是为了提高索引的插入效率。特别是当数据了巨大时,效率提升尤为明显。

    为什么业务主键不能取代非业务主键

    基于上面为什么要有非业务主键,我想这个问题就迎刃而解。因为业务主键,因为需要包含业务含义,所以只能是varchar类型,并且是按照一定规则生成的,必定是无序的。这两点,都是不适合作为表数据的主键的原因。也决定了业务主键不能代替非业务主键,作为数据表的真正意义上的主键。

    关于mysql的B+树的插入及分裂逻辑,先挖个坑,后面再做分享。

    以上内容,如有不正,还望指出。

    展开全文
  • 关于业务主键和逻辑主键的思考 这几天对逻辑主键业务主键和复合主键进行了一些思考,也在网上搜索了一下相关的讨论,相关讨论可以看最下面的参考链接。下面是自己基于 SQL Server 的一些总结,其他数据库...

    关于业务主键和逻辑主键的思考

    这几天对逻辑主键、业务主键和复合主键进行了一些思考,也在网上搜索了一下相关的讨论,相关讨论可以看最下面的参考链接。下面是自己基于 SQL Server 做的一些总结,其他数据库(Oracle、MySQL、DB2、......)应该也类似吧。这个只是自己一时的思考,如有不当请告知,重新思考后再修正。

    定义(部分定义来源于 SQL Server 联机丛书):

    主键(PRIMARY KEY):表通常具有包含唯一标识表中每一行的值的一列或一组列。这样的一列或多列称为表的主键 (PK),用于强制表的实体完整性。

    外键(FOREIGN KEY):外键 (FK) 是用于建立和加强两个表数据之间的链接的一列或多列。在外键引用中,当一个表的列被引用作为另一个表的主键值的列时,就在两表之间创建了链接。这个列就成为第二个表的外键。

    聚集索引:聚集索引基于数据行的键值在表内排序和存储这些数据行。每个表只能有一个聚集索引,因为数据行本身只能按一个顺序存储。

    非聚集索引:非聚集索引包含索引键值和指向表数据存储位置的行定位器。可以对表或索引视图创建多个非聚集索引。通常,设计非聚集索引是为改善经常使用的、没有建立聚集索引的查询的性能。

    自动编号列和标识符列:对于每个表,均可创建一个包含系统生成的序号值的标识符列,该序号值以唯一方式标识表中的每一行。

    业务主键(自然主键):在数据库表中把具有业务逻辑含义的字段作为主键,称为“自然主键(Natural Key)”。

    逻辑主键(代理主键):在数据库表中采用一个与当前表中逻辑信息无关的字段作为其主键,称为“代理主键”。

    复合主键(联合主键):通过两个或者多个字段的组合作为主键。

     

    原理分析:

    使用逻辑主键的主要原因是,业务主键一旦改变则系统中关联该主键的部分的修改将会是不可避免的,并且引用越多改动越大。而使用逻辑主键则只需要修改相应的业务主键相关的业务逻辑即可,减少了因为业务主键相关改变对系统的影响范围。业务逻辑的改变是不可避免的,因为“永远不变的是变化”,没有任何一个公司是一成不变的,没有任何一个业务是永远不变的。最典型的例子就是身份证升位和驾驶执照号换用身份证号的业务变更。而且现实中也确实出现了身份证号码重复的情况,这样如果用身份证号码作为主键也带来了难以处理的情况。当然应对改变,可以有很多解决方案,方案之一是做一新系统与时俱进,这对软件公司来说确实是件好事。

    使用逻辑主键的另外一个原因是,业务主键过大,不利于传输、处理和存储。我认为一般如果业务主键超过8字节就应该考虑使用逻辑主键了,因为int是4字节的,bigint是8字节的,而业务主键一般是字符串,同样是 8 字节的 bigint 和 8 字节的字符串在传输和处理上自然是 bigint 效率更高一些。想象一下 code == "12345678" 和 id == 12345678 的汇编码的不同就知道了。当然逻辑主键不一定是 int 或者 bigint ,而业务主键也不一定是字符串也可以是 int 或 datetime 等类型,同时传输的也不一定就是主键,这个就要具体分析了,但是原理类似,这里只是讨论通常情况。同时如果其他表需要引用该主键的话,也需要存储该主键,那么这个存储空间的开销也是不一样的。而且这些表的这个引用字段通常就是外键,或者通常也会建索引方便查找,这样也会造成存储空间的开销的不同,这也是需要具体分析的。

    使用逻辑主键的再一个原因是,使用 int 或者 bigint 作为外键进行联接查询,性能会比以字符串作为外键进行联接查询快。原理和上面的类似,这里不再重复。

    使用逻辑主键的再一个原因是,存在用户或维护人员误录入数据到业务主键中的问题。例如错把 RMB 录入为 RXB ,相关的引用都是引用了错误的数据,一旦需要修改则非常麻烦。如果使用逻辑主键则问题很好解决,如果使用业务主键则会影响到其他表的外键数据,当然也可以通过级联更新方式解决,但是不是所有都能级联得了的。

    使用业务主键的主要原因是,增加逻辑主键就是增加了一个业务无关的字段,而用户通常都是对于业务相关的字段进行查找(比如员工的工号,书本的 ISBN No. ),这样我们除了为逻辑主键加索引,还必须为这些业务字段加索引,这样数据库的性能就会下降,而且也增加了存储空间的开销。所以对于业务上确实不常改变的基础数据而言,使用业务主键不失是一个比较好的选择。另一方面,对于基础数据而言,一般的增、删、改都比较少,所以这部分的开销也不会太多,而如果这时候对于业务逻辑的改变有担忧的话,也是可以考虑使用逻辑主键的,这就需要具体问题具体分析了。

    使用业务主键的另外一个原因是,对于用户操作而言,都是通过业务字段进行的,所以在这些情况下,如果使用逻辑主键的话,必须要多做一次映射转换的动作。我认为这种担心是多余的,直接使用业务主键查询就能得到结果,根本不用管逻辑主键,除非业务主键本身就不唯一。另外,如果在设计的时候就考虑使用逻辑主键的话,编码的时候也是会以主键为主进行处理的,在系统内部传输、处理和存储都是相同的主键,不存在转换问题。除非现有系统是使用业务主键,要把现有系统改成使用逻辑主键,这种情况才会存在转换问题。暂时没有想到还有什么场景是存在这样的转换的。

    使用业务主键的再一个原因是,对于银行系统而言安全性比性能更加重要,这时候就会考虑使用业务主键,既可以作为主键也可以作为冗余数据,避免因为使用逻辑主键带来的关联丢失问题。如果由于某种原因导致主表和子表关联关系丢失的话,银行可是会面临无法挽回的损失的。为了杜绝这种情况的发生,业务主键需要在重要的表中有冗余存在,这种情况最好的处理方式就是直接使用业务主键了。例如身份证号、存折号、卡号等。所以通常银行系统都要求使用业务主键,这个需求并不是出于性能的考虑而是出于安全性的考虑。

    使用复合主键的主要原因和使用业务主键是相关的,通常业务主键只使用一个字段不能解决问题,那就只能使用多个字段了。例如使用姓名字段不够用了,再加个生日字段。这种使用复合主键方式效率非常低,主要原因和上面对于较大的业务主键的情况类似。另外如果其他表要与该表关联则需要引用复合主键的所有字段,这就不单纯是性能问题了,还有存储空间的问题了,当然你也可以认为这是合理的数据冗余,方便查询,但是感觉有点得不偿失。

    使用复合主键的另外一个原因是,对于关系表来说必须关联两个实体表的主键,才能表示它们之间的关系,那么可以把这两个主键联合组成复合主键即可。如果两个实体存在多个关系,可以再加一个顺序字段联合组成复合主键,但是这样就会引入业务主键的弊端。当然也可以另外对这个关系表添加一个逻辑主键,避免了业务主键的弊端,同时也方便其他表对它的引用。

    综合来说,网上大多数人是倾向于用逻辑主键的,而对于实体表用复合主键方式的应该没有多少人认同。支持业务主键的人通常有种误解,认为逻辑主键必须对用户来说有意义,其实逻辑主键只是系统内部使用的,对用户来说是无需知道的。

    结论或推论:

    1、尽量避免使用业务主键,尽量使用逻辑主键。

    2、如果要使用业务主键必须保证业务主键相关的业务逻辑改变的概率为0,并且业务主键不太大,并且业务主键不能交由用户修改。

    3、除关系表外,尽量不使用复合主键。

    使用逻辑主键的最佳实践指南:

    1、足够用就好。系统使用的生命周期以100年为限,逻辑主键数据类型采用下表规则,如果不确定则使用int类型。

    数据量数据类型数据大小生成频率备注
    < 128tinyint1 字节1条/年频率过低,不太靠谱,不建议采用
    < 3 万smallint2 字节27条/月频率较低,慎用
    < 21 亿int4 字节40条/分钟能满足大部分情况
    < 922 亿亿bigint8 字节292万条/毫秒能满足绝大部分情况 
    >= 922 亿亿uniqueidentifier16 字节

    100亿用户同时每毫秒生成10亿条,可以连续生成10亿年

    可用于分布式、高并发的应用

    2、一般采用自增长方式或NewID()方式。

    3、主键字段名称一般采用“表名ID”方式,方便识别和表联接。

    4、如果表存在分布式应用,则可以考虑采用不同起始值,相同步长方式自增。例如有3个部署在不同地方的库,则可以如下设计:

    起始值步长
    110
    210
    310

    步长统一设置10是为了方便日后扩展,这样不同库之间也能保持主键唯一性了,也方便合并。

    5、如果存在高并发性需求或数据表迁移需求,可以考虑使用uniqueidentifier类型,并使用NewID()函数。

    6、可以考虑对业务主键建立唯一性索引,以实现业务主键唯一性的业务需求。

    7、如果需要考虑业务主键的性能需求,可以把业务主键建立聚集索引,而逻辑主键只建立主键约束和非聚集索引即可。

    8、关系表可以考虑采用复合主键方式,复合主键不用于实体表。

    参考:https://www.cnblogs.com/sparkbj/p/6015690.html

    展开全文
  • 读写比较多的操作,设置id作为自增主键
  • 搜索功能,或者oa系统的员工查询,这个时候,数据首先会在网站的系统中的数据库中,会先有一份,此时就肯定会有一个数据库的primary key(自增长,uuid,业务id),此时就比较适合采用数据在数据库中已有的primary...
  • 关于业务主键和逻辑主键

    千次阅读 2017-11-03 22:00:52
    业务主键(自然主键):在数据库表中把具有业务逻辑含义的字段作为主键,称为“自然主键(Natural Key)”。 逻辑主键(代理主键):在数据库表中采用一个与当前表中逻辑信息无关的字段作为其主键,称为“代理主键...
  • 缺点:无法转移数据库,比如把表中的一批数据 转移 或 附带到 另一个表中,那么由于是自增长字段,那么会导致无法转移,因为另外一个表可能已经存在部分数据,会造成主键冲突。自增长字段的缺陷。业务数据的完整性,...
  • 主关键字(PRIMARY KEY):主键是表中的一个或多个字段,它的值用于唯一地标识表中的某一条记录。 外键(FOREIGN KEY):如果公共关键字在一个关系中是主关键字,那么这个公共关键字被称为另一个关系的外键。由此可见...
  • 谈谈企业信息系统数据库设计是使用id主键还是uuid逻辑主键业务主键
  • 不用主键用来与子表关联,那就得新建一个字段,这值应该怎么赋?
  • 1、可以反过来思考,若使用业务相关的唯一标识做主键,小概率可能会因为业务场景的变更,此时的被认定为业务唯一标识的字段不唯一。虽然有点牵强,但是很多都基于这种考虑。 2、对于InnoDB来说,主键是聚簇索引,...
  • 原有的数据库表的设计中,很多表中都是用了联合主键 ,因为这样的设计符合业务上的概念,而且原生的jdbc数据库访问也不存在使用联合主键的困难。 但是,现在一切变了,一方面,我们享受者hibernate的面向对象的变量...
  • mysql在已有主键的表中新增自增长字段

    千次阅读 热门讨论 2019-09-08 21:11:32
    如标题所示,给数据表加自增长字段是很简单的事情,但是如果这个表已经有主键,并且存在数据的情况下,怎么给数据表加自增长主键呢?下面咱们来详细说一下这个东东。 1、目标需求: (1)数据表没有id字段,但是有个...
  • 1.任何一个字段自增长必须前提是本身的一个索引(key-栏有值),主键本身是一种索引。 -- 自增长 create table my_auto( id int auto_increment comment '自动增长', name varchar(10) not null )charset utf8; ...
  • 目的:一次偶然在把mysql数据同步到ES,同步时在不经意间在mysql修改了几数据信息,导致在同步的过程中,在ES中加入大量重复数据,但是因为主键是自动生成,所以存在主键不同,但是数据相同的数据,在项目展示测试中...
  • 这几天对逻辑主键业务主键和复合主键进行了一些思考,也在网上搜索了一下相关的讨论,相关讨论可以看最下面的参考链接。下面是自己基于 SQL Server 的一些总结,其他数据库(Oracle、MySQL、DB2、......)应该也...
  • 为什么非得用一个与业务无关的自增ID作为主键呢?他还没有我的业务编号作为主键查找方便呢。于是,我带着疑虑,去求证这一劝告的真实性。 一、从数据在数据库的存储角度来看,自增id 是int 型,一般比自定义的属性...
  • – 即使你有一百个理由要用关联主键, 你也能找到这唯一的一个理由放弃, 改而使用单一字段做主键. —— 在数据库设计中, 每一个表都应该有一个字段作为主键. 这个字段一般是自增整数字段, 或者某些数据库支持的...
  • 什么是逻辑主键业务主键

    千次阅读 2019-03-10 18:11:46
    定义: 逻辑主键(surrogate key):...业务主键(natrual key):有意义的字段,比如身份证 ID。也叫自然主键 维基百科介绍: 在关系数据库设计中,业务主键是一个由以及真实存在于世界中的属性构成的键。业务...
  • 逻辑主键业务主键和复合主键

    千次阅读 2018-07-17 00:09:49
    主键(PRIMARY KEY):表通常具有包含唯一标识表中每一行的值的一列或一组列。这样的一列或多列称为表的主键 (PK),用于强制表的实体完整性。 外键(FOREIGN KEY):外键 (FK) 是用于建立和加强两个表数据之间的链接的...
  • 问题:到底业务单号作为主键 还是 逻辑主键做为主键好? 业务主键的如果保证不变性,其实是可以的。但是有时候很难保证。例如身份证号其实也出现重复性问题。 使用逻辑主键会增加冗余字段。 目前建议使用逻辑主键...
  • 第一种情况:使用数据库表的主键做业务关联,order表存user_id,goods表存order_id;如果不是涉及到数据迁移,只是单库系统,这样做其实也没什么问题。 主键其实最原始的目的只是为了保证数据查找唯一,后面才是存储...
  • 字段属性--主键

    2017-09-28 10:38:00
    字段属性 ...增加主键SQL操作中有多种方式可以给表增加主键:大体可以分为三种方案1:在创建表的时间,直接在字段之后,跟primary key关键字(主键本身不允许为空) 优点:非常直接;缺点:只...
  • mysql为什么不建议使用订单号或者其他形式的业务单号作为主键? 目前我们电商平台的订单号,或者其他业务单号,为了保证唯一,多数都选择的是雪花算法snowflake或者其他变种来生成的。 生成分布式电商业务唯一id的...
  •  有些朋友可能不提倡数据库表必须要主键,但在我的思考中,觉得每个表都应该具有主键,不管是单主键还是双主键主键的存在就代表着表结构的完整性,表的记录必须得有唯一区分的字段主键主要是用于其他表的外键...
  • mysql数据库添加非主键自增字段

    千次阅读 2019-06-16 11:10:46
    前些天在项目上遇到一个坑,根据业务要求在指定的表中个不为空的自增非主键字段,我直接向表中添加自增的非主键字段时会报错,错误信息如下: 翻译上面错误提示:表定义不正确;只能有一个自动列,必须将其定义...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 94,763
精华内容 37,905
关键字:

业务字段做主键