PS：从大学课设小项目到外包大型项目，再到实习时候参与过的千万级用户后台架构设计。对后端的数据库设计有了一些自己的心得体会，这里记录一些现在这个阶段的所想所得，后面随着level不断提升，持续更新。

为什么要进行数据库设计

因为这是一个：需求分析->逻辑设计->物理设计->维护优化的复杂过程。

需求分析：

• 数据是什么？
• 数据有哪些属性？
• 数据和属性各自的特点是？
• 实体间的关系（1对1，1对多，多对多）
• 实体唯一标识的属性（属性组合）

eg：
以一个小型的电子商务网站为例，在这个电子商务网站的系统中包括了几个核心模块：用户模块，商品模块，订单模块，购物车模块，供应商模块。

就用户模块讲：
- 包括属性：用户名、密码、电话、邮箱、身份证号、地址、姓名、昵称
- 可选唯一标识属性：用户名、身份证、电话
- 存储特点：随系统上线时间逐渐增加，需要永久存储。

就商品模块讲：
-包括属性：商品编码、商品名称、商品描述、商品种类、供应商名称、重量、有效期、价格...
-可选唯一标识属性：（商品名称，供应商名称）、（商品编码）
-存储特点：对于下线商品可以归档存储

就订单模块讲：
-包括属性：订单号、用户姓名、用户电话、收获地址、商品编号、商品名称、数量、价格、订单状态、支付状态、订单类型......
-可选唯一标识属性：（订单号）
-存储特点：永久存储（分表、分库存储）

...

其他模块省略分析

可以看出，其中：

一对多关系有2组：用户<->订单，用户<->购物车

多对多关系有3组：订单<->商品、商品<->购物车、商品<->供应商

逻辑设计：

使用ER图，进行数据库逻辑建模。

逻辑设计是做什么的？

• 1、将需求转化为数据库的逻辑模型
• 2、通过ER图的型式对逻辑模型进行展示
• 3、同所选用的具体的DBMS系统无关

名字解释

• 关系：一个关系对应通常所说的一张表
• 元祖：表中的一行即为一个元组。
• 属性: 表中的一列即为一个属性，每一个属性都有一个名称，称为属性名，
• 候选码：表中的某个属性组，TA可以唯一确定一个元组。
• 主码：一个关系有多个候选码，选定其中一个为主码。
• 域：属性的取值范围。
• 分量：元组中的一个属性值。

ER图例

数据库设计范式

数据库设计需要遵循这些范式规则，否则就很可能会出现如下异常：

操作异常

• 插入异常: 如果某实体随着另一个实体的存在而存在，即缺少某个实体时无法表示这个实体，那么这个表就存在插入异常。

• 更新异常: 如果更改表所对应的某个实体实例的单独属性时，需要多行更新，那么就说这个表存在更新异常。

• 删除异常：如果删除表的某一行来反映某实体实例失效时导致另一个不同实体实例信息丢失，那么这个表中就存在删除异常。

数据冗余：

• 是指相同的数据在多个地方存在，或者说表中的某个列可以由其他列计算得到。

范式

• 第一范式、数据库中的所有字段都是单一属性，不可再分的，这个单一属性是由基本的数据类型所构成的，如整数、浮点数、字符串、等；第一范式要求数据库中的表都是二维表。

• 第二范式、数据库的表中不存在非关键字段对任一候选关键字段的部分函数依赖。所有单关键字段的表都符合第二范式。（通常通过拆分表来达到满足范式要求）

  • eg：（商品名称）-> (价格、描述、重量、商品有效期)

    （供应商名称）->（供应商电话）

  存在两组依赖，有一组还是非关键字段，所以不满足。

  • 将存在的问题如下：
    1、插入异常
    2、删除异常
    3、更新异常
    4、数据冗余
• 第三范式、在第二范式的基础上定义的，如果数据表中不存在非关键字段，对任意候选关键字段传递函数依赖则符合第三范式。（通常通过拆分表来达到满足范式要求） - eg: (商品名称) -> (分类) -> (分类描述)
  也就是说存在非关键字段“分类描述”，对关键字段“商品名称”的传递函数依赖。所以不满足。
  • 将存在的问题如下：（分类、分类描述）对于每一个商品都会进行记录，所以存在数据冗余。同时也还存在数据的插入、更新及删除异常。
• BC范式（Boyce.Codd）、在第三范式的基础之上，数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖则附合BC范式。也就是说如果是复合关键字，则复合关键字之间也不能存在函数依赖关系。
  • eg: (供应商)->(供应商联系人)
    (供应商联系人)->(供应商)
    并且存在数据操作异常和数据冗余。
• PS：（第四、第五范式不做重点分析，自行百度）

物理设计：

• 1、选择合适的数据库管理系统，根据逻辑选择NoSQL或者是关系数据库等。
• 2、定义数据库、表及字段的命名规范。
• 3、根据所选的DBMS系统选择合适的字段类型。
• 4、反范式化设计。

