《Java面试自救指南》（专题三）数据库

一条sql语句的查询流程

1. 基本查询流程：

（1）客户端通过数据库连接池发送一条查询给服务器。

（2）服务器先检查缓存，如果命中缓存，则立刻返回缓存结果。否则进入下一阶段。

（3）服务器端的分析器进行语法验证、SQL解析、预处理

（4）优化器依据成本最小原则来选择索引，生成对应的执行计划。

（5）MySQL根据优化器生成的执行计划，再调用存储引擎的API来执行查询。

（6）将查询结果返回给客户端。

2. 缓冲池

每次查询的结果会存到缓冲池 Buffer Pool 中，这样后面再有请求的时候就会先从缓冲池中去查询，如果没有再去磁盘中查找，然后在放到 Buffer Pool 中。

3. 优化器

MySQL使用基于成本的查询优化器(Cost-Based Optimizer，CBO)。它会尝试预测一个查询使用某种执行计划时的成本，并选择其中成本（IO & CPU 成本）最少的一个，IO 成本即从磁盘把数据加载到内存的成本，和页大小有关；CPU 成本和读取数据行数有关。

优化器会根据优化规则对关系表达式进行转换，这里的转换是说一个关系表达式经过优化规则后会生成另外一个关系表达式，同时原有表达式也会保留，经过一系列转换后会生成多个执行计划，然后CBO会根据统计信息和代价模型(Cost Model)计算每个执行计划的Cost，从中挑选Cost最小的执行计划。

4. sql查询语句详解
   每一个复杂sql语句执行都会拆分成若干步骤，并且都是从 FROM 开始执行的。每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入。以下面的语句为例，执行步骤如序号所示：
   

链接：
https://juejin.cn/post/6844903655439597582
https://www.nowcoder.com/discuss/353157489787084800

有哪些数据库存储引擎，各自的区别

1. 引擎查看命令：

SHOW ENGINES
SHOW VARIABLES LIKE 'storage_engine';

输出：

2. MyISAM存储引擎

（1）锁级别为表级锁

MyISAM的表锁有两种方式，一种是读共享锁，另一种是写独占锁，读共享锁是指当一个请求读取一个表时，也允许其他请求读取这个表；而写独占锁的就是指当一个请求是修改一个表时，阻塞其他请求读取和修改这个表。

（2）不支持事务和全文索引

（3）索引为非聚簇索引

• 聚簇索引：索引所采用的树结构的叶子节点储存的数据

• 非聚簇索引：索引所采用的树结构的叶子节点储存的不是数据，而是数据的地址

（4）适合查询比较频繁的场景，插入，更改比较少，无需事务的场景

3. InnoDB存储引擎

（1）锁级别为行级锁

InnoDB的锁最高级别为行级锁，但同时支持表级锁和行级锁，InnoDB的行级锁是通过给索引项加锁来实现的，当不通过索引时，InnoDB还是启用的表级锁，行级锁的获取锁和释放锁的过程都会占用很多的资源，所以InnoDB不适合查询过于频繁的场所。

（2）支持事务和外键，5.6.4版本之后支持全文索引（FullText）

InnoDB的设计是为了适应高并发的场景，所以InnoDB提供了具有提交、回滚和崩溃恢复能力的事务，同时支持外键。

// content列添加全文索引
create table test (
    id int(11) unsigned not null auto_increment,
    content text not null,
    primary key(id),
    fulltext key content_index(content)
) engine=MyISAM default charset=utf8;

// target为检索文本，注意target有最小/大搜索长度限制
select * from fulltext_test 
    where match(content,tag) against('target');

（3）索引为聚簇索引

InnoDB在索引上进行了很大的优化，将数据直接放在了主索引叶子上，这样主索引便可以直接找到对应的数据，当调用辅助索引的时候会产生回表操作，辅助索引树的叶子节点储存的是主键，所以会再次会到主索引上再次查找，最终获取到数据。

（4）适合并发性较高，更改表比较频繁的，安全要求较高的场景

4. MEMORY存储引擎

（1）MEMORY存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。但如果mysqld进程发生异常、重启或计算机关机等等都会造成这些数据的消失，所以这种存储引擎中的表的生命周期很短，一般只使用一次。
（2）如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果。

5. 总结

链接：
https://www.nowcoder.com/discuss/353157391682314240?sourceSSR=users
https://developer.aliyun.com/article/636314
https://www.nowcoder.com/discuss/353157479020306432

数据库的三大范式

教材中范式的定义：符合某一种级别的关系模式的集合，表示一个关系内部各属性之间的联系的合理化程度。（看不懂~~）

1. 第一范式1NF (确保每列保持原子性)

是最基本的范式，符合1NF的关系中的每个属性都不可再分，或者理解为每个字段不需要再次拆分以达到更好的数据库性能。以下关系违反1NF。

仅仅符合1NF的设计，仍然会存在数据冗余过大，插入异常，删除异常，修改异常的问题。

以学校场景为例，一张学生表找不到任何一条记录，它们的学号相同而对应的姓名不同。所以我们可以说姓名函数依赖于学号，写作 学号 → 姓名。

2. 第二范式2NF (确保表中的每列都和主键相关)

在第一范式的基础上更进一步，需要确保数据库表中的每列均与主键相关，而不能只与主键的某一部分相关（主要针对联合主键，称为部分函数依赖）。即 在一个数据库表中，一个表中只能保存一种数据，不能将多种数据保存在同一张数据库表中。消除这些部分函数依赖，只有一个办法，就是将大数据表拆分成两个或者更多个更小的数据表，在拆分的过程中，要达到更高一级范式的要求，这个过程叫做”模式分解“。

稍微补充两个概念，明确2NF和3NF的区别。

码：关系中的某个属性或者某几个属性的组合，用于区分每个元组（可以把“元组”理解为一张表中的每条记录，也就是每一行）。

设 K 为某表中的一个属性或属性组，假如当 K 确定的情况下，该表除 K 之外的所有属性的值也就随之确定，那么 K 就是码。

3. 第三范式3NF

需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。如果有，可以拆分成多张表，并用外键连接。3NF在2NF的基础之上，消除了非主属性对于码的传递函数依赖（直接相关）。

消除了学号对系主任的传递函数依赖。

链接：
https://www.zhihu.com/question/24696366（强推）
https://zhuanlan.zhihu.com/p/63146817

事务的四大特性（含隔离级别）

1. ACID四大特性

（1）原子性（Atomicity）

原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

（2）一致性（Consistency）

一致性是指一个事务执行之前和执行之后，数据库都必须处于一致性状态，比如在转账中，不能存在A减少，B没增加的情况，即要求 数据能够保持一致，多个事务对统一数据读取的结果相同。

（3）隔离性（Isolation）

隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。</