Mysql架构以及SQL语句的执行流程

在平时的开发过程中，我们使用的数据库大多数看到的只是数据库的一个整体，一般都是输入一条语句，返回一个结果集，但是如果我们不知道其内部执行的细节，当我们在碰到一些异常情况的时候，可能不知道如何下手，更别谈对sql语句的优化了，在这篇文章中，我来简单的介绍下mysql设计的架构以及当我们输入一条SQL语句的时候在mysql内部是如何执行的。

我们来举个例子，比如有如下一个简单的查询语句

select * from T where id = 1;

我们如果直接执行他，只会看到返回的结果集，可是其内部究竟经历了什么呢？

下图是mysql基本的架构的示意图，mysql在执行一条sql语句的时候经过的大概也是下图所示的流程。

从上图我们可以看出，mysql的架构可以简单的分为server层和存储引擎两部分，在server层中，我们可以看到连接器，查询缓存，分析器，优化器，执行器等等结构。在这些结构中，提供了mysql的大多数的核心的功能，包括所有的内置函数的实现，以及所有跨存储引擎层的功能的实现。

而存储引擎层提供的服务是对数据的存取，其架构模式是插件式的，我们熟知的存储引擎比如innoDB，MyISAM，Memory等等。而我们最常用的存储引擎肯定就是InnoDB了，其对事物的支持以及对行锁的支持是对现在所有高并发高一致性业务重要保障。在MySQL 5.5之后，InnoDB也成为了MySQL的默认的存储引擎。也就是说，当我们使用create table建表的时候，无需指定engine = InnoDB，其默认使用就是InnoDB，如果我们想要使用其他存储引擎则需要显式指定。

在图中我们可以看到，所有的存储引擎共用一个server层，接下来我来说一下一条语句整个的执行流程，同时讲解下每个组件的作用。

连接器

对数据库进行操作的第一步就是与数据库建立一个TCP连接，这个工作就是数据库连接器所提供的，连接器负责跟客户端建立连接、获取权限、维持和管理连接，我们对数据库连接时的操作是这样的：

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

接下来mysql会提示我们输入密码，我们也可以同时通过-p参数一起输入密码，但是并不建议这样做，可能会导致密码的泄露。

当我们输入密码过后，会收到mysql server的提示

• 如果我们密码输入错误，我们会收到Access denied for user的提示。

• 如果密码输入正确，连接器会到权限表中查看你拥有的权限，并且之后该链接里面所有的权限判断都依赖此时读到的权限。

  所以我们如果修改一个账号的权限后，需要重新登录才会生效

  当一个客户端在一段时间没有操作后，此连接会自动断开，可以通过wait_timeout参数来控制。

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.01 sec)

我们可以通过 show processlist命令来查看所有连接的用户。

长连接与短连接

在数据库中，所谓长连接就是建立连接后，如果客户端有持续的请求，一直使用同一个连接，而短连接则是指每次执行完很少的请求后就会断开连接从新建立请求。

而建立连接通常花费的时间是普通查询语句的若干倍，所以最好不要在生产环境中频繁的断开和建立连接。

但是在mysql的设计中，临时使用的内存是与连接对象关联的，只有当连接释放后才会将这些内存释放，所以有可能一个长连接导致OOM。

在MySQL5.7及之后提供了mysql_reset_connection 来重新初始化连接资源，这个过程不需要重连和重新做权限检测就可以将mysql连接恢复到初始的状态。

查询缓存

当我们建立连接成功的时候，我们这个查询语句就会在查询缓存中查看是否执行过这条语句，如果执行过这条语句并且这张表在上一条同样的查询之后没有被更新过，就可以直接从查询缓存中拿到结果。如果后续有过更新或者第一次执行这条语句，查询缓存中都拿不到结果。则会执行下面的逻辑。所以如果我们的表有频繁的更新查询缓存存在的意义并不大。在MySQL8.0后查询缓存的机制也就被废弃了。

分析器

