搜文章
推荐 原创 视频 Java开发 iOS开发 前端开发 JavaScript开发 Android开发 PHP开发 数据库 开发工具 Python开发 Kotlin开发 Ruby开发 .NET开发 服务器运维 开放平台 架构师 大数据 云计算 人工智能 开发语言 其它开发
Lambda在线 > 无刀流 > 架构核心的核心-数据库设计原则

架构核心的核心-数据库设计原则

无刀流 2018-02-28

       在IT领域,无论是传统IT还是互联网,或是未来已来的区块链,数据结构设计都是重中之重。我们读书的时候就知道,程序 = 数据结构 + 算法,而数据结构是在前面,也就表示了要先有米,才能煮饭。一个好的数据结构设计或者说数据库设计,不仅能应对复杂的业务变化,更可以应对未来海量的数据扩容,同时数据结构逻辑清晰更方便业务人员去使用数据,而对开发人员来说,看到好的数据库设计,就像欣赏到了一个芳龄美女出浴,吃了一顿美食,打新打到了一样,回味无穷。


        本文所介绍的数据库设计原则是根据学习工作中的经验总结来的,在工作中做为数据库设计天条来对待。适用场景:互联网高并发、高吞吐、海量数据、强一致性要求高、mysql数据库、互联网金融业务

当前浏览器不支持播放音乐或语音,请在微信或其他浏览器中播放 架构核心的核心-数据库设计原则


一、数据库设计框架思维

    1. 数据库设计第一原则,领域设计 + 恒等式设计 + 数据流转设计

    注:这三个思想后面会单独写篇文章来讲,非常非常重要的指导思想

    2. 领域驱动表设计,一个领域的属性放在一个表内

    注:自行车的归自行车,汽车的归汽车,一个表内不应有不在一个频道的字段

    3. 一套表的设计,最基本要满足第三范式

    注:百度哈

    4. 对于基础服务的表,要满足BC范式的要求

    注:基础服务表要求更高,在第三范式基础上,升级为BC范式

    5. 对互联网数据库设计,可以增加静态数据冗余

    注:互联网都是单表查询,静态数据冗余可以有效防止N+1操作,大大提高性能

    6. 数据库引擎必须选择Innodb

    注:(1)没的商量,就是这个,如果只给一个原因,就一条,我们是做金融行业,安全、安全、安全

          (2) Innodb支持事务,行级锁,并发性能更好,资源利用率更高,数据库崩溃恢复稳定
            (3) 支持在线备份,方便运维工作
            (4)官方版本致力于优化innodb,后期功能和稳定性会越来越好

            (5) 支持行锁,支持mvcc

    7. 字符集必须是UTF-8字符集

    注:什么国家字符都支持,不乱码,这就够了

    8. 严格禁止使用触发器、视图、存储过程

    注:(1) 互联网业务瞬息万变,数据库表结构变化频繁,用触发器,存储过程,视图作业做业务,后期的维护成本会高昂的吓死人,如果一个开发走了,都不知道影响到哪些业务,你说吓不吓人

           (2) 计算尽量移到应用层来计算,互联网DB是用来存储海量数据的,不是用来做数据计算的,分工明确,而且业务计算逻辑放到业务层,方便业务快速迭代,也可以通过加机器实现计算能力快速提升

    9. 每个字段要有注释,每个表名要有注释,字段的取值含义或者范围,枚举值要有注释,这些都要有中文注释

    注:有注释后来人才好维护,才好学习,不然都是坑,好的注释看着也爽啊


二、命名规范

    1. 表名:同一个应用(或领域)下的,要有相同的前缀,如:tb_share, tb_posi,tb_valu,风格 t_xxx。

