当前位置：首页 > news >正文

秋招Java后端开发冲刺——关系型数据库篇（Mysql）

news 文章来源：https://blog.csdn.net/qq_50604294/article/details/139882670 2024/7/6 18:59:55

本文介绍关系型数据库及其代表Mysql数据库，并介常见面试题目。
在这里插入图片描述

一、数据库概述

1. 数据库（Database, DB）：是长期储存在计算机内的、有组织的、可共享的数据集合。
2. 数据库管理系统（Database Management System, DBMS）：是一种操纵和管理数据库的大型软件。
3. 数据库系统（Database System, DBS）：是计算机系统、数据库(DB)、数据库管理系统(DBMS)、应用软件、数据库管理员(DBA)和用户的全体(集合)。
三者之间的关系为：
在这里插入图片描述

二、关系数据库基础

1. 定义：采用了关系模型来组织数据的数据库，其以行和列的形式存储数据。
2. 特点

特点	说明
固定的表结构	使用预定义的表结构和关系模型，表中的每个列都有明确定义的数据类型和约束条件。
强一致性	遵循ACID（原子性、一致性、隔离性、持久性）事务特性，保证事务的原子性和数据的一致性。
SQL 查询语言	使用结构化查询语言（SQL）进行数据操作和管理，支持复杂的查询、连接和数据处理操作。
事务支持	提供强大的事务支持，保证在事务中的操作要么全部提交成功，要么全部回滚，确保数据的完整性。
规范化	通过规范化设计数据库模式，减少数据冗余，确保数据存储的一致性和节省存储空间。
复杂的关系	支持复杂的表关系（一对一、一对多、多对多），通过外键保持数据之间的完整性和关联。
成熟稳定	已经经过长时间的发展和优化，成熟的产品和广泛的使用基础，具备稳定性和可靠性。
ACID事务	保证事务的原子性、一致性、隔离性和持久性，适合处理需要强一致性和事务控制的应用场景。
复杂查询优化	通过索引和查询优化器优化复杂查询，提高查询效率，支持大规模数据处理和高性能应用。
数据完整性	使用约束（如主键、外键、唯一约束）和触发器来保持数据的完整性和一致性。

3. 代表：Oracle、DB2、MySQL、Microsoft SQL Server、Microsoft Access

4. 候选码、主码、外码

候选码：若关系中的某一属性或属性组的值能唯一的标识一个元组，而其任何、子集都不能再标识，则称该属性组为候选码。
主码（主键）：主码是从候选码中选出来的，一个实体集中只能有一个主码。
外码（外键）：一个关系中的一个属性是另外一个关系中的主码则这个属性为外码。
注：不推荐使用，建议在应用层建立外键约束，而不是在数据库中建立实际外键，降低开发复杂性。
主属性：候选码中出现过的属性称为主属性。
非主属性：不包含在任何一个候选码中的属性称为非主属性。

5. ER图（实体关系图）
在这里插入图片描述

6. 数据库范式（某大厂数据工程一面）

范式	定义与要求	特点
第一范式 (1NF)	要求每个字段都是不可再分的数据单元，确保数据表中的每一列保持原子性。	消除了重复的列，确保每列都包含单一的数据类型。
第二范式 (2NF)	在满足1NF的基础上，要求表中的非主属性完全依赖于主键，即不存在部分依赖。	消除了非主属性对主键部分依赖的问题。
第三范式 (3NF)	在满足2NF的基础上，要求表中的非主属性不依赖于其他非主属性，即不存在传递依赖。	消除了非主属性之间的传递依赖。
巴斯-科得范式 (BCNF)	在满足3NF的基础上，要求每一个非主属性都完全依赖于候选键的每一个超键，即不存在依赖关系的例外情况。	强化了对主键的依赖关系，确保所有非主属性完全依赖于候选键。
第四范式 (4NF)	在满足BCNF的基础上，要求消除表中的多值依赖关系，即一个属性依赖于另一个属性时，不能有多个独立的值。	消除了多值依赖，确保一个属性不依赖于另一个属性的多个独立值。
第五范式 (5NF)	在满足4NF的基础上，要求消除表中的连接依赖，即表中的数据在无损分解之后能够重新连接起来，且数据不发生丢失。	消除了连接依赖，确保数据在分解后能够无损连接。
第六范式 (6NF)	在满足5NF的基础上，进一步规范化数据结构，确保数据的最小冗余，适用于时间相关数据的规范化。	在时间相关的数据处理中，提供了更高的规范化程度，确保数据的最小冗余。

7. 函数依赖

