本文转载自微信公众号《飞天小牛》，作者飞天小牛。转载本文请联系飞天小牛公众号。2001年，MySQL发布了3.23版本，从此得到了广泛的应用。随着不断的升级迭代，MySQL已经走过了20个年头。为了充分发挥MySQL的性能，顺利地使用它，必须正确理解它的设计思想。因此，有必要了解MySQL的逻辑架构。本文将通过一条SQL查询语句的具体执行过程，详细介绍MySQL架构中的各个组件。MySQL逻辑架构概述MySQL最重要和与众不同的特点是其可插拔存储引擎架构，旨在将查询处理和其他系统任务从数据存储/检索中分离出来。参见官网说明：MySQL可插拔存储引擎架构使数据库专业人员能够为特定应用程序需求选择专门的存储引擎，同时完全摆脱管理任何特定应用程序编码要求的需要。粗略地说，MySQL是可插拔的存储引擎架构，使开发人员能够为特定的应用程序需求选择专门的存储引擎，同时完全无需管理任何特定于应用程序的编码要求。也就是说，尽管不同的存储引擎具有不同的能力，但应用程序不会受到这些差异的影响。如果应用程序更改导致需要更改底层存储引擎，或者添加一个或多个存储引擎来支持新需求，则无需进行重大编码或流程更改即可使事情正常进行。MySQL服务器架构通过跨存储引擎提供一致、易于使用的API，使应用程序免受存储引擎底层复杂性的影响。MySQL的逻辑架构图如下，参考《高性能 MySQL - 第 3 版》：我们可以大致将MySQL的逻辑架构分为服务器层和存储引擎层：1）MySQL的大部分核心服务功能都在服务器层，包括连接、查询分析、分析、优化、缓存和所有内置函数（例如，日期、时间、数学和加密函数），所有跨存储引擎功能都在这一层实现：存储过程、触发器,views等。值得一提的是，Server的最顶层服务是connector，它有管理MySQL连接和授权验证的功能。显然这不是MySQL独有的，大多数基于Web的客户端/服务器工具或服务都具有类似的体系结构。2）第二层是存储引擎（支持InnoDB、MyISAM、Memory等存储引擎）。存储引擎负责在MySQL中存储和提取数据，响应上层服务器的请求。每个存储引擎自然都有其优点和缺点。不同的存储引擎之间是不能互通的，所以我们需要根据不同的场景选择合适的存储引擎。服务器通过API与存储引擎通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对于上层的查询过程是透明的。存储引擎API包含许多低级函数，用于执行诸如“启动事务”或“根据主键获取行”等操作。需要注意的是，在MySQL5.1及之前的版本中，MyISAM是默认的存储引擎，而在MySQL5.5.5之后，InnoDB成为了默认的存储引擎。连接器（Connector）在MySQL5.7的官方文档中，对连接器的描述如下：MySQL连接器为客户端程序提供与MySQL服务器的连接。MySQL连接器为客户端程序提供到MySQL服务器的连接。更详细地说，连接器实际上会做两件事，一是管理MySQL连接，二是验证权限。我们依次解释。首先，要连接到MySQL服务器，我们通常需要提供MySQL用户名和密码，如果服务器运行在我们登录的机器以外的机器上，我们还需要指定主机名，例如host。所以连接命令一般是这样的：shell>mysql-hhost-uuser-pEnterpassword:********当然，如果是在运行MySQL的同一台机器上登录，可以省略hostname，直接使用以下内容：shell>mysql-uuser-p上面的命令大家应该很熟悉了。OK，通过以上命令完成经典的TCP三次握手建立连接后，连接器会根据你输入的用户名和密码验证你的身份：1）如果用户名或密码不正确，你会收到一个“Accessdeniedforuser”错误，则客户端程序结束执行。2）如果用户名和密码认证通过，你会看到如下一串内容：mysql>是提醒你MySQL已经准备好了，可以开始输入SQL语句了！当然，连接器做的不仅仅是比对用户名和密码，它还会验证用户是否有权限执行特定的查询（例如，是否允许用户对世界的Country表执行SELECT语句数据库）。之后，这个连接中所有的权限判断逻辑都会依赖于此时读取的权限。这意味着当一个用户成功建立连接后，即使你使用管理员账号在另一个终端上修改了该用户的权限，也不会对已有的连接权限产生任何影响。也就是说，当修改了用户权限后，只有新的连接才会使用新的权限设置。当一个连接建立后，如果你没有后续动作，那么这个连接就处于空闲状态（Sleep）。其实对于一个MySQL连接（或者说线程）来说，任何时候都是有一个状态的，它代表了MySQL当前在做什么。查看当前状态的方法有很多种，最简单的是使用SHOWFULLPROCESSLIST命令（该命令返回结果中的Command栏表示当前状态）。在一个查询的生命周期中，状态可以改变很多次。我不会在这里详细列出它们。上图中的Sleep状态表示当前连接正在等待客户端发送新的请求，Query状态表示当前连接正在执行查询或者将结果发送给客户端。在MyQL的默认设置中，如果一个连接处于Sleep状态8小时（即超过8小时未被使用），服务器会断开连接，后续对该连接的所有操作都会失败.这个时间是由参数wait_timeout控制的：查询缓存（QueryCache）OK。建立连接后，我们就可以输入select语句进行查询了。执行逻辑来到第二步：查询缓存。官方文档是这样解释QueryCache的：查询缓存将SELECT语句的文本与发送给客户端的相应结果一起存储。如果稍后收到相同的语句，服务器会从查询缓存中检索结果，而不是再次解析和执行该语句。查询缓存在会话之间共享，因此可以发送一个客户端生成的结果集以响应另一个客户端发出的相同查询。也就是说，查询缓存存储了SELECT语句的正文和响应给客户端的相应结果。这样，如果服务器稍后收到相同的SELECT语句，服务器会从查询缓存中检索结果，而不是再次解析和执行该语句。查询缓存在会话之间共享，以便可以发送一个客户端生成的结果集以响应另一个客户端发出的相同查询。如果当前查询恰好命中了查询缓存，MySQL会在返回查询结果前检查一次用户权限。这样仍然不需要解析查询SQL语句，因为当前查询需要访问的表信息已经存储在查询缓存中。那么既然涉及到缓存，那么就不可避免地无法回避缓存一致性的问题。值得庆幸的是，查询缓存不会返回过时的数据，而无需我们做任何额外的工作！查询缓存不返回陈旧数据。当表被修改时，查询缓存中的任何相关条目都会被刷新。当表被修改时，查询缓存中的任何相关条目都将被刷新。请注意，这里的flushed翻译为排空而不是刷新。好像没问题？失效缓存会自动清除，无需我们手动操作。不过遗憾的是，正是因为这个特性，从MySQL5.7.20开始，官方不再推荐查询缓存，MySQL8.0直接删除了查询缓存！查询缓存从MySQL5.7.20开始被弃用，并在MySQL8.0中被移除。其实也不难理解。比如一个流量很大的论坛项目，查询post表的需求一直存在，几乎每时每刻帖子都在增加，那么只要更新这张表，这张表上的所有查询缓存都会被清除，这给MySQL数据库带来了很大的压力，你可以想象。好不容易保存了查询结果，还没来得及使用，就被更新全部清空了。对于MySQL8.0之前的版本，可以将参数query_cache_type设置为DEMAND，这样所有的SQL语句将不再使用查询缓存。对于确定使用查询缓存的语句，可以使用SQL_CACHE显式指定，如下语句：mysql>selectSQL_CACHE*fromt1whereid=1;如果解析器（Parser）没有命中或者没有启用查询缓存，MySQL服务器接下来要做的就是将一条SQL语句转换成执行计划，然后根据执行计划与存储引擎进行交互。这包括多个子阶段：解析SQL、预处理、优化SQL执行计划。此过程中的任何错误（例如语法错误）都可能终止查询。其中，解析SQL和预处理是解析器做的事情，优化SQL执行计划是优化器做的事情。这里先说解析器。这里的《高性能 MySQL - 第 3 版》在书中讲的比较详细。解析器用于解析SQL，预处理器用于预处理。我暂时将它们归类为解析器。解析并生成对应的“解析树”，用于根据文法规则验证语句是否正确。例如，它会验证没有使用错误的关键字，或者关键字使用的顺序是否正确等等，或者它还会验证前后引号是否匹配正确。预处理会进一步检查解析树是否合法，例如检查数据表和数据列是否存在，检查表名和字段名是否正确等。优化器（Optimizer）既然解析树是合法的，MySQL已经知道你要做什么。但是，一个查询可以有多个执行计划，最后都返回相同的结果，那么应该选择哪个执行计划呢？一个简单的例子：mysql>select*fromt1whereid=10andname="good";对于上面的语句，可以先搜索name=good再搜索id=10，也可以先搜索id=10再搜索name=good。这两种不同的执行计划可能会消耗不同的时间成本。那么优化器的作用就是在其中找到最好的执行计划。需要注意的是，这里的执行计划是一个数据结构，而不是像其他很多关系型数据库那样生成相应的字节码。另外，优化器并不关心表使用什么存储引擎，但是存储引擎对优化查询有影响。优化器会请求存储引擎提供特定操作的容量或开销信息，以及表数据的统计信息等。当优化器阶段完成后，这条语句的执行计划就确定了，就可以进入执行者阶段。执行器与命中查询缓存相同。在开始执行SQL语句之前，执行器会先判断当前用户是否有对该表执行查询的权限。如果没有，它会返回一个没有权限的错误。权限认证完成后，MySQL会按照执行计划给出的指令一步步执行。在按照执行计划一步步执行的过程中，需要调用存储引擎实现的接口来完成大量的操作。这些接口就是我们所说的“处理程序API”。查询中的每个表都由处理程序的一个实例表示。实际上，MySQL在优化阶段会为每个表创建一个handler实例，优化器可以根据这些实例的接口获取表的相关信息，包括表的所有列名、索引统计信息等。例如：mysql>select*fromt1whereid=10;假设我们使用默认的InnoDB引擎，executor的执行流程大致是这样的（注意，如果id不是索引，会进行全表扫描，逐条查找，如果是索引，则会在索引组织表中查询，比较负责，这里举一个非索引的例子：1）调用InnoDB引擎接口获取这张表的第一行记录，判断id值是否为10，以及如果是，记录这一行存在于一个集合中；如果不存在，则进行下一行的判断，直到取出表的最后一行2）执行器将上述遍历过程中满足条件的所有行组成的记录集返回给客户端，作为结果张中的图片《高性能 MySQL - 第 3 版》总结下一条查询语句的执行过程：MySQL客户端与服务器建立连接，客户端向服务器发送查询；服务器首先检查查询缓存，如果命中缓存，则立即返回存储在缓存中的查询结果；否则，进入下一阶段；服务器进行SQL解析和预处理，生成合法的解析树；然后优化器生成相应的执行计划；MySQL根据优化器API生成的执行计划调用相应的存储引擎执行，并将执行结果返回给客户端。
