MySQL架构与SQL执行流程

一、MySQL的历史

MySQL开源以后也很有很多出名的分支，例如MariaDB(CentOS7默认自带，是MySQL创始人自己写的分支)等，国内互联网公司也有一些MySQL的分支或者自研的存储引擎，例如网易的InnoSQL等。

MySQL之前是ISAM存储引擎后来升级成MyISAM，然后5.5以后默认为InnoDB，MySQL5.7以后直接就是8.0版本。不同的表可以使用不同的存储引擎，并不一定全部都是InnoDB。

二、MySQL的执行流程

（一）网络通信

1、通信方式

可以分为单双工（只能单方通信），半双工（类似于对讲机，双方能互相通信，但是同一时刻只能一方在向另一方通信），全双工（类似于打电话，双方互相能通信，能随时跟对方沟通），而MySQL使用的就是半双工，因此客户端向MySQL服务器发送指令是不可能发送只执行一半。

2、通信类型：同步或者异步

同步的性能受限于被调用方的性能，异步虽然能避免阻塞，但是不能节约SQL执行的时间。所以一般来说，我们连接数据库都是同步连接。如果要用异步连接，必须用连接池，排队从连接池中获取连接而不是创建新的连接。

3、连接方式：长连接或者短连接

MySQL既支持短连接，又支持长连接。短连接的好处是操作完毕以后可以马上close掉，长连接可以保持打开，减少服务端创建和释放连接的消耗，后面的程序访问时可以复用，一般在连接池中会用长连接。
注： 保持长连接会消耗内存，长时间不活动的连接MySQL服务器会自动断开。

（二）SQL的执行流程

1、查询缓存

MySQL内部自带了一个缓存模块，每次把查询结果保存到缓存中以便下次直接把结果拿来直接使用。
但是这个缓存模块默认是关闭的，因为这个缓存模块要求SQL必须跟之前的一模一样，多一个空格都会被认为不同的SQL。同时，当表中的数据只要有一条发生了该表，该缓存就会失效，用起来实在是有点鸡肋。所以缓存这块还是交给专门的ORM框架（例如Mybatis默认开启了一级缓存）或者独立缓存服务（Redis）。
注： 于是MySQL8.0以后把这个独立的缓存模块给干掉了。

2、解析器

解析器分为词法解析器和语法解析器。
词法解析器：将SQL语句解析成一个个单词。
语法解析器：对SQL做语法检查，例如单引号有没有闭合等，然后根据MySQL定义的语法规则生成解析树。

3、预处理器

检查解析树，解决解析器无法解析的语义，例如表和列名是否存在等问题。预处理器处理完以后会再生成一个新的解析树。

4、优化器

得到解析树以后并不是直接执行SQL而是优化器通过解析树生成不同的执行计划。然后选择一种最优的执行计划，因为MySQL里面是基于开销的优化器，因此开销最小的执行计划就是最优的。
优化器能进行哪些优化？
1）当我们多张表进行关联查询时以哪张表作为基准表；
2）有多个索引使用时，选择哪个索引的成本最小。

注： 解析树和执行计划都是一个数据结构。

5、存储引擎

存储引擎有多种：InnoDB、MyISAM、BDB、Memory等等，每个存储引擎都有自己的特点，MySQL都支持这几种存储引擎。5.5版本以后默认的存储引擎改成了InnoDB，5.5之前默认的都是MyISAM。
其实存储引擎的本质就是我们存储数据的形式，只不过不同的存储引擎还有不同的特点。

如何选择存储引擎？
如果对数据一致性要求比较高，需要事务支持，则可以选择InnoDB；
如果查询多更新少，对查询性能要求比较高，则可以选择MyISAM；
如果需要一个用于查询的临时表，则可以选择Memory；

6、执行引擎

使用执行计划的是执行引擎，其利用存储引擎提供的API来完成SQL的查询，并将结果返回给客户端。

三、MySQL架构

（一）MySQL模块详解

这是在网上找到的MySQL内部模块的具体图解。
1、 Connector：用来支持各种语言和 SQL 的交互，比如 PHP，Python，Java 的 JDBC；
2、 Management Serveices & Utilities：系统管理和控制工具，包括备份恢复、MySQL 复制、集群等等；
3、 Connection Pool：连接池，管理需要缓冲的资源，包括用户密码权限线程等；
4、 SQL Interface：用来接收用户的 SQL 命令，返回用户需要的查询结果
5、 Parser：用来解析 SQL 语句；
6、 Optimizer：查询优化器；
7、 Cache and Buffer：查询缓存，除了行记录的缓存之外，还有表缓存，Key缓存，权限缓存等等； 8、 Pluggable Storage Engines：插件式存储引擎，它提供 API 给服务层使用， 跟具体的文件打交道。

（二）MySQL架构划分

总体上可以讲MySQL分为三层：
连接层：跟客户端对接的层，验证客户端的身份和权限；
服务层：真正执行操作的层，对SQL进行操作，包括查询缓存的判断，对sql进行词法语法的解析，然后优化器对sql进行优化生成执行计划，最后交给执行器去执行；
存储引擎层：跟硬件打交道的层，存储引擎是我们的数据真正存放的地方。
再往下走就是内存和磁盘。

