数据库设计规范，如何去设计数据库，数据库设计所遵循的原则

数据库设计规范

1. 分析

• 为什么需要规范
• 如何去设计数据库
• 数据库设计的具体规范
• 数据库设计所遵循的原则

2 为什么需要规范

在互联网项目，分工明确，拆分细致， 但一个项目往往会涉及众多参与人员，包括架构师、高级工程师、研发工程师、测试工程师、DBA等不同角色。 数据库是一个项目的最底层设计， 如果设计不清晰，结构模糊， 规范混乱，是很难做成一个成功的项目，而这些需要我们从最基本的开始，对数据库有一套明确的设计规范。

3 如何设计

1. 传统数据库设计往往会遵循设计范式（三范式）， 随着不断发展， 范式设计往往难以满足现在项目的要求， 出现了反范式设计。

   • 范式设计：

     第一范式：数据库表中的每一列都是不可分割的基本数据项， 比如股票名称、股票代码。

     第二范式：数据库表所有属性都必须和主键有完全依赖关系， 比如用户编号为主键，那么与之依赖的有用户名、用户地址、用户性别和身份证等， 这些属性完全与具体用户产生依赖。

     第三范式：数据库表中属性不能存在传递关系， 比如A->B->C, 属性之间存在这样关系则不符合第三范式。再具体点， 比如股票表（股票编号， 股票名称，股票市场，股票交易手续费），这个表就不符合第三范式，因为股票交易手续费是依赖股票市场来确定， 需要再拆分为（股票编号， 股票名称）和（股票市场，股票交易手续费）多张表。

   • 反范式设计：

     范式设计虽然关系层次明确， 但是给业务查询带来了相当的复杂度，中间关系表膨胀， 查询需要连接更多的表， 导致性能严重下降。

     反范式并不是完全打破范式设计， 而是在范式设计与查询性能上找到平衡点，适合业务的设计才是好的设计。

2. 在实际业务中, 如何去设计呢? 做好需求分析， 理解业务流程， 弄清业务概念， 技术是生产工具， 服务于业务才能体现其价值，所以明白业务需求才知道怎么去设计。 业务设计三步法则：

   • 先拆解： 哪些是业务系统的核心模块， 核心模块具体包含哪些对象， 哪些又是非核心模块， 必须先列清楚。
   • 画关系： 将拆解的模块串接起来，形成一个整体， 哪些是依赖关系， 哪些是包含关系，哪些是聚合， 哪些又是组合。
   • 再细化：有上面的清晰脉络， 再去填充对象的属性， 仔细考量， 适当加入反范式设计， 冗余一些常用信息，这些就需再仔细去细化， 斟酌与权衡。

4 命名与表设计规范

​ 规范并不是千遍一律， 但要在自己的项目体系内形成一套有效规范， 以下规范可作参考：

1. 命名规范

   1.命名有意义，一眼知道这张表是干什么用的
   2.数据库，表都用小写
   数据库形如：backend
   数据表形如：client_device_info(客户端设备信息)，不要缩写，字母全小写
   3.索引命名以idx_为前缀
   4.命名不要过长（应尽量少于25字符）
   5.不要使用保留字
   6.同一字段在不同的表中也应是相同的类型和长度
   7.同一数据库下有不同的模块，可以考虑对表名用不同的前缀标识
   8.备份表时加上时间标识
   

2. 表设计规范

   1.如果没有特殊情况，建议选择InnoDB引擎
   2.每个表都应该有主键，可选择自增字段，或长整型字段。例外情况，如果有多个字段可以组成唯一， 并且需要频繁查询的， 那么可以采用复合主键，比如用户编号+交易商编号做为复合主键，因为可以确保不产生重复数据， 同时节省空间。 
   3.(不做强制要求)尽量将字段设置为NOT NULL。因为NULL值的存储需要额外的空间，且会导致比较运算更为复杂，会使得优化器更难以优化sql。空值是不占用空间, 但NULL列需要占用一个额外字节。
   4.使用更短小的列，比如整型列。整型列的执行速度往往更快。
   5.存储精确浮点数必须使用DECIMAL代替float和double。
   6.建议使用unsigned类型存储非负值
   7.建议使用 int unsigned存储ipv4
   8.整型定义中不添加显示长度的值，使用int，而不是int(4)
   9.尽可能不要使用text,blob类型
   10.varchar（n） n表示字符数而不是字节数，比如varchar（255）最大可存储255个汉字，需根据实际字符长度选择n的值。
   11.字符集建议选择utf-8
   12.存储年时使用year类型
   13.存储日期时使用date类型
   14.存储时间时，建议使用timestamp类型，因为timestamp使用的是4字节，datetime使用的是8字节。
   15.不要在数据库中使用varbinary或blob存储图片及文件，mysql 并不适合大量存储这类型文件
   16.join 操作的字段，在不同表中的类型及命名要一致
   17.如果更改表结构会影响性能，需要找DBA、表设计人员进行联合评审。
   

5 数据库设计原则

1. 核心原则

   不在数据库做运算; cpu计算务必移至业务层;

   控制每个表的列数量(字段少而精,字段数建议在20以内);

   平衡范式与冗余(效率优先；往往牺牲范式)

   拒绝3B(拒绝大sql语句：big sql、拒绝大事物：big transaction、拒绝大批量：big batch);

2. 字段类原则

   用好数值类型(用合适的字段类型节约空间);

   字符转化为数字(能转化的最好转化,同样节约空间、提高查询性能);

   避免使用NULL字段(NULL字段很难查询优化、NULL字段的索引需要额外空间、NULL字段的复合索引无效);

   少用text类型(尽量使用varchar代替text字段);

3. 索引类原则

   合理使用索引(改善查询,减慢更新,索引一定不是越多越好);
   字符字段大量重复属性时， 尽量建前缀索引（比如地区chinaXXX, alter table x_test add index(x_city(6)) ）; 不在索引做列运算;
   innodb主键推荐使用自增列(主键建立聚簇索引,主键不应该被修改,字符串不应该做主键)
   不用外键(由程序保证约束);

4. SQL类原则

   sql语句尽可能简单(一条sql只能在一个cpu运算,大语句拆小语句,减少锁时间,一条大sql可以堵死整个库);
   避免使用trig/func(触发器、函数, 采用程序取而代之);
   大批量SQL的更新操作要开启事务，越多效果性能越明显。