架构核心的核心-数据库设计原则

    注:这样管理维护成本才低,认知理解成本也低,简单的例子就是统一制服,医生,护士,警察,卖保险等等,是不是听着还有点小兴奋

    2.  库名、表名、字段名:要字母小写加下划线风格,长度不能超过32个字符,禁止拼音加英文混合命名

    注:超过32个字符看起来太不舒服了,风格统一,不要另类

    3. 简洁、见名知意,

    注:csm代表渠道结算,全名channel settlement, 用全名会很长,csm简写会方便很多

    4. 专业,体现行业标准

    注:比如取现用withdraw;持仓用position;份额用share。因为我是做金融行业的,很多词汇都可以对应专业英文词汇,所以我们就不用自己造了。那当没有专业英语词汇时,我们也要尽量专业的去命名。这里,金融专业词汇推荐网站:MBA智库百科

    6. 索引命名规范:普通索引 idx_+字段名,主键索引 pk_+字段名,唯一索引 uk_+字段名

    注:这个就不用注了,一看就懂

    7. 连接数据库统一域名规范,不允许使用IP直连

    注: 开发环境:app.xxx.devdb

            测试环境:app.xxx.testdb

            线上环境:app.xxx.db

        一级从库加-s标志, 二级从库加-ss标志,依次递增

            一级从库:app.xxx-s.db

            二级从库:app.xxx-ss.db


当前浏览器不支持播放音乐或语音,请在微信或其他浏览器中播放 架构核心的核心-数据库设计原则 架构核心的核心-数据库设计原则


三、字段数据类型设计规范

    1. 如果需要时分秒时间记录,建议用datetime类型


    2. 如果需要的更多是日期的查询,建议用int型,不用DATE, 如20160909等,性能更高,空间更小


    3. 如果需要比时分秒更精确的时间记录,建议用long型,用应用生成时间戳,System.currentTimeMillis()进行存储


    4. 字符串存储能用varchar不要用text,varchar存储,搜索性能都高于text,text查询是会产生临时磁盘文件,性能差,如果长度超出了varchar长度,进行截取存储


    5. 数字类型选择,放弃float和double,全用decimal


    6. 放弃用BLOB二进制数据类型,如果涉及大数据存储,进行DB-索引-文件存储系统模式来处理


    7. 整数类型的具体选择,下面单独讲解

四、 整数型字段选择规范

类型

占用字节

范围

tinyint

1

-128~127

smallint

2

-32768~32767

mediumint

3

-8388608~8388607)

int

4

-2147483648~2147483647

bigint

8

+-9.22*10的18次方


1. 不浪费空间, 能用小的数据类型干嘛占用那么多空间

2. 能用int,不影响业务理解,不用char

3. 方便以后扩容,支持以后扩容需求

4. 注意int(1), int(10),没有存储空间上的差别,只是在补0查询是,占位达到的位数,所以空间还是类型的选择

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

五、金融金额字段默认规范

1、用decimal来存储金额字段,不要用float和double,会出现数据精度丢失

2、用decimal(M,N)

