mysql 架构 及 语句执行流程

转自
01 | 基础架构:一条SQL查询语句是如何执行的? - MySQL实战45讲
02 | 日志系统:一条SQL更新语句是如何执行的? - MySQL实战45讲

问大家一个问题。SELECT * FROM t WHERE id = 1 ; 以及 UPDATE t set c = 2 where id = 1 ; 这两个语句是怎么执行的。

假设表结构是

1
2
3
4
5
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

MySQL逻辑架构

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

第一层是连接处理、授权认证、安全等。这是大多数基于网络的客户端/服务器的工具或者服务都有的架构。

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

第三层是 存储引擎层,负责数据的存储和提取。每个存储引擎都有它的优势和劣势。服务器通过API与存储引擎层通信。其架构模式是插件式的,支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB,它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

(1) 连接器

第一步,你会先连接到这个数据库上,这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。连接命令一般是这么写的:

mysql -h $ip -P $port -u $user -p

连接命令中的 mysql 是客户端工具,用来跟服务端建立连接。在完成经典的 TCP 握手后,连接器就要开始认证你的身份,这个时候用的就是你输入的用户名和密码。

如果用户名或密码不对,你就会收到一个”Access denied for user”的错误,然后客户端程序结束执行。

如果用户名密码认证通过,连接器会到权限表里面查出你拥有的权限。之后,这个连接里面的权限判断逻辑,都将依赖于此时读到的权限。

这就意味着,一个用户成功建立连接后,即使你用管理员账号对这个用户的权限做了修改,也不会影响已经存在连接的权限。修改完成后,只有再新建的连接才会使用新的权限设置。

连接完成后,如果你没有后续的动作,这个连接就处于空闲状态,你可以在 show processlist 命令中看到它。其中的 Command 列显示为“Sleep”的这一行,就表示现在系统里面有一个空闲连接。

1
2
3
4
5
6
7
8
9
10
11
12
mysql> show processlist ;
+----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+
| 1 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 3 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 |
| 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 |
| 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 |
| 15 | root | localhost:49914 | NULL | Query | 0 | Init | show processlist | 0.000 |
+----+-------------+-----------------+------+---------+------+--------------------------+------------------+----------+
6 rows in set (0.00 sec)

客户端如果太长时间没动静,连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的,默认值是 8 小时。
在用的时候一般会在程序的配置文件里配置 validationQuery=SELECT 1 就是因为这个原因。

如果在连接被断开之后,客户端再次发送请求的话,就会收到一个错误提醒: Lost connection to MySQL server during query。这时候如果你要继续,就需要重连,然后再执行请求了。

数据库里面,长连接是指连接成功后,如果客户端持续有请求,则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接,下次查询再重新建立一个。

建立连接的过程通常是比较复杂的,所以我建议你在使用中要尽量减少建立连接的动作,也就是尽量使用长连接。

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

怎么解决这个问题呢?你可以考虑以下两种方案。

  1. 定期断开长连接。使用一段时间,或者程序里面判断执行过一个占用内存的大查询后,断开连接,之后要查询再重连。
  2. 如果你用的是 MySQL 5.7 或更新版本,可以在每次执行一个比较大的操作后,通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证,但是会将连接恢复到刚刚创建完时的状态。

(2) 查询缓存

连接建立完成后,你就可以执行 select 语句了。执行逻辑就会来到第二步:查询缓存。

MySQL 拿到一个查询请求后,会先到查询缓存看看,之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式,被直接缓存在内存中。key 是查询的语句,value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key,那么这个 value 就会被直接返回给客户端。

如果语句不在查询缓存中,就会继续后面的执行阶段。执行完成后,执行结果会被存入查询缓存中。你可以看到,如果查询命中缓存,MySQL 不需要执行后面的复杂操作,就可以直接返回结果,这个效率会很高。

但是大多数情况下我会建议你不要使用查询缓存,为什么呢?因为查询缓存往往弊大于利。