当我们输入一串sql语句的时候，对mysql server来讲，只不过是一串字符串罢了，mysql需要用自己的分析器来分析sql语句中每个单词所代表的含义，这个步骤就是词法解析。

接下来，mysql还要做语法解析，判断我们输入的sql语句是否符合语法。

优化器

在语句真正开始执行之前，还需要经过优化器的处理。

优化器会决定索引的选择和join的连接顺序。比如有一个查询有两个索引，索引的选择就是优化器所做的，再比如如下的语句

select * from t1 join t2 on t1.id=t2.id where t1.c=10 and t2.d=20;

这时候就涉及到join的顺序了。

• 方案一：先从t1表中查出列c为10的值再用id关联出t2表中的记录查看t2表中的列d值是否为20。
• 方案二：先从t2表中查出列d为20的值再用id关联出t1表中的记录查看t1表中的列c值是否为10。

执行器

执行器所做的当然就是语句的执行，不过执行之前，还要验证下此用户有没有对该表操作的权限。

如果命中缓存，在查询缓存返回结果的时候也要做权限验证。

通过权限验证后，执行器调用存储引擎的接口接受存储引擎返回的数据。然后返回给客户端。

在执行器执行的时候，例如我们最上面那条语句，如果ID列没有索引的话，执行流程如下。

1. 通过存储引擎接口拿到该表的第一行，判断ID的值，如果ID是10，就加入结果集，否则跳过该条记录。
2. 循环调用下一行，重复上述逻辑，直到这个表最后一行。
3. 将结果集返回给客户端。

如果该表ID字段有索引，执行流程也差不多，只不过不是查找第一行和循环查找下一行了，而是查找符合条件的第一行和符合条件的下一行。这些都是存储引擎层面提供的接口。我们在慢查询日志中的row_examined也是在执行器每次调用存储引擎时进行累加的。

更新语句执行流程

介绍完上述组件后，接下来说一下更新语句的执行流程

首先我们准备这样一个表

create table T(id int primary key,c int);

例如有如下这样一条更新语句

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

首先还是一样，在我们操作数据库的时候，会需要连接到数据库，这是连接器的工作。

接下来上面刚刚也说到，在更新一个表的数据时，会使查询缓存失效，所以在执行这条语句之前，如果开启了查询缓存，会将查询缓存清空。

然后到了分析器和优化器与执行器，分析器会识别出这是一条更新的语句，优化器会决定使用ID这个主键索引，执行器调用InnoDB引擎实现的接口进行数据的更新。

但是不一样的是，在这个更新的过程中，涉及到了两个日志，大家一定都听过，就是redo log（重做日志）和binlog（归档日志）。

redo log（重做日志）

我们首先要达成一个共识，在计算机中，对磁盘的操作是最昂贵的，时间的消耗也是毫秒级别的，尤其是对磁盘的随机读写；而对内存的操作相比于对磁盘来说，相差了好几个数量级。

对与msyql来说，我们对数据的更改需要持久化到磁盘中，而对于每次的数据的修改操作，如果都需要写进磁盘，然后磁盘也要找到那条记录然后进行更新，这个过程中的IO成本，查找成本都比较高。而为了解决这个问题，mysql的设计者，应该说是InnoDB的设计者就提供了redo log。

其实我们要说的就是我们常听到了WAL技术,WAL就是Write-Ahead Logging的缩写，意思就是在写磁盘之前，先写日志，等不忙的时候再去写磁盘。因为写日志是顺序IO，写磁盘是随机IO，使用顺序IO来代替随机IO大大提升了数据库的并发效率。具体的说，当有一条记录需要更新的时候（包括插入），InnoDB引擎首先会将记录写入到redo log中，然后并更新该数据页在内存中的值，这时候更新就算完成了，等到系统相对来说空闲的时候会将这个操作记录更新到磁盘中去，并将redo log中的记录擦除，为新的数据腾出空间。

