# MYSQL实战45讲

## 01 | 基础架构:一条SQL查询语句是如何执行的?

![MySQL的逻辑架构图](https://937995404-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MMTM8NYe0uekudlCMKb%2F-MP-9ug8WDS2nx0DumsH%2F-MP-AI4E0_PBNG8O-CgN%2Fimage.png?alt=media\&token=ce66cfe7-b1e4-494b-9be2-c9c306f850cd)

大体来说,MySQL 可以分为 Server 层和存储引擎层两部分。

Server 层包括连接器、查询缓存、分析器、优化器、执行器等,涵盖 MySQL 的大多数核 心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引 擎的功能都在这一层实现,比如存储过程、触发器、视图等。

而存储引擎层负责数据的存储和提取。其架构模式是插件式的,支持 InnoDB、 MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB,它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

## 0**2** | 日志系统:一条SQL更新语句是如何执行的?

### 物理日志:redo log

InnoDB 的 redo log 是固定大小的,比如可以配置为一组 4 个文件,每个文 件的大小是 1GB,那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写,写到末 尾就又回到开头循环写,如下面这个图所示。

![](https://937995404-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MMTM8NYe0uekudlCMKb%2F-MP-9ug8WDS2nx0DumsH%2F-MP-FEYm8ZEQDRLBNlKm%2Fimage.png?alt=media\&token=72ada0e3-e915-43d5-9d1f-d45ef24d3699)

write pos 是当前记录的位置,一边写一边后移,写到第 3 号文件末尾后就回到 0 号文件 开头。checkpoint 是当前要擦除的位置,也是往后推移并且循环的,擦除记录前要把记录更新到数据文件。

write pos 和 checkpoint 之间的是`redo log`上还空着的部分,可以用来记录新的操作。如果 write pos 追上 checkpoint,表示`redo log`满了,这时候不能再执行新的更新,得停下来先擦掉一些记录,把 checkpoint 推进一下。

有了 redo log,InnoDB 就可以保证即使数据库发生异常重启,之前提交的记录都不会丢 失,这个能力称为crash-safe。

**通俗点说就是：执行更新时先写日志，再写磁盘**

### 逻辑日志:binlog

#### **这两种日志有以下三点不同：**

1. redo log 是 InnoDB 引擎特有的;binlog 是 MySQL 的 Server 层实现的,所有引擎

   都可以使用。
2. redo log 是物理日志,记录的是“在某个数据页上做了什么修改”;binlog 是逻辑日

   志,记录的是这个语句的原始逻辑,比如“给 ID=2 这一行的 c 字段加 1 ”。
3. redo log 是循环写的,空间固定会用完;binlog 是可以追加写入的。“追加写”是指

   binlog 文件写到一定大小后会切换到下一个,并不会覆盖以前的日志。

#### 执行器和 InnoDB 引擎在执行这个简单的 update 语句时的内部流程：

1. 执行器先找引擎取 ID=2 这一行。ID 是主键,引擎直接用树搜索找到这一行。如果

   ID=2 这一行所在的数据页本来就在内存中,就直接返回给执行器;否则,需要先从磁

   盘读入内存,然后再返回。
2. 执行器拿到引擎给的行数据,把这个值加上 1,比如原来是 N,现在就是 N+1,得到

   新的一行数据,再调用引擎接口写入这行新数据。
3. 引擎将这行新数据更新到内存中,同时将这个更新操作记录到 redo log 里面,此时

   redo log 处于 prepare 状态。然后告知执行器执行完成了,随时可以提交事务。
4. 执行器生成这个操作的 binlog,并把 binlog 写入磁盘。
5. 执行器调用引擎的提交事务接口,引擎把刚刚写入的 redo log 改成提交(commit)状

   态,更新完成。

![](https://937995404-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MMTM8NYe0uekudlCMKb%2F-MP-9ug8WDS2nx0DumsH%2F-MP-GhXt9CVI3iY_HPdq%2Fimage.png?alt=media\&token=0216ba0b-6129-4663-9b83-14d959fefb47)

**小结：redo是物理的,binlog是逻辑的;现在由于redo是属于InnoDB引擎,所以必须要有 binlog,因为可以使用别的引擎 保证数据库的一致性,必须要保证2份日志一致,使用的2阶段式提交;其实感觉像事务, 不是成功就是失败,不能让中间环节出现,也就是一个成功,一个失败 ...**

## 03 | 事务隔离:为什么你改了我还看不见?

1. 事务的特性:原子性、一致性、隔离性、持久性 
2. 多事务同时执行的时候,可能会出现的问题:脏读、不可重复读、幻读 
3. 事务隔离级别:读未提交、读提交、可重复读、串行化 
4. **不同事务隔离级别的区别:**

> 读未提交:别人改数据的事务尚未提交,我在我的事务中也能读到。
>
> 读已提交:别人改数据的事务已经提交,我在我的事务中才能读到。
>
> 可重复读:别人改数据的事务已经提交,我在我的事务中也不去读。
>
> 串行:我的事务尚未提交,别人就别想改数据。 

**这4种隔离级别,并行性能依次降低,安全性依次提高。**

## 04 | 深入浅出索引(上)

**索引的出现其实就是为了提高数据查询的效率,就像书的目录一样。**

 主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小。

一般情况下我会建议创建一个自增主键,这样非主键索引 占用的空间最小。

> 每一张表其实就是好几颗B+树,树结点的key值就是某一行的主 键,value是该行的其他数据。新建索引就是新增一个B+树,查询不走索引就是遍历主 B+树。