从MySQL基础架构中-解读查询和更新流程

1. 一条SQL查询语句是如何执行的？

以mysql.user 表为例，表里有一个 User 字段，执行下面这条查询：

select * from mysql.user where User='ansel.zhang' limit 1\G
*************************** 1. row ***************************
                  Host: localhost
                  User: ansel.zhang
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
  ......
       password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

按照 MySQL 的基本架构示意图，可以看出 SQL 语句在 MySQL 的各个模块中的执行过程。

MySQL 基础架构整体来说分两层：Server层（连接层，SQL层）和Engine层。

**连接层：**类似于linux的操作系统Linux Os

作用：
1.连接协议: tcp/ip和Unix套接字socket 要开启服务,才能连接；

2.加载授权表(mysql.user/mysal.db/xxx) :用户密码验证；

3.通过密码验证后生成连接线程。

**SQL层：**负责SQL语句的处理
过程：
1.语法检查,语义检查；

DDL: (data definition language)   数据定义语言，主要是用在定义或改变表的结构,数据类型,表之间的链接和约束等初始化工作上
DCL: (Data Control Language)      数据控制语言，主要是用在设置或更改数据库用户或角色权限的语句,包括(grant,deny,revoke等)语句
DML: (data manipulation language) 数据操纵语言，主要是用在SELECT, UPDATE, INSERT, DELETE对数据库的数据进行一些操作

2.对象存在性,权限检查(查找的对象在不在,有没有查看权限);
3.SQL语句解析预处理;

SQL语句解析预处理 -> 生成解析树,并统计执行代价 -> 统计信息(存放在mysql.innodbindex_stats/mysql.innodb_table-stats中)

4.优化器进行优化;

根据(每种执行计划的代价评估/SQL语句的执行顺序/查询方式的选择) -> 查看资源消耗情况(IO/CPU/内存) -> 选择出SQL线程认为代价最低的执行计划

5.按照执行计划执行SQL语句。

得出需要的数据的具体位置(哪个磁盘哪个位置) -> 告知给Engine层

**Engine：**类似于linux的文件系统。负责数据的存储和提取，支持 InnoDB、MyISAM、Memory 等多个存储引擎。MySQL 5.5.5 版本后默认存储存储引擎是 InnoDB。和磁盘做交互Engine层获取到的是16进制的数据，Engine层会把数据返还给SQL层, SQL层会生成表格的形式返还给用户,显示到界面上。

**总结：**客户端发送一条查询给Mysq服务器,服务器先检查查询缓存,如果命中了缓存,则立刻返回存储在缓存中的结果。否则,服务器端进行SQL解析、预处理,再由优化器生成对应的执行计划按照执行计划执行SQL语句告知Engine层,Engine层与磁盘做交互,获取到数据,交给SQL层,SQL层生成表格格式的数据,返还给用户。

2. 一条SQL更新语句是如何执行的？

重要的日志模块 ：redo log  （innodb 引擎层特有）
重要日志模块：binlog       （Mysql的Server层的，所有引擎都可用）
两阶段提交                （prepare和commit ）

上面1介绍了SQL查询语句基本的执行链路，这里更新语句同样适用。执行语句之前要先连接数据库，这是连接层的工作。

在一个表上有更新的时候，根这个表有关的查询缓存会失效，所以这条语句就会把T表上所有的缓存结果清空，接下来，分析器会通过词法分析，语法分析解析知道这是一条更新语句，优化器决定要使用xxxx索引。然后执行器负责具体执行，找到这一行然后更新。

