2-1 数据库之MySQL

# 2-1　数据库之 MySQL

## 1、数据库设计的基本步骤

1. **需求分析** : 分析用户的需求，包括数据、功能和性能需求。
2. **概念结构设计** : 主要采用 E-R 模型进行设计，包括画 E-R 图。
3. **逻辑结构设计** : 通过将 E-R 图转换成表，实现从 E-R 模型到关系模型的转换。
4. **物理结构设计** : 主要是为所设计的数据库选择合适的存储结构和存取路径。
5. **数据库实施** : 包括编程、测试和试运行
6. **数据库的运行和维护** : 系统的运行与数据库的日常维护。

## 2、数据库三范式概念

> **`范式`（ Normal Form）**，是英国人 E.F.Codd（关系数据库的老祖宗）在上个世纪70年代提出关系数据库模型后总结出来的，范式是关系数据库理论的基础，也是我们在设计数据库结构过程中所要遵循的规则和指导方法。目前有迹可寻的共有8种范式，依次是：1NF，2NF，3NF，BCNF，4NF，5NF，DKNF，6NF。

通常所用到的只是前三个范式，即：**第一范式（1NF），第二范式（2NF），第三范式（3NF）。**

+ **第一范式（1NF）**：要求属性不能被继续分割，是**数据库最基本的要求**。

+ 比如下面这个表不符合 1NF，将所有属性分解为最基本的属性即可。

+ **第二范式（2NF）**：**消除非主属性对于码的部分函数依赖** ，即在满足 1NF 的前提下，要求表必须有主键，并且没有包含在主键中的列必须完全依赖于主键，不能只依赖于主键的一部分。

+ 比如下面这个表不符合 2NF，主码为【学号，课程号】，而关系模式中存在【姓名】等属性依赖于【学号】的关系，即【姓名】等属性对【学号，课程号】存在部分函数依赖。

+ 将其转化为如下 2 个表，即可符合 2NF：

+ 学生信息表【学号，姓名，性别，系名，公寓名称】
    + 成绩表【学号，课程号，成绩】

+ *但是，如果一个系一栋或几栋公寓的前提下，上面这种转化方式不符合 3NF。*

+ **第三范式（3NF）**： **消除非主属性对于码的函数依赖** ，即在满足 2NF 的前提下，要求非主键列必须直接依赖于主键，不能存在传递依赖，即不能存在【非主键列 A 依赖于非主键列 B ，非主键列 B 依赖于主键】的情况。

+ 比如一个 订单表【订单ID，订单日期，客户ID，客户名称，客户地址】，其主键是【订单ID】：
    + 订单表中非主键列，均完全依赖于主键【订单ID】，符合 2NF；
    + 但非主键列中存在一种直接依赖关系，即【客户名称，客户地址】直接依赖于【客户ID】；
    + 【客户名称，客户地址】是通过【客户ID】传递依赖于主键【订单ID】，故不符合 3NF。
  + 将其转化为如下 2 个表，即可符合 3NF：
    + 订单表【订单ID，订单日期，客户ID】
    + 客户表【客户ID，客户名称，客户地址】

## 3、删除语句 drop、delete、truncate 的区别

+ `drop`：丢弃数据，作用于表，即将整个表的数据全部删除；
  + 用法：`drop table 表名`
+ `truncate`：清空数据，作用于表数据，即删除表中的全部数据，并将主键自增值初始化为1；
  + 用法：`truncate table 表名` 
+ `delete`：删除数据，作用于行数据，即删除符合 where 子句的数据，不加 where 和 truncate 类似。
  + 用法：`delete from 表名 where 列名=值`

## 4、存储过程的基本使用与优缺点

> **`存储过程`（Stored Procedure）**是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，**一次编译后永久有效，比单纯的SQL速度更快**，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
>
> **`存储过程`**中可以包含 **逻辑控制语句** 和 **数据操纵语句** , 它可以接受参数 , 输出参数 , 返回单个或多个结果集以及返回值。

**存储过程的优点：**

1. 存储过程 将复杂的 SQL 语句进行封装，在复杂查询过程中提供了一个替换位置；
2. 存储过程 在创建时即编译并存储在数据库中，其**运行速度比单纯的 SQL 语句更快**；
3. 存储过程 的调用只需要提供存储过程名和必要的参数信息，可以**减少网络流量，减轻网络负担**；
4. 存储过程 可以用于应用程序代码的不同位置，**代码精简一致**；
5. 存储过程 的访问权限（不基于表）可以向不同用户**分别授权**，**数据访问的安全性高**；
6. 存储过程 的参数数据类型，可以通过 SQLParameter 类指定，提高防御；
7. 提高代码安全，方式 SQL 注入。（但未彻底解决 , 例如将数据操作语言 `DML` 附加到输入参数）
8. 存储过程 的更新，比应用程序的更改、测试、部署需要的时间和精力更少，**可维护性高**【有争议】；

**存储过程的缺点：**