若在一张表中，在属性（或属性组）X 的值确定的情况下，必定能确定属性 Y 的值，那么就可以说 Y 函数依赖于 X，写作 X → Y。
部分函数依赖：如果 X→Y，并且存在 X 的一个真子集 X0，使得 X0→Y，则称 Y 对 X 部分函数依赖。
完全函数依赖：在一个关系中，若某个非主属性的值依赖于全部关键字称之为完全函数依赖。
传递函数依赖：在关系模式 R(U)中，设 X，Y，Z 是 U 的不同的属性子集，如果 X 确定 Y、Y 确定 Z，且有 X 不包含 Y，Y 不确定 X，（X∪Y）∩Z=空集合，则称 Z 传递函数依赖于 X。
8. 存储过程：在其中增加了逻辑控制语句的SQL 语句的集合。
优点：存储过程是预编译过，运行速度快，适合处理复杂业务
缺点：难以调试和扩展，且没有移植性

三、SQL总结

1. 创建、展示、删除、切换、修改数据库

create database 数据库名称;
show database 数据库名称;
drop database 数据库名称;
user 数据库名称;
alter database 数据库名称 character set gbk

2. 数据表操作
（1）创建、展示、删除数据表

create table 数据表名(字段名 数据类型,字段名 数据类型,字段名 数据类型,	
);
show table 数据表名;
drop table 数据表名;

（2）修改数据表（某大厂数据工程一面：如何添加字段）

##修改表名
alter table 数据表名 rename to 修改后数据表名;
##修改字段名
alter table 数据表名 change 字段名 修改后字段名 数据类型;
##修改字段类型
alter table 数据表名 modify 字段名 数据类型;
##增加字段
alter table 数据表名 add 字段名 数据类型;
##删除字段
alter table 数据表名 drop 字段名;

3. drop、delete 与 truncate 的联系与区别

特性	DROP	DELETE	TRUNCATE
作用对象	整个表、数据库、视图、索引等	表中的特定行	整个表
是否删除表结构	是	否	否
数据恢复	不能恢复（除非有备份）	可以通过回滚恢复	不能恢复（除非有备份）
触发器触发	不触发	触发	不触发
事务控制	无法回滚（在大多数数据库中）	可以回滚	无法回滚（在大多数数据库中）
自动增量 (AUTO_INCREMENT)	移除表后重置	不重置	重置
删除速度	最快	最慢	快
删除条件	无条件删除整个对象	可以指定条件删除特定记录	无条件删除整个表的数据
SQL 标准	SQL 标准	SQL 标准	SQL 标准
空间释放	释放所有关联的空间	不一定释放空间	释放大部分空间

4. 数据表约束

主键约束（PRIMARY KEY）：用于唯一标识对应的记录
外键约束（FOREIGN KEY）：用于关联两个表
非空约束（NOT NULL）：用于字段值规定
唯一约束（UNIQUE）：用于字段约束
默认值约束（DEFAULT）：设置字段默认值

5. 数据库查询

普通查询
多表连接查询
（1）交叉连接查询：查询两个数据表的笛卡尔积（数据组合）

SELECT * FROM 表1 CROSS JOIN 表2;

（2）内连接查询：查询两个数据表满足连接条件的数据行

SELECT 查询字段1,查询字段2, ... FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段=表2.关系字段

（3）左外连接查询：查询两个数据表中右表满足连接条件的数据行和左边数据表全部行

SELECT 查询字段1,查询字段2, ... FROM 表1 LEFT [OUTER] JOIN 表2 ON 表1.关系字段=表2.关系字段 WHERE 条件

（4）右外连接查询：查询两个数据表中左表满足连接条件的数据行和右边数据表全部行

SELECT 查询字段1,查询字段2, ... FROM 表1 RIGHT [OUTER] JOIN 表2 ON 表1.关系字段=表2.关系字段 WHERE 条件

注：outer可省略

子查询：在查询中嵌套查询（any，all，exist）

6. 视图

视图是基于 SQL 语句的结果集的可视化的表，是虚拟的表，本身不包含数据，也就不能对其进行索引操作
基本语法：

##创建
CREATE VIEW 视图名 AS
SELECT 列
FROM 表
WHERE 查询条件;
##删除
DROP VIEW 视图名;

7. 触发器

当对表进行插入、更新、删除操作的时候会自动执行的特殊的存储过程
作用：数据一致性维护、日志记录和审计、自动计算字段
基本语法

create trigger 触发器的名字
<before|after>  #定义触发器的触发时机
<新增insert|删除delete|修改update> #定义数据操作语言DML类型
on 表名  #指定具体是哪张表
for each row  #声明为行级触发器（只要操作一条记录就能触发触发器执行一次）
具体的触发器操作

8. 事务：用于保证数据的一致性

START TRANSACTION - 指令用于标记事务的起始点。
SAVEPOINT - 指令用于创建保留点。
ROLLBACK TO - 指令用于回滚到指定的保留点；如果没有设置保留点，则回退到 START - TRANSACTION 语句处。
COMMIT - 提交事务。