与查询流程不一样的是更新流程还涉及到两个重要的日志模块，redo log(重做日志）和binlog(归档日志)，以及两阶段提交（prepare和commit ）。

2.1 redo log

酒店掌柜有一个粉板，专门用来记录客人的赊账记录，如果赊账的人不多，那么可以把顾客名和账目写在粉板上。但如果赊账的人多了，粉板总会有记不下的时候，这时候掌柜一定还有一个专门记录赊账的账本。
假如有人要赊账或者还账的时候，掌柜一般有两种做法：
1.直接把账本翻出来，把这个赊账的记录加上去或者删除掉；
2.先在粉本上记下这次的账，等打烊后再把账本翻出来核算。 在生意红火柜台很忙时，掌柜一定会选择后者，因为前者操作实在是太麻烦了。首先你得找到这个人赊账的总额那条记录。你想想看密密麻麻几十页，掌柜要找到那个名字，还得带上老花镜慢慢找，找到之后再拿出来盘算计算，然后再将结果写回到账本上，这样就有点麻烦了。
《孔乙己》

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

而粉板和账本配合的整个过程，就是 Mysql里常说WAL技术，WAL的全称是Write-Ahead Loging 它的关键是先写日志再写磁盘，也就是先写粉板等不忙的时候再写账本。

begin;
update grade set a=1 where A=1;
commit;

具体来说，当一条记录需要更新的时候，磁盘上查找到相应的数据A=1的数据页，生成事务ID，一起调到内存buffer_pool中进行缓存；内存中更新数据时（形成脏页），在log_buffer中生成redo日志(记录数据页发生的变化)，以及生成LSN；执行commit时，基于WAL机制，将redo日志存放在磁盘的ib_logfireN中,commit完成；ib_logfireN中记录了一条日志内容：page100数据页变化+LSN=2000 采用异步同步机制。

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

write pos是记录当前位置，一边写一边往后移，写到第三号文件的末尾就回到0号文件开头。

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

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

有了redo log，innoDB就可以保证即使数据库发生异常重启，之前提交的记录不会丢失，这个能力被称为 crash-sale。要理解 crash-safe这个概念，可以想想前面赊账的例子。只要赊账记录在粉本上或者写在了账本上，之后即使掌柜忘记了，比如突然停业几天，恢复生意后依然可以通过账本和粉板上的数据明确赊账账目。回到Mysql当中，IBP和ILB数据全丢。启动后，将磁盘的数据加载到内存IBP中和Ib_logfile0将磁盘的数据加载到内存ILB。ILB进行redo操作IBP，自动处理故障恢复。

总结：宕机时，已经提交的事务不会丢。 commit ----> redo log落盘

2.2 binlog

上面聊到的粉板redo log是InnoDB引擎特有的日志，而Server层也有自己的日志，称为binlog(归档日志)。
两种日志有以下三点不同

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

    1.先找到引擎取 A=1 这一行。A是主键，引擎直接用BTREE搜索到这一行。如果 A=1 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则需要先从磁盘读入到内存，然后返回；
    2.执行器拿到引擎给的行数据把这个值 a=1，比如原来是N，新的一行是 1 , 得到新的一行的数据，在调用引擎接口写入这行新数据;
    3.引擎将这行新数据更新到内存中，同时将这个更新记录操作记录到redolog里面。此时redo log处于prepare 状态。然后告知执行器执行完了，随时可以提交事务;
    4.执行器生成这个操作的binlog 并把binlog写入磁盘;
    5.执行器调用提交事务的接口，引擎把刚写入的redo log  状态commit更新完成。

2.3 两阶段提交

为什么必须有“两阶段提交”呢？这就是为了让两份日志间的逻辑一致。前面我们说过，binlog会记录所有的逻辑操作，采用的是“追加写的”形式，如果你承诺说半个月内可以恢复，那么备份系统中一定会保存最近半个月的所有binlog ,同时系统会定期做整库备份。这里“定期”取决于定期的重要性。

当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，详情请点击：clickhouse支持原生Binlog同步mysql

我们回来说说，为什么日志需要“两阶段提交”。这里不妨用反证法来进行解释。由于redo log和binlog是两个独立的逻辑，如果不用两阶段提交，要么就是先写完redo log再写 binlog，或者采用反过来的顺序。我们看看这两种方式会有什么问题。

仍然用前面的update语句来做例子。假设当前 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都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

详情信息请关注微信公众号