MYSQL实战45讲

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

MySQL的逻辑架构图

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

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

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

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

物理日志:redo log

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

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)状

   态,更新完成。

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

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

1. 事务的特性:原子性、一致性、隔离性、持久性

2. 多事务同时执行的时候,可能会出现的问题:脏读、不可重复读、幻读

3. 事务隔离级别:读未提交、读提交、可重复读、串行化

4. 不同事务隔离级别的区别:

读未提交:别人改数据的事务尚未提交,我在我的事务中也能读到。

读已提交:别人改数据的事务已经提交,我在我的事务中才能读到。

可重复读:别人改数据的事务已经提交,我在我的事务中也不去读。

串行:我的事务尚未提交,别人就别想改数据。

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

04 | 深入浅出索引(上)

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

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

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

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