(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位利率,这个业务层处理下就好

3、对于精度要求更高的情况下,用int、或bigint存储金额,精度单独存一个精度字段


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

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

6、varchar长度超过255的时候用text,用varchar就没有意义了,建议varchar不要超过255

七、默认建表存在的字段规范

1、一个表必须要有的默认字段,这些字段可以排查问题,做变更记录,方便BI数据统计等

2、注意create_time一定要加索引,真的要加,必须加

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

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

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

3、create_time和modify_time建议在daoimpl层做时间处理,或者在sql层面做默认时间处理,可以完全保证准确没有脏数据,防止被误用


4、对于后台操作的系统,还需要有的字段

operator 操作人 operate_time 操作时间 等

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

version 版本号 seq序列号

八、数据加密规范

1、哪些字段需要加密?

    注:敏感字段,如姓名,身份证号,银行卡号,邮箱

2、加密算法选择?对称还是非对称?

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

3、字段长度选择?

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

九、表字段顺序规范

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

1. 从前到后,按照字段的重要性和使用频率排列

2. 按字段的分类归集排列:如 金额相关的在一块,文案相关的在一块,时间相关的在一块等

3. create_time,modify_time,remark三个字段在最后

十、主键ID规范

1. 每个表都应该设置一个ID主键,最好的是一个INT型,并且设置上自动增加的AUTO_INCREMENT标志,这点其实应该作为设计表结构的第一件必然要做的事!!

2. 个人强烈建议:自增id主键+全局唯一序列号, 全局唯一序列号(必须加唯一索引)

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

            同时自增主键ID,对于遍历需求来说很容易实现不重复不漏掉遍历

3. 唯一键设计推荐规范:

(1)全局自增id,通过db步长来做

(2)15位时间戳+业务标志+ip+分库+分表,生成业务含义全局唯一键

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


十一、索引设计规范

    1. 单表索引数目不能超过5个

    注:聚簇索引造成的存储和查询成本当索引过多时,性能降低很快

    2. 根据业务需求设计索引,如果没有查询需求,干嘛要去建索引呢

    3. 一个字段的值范围很小,不要设置索引,索引不生效同事浪费插入性能

    注:当索引的值就三五个,范围很小时,数据库进行的基本是全表扫描,没必要建索引

    4. 如果有幂等性需求,对需要幂等字段或字段组合,设置数据库唯一索引

    5. null值对索引是一大伤害,所以不要让索引的列有null值存在

    6. 尽量加索引的字段的数据类型小,也就是能用整数不用varchar能用短的varchar不用长的varchar,不要在text上设置索引

    7. 一个索引包含的字段数不能超过3个

    8. 尽量在静态数据上建立索引,频繁变动数据建索引,每次db都要考虑是否重建B+树

    

    注:

    (1)如账户,userId设置为唯一索引,一个人只能有一个铜板账户,静态数据

    (2)收支流水表的userID设置普通索,查询效率高,查询需求

    (3)create_time 默认加索引。。。。

    (4)a,b,c   组合索引,索引构建是用a+b+c进行构建的索引,如果是btree索引,查询的时候  abc|ab|a三种查询条件都会走索引,不需要对a和ab重复创建索引 ,但是 like '%b'|'%bc'|'%c'不会走索引,所以,组合索引,一定使用频率高的放在最左边    


十二、数据库容量设计规范

1. 根据业务发展预估,数据库容量设计要满足支撑未来3--5年数据增加需求,同时设计之初要考虑后续扩容很方便

    注:不给后人挖坑,做一个栽树的前人

2. 深入理解业务,根据业务特性来分表扩容,同时根据查询需求来分表

案例:

(1)代金券分表     (季度分)

(2)每日收益分表   (userId取余)

(3)对账分表     (按月分)

(4)微信红包分表   (年度+用户分)

3. 具体分表策略,见前面一篇文章


十三、防止全表扫描规范做法

1. 原则:

(1)全表扫描会造成数据库挂掉,OOM,慢查询等可怕事情

(2)索引的值范围很少,索引即使设计了也不会生锈,也会全表扫描

(3)删数据时,where后必须有条件,条件走索引,同时加limit限制,防止sql错误,多删数据

2. 会出现全表扫描的情况,如下

(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);


3. 防止全表扫描的办法

(1)DAOIMPL层面限制不走索引的查询sql

(2) DAOIMPL层面限制索引值少的查询sql

(3) mybatis.xml层面对删除进行限制Delete from tb_position where id=142343 limit 1;

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



十四、如何有效遍历

1. 有自增主键的遍历

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

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

缺点:暂时没发现


2. 带状态更新的遍历

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

解决办法:

(1)通过自增ID id>#lastId, limit 100, order没有自增主键

(2)临时表, 存在新增数据的遍历,做临时表,临时表做业务重复判断。

(3)create_time 半开查询      create_time >= egtCreateTime and create_time<ltCreateTime


十五、看着设计好的一套表,感觉美,你神功大成


终于写完了,好久没写了,思路不是很流畅,前后花了3个多小时,很多地方可能有疏漏和不足的地方,请围观群批评指正,不吝赐教。好久没写了,通过今天看的出,还是要没事多写写,锻炼锻炼文笔,不然写的就跟流水账是的,没有情调,写的速度也慢,浪费太多时间。不说了,太困了,睡去了


长按扫描二维码来打赏,感谢支持


版权声明:本站内容全部来自于腾讯微信公众号,属第三方自助推荐收录。《架构核心的核心-数据库设计原则》的版权归原作者「无刀流」所有,文章言论观点不代表Lambda在线的观点, Lambda在线不承担任何法律责任。如需删除可联系QQ:516101458

文章来源: 阅读原文

相关阅读

关注无刀流微信公众号

无刀流微信公众号:huangqun-1989

无刀流

手机扫描上方二维码即可关注无刀流微信公众号

无刀流最新文章

精品公众号随机推荐