如何设计好Mysql数据库

【今日推荐】：为什么一到面试就懵逼！>>>

• 目录
• 1. 前言
• 2. 数据库设计流程
• 2.1 需求分析
• 2.2 逻辑设计
• 2.2.1 操作异常
• 2.2.2范式概念
• 2.3物理设计
• 2.3.1设计思路
• 2.3.2 mysql常用存储引擎
• 2.3.3表和字段的命名规则
• 2.4 维护和优化
• 2.4.1维护数据字典
• 2.4.2维护索引
• 2.4.3维护表结构
• 2.4.4合理操作原则

1. 前言

在设计数据库之前，我们需要了解一些基本的知识，才能更好地设计。

2. 数据库设计流程

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

2.1 需求分析

了解一个项目的存储那些数据、存储的特点和生命周期；必须搞清楚的是实体与实体之间的关系（一对一，一对多，多对多），实体包含的属性，属性或者属性的组合哪些可以做唯一标识

2.2 逻辑设计

将需求转化为数据库逻辑模型，一般用ER图对逻辑模型展示

2.2.1 操作异常

插入，更新，删除操作异常；如果一个表存在插入异常,那么必定存在删除异常和更新异常

2.2.2范式概念

1）第一范式

数据表中的所有字段都是单一属性,不能再分

2）第二范式

数据库的表中不存在非关键字段对任一候选关键字段的部分函数依赖

3）第三范式

在第二范式基础上,不存在非关键字段对任意候选关键字段的传递函数依赖

4）BC范式

在第三范式基础上,数据库表不存在任意字段对任一候选关键字段的传递函数依赖

2.3物理设计

2.3.1设计思路

(1)寻则合适的数据库管理系统

(2)定义好数据库,表以及字段的命名规范

(3)根据DBMS系统选择合适字段类型

(4)反范式化设计

2.3.2 mysql常用存储引擎

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

2.3.3表和字段的命名规则

1）可读性原则

使用大写和小写格式化的库对象名字以此获得良好的可读性.

比如:使用CustAddress而不是custaddress

2）表意性原则

对象的名字要能够描述它所表示的对象.

3）长名原则

尽可能少写或者不使用缩写

4）优先考虑数字类型

类型考虑应先考虑数字类型,其次是日期或二进制类型,最后是字符类型,对于相同级别的数据类型,应该优先选择占用空间小的数据类型,如下图所示

列类型	存储空间	备注
TINYINT	1字节
SMALLINT	2字节
MEDIUMINT	3字节
INT	4字节
BINGINT	8字节
DATE	3字节
DATETIME	8字节
TIMESTAMP	4字节
CHAR	1-255
VARCHAR	1-255	65535字符

原因:

同样的数据,字符处理比数字处理更慢；数据处理以页为单位,列长度越小,性能越好

5）char和varchar如何选择

列中存储数据长度差不多一致,优先考虑char；列中数据最大数据长度小于50Byte,一般考虑char;当然,如果该列很少被使用,基于节省空间和减少I/O考虑,可以选择varchar；一般不宜定义大于50Byte的char类型列

6）decimal与float如何选择

前者用于存储精确数据,后者存储非精确数据；float的存储空间开销比decimal小,所以非精度优先选择float类型

7）时间类型如何选择

建议使用int（11）来存储时间字段的优缺点

优点:字段长度比datetime小

缺点:使用不方便,需要进行函数转换

限制:只能存储到2038-1-19 11:14:07 既是2的32次方(2147483648)

8）主键的选择

主键需要区分业务主键和数据库主键

业务主键是用来标识业务数据，进行表与表之间关联的

数据库主键是为了优化数据存储的，一般来说Innodb会生成6个字节的隐含主键

9）避免使用外键约束

外键的使用减低数据导入效率、增加维护成本、但相关联的列上一定要建立索引

10）避免使用触发器

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

11）严禁使用预留字段

12）反范式化

为了提高性能和读取的效率,适当对第三范式的要求进行违反,允许存在少量数据冗余,也就是我们常说的空间换时间.

2.4 维护和优化

2.4.1维护数据字典

使用第三方工具对数据字典继续拧维护

利用数据库本身备注字段来维护

2.4.2维护索引

选择合适的列建立索引,比如出现在where从句,group by从句,order by从句中的列,注意:索引并不是越多越好,过多的索引会降低读的效率,定期维护索引碎片,在SQL语句不要使用强制索引关键字

2.4.3维护表结构

在适当时候对标水平拆分或者垂直拆分

如何垂直拆分:经常查询的列放到一起,text,blob等大字段拆分出到附加表中

2.4.4合理操作原则

批量操作效率高于逐条操作

禁止使用SELECT * 这样的查询

控制使用用户自定义函数

不要使用数据库的全文索