MYSQL语句执行大体流程

学习笔记是学习了 极客时间 - 《MySQL实战45讲》整理的笔记。

MYSQL简单架构

MySQL 可以分为 Server 层和存储引擎层两部分。

• Server 层： Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核
  心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。，不同的存储引擎共用一个Server 层，也就是从连接器到执行器的部分。
  • 连接器：连接器负责跟客户端建立连接、获取权限、维持和管理连接。
  • 查询缓存（MySQL 8.0版本以后直接取消了查询缓存的整块功能）：MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个value 就会被直接返回给客端。
    • 对于更新频繁的数据库表,不建议查询缓存。查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低。
    • 按需配置：
      • 关闭缓存：将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。
      • 设置缓存：用 SQL_CACHE 显式指定 select SQL_CACHE * from T where ID=10；
  • 分析器：
    • 词法分析
    • 语法分析
  • 优化器：在表里面有多个索引的时候，决定使用哪个索引
  • 执行器：
    • 判断权限
    • 调用引擎接口
• 存储引擎层：存储引擎层负责数据的存储和提取。

MYSQL中 SQL语句的执行流程

查询语句执行流程如图：

msql查询.png

更新语句执行流程（InnoDB引擎）

InnoDB架构

InnoDB架构.png

在更新语句执行流程中，大部分会使用到内存池。

执行SQL语句

update T set c=c+1 where ID=2;

update.png

可以看到 更新语句执行的时候，涉及到了两个重要的日志模块。

WAL 技术

为什么会涉及这两个模块，首先我们需要了解一下 MYSQL WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。

• redo log（重做日志）: InnoDB 引擎特有的日志并且是固定大小,循环写入机制。比如4个日志文件如下图所示。

relog1.png

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。write pos checkpoint 之间的是用来记录新的操作。如果 write pos 追上 checkpoint，如要将数据写入磁盘后在进行下一步操作。

• 优点：保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

• redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；如下图所示：

• 参数配置：innodb_flush_log_at_trx_commit 这个参数设置成1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。

• binlog（归档日志）：是 MySQL 的 Server 层实现的，所有引擎都可以使用。只能用于归档(备份)。其主要记录的是SQL语句原始的逻辑。如下图所示：

  • 参数配置：sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。
  • Binlog有两种模式，statement 格式的话是记sql语句， row格式会记录行的内容，记两条，更
    新前和更新后都有。

对比

	redo log	binlog
实现	InnoDB 引擎特有	Server 层所有引擎都可以使用。
类别	物理日志，记录的是“在某个数据页上做了什么修改”	binlog 是逻辑日志，记录的是这个语句的原始逻辑。比如“给 ID=2 这一行的 c 字段加 1 ”。
写入方式	循环	追加，文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

两阶段提交（ prepare 和commit ）

在之前的逻辑图中我们可以看到，进行了两阶段提交操作，为了让两份日志之间的逻辑一致。通过一种特殊的xid，可以在两阶段提交的不同时刻发生异常重启时，将redo和binlog联系起来。如下：

 [Note] Found 1 prepared transaction(s) in InnoDB
 [Note] commit xid 'MySQLXid\1\0\0\0\0\0\0\0\6\0\0\0\0\0\0\0'

非两阶段提交出现宕机问题 以update T set c=c+1 where ID=2;为例

1. 先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。
2. 先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

关于 binlog 和 redo log更为深入的了解 推荐阅读：一文带你看懂binlog和redo log