一条SQL查询语句是如何执行的

---

一.执行流程：

1.客户端

连接工具（Navacat、SQLyog、JDBC）都归纳为MYSQL客户端(Client)，主要用于发送执行sql语句的请求

2.服务端（Server）

主要包括连接器、缓存查询、分析器、优化器、执行引擎等，涵盖mysql大部分功能以及所有内置函数（日期、数学和加密函数等），所有跨引擎的功能都在这一层实现，例如视图、存储过程、触发器等

执行过程：

1）连接器：

执行SQL语句就一定要连接，而连接动作就是通过连接器来执行的

连接语句为：mysql -uroot -P3306 -p(password)

I.查询权限：

连接后连接器会在权限表中查找这个用户的权限，这个用户的所有操作都是依赖于此时查找到的权限的
任何对权限表的操作都不能影响到此次连接的权限，重新连接后才会刷新权限

II.连接状态：

连接完成后没有后续动作操作就会进入睡眠状态（Sleep），长时间不进行就会断开连接，这个时间是由参数 wait_timeout 控制的，默认值 是 8 小时
如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒：Lost connection to MySQL server during query。

III.长连接和短连接：

长连接：

连接成功后，如果客户端持续有请求，则一直使用同一个连接。

短连接：

每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

Tip：
建立连接的过程通常是比较复杂的，建议在使用中要尽量减少建立连接的动作，尽量使用长连接。

问题：

全部使用长连接后，有时候 MySQL 占用内存涨得特别快，因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的，这些资源会在连接断开的时候才释放，所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM）。
从现象看就是 MySQL 异常重启了

解决方案：

i.定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
ii.MySQL 5.7 以上版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。
这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

2）查询缓存

在MySQL5.7版本，连接完成后就会直接查询缓存，查询此语句是否执行过

I.缓存：

缓存中会有以键值对（Key-Value）存储的查询结果，Key为CELECT语句，Value为结果

II.查询：

如果在缓存中能找到Key——即CELECT语句，就会直接返回Value——即结果
如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。
如果查询命中缓存，MySQL 不需要执行后面的复杂操作，就可以直接返回结果，会提升效率。

Tip：
在MySQL8.0中查询缓存被删除了
查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。

问题：

对于更新压力大的数据库来说，查询缓存的命中率会非常低。
如果业务中需要有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。

解决方法：

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

mysql> select SQL_CACHE * from user_info where id = 1;

3）分析器

若查询缓存未命中，则会执行分析器，来分析查询语句是否合法

词法分析：

主要负责从 SQL 语句中提取关键字，比如：查询的表，字段名，查询条件等等。
词法分析阶段是从 information schema 里面获得表的结构信息的。

语法分析：

判断输入的SQL 语句是否满足 MySQL 语法
如果 SQL 语句不对，就会返回 You have an error in your SQL syntax 的错误提醒，一般语法错误会提示第一个出现错误的位置

4）优化器

若语句正确，就会进入优化器
优化器的作用是在基于同一个查询语句的多个查询方案中找出效率最高的
如，表里面有多个索引的时候，决定使用哪个索引；在一个语句有多表关联（join）的时候，决定各个表的连接顺序

5）执行引擎

优化器确定查询方案后就会交给执行引擎执行操作
开始执行的时候，要先判断一下你对这个表有没有执行查询的权限，如果没有，就会返回没有权限的错误
如果有权限，就打开表继续执行。

打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口：
I.调用引擎接口取这个表的第一行，判断是否满足条件，如果不是则跳过，如果是则将这行存在结果集中
II.调用引擎接口取下一行，重复相同的判断逻辑，直到取到这个表的最后一行
III.执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端

3.存储引擎

主要负责数据的存储和提取，支持InnoDB，MyISAM，Memory等多种存储引擎，其中InnoDB是目前大部分版本的默认存储引擎

二.SELECT语句的执行顺序：

1.书写顺序

SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT…

2.执行顺序

FROM（包括JOIN…ON…） -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT
在 SELECT 语句执行时，每个步骤都会产生一个 虚拟表 ，然后将这个虚拟表传入下一个步骤中作为输入
E.g：

SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5 
FROM player 
JOIN team 
ON player.team_id = team.team_id # 顺序 1 
WHERE height > 1.80 # 顺序 2 
GROUP BY player.team_id # 顺序 3 
HAVING num > 2 # 顺序 4 
ORDER BY num DESC # 顺序 6 
LIMIT 2 # 顺序 7

3.SELECT语句的执行流程：

1）查询数据表的原始数据：

FROM：

多张表联查：

1. 首先先通过 CROSS JOIN 求笛卡尔积，相当于得到虚拟表 vt（virtual table）1-1；
2. 通过 ON 进行筛选，在虚拟表 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2；
3. 添加外部行。如果我们使用的是左连接、右链接或者全连接，就会涉及到外部行，
   也就是在虚拟表 vt1-2 的基础上增加外部行，得到虚拟表 vt1-3。
   如果操作的是两张以上的表，还会重复上面的步骤，直到所有表都被处理完为止。这个过程得到是我们的原始数据（vt1）

2）筛选：

WHERE：

根据 vt1 表的结果进行筛选过滤，得到虚拟表 vt2

3）分组：

GROUP：

在虚拟表 vt2 的基础上进行分组，得到中间的虚拟表 vt3

4）分组过滤：

HAVING：

在虚拟表 vt3 的基础上进行分组过滤，得到中间的虚拟表 vt4

5）提取字段：

SELECT：

提取想要的字段，得到中间的虚拟表vt5-1

DISTINCT：

过滤掉重复的行，得到中间的虚拟表vt5-2
最后得到虚拟表vt5

6）排序：

ORDER BY：

排序 ，得到虚拟表 vt6

7）分页：

LIMIT：

取出指定行的记录，得到最终的结果，虚拟表vt7

---