MYSQL逻辑架构

在日常工作中，我们写得最多的sql语句就是查询语句，那一条查询语句从编写到执行到输出结果，都经历哪里过程，mysql底层又都做了哪些处理，接下来我们通过一步一个脚印来分析并弄懂这些问题的答案。

首先，假设我们有一个表t_user，我们通过执行一条select语句来看返回了什么：

mysql> select  * from t_user where id_ = 1;+-----+-------+| id_ | name_ |+-----+-------+|   1 | test  |+-----+-------+1 row in set (0.01 sec)

我们可以看到，就返回了一个查询结果，并不知道它在mysql里面经历了哪些过程，此时我们需要了解mysql底层架构由哪几部分组成，每个部分的作用，他们之间的执行顺序，还有最终如何协同合作，输出我们想要的结果的，下面我们先来看看mysql底层逻辑的架构示意图：

整个逻辑架构主要分为3层：

第一层是客户端层，改成并不仅限于mysql，也并非mysql所独有，该层主要用于应用程序和数据库交互，是一层中间件，负责应用程序与数据库之间连接处理，登录安全验证等操作的代理人。剩下的第二层和第三层则是mysql的主要组成部分。第二层是核心服务层，也叫做SQL 层，是mysql的核心部分，所有跨存储引擎的功能均在这一层实现，也就是读写数据之前的所有操作，都在这一层操作，包括权限判断验证，SQL解析，优化并生成执行计划，索引选择，查询缓存处理，内置函数实现，存储过程，触发器，视图等。第三层是存储引擎层，也叫数据层，负责数据的存储与读取，采用了插件式的架构模式，支持多种存储引擎，例如InnoDB，MyISAM，MEMORY，每种存储引擎提供了不同的特性，不同场景下各有优劣，使用方可以按需选择，上层通过统一的存储引擎接口来跟不同的存储引擎交互，存储引擎接口隐藏了不同存储引擎之间的差异，对调用层来说尽可能透明，类似于java里的继承与多态，所以也许以后会有越来越多不同类型的存储引擎集成进来，只要数据库产商根据mysql提供的统一协议标准进行开发。对于如何选择合适存储引擎，在以后的文章中会有所讨论，大家敬请关注。

从上图可看出，不管哪种存储引擎都共用了同一个server层，而server层又由多个组件所组成，每个组件都有着自己重要的职责，缺一不可，加下来我们结合开头执行的那条SELECT语句，走一遍整个执行流程，并分析每个组件的作用。

1.连接器

我们要使用mysql做任何事之前是不是要先跟mysql建立连接，所以我们第一步要先发送请求连接命令给mysql，此时负责接收并处理命令的就是连接器了，负责跟客户端建立连接，获取并判断登录权限，维持并管理连接，连接超时等工作，我们在装有mysql客户端的环境里，发送以下命令，请求建立连接：

mysql -h$ip -P$port -u$user -p

注意第一个p为大写，输入端口号，第二个p为小写，代表密码，建议输入第二个p后直接回车再输入密码，如果直接在p后面直接输入密码，可能会导致密码泄露，例如通过history命令可看到历史命令，如果是在生产库执行，会有安全风险。

客户端工具与mysql服务端完成TCP三次握手连接后，连接器接受到连接指令后，通过输入用户名和密码验证登录身份。

如果用户名或者密码错误，则客户端会受到一条“Access denise for user xxx”的错误提示，然后客户端程序结束。如果登录验证通过，连接器则会去系统数据库的用户权限表里面查出当前用户所拥有的权限，一般数据库安装完都会有个默认的名为mysql的库，里面的tables_priv表就保存了系统用户的权限信息，之后这个连接里面的权限判断逻辑，都将依赖于此时读到的权限，所以当一个用户成功建立连接后，后面对该用户的权限进行了修改，也不会影响已经存在连接的权限，只有重新建立连接，最新的权限才会生效，所以，如果用了代码里面用了连接池，修改权限之后，要重启一下应用使新的权限生效或者等过期重连。

连接完成后，如果没有后续的请求操作，例如执行任何sql语句请求，该链接的状态sleep,可通过show processlist查看当前所有执行中线程，如果有其他查询语句，状态为query，该命令也可以用来查找慢查询语句。

mysql> show processlist;+----+------+-----------------+-------+---------+------+----------+------------------+| Id | User | Host            | db    | Command | Time | State    | Info             |+----+------+-----------------+-------+---------+------+----------+------------------+|  3 | root | localhost:53609 | mbuy  | Query   |    0 | starting | show processlist ||  4 | root | localhost:53616 | mysql | Sleep   |  705 |          | NULL             ||  6 | root | localhost:53207 | mysql | Sleep   |  705 |          | NULL             |+----+------+-----------------+-------+---------+------+----------+------------------+3 rows in set (0.03 sec)

