高质量数据库建模系列课程<8> PPT & 讲义 -- 详解代理键和自然键 (二)

浏览: 3529

到2016-3-29日为止,已经有987人参与学习,课程至少保证每周一节,我也正在加快出课的速度,但前提还是要保证课程的质量。

课程的链接:http://www.hellobi.com/course/54

欢迎对数据库建模感兴趣的朋友参与我的课程。



大家好, 上一节课我们讲了一下自然键与代理键的区别和用法,但没讲完,这节我们接着讲。


上节课我们提到了金科玉律1,在维度表中,存在代理键也必须存在1:1的自然键。

这里我们再提一个,金科玉律2,代理键绝对不可以对用户可见,通常来说,我们在写SQL的时候都是把代理键作为Join的条件,而不能用于where的搜索条件。比如,像我们平时经常用的订单号,快递号,都有页面让我们直接输入这种号码然后做搜索,因为这些都是自然键,是具备商业意义的,是可以这么用的。而代理键是毫无商业含义的,我们千万别直接输入代理键作为搜索条件。代理键暴露给用户,只会给误导用户。如果用户习惯于使用代理键做查询会有什么隐患呢?因为代理键这种东西是会随着环境变化而变化的,它是系统自动生成的,一旦环境变更,比如你系统数据迁移了,相同的自然键换一个环境重新生成的代理键就不一样了。此外,我还要强调一点,因为我们说了,代理键不能作为像自然键那样搜索条件以及不能对最终用户可见,那么就需要我们在编程的时候特别注意这方面的问题,因此命名规则就非常重要,让编程人员看一眼字段名称,就能知道它是代理键,而不去乱用.


相比自然键,代理键还有哪些优点和缺点呢? 我们继续从程序编写的角度来看. 在大的事实表或者交易表里面, 我们会经常有这种需求,就是搜索某一个订单啊,或者电话号码所对应的信息, 而最终用户是非常偏爱这种搜索的, 而这事实表有这种自然键存在的话, 那查询就相对简单,就不用Join到维度表上, 看我左边的例子, 如果需要按手机号码进行搜索, 直接在事实表上查就行了, 不用关联维度表User. 那我们看右边的代理键的表,遇到这种直接需要搜手机号的需求就没有好办法,只能去Join维表。


因此,在这再讲一点设计的小技巧哈,这种情况下,就是对于高基数,并常用于直接搜索自然键的表,很多时候我们都会做一定的逆规范化,把手机号自然键加到事实表中做为常用的搜索条件。




那我们再看下一个场景,这个场景也是典型的维度事实星型结构,我们看左边,用自然键方式建模的。这里有一张表我们要特别注意一下,DIM_PRODUCT,这张表是复合键,其中PRODUCT_CD +EFFECTIVE_DATE,就是我们非常常用的用生效时间去保存历史版本。再看右面呢,每张维表都用代理键,我们也可以看到DIM_PRODUCT2,这张表是用一个代理键PRODUCT_SK作为主键,来代替原来的复合键。我这边只是举了一个表的例子,是为了看起来更简单一些,真实项目里面通常都比这复杂。


那我们来看这个SQL,看我画红框的部分,这里需要增加两个条件,就是对生效时间的查询控制。我这里举的例子很简单,所以这个SQL看起来也不复杂,而真实的项目中呢,如果复合键很多,并且大量使用生效时间这种字段,编程就会复杂的多,也容易出错。而且我这里特别强调了一点,当我们用报表工具的时候,有很多报表工具,比如BO,Cognos, BIEE这些,都要先创建语义层,然后语义层上再创建报表,所以查询的结构简单就会给这类工具减少很多麻烦。尤其项目规模庞大,用这种工具生成的SQL是非常难以优化的。另外还有就是Cube的建立,查询结构简单对Cube的实现也会方便很多。



我们再来看看查询性能的差异,我这里给了一个例子,这个是在postgreSQL里面建的两张表,两张表的数据量一样,都是437万,然后做了一个很简单的SQL,就是按两个字段做group by求一下sum, 然后返回同样条数的结果,我们可以看到,用自然键的模式,需要381秒,而用代理键的模式需要305秒,原因是自然键的方案里面,维度表里面存在复合键,以及自然键字符类型是存在varchar类型的。而代理键的方案呢,全是整型,这个对比可能不见得那么精确,但还是反应了一定的问题。基本上来说,如果基表的数据小于1万,甚至10万,这两种查询的差异都可以忽略不计。但如果规模上百万了,性能的差异就开始出现了,我这个例子里面呢,代理键的SQL性能大约比自然键的高25%,这个我感觉好像有点偏差, 通常也就15-20%,没有25%这么高,不过总体来说还是代理键性能更好一点。


