一条查询SQL是如何执行的？-CSDN博客

本文链接：https://blog.csdn.net/weixin_41716183/article/details/126019937

Mysql 架构与内部模块

一条查询sql是如何执行的

在这里插入图片描述

①建立连接（通信协议）

MySql支持多种通信协议
一：TCP/IP协议
二：Unix Socket，比如我们的Linux服务器，不用通过网络协议，也可以连接到 MySQL 的服务器，它需要用到服务器上的一个物理文件（mysql.sock）
另外还有命名管道（Named Pipes）和内存共享（Share Memory）的方式

通信方式

在这里插入图片描述
mysql 使用半双工的通信方式
半双工意味着要么是客户端向服务端发送数据，要么是服务端向客户端发送数据，这两个动作不能
同时发生。
所以客户端发送 SQL 语句给服务端的时候，（在一次连接里面）数据是不能分成小块发送的，不管
你的 SQL 语句有多大，都是一次性发送
如果发送给服务器的数据包过大，我们必须要调整 MySQL 服务器配置 max_allowed_packet 参数的值
（默认是 4M）
另一方面，对于服务端来说，也是一次性发送所有的数据，不能因为你已经取到了想要的数据就中
断操作。所以，我们一定要在程序里面避免不带 limit 的这种操作。

连接方式

MySQL 既支持短连接，也支持长连接
长时间不活动的连接，MySQL 服务器会断开。
在这里插入图片描述
默认是 28800 秒，8 小时
MySQL 默认的最大连接数是 151 个（5.7 版本），最大是 16384（2^14）

使用 SHOW FULL PROCESSLIST;查看查询的执行状态。
在这里插入图片描述

②查询缓存

MySQL 内部自带了一个缓存模块。默认是关闭的。主要是因为 MySQL 自带的缓存的应用场景有
限，第一个是它要求 SQL 语句必须一模一样。第二个是表里面任何一条数据发生变化的时候，这张表所
有缓存都会失效。
在 MySQL 5.8 中，查询缓存已经被移除了。

③语法解析和预处理(Parser & Preprocessor)

服务器是怎么知道我输入的内容（语法）是错误的？
这个就是 MySQL 的 Parser 解析器和 Preprocessor 预处理模块。
这一步主要做的事情是对 SQL 语句进行词法和语法分析和语义的解析。

词法解析

词法分析就是把一个完整的 SQL 语句打碎成一个个的单词。
比如一个简单的 SQL 语句：
select name from user where id = 1;
它会打碎成 8 个符号，记录每个符号是什么类型，从哪里开始到哪里结束。

语法解析

第二步就是语法分析，语法分析会对 SQL 做一些语法检查，比如单引号有没有闭合，然后根据 MySQL
定义的语法规则，根据 SQL 语句生成一个数据结构。这个数据结构我们把它叫做解析树。
在这里插入图片描述

预处理器（Preprocessor）

如果表名错误，会在预处理器处理时报错。
它会检查生成的解析树，解决解析器无法解析的语义。比如，它会检查表和列名是否存在，检查名字和别名，保证没有歧义。

④查询优化（Query Optimizer）

什么是优化器？
问题：一条 SQL 语句是不是只有一种执行方式？或者说数据库最终执行的 SQL 是不是就是我们发送的 SQL？
这个答案是否定的。一条 SQL 语句是可以有很多种执行方式的。但是如果有这么多种执行方式，这些执行方式怎么得到的？最终选择哪一种去执行？根据什么判断标准去选择？
这个就是 MySQL 的查询优化器的模块（Optimizer）。
查询优化器的目的就是根据解析树生成不同的执行计划，然后选择一种最优的执行计划，MySQL 里面使用的是基于开销（cost）的优化器，那种执行计划开销最小，就用哪种。
使用如下命令查看查询的开销：
show status like ‘Last_query_cost’ – 代表需要随机读取几个 4K 的数据页才能完成查找

优化器是怎么得到执行计划的？

首先我们要启用优化器的追踪（默认是关闭的）：
SHOW VARIABLES LIKE ‘optimizer_trace’;
set optimizer_trace=“enabled=on”;
注意开启这开关是会消耗性能的，因为它要把优化分析的结果写到表里面，所以不要轻易开启，或者查看完之后关闭它（改成 off）。
接着我们执行一个 SQL 语句，优化器会生成执行计划：
select t.tcid from teacher t,teacher_contact tc where t.tcid = tc.tcid;
这个时候优化器分析的过程已经记录到系统表里面了，我们可以查询：
select * from information_schema.optimizer_trace\G
在这里插入图片描述
expanded_query 是优化后的 SQL 语句,considered_execution_plans 里面列出了所有的执行计划。
记得关掉优化器的追踪：
set optimizer_trace=“enabled=off”;
SHOW VARIABLES LIKE ‘optimizer_trace’;

优化器可以做什么？

MySQL 的优化器能处理哪些优化类型呢？
1、当我们对多张表进行关联查询的时候，以哪个表的数据作为基准表。
2、select * from user where a=1 and b=2 and c=3，如果 c=3 的结果有 100 条，b=2 的结果有200 条， a=1 的结果有 300 条，你觉得会先执行哪个过滤？
3、如果条件里面存在一些恒等或者恒不等的等式，是不是可以移除。
4、查询数据，是不是能直接从索引里面取到值。
5、count()、min()、max()，比如是不是能从索引里面直接取到值。

⑤执行计划（优化器得到的结果）

优化器最终会把解析树变成一个查询执行计划，查询执行计划是一个数据结构。
当然，这个执行计划是不是一定是最优的执行计划呢？不一定，因为 MySQL 也有可能覆盖不到所有的执行计划。MySQL 提供了一个执行计划的工具。我们在 SQL 语句前面加上 EXPLAIN，就可以看到执行计划的信息。