数据库设计经验

青旅半醒 2022-02-28 07:22 604阅读 0赞

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

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

## 一、数据库设计框架思维 ##

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

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

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

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

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

注：第三范式(Third Normal Form,3rd NF)就是指表中的所有数据元素不但要能惟一地被主关键字所标识,而且它们之间还必须相互独立,不存在其他的函数关系。

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。

![640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1][]

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

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. 整数类型的具体选择，下面单独讲解

## 四、 整数型字段选择规范 ##

<table> 
 <tbody> 
 <tr> 
 <th> 类型 </th> 
 <th> 占用字节 </th> 
 <th> 范围 </th> 
 </tr> 
 <tr> 
 <td> tinyint </td> 
 <td> 1 </td> 
 <td> -128~127 </td> 
 </tr> 
 <tr> 
 <td> smallint </td> 
 <td> 2 </td> 
 <td> -32768~32767 </td> 
 </tr> 
 <tr> 
 <td> mediumint </td> 
 <td> 3 </td> 
 <td> -8388608~8388607） </td> 
 </tr> 
 <tr> 
 <td> int </td> 
 <td> 4 </td> 
 <td> -2147483648~2147483647 </td> 
 </tr> 
 <tr> 
 <td> bigint </td> 
 <td> 8 </td> 
 <td> +-9.22*10的18次方 </td> 
 </tr> 
 </tbody> 
</table>

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

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

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

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

注：direction表示收支方向， 就收，支，平，三个值，直到[沧海桑田][Link 1]，[海枯石烂][Link 2]也是三个值，所以一定用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

[640_wx_fmt_png_tp_webp_wxfrom_5_wx_lazy_1]: /images/20220228/92f90a6bb3064a47b1825819e7b1ec78.png
[Link 1]: https://www.baidu.com/s?wd=%E6%B2%A7%E6%B5%B7%E6%A1%91%E7%94%B0&tn=24004469_oem_dg&rsv_dl=gh_pl_sl_csd
[Link 2]: https://www.baidu.com/s?wd=%E6%B5%B7%E6%9E%AF%E7%9F%B3%E7%83%82&tn=24004469_oem_dg&rsv_dl=gh_pl_sl_csd
[--]: https://blog.csdn.net/ovejur/article/details/88787855