本文转载自微信公众号《石山的架构笔记》，作者崔浩。转载本文请联系石山架构笔记公众号。一开始，相信程序员朋友们经常会使用MySQL数据库作为书籍持久化的工具。我们最常使用MySQL中的SQL语句，将客户端的指令一条一条发送给MySQL，然后获取返回的数据结果进行后续的逻辑处理。.虽然大家经常使用SQL语句来完成工作，但是你有没有关注过它的执行阶段，是用什么技术来完成的呢？今天就带大家看看MySQL数据库处理SQL请求的全过程。下面将介绍以下内容：MySQL中查询请求处理流程MySQL中处理SQL的组件介绍，包括：connectorquerycacheanalyzeroptimizerexecutorqueryexecutionflow众所周知，查询请求在MySQL数据库应用中最多使用，假设我们在代码段1中输入SQL，通过客户端请求MySQL服务器，我们会得到一个包含user的结果集。但是，我们对MySQL的处理过程并不了解，那么我们来看看MySQL服务器在查询请求前后发生了什么。Select*fromuserwhereuserId=1代码段1如图1所示：图1MySQL查询请求处理流程整个图由三部分组成，从上到下分别是客户端（紫色）、MySQLServer层（绿色）、MySQL存储引擎层（黄色）。client是不言而喻的，主要负责与MySQLServer层建立连接，发送查询请求和接收响应结果集。MySQLServer层主要包括连接器、查询缓存、分析器、优化器和执行器。这些组件包含了MySQL的大部分主要功能，比如最常用的存储过程、触发器、视图都在这一层。还有一个通用的日志记录模块binlog。MySQL存储引擎层主要负责数据的存储和检索。支持多种存储引擎，如：InnoDB、MyISAM等，常用的是InnoDB，从MySQL5.5.5开始成为MySQL默认的存储引擎。重要的是InnoDB引擎包含了自己的日志模块redolog，这个在后面的update语句中会提到。上面介绍了MySQL的组件结构，这里简单介绍一下它处理SQL语句的过程，然后再对各个组件一一介绍。如图2所示，在图1的基础上增加了进程处理的编号，按照编号来看MySQL的各个组件是如何处理SQL查询请求的。1、Connector：客户端登录MySQL时，判断身份认证和权限。2、查询缓存：执行查询语句时，会先查询缓存（MySQL8.0后移除）。3.分析器：假设没有命中查询缓存，SQL请求就会来到分析器。分析器负责明确SQL要完成的功能，检查SQL的语法是否正确。4.优化器：为SQL提供优化的执行计划。5.Executor：将语句分发给相应的存储引擎执行并返回数据。MySQL组件定义下面通过一张大图梳理了SQL执行过程，这里详细介绍对应的组件。图2SQL请求执行流程连接器客户端需要通过连接器访问MySQLServer，连接器主要负责身份认证和权限识别。即负责用户登录数据库的相关认证操作，如：验证账号密码、权限等。在用户名和密码有效的前提下，用户相应的权限将被在权限表中查询，权限就会分配给用户。连接完成后，可以通过图3看到连接状态，可以通过命令行“showprocesslist”生成图3中的查询结果。“Command”一栏返回的内容中，“Sleep”表示MySQL对应一个空闲连接。“查询”表示正在查询的连接。图3连接状态连接状态如前所述。这里将五种连接状态整理成下表供大家参考。command表示sleep线程正在等待client发送数据query连接线程正在执行query锁定线程正在等待释放表锁排序结果线程正在排序结果发送数据返回数据给请求端mysql将连接器中的连接分为长连接和短连接.长连接是指连接成功后，客户端请求始终使用同一个连接。短连接是指每执行一次SQL请求后连接就会断开，如果再有SQL请求会重新建立连接。由于短连接会重复创建连接消耗相同的资源，所以大多数情况下会选择长连接。但是为了保持长连接，它会占用系统内存，这些占用的内存要等到连接断开后才会释放。这里提出两种解决方案：1、定时断开长连接，在一段时间后或者执行占用内存较大的查询后断开，从而释放内存，查询时重新建立连接。2.对于MySQL5.7或更高版本，通过执行mysql_reset_connection重新初始化连接。这个过程不会重新建立连接，但会释放占用的内存，将连接恢复到刚刚建立的状态。查询缓存可以在与数据库建立连接后执行SQL语句，但是在执行之前会先查询缓存，目的是检查语句之前是否执行过，并将执行结果缓存在内存中key-valueup的形式。Key是查询SQL语句，Value是查询结果。如果命中缓存键，则直接返回给客户端。如果没有命中，则进行后续操作。SQL执行后，结果仍会缓存起来，以备下次调用。不建议对MySQL查询使用缓存，因为会出现这样的场景：如果对某张表进行了更新，那么这张表的查询缓存就会被清空。如果表不断被使用（更新、查询），查询缓存会频繁失效，获取查询缓存也会失去意义。但是可以用在一些不经常修改的数据表中，比如：系统配置，或者不经常修改的表。缓存淘汰策略是先进先出的，适用于查询远大于修改的情况。否则，建议使用Redis或其他缓存工具。因此，大多数情况下不建议使用查询缓存。MySQL8.0后删除了查询缓存功能。官方认为这个功能应用场景较少，所以去掉了。如果在MySQL中不需要使用querycache，也可以将参数query_cache_type设置为DEMAND，那么在执行SQL语句时默认不会使用querycache。如果启用了缓存，可以通过“showstatuslike'Qcache%'”命令查看缓存的状态。如图4所示，缓存状态如图4所示，一些比较常用的状态值如下：Qcache_inserts中是否有新的数据加入，每增加一条该Value就会加1添加了数据。Qcache_hits查询语句是否命中缓存，每命中一次，Value加1。Qcache_free_memory缓存空闲大小。如果分析器没有命中查询缓存，SQL请求就会进入分析器，用于区分SQL语句的执行目的。执行过程大致分为两步：第一步，词法分析（Lexicalscanner），主要负责从SQL语句中提取关键字，如：查询表、字段名、查询条件等。第二步，语法规则（Grammarrulemodule），主要判断SQL语句是否符合MySQL的语法。其实说白了，词法分析（Lexicalscanner）就是把整个SQL语句拆分成单词，语法规则（Grammarrulemodule）根据MySQL定义的语法规则生成相应的数据结构，存储在对象中结构。结果被优化器用来生成执行计划，然后调用存储引擎接口执行。我们看下面的例子，假设有这样一条SQL语句“selectusernamefromuserinfo”。首先通过词法分析，从左到右对字符一个一个进行分析，得到如表1所示的四个词。判断输入的SQL语句是否满足MySQL语法，生成图5的语法树。在SQL语句生成的四个词中，识别出两个关键字，分别是select和from。按照MySQL的语法，Select和from对应的是fields字段，下面要附上用户名；Tables字段后跟from，userinfo附加在它下面。图5语法规则生成语法树优化器优化器的作用是优化SQL，生成最高效的执行计划。如图6所示，上述SQL解析器通过语法分析和语法规则生成SQL语法树。这个语法树作为优化器的输入，优化器（黄色部分）包括逻辑转换和代价优化两部分。优化完成后，会生成SQL执行计划作为整个优化过程的输出，交给执行器在存储引擎上执行。图6优化器的位置如上图所示。本节重点介绍优化器中的逻辑转换和成本优化。逻辑变换逻辑变换是基于关系代数的变换。其目的是为了简化并保证SQL变化前后的结果相同，即逻辑变化不会带来结果集的变化。主要包括以下几个方面：否定剔除：对于“求和”或“析取”前出现“否定”的情况，对关系条件进行拆分，剔除外面的“NOT”。等价常数传递：利用等价关系的传递特性，为了尽快进行“下推”操作。“下推”的基本策略是始终将过滤器表达式移动到尽可能靠近数据源的位置。常量表达式计算：对于一个可以立即计算出结果的表达式，直接计算出结果，同时尽可能提前对结果和其他条件进行简化。这样概念可能有些抽象，我们通过图7看看SQL中是如何执行逻辑变化的。图7逻辑转换如图7，从上到下有四个步骤：1.对于已有的SQL语句，先通过“否定消去”去掉条件判断中的“NOT”。该语句由原来的“或”转换为“和”，并且改变了大于或小于的符号。蓝色部分是修改前的SQL，红色部分是修改后的SQL。2、等值传递，这一步很好理解就是分别去掉“t2.a=9”和“t2.b=5”来替换SQL中对应的值。3、下一步就是计算常量表达式，计算“5+7”得到“12”。4、最后进行常量表达式计算后的化简，将“9<=10”化简为“true”带入最终的SQL表达式，完成优化。成本优化成本优化用于确定每个表，是否根据条件应用索引，应用哪个索引，以及确定多表连接的顺序。为了完成成本优化，需要找到成本最低的解决方案。因此，优化器通过基于成本的计算方法（Cost-basedOptimization）来决定如何执行查询。简化流程如下：赋值操作成本：为每一次数据库操作（创建表、返回数据集）设置相应的成本，这个成本值一般设置为1、0.2等值，没有具体含义就是成本操作定义。计算操作数：对SQL语句涉及的操作进行逻辑计算。说白了，就是看这个SQL请求需要做哪些具体的数据库操作。求和操作成本：既然知道了SQL由哪些数据库操作组成，每一个操作对应的成本，求和之后，就知道了整个SQL执行的成本。选择成本计划：如果SQL不执行的操作都是一个计划，那么这些操作的不同组合会对应不同的计划。这里需要选择整体执行成本最低的运行计划作为本次执行SQL语句的成本计划，从而达到最低的总成本。这里将配置操作的开销分为MySQL服务层和MySQL引擎层。MySQL服务层主要定义CPU开销，MySQL引擎层主要定义IO开销。MySQL5.7引入了两张系统表mysql.server_cost和mysql.engine_cost分别配置这两层的costs，如下：MySQL服务层cost存储在server_cost表中，具体内容如下：row_evaluate_cost(default0.2)计算满足条件的行的成本，行数越多成本越大memory_temptable_create_cost(default2.0)创建临时内存表的成本memory_temptable_row_cost(default0.2)一行临时内存的成本tablekey_compare_cost(default0.1)key比较的代价，比如排序disk_temptable_create_cost(default40.0)内部myisam或innodb临时表的创建代价disk_temptable_row_cost(default1.0)内部myisam或innodb临时表的行代价可以看出上面说创建临时表的代价是非常高的，尤其是内部的myisam或者innodb临时表表面。MySQL引擎层的开销存储在表engine_cost中，其具体内容如下：io_block_read_cost(default1.0)从磁盘读取数据的开销，对于innodb来说，就是从磁盘读取一页的开销memory_block_read_cost(default1.0)从内存中读取数据的成本Cost，对于InnoDB来说，是指从缓冲池中读取一页的成本。目前io_block_read_cost和memory_block_read_cost的默认值都是1，实际生产中建议适当增加memory_block_read_cost，尤其是普通硬盘场景。MySQL会根据SQL查询生成的查询计划中对应的操作，从上面两个成本表中查找对应的成本值，累加起来形成SQL计划的最终执行成本。然后比较多个可能的执行计划，选择成本最小的计划执行。Executor分析器生成查询计划并通过优化器后，到达执行器。执行器会选择执行计划开始执行，但在执行前会检查请求用户是否有查询权限，如果没有则返回错误信息，否则会调用MySQL引擎层的接口执行相应的SQL语句并返回结果。例如，SQL：“SELECT*FROMuserinfoWHEREusername='Tom';”假设“用户名”字段没有被索引，它会调用存储引擎从第一个条目开始搜索。如果用户名为“Tom”，结果会Set返回，没有找到则检查下一行，重复上一步的操作，直到读取全表或找到对应的记录。需要注意的是，SQL语句的执行顺序并不是按照书写的先后顺序。顺序的定义会在分析器中完成，一般按照如下顺序：图8SQL执行顺序总结本文从SQL语句在MySQL中的执行过程开始，首先介绍查询请求的执行流程，其中对MySQL的处理分为MySQLServer层和MySQL存储引擎层。通过介绍SQL语句的流程，介绍了后面要介绍的五大组件，它们分别是：连接器、查询缓存、分析器、优化器、执行器。以下各节详细介绍了每个组件。Connector，负责身份认证和权限识别；查询缓存，缓存查询结果集，提高查询效率；分析器，对SQL语句进行语法分析和语法规则，生成语法树和执行计划；优化器，包括逻辑转换和成本优化；执行者检查用户权限后逐项查询数据，整个过程遵循SQL语句的执行顺序。
