MySQL：关于mysql处理一条sql语句的全流程

从学习了mysql数据库到把他实践到诸多学习项目以来这么长时间，发现并没有很系统的去了解mysql处理一条sql语句的全过程。所以今天大致梳理了一下，以供后面复习参考。

简述

        概括而言，从客户端通过手写或者ORM产生sql之后，客户端会通过维护的网络连接池同mysql建立连接，不过mysql自身的连接器也维护着一个工作线程的连接池，从该连接池中获取工作线程进而继续执行sql。

        我们要执行的sql语句从sql接口进入之后，会尝试从缓存（mysql8.0后已移除该特性）中直接获取对应的记录与结果集，失败后则会进入解析器（叫他分析器也行）、优化器、执行器的流程。

        在解析器中sql语句从人类的自然语义通过词法分析器、语法分析器、语法生成树转换成指令交由优化器生成优化的执行方案，交给执行器后将生成的结果集返回给sql接口，最后根据连接的信息封装结果集为tcp数据报返回给客户端。

大概像这样

mysql的处理流程

1.sql处理前的判断和线程维护

sql语句在上游生成的过程在这里就按下不表。

池化技术

我们在简述中说到，一般在项目里会出现由客户端和数据库维护的两种连接池。那么为什么需要有两种连接池，原因其实很显而易见。

从维护的对象来看：客户端维护的连接池是网络连接的连接池；而mysql的连接器维护的连接池则是工作线程的连接池。

从建立连接池的需求上来看：

• 客户端建立连接池显然是为了更好的网络性能，我们想象一下，每一次建立网络连接都需要客户端从配置文件中读取相应的信息，再通过tcp的三次握手开启连接、四次挥手释放连接，这个过程显然存在可以优化的空间，至于手段那就是池化技术；
• 而mysql的连接器则是为了复用线程，mysql中的数据库连接实际上对应着一条工作线程，每建立一个连接都要负担一个线程的生命周期所带来的开销，包括为这个连接创建线程的开销、映射内核线程、分配的栈资源、线程的销毁等等。池化技术就很好的解决了这个问题。

也就是说sql语句进来被处理之前，客户端要拿到和连接器的连接，而连接器会根据目前是否有空闲线程来为这个数据库连接分配工作线程。

如果没有空闲线程且线程池内的线程数没有达到预设的最大值，那么会进行线程的创建工作，反之则进入等待。接下来工作线程会查询用户权限表，对客户端的连接进行确权和授权。

2.sql语句进入sql接口后的执行过程

sql接口与缓存

当sql顺利进入执行流程之后，会先达到mysql的sql接口，在这里有一个分支，sql接口会先向内存中去查询这句sql有没有被执行过，如果有就拿内存中的结果集返回给客户端（缓存以k-v的形式存储），反之则直接进入解析器，走完一个执行流程将新的结果更新到缓存中。

小细节：在查询缓存的过程中是会校验用户的授权情况的。

实际上，在mysql8.0之后缓存的功能被删去了。原因也很显然，这里的缓存除了对不经常更新的读有作用，其他大部分场景下缓存都会频繁失效，这个功能的意义就不是很大了。

解析器

解析器将sql语句的语义解释为机器指令，其中词法分析、语法分析、生成语法树是最关键的步骤。

词法分析：在这一步里mysql会提取其中的关键词，如：select、where等等，逐步确定这句sql的操作类型、操作对象、具体操作等等。

语法分析：根据mysql的语法规则，语法上的错误会在这里被检查出来。

最后基于之前提取到的关键字信息mysql会生成对应的语法树，这个语法树包含了待执行sql的语义，并且作为mysql程序能够理解的指令交给优化器

优化器

优化器里mysql会基于解析器传入的语法树去产生多个解决方案，也就是执行计划，并把它认为的相对最好的执行计划交给执行器去执行。

优化器的优化原则其实相当复杂，包括索引的选择、多条件下的查询顺序、多表查询下的顺序等等很多方面。

