学习总结:如何设计良好的数据库

数据库设计的概念

一个应用系统需要存放数据,那么我们就需要考虑用什么数据库,建几张表,表与表之间的关系是什么,每张表有多少字段,每个字段有什么特性,比如:数据类型、数据长度、数据约束(主外键、唯一约束、非空约束等)、数据索引等。

良好的设计和糟糕的设计

良好的设计:

  • 可以减少数据库的冗余
  • 尽可能的避免数据异常
  • 解决存储空间
  • 可以达到高效的访问

糟糕的设计:

  • 存在大量数据冗余
  • 存在数据插入,更新,删除异常
  • 浪费大量存储空间
  • 访问数据低效

数据库设计的步骤

  1. 需求分析:根据数据的属性和特点设置数据类型
  2. 逻辑设计:实现数据库的逻辑建模和数据关系(ER图)
  3. 物理设计:考虑具体的需求来选择合适的数据管理系统
  4. 维护优化:考虑表结构的建立、索引优化、大表拆分等

一、需求分析

我们接到一个项目,一般会进行以下的分析:

  1. 首先要去分析这个项目有哪些模块
  2. 然后针对具体模块分析有哪些属性
  3. 针对属性分析哪个属性或哪几个属性的集合可以用来作为[唯一标识]
  4. 分析这个模块数据是否永久存储,是否数据增长很快,是否经常查询
  5. 如果就要考虑分库分表了
  6. 如果不是,那么这些数据是否只会存储一定时间,是否需要永久记录
  7. 如果需要永久记录,我们就定期归档及数据的迁移操作

二、逻辑设计

1. 逻辑设计的工作

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

2. 数据库设计范式

数据库设计范式概念:是一些设计数据库的良好的规则或者经验。常见的数据库设计设计包括:第一范式、第二范式、第三范式

  • 【第一范式】: 数据库中的所有字段都是单一属性,不可再分的。这个单一属性是由基本的数据类型所构成的,如整数,浮点娄,字符串等。第一范式要求数据库中的表都是二维表。
  • 【第二范式】 数据库中的表中不存在非关键字段对任一候选关键字段的部分函数依赖。 部分函数依赖是指存在着组合关键字中的某一关键字决定非关键字的情况。 换名话说:所有单关键字段的表都符合第二范式。
  • 【第三范式】 第三范式是在第二范式的基础之上定义的。如果数据表中不存在非关键字段对任意候选关键字段的传递函数依赖则符合第三范式。 第三范式存在的问题:存在数据冗余;存在数据插入、更新和删除异常
  • 【BCNF范式(Boyce.Codd范式)】 在3范式基础上,数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖则符合BC范式。也就是说如果是复合关键字,则复合关键字之间也不能存在函数依赖关系。

三、物理设计

1. 物理设计要做什么

  • 选择合适的数据库管理系统: oracle、sqlserver、mysql、PgSQL
  • 定义数据库、表以及字段的命名规范
  • 根据所选的dbms系统选择合适的字段类型
  • 反范式化设计

2. 选择哪种数据库

  • 商业数据库:oracle sqlserver 要考虑对应的成本,版权的费用
  • 开源数据库:mysql、 pgsql 考虑对应的效率,对应的系统,开发所使用的语言
  • 商业数据库更适合企业级项目, 开源数据库适用于互联网系统
  • Mysql常用存储引擎:
存储引擎 事务 锁粒度 主要应用 忌用
MyISAM 不支持 支持并发插入的表级锁 select,insert 读写操作频繁
MRG_MYISAM 不支持 支持并发插入的表级锁 分段归档,数据仓库 全局查找过多的场景
Innodb 支持 支持MVCC的行级锁 事务处理
Archive 不支持 行级锁 日志记录,只支持insert,select 需要随机读取、更新、删除
Ndb cluster 支持 行级锁 高可用性 大部分应用

3. 表及字段命名规则

  • 可读性原则(使用大小写来格式化的库对象名以获得良好的可读性)
  • 表意性原则(对象的名字应该能描述出它所标识的的对象)
  • 长名原则(尽可能少使用或不适用缩写)

4.字段选择类型的原则

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

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

5. 如何具体选择字段类型

char与varchar的选择:
  • 如果列中要存储的数据程度差不多是一致的,则应该考虑用char,否则应该考虑用varchar
  • 如果列中的最大长度小于50byte,则一般先要考虑char
  • 一般不定义大于50byte的char类型列。
