MySQL面试题（一）

一、基础[❤️]

1. 基本概念

1.1 MySQL有哪些数据类型？

MySQL 支持多种类型，大致可以分为三类：数值、日期 / 时间和字符串 (字符) 类型。

数值类型：

• 任何整数类型都可以加上 unsigned 属性，表示无符号整数。
• 任何整数类型都可以指定长度，但它不会限制数据的合法长度，仅仅限制了显示长度。

日期和时间类型：

尽量使用 TIMESTAMP，空间效率高于 DATETIME。

字符串类型：

注意：

• 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）：遵循原子性。即，表中字段的数据，不可以再拆分。

先看一个不符合第一范式的表结构，如下：

在这一个表中的，姓名 字段下的数据是可以再进行拆分的，因此它不符合第一范式，那怎么样才符合第一范式呢？如下：

那是否遵循第一范式就一定是好的呢？如下：

通过观察上述表结构，我们发现，地址是可以再进一步拆分的，比如：

虽然拆分后，看上去更符合第一范式了，但是如果项目就只需要我们输出一个完整地址呢？那明显是表在没拆分的时候会更好用。

所以范式只是给了我们一个参考，我们更多的是要根据项目实际情况设计表结构。

第二范式（2NF）：在满足第一范式的情况下，遵循唯一性，消除部分依赖。即，表中任意一个主键或任意一组联合主键，可以确定除该主键外的所有的非主键值。

再通俗点讲就是，一个表只能描述一件事情。

我们用一个经典案例进行解析。

我们先分析一下表结构。

1. 假设学号是表中的唯一主键，那由学号就可以确定姓名和年龄了，但是却不能确定课程名称和成绩。
2. 假设课程名称是表中的唯一主键，那由课程名称就可以确定学分了，但是却不能确定姓名、年龄和成绩。
3. 虽然通过学号和课程名称的联合主键，可以确定除联合主键外的所有的非主键值，但是基于上述两个假设，也不符合第二范式的要求。

那我们应该如何调整表结构，让它能复合第二范式的要求呢？

我们可以基于上述的三种主键的可能，拆分成 3 张表，保证一张表只描述一件事情。

学生表 - 学号做主键

课程表 - 课程名称做主键

成绩表 - 学号和课程名称做联合主键

这时候我们可能会想，为什么我们就要遵循第二范式呢？不遵循第二范式会造成什么样的后果呢？

• 造成整表的数据冗余。 如，学生表，可能我就只有2个学生，每个学生都有许多的信息，比如，年龄、性别、身高、住址......如果与课程信息放到同一张表中，可能每个学生有3门课程，那数据总条数就会变成6条了。 但是通过拆分，学生表我们只需要存储 2 条学生信息，课程表只需要存储 3 条课程信息，成绩表就只需保留学号、课程名称和成绩字段。
• 更新数据不方便。 假设，课程的学分发生了变更，那我们就需要把整表关于该课程的学分都要更新一次，但如果我们拆分出课程表，那我们就只需要把课程表中的课程信息更新就行。
• 插入数据不方便或产生异常。
  • 假设主键是学号或课程名称，我们新增了某个课程，需要把数据插入到表中，这时，可能只有部分人有选修这门课程，那我们插入数据的时候还要规定给哪些人插入对应的课程信息，同时可能由于成绩还没有，我们需要对成绩置空，后续有成绩后还得重新更新一遍。
  • 假设主键是学号和课程名称的联合主键。同样也是新增了某课程，但是暂时没有人选修这门课，缺少了学号主键字段数据，会导致课程信息无法插入。

第三范式（3NF）：在满足第二范式的情况下，消除传递依赖。即，在任一主键都可以确定所有非主键字段值的情况下，不能存在某非主键字段 A 可以获取 某非主键字段 B。

即：非主键列只依赖于主键，不依赖于其他非主键。

仍然用一个经典例子来解析：

这个表中，学号是主键，它可以唯一确定姓名、班级、班主任，符合了第二范式，但是在非主键字段中，我们也可以通过班级推导出该班级的班主任，所以它是不符合第三范式的。

那怎么设计表结构，才是符合第三范式的呢？

1. 学生表 学号姓名班级001小黄一年级（1）班
2. 班级表 班级班主任一年级（1）班高老师

通过把班级与班主任的映射关系另外做成一张映射表，我们就成功地消除了表中的传递依赖了。

姓名可能相同，所以通过姓名不能确定班级，所以无需拆分。

除了三大范式外，还有BC范式和第四范式，但其规范过于严苛，在生产中往往使用不到。

2.2 什么是范式和反范式，以及各自优缺点？

• 范式是符合某一种级别的关系模式的集合。 构造数据库必须遵循一定的规则。在关系数据库中，这种规则就是范式。

所以在平时工作中，我们通常是将范式和反范式相互结合使用。

我正在参与2023腾讯技术创作特训营第三期有奖征文，组队打卡瓜分大奖！