当程序执行一条查询语句时，MySQL内部到底发生了什么？ （说一下 MySQL 执行一条查询语句的内部执行过程？

先来个最基本的总结阐述，希望各位小伙伴认真的读一下，哈哈：

1）客户端（运行程序）先通过连接器连接到MySql服务器。

2）连接器通过数据库权限身份验证后，会先查询数据库缓存是否存在（之前执行过相同条件的SQL查询），如果有会直接返回缓存中的数据。如果没有则会进入分析器。

3）进入分析器后会对查询语句进行词法语法的分析，判断该查询语句SQL是否存在语法错误，如果存在查询语法词法错误，会直接返回给客户端错误，如果正确则会进入优化器。

4）优化器会对查询语句进行优化处理：例如：如果一条语句用到了多个索引会判断哪个索引性能更好。

5）最终会进入执行器，开始执行查询语句直到查询出满足条件的所有数据，然后进行返回。

下面我们详细的来说一下：

假如说我们有一张 User 表 ，表里只有一个字段 ID，当我们执行下边这条SQL语句时：

mysql> select * fron T where ID = 10；

在我们眼中能看到的只是输入一条 SQL语句，返回一条查询结果，却不曾知道这条SQL在MySQL的内部经历了什么，下面我们来一步一步的分析一下；如下是MySQL的基本架构图，从图中可以清楚的看到SQL在MySQL中各个功能模块执行的过程：

 

大体来说，MySQL可以分为Server层和存储引擎两部分。

Server层：包括连接器、分析器、查询缓存、优化器、执行器等。

存储引擎：负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多种存储模式。现如今最常用的存储引擎是InnoDB，它从MySQL5.5.5开始成为了默认存储引擎。也就是说当我们不指定存储引擎时默认使用的就是InnoDB，我们也可以在 create table时通过engine=memory 来指定存储引擎。

 

从图中可以看出：不同的存储引擎共用同一个 server层，也就是连接器到执行器那一部分。

 

现在我们跟着开篇提到的那条SQL进入MySQL内部到底是怎么执行的，Let's go:

 

连接器：

首先，我们需要连接上数据库，这时候接待我们的就是连接器，连接器主要负责的工作就是跟客户端建立连接、获取权限、维持和管理连接。连接命令如下：

-- $ip: 服务器IP
-- $port: MySQL端口号
-- $user: 用户名
mysql -h$ip -P$port -u$user -p

• 如果用户名或密码不对，则会收到一个“Access denied for user”错误，然后客户端程序结束执行。
• 如果用户名密码认证通过，连接器则会去权限表中查询该用户所拥有的权限，这个链接里边的权限逻辑判断，全都依赖于此时读到的权限。

这就意味着，当一个用户成功建立连接后，即使使用管理员账户对其权限做了修改，也不会立即生效，只有重新建立链接后才会使用新的权限设置。

 

查询缓存：

连接建立成功后，我们就可以执行SQL语句了，这时候会先来查询缓存：

MySQL拿到一个SQL语句之后会先到缓存看看是否在此之前执行过这条语句，之前执行的语句可能会以 key-value 的形式直接缓存在内存中。key是查询语句，value是查询结果。如果你的查询能在缓存中找到相应的key，则直接返回其对应的value 给客户端。

如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。你可以看到，如果查询命中缓存，MySQL不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。

 

分析器：

如果没有命中缓存的话，则真正开始执行语句了；首先MySQL要知道我们要做什么，所以要先对SQL进行解析。

分析器首先进行词法分析。我们输入的SQL是由多个字符串组成的，MySQL需要识别出来里边的字符串分别是什么，代表什么意思。

MySQL从我们输入的select识别出来这是一个查询语句，它还需要把字符串T识别成表名User，把ID识别成列ID。

词法分析结束之后，需要对SQL进行语法分析。根据词法分析的结果，语法分析器会根据语法规则来判断我们输入的这条SQL是否满足MySQL的语法规则。
如果我们的语法不对的话，我们会受到MySQL的错误提示You have an error in your SQL syntax，比如这条SQL中的select少了个s：

mysql> elect * from user where id = 1;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'elect * from t where ID=1' at line 1

 

优化器

经过了分析器，MySQL知道我们要做什么了，在它开始执行之前，还要先经过优化器的处理。

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（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；

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

 

执行器

MySQL通过分析器知道了我们要做什么，通过优化器知道了应该怎么做，于是就进入执行器阶段，开始真正的执行语句。

开始执行的时候，首先要确认我们是否有操作这个表（T）的权限，如果没有权限则会返回没有权限的错误:

mysql> select * from T where ID=10;

ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

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

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

• 调用InnoDB引擎接口取这个表的第一行，判断ID是否为10，如果不是则跳过，如果是则将这行存在结果集中。
• 调用引擎接口取下一行，重复相同的逻辑判断，直到取到这个表的最后一行。
• 执行器将上述遍历过程的所有满足条件的行组成的记录集作为结果集返回给客户端。

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

对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。你会在数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。

在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。

 

延伸出的面试题问法，各种变态考察：

 

1）MySQL 提示“不存在此列”是执行到哪个节点报出的？

此错误是执行到分析器阶段报出的，因为 MySQL 会在分析器阶段检查 SQL 语句的正确性。

 

2）MySQL 查询缓存的功能有何优缺点？

MySQL 查询缓存功能是在连接器之后发生的，它的优点是效率高，如果已经有缓存则会直接返回结果。 查询缓存的缺点是失效太频繁导致缓存命中率比较低，任何更新表操作都会清空查询缓存，因此导致查询缓存非常容易失效。

 

3）如何关闭 MySQL 的查询缓存功能？

MySQL 查询缓存默认是开启的，配置 querycachetype 参数为 DEMAND（按需使用）关闭查询缓存，MySQL 8.0 之后直接删除了查询缓存的功能。