一、基础[❤️]
1. 基本概念
1.1 MySQL有哪些数据类型？
MySQL 支持多种类型，大致可以分为三类：数值、日期 / 时间和字符串 (字符) 类型。
数值类型：
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
|---|---|---|---|---|
TINYINT（tiny Int） | 1 Bytes | (-128，127) | (0，255) | 小整数值 |
SMALLINT（small Int） | 2 Bytes | (-32 768，32 767) | (0，65 535) | 大整数值 |
MEDIUMINT（medium int） | 3 Bytes | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
INT 或 INTEGER（integer） | 4 Bytes | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
BIGINT（big int） | 8 Bytes | ... | (0，18 446 744 073 709 551 615) | 极大整数值 |
FLOAT （float） | 4 Bytes | ... | 0，(1.175 494 351 E-38，3.402 823 466 E 38) | 单精度 浮点数值 |
DOUBLE（double） | 8 Bytes | ... | ... | 双精度 浮点数值 |
- 任何整数类型都可以加上
unsigned属性，表示无符号整数。 - 任何整数类型都可以指定长度，但它不会限制数据的合法长度，仅仅限制了显示长度。
日期和时间类型：
类型 | 大小(bytes) | 格式 |
|---|---|---|
DATE（date） | 3 | YYYY-MM-DD |
TIME （time） | 3 | HH:MM:SS |
YEAR（year） | 1 | YEAR |
DATETIME（date time） | 8 | YYYY-MM-DD hh:mm:ss |
TIMESTAMP（time stemp） | 4 | YYYY-MM-DD hh:mm:ss |
尽量使用 TIMESTAMP，空间效率高于 DATETIME。
字符串类型：
类型 | 大小(bytes) | 用途 |
|---|---|---|
CHAR（char） | 0-255 | 定长字符串 |
VARCHAR（varchar） | 0-65535 | 变长字符串 |
TINYBLOB（tiny blob） | 0-255 | 不超过 255 个字符的二进制字符串 |
TINYTEXT（tiny text） | 0-255 | 短文本字符串 |
BLOB（blob） | 0-65 535 | 二进制形式的长文本数据 |
TEXT（text） | 0-65 535 | 长文本数据 |
MEDIUMBLOB（medium blob） | 0-16 777 215 | 二进制形式的中等长度文本数据 |
MEDIUMTEXT（meidum text） | 0-16 777 215 | 中等长度文本数据 |
LONGBLOB（long blob） | 0-4 294 967 295 | 二进制形式的极大文本数据 |
LONGTEXT（long text） | 0-4 294 967 295 | 极大文本数据 |
注意：
char(n)和varchar(n)中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。- 尽量避免使用 text/blob 类型，查询时会使用临时表，导致严重的性能开销。
- Char 和 Varchar 支持设置默认值，而 Text 不能指定默认值。
1.3 datetime 和 timestamp 的区别
datetime能保存大范围的值，从 1001~9999 年，精度为秒。把日期和时间封装到了一个整数中，与时区无关，使用 8 字节存储空间。timestamp和 UNIX 时间戳相同，只使用 4 字节的存储空间，范围比 DATETIME 小得多，只能表示 1970 ~2038 年，并且依赖于时区。
1.4 数据类型有哪些优化策略?
- 更小的通常更好： 一般情况下尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常也更快，因为它们占用更少的磁盘、内存和 CPU 缓存。
- 尽可能简单：
简单数据类型的操作通常需要更少的 CPU 周期，例如：
- 整数比字符操作代价更低，因为字符集 和 校对规则 使字符相比整形更复杂。
- 应该使用 MySQL 的内建类型 date、time 和 datetime 而不是字符串来存储日期和时间，
- 应该使用整形存储 IP 地址。
- 尽量避免 NULL： 通常情况下最好指定列为 NOT NULL，除非需要存储 NULL 值。 因为如果查询中包含可为 NULL 的列对 MySQL 来说更难优化，可为 NULL 的列使索引、索引统计 和 值比较 都更复杂，并且会使用更多存储空间。当可为 NULL 的列被索引时，每个索引记录需要一个额外字节，在 MyISAM 中还可能导致固定大小的索引变成可变大小的索引。 如果计划在列上建索引，就应该尽量避免设计成可为 NULL 的列。
2. 数据库设计
2.1 什么是三大范式？[❤️]
范式只是给了我们一个参考，我们更多的是要根据项目实际情况设计表结构。
第一范式（1NF）：遵循原子性。即，表中字段的数据，不可以再拆分。
先看一个不符合第一范式的表结构，如下：
员工编码 | 姓名 | 年龄 |
|---|---|---|
001 | 销售部小张 | 28 |
002 | 运营部小黄 | 25 |
003 | 技术部小高 | 22 |
在这一个表中的，姓名 字段下的数据是可以再进行拆分的，因此它不符合第一范式，那怎么样才符合第一范式呢？如下：
员工编码 | 部门 | 姓名 | 年龄 |
|---|---|---|---|
001 | 销售部 | 小张 | 28 |
002 | 运营部 | 小黄 | 25 |
003 | 技术部 | 小高 | 22 |
那是否遵循第一范式就一定是好的呢？如下：
员工编码 | 姓名 | 地址 |
|---|---|---|
001 | 小张 | 江西省南昌市东湖区 |
002 | 小黄 | 广东省佛山市禅城区 |
003 | 小高 | 湖北省武汉市新洲区 |
通过观察上述表结构，我们发现，地址是可以再进一步拆分的，比如：
员工编码 | 姓名 | 省 | 市 | 区 |
|---|---|---|---|---|
001 | 小张 | 江西省 | 南昌市 | 东湖区 |
002 | 小黄 | 广东省 | 佛山市 | 禅城区 |
003 | 小高 | 湖北省 | 武汉市 | 新洲区 |
虽然拆分后，看上去更符合第一范式了，但是如果项目就只需要我们输出一个完整地址呢？那明显是表在没拆分的时候会更好用。
所以范式只是给了我们一个参考，我们更多的是要根据项目实际情况设计表结构。
第二范式（2NF）：在满足第一范式的情况下，遵循唯一性，消除部分依赖。即，表中任意一个主键或任意一组联合主键，可以确定除该主键外的所有的非主键值。
再通俗点讲就是，一个表只能描述一件事情。
我们用一个经典案例进行解析。
学号 | 姓名 | 年龄 | 课程名称 | 成绩 | 学分 |
|---|---|---|---|---|---|
001 | 小张 | 28 | 语文 | 90 | 3 |
001 | 小张 | 28 | 数学 | 90 | 2 |
002 | 小黄 | 25 | 语文 | 90 | 3 |
002 | 小黄 | 25 | 语文 | 90 | 3 |
003 | 小高 | 22 | 数学 | 90 | 2 |
我们先分析一下表结构。
- 假设学号是表中的唯一主键，那由学号就可以确定姓名和年龄了，但是却不能确定课程名称和成绩。
- 假设课程名称是表中的唯一主键，那由课程名称就可以确定学分了，但是却不能确定姓名、年龄和成绩。
- 虽然通过学号和课程名称的联合主键，可以确定除联合主键外的所有的非主键值，但是基于上述两个假设，也不符合第二范式的要求。
那我们应该如何调整表结构，让它能复合第二范式的要求呢？
我们可以基于上述的三种主键的可能，拆分成 3 张表，保证一张表只描述一件事情。
学生表 - 学号做主键
学号 | 姓名 | 年龄 |
|---|---|---|
001 | 小张 | 28 |
002 | 小黄 | 25 |
003 | 小高 | 22 |
课程表 - 课程名称做主键
课程名称 | 学分 |
|---|---|
语文 | 3 |
数学 | 2 |
成绩表 - 学号和课程名称做联合主键
学号 | 课程名称 | 成绩 |
|---|---|---|
001 | 语文 | 90 |
001 | 数学 | 90 |
002 | 语文 | 90 |
002 | 语文 | 90 |
003 | 数学 | 90 |
这时候我们可能会想，为什么我们就要遵循第二范式呢？不遵循第二范式会造成什么样的后果呢？
- 造成整表的数据冗余。 如，学生表，可能我就只有2个学生，每个学生都有许多的信息，比如，年龄、性别、身高、住址......如果与课程信息放到同一张表中，可能每个学生有3门课程，那数据总条数就会变成6条了。 但是通过拆分，学生表我们只需要存储 2 条学生信息，课程表只需要存储 3 条课程信息，成绩表就只需保留学号、课程名称和成绩字段。
- 更新数据不方便。 假设，课程的学分发生了变更，那我们就需要把整表关于该课程的学分都要更新一次，但如果我们拆分出课程表，那我们就只需要把课程表中的课程信息更新就行。
- 插入数据不方便或产生异常。
- 假设主键是学号或课程名称，我们新增了某个课程，需要把数据插入到表中，这时，可能只有部分人有选修这门课程，那我们插入数据的时候还要规定给哪些人插入对应的课程信息，同时可能由于成绩还没有，我们需要对成绩置空，后续有成绩后还得重新更新一遍。
- 假设主键是学号和课程名称的联合主键。同样也是新增了某课程，但是暂时没有人选修这门课，缺少了学号主键字段数据，会导致课程信息无法插入。
第三范式（3NF）：在满足第二范式的情况下，消除传递依赖。即，在任一主键都可以确定所有非主键字段值的情况下，不能存在某非主键字段 A 可以获取 某非主键字段 B。
即：非主键列只依赖于主键，不依赖于其他非主键。
仍然用一个经典例子来解析：
学号 | 姓名 | 班级 | 班主任 |
|---|---|---|---|
001 | 小黄 | 一年级（1）班 | 高老师 |
这个表中，学号是主键，它可以唯一确定姓名、班级、班主任，符合了第二范式，但是在非主键字段中，我们也可以通过班级推导出该班级的班主任，所以它是不符合第三范式的。
那怎么设计表结构，才是符合第三范式的呢？
- 学生表 学号姓名班级001小黄一年级（1）班
- 班级表 班级班主任一年级（1）班高老师
通过把班级与班主任的映射关系另外做成一张映射表，我们就成功地消除了表中的传递依赖了。
姓名可能相同，所以通过姓名不能确定班级，所以无需拆分。
除了三大范式外，还有BC范式和第四范式，但其规范过于严苛，在生产中往往使用不到。
2.2 什么是范式和反范式，以及各自优缺点？
- 范式是符合某一种级别的关系模式的集合。 构造数据库必须遵循一定的规则。在关系数据库中，这种规则就是范式。
名称 | 优点 | 缺点 |
|---|---|---|
范式 | 范式化的表减少了数据冗余，数据表更新操作快、占用存储空间少。 | 查询时通常需要多表关联查询，更难进行索引优化 |
反范式 | 反范式的过程就是通过冗余数据来提高查询性能，可以减少表关联和更好进行索引优化 | 存在大量冗余数据，并且数据的维护成本更高 |
所以在平时工作中，我们通常是将范式和反范式相互结合使用。
我正在参与2023腾讯技术创作特训营第三期有奖征文，组队打卡瓜分大奖！
