Mysql数据库设计的想法

什么是数据库设计

数据库设计是根据业务系统的具体需要，结合我们所选用的数据库管理系统(DBMS)，为这个业务系统构造出最优的数据存储模型。并建立好数据库中的表的结构及表与表之间的关联关系的过程。使之能有效的对应用系统中的数据进行存储，并可以高效的对已经存储的数据进行访问。数据库管理系统有很多：Mysql、oracle、SQL Server、Access、DB2、FoxPro等。

优良的数据库设计：1.减少数据冗余。2.避免数据维护异常。3.节约存储空间。4.高效的访问。

数据库设计的思路

需求分析--->逻辑设计--->物理设计--->维护优化

一、数据库需求分析的关键：1）数据是什么  2）数据有哪些属性 3）数据和属性都有什么特点

1.了解系统中要存储的数据

2.了解数据的存储特点（是否具有时效性，可以采用清理规则。数据增长快的一般要分区分表，判断是否是核心数据，是否需要存储在数据库中）

1）实体及实体之间的关系（1对1,1对多，多对多）

2）实体所包含的属性或者属性的组合可以唯一标识一个实体。

3.了解数据的生命周期

二、进行逻辑分析。

1.将需求转化为数据库的逻辑模型

名词解释：

关系：一个关系对应通常是所说的一张表

元组：表中的一行即为一个元组

属性：表中的一列即为一个属性；每一个属性都有一个名称，称为属性名。

候选码：表中的某个属性组，它可以唯一确定一个元组。

主码：一个关系有多个候选码，选定其中一个为主码。

域：属性的取值范围。

分量：元组中的一个属性值。

2.数据库设计的范式：常见的数据库设计范式包括，第一范式，第二范式，第三范式以及BC范式

这是目前大多数数据库设计所要遵循的范式。

第一范式：数据库中的所有字段都是单一属性，不可再分

其中的单一属性是指基本的数据类型构成：整型、浮点型、字符串等。 第一范式要求数据库中的表都是二维表。

第二范式：数据库的表中不存在非关键字段对任一候选关键字段的部分函数依赖。部分函数依赖是指存在着组合关键字中的某一个关键字决定非关键字的情况。

所有单关键字段的表都符合第二范式。

第三范式：如果数据表中不存在非关键字段，对任意候选关键字段的传递函数依赖则符合第三范式

BC范式：如果是复合关键字，则复合关键字之间也不能存在函数依赖关系。

数据操作异常及数据冗余

操作异常

1.插入异常：如果某实体随着另一个实体的存在而存在，既缺少某个实体时无法表示这个实体，那么这个表就会存在插入异常。

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

3.删除异常：如果删除表某一个行来反映某实体实例失效时，导致另一个实体出现信息丢失。

数据冗余

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

三、物理设计中最重要的是研究数据库管理系统的自身特点。

1.选择合适的数据库管理系统

常见的DBMS系统

1）Oracle与SQLServer是按核收费，属于商业数据库。适合企业级项目

2）Mysql与PSQL属于开源数据库。适合Java与PHP。适用于互联网项目

SQLSever支持在Windows操作系统。适合.net

a.MySql的常用存储引擎

存储引擎	事务	锁粒度	主要应用	忌用
MyISAM	不支持 读写速度快	支持并发插入的表级锁	SELECT,INSERT	读写操作频繁
MRG_MYISAM	不支持	支持并发插入的表级锁	分段归档，数据仓库	全局查找过多的场景
Innodb	支持	支持MVCC行级索	事务处理	无
Archive	不支持	行级索	日志记录，只支持insert,select	需要随机读取，更新，删除
Ndb cluster	支持	行级索	高可用性	大部分应用

2.定义数据库、表及字段的命名规范。

1）可读性原则：使用大小写来格式化的库对象名字以获得良好的可读性。注意：DBMS系统对表名的大小写敏感。查询时也要按大小写规则来。

2）表意性原则：对象的名字应该能够描述它所标识的对象。

3）长名性原则：不要缩写。

3.根据所选的DBMS系统选择合适的字段类型。

1）字段类型的选择原则

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

列类型	TINYINT	SMALLINT	MEDIUMINT	INT	BINGINT	DATE	DATETIME	TIMESTAMP	CHAR(M)	VARCHAR(M)
存储空间	1字节	2字节	3字节	4字节	8字节	3字节	8字节	4字节	M字节，1<=M<=255	可变

a)在对数据进行比较（查询条件、JOIN条件及排序）操作时：同样的数据，字符处理往往比数字慢。

b)在数据库中，数据处理以页为单位。列的长度越小，利于性能提升。

char和varchar的选择

• 如果列中要存储的数据长度差不多是一致的，则应该考虑用char；否则选择考虑用varchar.
• 如果列中的最大数据长度小于50Byte,则一般考虑用char.(如果这个列很少用，基于节省空间和减少I/O的考虑，还是可以考虑选择使用varchar)
• 大于50Byte不建议使用char类型。

decimal与float的选择

• decimal用于精确数据的存储，而float只能用于存储非精确数据。
• float的存储空间开销一般比decimal小。

时间类型的存储选择

• 使用int来存储时间，优点：字段长度小。缺点：需要去函数转换。长时间运用的用Datetime。int有存储时间限制
• 需要存储的时间粒度（年月日时分秒）

主键的选择

• 区分业务主键和数据库主键（业务主键用于标识业务数据，进行表与表之间的关联；数据库主键是为了优化数据存储）
• 根据数据库的类型，考虑主键是否要顺序增长（有些数据库是按主键的顺序逻辑存储的）
• 主键的字段类型所占空间要尽可能小

避免使用外键约束以及触发器，尽量不要使用预留字段。

4.反范式化设计。

为了性能和读取效率的考虑而适当的违反三范式

四、维护优化：1）新的需求进行建表 2）索引优化  3）大表拆分