1. 数据库基础

1.1 数据库基础

1.1.1 数据库、数据库管理系统、数据库系统和数据库管理员

• 数据库：数据的集合或数据库管理系统管理的数据的集合；
• 数据库管理系统：操纵和管理数据库的大型软件，通常用于建立、使用和维护数据库；
• 数据库系统：由软件、数据库和数据管理员组成；
• 数据库管理员：负责全面管理和控制数据库系统。

1.1.2 数据库管理系统的种类

数据库管理系统主要通过数据的保存格式（数据库的种类）来进行分类，目前主要有以下几种类型：

• 层次数据库（Hierarchical Database，HDB）
• 关系数据库（Relational Database，RDB）
  • Oracle Database：甲骨文公司的RDBMS
  • SQL Server：微软公司的RDBMS
  • DB2：IBM公司的RDBMS
  • PostgreSQL：开源的RDBMS
  • MySQL：开源的RDBMS（本笔记重点学习内容）
• 以上5种是具有代表性的RDBMS，其特点是由行和列组成的二维表来管理数据，这种类型的 DBMS 称为关系数据库管理系统（Relational Database Management System，RDBMS）。
• 面向对象数据库（Object Oriented Database，OODB）
• XML数据库（XML Database，XMLDB）
• 键值存储系统（Key-Value Store，KVS），比如MongoDB

1.1.3 数据库相关概念

• 元组：
  • 元组是关系数据库中的基本概念；
  • 关系是一张表，表中的每行（即数据库中的每条记录）就是一个元组，每列就是一个属性；
  • 在二维表里，元组也称为行。
• 码：
  • 码就是能唯一标识实体的属性，对应表中的列；
• 候选码 ：
  • 关系中的某一属性或属性组的值能唯一的标识一个元组，而其任何、子集都不能再标识，则称该属性组为候选码；
  • 例如：在学生实体中，“学号”是能唯一的区分学生实体的，同时又假设“姓名”、“班级”的属性组合足以区分学生实体，那么{学号}和{姓名，班级}都是候选码。
• 主键（主码）:
  • 用于唯一标识一个元组，不能有重复，不允许为空；
  • 一个表中只能有一个主码，但可以有多个候选码。
• 外键（外码）:
  • 如果一个关系表中的一个属性是另外一个关系表中的主键则这个属性为外键；
  • 用来和其他表建立联系用，一个表可以有多个外键。
• 主属性：
  • 候选码中出现过的属性称为主属性；
  • 比如：关系工人（工号，身份证号，姓名，性别，部门）。显然工号和身份证号都能够唯一标示这个关系，所以都是候选码。工号、身份证号这两个属性就是主属性。如果主码是一个属性组，那么属性组中的属性都是主属性。
• 非主属性：
  • 不包含在任何一个候选码中的属性称为非主属性；
  • 比如在关系 —— 学生（学号，姓名，年龄，性别，班级）中，主码是“学号”，那么其他的“姓名”、“年龄”、“性别”、“班级”就都可以称为非主属性。

1.1.4 E-R图

E-R 图也称实体-联系图(Entity Relationship Diagram)，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型。它是描述现实世界关系概念模型的有效方法。是表示概念关系模型的一种方式。
下图是一个学生选课的E-R图，其中：

image-20220527141307120

1.1.5 关系型数据库管理系统的结构

一般的RDBMS的系统结构是客户端/服务器型(C/S型)结构，如下图所示：

image-20220612195043138

1.2 MySQL架构

1.2.1 MySQL基础架构

image-20220613201127586
上图表示了MySQL的架构，主要有以下几部分组成：

• 连接器： 身份认证和权限相关，主要作用在MySQL登录过程和语句执行过程；
• 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除）；
• 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器主要就是检查我们实现的 SQL 语句要做什么嘛，再检查语法是否正确。
• 优化器： 按照 MySQL 认为最优的方案去执行；
• 执行器： 执行语句，然后从存储引擎返回数据。 执行语句之前会先判断是否有权限，如果没有权限的话，就会报错；
• 插件式存储引擎 ： 主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎。

简单来说 MySQL 主要分为 Server 层和存储引擎层：

• Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binlog 日志模块。
• 存储引擎： 主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redo log 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5 版本开始就被当做默认存储引擎了。

1.2.2 MySQL 存储引擎

MySQL 5.5.5 之前，MyISAM 是 MySQL 的默认存储引擎。5.5.5 版本之后，InnoDB 是 MySQL 的默认存储引擎。MyISAM和InnoDB的主要区别如下：

【注意】：
虽然MyISAM和InnoDB的索引都为B+树，但是实现细节却不同（详见聚集索引和非聚集索引）。

1.2.3 SQL 语句执行过程

• 查询语句的执行流程如下：权限校验（如果命中缓存）—-> 查询缓存 —-> 分析器 —-> 优化器 —->权限校验 —-> 执行器 —-> 引擎
• 更新语句执行流程如下：分析器 —-> 权限校验 —-> 执行器 —-> 引擎 —-> redo log(prepare 状态) —-> bin log —-> redo log(commit状态)

1.3 数据库范式

