深入理解MySQL篇一

  • MySQL的逻辑架构
  • SQL语句完整执行流程
  • 1.查询语句的完整执行流程
  • 2.更新语句的完整执行流程
  • 3.为什么使用两阶段提交


MySQL的逻辑架构

如图为MySQL的逻辑架构图：


mysql底层基于什么实现的 mysql底层逻辑_mysql

第一层客户端连接：该层服务并不是MySQL独有的，大多数基于网络的客户端/服务器的工具或是服务都有类似的架构。比如：连接处理，授权验证，安全等。

第二层为MySQL的核心服务层：MySQL的核心功能都在这一层，例如：查询解析、分析、优化、缓存以及所有的内置函数。另外所有的跨存储引擎的功能：存储过程。触发器、视图等。

第三层为MySQL的存储引擎层：什么是存储引擎？存储引擎负责MySQL中的数据的存储和提取。MySQL可以支持多种存储引擎。如：InnoDB、MyISAM、Memory等。在MySQL5.5.5版本后默认使用的就是InnoDB。但是存储引擎之间不会相互通信。而是只是简单的相应上层服务器的请求。后续将详细介绍存储引擎。

第四层：就是计算机存储，磁盘存储等

为什么MySQL架构设计成这样？

答：当我们使用MySQL进行数据的存储和修改等操作的时候，在SQL语句的执行就对应着MySQL底层架构的实现。下面就通过分析一条SQL语句的完整执行流程来详细了解MySQL结构的各个部分。

SQL语句完整执行流程

1.查询语句的完整执行流程

下面为最简单的一条查询语句：

select * from User where ID=1;

正常的，我们输入一条这样的语句返回的就是一个查询的结果。那它的内部是如何做到的呢？

SQL语句的执行流程：

  1. 连接器：首先会连接到这个数据库上，使用的就是连接器。连接器负责根客户端建立连接，获取权限，维持和管理连接。就是通常我们连接数据库命令：
mysql -u{用户名} -p{密码}

mysql:是客户端工具，建立服务端的连接，此时连接器就需要开始验证身份，就需要我们输出用户名和密码

当我们的用户密码通过后，连接器就会到权限表中去查询我们拥有的权限，是root用户还是普通用户。连接成功后，在不进行后续操作的情况下，连接处于空闲状态。

查看连接状态：

show processlist

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

这里就会有两个概念：

长连接：是指连接成功后，如果客户端持续有请求，则一直使用同一个连接

短连接：是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个

注意：实际使用数据库的时候，尽量使用长连接！

使用长连接会出现的问题：

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

怎么解决这个问题呢？

答：定期断开长连接；或是在每次执行较大的操作后，执行【mysql_reset_connection】来重新初始化连接资源。

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

如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。

为什么使用缓存？

答：如果查询命中缓存，MySQL不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。

为什么不推荐使用缓存？

答：查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。所以在MySQL8.0版本直接将查询缓存功能删除了。

  1. 分析器：如果没有命中查询缓存，就开始真正执行语句了。首先，MySQL需要知道你要做什么，因此需要对SQL语句做解析。这时分析器就会对你的SQL语句进行解析：词法+语法分析。就如字面上的意思，要做什什么？语句是否正确？
  2. 优化器：经过了分析器，MySQL就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join） 的时候，决定各个表的连接顺序。优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。
  3. 执行器：MySQL通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶 段，开始执行语句。
  • 开始执行的时候，要先判断一下你对这个表T有没有执行查询的权限，如果没有，就会返回没有 权限的错误。
  • 如果有权限，就打开表继续执行。打开表的时候，执行器会根据表的引擎定义，去使用这个引擎提供的接口。
比如我们这个例子中的表T中，ID字段没有索引，那么执行器的执行流程是这样的：

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

至此，这个查询语句就执行完成了。通过上面一条语句的执行流程，就可以看到在mysql架构中各个部分的关键作用。

2.更新语句的完整执行流程

有人会想：我都知道SQL语句的执行流程，还介绍更新的干啥？

在MySQL中，更新语句的执行流程是不同于查询的。因为更新操作涉及到对数据库中的数据进行更改的操作。

那么一条更新语句的执行流程又是怎样的呢？

例如：我们进行下列语句的执行：

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

对比查询流程的执行过程。在一个表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句就会 把表T上所有缓存结果都清空。这也就是我们一般不建议使用查询缓存的原因。接下来，分析器会通过词法和语法解析知道这是一条更新语句。优化器决定要使用ID这个索引。 然后，执行器负责具体执行，找到这一行，然后更新

但是，与查询流程不一样的是，更新流程还涉及两个重要的日志模块：redo log(重做日志)和 binlog(归档日志）。

日志模块：redo log

通常的提到日志模块，我们就会想到：那不就是查看系统执行过程及查错的地方的么？这里不是！在解释redo log作用之前，我们先举例一个场景：

  • 商店买东西的过程：
在商店，有的人买东西是直接付钱，而有的人买东西就会直接记账以后再付。

对于直接付钱的人没有什么好说的，收钱完事！但是对于记账的人就会出现这样的问题：

    - 一种做法是直接把账本翻出来，把这次赊的账加上去或者扣除掉；
    
    - 另一种做法是先在纸上记下这次的账，等晚上关门以后再把账本翻出来核算。

显然第一种做法是非常费时间的！在计算机上就表现的就是大量的IO操作。

在MySQL里也有这个问题，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到 对应的那条记录，然后再更新，整个过程IO成本、查找成本都很高。为了解决这个问题。MySQL的设计就采用第二种做法。

这种记录更新的方式就是MySQL里经常说到的WAL技术，WAL的全称是WriteAhead Logging，它的关键点就是先写日志，再写磁盘，也就是先写一张白纸上，等不忙的时候再写账本。

redo log作用：当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log（白纸）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB引擎会在适当的时候，将这个操作 记录更新到磁盘里面。

问题：如果白纸上记录满了怎么办？

答：InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是 1GB，那么这块“白纸”总共就可以记录4GB的操作。从头开始写，写到末尾就又回到开头循环写。而不是我们想的重新创建一张“白纸”。

日志模块：bin log

上面我们介绍了redo log的作用。redo log 是在存储引擎层的日志系统模块。负责存储的具体事宜。bin log则是服务层的日志系统。也称为归档日志。

为什么需要两个日志模块？

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

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

两个日志模块的异同：这两种日志有以下三点不同。

  • redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。
  • redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。
  • 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）状态，更 新完成。

执行流程图如下：

mysql底层基于什么实现的 mysql底层逻辑_数据库_02

通过流程图，我们可以了解到，redo log使用的是两阶段提交的方式：

3.为什么使用两阶段提交

答：这是为了让两份日志之间的逻辑一致！

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

假设当前ID=2的行，字段c的值是0，再假设执行update语 句过程中在写完第一个日志后，第二个日志还没有写完期间发生了crash，会出现什么情况呢？

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

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

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

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

行c的值是0。但是binlog里面已经记录了“把c从0改成1”这个日 志。所以，在之后用binlog来恢复的时候就多了一个事务出来，恢复出来的这一行c的值就是 1，与原库的值不同。

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

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

本文参考：
《高性能MySQL》
《MySQL实战45讲(林晓斌)》