设计表是我们开发过程中必然要涉及到的步骤,想要一个优秀的系统,表的设计是基础,要是基础没设计好,那什么sql语句优化,索引优化,都是杯水车薪
设计表我们一般从2个角度触发考虑问题: 范式设计思想 / 物理设计
一.范式设计思想
范式设计的三个标准:
- 表的字段只能是单一的属性
- 表的主键和其他非主键,是一个一对一的关联关系
- 表的主键和其它非主键,是一个直接的关系
第一范式例子:
明显不符合第一范式要求,name_age包含2个属性 name 和 age,规范建表-->
第二范式例子:
学生和课程不是一个一对一的关系,而是一个多对多的关系
第三范式例子:
字段冗余,关联的class不仅有name还有teacher_name、plan,假如这门课变更了,需要将这2个字段一起修改,这部分可优化?--->
以上是对三大范式的一个基本的演示,但是这样子会不会有问题呢?
我们如果想要查找A同学选了那几个老师的课程,会使用到多个join语句,当产生笛卡尔积问题的时候,性能就不必多说了,差 所以说在设计表不一定严格满足范式要求,我们的设计优先级应该是 业务 >= 性能 > 范式要求
------->在某些场景我们需要使用这种空间换时间的方式,当然在不同的系统以及系统不同的阶段情况都会发生变化,这就比较考验一个架构师-sql优化 的能力了
二. 物理性设计思想
1.定义数据库 、表 、字段 的命名规范:
可读性 | 大小写区分情况使用驼峰命名 , 也可使用横线分割 | |
表意性 | 使用能表意的词汇,一般选择英文(我也试过中文,中文那一个系统的要全中文不能中间出现其他语言) | |
长名原则 | 尽量减少缩写,保证一个完整性,间接提高可读性 | |
2.数据库存储引擎选择:
一般情况(主要工作:增删改查)下我们从Myisam 和 InnoDB中选择一个,结合业务在根据每种引擎的特性进行选择,引擎特性分析在 mysql - 1. 性能-优化概述 AND 架构与存储引擎 中有具体分析
3. 表中字段的数据类型选择:
每一个字段在类型选择上都是一个集合 (当然集合大小是不固定的 1~N , N<系统支持类型数),根据性能的排列,以及字段表意的选择,简单的设计了一个排序规则:
| 优先 使用空间占用少的类型 |
数字类型 | 写操作和读操作在性能上都是最好的 |
日期类型/时间类型 | 数据的存储也是数字类型 |
字符类型 | 优先选择varchar可变长度的字符类型,减少空间占用 |
Blob类型 | |
在对于精度要求高的领域:货币、金融 等领域 使用decimal字段
float | 4 byte | 非精准类型 |
double | 8 byte | 非精准类型 |
dicimal | open(每4byte存9个数字,小数点占1byte) | 精准类型 |
timestamp 与 datetime的区别:
类型 | 大小Byte | 范围 | 格式 | 用途 | 时区影响 |
datetime | 8 | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 | YYYY-MM-DD HH:mm:ss | | 无 |
timestamp | 4 | 1970-01-01 00:00:00 ~2037-12-31 23:59:59 | YYYY-MM-DD HH:mm:ss | | 有 |
set time_zone 设置时区情况 一般为 8:00