一、概述

深入学习MySQL，从概览MySQL逻辑架构开始。

首先来看一下MySQL的逻辑架构图：

MySQL最重要、最与众不同的特性就是它的存储引擎架构，这种架构将：查询处理、其他系统任务、数据的存储与提取 三部分分离。所以，带来的好处就是可以在使用时根据性能、特性，以及其他需求来选择数据存储方式。

存储引擎架构分为三层，自上而下，分为第一层：连接层；第二层：服务层；第三层：引擎层。

第一层：连接层：

MySQL的最上层是连接服务，引入了线程池的概念，允许多台客户端连接。主要工作是：连接处理、授权认证、安全防护等。

连接层为通过安全认证的接入用户提供线程，同样，在该层上可以实现基于SSL 的安全连接。

第二层：服务层：

服务层用于处理核心服务，如标准的SQL接口、查询解析、SQL优化和统计、全局的和引擎依赖的缓存与缓冲器等等。所有的与存储引擎无关的工作，如过程、函数等，都会在这一层来处理。在该层上，服务器会解析查询并创建相应的内部解析树，并对其完成优化，如确定查询表的顺序，是否利用索引等，最后生成相关的执行操作。如果是SELECT 语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

第三层：**引擎层**：

存储引擎层，存储引擎负责实际的MySQL数据的存储与提取，服务器通过API 与 存储引擎进行通信。不同的存储引擎功能和特性有所不同，这样可以根据实际需要有针对性的使用不同的存储引擎。

二、连接器

当客户端（应用）连接到MySQL服务器时，服务器需要对其进行认证。认证基于用户名、原始主机信息和密码。

除了基本认证之外，连接器还会进行一些线程的处理。

每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个CPU核心或者CPU中运行。服务器会负责缓存线程，因此不需要为每一个新建的连接创建或者销毁线程。

连接器：负责跟客户端建立连接 | 获取权限 | 维持和管理连接。连接命令一般如下形式:

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

通过如下语句可以查看当前连接的信息:

 mysql> show processlist;
 +----+------+--------------------+-------+---------+------+----------+------------------+
 | Id | User | Host               | db    | Command | Time | State    | Info             |
 +----+------+--------------------+-------+---------+------+----------+------------------+
 |  5 | root | 192.168.33.1:57721 | mysql | Sleep   |   29 |          | NULL             |
 |  6 | root | localhost          | NULL  | Query   |    0 | starting | show processlist |
 +----+------+--------------------+-------+---------+------+----------+------------------+
rows in set (0.00 sec)

其中"Sleep"代表现在系统里面有一个空闲连接.

客户端如果太长时间不活动, 连接器就会自动断开. 这个是由等待超时时间wait_timeout控制的,默认值是8小时.

数据库长连接: 指连接成功后, 如果客户端持续有请求, 则一直使用同一个连接.

数据库短连接: 指每次执行完很少的几次查询后就断开连接, 下次查询重新再建立一个.

两种连接实践方案:

1. 定期断开长连接. 使用一段时间, 或者程序里面判断执行过一个占用内存的大查询后, 断开连接, 之后要查询再重连.

2. 如果使用的MySQL版本是5.7或更新版本, 可以在执行一个比较大的操作后, 通过执行mysql_reset_connection来重新初始化连接资源. 这个过程不需要重连和重新做权限验证, 但是会将连接恢复到刚刚创建完时的状态。

三、查询缓存

对于SELECT语句，在解析查询之前，服务器会先检查查询缓存（Query Cache），如果能够在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。

但不推荐使用查询缓存**，即大多数情况下不建议使用查询缓存。为什么呢？**

原因：查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。

好在MySQL也提供了这种“按需使用”的方式。可以将参数query_cache_type设置成DEMAND，这样对于默认的SQL语句都不使用查询缓存。而对于确定要使用查询缓存的语句，可以用SQL_CACHE显式指定，如下：

 mysql> select SQL_CACHE * from T where ID=10；

四、解析器

• 词法分析

• 语法分析

如果缓存没有命中的话，MySQL会对查询语句进行解析。简单说解析的作用将我们人能看懂的SQL解析成MySQ能识别的语言。

解析器先会做“词法解析”。输入的是由多个字符串和空格组成的一条SQL语句，MySQL需要识别出里面的字符串分别是什么，代表什么。

MySQL从输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名T”，把字符串“ID”识别成“列ID”。

