数据结构(ER数据库)设计规范 原

表命名规范

表命名的规则分为3个层级,层级之间通过_分割,例如b_r_identityd_l_identity。规约为:

[leavel]_[type]_[name]

[leavel] 表示数据库表的层级和功能,分为:

  1. s:业务无关的系统数据表。
  2. d:业务字典表。
  3. b:基础业务表。
  4. v:视图。
  5. i:聚合中间表。

[type] 表示数据库表的类型,分为:

  1. r:行数据表。
  2. l:列数据表。
  3. g:分组数据表。

[name] 用来表示表的作用名称,由于mysql默认对大小写不敏感,采用下划线命名法。比如: identity_enterprise

因此,综合上面的规范,账号-账户管理模块命名为identity,相关的表命名为:

  • 模块中的数据字典以列数据的方式存储,名称为:d_l_identity_dc
  • 模块中的账号主表以行数据的方式存储,命名为:b_r_identity
  • 模块中的账户表以行数据的方式存储,名为:b_r_account
  • 模块中的账号企账扩展表以行数据的方式存储,名为:b_r_identity_enterprise

采用以上命名法的目的:

  1. 便于代码开发阶段区分表的功能和数据组织形式;
  2. 通过前缀为以后可能会引入的开源框架生成的表扩展命名空间。

比如引入了流程框架activity,会向数据库添加几十个表,其中有名为account的表,如果不适用前缀,会增加引入的成本。

字段命名规范

  1. 逻辑主键:id。所有的表必须创建逻辑主键。采用统一的主键便于分库分表以及数据抽取。
  2. 业务主键:code, 必须创建唯一索引。业务主键除了反应真实数据关联,也便于程序进行类型判断。
  3. 外键:columnName_fk,字段名+fk后缀,比如state_fk。
  4. 父主键关联:pid

行数据规范

所有的表必须包含modify_date、modify_type、modify_user、modify_access_id、activity字段。

  1. modify_date:标记数据修改时间,用于数据增量ETL或缺陷回溯。类型:TIMESTAMP(13)
  2. modify_type:数据修改类型,通常数据由运营后台修改OPR(0),或账号拥有这修改USR(1)。用于记录数据修改的行事人。类型:TINYINT(1)
  3. modify_user:结合modify_type,标记是修改人。类型:BIGINT
  4. modify_access_id:在数据库中用于标记当前数据修改是由哪个访问id导致的。类型:BIGINT
  5. activity:行数据标识符。用于标识行数据的作用范围,ACT(1)/DIS(2)/DEL(0),启用、停用、逻辑删除。类型:TINYINT(1)
  6. 所有的时间字段均以时间戳(Java十三位标准)的方式存储,Mysql对应TIMESTAMP(13)类型。

主键规范

逻辑(物理)主键使用64bit的BigInt类型,通过Snowflake算法获取。它可以完全充当Mysql主键,也能平滑兼容MyCatSharding-jdbc(3.0后更名为Sharding-Sphere)等开源分库分表数据源管理工具。

业务组件原则上不做任何关联查询,只用于标记单表业务内容。

采用该规范的原因请见后文主键规范设计背景及原因


主键规范设计背景及原因。

在分布式微服务系统中采用Mysql的自增主键在分表分库、灾备合库、分布式执行、缓存Write-Behind写时会有很大制约,因此需要制定不依赖数据库的行主键规范。

主键类型

在解释数据设计规范之前先理解物理主键逻辑主键业务主键的区别:

物理主键即认为是数据库的自身的物理标识主键,例如oracle的ROW_ID,mysql的自增Sequence,物理主键除了具备独立的物理特性,也是数据库连接数据的核心。mysql中要求单表唯一。

逻辑主键是与数据库无关的非业务意义的主键,用于对行数据的唯一性进行标识。在单数据库系统中,通常不需要逻辑主键,而在分布式系统中,逻辑主键的意义重大。无论是什么数据库,逻辑主键要求全库(所有的数据库)唯一。某些时候可以将物理主键和逻辑主键合二为一。

业务主键是指与含有业务特性的的主键,例如订单编号会以 时间+流水号+业务编号实行存在。业务主键通常的要求是单向业务唯一,由于从技术角度来说业务是随时可变的,因此业务主键并不能提到逻辑主键或物理主键。

MySql(InnoDB)索引特性

由于InnoDB的行数据排列是以主键数据(Oracle是ROW_ID)作为b+树索引,而扩展的索引都以主键索引作为数据对象——这种方式称为聚集索引。所以最大效率的保证b+树主键和索引数据进入的递增性对于数据库的性能有决定性作用(b+树越扁平,效率越高)。