我们接下来看一下redo log的结构，在InnoDB中，redo log是固定大小的，我们可以配置其大小与个数，例如4个1GB的文件，则mysql的redo log大小就为4GB。而redo log在写入的时候是类似一个环形的结构，如图所示：

write pos就是当前记录的位置，在写入日志的过程中这个点会逐渐后移，而check point是当前要擦除的位置，也是后移并且循环的，当write pos追上check point时此时必须在执行新的更新了，必须停下来将一些记录刷盘然后清除掉一些redo log。

同时，当我们引进了redo log之后，InnoDB获得了crash-safe的能力，何为crash-safe呢？crash-safe就是当数据库在任何时候发生异常重启时，只要是之前提交成功的记录，这个记录一定不会丢失。因为当数据库重启后，可以通过redo log对比binlog发现未持久化的数据然后将此数据写入到磁盘中，具体过程之后的文章会说明。

binlog（归档日志）

我们之前说过，mysql架构可分为server层和存储引擎层两个部分，而binlog就属于server层。

和redo log相比，binlog会有如下的不同：

1. redo log 是InnoDB特有的，而binlog是MySQL在server层实现的。所有的存储引擎都可以使用

2. redo log记录的是物理日志，比如说在某个数据页上进行了怎样的数据修改。而binlog是逻辑日志，记录的是原始语句的逻辑。

   binlog有三种模式

   1. statement
   2. row
   3. mix

3. redo log是循环写的，空间固定会用完，而binlog是追加写的，不会覆盖以前的日志。

这时候你可能会问，为什么已经有了redo log而还需哟binlog呢？

是这样的，binlog的产生优先于redo log，在mysql的默认存储引擎时MyISAM的时候就已经有binlog了，但是就像大家都知道的一样，MyISAM并不支持crash-safe，之后另一个公司以插件的形式提供了InnoDB引擎，因为binlog是没有crash-safe能力的，所以就增加了redo log来提供了crash-safe能力。

更新过程

介绍完这两个日志，终于可以说下上述更新语句的执行流程了。

1. 执行器在更新数据时，会调用存储引擎找到ID=2的这一行，引擎通过树形结构搜索到这一行的数据，如果ID=2的这一行数据本来就在内存中，就可以直接返回给执行器，否则会先将这个数据页从磁盘读入到内存，然后返回给执行器。
2. 执行器拿到行数据后，会将c值加上1，然后调用存储引擎去修改该行数据。
3. InnoDB将这行数据更新到内存中的时候，同时将这次更新操作写入到redo log中，这是后redo log中这次更新处于prepare状态，然后告知执行器执行完成。
4. 执行器操作binlog，将binlog写入磁盘。
5. 执行器调用事务的提交接口，InnoDB将刚刚写入的redo log改为commit状态，至此，更新操作完成。

二阶段提交

你可能对上述的描述会有些疑问，为什么需要先将redo log置为prepare状态然后再操作binlog最后再将redo log改为commit状态呢？

这其实就是我们在分布式场景中常见的二阶段提交。

在分布式场景中，我们为了达成一致性，所以有了这样的一个原子性的提交协议。而二阶段提交运用到这里，就是为了实现两份日志的逻辑的一致性。

为什么要实现一致性呢？那我们说下两份日志不一致会造成什么问题。大家都知道，mysql主从是通过发送binlog实现的。如果我们不使用二阶段提交，假设该库是主库：

1. 当先写redo log 后写binlog：当写入了redo log后数据库crash了，此时重启数据库后会根据redo log来刷数据页，而此时binlog里就会没有这条记录，这就会导致从库看不到这次更新，导致主从不一致。
2. 当先写binlog 后写redo log：当写入binlog后数据库crash了，因为没有写redo log重启后该库看不到这条数据，而binlog中含有这个数据，导致这条数据会同步给从库，所以最终也会导致主从不一致。

综上，无论是先写redo log还是先写binlog都会有问题。这也就是二阶段提交如此必要的原因，同时，不仅仅是在主从复制这种情况下会出现问题，当我们使用binlog恢复数据时同样会产生问题。