数据库设计规范

 

1. 数据库设计基本规范

---

• • 领域驱动表内容划分，一个领域内容的放在一个表内

• • 一套应用表或者一张表，最基本要满足第三范式要求

• • 对于基础服务的表，要满足BC范式的要求

2. 表名规范

---

• • 同一个应用(或领域)下的表，要有相同的前缀，如：tb_share, tb_position,tb_valuation

• • 简洁、见名知意， 如：csm代表渠道结算，全名channel settlement， 用全名会很长，csm简写会方便很多

• • 专业，因为我们是做金融的很多词汇都是有专业词汇的，建议多用专业词汇命名，推荐网站：MBA智库百科

3. 字段名规范

---

• • 专业、简洁 比如取现：withdraw，不用cash等，持仓是position，份额是share

• • 同一个系统中各个表中同样含义的字段，单词要一致，如用户ID，在很多表中都用user_id，就不要用member_id,一致性方便大家理解

4. 字段数据类型规范

---

• • 如果需要时分秒时间记录，建议用datetime类型
  • 如果需要的更多是日期的查询，建议用int型，不用DATE, 如20160909等，性能更高，空间更小
  • 如果需要比时分秒更精确的时间记录，建议用long型，用应用生成时间戳，System.currentTimeMillis()进行存储
  • 字符串存储能用varchar不要用text，varchar存储，搜索性能都高于text，text查询是会产生临时磁盘文件，性能差
  • 备注字段如果不是特别长，尽量用varchar不用text，在daoImpl层处理超出长度部分，进行截取存储
  • 数字类型选择，放弃float和double，尽量用decimal
  • 放弃用BLOB二进制数据类型，如果涉及大数据存储，进行DB-索引-文件存储系统模式来处理

  • 整数类型的具体选择，下面单独讲解

5. 整数型字段选择规范

---

类型

占用字节

范围

tinyint	1	-128~127
smallint	2	-32768~32767
mediumint	3	-8388608~8388607）
int	4	-2147483648~2147483647
bigint	8	+-9.22*10的18次方

原则：

1. 1. 不浪费空间, 能用小的数据类型干嘛占用那么多空间
   2. 能用int，不影响业务理解，不用char
   3. 方便以后扩容，支持以后扩容需求
   4. 注意int(1), int(10),没有存储空间上的差别，只是在补0查询是，占位达到的位数，所以空间还是类型的选择

例如：

     direction表示收支方向， 就收，支，平，三个值，直到沧海桑田，海枯石烂也是三个值，所以一定用tinyint(1)，省空间，效率高

. 金融金额字段默认规范

---

• • 用decimal来存储金额字段，不要用float和double，会出现数据精度丢失，不信可以自己试试
  • decimal(M,N)，

1. 1. M和D的关系（存储规则M>=D(M必须大于等于D)
   2. 整数位最大长度M-N位，小数位长度N位
   3. 占用字节数最大为M+2个字节数(保护-号和.的情况)

• • 金融金额字段建议DECIMAL(16,2)的数据类型，占用空间18个字节，小数保留到分，整数14位，最大可以表示十万亿级别的数据，DECIMAL(14,2)不建议，千亿级别不够玩
  • 金额利率字段建议DECIMAL(10,6)的数据类型，小数点后保留6位，对应金额的计算是准确的，我多年的经验，一般展示会展示4位利率，这个业务层处理下就好

7. 根据业务选择字段类型规范、默认值规范

---

1. 1. 一般扩展性有限的常量类型，建议用整形，性能高，占空间少，比如状态字段，初始设置值的时候，建议1,3,5,7这样，方便扩展
   2. 一般不确定扩展性的类型，建议用字符型，若订单子类型，字符型的好处，可以添加业务规则，一个字段可以同时表示出爷爷，儿子孙子的含义，比如
      subType=0000 0000 0000 前四个字段代表大类购买类型，中间代表子类购买，最后代表三级购买类型，这样通过一个字段可以做很多事情
   3. 序列号，id等字段，建议varchar(32)或者bigint
   4. 如果未了方便查问题，且表的数据量不大，可以用有含义的英文单词表示常量，比如收入IN， 支出OUT， 数据库查下排查问题很方便
   5. 不要有null值，不要有null值，不要有null值，建表是要default 一个默认值，尽量建表是not null语句，
      除了modify_time必须default null以外，其他都是not null

8. 默认建表存在的字段规范

---

• • 一个表必须要有的默认字段，这些字段可以排查问题，做变更记录，方便BI数据统计等
  • 同事注意create_time一定要加索引，真的要加，要加

`create_time` datetime NOT NULL COMMENT '表创建时间',

`modify_time` datetime DEFAULT NULL COMMENT '表修改时间',

`remark` varchar(64) DEFAULT NULL COMMENT '备注',

• • create_time和modify_time建议在daoimpl层做时间处理，可以完全保证准确没有脏数据，防止被误用

• • 对于后台操作的系统，还需要有的字段

operator 操作人 operate_time 操作时间 等

• • 对于事务要求强的业务表和一些变更记录表，需要有的字段

version 版本号 seq序列号

9. 主键ID规范

---

• • 每个表都应该设置一个ID主键，最好的是一个INT型，并且设置上自动增加的AUTO_INCREMENT标志，这点其实应该作为设计表结构的第一件必然要做的事！！
  • 个人强烈建议：自增id主键+全局唯一序列号， 全局唯一序列号(必须加唯一索引)

100xxxxxx-广东省,  200xxxxxx-黑龙江

遍历需求的对于自增ID的表来说很容易实现

 

• • 主键设计：

1.自增id， bigint