如果客户端连接后长时间没有其他操作，超过超时时间，连接器就会自动断开、这个时间由数据库配置参数wait_timeout控制的，默认为8小时，因为建议连接的过程比较复杂，频繁建立与断开会对程序有性能影响问题，所以我们在开发中一般是用连接池方式，所以使用连接池这个地方就要特别注意了，连接池配置信息中的连接的超时断开时间配置不能和该值互斥，否则可能会导致连接池中保存被连接器断开的链接，而拿到无效的连接从而导致程序异常，例如在用druid连接池，踩过这个坑，特别一些冷系统，操作不频繁的内部应用,会收到communication failure或者Lost connection的错误信息。大量长连接也有弊端，那就是会带来内存占用问题，导致00M，解决方法就是定时断开连接重连或者通过执行mysql_reset_connect来重新初始化连接资源（5.7后支持），特别是执行过较大操作的连接执行之后。

2、查询缓存（query cache）

经过连接器成功建立好通信连接并获取到所拥有权限后，此时就可以执行我们开头那条select语句了，执行逻辑分成两种情况：

判断是否启用了查询缓存，如果是则先查缓存看看是否存在以这条sql语句唯一hash为key的值，如果命中，则取出值，并直接返回给客户端。如果没有启用查询缓存或者找到改sql的缓存结果，则会往另一个分支流程进行执行处理，这里可以看到如果能命中缓存的话，可以省了后面很多的执行操作，查询效率非常高，但是大多数情况下，不太建议使用查询缓存，因为查询缓存非常容易失效，只有对应的表有更新操作，这个表所有的查询缓存就都会失效，对于一张更新热表，这个缓存的意义就不大了，因为命中率非常低，除了是极少更新的静态表，例如固定的系统配置，这样的表查询就比较适合，一般核心业务库，数据更新频繁的，建议设置默认不走查询缓存，设置参数为query_cache_type设置成DEMAND，当你确定要使用查询缓存的语句，可以用SQL_CACHE显示指定，如下：

mysql> select SQL_CACHE * from t_user where id_ = 1;

+-----+-------+

| id_ | name_ |

+-----+-------+

| 1 | test |

+-----+-------+

1 row in set (0.03 sec)

不过听说mysql8已经将这个功能彻底删除，可执行验证，目前公司用的最新还是5,7版本。

3、分析器

当没有命中查询缓存，SQL会进入分析器，分析器会先对SQL进行词法分析，以空格为分隔符将SQL语句分隔成多个字符串，并识别出其中的关键字SELECT，判定这是一条查询语句，并识别出表名为t_user，where后面的id_为列，识别完成后就进来语法分析，根据词法分析的结果，语法解析器根据语法树规则，判断该条SQL是否满足mysql语法，如果语法错误，将会返回错误SQL的提示，并结束查询，例如select拼写错误，或者表名写错，或者用了不存在的字段名等。

4、优化器

当经过分析器解析无误执行之前，需要经过优化器的处理，优化器会将解析树转换成执行计划，一条查询可以有多种执行方案，最后返回的结果相同，但是执行的效率却完全不同，mysql的优化器则是使用成本预测的方式，选择执行成本最低的方案为最终的执行计划， 成本低并不意味着执行时间最短，所以有可能选择到错误的执行计划，主要原因在于预测所使用的统计信息发生错误上，数据页个数，索引长度，索引分布情况等，由于篇幅原因，优化器对索引的选择将放在后面文章单独分析讨论，这里列举几个优化的类型：

将外连接转化成内连接使用等价变换规则，例如（5=5 AND a > 5）将被改成a > 5等值传播等

还有很多的优化规则，这里就不一一列出，想更加详细了解，可以参考《高性能MYSQL第三版》查询性能优化章节内容。

生成执行计划的过程会消化较多时间，特别遇到复杂的查询语句是，会产生较多的执行计划，所以当一个SQL语句最终的执行计划确定之后，将其缓存起来，后面遇到类似的语句，则直接使用该执行计划，跳过执行计划生成和选择过程，提高语句执行速度。

5、执行器

优化器阶段完成后，查询语句的执行计划就确定下来了，进入执行器阶段开始执行语句，开始执行前，需要先判断当前用户是否有对该表有相应的操作权限，权限信息则是在连接器阶段获取到的那些，如果没有，则会返回没有权限的错误（验证实现是在命中缓存返回结果之前或者在优化器调用之前进行precheck操作），如果有权限，则打开表继续持续操作。

6、存储引擎和文件系统

最后执行器通过调用存储引擎API操作表定义指定存储引擎读取数据文件系统的数据，并返回找到的数据结果，这一块涉及到mysql存储引擎体系结构部分，会放在后续讲日志文件的文章单独讨论，此处就不再详细展开了