使用mysql的自增Sequence可以很自然的解决这个问题,主键就向一个队列一样,只要insert数据向队列尾push数据即可,几乎不会发生索引重建和数据碎片。但是自增队在分布式系统中使用有巨大的局限性。

如果直接使用UUID既充当物理主键又充当业务主键,由于 UUID并无法保障数据的递增性(?),会导数据碎片已经主键索引更新效率。此外UUID的长度是32位字符串,即使用ascii的编码方案,也会占据不少的空间。

传统中间解决方案

基于Mysql目前也可以自动生成UUID,所以有一种中间解决方案是在分布式系统的数据库中物理主键使用Mysql的自增Sequence,逻辑主键使用UUID,所有的ER关联都使用UUID建立,这样可以很好的保障聚集索引添加数据的效率,且能极大减少碎片。由于InnoDB聚集索引除了主键索引都会引起二次查询,所以这种方式外关联效率较差(即使是单表查询效率也一般)。

主键需求

整合以上内容,现在我们需要一个具备以下特征的主键:

  1. 递增。
  2. 全系统唯一(至少保证单业务唯一)。
  3. 高效产生。
  4. 尽量短。(减少扩展索引的存储空间)

连续递增与趋势递增

对于B+树递增要求的并不需要连续递增(0,1,2,3,4......),只要趋势递增即可(0,3,5,7,18,100.....)。

Snowflake算法

为了满足主键需求,现在比较推崇的是Snowflake算法

Snowflake算法会产生一个64bit的数据,正好在Java中是一个long类型,对应Mysql是一个BigInt类型。

  1. 第一位是符号位(正负号)。在使用过程中基本不用理睬。
  2. 其后的41位表示时间戳的差值。
  3. 10位工作机id称为workid,需要人工指定。10bit=2^10=1024个Id
  4. 后续的12位用于在微秒级别生成序列号。

效率:

  • 因为其本质上还是一个数字,所以在关联查询能力上不会比源生的自增Sequence的差多少(微秒/纳秒级别)。
  • 官方文档Snowflake Id算法理论上单机每秒可以生成409.6万个ID——1000个微秒单位,12位序列编码=1000*(2^12)。

递增性质:

算法是以微秒+递增序列作为区分的,并且时间单位处于64bit中的高位,在所有的微服务节点没有达到生成极限时(每秒409.6万个)一定是趋势递增的,计时达到了极限,也仅仅在时间单位出现相同。

传输:

64bit的long类型转换为十进制只有20个数字,由于64bit的第一个位置表示符号,所以实际只有19个数字。在http报文中仅仅是19个字符。如果将其转换为16进制或[0~9a~z]满表的36进制。长度还能极大的压缩。

局限性:

  1. 由于其本质是基于微秒级的机器时间戳进行ID生成,所以当整个集群有时间一致性服务时候,可能会发生时间回拨(也有可能是人为修改,不过几乎不可能发生)。当时间发生回拨时就会有极大的概率在回拨时间区内出现主键冲突。百度有个Snowflake算法变种解决方案是使用中心化的按块生成ID尽可能的回避这个问题。此外如果并发并没有达到极高的程度时,可以让入口服务器来统一生成access_id作为后续业务新增数据时的主键,当然这也没法完全解决这个问题。
  2. 64bit的算法如果要求全系统主键唯一,那么基于算法的workid特性最大支持1024台服务器同时生成主键,再多就会出现冲突。解决办法就是不要求全系统唯一,而收敛为单个业务唯一,这样可以视为单个业务可以具有1024个分布式服务。
  3. 其数据位数决定了其从使用开始最多服务61年,61年后出现类似于千年虫的问题超出现有数据位。

参考

  1. Snowflake算法最早由推特twitter的工程师创立并开源,现在整合到RPC框架Finagle中,当然没必要引入整个Finagle,可以到这里下载核心算法
  2. Snowflake有个非常方便的使用途径是引入sharding-core。然后使用其中的SnowflakeShardingKeyGenerator。当然Snowflake算法本身并不复杂,使用源码就能解决问题,而且具有极佳的扩展性。
  3. 算法中workid最大支持1024,通常可以通过主动命名、ip地址、服务器命名等方式决定。
  4. 网上关于Snowflake算法的极少很多,自行查询解决问题。

(adsbygoogle = window.adsbygoogle || []).push({});

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券