2.15位时间戳+业务标志+rand   32 (TSS可以生成全局唯一主键)，生成业务含义全局唯一主键，可以模仿订单服务ID生成器

3.分布式全局唯一， UUID或额外系统主键生成系统支持

10. 幂等性支持规范

---

• • 幂等性处理方法，可以找，技术开发中的坑，这个ppt里面有，在wiki
  • 如果数据表中记录在某个字段下或字段组合下是唯一的，需要加唯一索引，防止脏数据生成
  • 对外接口幂等性，设置source+seq两个字段为唯一索引再数据库中，保证幂等

11. 索引设计规范

---

原则：

1. 1. 根据业务需求设计索引
   2. 索引不要过多，影响插入更新性能
   3. 一个字段的值范围很小，不要设置索引，索引不生效同事浪费插入性能
   4. 如果有幂等性需求，设置数据库唯一索引
   5. null值对索引是一大伤害，所以不要让索引的列有null值存在
   6. 尽量加索引的字段的数据类型小，也就是能用整数不用varchar能用短的varchar不用长的varchar，不要在text上设置索引

例子

1. 1. 如账户，userId设置为唯一索引，一个人只能有一个铜板账户
   2. 收支流水表的userID设置普通索，查询效率高
   3. create_time 默认加索引。。。。
   4.  a,b,c   组合索引，索引构建是用a+b+c进行构建的索引，如果是btree索引，查询的时候  abc|ab|a三种查询条件都会走索引
      不需要对a和ab重复创建索引 ，但是 like '%b'|'%bc'|'%c'不会走索引，所以，组合索引，一定使用频率高的放在最左边

12. 数据加密规范

---

• • 哪些字段需要加密？

敏感字段，如姓名，身份证号，银行卡号，邮箱等

• • 加密算法选择？对称还是非对称？

根据实际业务进行选择，可以MD5，可以RSA，可以AES，具体选择后续，老夫出个介绍算法的

• • 字段长度选择？

字段长度要根据业务长度加密后长度进行设定，如果过长要选择换加密算法等

13. 注释规范

---

每个字段要有注释

每个表名要有注释

字段的取值含义或者范围，枚举值要有注释

总之：看建表语句，看的爽，就对了

14. 表字段顺序规范

---

• • 你没有看错，是这个，表的字段顺序很重要

1. 1. 从前到后，按照字段的重要性和使用频率排列。
   2. 按字段的分类归集排列：如 金额相关的在一块，文案相关的在一块，时间相关的在一块等
   3. create_time,modify_time,remark三个字段在最后

15. 数据库引擎的选择

---

Innodb，没的商量，就是这个，原因就一条，我们做的是金融行业，安全、安全、安全

 

 必须选用innodb理由
1 Innodb支持事务，数据库崩溃恢复稳定
2 支持在线备份，方便运维工作
3 官方版本致力于优化innodb，后期功能和稳定性会越来越好
4 支持行锁，支持mvcc

16. 如何防止全表扫描

---

• • 原则：

1. 1. 全表扫描会造成数据库挂掉,OOM,慢查询等可怕事情
   2. 索引的值范围很少，索引即使设计了也不会生锈，也会全表扫描
   3. 删数据时，where后必须有条件，条件走索引，同时加limit限制，防止sql错误，多删数据
      
      

• • 会出现全表扫描的情况，如下

1. 1. 未创建索引 例如： select * from tbname where name='?' 如果name字段未创建索引，那么就是全表扫描
   2. 隐式转换 例如：select * from tbname where id=1234; 如果id字段是varchar类型，那么就算id字段上建立有索引，也还是会走全表扫描。
   3. 索引区分度问题：select * from tbname where status=1 and name=？ ,status,sex这类字段的重复值过低，索引区分度超过30以上，
      优化器会认为status索引效率低，如果name字段没有索引的话，就会全表扫描
   4. 索引字段上使用函数 select * from tbname where date(create_time)=? create_time字段上使用了函数，将不会走索引，可以改成 create_time=date_format(?)这种形式
   5. 联合索引字段顺序， a,b字段创建联合索引，select * from tbname where b=? 不符合左前缀原则， 单独使用b字段无法使用索引，可改成 index(b,a);

• • 防止全表扫描的办法

1. 1. DAOIMPL层面限制不走索引的查询sql
   2. DAOIMPL层面限制索引值少的查询sql
   3. mybatis.xml层面对删除进行限制Delete from tb_position where id=142343 limit 1;
      
      

• • 下面的例子，大家可以借鉴下哈，哈哈哈哈，注意是对数量和索引的限制，防止慢查询和OOM
    
    

17. 如何遍历

---

• • 有自增主键的遍历

select * from table_xxx where status = #status and id > #lastQueryId limit 1000;

优点：性能高，不漏数据，遍历数据状态变更不会造成位移，每次取出最后一条ID，进行下一次查询，性能极高，屌屌的

缺点：暂时没发现

• • 是否是带状态更新的遍历：

会可能存在位移差，存在漏数据

解决办法：

1. id>#lastId, limit 100, order没有自增主键

2. Create_time 索引，  create_time>=#lastCreateTime, limit 100;支持幂等

3. 存在新增数据的遍历，做临时表，临时表做业务重复判断。

4. create_time 半开查询      create_time >= egtCreateTime and create_time<ltCreateTime

18. 如何分表

---

• • 深入理解业务，根据业务特性来分表，根据查询需求来分表

案例：

1.代金券分表     （季度分）

2.每日收益分表   （userId取余）

3.对账分表     （按月分）

4.微信红包分表   （年度+用户分）

 

欢迎大家关注我的公众号：