查询缓存的失效非常频繁,只要有对一个表的更新,这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来,还没使用呢,就被一个更新全清空了。对于更新压力大的数据库来说,查询缓存的命中率会非常低。除非你的业务就是有一张静态表,很长时间才会更新一次。比如,一个系统配置表,那这张表上的查询才适合使用查询缓存。

好在 MySQL 也提供了这种“按需使用”的方式。你可以将参数 query_cache_type 设置成 DEMAND,这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓存的语句,可以用 SQL_CACHE 显式指定,像下面这个语句一样:

select SQL_CACHE * from T where ID=10;

需要注意的是,MySQL 8.0 版本直接将查询缓存的整块功能删掉了,也就是说 8.0 开始彻底没有这个功能了。

(3) 分析器

如果没有命中查询缓存,就要开始真正执行语句了。首先,MySQL 需要知道你要做什么,因此需要对 SQL 语句做解析。

分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句,MySQL 需要识别出里面的字符串分别是什么,代表什么。

MySQL 从你输入的”select”这个关键字识别出来,这是一个查询语句。它也要把字符串“T”识别成“表名 T”,把字符串“ID”识别成“列 ID”。

做完了这些识别以后,就要做“语法分析”。根据词法分析的结果,语法分析器会根据语法规则,判断你输入的这个 SQL 语句是否满足 MySQL 语法。

如果你的语句不对,就会收到“You have an error in your SQL syntax”的错误提醒,比如下面这个语句 select 少打了开头的字母“s”。

1
2
3
mysql> elect * from t where ID=1;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'elect * from t where ID=1' at line 1

(4) 优化器

经过了分析器,MySQL 就知道你要做什么了。在开始执行之前,还要先经过优化器的处理。

优化器是在表里面有多个索引的时候,决定使用哪个索引;或者在一个语句有多表关联(join)的时候,决定各个表的连接顺序。比如你执行下面这样的语句,这个语句是执行两个表的 join:

mysql> select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

既可以先从表 t1 里面取出 c=10 的记录的 ID 值,再根据 ID 值关联到表 t2,再判断 t2 里面 d 的值是否等于 20。

也可以先从表 t2 里面取出 d=20 的记录的 ID 值,再根据 ID 值关联到 t1,再判断 t1 里面 c 的值是否等于 10。

这两种执行方法的逻辑结果是一样的,但是执行的效率会有不同,而优化器的作用就是决定选择使用哪一个方案。

(5) 执行器

MySQL 通过分析器知道了你要做什么,通过优化器知道了该怎么做,于是就进入了执行器阶段,开始执行语句。

开始执行的时候,要先判断一下你对这个表 T 有没有执行查询的权限,如果没有,就会返回没有权限的错误,如下所示 (在工程实现上,如果命中查询缓存,会在查询缓存返回结果的时候,做权限验证。查询也会在优化器之前调用 precheck 验证权限)。

1
2
3
mysql> select * from T where ID=10;

ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

如果有权限,就打开表继续执行。打开表的时候,执行器就会根据表的引擎定义,去使用这个引擎提供的接口。

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

至此,这个语句就执行完成了。

对于有索引的表,执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口,之后循环取“满足条件的下一行”这个接口,这些接口都是引擎中已经定义好的。

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

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


MySQL的架构这儿就完了。下面是两个比较重要的模块。

孔乙己

不知道你还记不记得《孔乙己》这篇文章,酒店掌柜有一个粉板,专门用来记录客人的赊账记录。如果赊账的人不多,那么他可以把顾客名和账目写在板上。但如果赊账的人多了,粉板总会有记不下的时候,这个时候掌柜一定还有一个专门记录赊账的账本。

如果有人要赊账或者还账的话,掌柜一般有两种做法:

  1. 一种做法是直接把账本翻出来,把这次赊的账加上去或者扣除掉;
  2. 另一种做法是先在粉板上记下这次的账,等打烊以后再把账本翻出来核算。

 在生意红火柜台很忙时,掌柜一定会选择后者,因为前者操作实在是太麻烦了。首先,你得找到这个人的赊账总额那条记录。你想想,密密麻麻几十页,掌柜要找到那个名字,可能还得带上老花镜慢慢找,找到之后再拿出算盘计算,最后再将结果写回到账本上。
 
 这整个过程想想都麻烦。相比之下,还是先在粉板上记一下方便。你想想,如果掌柜没有粉板的帮助,每次记账都得翻账本,效率是不是低得让人难以忍受?

