#### 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次方 | **原则:** * 不浪费空间, 能用小的数据类型干嘛占用那么多空间 * 能用int,不影响业务理解,不用char * 方便以后扩容,支持以后扩容需求 * 注意int(1), int(10),没有存储空间上的差别,只是在补0查询是,占位达到的位数,所以空间还是类型的选择 例如: direction表示收支方向, 就收,支,平,三个值,直到沧海桑田,海枯石烂也是三个值,所以一定用tinyint(1),省空间,效率高; #### 6. 金融金额字段默认规范 * 用decimal来存储金额字段,不要用float和double,会出现数据精度丢失,不信可以自己试试 * decimal(M,N), * M和D的关系(存储规则M>=D(M必须大于等于D) * 整数位最大长度M-N位,小数位长度N位 * 占用字节数最大为M+2个字节数(保护-号和.的情况) * 金融金额字段建议DECIMAL(16,2)的数据类型,占用空间18个字节,小数保留到分,整数14位,最大可以表示十万亿级别的数据,DECIMAL(14,2)不建议,千亿级别不够玩 * 金额利率字段建议DECIMAL(10,6)的数据类型,小数点后保留6位,对应金额的计算是准确的,我多年的经验,一般展示会展示4位利率,这个业务层处理下就好 #### 7. 根据业务选择字段类型规范、默认值规范 * 一般扩展性有限的常量类型,建议用整形,性能高,占空间少,比如状态字段,初始设置值的时候,建议1,3,5,7这样,方便扩展 * 一般不确定扩展性的类型,建议用字符型,若订单子类型,字符型的好处,可以添加业务规则,一个字段可以同时表示出爷爷,儿子孙子的含义,比如 * subType=0000 0000 0000 前四个字段代表大类购买类型,中间代表子类购买,最后代表三级购买类型,这样通过一个字段可以做很多事情 * 序列号,id等字段,建议varchar(32)或者bigint * 如果未了方便查问题,且表的数据量不大,可以用有含义的英文单词表示常量,比如收入IN, 支出OUT, 数据库查下排查问题很方便 * 不要有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. 索引设计规范 #### 原则: * 根据业务需求设计索引 * 索引不要过多,影响插入更新性能 * 一个字段的值范围很小,不要设置索引,索引不生效同事浪费插入性能 * 如果有幂等性需求,设置数据库唯一索引 * null值对索引是一大伤害,所以不要让索引的列有null值存在 * 尽量加索引的字段的数据类型小,也就是能用整数不用varchar能用短的varchar不用长的varchar,不要在text上设置索引 #### 例子 如账户,userId设置为唯一索引,一个人只能有一个铜板账户 收支流水表的userID设置普通索,查询效率高 create_time 默认加索引。。。。 a,b,c 组合索引,索引构建是用a+b+c进行构建的索引,如果是btree索引,查询的时候 abc|ab|a三种查询条件都会走索引 不需要对a和ab重复创建索引 ,但是 like '%b'|'%bc'|'%c'不会走索引,所以,组合索引,一定使用频率高的放在最左边 #### 12. 数据加密规范 * 哪些字段需要加密? * 敏感字段,如姓名,身份证号,银行卡号,邮箱等 * 加密算法选择?对称还是非对称? * 根据实际业务进行选择,可以MD5,可以RSA,可以AES,具体选择后续,老夫出个介绍算法的 * 字段长度选择? * 字段长度要根据业务长度加密后长度进行设定,如果过长要选择换加密算法等 #### 13. 注释规范 * 每个字段要有注释 * 每个表名要有注释 * 字段的取值含义或者范围,枚举值要有注释 * 总之:看建表语句,看的爽,就对了 #### 14. 表字段顺序规范 * 你没有看错,是这个,表的字段顺序很重要 * 从前到后,按照字段的重要性和使用频率排列。 * 按字段的分类归集排列:如 金额相关的在一块,文案相关的在一块,时间相关的在一块等 * create_time,modify_time,remark三个字段在最后 #### 15. 数据库引擎的选择 Innodb,没的商量,就是这个,原因就一条,我们做的是金融行业,安全、安全、安全 必须选用innodb理由 1 Innodb支持事务,数据库崩溃恢复稳定 2 支持在线备份,方便运维工作 3 官方版本致力于优化innodb,后期功能和稳定性会越来越好 4 支持行锁,支持mvcc #### 16. 如何防止全表扫描 #### 原则: * 全表扫描会造成数据库挂掉,OOM,慢查询等可怕事情 * 索引的值范围很少,索引即使设计了也不会生锈,也会全表扫描 * 删数据时,where后必须有条件,条件走索引,同时加limit限制,防止sql错误,多删数据 **会出现全表扫描的情况,如下** * 未创建索引 例如: select * from tbname where name='?' 如果name字段未创建索引,那么就是全表扫描 * 隐式转换 例如:select * from tbname where id=1234; 如果id字段是varchar类型,那么就算id字段上建立有索引,也还是会走全表扫描。 * 索引区分度问题:select * from tbname where status=1 and name=? ,status,sex这类字段的重复值过低,索引区分度超过30以上, * 优化器会认为status索引效率低,如果name字段没有索引的话,就会全表扫描 * 索引字段上使用函数 select * from tbname where date(create_time)=? create_time字段上使用了函数,将不会走索引,可以改成 create_time=date_format(?)这种形式 * 联合索引字段顺序, a,b字段创建联合索引,select * from tbname where b=? 不符合左前缀原则, 单独使用b字段无法使用索引,可改成 index(b,a); **防止全表扫描的办法** DAOIMPL层面限制不走索引的查询sql DAOIMPL层面限制索引值少的查询sql 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.微信红包分表 (年度+用户分)