关系数据库

Oracle、SQLServer常使用于企业级应用。
MySQL、PgSQL常用于互联网项目。

MySQL常用的存储引擎：

表及字段的命名规则

1、可读性原则

使用大写和小写来格式化的库对象名字以获得良好的可读性。例如：使用CustAddress而不是cutaddress来提高可读性。（这里要注意有些DBMS系统对表名的大小写是敏感的）

2、表意性原则

对象的名字应该能描述TA所标识的对象。例如：对于表，表的名称应该能够体现表中存储的数据内容；对于存储过程，存储过程名称应该能够体现存储过程的功能。

3、长名原则

尽可能少使用或者不使用缩写，使用于数据库（DATABASE）名之外的任一对象。

字段类型的选择原则

列的数据类型一方面影响数据存储空间的开销，另一方面也会影响数据查询性能。当一个列可以选择多种数据类型时，应该优先考虑数字类型，其次是日期或二进制类型，最后是字符类型。对于相同级别的数据类型，应该优先选择占用空间小的数据类型。

以上选择原则主要是从下面两个角度考虑：

• 1、在对数据进行比较（查询条件、JOIN条件及排序）操作时：同样的数据，字符处理往往比数字处理慢，
  • 2、在数据库中，数据处理以页为单位，列的长度越小，利于性能提升。

char和varchar如何选择

原则：

• 1、如果列中要存储的数据长度差不多是一致的，则应该考虑用char；否则应该考虑用varchar。

• 2、如果列中的最大数据长度小于50Byte，则一般也考虑用char。

• 3、一般不宜定义大于50Byte的char类型列。

decimal与float如何选择

原则：

• 1、decimal用于存储精确数据，而float只能用于存储非精确数据。

• 2、由于float的存储空间开销一般比decimal小（精确到7位小数只需要4个字节，而精确到15位小数只需要8字节），故非精确数据优先选择float类型。

时间类型如何存储

原则：

• 1、使用int来存储时间字段的优缺点

优点：字段长度比datetime小。
缺点：使用不方便，要进行函数转换。
限制：只能存储到2018-1-19 11：14：07即2^32

• 2、需要存储的时间粒度

年 月 日 小时 分 秒 周

如何选择主键

• 1、区分业务主键和数据库主键

业务主键用于标识业务数据，进行表与表之间的关联；
数据库主键为了优化数据存储（Innodb会生成6个字节的隐含主键）

• 2、根据数据库的类型，考虑主键是否要顺序增长

有些数据库是按主键的顺序逻辑存储的

• 3、主键的字段类型所占空间要尽可能的小

对于使用聚集索引方式存储的表，每个索引后都会附加主键信息。

避免使用外键约束

• 1、降低数据导入的效率。

• 2、增加维护成本。

• 3、虽然不建议使用外键约束，但是相关联的列上一定要建立索引。

避免使用触发器

• 1、降低数据导入的效率。

• 2、可能会出现意想不到的数据异常。

• 3、使业务逻辑变的复杂。

• 4、严禁使用预留字段。

反范式化

反范式化是针对范式化而言的，在前面介绍了数据库设计的第三范式，所谓的反范式化就是为了性能和读取效率的考虑而适当的对第三范式的要求进行违反，而允许存在少量的数据冗余，换句话来说反范式化就是使用空间来换取时间。

优点：

• 1、减少表的关联数量。

• 2、增加数据的读取效率。

• 3、反范式化一定要适度。

维护优化：

新的需求进行建表，索引优化，大表拆分

• 1、维护数据字典
  • 使用第三方工具对 数据字典进行维护（待补充）
  • 利用数据库本身的备注字段来维护数据字典。以MySQL为例：
  • 导出数据字典。

 CREATE TABLE customer(
cust_id INT AUTO_INCREMENT NOT NULL COMMENT '自增ID',
cust_name VARCHAR(10) NOT NULL COMMENT ' 客户姓名',
PRIMARY KEY(cust_id)
)COMMENT '客户表'

• 2、维护索引

如何选择合适的列做索引：

  - 出现在WHERE从句，GROUP BY从句，ORDER BY从句中的列。
- 可选择性高的列要放到索引的前面。
- 索引中不要包括太长的数据类型。

注意：

- 索引并不是越多越好，过多的索引不但会降低写效率而且会降低读效率。
- 定期维护索引碎片。
- 在SQL语句中不要使用强制索引关键字。

• 3、维护表结构
  • 使用在线变更表结构的工具，例如MySQL5.5之前可以使用pt-online-schema-change，MySQL5.6之后本身支持在线表结构的变更。
  • 同时对数据字典进行维护。
  • 控制表的宽度和大小。
• 4、为了控制表的大小/宽度,在适当的时候对表进行水平拆分/垂直拆分
  • 经常一起查询的列放到一起
  • text，blob等大字段拆分出到附加表中

【持续更新ing】