最新消息: PyCharm vs VSCode,哪个更好?
您现在的位置是:群英 > 开发技术 > PHP语言 >
详解MySQL中一条SQL语句是怎样执行的
ITPUB发表于 2021-01-14 17:40 次浏览
 

本篇文章会分析一个 sql 语句在 MySQL 中的执行流程,包括 sql 的查询在 MySQL 内部会怎么流转,sql 语句的更新是怎么完成的。
 
在分析之前我会先带着你看看 MySQL 的基础架构,知道了 MySQL 由那些组件组成以及这些组件的作用是什么,可以帮助我们理解和解决这些问题。

MySQL 中 SQL 语句的执行过程

一个 MySQL 请求的处理流程图:

从上图可以看出,MySQL 的处理流程主要分为 4 个步骤:客户端与服务端通信、查询优化处理过程、查询执行引擎、返回结果给客户端。

1、客户端与服务端通信

一般通信方式有 3 种:单工,半双工,全双工。单工就是只能单向传输,要么 A 端给 B 端传输,要么 B 端给 A 端传输;半双工是可以双向传输的,但是同一时间只能是一个方向传输,也就是说 A 端给 B 端传输的时候,B 端只能等待,反过来也一样,B 端给 A 端传输的时候,A 端也只能等待;全双工是双向随便传输。

MySQL 客户端与服务器的通信方式是半双工的,也就是说,我们的一个数据库连接在向数据库发送数据的时候,此时这个数据库连接是不能给客户端返回数据的,一定是数据返回完毕以后,客户端才能再次发起查询操作。这也就是我们在做数据查询的时候用 where 条件 和 limit 限制数据结果行数的原因,否则客户端连接需要等到数据库把所有的查询结果返回之后,才能进行下一个操作。

从上面的分析可以看出,MySQL 数据库半双工通信模式的一个重要特点是:客户端一旦开始发送指令,服务端需要接收完毕才能响应,客户端只有在完全接收到服务端响应的数据后,才能再次发送指令。有点像对讲机,这就是为什么电视里看到两个人对讲时,最后要说一句 over 的原因,当听到 over 的时候,另一端的人就可以按对讲键进行说话了。我们在程序开发中,一般会用多个连接进行数据交互,通过数据库连接池来进行管理,因此对这块体会可能不够深刻。

其实 MySQL 的每一个连接都有其对应的状态来标识它目前所处的阶段,和线程类似,我们可以通过下面的命令查看数据库连接的状态:

SHOW [FULL] PROCESSLIST

常用的几个状态描述:

当发现数据库连接长时间占用的时候,可以用 kill 命令杀死线程:

kill processlist_id

2、查询优化处理过程

解析器解析 sql 语句:通过 lex 词法分析器(就是把一个完整的 SQL 语句分析成独立的单词 )、yacc 语法分析器(就是分析是否符合语法规则,比如单引号是否闭合等)进行分析,将 sql 语句按 sql 标准解析成 解析树(select_lex)对象,主要功能是把一个 sql 语句的字符串解析成数据库服务器可以处理的解析树对象,便于后续进行预处理和生成执行计划。

预处理:预处理会根据 mysql 的语法规则对解析树对象进行合法性检查,比如检查表名列名是否存在、检查名字和别名,保证没有歧义,预处理之后得到一个新的解析树。

优化器生成执行计划:优化器的主要作用就是把这个 sql 语句找到最优的执行计划,MySQL 的查询优化器和 Oracle 类似,都是基于成本的计算,优化器会尝试使用不同的执行计划,以便于找到一个最优的执行计划(一般随机读取 4K 的数据库进行分析)。

可以使用以下的命令查看查询的成本:

show status like 'Last_query_cost';

优化器最终会把解析树变成一个查询执行计划。MySQL 提供了一个执行计划的工具,我们在 SQL 语句前面加上 EXPLAIN,就可以看到执行计划的信息。

我们在做 sql 调优的时候主要也就是对这部分进行处理,在《掌握 SQL 语句调优中 explain 的用法》中会详细介绍。

3、查询执行引擎

查询执行模块,也就是查询执行引擎,根据优化器生成的最优执行计划调用对应存储引擎的 API 的进行执行计划的执行,并获取查询应该返回的结果集。

4、返回结果给客户端

如果没有开启缓存,把查询到的结果集返回到客户端;如果开启了缓存,执行缓存操作,把结果集存入缓存,然后把结果返回给客户端,即使结果集是空的,也要返回。

MySQL 的缓存介绍

一般情况下,我们不会用到数据库自带的缓存,所以 MySQL 默认是不开启缓存的。只有以读为主的业务,数据不变化的情况下,可以开启数据库的缓存。

查看缓存是否开启:

show variables like 'query_cache%';

queyrcachetype:on,表示缓存开启,默认是关闭的,可以通过修改 MySQL 配置文件 my.cnf 进行调整,重启服务后生效。

querycachelimit:1048576,表示单词查询缓存的结果集大小 1M,超过 1M 则不会缓存。

querycachesize,表示缓存开辟的空间大小

查看缓存操作情况:

show status like 'Qcache%';

Qcache_hits:表示缓存命中次数

Qcache_inserts:表示缓存写入次数

缓存生效的条件是在缓存开启的情况下,执行的 sql 语句字符串一模一样的时候,可以从缓存直接读取数据,但是当缓存数据相关的表存在数据变化的时候,原有的缓存就会失效,需要重新写人缓存。

MySQL 的缓存开启后,当 sql 查询语句带有 sqlnocache 关键字或者带有函数操作或者单次查询结果集超过 querycachelimit 的设置的值或者查询系统表时,不会用到缓存。

我们在开发中,最好不要开启缓存,将 querycachetype 设置为 off,querycachesize 设置为 0;缓存一般会用 Redis 方案来替代。

一条SQL是如何在MySQL中执行的,你get 到了吗?


免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:mmqy2019@163.com进行举报,并提供相关证据,查实之后,将立刻删除涉嫌侵权内容。
相关信息推荐
2021-01-05 17:14:52 关键词:mysql安装教程
摘要:上一篇文章我写了MySQL的安装,现在我们来讲解一下MySQL 教程。 从零开始学习 MySQL,主要是面向 MySQL 数据库管理系统初学者。下面我们来看看详细的讲解吧。 什么是数据库? 数据库(..
2020-12-03 15:21:33 关键词:mysql安装教程
摘要:本篇文章给大家详细分析了MySQL在并发场景下的问题及解决思路,对这方面有兴趣的朋友参考学习下吧。 1、背景 对于数据库系统来说在多用户并发条件下提高并发性的同时又要保证数..