数据设计 参数慕课教程

数据库设计

• 1.简介
  • 1.1 数据库设计
• 2.设计步骤
• 3.需求分析
• 4.逻辑设计
• 5.名词解释
• 6.ER图
• 7.设计范式以及数据操作
• 8.物理设计
• 9.维护和优化

数据库设计

• 根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型。并建立好数据库中的表的结构以及表与表之间关联关系的过程。使之能有效的对应用系统中的数据进行存储，并可以高效的的对已经存储的数据进行访问。

设计步骤

• 需求分析
  • 从数据的属性和数据的特点进行分析
• 逻辑设计
  • 使用ER图对数据库进行逻辑建模
  • 和数据库系统没有关系
• 物理设计
  • 考虑数据库系统的特点，充分利用系统的特点
• 维护优化
  • 新的需求进行建表
  • 索引优化
  • 大表拆分

需求分析

- 搞清楚实体以及实体之间的关系(1对1,1对多，多对多) - 实体的属性应包含哪些属性？ - 哪些属性或属性的组合可以唯一标识一个实体

逻辑设计

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

名词解释

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

ER图

• 矩形：表示实体集，矩形内写实体集的名字
• 菱形：表示联系集
• 椭圆：表示实体的属性
• 线段：将属性线段连接到实体集，或者将实体集联系到联系集

设计范式以及数据操作

• 常见数据库设计范式

  • 第一范式
    • 定义：数据库表中的所有字段都是单一属性，不可再分的，单一属性有基本数据类型构成
    • 要求数据库中的表都是二维表
  • 第二范式
    • 定义：数据库的表中不存在非关键字段对任一候选关键字段的部分函数依赖
    • 部分函数依赖是指存在这组合关键字中的某一个关键字决定非关键字的情况
    • 所有单关键字段的表都符合第二范式
  • 第三范式
    • 定义：第三范式是在第二范式的基础之上定义的，如果数据表中不存在非关键字段，对任意候选关键字段的传递函数依赖则符合第三范式
  • BC范式
    • 定义：在第三范式的基础之上，数据库表中如果不存在任何字段对任一候选关键字字段的传递函数依赖则符合BC范式，也就是说如果是符合关键字，则符合关键字之间也不能存在函数依赖关系

• 如果不符合三种范式都会产生数据操作异常和数据冗余

• 数据操作异常

  • 插入异常：如果某实体随着另一个实体存在而存在，即缺少某个实体时无法表示这个实体，那么这个表就存在插入异常
  • 更新异常：如果更改表所对应的某个实体实例的单独属性时，需要将多行进行更新，那么这个表就存在更新异常
  • 删除异常：如果删除表中的某一行来反映某实体实例失效时候导致另一个不同实体信息丢失，那么这个表就存在删除异常

• 数据冗余

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

物理设计

• 要做什么？

  • 选择合适的数据库管理系统
    • 根据使用用途、行业、成本方面进行选择
  • 定义数据库、表以及字段的命名规范
    • 更高效率的去进行数据库的建设
    • 可读性原则（区分大小写）
    • 表意型原则（更好的简述表作用）
    • 长名原则（更好的区分名称）
    • 命名规范，应该从数据、表名称、字段名称等粒度
    • 表的字段类型的选择（varchar char）一般是根据需求和实际的工作经验来选择的，前提是符合一定的命名规范，另外就是要考虑节省空间，提高查询效率，易编程性。
  • 根据所选的DBMS系统选择合适的字段类型
    • 列的数据类型一方面影响数据存储空间的开销，另一方面也会影响数据查询性能。当一个列可以选择多种数据类型时，应该是优先考虑数字类型，其次是日期或二进制类型，最后是字符类型，对于相同级别的数据类型，应该优先选择占用空间小的数据类型
    • 字段类型选择原则：
      • 优先选择数字类型且 数据处理以页为单位，列的长度越小，利用性能提升
      • 列中存储数据长度差不多是一致，优先考虑char，否则用varchar
      • 列的最大数据长度小于50Byte，一般考虑用char
      • 不宜定义大于50Byte的char类型列
      • decimal（精确字段）和float（非精确字段）
  • 反范式化设计
    • 适当的数据冗余可以使得数据库设计更加完善

• 如何选择主键

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

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

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

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

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

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

• 避免使用外键约束

  • 降低数据导入的效率
  • 增加维护成本
  • 不建议使用外键约束，但是相关联的列上一定要建立索引

• 避免使用触发器

  • 降低数据导入的效率
  • 可能会出现意想不到的数据异常
  • 使业务逻辑变的复杂

• 关于预留字段

  • 无法准确知道预留字段的类型
  • 无法准确知道预留字段中所存储的内容
  • 后期维护预留字段所要的成本，同增加一个字段所需要的成本相同
  • 严禁使用预留字段

• MYSQL常用存储引擎

维护和优化

- 维护数据字典 - 使用MySQL本身的备注字段来维护字典 - 使用第三方工具对数据字典进行维护 - 导出数据字典 - 维护索引 - 如何建立合适的索引？ - 出现在where从句，group by 从句，order by从句的列 - 可选择性高的列要放到索引的前面 - 索引中不要包括太长的数据类型 - 如何维护索引？ - 过多的索引降低写效率、而且会降低读效率 - 定期维护索引锁片 - 在SQL语句中不要使用强制索引关键字 - 维护表结构 - 使用在线变更表结构工具（5.5之前需要使用工具，5.6之后数据库本身支持在线变更结构） - 同时对数据字典进行维护 - 控制表的宽度和大小 - 在适当的时候对表进行水平或垂直拆分 - 水平拆分减少表的数据量 - 垂直拆分减少了表的宽度