开篇

• 相信广大程序员朋友经常使用MySQL数据库作为数据持久化的工具，我们最常使用的就是MySQL中的SQL语句，从客户端向MySQL发出一条条指令，然后获取返回的数据结果进行后面的逻辑处理。尽管大家经常使用SQL语句完成工作，你是否关注过其执行的阶段，利用了哪些技术完成？今天，就带大家一起看看MySQL数据库处理SQL请求的全过程。下面将会讲述如下内容：
• 查询请求在MySQL中的处理流程
• MySQL 中处理SQL的组件介绍，包括：

• 连接器
• 查询缓存
• 分析器
• 优化器
• 执行器

查询请求的执行流程

• 客户端不言而喻，主要负责与MySQL Server层建立连接，发送查询请求以及接受响应的结果集。

• MySQL Server层，主要包括连接器、查询缓存、分析器、优化器、执行器等。这些组件包含了MySQL的大部分主要功能，例如平时使用最多的存储过程、触发器、视图都在这一层中。还有一个通用的日志模块 bin log。

• MySQL 存储引擎层，主要负责数据的存储和提取。其支持多个存储引擎，例如：InnoDB、MyISAM等。常用的有InnoDB，它从MySQL 5.5.5版本开始成为了MySQL的默认存储引擎，重要的是InnoDB 引擎包含了自带的日志模块 redo log，这个在后面讲述更新语句的时候会着重提到。

执行器 对"表" 的权限进行判断。而连接器是验证用户身份。 

MySQL组件定义

连接器

客户端需要通过连接器访问MySQL Server，连接器主要负责身份认证和权限鉴别的工作。也就是负责用户登录数据库的相关认证操作，例如：校验账户密码，权限等。在用户名密码合法的前提下，会在权限表中查询用户对应的权限，并且将该权限分配给用户。在连接完成以后可以通过图3看到连接状态，可以通过命令行“show processlist”生成图3的查询结果。其中“Command”列返回的内容中，“Sleep”表示MySQL相同中对应一个空闲连接。而“Query”表示正在查询的连接。

图3 连接状态

上面提到了连接状态，这里将5种连接状态整理为如下表格，方便大家参考。

MySQL将连接器中的连接分为长连接和短连接。

• 长连接是指连接成功后，客户端请求一直使用是同一个连接。

• 短连接是指每次执行完SQL请求的操作之后会断开连接，如果再有SQL请求会重新建立连接。由于短连接会反复创建连接消耗相同资源，因此多数情况下会选择长连接。但是为了保持长连接，会占用系统内存，而这些被占用的内存知道连接断开以后才会释放。这里提出了两个解决方案：

查询缓存

在建立与数据库的连接以后就可以执行SQL语句来，不过在执行之前会先查询缓存，其目的是查看是否之前执行过该语句，并且将执行结果按照key-value的形式缓存在内存中了。

Key 是查询的SQL语句，Value 是查询的结果。如果缓存 Key 被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，执行完SQL仍旧会把结果缓存起来，方便下一次调用。

MySQL 查询不建议使用缓存，因为会出现这样的场景：如果针对某张表进行更新，针对这张表的查询缓存就会被清空。如果张表不断地被使用（更新、查询），那么查询缓存会频繁地失效，获取查询缓存也失去了意义。不过可以运用在一些修改不频繁的数据表，例如：系统配置、或者修改不频繁的表。缓存的淘汰策略是先进先出，适用于查询远大于修改的情况下， 否则建议使用Redis或者其他做缓存工具。因此大多数情况下不推荐使用查询缓存。MySQL 8.0 版本后删除了查询缓存的功能，官方认为该功能应用场景较少，所以将其删除。

如果你不需要在MySQL中使用查询缓存，也可以将参数query_cache_type设置成 DEMAND，那么默认情况下的执行SQL语句时就不会使用查询缓存了。如果打开了缓存可以通过“show status like 'Qcache%'”命令查看缓存的情况。

如图4 缓存状态

如图4 所示，其中几个使用较多的状态值如下：

Qcache_inserts 是否有新的数据添加，每有一条数据添加Value会加一。

Qcache_hits 查询语句是否命中缓存，每有一条语句命中Value会加一。

Qcache_free_memory 缓存空闲大小。

分析器

如果查询缓存没有命中，那么SQL请求会进入分析器，分析器是用来分辨SQL语句的执行目的，其执行过程大致分为两步：

第一步，词法分析(Lexical scanner)，主要负责从SQL 语句中提取关键字，比如：查询的表，字段名，查询条件等等。

