关于java:面试官MySQL-是如何执行一条查询语句的

对于一个开发工程师来说，理解一下 MySQL 是如何执行一条查问语句的，我想是十分有必要的。

首先咱们要理解一下 MYSQL 的体系架构是什么样子的？而后再来聊聊一条查问语句的执行流程是如何？

MYSQL 体系结构

先看一张架构图，如下：

模块详解

1. Connector：用来反对各种语言和 SQL 的交互，比方 PHP，Python，Java 的 JDBC；
2. Management Serveices & Utilities：系统管理和管制工具，包含备份复原、MySQL 复制、集群等；
3. Connection Pool：连接池，治理须要缓冲的资源，包含用户明码权限线程等等；
4. SQL Interface：用来接管用户的 SQL 命令，返回用户须要的查问后果；
5. Parser：用来解析 SQL 语句；
6. Optimizer：查问优化器；
7. Cache and Buffer：查问缓存，除了行记录的缓存之外，还有表缓存，Key 缓存，权限缓存等等；
8. Pluggable Storage Engines：插件式存储引擎，它提供 API 给服务层应用，跟具体的文件打交道。

架构分层

把 MySQL 分成三层，跟客户端对接的连贯层，真正执行操作的服务层，和跟硬件打交道的存储引擎层。

连贯层

咱们的客户端要连贯到 MySQL 服务器 3306 端口，必须要跟服务端建设连贯，那么治理所有的连贯，验证客户端的身份和权限，这些性能就在连贯层实现。

服务层

连贯层会把 SQL 语句交给服务层，这外面又蕴含一系列的流程：

比方查问缓存的判断、依据 SQL 调用相应的接口，对咱们的 SQL 语句进行词法和语法的解析（比方关键字怎么辨认，别名怎么辨认，语法有没有谬误等等）。

而后就是优化器，MySQL 底层会依据肯定的规定对咱们的 SQL 语句进行优化，最初再交给执行器去执行。

存储引擎

存储引擎就是咱们的数据真正寄存的中央，在 MySQL 外面反对不同的存储引擎。再往下就是内存或者磁盘。

SQL 的执行流程

以一条查问语句为例，咱们来看下 MySQL 的工作流程是什么样的。

select name from user where id=1 and age>20; 

首先咱们先来看一张图，接下来的过程都是基于这张图来讲的：

连贯

程序或者工具要操作数据库，第一步要跟数据库建设连贯。

在数据库中有两种连贯:

• 短连贯：短连贯就是操作结束当前，马上 close 掉。
• 长连贯：长连贯能够放弃关上，缩小服务端创立和开释连贯的耗费，前面的程序拜访的时候还能够应用这个连贯。

建设连贯是比拟麻烦的，首先要发送申请，发送了申请要去验证账号密码，验证完了要去看你所领有的权限，所以在应用过程中，尽量应用长连贯。

放弃长连贯会耗费内存。长时间不流动的连贯，MySQL 服务器会断开。能够应用 sql 语句查看默认工夫：

show global variables like 'wait_timeout';

这个工夫是由 wait_timeout 来管制的，默认都是 28800 秒，8 小时。

查问缓存

MySQL 外部自带了一个缓存模块。执行雷同的查问之后咱们发现缓存没有失效，为什么？MySQL 的缓存默认是敞开的。

show variables like 'query_cache%';

默认敞开的意思就是不举荐应用，为什么 MySQL 不举荐应用它自带的缓存呢？

次要是因为 MySQL 自带的缓存的利用场景无限：

第一个是它要求 SQL 语句必须截然不同，两头多一个空格，字母大小写不同都被认为是不同的的 SQL。

第二个是表外面任何一条数据发生变化的时候，这张表所有缓存都会生效，所以对于有大量数据更新的利用，也不适宜。

所以缓存还是交给 ORM 框架（比方 MyBatis 默认开启了一级缓存），或者独立的缓存服务，比方 Redis 来解决更适合。

在 MySQL 8.0 中，查问缓存曾经被移除了。

语法解析和预处理

为什么一条 SQL 语句可能被辨认呢？如果轻易执行一个字符串 hello，服务器报了一个 1064 的错：

[Err] 1064 - 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 'hello' at line 1

这个就是 MySQL 的解析器和预处理模块。

这一步次要做的事件是对语句基于 SQL 语法进行词法和语法分析和语义的解析。

词法解析

词法剖析就是把一个残缺的 SQL 语句打碎成一个个的单词。

比方一个简略的 SQL 语句：select name from user where id = 1 and age >20;

它会将 select 辨认进去，这是一个查问语句，接下来会将 user 也辨认进去，你是想要在这个表中做查问，而后将 where 前面的条件也辨认进去，原来我须要去查找这些内容。

语法分析

语法分析会对 SQL 做一些语法查看，比方单引号有没有闭合，而后依据 MySQL 定义的语法规定，依据 SQL 语句生成一个数据结构。这个数据结构咱们把它叫做解析树（select_lex）。

就比方英语外面的语法“我用 is，你用 are”这种，如果不对必定是不能够的，语法分析之后发现你的 SQL 语句不合乎规定，就会收到 You hava an error in your SQL syntax 的谬误提醒。

预处理器

如果写了一个词法和语法都正确的 SQL，然而表名或者字段不存在，会在哪里报错？是在数据库的执行层还是解析器？比方：
select * from hello;