同样,在 MySQL 里也有这个问题,如果每一次的更新操作都需要写进磁盘,然后磁盘也要找到对应的那条记录,然后再更新,整个过程 IO 成本、查找成本都很高。为了解决这个问题,MySQL 的设计者就用了类似酒店掌柜粉板的思路来提升更新效率。

 而粉板和账本配合的整个过程,其实就是 MySQL 里经常说到的 WAL 技术,WAL 的全称是 Write-Ahead Logging,它的关键点就是先写日志,再写磁盘,也就是先写粉板,等不忙的时候再写账本。


(6) 重要的日志模块:redo log (重做日志)

 具体来说,当有一条记录需要更新的时候,InnoDB 引擎就会先把记录写到 redo log(粉板)里面,并更新内存,这个时候更新就算完成了。同时,InnoDB 引擎会在适当的时候,将这个操作记录更新到磁盘里面,而这个更新往往是在系统比较空闲的时候做,这就像打烊以后掌柜做的事。

 如果今天赊账的不多,掌柜可以等打烊后再整理。但如果某天赊账的特别多,粉板写满了,又怎么办呢?这个时候掌柜只好放下手中的活儿,把粉板中的一部分赊账记录更新到账本中,然后把这些记录从粉板上擦掉,为记新账腾出空间。

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

redo log 重做日志

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

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

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

 要理解 crash-safe 这个概念,可以想想我们前面赊账记录的例子。只要赊账记录记在了粉板上或写在了账本上,之后即使掌柜忘记了,比如突然停业几天,恢复生意后依然可以通过账本和粉板上的数据明确赊账账目。

 redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候,表示每次事务的 redo log 都直接持久化到磁盘。这个参数建议你设置成 1,这样可以保证 MySQL 异常重启之后数据不丢失。


(7) 重要的日志模块:binlog (归档日志)

 MySQL 整体来看,其实就有两块:一块是 Server 层,它主要做的是 MySQL 功能层面的事情;还有一块是引擎层,负责存储相关的具体事宜。上面我们聊到的粉板 redo log 是 InnoDB 引擎特有的日志,而 Server 层也有自己的日志,称为 binlog(归档日志)。

 最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM,但是 MyISAM 没有 crash-safe 的能力,binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的,既然只依靠 binlog 是没有 crash-safe 能力的,所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

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

 Tips
 Redo log不是记录数据页“更新之后的状态”,而是记录这个页 “做了什么改动”。
 Binlog有两种模式,statement 格式的话是记sql语句, row格式会记录行的内容,记两条,更新前和更新后都有。
redo log 记录 做了什么改动(比如把某个字段从0改成了1)
binlog 记录 是怎么修改的(记录sql语句 或者 记录更新前后的行)

 有了对这两个日志的概念性理解,我们再来看执行器和 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)状态,更新完成。  