第二步，语法规则(Grammar rule module)，主要判断SQL语句是否合乎MySQL的语法。

其实说白了词法分析(Lexical scanner) 就是将整个SQL语句拆分成一个个单词，而语法规则(Grammar rule module)则根据MySQL定义的语法规则生成对应的数据结构，并存储在对象结构当中。

其结果供优化器生成执行计划，再调用存储引擎接口执行。来看下面这个例子，假设有这样一个SQL语句“select username from userinfo”。

先通过词法分析，从左到右逐个字符进行解析，获得如表1的四个单词。

表1 语法分析关键字

然后再通过语法规则解析，判断输入的SQL 语句是否满足MySQL语法，并且生成图5的语法树。由SQL语句生成的四个单词中，识别出两个关键字，分别是select 和from。根据MySQL的语法Select 和 from之间对应的是fields 字段，下面应该挂接username；在from后面跟随的是Tables字段，其下挂接的是userinfo。

图5 语法规则生成语法树

优化器

优化器的作用是对SQL进行优化，生成最优的执行方案。如图6所示，前面提到的SQL解析器通过语法分析和语法规则生成了SQL语法树。这个语法树作为优化器的输入，而优化器（黄色的部分）包含了逻辑变换和代价优化两部分的内容。在优化完成以后会生成SQL执行计划作为整个优化过程的输出，交给执行器在存储引擎上执行。

图6 优化器所处的位置

如上图所示，这节的重点在优化器中的逻辑变换和代价优化上。

逻辑变换

• 否定消除：针对表达式“和取”或“析取”前面出现“否定”的情况，应将关系条件进行拆分，从而将外层的“NOT”消除。

• 等值常量传递：利用了等值关系的传递特性，为了能够尽早执行“下推”运算。“下推”的基本策略是，始终将过滤表达式尽可能移至靠近数据源的位置。

• 常量表达式计算：对于能立刻计算出结果的表达式，直接计算结果，同时将结果与其他条件尽量提前进行化简。

代价优化

1. 赋值操作代价：针对每个数据库操作（创建表、返回数据集）设置对应的代价，这个代价值一般设置为1、0.2之类的值，没有具体的含义就是对操作的代价定义。

2. 计算操作数量：将SQL语句中涉及到的操作进行逻辑，并且做计算。说白了就是看这次SQL请求需要做哪些具体的数据库操作。

3. 求和操作代价：既然知道SQL由哪些数据库操作组成，同时知道每个操作对应的代价，求和以后就是知道整体SQL执行的代价。

4. 选择代价计划：如果说没给SQL执行的操作都是一个计划，那么这些操作的不同组合就会对应不同的计划，这里需要选择整体执行代价最低的操作计划，作为这次执行SQL语句的代价计划，从而达到总代价最低。

• MySQL 服务层代价保存在表server_cost中，其具体内容如下：

• row_evaluate_cost (default 0.2) 计算符合条件的行的代价，行数越多，此项代价越大

• memory_temptable_create_cost (default 2.0) 内存临时表的创建代价

• memory_temptable_row_cost (default 0.2) 内存临时表的行代价

• key_compare_cost (default 0.1) 键比较的代价，例如排序

• disk_temptable_create_cost (default 40.0) 内部myisam或innodb临时表的创建代价

• disk_temptable_row_cost (default 1.0) 内部myisam或innodb临时表的行代价

• io_block_read_cost (default 1.0) 从磁盘读数据的代价，对innodb来说，表示从磁盘读一个page的代价

• memory_block_read_cost (default 1.0) 从内存读数据的代价，对innodb来说，表示从buffer pool读一个page的代价

执行器

当分析器生成查询计划，并且经过优化器以后，就到了执行器。执行器会选择执行计划开始执行，但在执行之前会校验请求用户是否拥有查询的权限，如果没有权限，就会返回错误信息，否则将会去调用MySQL引擎层的接口，执行对应的SQL语句并且返回结果。

例如SQL：“SELECT * FROM userinfo WHERE username = 'Tom';“

假设 “username“ 字段没有设置索引，就会调用存储引擎从第一条开始查，如果碰到了用户名字是” Tom“， 就将结果集返回，没有查找到就查看下一行，重复上一步的操作，直到读完整个表或者找到对应的记录。

需要注意SQL语句的执行顺序并不是按照书写顺序来的，顺序的定义会在分析器中做好，一般是按照如下顺序：

图8 SQL的执行顺序

总结