1. 如何设计一个关系型数据库
要设计一个关系型数据库，首先将其划分为两大部分：存储部分和程序实例：
- 存储部分类似于一个文件系统，来将数据持久化到存储设备中。
还需要程序实例模块来对存储进行逻辑上的管理，而程序实例模块还需包含：
连接层：处理客户端的连接以及权限的验证。
- 服务层：查询语句的解析、优化、缓存以及内置函数的实现和存储。
- 引擎层：负责数据的存储和 提取。MySQL 中的事务是由存储引擎实现的，MySQL5.5.5 后默认使用 InnoDB，其他的存储引擎都不支持事务。
- 存储引擎：负责硬件设备的数据存储。
3. 一条 SQL 语句在数据库框架中的执行流程？
- 应用程序把查询 SQL 语句发送给服务器端执行。
- 查询缓存。之前执行过的语句及其结果都会以 key-value 对的形式，被直接缓存到内存中。其中，key 是查询的语句，value 是查询的结果。如果你的查询能够直接在缓存中找到 key，那么这个 value 就会直接返回给客户端。MySQL 8.0版本已经将查询缓存功能删除了。
- 经过解析器，对 SQL 语句进行解析，将 SQL 语句翻译成 MySQL 自己能认识的语言。
- 经过优化器，MySQL 优化器 会计算 「IO 成本 + CPU成本」，确定索引成本最小的执行方案。
- 经过执行器，调用存储引擎接口，开始执行 SQL 语句。所以真正执行 SQL 的动作是在存储引擎中完成的。
4. char 和 varchar 的区别？
- char：固定长度类型，比如 char(10)，当输入 “abc” 三个字符的时候，它们占的空间还是 10 个字节，其他 7 个是空字节。
- char 的优点是效率高，缺点是占用空间。
- 适用场景：存储密码的 md5 值，固定长度的场景使用 char 就比较合适。
- varchar：可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节。
所以，从空间上考虑 varchar 比较合适，从效率上考虑 char 比较合适，二者使用需要权衡。
5. bigint(20)、varchar(10) 括号里的数字代表的是什么？
括号里的数字叫数据的宽度，不同的数据类型对宽度的处理也不一样：
- 整数类型。这里显示的宽度和数据类型的取值范围是没有任何关系的，显示宽度只是指明 MySQL 最大可能显示的数字个数，数值的位数小于指定的宽度时会由空格填充；如果插入了大于显示宽度的值，只要该值不超过该类型的取值范围，数值依然可以插入，而且能够显示出来。例如一个字段，显示的宽度是 4，但是我向这个字段中插入 100001，也是可以的，保存和显示的都会是 100001；如果你不设置宽度，系统将添加默认的宽度 tinyint(4)、smallint(6)、mediumint(9)、int(11)、bigint(20) 等，这些默认的宽度是跟该类型的取值范围长度相关。
- 字符串类型。字符串类型才是真的用上了这个宽度。不管是 char 还是 varchar，宽度都定义了字符串的最大长度；例如对于一个字段 password varchar(20)，如果你输入了一个 21 个字符的密码，那么保存和显示的只会是前 20 个字符，你将丢失一个字符信息，char 同理。由于 varchar 是变长存储的，所以实际开发中我们一般都把 varchar 的宽度设为最长 255，反正你没用完它也不会浪费空间。
- 浮点和日期等数据类型对数据的宽度没有要求，一般也不设置，默认是0。
varchar(10) 和 varchar(20) 之间有什么区别？ varchar(n) 中 n 的含义是最多存放 n 个字符。如果存储 “hello”，varchar(10) 和 varchar(20) 所占空间一样，但后者在排序时会消耗更多内存。
6. MyISAM 和 InnoDB 的区别
两者的对比：
- 是否支持行级锁：MyISAM 只有表级锁，而 InnoDB 支持行级锁和表级锁，默认为行级锁。
- 是否支持事务： MyISAM 不提供事务支持，而 InnoDB 提供事务支持。MYISAM 适合用于大量查询少量插入的场景来使用。
- 是否支持外键：MyISAM 不支持，而 InnoDB 支持。
- 是否支持MVCC：仅 InnoDB 支持。应对高并发事务, MVCC 比单纯的加锁更高效。MVCC 只在
READ COMMITTED和REPEATABLE READ两个隔离级别下工作。MVCC 可以使用乐观锁和悲观锁来实现，各数据库中的 MVCC 实现并不统一。 - InnoDB 的数据和索引是存储在同一个文件中的，而 MyISAM 的数据和索引是分开存储的。
7. MySQL 三大日志
7.1 Undo Log 日志
7.1.1 什么是 Undo Log？
顾名思义，Undo Log 的字面意思就是撤销操作的日志，指的是使 MySQL 中的数据回到某个状态。在事务开始之前，MySQL 会将待修改的记录保存到 Undo Log 中，如果数据库崩溃或者事务需要回滚时，MySQL 可以通过 Undo Log 将数据库中的数据回滚到之前的状态。Undo Log 是一种逻辑日志， 记录的是一个变化过程。比如，MySQL 执行一个 delete 操作，Undo Log 就会记录一个 insert 操作；MySQL 执行一个 insert 操作，Undo Log就会记录一个 delete 操作；MySQL 执行一个 update 操作，Undo Log 就会记录一个相反的 update 操作。
7.1.2 Undo Log 实现事务的原子性
为了满足事务的原子性，在操作任何数据之前，首先要把数据备份到 Undo Log，用于存放数据被修改前的值。在事务的处理过程中，如果 MySQL 出现了错误或者用户手动执行了 rollback 操作，MySQL 可以通过 Undo Log 日志将数据库中的数据恢复到之前的状态。
7.1.3 Undo Log 实现事务的多版本并发控制
Undo Log 实现了 MySQL 事务的多版本并发控制（MVCC）。事务未提交前，Undo Log 保存了未提交之前的版本数据，Undo Log 中的数据可以作为旧版本数据的副本或者快照以便其他并发事务进行读取操作。
如上图所示，事务 A 手动开启事务后，对 goods 数据表中 id=1 的数据进行更新操作，首先会把事务发生之前的数据写入到 Undo Buffer 中。在事务 A 未提交之前，此时，事务 B 手动开启事务，对 goods 数据表中的 id=1 的数据进行查询操作，此时的事务 B 会读取 Undo Log 中的数据并返回给客户端，这就是 MySQL 中的 MVCC 机制。
7.2 Redo Log 日志
7.2.1 什么是 Redo Log？
顾名思义，Redo Log 的字面意思就是重做日志，指的是在数据库出现意外情况时能够对重新执行某种操作。在 MySQL 中，事务中修改的任何数据，都会将最新的数据写入 Redo Log 中进行备份。
7.2.2 Redo Log 实现事务的持久性
当数据库对数据做修改的时候，需要把数据页从磁盘读到 Buffer 中，然后在 Buffer Pool 中进行修改。那么此时 Buffer Pool 中的数据页就和磁盘中数据页的不一致，此时 Buffer Pool 中的数据页称为『脏数据』。如果此时发生非正常的数据库重启，那么这些脏数据还没有同步到磁盘的数据页中（同步到磁盘文件是个随机 IO），会发生数据丢失。Redo Log 的作用就是在 Buffer Pool 中的数据页修改之后，记录相应的修改记录（记录日志是顺序 IO）。那么当数据库发生非正常的重启的时候，可以根据 Redo Log 的记录将脏数据进行重做。
当事务操作的脏页写入到磁盘之后，Redo Log 的使命也就完成了，此时，Redo Log 日志占用的空间可以重新利用，会被后续产生的 Redo Log 日志覆盖。
7.3 Bin Log 日志
7.3.1 什么是 Bin Log？
Bin Log 记录所有 MySQL 数据库表结构变更以及表数据修改的二进制日志，不会记录 select 和 show 这类查询操作的日志，它是一个二进制文件。Bin Log 日志是以事件形式记录，还包含语句所执行的消耗时间等其他额外信息。开启 Bin Log 日志有以下两个最重要的使用场景：
- 主从复制：在主库中开启 Bin Log 功能，这样主库就可以把 Bin Log 传递给从库，从库拿到 Bin Log 后实现数据恢复达到主从数据一致性。
- 数据恢复：通过 mysqlbinlog 等工具来恢复数据。
7.3.2 Bin Log 的文件记录模式
- ROW（row-based replication, RBR）：日志中会记录每一行数据被修改的情况，然后在 slave 端对相同的数据进行修改。优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。缺点：批量操作，会产生大量的日志，尤其是alter table会让日志暴涨。
- STATMENT（statement-based replication, SBR）：每一条被修改数据的SQL都会记录到 master 的 Binlog 中，slave 在复制的时候 SQL 进程会解析成和原来 master 端执行过的相同的 SQL 再次执行。简称 SQL 语句复制。优点：日志量小，减少磁盘IO，提升存储和恢复速度。缺点：在某些情况下会导致主从数据不一致，比如 last_insert_id()、now() 等函数。
- MIXED（mixed-based replication, MBR）：以上两种模式的混合使用，一般会使用 STATEMENT 模式保存binlog，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog，MySQL 会根据执行的 SQL 语句选择写入模式 。
8. 数据库三范式
- 1范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；(只要是关系型数据库都满足1NF)
- 2范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；
- 3范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。没有冗余的数据库设计可以做到。
但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据，具体做法是：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑，降低范式就是增加字段，允许冗余。
9. 数据库删除操作中的 delete、drop、 truncate 区别在哪？
- 当不再需要该表时可以用 drop 来删除表。
- 当仍要保留该表，但要删除所有记录时，用 truncate 来删除表中记录。
- 当要删除部分记录时（一般来说有 WHERE 子句约束） 用 delete 来删除表中部分记录。
10. SQL 语句有哪些分类？
- DDL：数据定义语言（create alter drop）
- DML：数据操作语句（insert update delete）
- DTL：数据事务语句（commit collback savapoint）
- DCL：数据控制语句（grant revoke）
11. 语法部分
SQL 执行顺序
from -> on -> join -> where -> group by -> having -> select -> distinct -> union -> order by -> limit查询所有同学的学号、选课数、总成绩
select student_id, count(course_id), sum(score)from scoregroup by student_id;
查询平均成绩大于60分的同学的学号和平均成绩
HAVING：
- 通常与 GROUP BY 子句一起使用
- WHERE 过滤行，HAVING 过滤组
- 出现在同一 SQL 的顺序：WHERE > GROUP BY > HAVING
- 如果省略了 GROUP BY 子句，HAVING 子句的行为就和 WHERE 子句一样
select student_id, avg(score) from score group by student_id having avg(score) > 60;查询没有学全所有课的同学的学号、姓名
select stu.student_id, stu.name from student stu, score s, where stu.student_id = s.student_id group by s.student_id having count(*) < ( select count(*) from course );12. 什么是数据库连接池？
在系统和 MySQL 进行交互之前，MySQL 驱动会帮我们建立好连接，然后我们只需要发送 SQL 语句就可以执行 CRUD 了。一次 SQL 请求就会建立一个连接，多个请求就会建立多个连接。然而我们的 web 系统一般都是部署在 tomcat 容器中的，而 tomcat 是可以并发处理多个请求的，这就会导致多个请求会去建立多个连接，然后使用完再都去关闭。那这样势必会造成不必要的浪费和性能的下降，也就是多线程请求的时候频繁的创建和销毁连接显然是不合理的，这必然会大大降低我们系统的性能。这时候如果给我们提供一些固定的用来连接的线程，这样是不是不需要反复的创建和销毁连接了呢？
这就是数据库连接池：维护一定的连接数，方便系统获取连接，使用就去池子中获取，用完放回去就可以了，我们不需要关心连接的创建与销毁，也不需要关心线程池是怎么去维护这些连接的。采用连接池大大节省了不断创建与销毁线程的开销，这就是有名的「池化」思想，不管是线程池还是 HTTP 连接池，都能看到它的身影。
所以，我们的系统在访问 MySQL 数据库的时候，建立的连接并不是每次请求都会去创建的，而是从数据库连接池中去获取，这样就解决了因为反复的创建和销毁连接而带来的性能损耗问题了。不过这里有个小问题，业务系统是并发的，而 MySQL 接受请求的线程是否也是池化了的呢？答案是肯定的。MySQL 的架构体系中也已经提供了这样的一个池子，也是数据库连接池。双方都是通过数据库连接池来管理各个连接的，这样一方面线程之前不需要是争抢连接，更重要的是不需要反复的创建的销毁连接。
参考
若有收获，就点个赞吧
0 人点赞
