MySQL八股整理.md

# MySQL八股整理 > [【计算机基础】数据库MySQL面试八股总结_哔哩哔哩_bilibili](https://www.bilibili.com/video/BV1uHkXBMExe/?spm_id_from=333.337.search-card.all.click&vd_source=8c5774f0412cfb872126f5a44585db4f)  > 上图来自于视频：[【计算机基础】数据库MySQL面试八股总结_哔哩哔哩_bilibili](https://www.bilibili.com/video/BV1uHkXBMExe/?spm_id_from=333.337.search-card.all.click&vd_source=8c5774f0412cfb872126f5a44585db4f) ## MySQL 组成结构 MySQL 主要由两部分组成：server 层、存储引擎层 ``` 客户端 │ ▼ ┌──────────────────────────────┐ │ Server 层 │ │ │ │ 连接管理 │ │ SQL 解析器 │ │ SQL 优化器 │ │ SQL 执行器 │ │ 权限校验等 │ └──────────────┬───────────────┘ │ ▼ ┌──────────────────────────────┐ │ 存储引擎层 │ │ │ │ InnoDB │ │ MyISAM │ │ Memory │ │ ... │ └──────────────┬───────────────┘ │ ▼ 磁盘数据 ``` ## 从后端一段SQL代码到MySQL的执行流程 > 客户端发送SQL查询语句到MySQL服务器，MySQL处理请求，并与客户端建立连接，获取权限，后续MySQL的解析器对SQL语句进行解析，检查是否符合SQL语法规则，通过后优化器尝试找出最高效的方式来执行查询，执行器调用存储引擎的API进行数据的读写，并返回给客户端完成本次查询 第一步，**客户端**发送 SQL 查询语句到 MySQL 服务器。 第二步，MySQL 服务器的连接器开始处理这个请求，跟客户端建立连接、获取权限、管理连接。 第三步，**解析器**开始对 SQL 语句进行解析，检查语句是否符合 SQL 语法规则，确保引用的数据库、表和列都存在，并处理 SQL 语句中的名称解析和权限验证。 第四步，**优化器**负责确定 SQL 语句的执行计划，这包括选择使用哪些索引，以及决定表之间的连接顺序等。优化器会尝试找出最高效的方式来执行查询。 第五步，**执行器**会调用**存储引擎**的 API 来进行数据的读写。 第六步，MySQL 的存储引擎是插件式的，不同的存储引擎在细节上面有很大不同。例如，InnoDB 是支持事务的，而 MyISAM 是不支持的。之后，会将执行结果返回给客户端 第七步，客户端接收到查询结果，完成这次查询请求。 ## 常用命令 创建、删除，CRUD ## MySQL支持哪些存储引擎？各个引擎之间有什么区别？  常见的存储引擎主要有三个：memory、innodb、myisam，主要区别是： - memory：基于内存 - innodb：支持事务、行锁、外键，其中与myisam的区别是，innodb存储引擎存放数据只有一个idb文件，该文件包含了索引与数据，而myisam则分为两个：索引文件与数据文件，查询时需先查询索引，然后根据索引再去找到数据 - myisam：不支持事务、行锁、外键 | 文件 | 含义 | 存什么 | | ------ | ------- | ------------ | | `.MYD` | MYData | 实际的行数据 | | `.MYI` | MYIndex | 索引数据 | ## MySQL 日志文件有哪些？ > Error Log 看 MySQL 自己出了什么问题；Binlog 看数据发生了什么变化；General Log 看客户端执行了什么 SQL；Slow Log 看哪些 SQL 执行得太慢 | 日志类型 | 英文名称 | 主要记录内容 | 默认状态 | 主要用途 | 补充说明 | | ---------------- | ------------------- | ------------------------------------------------------------ | ------------------ | --------------------------------------- | ------------------------------------------------------------ | | **错误日志** | Error Log | 记录 MySQL 在**启动、运行、停止**过程中产生的错误、警告以及部分重要运行信息 | 默认开启 | 故障排查、定位 MySQL 启动失败或运行异常 | 例如数据库启动失败、存储引擎异常、权限问题等。**错误日志本身并不是用于恢复业务数据的日志** | | **二进制日志** | Binary Log / Binlog | 记录数据库中会导致**数据或结构发生变化的事件**，例如 `CREATE`、`INSERT`、`UPDATE`、`DELETE` 等；通常不记录普通 `SELECT` 查询 | MySQL 8.0 默认开启 | **数据恢复、主从复制、数据同步** | Binlog 主要有 `STATEMENT`、`ROW`、`MIXED` 三种格式。`STATEMENT` 记录执行的 SQL；`ROW` 记录行数据发生的变化；`MIXED` 根据情况在两者之间选择 | | **通用查询日志** | General Query Log | 记录客户端连接以及 MySQL 接收到的各种 SQL 请求，包括 `SELECT`、`INSERT`、`UPDATE`、`DELETE` 等 | 默认关闭 | 排查 SQL 请求、分析客户端执行了哪些操作 | 记录范围非常广，写入频繁，**性能开销较大**，通常只在排查问题时临时开启 | | **慢查询日志** | Slow Query Log | 记录执行时间超过指定阈值的 SQL | 默认关闭 | **定位慢 SQL、进行数据库性能优化** | 通过 `long_query_time` 设置慢查询时间阈值，例如设置为 `2`，表示执行时间超过 2 秒的 SQL 可被记录 | ## innodb 存储引擎中 redolog 与 undolog 的区别 - undolog：记录旧数据，即更新前的数据，用于回滚和MVCC多版本并发控制 - redolog：记录修改，用于崩溃后恢复，由于innodb对于数据更改，缓冲区与实际存储数据的磁盘处理顺序不同，首先将位于内存的缓冲区数据更新，并写入redolog，若数据写入实际存储的磁盘时发生崩溃，此时重启后可以通过redolog重新恢复已提交的数据 ## redo log 与 binlog 有什么区别 最核心的区别是： ``` redo log ↓ InnoDB 层 ↓ 保证存储引擎自身崩溃恢复 binlog ↓ MySQL Server 层 ↓ 记录数据库发生的逻辑变化 ``` redolog是MySQL存储引擎层innodb的日志记录文件，而binlog是MySQL server层的日志记录文件，redolog与innodb绑定，记录物理级别的修改操作，如：具体的索引、页、以及数据等内容，而binlog主要是逻辑层面的修改操作，有三种记录方式：statement、row、mixed（两者混合），其中statement记录SQL语句，row记录更改前后的数据 ## MySQL 两阶段提交 背景：一条SQL语句进行更新操作设计了两阶段提交 1. 为什么需要两阶段提交？ 2. 两阶段提交的流程是什么？ 两阶段提交是为了保证 redolog 和 binlog 的一致性，由于 redolog 是 innodb 存储引擎的日志记录，而 binlog 是MySQL server层的日志记录，主要用于主从复制和数据恢复。如果两者独立提交，宕机时可能出现 redolog 有记录但 binlog 没有，或者 binlog 有记录但 redolog 没有，从而导致主库恢复结果与 binlog 不一致，因此 MySQL 将 redolog 的提交分为 prepare 与 commit 两个阶段，中间写入 binlog，以保证两套日志对同一个事务的提交状态保持一致 两阶段提交的流程是， innodb 先写 redolog，并将其标记为 prepare 状态，然后server 层写人binlog，binlog 写入成功后，再将 redolog 标记为 commit，事务完成；若后续发生故障可能进行回滚或者判断事务已经成功 如果在 prepare 后，binlog 前宕机，此时binlog没有成功写入，事务会回滚；如果 binlog 已经写入成功，但 redolog 仍处于 prepare 状态，重启后会根据 binlog 判断事务已经成功，并完成提交 > 其中真正的“两阶段”主要指 redo log 的： > > ``` > prepare > ↓ > commit > ``` > > binlog 写入发生在这两个阶段之间 ## MySQL 索引 > [【编程不良人】这一次彻底搞定MySQL索引、从此不在恐惧面试_哔哩哔哩_bilibili](https://www.bilibili.com/video/BV19y4y127h4/?spm_id_from=333.788.videopod.sections&vd_source=8c5774f0412cfb872126f5a44585db4f) 索引分类： ``` 按功能分类 ├── 主键索引 ├── 唯一索引 ├── 普通索引 └── 全文索引 按包含字段数量分类 ├── 单列索引 └── 复合索引（联合索引） ``` ==不同索引加锁情况不同== | 索引类型 | 说明 | 特点 / 注意事项 | 示例 | | ---------------------- | ------------------------------------------------------------ | ------------------------------------------------------------ | -------------------------------------- | | 主键索引 `PRIMARY KEY` | 创建表时设置主键，MySQL 会自动为主键创建索引 | 数据必须**唯一且不能为 NULL**；一个表只能有一个主键 | `PRIMARY KEY (id)` | | 唯一索引 `UNIQUE` | 用于保证索引列的值具有唯一性 | 不允许出现重复的非 NULL 值；MySQL 中通常**允许多个 NULL** | `UNIQUE INDEX idx_email (email)` | | 普通索引 `INDEX` | 最基本的索引类型，不要求数据唯一 | 主要用于