四、Mysql数据库

Mysql最流行的关系型数据库管理系统之一，将数据以表的形式存储，体积小、速度快。（某大厂数据工程一面）

（一）基本数据类型

1. 整型：TINYINT（1字节）、SMALLINT（2字节）、MEDIUMINT（3字节）、INT（4字节，常用）和 BIGINT（8字节）
2. 浮点型：FLOAT（4字节）、DOUBLE（8字节）和DECIMAL（M,D）（M表示数据的长度，D表示的是小数点后的长度）
注：FLOAT和DOUBLE存在浮点数精读丢失的问题，因此在存储金额等需要准确无误的数据时需要使用DECIMAL。
3. 字符串类型：CHAR（定长）和VARCHAR（变长，一般不确定长度的时候建议使用，节约资源）
4. 文本类型（存储一些描述性文字）：TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
5. 日期与时间类型：YEAR、DATE、TIME、DATETIME 和 TIMESTAMP
6. 二进制类型（存储图片、文件等）：TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB

（二）基本架构

在这里插入图片描述

1. Mysql主要由两部分组成：存储引擎和Server层，server层又包括：连接器、查询缓存、分析器、优化器、执行器和一个日志模块（binlog）。各个部分的功能如下：

连接器:身份认证和权限相关
查询缓存：执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除）
分析器：检查SQL语句语法是否正确
优化器：按照 MySQL 认为最优的方案去执行(分析索引）
执行器：执行语句，然后从存储引擎返回数据
binlog：记录数据库操作语句的原始逻辑，用于主从备份，数据一致性
存储引擎：主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎。（现在使用的基本上都是InnoDB）

2. SQL语句执行过程

查询语句
增删改语句

（三）Mysql存储引擎（某大厂数据工程一面）

Mysql存储引擎采用的是插件式架构，是基于表的，不是基于数据库的
MySQL 5.5.5 之前，MyISAM 是默认存储引擎。之后，InnoDB 是 MySQL 的默认存储引擎（目前主流）。二者之间的区别为：

特性	MyISAM	InnoDB
锁粒度	表级别	行级别
事务支持	不支持	支持
隔离级别	不支持	支持 SQL 标准的四个隔离级别
外键支持	不支持	支持
MVCC 支持	不支持	支持
索引结构	B+Tree，具体实现不同	B+Tree，具体实现不同
崩溃恢复	不支持	支持
性能	较弱	更强大

（四）Mysql索引

1. 定义：索引是帮助mysql高效获取数据的数据结构
2. 索引分类

索引类型	特点
B+Tree 索引	默认索引类型，适用于大多数查询操作。支持范围查询、排序查询等。InnoDB 和 MyISAM 都使用 B+Tree 作为默认索引结构。
Hash 索引	基于哈希表实现，适用于等值查询，不支持范围查询。Memory 存储引擎支持此索引。
全文索引	适用于全文检索，用于查找文本数据中的关键词。MyISAM 和 InnoDB（从 MySQL 5.6 开始）支持全文索引。
空间索引	用于地理数据类型的索引（如 GIS 数据），支持复杂的地理空间查询。MyISAM 和 InnoDB（从 MySQL 5.7 开始）支持空间索引。
唯一索引	确保索引列的值唯一，可以有多个 NULL 值。适用于需要唯一性约束的列。
主键索引	一种特殊的唯一索引，不允许 NULL 值。每个表只能有一个主键索引。
复合索引	由多个列组成的索引，用于提高多列查询的性能。
前缀索引	对字符列的前缀部分进行索引，适用于需要对长字符串列的前几部分进行索引的情况。
聚簇索引	数据存储与索引顺序一致，每个表只能有一个聚簇索引。InnoDB 使用主键作为聚簇索引。如果没有定义主键，则选择一个唯一的非空索引。如果没有合适的唯一索引，InnoDB 会隐式创建一个行 ID。
非聚簇索引	索引结构和数据存储分离，索引中存储指向数据行的指针，可以有多个非聚簇索引。MyISAM 和 InnoDB 都支持非聚簇索引。

3. 最左匹配原则（某大厂数据工程一面）

使用联合索引查询时，索引的使用从最左边的列开始，必须依次匹配索引的列，不能跳过任何一列。
违反最左匹配原则导致索引失效情况：查询条件不包含组合索引的最左列；在查询条件中跳过了组合索引中的某一列；使用了不支持索引的函数或运算符

4. 索引失效

创建了组合索引，但查询条件未遵守最左匹配原则
在索引列上进行计算、函数、类型转换等操作
以 % 开头的 LIKE 查询比如 LIKE ‘%abc’;
查询条件中使用 OR，且 OR 的前后条件中有一个列没有索引，涉及的索引都不会被使用到
N 的取值范围较大时会导致索引失效，走全表扫描(NOT IN 和 IN 的失效场景相同);
发生隐式转换

（五） Mysql三大日志

1. binlog日志（server层）

逻辑日志，记录内容是语句的原始逻辑；主要用于MySQL 数据库的数据备份、数据同步，保证数据一致性
三种格式：statement、row（可能会产生数据不一致情况用，开销较大）、mixed

2. redo log（InnoDB 存储引擎独有）

物理日志，记录数据页的物理修改，用于数据库的崩溃恢复，保证数据库的持久性
每条 redo 记录由“表空间号+数据页号+偏移量+修改数据长度+具体修改的数据”组成
刷盘时机：事务提交；log buffer 空间不足；事务日志缓冲区满；Checkpoint后台刷新线程；正常关闭服务器

3. undo log

逻辑日志，记录内容与原始操作相反的 SQL 语句，用于事务回滚，保证数据库的原子性
注：redo log 在事务执行过程中可以不断写入，而 binlog 只有在提交事务时才写入

（六）Mysql事务

1. 事务特性（ACID）

原子性（Atomicity）：事务是最小的执行单位，不允许分割
一致性（Consistency）：执行事务前后，数据保持一致
隔离性（Isolation）：并发访问数据库时，各并发事务之间数据库是独立的
持久性（Durability）：一个事务被提交之后，它对数据库中数据的改变是持久的
$A+I+D\rightarrow D$

2. 并发事务导致的问题及解决方式

现象	说明	解决方法
脏读	一个事务读到了另一个事务尚未提交的修改数据。	设置事务隔离级别为 READ COMMITTED 或更高。
不可重复读	一个事务在前后两次读取同一数据时，发现数据被另一个事务修改了，导致两次读取结果不一致。	设置事务隔离级别为 REPEATABLE READ 或更高（对记录加锁）。
幻读	一个事务在前后两次读取时，发现插入或删除了一些新的数据行，导致读取结果集的行数不一致。	设置事务隔离级别为 SERIALIZABLE（对记录加锁和加GAP锁）。

3. 事务隔离级别
Mysql中有四种事务隔离级别：

READ-UNCOMMITTED(读取未提交) ：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复
READ-COMMITTED(读取已提交) ：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。（通过MVCC实现）
REPEATABLE-READ(可重复读) ：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。（通过MVCC实现）
SERIALIZABLE(可串行化) ：最高的隔离级别，完全服从 ACID 的隔离级别。该级别可以防止脏读、不可重复读以及幻读。（通过加锁实现）

4. 并发事务控制
（1）锁（悲观）

共享锁（S锁）：允许多个事务同时获取（锁兼容），通常用于读取操作
排他锁（X锁）：不允许多个事务同时获取，通常用于写操作
表级锁：针对非索引字段加的锁，对当前操作的整张表加锁
行级锁（记录锁、间隙锁、临键锁）：针对索引字段加的锁，只针对当前操作的行记录进行加锁
注：InnoDB 不光支持表级锁，还支持行级锁（默认）；表级锁和行级锁都有X和S两种锁
（2）MVCC（多版本并发控制）：对一份数据会存储多个版本，通过事务的可见性来保证事务能看到自己应该看到的版本
-通过创建数据的多个版本和使用快照读取来实现并发控制
实现手段：隐藏字段、read view（用于控制版本可见性）、undo log（用于记录某行数据的多个版本的数据）
（3）当前读和快照读
当前读：一致性锁定读，就是给行记录加 X 锁或 S 锁
快照读：一致性非锁定读，就是普通的 SELECT 语句

5. 分库分表
（1）垂直拆分

垂直分库：以表为依据，根据业务将不同表拆分到不同库中
垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中
（2）水平拆分
水平分库：将一个库的数据拆分到多个库中
水平分表：将一个表的数据拆分到多个表中(可以在同一个库内)

（七）Mysql优化

1. 优化方法

表设计优化，可参考Mysql高性能优化
索引优化：把握索引创建原则和失效注意事项（见上文）
主从复制，读写分离
分库分表

2. 定位慢查询
（1）方法

开源工具：arthas、prometheus、skywalking
mysql自带的慢日志
（2）sql执行很慢的分析方法：在sql查询语句前面加explain或desc，分析索引等的使用情况

3. 深度分页优化
（1）深度分页指查询偏移量过大的场景，会导致查询耗时多，响应慢。
（2）优化方案

当可以保证 ID 的连续性时，根据 ID 范围进行分页
先查询出 limit 第一个参数对应的主键值，再根据这个主键值再去过滤并 limit
延迟关联的优化思路，跟子查询的优化思路其实是一样的：都是把条件转移到主键索引树，减少回表的次数，使用内连接包含子查询。
建立覆盖索引，减少回表次数
注：回回表是指根据索引查询到的主键值再去访问主键索引，从而获取完整的数据记录。