MySQL中的SQL执行流程

SQL执行流程

MySQL的配置文件的位置

/etc/my.cnf

MySQL中的SQL执行流程

MySQL的查询流程

1. 查询缓存：Server如果在查询缓存中发现了这条SQL语句，就会直接将结果返回给客户端；如果没有，如果进入到解析器阶段。需要说明的是，因为查询混窜往往效率不高，所以在MySQL8.0之后就抛弃了这个功能

MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。key是查询的语句，value是查询的结果。如果你的查询能够直接在这个缓存中找到key，那么这个value就会被直接返回给客户端。如果语句不在查询缓存中，就会继续后面的执行阶段。执行完成后，执行结果会被存入查询缓存中。所以，如果查询命中缓存，MySQL不需要执行后面的复杂操作，就可以直接返回结果，这个效率会很高。

大多数情况查询缓存就是个鸡肋，为什么呢

查询缓存是提前把查询结果缓存起来，这样下次不需要执行就可以直接拿到结果。需要说明的是，在MySQL中的查询缓存，不是缓存查询计划，而是查询对应的结果。这就意味着查询匹配的鲁棒性大大降低，只有相同的查询操作才会命中查询缓存。两个查询请求在任何字符上的不同（例如:空格、注释、大小写)，都会导致缓存不会命中。因此 MySQL的查询缓存命中率不高。

同时，如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，如mysql、 information_schema、performance_schema数据库中的表，那这个请求就不会被缓存。以某些系统函数举例，可能同样的函数的两次调用会产生不一样的结果，比如函数NOW，每次调用都会产生最新的当前时间，如果在一个查询请求中调用了这个函数，那即使查询请求的文本信息都一样，那不同时间的两次查询也应该得到不同的结果，如果在第一次查询时就缓存了，那第二次查询的时候直接使用第一次查询的结果就是错误的!

此外，既然是缓存，那就有它缓存失效的时候。MySQL的缓存系统会监测涉及到的每张表，只要该表的结构或者数据被修改，如对该表使用了INSERT、UPDATE、DELETE、TRUNCATE TABLE、ALTER TABLE、DROPT ABLE或 DROP DATABASE语句，那使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除!对于更新压力大的数据库来说，查询缓存的命中率会非常低。

总之，因为查询缓存往往弊大于利，查询缓存的失效非常频繁。

一般建议大家在静态表里使用查询缓存，什么叫静态表呢就是一般我们极少更新的表。比如，一个系统配置表、字典表，这张表上的查询才适合使用查询缓存。好在MySQL也提供了这种“按需使用”的方式。你可以将my.cnf参数query_cache_type设置成DEMAND，代表当sql语句中有SQL_CACHE关键词时才缓存。比如:

#query_cache_type有3个值0代表关闭查询缓存0FF，1代表开启ON，2(DEMAND)
query_cache_type=2

这样对于默认的SQL语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用SQL_CACHE显式指定，像下面这个语句一样:

select sQL_CACHE * from test where ID=5;

如果SQL语句正确，则会生成这样一颗语法树

下面是SQL词法分析过程

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-n9cq32Lc-1647062843063)(https://cdn.jsdelivr.net/gh/losserlong/pictures/20220312132649.png)]

都有哪些维度可以进行数据库调优简言之:。

• 索引失效，没有充分利用到索引——索引建立

• 关联查询太多JOIN(设计缺陷或不得已的需求)——SQL优化

• 服务器调优及各个参数设置（缓冲、线程数等）——调整my.cnf

• 数据过多――分库分表

比如：表test中，ID字段没有索引，那么执行流程是这样的：

调用InnoDB引擎接口取这个表的第一行，判断ID值是不是1，如果不是则跳过，如果是则将这行存在结果集中;调用引擎接口取"下一行"，重复相同的判断逻辑，直到取到这个表的最后一行。

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

至此，这个语句就执行完成了。对于有索引的表，执行的逻辑也差不多。

SQL语句在MySQL中的执行流程是：SQL语句->查询缓存->解析器->优化器->执行器

MySQL8中的SQL执行原理

前面的结构图很复杂，我们需要抓取最核心的部分: SQL的执行原理。不同的DBMS的SQL的执行原理是相通的，只是在不同的软件中，各有各的实现路径。

既然一条SQL语句会经历不同的模块，那我们就来看下，在不同的模块中，SQL执行所使用的资源(时间）是怎样的。如何在MysQL中对一条SQL语句的执行时间进行分析。

确认profiling是否开启

了解查询语句底层执行的过程: select @@profiling;或者show variables like '%profiling%'查看是否开启计划。开启它可以让MySQL收集在SQL执行时所使用的资源情况，命令如下:

select  @@profiling;

show variables like '%profiling%';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-S8U9WgPK-1647062843064)(https://cdn.jsdelivr.net/gh/losserlong/pictures/20220312132656.png)]

profiling=0代表关闭，我们需要把profiling打开，即设置为1:

set profiling =1;

Profiling功能由MySQL会话变量：profiling控制。默认是OFF（关闭状态）

show profiles;

多次执行相同的SQL查询&&查看profiles

查看之前的执行的SQL语句

查看profile

查看最近执行的一条SQL语句，也就是第12条

查看指定的一条SQL的执行时间

mysql> show profile for query 10;

1、查看cpu还有io的相关开销

mysql> show profile cpu , block io for query 6;

在MySQL5.7中SQL执行原理

配置文件中开启查询缓存

vim /etc/my.cnf
在里面加上
query_cache_type=1

重启mysql服务

systemctl restart mysqld

开启查询执行计划

set profiling =1;

执行语句两次

 use atguigudb;
select * from employees limit 4;
select * from employees limit 4;

查看profiles

查看profile

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eWlJdf3Y-1647062843066)(https://cdn.jsdelivr.net/gh/losserlong/pictures/20220312132712.png)]

1、除了查看cpu、io阻塞等参数情况，还可以查询下列相关参数的利用情况

Syntax:
SHow PROFILE [type [, type] ... ]
[ FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type: {
ALL 	--显示所有参数的开销信息
|BLOCK I0 --显示I0的相关开销
|CONTEXT SWITCHES -- 上下文切换相关开销
|CPU --显示CPU相关开销信息
|IPC --显示发送和接收相关开销信息
|MEMORY --显示内存相关开销信息
|PAGE FAULTS --显示页面错误精关开销信息
|SOURCE --显示和Source_function, Source_file , Source_line 相关的开销信息
|SWAPS --显示交换次数相关的开销信息

2、发现两次查询当前情况都一致，说明没有缓存之说。

在8.0 版本之后，MySQL不再支持缓存的查询。一旦数据表有更新，缓存都将清空，因此只有数据表是静态的时候，或者数据表很少发生变化时，使用缓存查询才有价值，否则如果数据表经常更新，反而增加了SQL的查询时间。

注意在8.0中是不存在查询缓存的

SQL语法顺序

随着MySQL版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。

需求：查询每个部门年龄高于20岁的人数且高于20岁人数不能少于2人，显示人数最多的第一名部门信息
下面是经常出现的查询顺序：

手写：

SELECT DISTINCT 
<select_list>
FROM
<left_table> <join_type>
JOIN <right_table> ON <join_condition>
WHERE
<where_condition>
GROUP BY
<group_by_list>
HAVING
<having_condition>
ORDER BY
<order_by_condition>
LIMIT 
<limit_number>

机读：

FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>