(7.1) 两阶段提交

 为什么必须有“两阶段提交”呢?这是为了让两份日志之间的逻辑一致。要说明这个问题,我们得从文章开头的那个问题说起:怎样让数据库恢复到半个月内任意一秒的状态?

 前面我们说过了,binlog 会记录所有的逻辑操作,并且是采用“追加写”的形式。如果你的 DBA 承诺说半个月内可以恢复,那么备份系统中一定会保存最近半个月的所有 binlog,同时系统会定期做整库备份。这里的“定期”取决于系统的重要性,可以是一天一备,也可以是一周一备。

 当需要恢复到指定的某一秒时,比如某天下午两点发现中午十二点有一次误删表,需要找回数据,那你可以这么做:
 1. 首先,找到最近的一次全量备份,如果你运气好,可能就是昨天晚上的一个备份,从这个备份恢复到临时库;
 2. 然后,从备份的时间点开始,将备份的 binlog 依次取出来,重放到中午误删表之前的那个时刻。
 这样你的临时库就跟误删之前的线上库一样了,然后你可以把表数据从临时库取出来,按需要恢复到线上库去。

 好了,说完了数据恢复过程,我们回来说说,为什么日志需要“两阶段提交”。这里不妨用反证法来进行解释。

 由于 redo log 和 binlog 是两个独立的逻辑,如果不用两阶段提交,要么就是先写完 redo log 再写 binlog,或者采用反过来的顺序。我们看看这两种方式会有什么问题。

 仍然用前面的 update 语句 update T set c=c+1 where ID=2; 来做例子。假设当前 ID=2 的行,字段 c 的值是 0,再假设执行 update 语句过程中在写完第一个日志后,第二个日志还没有写完期间发生了 crash,会出现什么情况呢?

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

 先写 binlog 后写 redo log。如果在 binlog 写完之后 crash,由于 redo log 还没写,崩溃恢复以后这个事务无效,所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以,在之后用 binlog 来恢复的时候就多了一个事务出来,恢复出来的这一行 c 的值就是 1,与原库的值不同。

 可以看到,如果不使用“两阶段提交”,那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

 不只是误操作后需要用这个过程来恢复数据。当你需要扩容的时候,也就是需要再多搭建一些备库来增加系统的读能力的时候,现在常见的做法也是用全量备份加上应用 binlog 来实现的,这个“不一致”就会导致你的线上出现主从数据库不一致的情况。

 简单说,redo log 和 binlog 都可以用于表示事务的提交状态,而两阶段提交就是让这两个状态保持逻辑上的一致。

 sync_binlog 这个参数设置成 1 的时候,表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1,这样可以保证 MySQL 异常重启之后 binlog 不丢失。

(8) 执行一条查询语句的流程

  1. 连接器 连接数据库。
  2. 查询缓存
  3. 分析器 词法解析 语法解析
  4. 优化器
  5. 执行器

(9) 更新一条语句的流程

  1. 连接器 连接数据库。
  2. 清空对应表缓存
  3. 分析器 词法解析 语法解析
  4. 优化器
  5. 执行器
  6. 更新 redo log(重做日志)和 binlog(归档日志)。

(10) 牛刀小试

(10.1) 面试题

  1. MySQL的框架有几个组件, 各是什么作用?
  2. Server层和存储引擎层各是什么作用?
  3. you have an error in your SQL syntax 这个保存是在词法分析里还是在语法分析里报错?
  4. 对于表的操作权限验证在哪里进行?
  5. 执行器的执行查询语句的流程是什么样的?

(10.2) 如果表 T 中没有字段 k,而你执行了这个语句 select * from T where k=1, 那肯定是会报“不存在这个列”的错误: “Unknown column ‘k’ in ‘where clause’”。你觉得这个错误是在我们上面提到的哪个阶段报出来的呢?

答案是 分析器

办法:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
安装完MySQL之后,使用Debug模式启动
mysqld --debug --console &后,
mysql> create database wxb;
Query OK, 1 row affected (0.01 sec)

mysql> use wxb;
Database changed
mysql> create table t(a int);
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t where k=1;
ERROR 1054 (42S22): Unknown column 'k' in 'where clause'

T@4: | | | | | | | | | error: error: 1054 message: 'Unknown column 'k' in 'where clause''

Complete optimizer trace:
答案就很清楚了

(10.3) 各个模块的功能

1,连接
连接管理模块,接收请求;连接进程和用户模块,通过,连接线程和客户端对接
2,查询
查询缓存 Query Cache
分析器,内建解析树,对其语法检查,先from,再on,再join,再where……;检查权限,生成新的解析树,语义检查(没有字段k在这里)等
优化器,将前面解析树转换成执行计划,并进行评估最优
执行器,获取锁,打开表,通过meta数据,获取数据
3,返回结果
返回给连接进程和用户模块,然后清理,重新等待新的请求。