（三）InnoDB内存结构和磁盘结构

上面讲述的都是查询操作的流程，更新的流程有什么区别呢？
其实基本流程都是一样，也需要经过解析器、优化器的处理，最后交给执行器去执行，区别就在于拿到符合条件的数据之后的操作。
下面在讲更新操作的具体流程之前先讲解一下几个必要的知识点，这样才能讲清楚更新操作的具体流程。

首先大家应该知道InnoDB的数据都是存放在磁盘上的，InnoDB操作数据的最小逻辑单位是页。不清楚InnoDB存储引擎的可以看我另一博客：MySQL之索引原理

我们对数据的操作不是每次都直接操作磁盘，这样每次都得进行IO，速度多慢，这个时候InnoDB就用到了缓冲池的技术，也就是把磁盘读到的页放到一块内存区域里面，这个区域就是Buffer Pool。这个不难理解，就跟我们请求数据库比较慢，直接使用redis缓存数据一样。

当我们读取相同的页数据时先判断缓冲池中有没有，有就直接读取，没有就再访问磁盘。修改数据的时候，先修改缓冲池中的页，当内存中的数据页和磁盘中的数据页不一致的时候，我们就把它叫做脏页。InnoDB有专门的后台线程会把Buffer Pool的数据写入磁盘，每隔一段时间就一次性把多个修改写入磁盘，这个动作就叫做刷脏。

下图就是InnoDB的内存结构和磁盘结构。

1、InnoDB的内存结构

1.1、缓冲池(Buffer Pool)

Buffer Pool是InnoDB里非常重要的一个结构，它的内部又分为几块区域。从上图我们可以看到，Buffer Pool主要分为三个部分：Buffer Pool、Change Buffer、Adaptive Hash Index。除此之外，我们可以看到内存结构中除了Buffer Pool还有一个Log Buffer，这个区域就是跟我们日常用于数据恢复的redo log有关系了。

1.1.1、Buffer Pool

Buffer Pool缓存的是页的信息，包括数据页和索引页。Buffer Pool默认大小是128M，可以调整。
当内存的缓存池写满了怎么办？
内存的缓存池跟Redis一样都使用了LRU算法，当然两者的具体实现逻辑不一样，但是核心算法是一样的，都是淘汰不常用的，留下来的都是热点数据。

1.1.2、Change Buffer (写缓冲)

如果数据页不是唯一索引，也不存在数据重复的情况，那么就不需要从磁盘加载索引页判断数据是不是重复，这种情况就可以先把修改记录在内存的缓冲池中，从而提升更新语句的执行速度。最后某个时间点把Change Buffer记录到数据页的操作叫做merge。
发生merge的情况：访问数据页的时候、数据库shut down的时候、redo log写满的时候。

1.1.3、Adaptive Hash Index

一种哈希的索引，这个是特殊的索引，有兴趣的可以自行百度，这里就不细说了。

1.2、Log Buffer (redo)

当Buffer Pool的脏页还没有刷入磁盘的时候如果数据库宕机或者重启，那么数据就会丢失。为了避免这个问题，InnoDB把所有对页的修改操作专门写入一个日志文件，并且在数据库启动时从这个文件进行恢复操作。这个文件就是位于磁盘上的redo log，就是用redo log来实现事务的持久性的。

这个文件对应于/vr/lib/mysql/目录下的ib_logfile0和ib_logfile1，每个48M。
这种日志和磁盘配合的过程就是MySQL里的WAL（Write-Ahead Loggin）技术，它的关键点就是先写日志后写磁盘。

可能有人有疑惑，既然我能直接写磁盘上的redo log，那为什么我不直接把修改的数据直接写到磁盘上的DB File里呢？
这里我们就来了解一下顺序IO和随机IO的区别了。
磁盘的最小组成单元是扇区，通常是512字节；操作系统和内存交互，最小的单位是页（Page）；操作系统和磁盘交互，读写磁盘，最小的单位是块（Block）。

如果我们需要的数据是随机分散在不同页的的不同扇区中，那么找到相应的数据就需要等到磁臂旋转到指定的页，然后盘片寻找到对应的扇区，才能找到我们所需要的一块数据，一直进行此操作直到找完所有的数据，这就是随机IO，速度比较慢。
如果我们找到第一块数据以后，所需要的其他数据就在这一块数据之后，那么就不需要重新寻址，可以依次拿到我们需要的数据，这就是顺序IO。

数据的刷盘就是随机IO，而记录日志是顺序IO，顺序IO的效率更高，因此可以先把修改的数据写入到内存中Buffer Pool中的Log Buffer中，然后再将Log Buffer的文件写入到磁盘的redo log文件中。但并不是每次操作都是直接写入Log Buffer，然后直接写入redo log中，而是有参数控制的，参数默认为1，参数含义具体如下：

在我们写入数据到磁盘的时候，操作系统本身是有缓存的，flush就是把操作系统缓冲区写入到磁盘的中去。
注： redo log的内容主要用于崩溃恢复。磁盘的数据文件（db file）其中的数据来自buffer pool。redo log写入磁盘，不是写入数据文件中。