1.  难以调试和拓展，更没有拓展性
2. 存储过程 将应用程序绑定到 Server，所以使用存储过程封装业务逻辑将**限制应用程序的可移植性**。

**简单存储过程实现：**

创建

```sql
create procedure GetUsers()
begin 
	select * from user; 
end;
```

调用

```sql
call GetUsers();
```

删除

```
drop procedure if exists GetUsers;
```

**带参数的存储过程实现：**

MySql 支持 IN (传递给存储过程) , OUT (从存储过程传出) 和 INOUT (对存储过程传入和传出) 类型的参数 , 存储过程的代码位于 BEGIN 和 END 语句内 , 它们是一系列 SQL 语句 , 用来检索值 , 然后保存到相应的变量 (通过指定INTO关键字) ;

创建

```sql
create procedure GetNameByID(
	in userID int,
	out userName varchar(200)
)
begin
	select name from user
	where id = userID
	into userName;
end;
```

调用

```
call GetNameByID(1, @userName);
select @userName;
```

## 5、数据库引擎 MyISAM 和 InnoDB 的区别

## 6、模糊查询 % 和 * 通配符的区别

1. 作用不同

- *通配符的作用是用来匹配所有结果集。
   - %通配符的作用是用来替代一个或多个字符。

2. 优先级不同

*通配符的优先级比%通配符高，在同一条sql语句中，优先执行。

3. 针对范围不同

- *通配符的针对范围为一条记录的所有字段。
   - %通配符的针对范围为一条记录的单个字段。

## 7、MySQL 查询缓存（query_cache）

开启查询缓存后，在同样的查询条件以及数据情况下，会直接返回缓存中的结果。

**注意：**

+ 缓存能够**提升数据库的查询性能**；
+ 缓存也**带来额外的开销**，每次查询后都要做一次缓存操作，失效后还要销毁；
+ 查询缓存的**开启要谨慎**，尤其是写密集的应用。
+ 需要执行大量相同的 SQL 语句，且不需要频繁更改表时可开启。

查看 MySQL 是否已开启缓存：

```sql
SHOW VARIABLES LIKE 'have_query_cache';
```

![image-20220722163722539](https://img.zxdmy.com/2022/202207221637476.png)

更多操作：https://blog.csdn.net/weixin_56219549/article/details/123042365

具体可以在 `/usr/my.cnf` 中，添加设置并重启MySQL开启。

## 8、事务的 ACID 特性

> **是逻辑上的一组操作，要么都执行，要么都不执行。**

1. **原子性**（`Atomicity`） ： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
2. **一致性**（`Consistency`）： 执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
3. **隔离性**（`Isolation`）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
4. **持久性**（`Durability`）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

## 9、并发事务带来的问题

- **脏读（Dirty read）:** 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
- **丢失修改（Lost to modify）**
- **不可重复读（Unrepeatable read）:** 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
- **幻读（Phantom read）:** 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

## 10、事务的隔离级别

- **READ-UNCOMMITTED(读取未提交)：** 最低的隔离级别，允许读取尚未提交的数据变更，**可能会导致脏读、幻读或不可重复读**。
- **READ-COMMITTED(读取已提交)：** 允许读取并发事务已经提交的数据，**可以阻止脏读，但是幻读或不可重复读仍有可能发生**。
- **REPEATABLE-READ(可重复读)：** 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，**可以阻止脏读和不可重复读，但幻读仍有可能发生**。
- **SERIALIZABLE(串行化)：** 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，**该级别可以防止脏读、不可重复读以及幻读**。

## 11、索引的优缺点

**优点** ：

- 使用索引可以大大加快数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因。
- 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

**缺点** ：

- 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
- 索引需要使用物理文件存储，也会耗费一定空间。

## 12、索引的底层数据结构

- Hash

**为什么MySQL 没有使用其作为索引的数据结构呢？**

**1.Hash 冲突问题** ：我们上面也提到过Hash 冲突了，不过对于数据库来说这还不算最大的缺点。

**2.Hash 索引不支持顺序和范围查询(Hash 索引不支持顺序和范围查询是它最大的缺点：** 假如我们要对表中的数据进行排序或者进行范围查询，那 Hash 索引可就不行了。

- B & B+

**B 树& B+树两者有何异同呢？**

- B 树的所有节点既存放键(key) 也存放 数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
  - B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
  - B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。

## 13、索引的分类，及其特点与优缺点

## 14、创建索引的注意事项

- 选择合适的字段
  - 不为NULL的字段 , 如果需要 , 使用0 , 1 , true , false来代替
  - 被频繁查询的字段
  - 被作为条件查询的字段
  - 频繁需要排序的字段
  - 频繁连接的字段
- 被频繁更新的字段应谨慎建立索引
- 尽量考虑联合索引而非单列索引
- 尽量避免冗余索引
  - 能够命中索引(a, b)就肯定能命中索引(a) ，那么索引(a)就是冗余索引

## 15、数据库的MVCC（多版本并发控制）

正文

2-1 数据库之MySQL

相关阅读

2-1 数据库之MySQL

操作系统

计算机网络

Java8 新特性