• 第一范式：
  • 属性（对应于表中的字段）不能再被分割，也就是这个字段只能是一个值，不能再分为多个其他的字段了；
  • 1NF 是所有关系型数据库的最基本要求 ，也就是说关系型数据库中创建的表一定满足第一范式。
• 第二范式：
  • 2NF 在 1NF 的基础之上，消除了非主属性对于码的部分函数依赖。也就是说，所有的非主属性都依赖于主键，比如下面的例子：
  • 
  • image-20220630095559676
• 第三范式
  • 3NF 在 2NF 的基础之上，消除了非主属性对于码的传递函数依赖 ，即所有的非主属性都要与主属性直接函数相关。符合 3NF 要求的数据库设计，基本上解决了数据冗余过大，插入异常，修改异常，删除异常的问题。比如在关系 R(学号 , 姓名, 系名，系主任)中，学号 → 系名，系名 → 系主任，所以存在非主属性系主任对于学号的传递函数依赖，所以该表的设计，不符合 3NF 的要求；

相关的概念：

• 函数依赖（functional dependency）：若在一张表中，在属性（或属性组）X 的值确定的情况下，必定能确定属性 Y 的值，那么就可以说 Y 函数依赖于 X，写作 X → Y。
• 部分函数依赖（partial functional dependency）：如果 X→Y，并且存在 X 的一个真子集 ，使得 → Y，则称 Y 对 X 部分函数依赖。比如学生基本信息表 R 中（学号，身份证号，姓名）当然学号W属性取值是唯一的，在 R 关系中，（学号，身份证号）→（姓名），（学号）→（姓名），（身份证号）→（姓名）；所以姓名部分函数依赖与（学号，身份证号）；
• 完全函数依赖（Full functional dependency）：在一个关系中，若某个非主属性数据项依赖于全部关键字称之为完全函数依赖。比如学生基本信息表 R（学号，班级，姓名）假设不同的班级学号有相同的，班级内学号不能相同，在 R 关系中，（学号，班级）→（姓名），但是（学号）→ (姓名) 不成立，（班级）→ (姓名) 不成立，所以姓名完全函数依赖与（学号，班级）；
• 传递函数依赖 ：
  • 在关系模式 R(U) 中，设 X，Y，Z 是 U 的不同的属性子集，如果 X 确定 Y、Y 确定 Z，且有 X 不包含 Y，Y 不确定 X，（X∪Y) ∩ Z = 空集合，则称 Z 传递函数依赖(transitive functional dependency) 于 X；
  • 传递函数依赖会导致数据冗余和异常。传递函数依赖的 Y 和 Z 子集往往同属于某一个事物，因此可将其合并放到一个表中。比如在关系 R(学号 , 姓名, 系名，系主任) 中，学号 → 系名，系名 → 系主任，所以存在非主属性系主任对于学号的传递函数依赖。

1.3 高频面试题

• 数据库设计分为几步？
  • 需求分析：分析用户的需求，包括数据、功能和性能需求；
  • 概念结构设计：采用E-R模型进行设计，包括画E-R图；
  • 逻辑结构设计：E-R图转换为表，实现从E-R模型到关系模型的转变；
  • 物理结构设计：为所设计的数据库选择合适的存储结构和存取路径；
  • 数据库实施：包括编程、测试和试运行；
  • 数据库的运行和维护：系统的运行和数据库的日常维护。
• 
  
• 数据库设计的三大范式：
  • 每列的字段不可分割；
  • 表中的每列都和主键相关；
  • 表中的每列都和主键直接相关。
• 
  
• 简单介绍下MySQL主键：
• 主键由表中的一个或者多个字段组成，可以唯一标识一条记录，它有以下三个特点：
  • 主键值必须唯一；
  • 不能包含Null值；
  • 该值的属性一定是自增的（提高存取效率）。
• 
  
• 为什么不建议使用外键和级联操作？
  • 以学生和成绩的关系为例，学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。
  • 外键与级联更新适用于单机低并发，不适合分布式、高并发集群;
  • 级联更新是强阻塞，存在数据库更新风暴的风险;
  • 外键影响数据库的插入速度。
• 
  
• 为什么插入语句中redo log要有预提交和提交两种状态？
  • 先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 binlog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。
  • 先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。
• 综上所述，引入两种状态可以解决数据一致性的问题。
• 
  

• InnoDB引擎和MyISAM引擎的区别？ |
  -

  |
  - MyISAM
  |
  - InnoDB
  | | —- | —- | —- | |
  - 外键
  |
  - 不支持
  |
  - 支持
  | |
  - 事务
  |
  - 不支持
  |
  - 支持
  | |
  - 锁
  |
  - 表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作
  |
  - 行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作
  | |
  - 缓存
  |
  - 只缓存索引，不缓存真实数据
  |
  - 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
  |

• MyISAM在什么场景下比InnoDB好？
  • MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择；
  • 对于不会进行修改的表，MyISAM支持压缩表 ，极大地减少了磁盘空间的占用。

参考资料

• Java Guide：https://gitee.com/SnailClimb/JavaGuide/#mysql