MySQL优化01:数据库设计的基本原则
- 数据库字段的设计
- 原则1:尽量使用整数表示字符串
- 整数表示字符串例子1:使用整数存储ip地址
- 整数表示字符串例子2:使用枚举类型和集合类型
- 原则2:使用定点数表示浮点数
- 原则3:使用尽量小的数据类型
- 原则4:尽量避免使用非空字段
- 原则5:字段注释完整,字段名具有逻辑含义
- 原则6:单表字段不宜过多
- 原则7:可以预留字段
- 关联表的设计
- 一对多关联关系
- 多对多关联关系
- 一对一关联关系
- 数据库范式
- 第一范式:字段具有原子性
- 第二范式:消除对主键的部分依赖
- 第三范式:消除对主键的传递依赖
数据库字段的设计
对于数据库字段的设计,有如下6条原则
- 尽量使用整数表示字符串
- 使用定点数表示浮点数
- 使用尽量小的数据类型
- 尽量避免使用非空字段
- 字段注释完整,字段名具有逻辑含义
- 单表字段不宜过多
- 可以预留字段
原则1:尽量使用整数表示字符串
使用整型数表示字符串有两大优势:
- 整型数所占存储空间一般小于字符串
- 整型数的运算速度快
下面两个例子演示了使用整数表示字符串的两种情况:
整数表示字符串例子1:使用整数存储ip地址
一个IPV4地址表示成字符串要以varchar(15)数据格式表示. 但实际上IPV4地址每一节的取值范围都是[0~255],刚好可以用一个字节表示,因此可以使用一个int unsigned数据格式来表示一个IPV4地址.
MySQL正好提供了inet_aton()和inet_ntoa()两个函数实现IPV4地址的varchar表示与int unsigned表示之间的转换.
mysql> select inet_aton('38.71.123.233');
+----------------------------+
| inet_aton('38.71.123.233') |
+----------------------------+
| 642218985 |
+----------------------------+
1 row in set (0.00 sec)
mysql> select inet_ntoa(642218985);
+----------------------+
| inet_ntoa(642218985) |
+----------------------+
| 38.71.123.233 |
+----------------------+
1 row in set (0.00 sec)整数表示字符串例子2:使用枚举类型和集合类型
枚举类型enum也体现了使用整数表示字符串的思想.某些可选值有限的字段,如性别,可以试用枚举类型enum来存储.
gender enum(男, 女, 保密)但实际情况中这种用法比较少,因为枚举类型的数据难以维护(如增加一种新性别时需要修改user表).在实际情况中,我们可以采用中间表作为一种替代方案,比如对于上边的例子,我们可以创建中间表gender,其内容如下:
id | title |
1 | 男 |
2 | 女 |
3 | 保密 |
在user表中使用外键引用gender表,这样达到了与使用枚举类型enum相同的效果,且易于维护(增加新性别只需在gender表中添加一条记录即可).
原则2:使用定点数表示浮点数
浮点数在计算机中保存和运算易失真,对于银行金额一类精度要求高的数据,我们可以选用定点数decimal存储,定义decimal(P, D)可以创建一个整数位最多P位,小数位最多D位的定点数.
price decimal(8,2) -- 整数部分有8位,小数部分有2位当然,也可以使用小单位,大数额的方案存储金额,将数据存为BIGINT类型
price BIGINT(10) -- 前8位存整数部分,后2位存小数部分原则3:使用尽量小的数据类型
原则4:尽量避免使用非空字段
非空字段的缺点如下:
-
null难以参加各种运算,null参与除is null和is not null以外的运算,其运算结果都是null -
null的存储需要额外空间.
我们可以在含有空值的字段上使用默认值来占位,但要避免产生逻辑上的歧义.
原则5:字段注释完整,字段名具有逻辑含义
原则6:单表字段不宜过多
原则7:可以预留字段
关联表的设计
一对多关联关系
存在一对多关联关系的两张关联表,可以在多端使用关联字段,关联一端的主键.
多对多关联关系
多对多的关联关系,可以用一张中间表来实现,中间表存储两张表主键之间的对应关系.中间表与两张实体表分别形成了两个一对多的关系.
一对一关联关系
一对一关联关系,可以直接用一张表来存储.但是如果表中的字段数过多或部分字段不常用,可以考虑分表,两张表共享同一主键.
数据库范式
满足三范式带来的好处:
- 减少数据冗余
- 易于维护
第一范式:字段具有原子性
原子性要求字段不能被再分割.
下面数据库表不具有原子性,其授课时间段字段可以被分割
id | 授课时间段(开始~结束) |
3 | [2019-08-15 13:00:00, 2019-08-15 15:00:00] |
4 | [2019-08-16 15:00:00, 2019-08-15 17:00:00] |
5 | [2019-08-18 16:00:00, 2019-08-15 18:00:00] |
将该字段拆分后所有字段都满足原子性
id | 上课时间 | 下课时间 |
3 | 2019-08-15 13:00:00 | 2019-08-15 15:00:00 |
4 | 2019-08-16 15:00:00 | 2019-08-15 17:00:00 |
5 | 2019-08-18 16:00:00 | 2019-08-15 18:00:00 |
第二范式:消除对主键的部分依赖
依赖: 若A字段能确定B字段,则B字段依赖于A字段
对主键的部份依赖: 若某个字段依赖复合主键的一部分字段,则称为对主键的部份依赖.
例如:在下面的表里,教师姓名和班级构成复合主键,教师性别仅依赖于教师姓名,是为对复合主键的部份依赖.
教师姓名 | 班级 | 教师性别 | 教室 | 上课时间 | 下课时间 |
孔子 | 一班 | 男 | E101 | 2019-08-15 13:00:00 | 2019-08-15 15:00:00 |
蔡文姬 | 一班 | 女 | E101 | 2019-08-16 15:00:00 | 2019-08-15 17:00:00 |
孔子 | 二班 | 男 | E101 | 2019-08-18 16:00:00 | 2019-08-15 18:00:00 |
解决方法: 增加一个与业务无关的id字段作为主键,增加课程id作为唯一主键,其他字段只依赖于该主键.
课程id | 教师姓名 | 班级 | 教师性别 | 教室 | 上课时间 | 下课时间 |
1 | 孔子 | 一班 | 男 | E101 | 2019-08-15 13:00:00 | 2019-08-15 15:00:00 |
2 | 蔡文姬 | 一班 | 女 | E101 | 2019-08-16 15:00:00 | 2019-08-15 17:00:00 |
3 | 孔子 | 二班 | 男 | E101 | 2019-08-18 16:00:00 | 2019-08-15 18:00:00 |
第三范式:消除对主键的传递依赖
传递依赖: 若B字段依赖A字段,C字段依赖B字段,则C字段对A字段构成传递依赖.
例如上例中的教师姓名依赖于课程id,而教师性别依赖于教师姓名(实际上依赖的是教师的id,在这里我们认为没有重名的).
解决方法:分表,将传递依赖部分单独建表.
课程表如下
课程id | 教师id | 班级 | 教室 | 上课时间 | 下课时间 |
1 | 1 | 一班 | E101 | 2019-08-15 13:00:00 | 2019-08-15 15:00:00 |
2 | 2 | 一班 | E101 | 2019-08-16 15:00:00 | 2019-08-15 17:00:00 |
3 | 1 | 二班 | E101 | 2019-08-18 16:00:00 | 2019-08-15 18:00:00 |
教师信息表如下,其存储了教师信息对教师id的依赖
教师id | 教师姓名 | 教师性别 |
1 | 孔子 | 男 |
2 | 蔡文姬 | 女 |