(10.4) 动画描述各个模块的功能

连接器:门卫,想进请出示准入凭证(工牌、邀请证明一类)。“你好,你是普通员工,只能进入办公大厅,不能到高管区域”此为权限查询。
分析器:“您需要在公司里面找一张头发是黑色的桌子?桌子没有头发啊!臣妾做不到”
优化器:“要我在A B两个办公室找张三和李四啊?那我应该先去B办公室找李四,然后请李四帮我去A办公室找张三,因为B办公室比较近且李四知道张三具体工位在哪”
执行器:“好了,找人的计划方案定了,开始行动吧,走你!糟糕,刚门卫大哥说了,我没有权限进B办公室”

(10.5) 为什么对权限的检查不在优化器之前做?

有些时候,SQL语句要操作的表不只是SQL字面上那些。比如如果有个触发器,得在执行器阶段(过程中)才能确定。优化器阶段前是无能为力的

(10.6) 在什么场景下,一天一备会比一周一备更有优势呢?或者说,它影响了这个数据库系统的哪个指标?

(10.7) 假如要插入10万条数据,怎么写插入语句比较好

批量插入 insert into t (id, c) values (1,1), (2, 2)

(10.7) 假如要更新10万条数据,怎么写插入语句比较好

批量更新

(10.8) 详细的执行过程

1. 首先客户端通过tcp/ip发送一条sql语句到server层的SQL interface

2. SQL interface接到该请求后,先对该条语句进行解析,验证权限是否匹配

3. 验证通过以后,分析器会对该语句分析,是否语法有错误等

4. 接下来是优化器器生成相应的执行计划,选择最优的执行计划

5. 之后会是执行器根据执行计划执行这条语句。在这一步会去open table,如果该table上有MDL,则等待。如果没有,则加在该表上加短暂的MDL(S)。(如果opend_table太大,表明open_table_cache太小。需要不停的去打开frm文件)

6. 进入到引擎层,首先会去innodb_buffer_pool里的data dictionary(元数据信息)得到表信息

7. 通过元数据信息,去lock info里查出是否会有相关的锁信息,并把这条update语句需要的锁信息写入到lock info里(锁这里还有待补充)

8. 然后涉及到的老数据通过快照的方式存储到innodb_buffer_pool里的undo page里,并且记录undo log修改的redo (如果data page里有就直接载入到undo page里,如果没有,则需要去磁盘里取出相应page的数据,载入到undo page里)

9. 在innodb_buffer_pool的data page做update操作。并把操作的物理数据页修改记录到redo log buffer里。由于update这个事务会涉及到多个页面的修改,所以redo log buffer里会记录多条页面的修改信息。因为group commit的原因,这次事务所产生的redo log buffer可能会跟随其它事务一同flush并且sync到磁盘上

10. 同时修改的信息,会按照event的格式,记录到binlog_cache中。(这里注意binlog_cache_size是transaction级别的,不是session级别的参数,一旦commit之后,dump线程会从binlog_cache里把event主动发送给slave的I/O线程)

11. 之后把这条sql,需要在二级索引上做的修改,写入到change buffer page,等到下次有其他sql需要读取该二级索引时,再去与二级索引做merge (随机I/O变为顺序I/O,但是由于现在的磁盘都是SSD,所以对于寻址来说,随机I/O和顺序I/O差距不大)

12. 此时update语句已经完成,需要commit或者rollback。这里讨论commit的情况

13. commit操作,由于存储引擎层与server层之间采用的是内部XA(保证两个事务的一致性,这里主要保证redo log和binlog的原子性),所以提交分为prepare阶段与commit阶段

14. prepare阶段,将事务的xid写入,将binlog_cache里的进行flush以及sync操作(大事务的话这步非常耗时)

15. commit阶段,由于之前该事务产生的redo log已经sync到磁盘了。所以这步只是在redo log里标记commit

16. 当binlog和redo log都已经落盘以后,如果触发了刷新脏页的操作,先把该脏页复制到doublewrite buffer里,把doublewrite buffer里的刷新到共享表空间,然后才是通过page cleaner线程把脏页写入到磁盘中