decimal与float 如何选择
  • decimal用于存储精确数据,而float只能用于存储非精度数据。
  • 由于float的存储空间开销一般比demimal小。(精确到7位小数只需要4个字节,而精确到15位小数只需要8个字节),故非精度数据优先选择float
时间类型如何存储
  • 使用int来存储时间字段的优缺点
    优点:字段长度比datetime小
    确定:使用不方便,要进行函数转换。
    限制:只能存储到2038-1-19 11:14:07即是2的32次方为2147483648
  • 需要存储的时间粒度
    年月日小时分秒周

6. 数据库设计的注意事项

如何选择主键

1.区分业务主键和数据库主键
业务主键用于标识业务数据,进行表与表之间的关联。
数据库主键为了优化数据存储

2.根据数据库的类型,考虑主键是否要顺序增长
有些数据库是按主键的顺序逻辑存储的

3.主键的字段类型所占用空间要尽可能的小
对于使用聚集索引方式的存储的表,每个索引后都会附加主键的信息

避免使用外键约束
  • 降低数据导入的效率
  • 增加维护成本
  • 虽然不建议使用外键约束,但是相关联的列上一定要建立索引。
避免使用触发器
  • 降低数据导入的效率
  • 可能会出现意想不到的数据异常
  • 使业务逻辑变的复杂。
关于预留字段
  • 无法准确的知道预留字段的类型
  • 无法准确的知道预留字段中所存储的内容
  • 严禁使用预留字段

7. 反范式化表设计

反范式化设计就是为了性能和读取效率的考虑而适当的对第三范式的要求进行违反,而允许存在少量的数据冗余,换句话来说反范式化就是使用空间来换取时间。
好处:减少表的关联数量、增加数据的读取效率、反范式化一定要适度

四、维护优化

1. 维护优化要做什么

  • 数据字典记录表中每个列所存储的内容是什么,方便后期维护和升级,特别是一些状态字段
  • 索引的维护,主要是从一个动态变化的角度来看待我们的数据和索引
  • 数据表中存在过时的字段,需要清理
  • 当表中的数据量达到一个数量级的话,影响查询的速度,需要对表进行适当的拆分

2. 如何维护数据字典

  • 使用第3方工具对数据字典进行维护(ORACLE PL/SQL, ORACLE TOAD…)
  • 利用数据库本身的备注字段来维护数据字典:
 //MYSQL为例:
CREATE TABLE Customer(cust_id in auto_increment not null comment ‘自增ID’, cust_name varchar(10) not null comment ‘客户姓名’, primary key(cust_id)) comment ‘客户表’
//导出数据字典(MYSQL数据字典案例:
select a.table_name, b.table_comment, a.column_name, a.column_type, a.column_comment from INFORMATION_SCHEMA.COLUMNS a join INFORMATION_SCHEMA.TABLES b on a.table_schema = b.table_schema and a.table_name = b.table_name where a.table_name = ‘customer’;

3. 如何维护索引

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

【注意事项】:
– 索引不是越多越好,过多的索引不但会降低写效率而且会降低读的效率
– 定期维护索引碎片
– 在SQL语句中不要使用强制索引关键字

4. 数据库中适合的操作

维护表结构注意事项:

  • 使用在线变更表结构工具:
    MYSQL5.5之前可以使用 pt-online-schema-change
    MYSQL5.6之后本身支持在线表结构的变更
  • 同时对数据字典进行维护
  • 控制表的宽度和大小(表字段的大小控制,表数据量的分区,拆分处理等)
  • 禁止使用SELECT *这样的查询(把不必要的字段也查询出来,浪费IO)
  • 控制使用用户自定义函数(索引失效)
  • 不要使用数据库中的全文索引

5. 表的垂直拆分和水平拆分

  • 垂直拆分:解决宽度的问题
  • 水平拆分:解决数据量的问题

垂直拆分:当表的列过多,几十列等等,这是查询该表的IO速度会变慢,这是就建议进行垂直拆分,即拆分列,为了控制表的宽度。
– 经常一起查询的列放到一起
– TEXT, BLOB等大字段拆分到附加表中

水平拆分:表的水平拆分就是为了控制表的大小,也就是说,把一张大表里面的数据分配到其它几张相同的表中,这样就可以减少一张表里面存储的数据。一般采用的是哈希(hash key)的方式。