一些可以参考的优化准则如下(参考自：https://juejin.cn/post/7145102393988874253)：

• 多条件查询时，重排条件先后顺序，将效率更好的字段条件放在前面。
• 当表中存在多个索引时，选择效率最高的索引作为本次查询的目标索引。
• 使用分页Limit关键字时，查询到对应的数据条数后终止扫表。
• 多表join联查时，对查询表的顺序重新定义，同样以效率为准。
• 对于SQL中使用函数时，如count()、max()、min()...，根据情况选择最优方案。
  • max()函数：走B+树最右侧的节点查询（大的在右，小的在左）。
  • min()函数：走B+树最左侧的节点查询。
  • count()函数：如果是MyISAM引擎，直接获取引擎统计的总行数。
  • ......
• 对于group by分组排序，会先查询所有数据后再统一排序，而不是一开始就排序。
• ......

执行器（存储引擎等）

进入执行器之后，首先要去确定用户有没有操作的权限，如果没有则返回错误，反之继续。这里就是调用存储引擎提供的api来执行优化器的执行方案，拿到结果返回sql接口，由sql接口整理成结果集。

这里执行过程实际上是有些细节的，并不一定会直接调用存储引擎。

以更新操作为例，在执行开始之前，先记录一下undo-log日志和redo-log(prepare状态)日志。在缓冲区中查找是否存在当前要操作的行记录或表数据（内存中）：若存在，直接对缓冲区中的数据进行写操作，然后利用Checkpoint机制异步刷写到磁盘；若不存在则会调用存储引擎，与磁盘进行直接IO。

3.日志发生了什么

查询语句的那一套流程，更新语句也是同样会走一遍，与查询流程不一样的是，更新语句涉及到事务，就必须保证事务的四大特性：ACID，所以更新流程涉及到几个重要的日志模板：redo log（重做日志）、 binlog（归档日志）和undo-log（回滚日志）

读写sql的区别

对于读sql而言，它一般是没有状态的，但用时较长的sql会被记录在慢查询日志里。

对于写sql而言，日志的作用就体现出来了。任何一条的写入操作的sql都是有状态的，在执行前被撤销的写sql会记录在undo-log中

redo-log会将事务过程记录下来，在写sql前会记录一条perpare状态的日志，在执行完成并且提交事务之后会更改这条日志的状态为commit。这些操作能够保证事务的原子性和持久性，宕机重启也可以据此恢复数据。

redo-log和bin-log的协作

一言以蔽之，redo-log和bin-log的协作主要是为了解决分布式事务一致性问题所采取的两阶段提交的实现。下面具体讲解这个两阶段提交过程。

先讲redo-log的一些工作特点：

redo-log的实现用到了mysql里一个叫做WAL的技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面。

那么redo-log会写满吗？是会的。redo-log利用了CheckPoint机制，存储文件可以抽象成一个环形结构。

redo-log是可以被分配大小的一组文件，mysql会维护这组文件为一个环形结构，每一个文件都是这个环上的一个弧，因此当读到最后一个文件的末尾时再继续会读到第一个文件的开始。

checkpoint机制就是基于这个环展开的：有两个分别叫做write pos和checkpoint的指针，每当有记录写入之后write pos会向后移动将内存中的记录记入redo-log中。checkpoint则是一边移动一边把记录更新到数据文件之中。

为了理解这个协作过程，这里举一个例子。假如有个最最初始的状态，那就是先有write pos往后移动，后产生checkpoint去追赶他（但实际上应该是write pos追赶checkpoint才对）。write pos之前的记录是待落盘的记录，checkpoint经过这些记录之后会将其更新到数据文件之后从redo-log日志中删除掉。那么这个环可以被分成两个区域：checkpoint到write pos的区域是待落盘的记录；write pos到checkpoint是redo-log的剩余空间。当wirte pos追上checkpoint时就是redo-log记满的时候，没有办法继续更新了，需要停下来等待了。

显然，redo-log的这种机制让innoDB可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

那么bin-log在与redo-log的协作中充当了什么角色呢？

首先，redo-log和bin-log存在着以下不同：

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

bin-log的引入可以让redo-log得以被拆分成prepare和commit两个阶段。

假如有一个n更新为n+1的操作，这个操作首先会被写入内存，进而写入redo-log，进入prepare阶段，随后这个操作被写入bin-log记录确定要将其交由引擎执行，待引擎执行完成之后这个会再次写入redo-log日志将该条记录的状态修改为commit状态。至此，借助redo-log和bin-log的协作完成了对两阶段提交的实现。

至于两阶段提交是如何解决分布式事务的一致性问题的，后面整理方案时会一起讨论。