(10.9) 写redo日志也是写io(我理解也是外部存储)。同样耗费性能。怎么能做到优化呢

1.写redo日志也是写io(我理解也是外部存储)。同样耗费性能。怎么能做到优化呢
2.数据库只有redo commit 之后才会真正提交到数据库吗

  1. Redolog是顺序写,并且可以组提交,还有别的一些优化,收益最大是是这两个因素;
    2.是这样,正常执行是要commit 才算完,但是崩溃恢复过程的话,可以接受“redolog prepare 并且binlog完整” 的情况

(10.10) redo log是为了快速响应SQL充当了粉板

 1. redo log本身也是文件,记录文件的过程其实也是写磁盘,那和文中提到的离线写磁盘操作有何区别?
 2.响应一次SQL我理解是要同时操作两个日志文件?也就是写磁盘两次?

  1. 写redo log是顺序写,不用去“找位置”,而更新数据需要找位置
  2. 其实是3次(redolog两次 binlog 1次)。不过在并发更新的时候会合并写

(10.11) binlog为什么说是逻辑日志呢?它里面有内容也会存储成物理文件,怎么说是逻辑而不是物理

这样理解

逻辑日志可以给别的数据库,别的引擎使用,已经大家都讲得通这个“逻辑”;

物理日志就只有“我”自己能用,别人没有共享我的“物理格式”

(10.12) redo log 和 bin log 是否重复

redo 是引擎提供的,binlog 是server 自带的,文中提到前者用在crash的恢复,后者用于库的恢复。两者是否在某种程度上是重复的?如果在都是追加写的情况下,是否两种日志都能用于 crash 与 库 的恢复呢?
Crash-safe是崩溃恢复,就是原地满血复活;binlog恢复是制造一个影分身(副本)出来。

(10.11)

  1. 如果把 innodb_flush_log_at_trx_commit 设置成1每次都写入到磁盘,那不就等于是掌柜的每次记账都记到账本上嘛,那还要小黑板干嘛呢?

  2. binlog是逻辑,redolog是物理,两者都能记录历史,如果发生异常情况binlog就可以恢复数据,为什么说只有redolog才能算是crash-safe了呢。

  3. Redolog是顺序写,数据文件是随机写。虽然都写盘,顺序写还是快很多的

(10.12)

1、首先数据库更新操作都是基于内存页,更新的时候不会直接更新磁盘,如果内存有存在就直接更新内存,如果内存没有存在就从磁盘读取到内存,在更新内存,并且写redo log,目的是为了更新效率更快,等空闲时间在将其redo log所做的改变更新到磁盘中,innodb_flush_log_at_trx_commit设置为1时,也可以防止服务出现异常重启,数据不会丢失

2、redo log两阶段提交,是为了保证redo log和binlog的一致性,如果redo log写入成功处于prepare阶段,写binlog失败,事务回滚,redo log会回滚到操作之前的状态

3、redo log也是写磁盘,写redo log是顺序写,update直接更新磁盘,需要找到数据,再对此数据进行更新(随机写)。

(10.13)

开启了半同步复制after_sync以后,假设一个事务在已经把binlog sync到磁盘了,在传输binlog到从库上时,主库挂了。如果这时发生了主从切换,从库是没有这个事务的.但是挂掉的主库重新启起来,由于binlog已经fsync到磁盘,虽然引擎层未commit,但是会根据binlog来恢复这个事务.这个时候就是主从不一致了,那么和after_commit那就没区别呀。请问这个事务是恢复还是回滚呢?如果是回滚,内部又是怎么判断的呢?

如果你设置来双MM,启动以后binlog还会传到新主库的。
但是如果你设置的是单向的,那就会不一致了…

References

[1] 01 | 基础架构:一条SQL查询语句是如何执行的? - MySQL实战45讲
[2] 02 | 日志系统:一条SQL更新语句是如何执行的? - MySQL实战45讲
[3] 《高性能MySQL》 O’REILLY