redo log的特点：
1）redo log是InnoDB存储引擎实现的，并不是所有存储引擎都有；
2）redo log记录的是页发生了什么改动，而不是数据页更新后的状态，属于物理日志；
3）redo log的大小是固定的，前面的内容会被覆盖；

2、InnoDB的磁盘结构

磁盘结构里面主要是各种表空间，表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都放在表空间中。InnoDB的磁盘表空间主要分为5类以及一个Redo Log。

2.1、系统表空间(system tablespace)

默认情况下InnoDB存储引擎有一个共享表空间（在var/lib/mysql/ibdata1）,也叫做系统表空间。
系统表空间包括InnoDB的数据字典、双写缓冲区，Change Buffer和Undo logs。
如果没有指定独占表（file-per-table），那么用户创建的表和索引数据也在系统表空间中。

InnoDB的数据字典：由内部系统表组成，存储表和索引的元数据（定义信息）；
Undo logs：后面介绍，有单独的Undo log表空间；
双写缓冲（InnoDB的一大特性）：InnoDB的页跟操作系统的页大小不一致，InnoDB的页默认大小为16k，操作系统的页大小为4k，因此InnoDB的页写入磁盘时，一个页需要分为4次写入。如果存储引擎正在写入页的数据到磁盘的时候发生了宕机，可能出现页只写了一部分的情况，这种情况就叫做部分写失效，可能导致数据丢失。虽然可以使用redo log进行数据故障恢复，但是如果这个页本身已经损坏了，那就没法用它来做奔溃恢复了。所以在应用redo log之前需要有个页的副本，如果出现了写入失效，就用也的副本来还原这个页，然后在应用redo log，这个副本就是double write，InnoDB的双写技术实现了数据页的可靠性。
跟redo log一样，double write由两部分组成，一部分是内存你的double write，另一部分是磁盘上的double wirte。因为double write是顺序写入的，不会带来很大的开销。

2.2、独占表空间(file-per-table tablespace)

我们可以设置让每张表单独占一个表空间，可以通过参数设置，默认是开启的。开启后，每张表都会开辟一个表空间，存放表的索引和数据。
但是其他类的数据，例如undo信息、插入缓冲索引页、系统事务信息，二次写缓冲等还是存放在共享表空间。

2.3、通用表空间(general tablespaces)

通用表空间也是一种共享的表空间。可以创建一个通用的表空间，用来存储不同数据库的表，数据路径和文件都可以自定义。
在创建表的时候可以指定表空间；不同表空间的数据是可以移动的；删除表空间需要先删除里面的所有表。
这些操作都可以网上搜到相应的语法，有兴趣的可以自行百度。

2.4、临时表空间(temporary tablespaces)

临时表空间用于存储临时表的数据，包括用户创建的临时表和磁盘的内部临时表。对应数据目录下的ibtmp1文件。当数据服务器正常关闭时，该表空间被删除，下次重新产生。

2.5、Redo log

上面介绍内存机构的时候已经介绍了Redo log，这里就不再说了。

2.6、undo log tablespace

undo log就是我们事务中用于回滚操作的根本原因，保持原子性。undo log记录了事务发生之前的数据状态，如果出现修改数据出现异常时可以使用undo log来实现回滚操作。

在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页上操作实现的，属于逻辑格式的日志。

undo log的数据默认在西永表空间ibdata1文件中，因为共享表空间不会自动收缩，也可以单独创建一个undo表空间。

我们来梳理一下更新操作的流程：
1）事务开始，从内存或者磁盘中取到这条数据，返回给Server的执行器；
2）执行器将原值a修改为b；
3）记录a到undo log；
4）记录b到redo log；
5）调用存储引擎接口，在内存（Buffer Pool）中修改值为b；
6）事务提交。

3、Binlog

除了InnoDB架构中的日志文件，MySQL的Server层也有一个日志文件，那就是binlog，它可以被所有的存储引擎使用。
binlog以事件的形式记录了所有的DDL和DML语句，因为记录的是操作而不是数据值，因此可以用来做主从复制和数据恢复。

跟redo log不同，binlog的文件内容是可以追加的，没有固定大小的限制。

数据恢复：在开启binlog的情况下，我们可以把binlog导出为SQL语句，把所有的操作重放一遍，来实现数据恢复。
主从复制：从服务器读取主服务器的binlog然后重新执行一遍以此来实现主从复制。

MySQL的binlog在8之前是默认关闭的，需要手动开启

（四）数据的更新操作

结合以上所讲的，我们来总结一下sql的更新操作流程，如下图：

1、先查询到这条数据，如果有缓存会用到缓存；
2、把name更新成想要的值，然后调用存储引擎的api接口，写入这一行数据到内存中，同时记录redo log，这时redo log进入prepare状态，然后告诉执行器执行完成，可以随时提交了；
3、执行器收到通知以后记录binlog，然后调用存储引擎的api接口，设置redo log为commit状态；
4、更新完成。