然后再来看看存储空间方面,这两种方案有什么区别。对于维度表而言呢,自然键这个占用的更少,这个很容易理解哈,我们看上面这个例子,DIM_STORE这张表用的就是自然键,而DIM_STORE2用的就是代理键,因为DIM_STORE少一个派生出来的代理键, 所以显然它占的空间小,而DIM_STORE2呢,多一个代理键,因此这种方案维表占空间更多一些,另外,如果是大维度表,就是说维度表基数比较大的,比如客户,产品这类的。通常还会在自然键上建索引以方便搜索,这也会占一定的空间。但对数据仓库而言,其实大部分数据都在事实表里。所以说维度表多占用点空间无所谓哈。那我们在看事实表,左边的表用自然键,我画红框这部分,字符类型类型分别是char(6), char(2), date, char(10), 看右边的表,用代理键,字符类型分别是int4, int2, int4, int2。那我们对比一下总长度,FACT_SALES这四个字段用了26个字节,FACT_SALES2用了12个字节,那假设事实表有1000万条数据,那么FACT_SALES会比FACT_SALES2多占130M空间。130M空间,对于TB级别的数据仓库,几乎可以忽略不计了,如果上到10亿条数据,那么就占到13G,也不是很多。所以存储空间方面,除非是特别大的表,不用考虑太多。


我们再来看看数据加载方面,自然键和代理键的区别。就单纯的维度表而言,自然键的方式呢,都是从源系统直接过来的,没什么额外的开销。而代理键呢,需要生成代理键。那么主要问题其实集中在代理键怎么生成,以及开销有多大。

第一种方法,非常常用,就是数据库自动生成。这个,各个数据库的生成方法不一样,我这边用的postgreSQL的例子,它首先要建一个sequence,然后再建表的时候自动把default值赋成序列值,就会自动增长了。这个很简单哈,而且我这边测了一下性能,插入400万条数据,没发现两种方法有明显的性能差异。

而第二种方法呢,经常是在ETL的处理过程中,去生成代理键,这方法比较土,就是从对应的表里面取最大的代理键+1,再插入。

第二种办法如果对于大表而言,每次都取max,性能就比较差,因此就有第三种方法,建一张表,比如像我建的这张表,用table_name+column_name,去确定维度表对应的列,这个其实如果每个维度表只用一个代理键,不用column_name这个列也行。然后每次都计算next_key的值,然后赋给代理键,然后把next_key再加一,其实整体思路和方法二是差不多的。但两种方法都有问题,就是如果多并发去加载,比如同时有两个ETL进程,恰好同时读到了相同的next_key的值,这样最终插入的时候就会出现主键冲突。然后就有下一种方法,叫高低位法。这个其实也很简单,把代理键分成两段,比如同时有多个NODE进行加载,每个Node设置成一个值。

比如NODE 1 = 101, Node2 = 102, .., 以此类推,Node99 = 199,这个就构成高位。然后用NODE1加载的数据就是101 00000001, 102 0000002,这么排下来,就不会出现冲突了。

还有一种方法,叫UUID,但这个一般就不是整型了,都是char(64) char(32)这种东西,有单独的算法,使用网卡的mac地址+当前的时间戳计算出来的,这种方法可以充分并行运行。

上面说的这些都是对维度表而言的数据加载,那么对事实表呢?这个我暂时就不举例子了,这个牵扯到一些迟到维度,迟到事实之类的东西,因为事实表和维度表之间是有依赖关系的。这部分比较复杂,我会在维度建模里面单独讲这部分。



除了上面描述的场景,还有两个场景可能会用到代理键,我这边也提一下。

第一种,对于完全没有主键的数据,这个也是存在的,比如有些上游系统过来的流水,以文本文件格式传过来的,本身就没主键。我们是可以给加个代理键作为主键的,除了用于分布式数据库去做partition key,我暂时还没想到其他用法。

第二种,就是分布式结构的数据库,对于多个联合主键构成的事实表,你可能用哪个字段做partition key都不太合适,而且有的数据库有限制,比如Netezza中,作为partition key的字段组合不能超过四个。这个时候可能建一个完全没有意义的代理键,这个可不一定作为主键,只是用来分区的,可以避免分区的偏移。

好了,我们今天就讲到这,代理键和自然键的区别基本就讲完了,下节课我们讲约束的概念和例子。

推荐 6
本文由 BAO胖子 创作,采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者,并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点, 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台,并不用于任何商业目的,如果有任何问题,请及时联系我们,我们将根据著作权人的要求,立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

4 个评论

最近产出很高
BAO胖子

BAO胖子 回复 CCC

比我的计划已经慢很多很多了,我计划每周一个小时的课的,已经落后6个小时了
本周如约而至 :)
内容不错 说好的一周两课,希望能提高录制速度

要回复文章请先登录注册