做完了这些识别以后，就要做“语法解析”。根据词法解析的结果，语法解析器会根据语法规则，判断输入的这个SQL语句是否满足MySQL语法。

五、优化器

• 选择合适的索引

• 决定各个表的连接顺序

经过了解析器，MySQL知道我们要干什么。

接下来并不是直接执行，而是会在优化器这一层进行优化，优化器是个非常复杂的部件，它会帮我们去使用他自己认为的最好的方式去优化这条 SQL 语句，并生成一条条的执行计划。

例如在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。比如你执行下面这样的语句，这个语句是执行两个表的join：

 mysql> select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;

• 既可以先从表t1里面取出c=10的记录的ID值，再根据ID值关联到表t2，再判断t2里面d的值是否等于20。

• 也可以先从表t2里面取出d=20的记录的ID值，再根据ID值关联到t1，再判断t1里面c的值是否等于10。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。

优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。如果你还有一些疑问，比如优化器是怎么选择索引的，有没有可能选择错等等，没关系，我会在后面的文章中单独展开说明优化器的内容。

六、执行器

1. 判断对这个表有没有查询权限

2. 有权限, 则继续执行.

3. 调用存储引擎接口进行执行查询或其他操作.

4. 最终将查询结果集返回给客户端, 语句即执行完成.

MySQL通过解析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，执行器会根据一系列的执行计划去调用存储引擎的接口去完成SQL的执行。

开始执行的时候，要先判断一下你对这个表T有没有执行查询的权限，如果没有，就会返回没有权限的错误，如下所示(在工程实现上，如果命中查询缓存，会在查询缓存放回结果的时候，做权限验证。查询也会在优化器之前调用precheck验证权限)。

 mysql> select * from T where ID=10;
 ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

比如我们这个例子中的表T中，ID字段没有索引，那么执行器的执行流程是这样的：

1. 调用InnoDB引擎接口取这个表的第一行，判断ID值是不是10，如果不是则跳过，如果是则将这行存在结果集中；

2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。

3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

至此，这个语句就执行完成了。

对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。

七、问题1：MySQL的工作流程

最上层：客户端连接

1、连接处理：客户端同数据库服务层建立TCP连接，连接管理模块会建立连接，并请求一个连接线程。如果连接池中有空闲的连接线程，则分配给这个连接，如果没有，在没有超过最大连接数的情况下，创建新的连接线程负责这个客户端。

２、授权认证：在真正的操作之前，还需要调用用户模块进行授权检查，来验证用户是否有权限。通过后，方才提供服务，连接线程开始接收并处理来自客户端的SQL语句。

第二层：核心服务

1、连接线程接收到SQL语句之后，将语句交给SQL语句解析模块进行语法分析和语义分析。

2、如果是一个查询语句，则可以先看查询缓存中是否有结果，如果有结果可以直接返回给客户端。

3、如果查询缓存中没有结果，就需要真的查询数据库引擎层了，于是发给SQL优化器，进行查询的优化。如果是表变更，则分别交给insert、update、delete、create、alter处理模块进行处理。

第三层：数据库引擎层

1、打开表，如果需要的话获取相应的锁。 　　 2、先查询缓存页中有没有相应的数据，如果有则可以直接返回，如果没有就要从磁盘上去读取。

3、当在磁盘中找到相应的数据之后，则会加载到缓存中来，从而使得后面的查询更加高效，由于内存有限，多采用变通的LRU表来管理缓存页，保证缓存的都是经常访问的数据。

最后，获取数据后返回给客户端，关闭连接，释放连接线程。

八、问题2

如果表 T 中没有字段 k，而你执行了这个语句 select * from T where k=1, 那肯定是会报“不存在这个列”的错误：“Unknown column ‘k’ in ‘where clause’”。你觉得这个错误是在哪个阶段报出来的呢？

答案：分析器.

MySQL会解析查询, 并创建内部数据结构(解析树). 分析器会检查: 数据表和数据列是否存在, 别名是否有歧义等. 所以此为分析器阶段. ----- 参考自<MySQL实战45讲> 01节课后讨论@圈圈圆圆的回答.

参考资料

[1] MYSQL提升笔记（1）：MySQL逻辑架构

[2] MySQL 高级-MySQL逻辑架构图简介

[3] <MySQL实战45讲>

快来关注我的微信公众号吧：正午Coder