原文链接：https://time.geekbang.org/column/article/68319

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

逻辑架构

MySQL 分为 Server 层和存储引擎层

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

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

create table `user` (
  `id` bigint  unsigned not null auto_increment primary key
) engine = innodb comment = '用户';  -- 指定引擎

连接器

负责跟客户端建立连接、获取权限、维持和管理连接

show processlist 可以查看当前连接的状态

Sleep 代表当前连接出于空闲状态

客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。

可能的坑

但是全部使用长连接后，你可能会发现，有些时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。

查询缓存

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空

由于上述鸡肋问题，MySQL 8.0 删除了查询缓存功能

这块儿应该由 ORM 或者 Redis 根据业务情况来做细粒度的缓存了

分析器

SQL 语句的分析、检查

优化器

在表里面有多个索引的时候，决定使用哪个索引
有多表关联（join）的时候，决定各个表的连接顺序

执行器

负责调用引擎的具体接口

是否有索引的区别

select * from T where ID=10;

无索引：

调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；
调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

有索引：

第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口

上面的无索引情况应该就是全表扫描

数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的

执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。

总结

MySQL 逻辑架构分为 Server 层和引擎层（插件式的，目前默认是 InnoDB 引擎）
Server 层分为连接器、查询缓存（8.0已经删除）、分析器、优化器、执行器

原文链接：https://time.geekbang.org/column/article/68633

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

redo log（重做日志）

一条常见的更新语句：

update user set num=num+1 where id=2;

问题背景：

如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。

解决方案：WAL

MySQL 里经常说到的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘

具体流程：

当有记录需要更新的时，InnoDB 会先把记录写到 redo log 里面，并更新内存，这个时候更新就算完成了同时，InnoDB 会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做 InnoDB 的 redo log 是固定大小的，从头开始写，写到末尾就又回到开头循环写

write pos 是当前记录的位置，checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件

crash-safe：

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

binlog （归档日志）

redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）

不同点：

redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。 redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ” redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志 redo log 是用来 crash-safe 的，而 binlog 是用于数据恢复，备份等

两阶段提交：

数据更新 -> 写入 redo log 状态为 prepare -> 成功写入 binlog 后 -> 引擎将刚刚的 redo log 状态修改为 commit

（感觉有点像 TCP 三次握手，用来保证可靠性）

参数设置

innodb_flush_log_at_trx_commit 设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘

sync_binlog 设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘

这样可以保证 MySQL 异常时数据不丢失

（MySQL 8.0 这两个参数默认都为 1）

yahuian / blog

《MySQL 实战 45 讲》学习笔记 #24