还是在解析的时候报错，解析 SQL 的环节外面有个预处理器。它会查看生成的解析树，解决解析器无奈解析的语义。比方，它会检查表和列名是否存在，查看名字和别名，保障没有歧义。预处理之后失去一个新的解析树。

查问优化器

一条 SQL 语句是不是只有一种执行形式？或者说数据库最终执行的 SQL 是不是就是咱们发送的 SQL？

这个答案是否定的。一条 SQL 语句是能够有很多种执行形式的，最终返回雷同的后果，他们是等价的。然而如果有这么多种执行形式，这些执行形式怎么失去的？最终抉择哪一种去执行？依据什么判断规范去抉择？

这个就是 MySQL 的查问优化器的模块（Optimizer）。查问优化器的目标就是依据解析树生成不同的执行打算（Execution Plan），而后选 择一种最优的执行打算，MySQL 外面应用的是基于开销（cost）的优化器，那种执行打算开销最小，就用哪种。

能够应用这个命令查看查问的开销：

show status like 'Last_query_cost';

MySQL 的优化器能解决哪些优化类型呢？

举两个简略的例子：

1、当咱们对多张表进行关联查问的时候，以哪个表的数据作为基准表。

2、有多个索引能够应用的时候，抉择哪个索引。

实际上，对于每一种数据库来说，优化器的模块都是必不可少的，他们通过简单的算法实现尽可能优化查问效率的指标。然而优化器也不是万能的，并不是再垃圾的 SQL 语句都能主动优化，也不是每次都能抉择到最优的执行打算，大家在编写 SQL 语句的时候还是要留神。

执行打算

优化器最终会把解析树变成一个执行打算（execution_plans），执行打算是一个数据结构。当然，这个执行打算不肯定是最优的执行打算，因为 MySQL 也有可能笼罩不到所有的执行打算。

咱们怎么查看 MySQL 的执行打算呢？比方多张表关联查问，先查问哪张表？在执行查问的时候可能用到哪些索引，实际上用到了什么索引？

MySQL 提供了一个执行打算的工具。咱们在 SQL 语句后面加上 EXPLAIN，就能够看到执行打算的信息。

EXPLAIN select name from user where id=1;

存储引擎

在介绍存储引擎先来问两个问题：

1、从逻辑的角度来说，咱们的数据是放在哪里的，或者说放在一个什么构造外面？

2、执行打算在哪里执行？是谁去执行？

存储引擎根本介绍

在关系型数据库外面，数据是放在表 Table 外面的。咱们能够把这个表了解成 Excel 电子表格的模式。所以咱们的表在存储数据的同时，还要组织数据的存储构造，这个存储构造就是由咱们的存储引擎决定的，所以咱们也能够把存储引擎叫做表类型。

在 MySQL 外面，反对多种存储引擎，他们是能够替换的，所以叫做插件式的存储引擎。为什么要反对这么多存储引擎呢？一种还不够用吗？

在 MySQL 外面，每一张表都能够指定它的存储引擎，而不是一个数据库只能应用一个存储引擎。存储引擎的应用是以表为单位的。而且，创立表之后还能够批改存储引擎。

如何抉择存储引擎？

• 如果对数据一致性要求比拟高，须要事务反对，能够抉择 InnoDB。
• 如果数据查问多更新少，对查问性能要求比拟高，能够抉择 MyISAM。
• 如果须要一个用于查问的长期表，能够抉择 Memory。
• 如果所有的存储引擎都不能满足你的需要，并且技术能力足够，能够依据官网外部手册用 C 语言开发一个存储引擎。（https://dev.mysql.com/doc/int…）

执行引擎

谁应用执行打算去操作存储引擎呢？这就是执行引擎（执行器），它利用存储引擎提供的相应的 API 来实现操作。

为什么咱们批改了表的存储引擎，操作形式不须要做任何扭转？因为不同性能的存储引擎实现的 API 是雷同的。

最初把数据返回给客户端，即便没有后果也要返回。

栗子

还是以下面的 sql 语句为例，再来梳理一下整个 sql 执行流程。

select name from user where id = 1 and age >20;

1. 通过连接器查问以后执行者的角色是否有权限，进行查问。如果有的话，就持续往下走，如果没有的话，就会被回绝掉，同时报出 Access denied for user 的错误信息；
2. 接下来就是去查问缓存，首先看缓存外面有没有，如果有呢，那就没有必要向下走，间接返回给客户端后果就能够了；如果缓存中没有的话，那就去执行语法解析器和预处理模块。（MySQL 8.0 版本间接将查问缓存的整块性能都给删掉了）
3. 语法解析器和预处理次要是剖析 sql 语句的词法和语法是否正确，没啥问题就会进行下一步，来到查问优化器；

4. 查问优化器就会对 sql 语句进行一些优化，看哪种形式是最节俭开销，就会执行哪种 sql 语句，下面的 sql 有两种优化计划：

   • 先查问表 user 中 id 为 1 的人的姓名，而后再从外面找年龄大于 20 岁的。
   • 先查问表 user 中年龄大于 20 岁的所有人，而后再从外面找 id 为 1 的。
5. 优化器决定抉择哪个计划之后，执行引擎就去执行了。而后返回给客户端后果。

结语

如果文章对你有点帮忙，还是心愿你们看完动动小手指， 点赞、关注和珍藏 。