换个角度去看一下不一样的MySSQL

最新推荐文章于 2021-01-27 22:39:14 发布

§九千七§

最新推荐文章于 2021-01-27 22:39:14 发布

阅读量143

点赞数

分类专栏： Java 与 MySQL 文章标签： mysql java

本文链接：https://blog.youkuaiyun.com/chuli0327/article/details/110704802

版权

Java 与 MySQL 专栏收录该内容

20 篇文章

订阅专栏

本文从连接器、查询缓存、分析器、优化器和执行器五个方面深入探讨MySQL的工作原理，帮助读者理解MySQL内部运行机制，提升数据库性能优化能力。通过实例介绍每个部分的职责和作用，如连接器的权限验证、查询缓存的优缺点、分析器的语法分析等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

文章目录

换个角度监视一下不一样的My

换个角度监视一下不一样的My

一,引言

你对MySQL的认识是否是了解SQL语句,了解DDL,TCL等操作呢,你有没有想过MySQL到底是怎样的,他的内部到底是怎么运行的呢,下面我们一起走近了解一下MySQL吧
在了解一个个系统模块的原理后,再来使用它,感觉是完全不一样的,在代码里面写下一行数据库命令时,他在数据库端将怎么执行,他的性能时怎样的,怎么写才能使数据库的综合性能更好,哪些数据处理在缓存里做性能可能更好,在建表和建索引时,我们会更有意识的为将来的查询优化做综合考虑等等

这有一个有趣的话题

关于MySQL的发音问题 : The official way to pronounce “MySQL” is “My Ess Que Ell” (not “my sequel”), but we do not mind if you pronounce it as “my sequel” or in some other localized way.
所以你明白了它的发音吗,下面就正式认识一下MySQL吧

二,初识MySQL

为什么说是初识呢,因为我觉得以前我眼中得MySQL看的不够真切,看到得只是被遮掩住的
我们第一个来了解MySQL的基本架构在这里插入图片描述
大体上,MySQL可以分为Server层和存储引擎层两部分

Server层,包括连接器,查询缓存,分析器,优化器,执行器等,执行MySQL的大多数核心服务功能,以及包含所有的内置函数(eg:日期,时间,数学和加密函数等),所有跨存储引擎的功能都在这一层实现,比如存储过程,触发器,视图等
存储引擎 : 负责数据的存储和提取,其架构模式是插件式的,支持InnoDB,MyISAM,Memory等多个存储引擎,其中InnoDB已经从MySQL5.5.5开始成为默认引擎
从图中看到所有的引擎都是共用一个Server层,也就是从连接器到执行器的部分

三,Server 层的主要五部分

连接器：负责跟客户端建立连接、获取权限、维持和管理连接。
查询缓存：查询请求先访问缓存(key 是查询的语句，value是查询的结果)。命中直接返回。不推荐使用缓存，更新会把缓存清除(关闭缓存：参数 query_cache_type 设置成 DEMAND)。
分析器：对 SQL 语句做解析，判断sql是否正确。
优化器：决定使用哪个索引，多表关联（join）的时候，决定各个表的连接顺序。
执行器：执行语句，先判断用户有无查询权限，使用表定义的存储引擎。

一,连接器

在这里插入图片描述
客户端 首先经过 TCP连接 建立连接 ,然后 传送用户名和密码 这个时候就到了 连接器 然后连接器就开始对传送过来的数据 验证用户名和密码 失败就返回错误,成功就去 获取权限 然后就到了后续操作 空闲状态 即没有其他操作或者 后续操作 即去执行 SQL语句或其他内容

1 . 连接器的职责 :

跟客户建立连接
获取权限
权限验证
维持连接
管理连接

2 . 跟客户端建立连接

客户端输入命令,进行数据库连接

mysql -h$ip -P$port -u$user -p

客户端与数据库连接的建立也是从经典的TCP三次握手开始的,确立连接

在输入密码的时候有一个小的注意点 : 虽然 -p后面可以直接输入密码,但是这样的操作就又可以导致密码泄露,特别是如果你连接的是生产服务器,那样密码就很危险,所以建议不要把密码输在-p后面而是在下一行再进行密码验证

3.身份,权限验证

连接器就开始认证你的身份了,这个时候就是验证了

验证用户名和密码:如果发生错误,连接器就会返回一个"Access denied for user"的错误提示,然后结束和客户端建立起的连接
如果用户名和密码正确,认证通过,连接器将会到权限表中查出你拥有的权限,之后这个连接里面的权限判断逻辑,都将依赖于此时读到的权限

这样也可以明白,当一个连接建立后,你的权限就是你第一次建立连接时获取的权限,当你使用管理员账号修改权限后,也不会影响你现在已经存在的连接,所以你要更新你的权限就必须先断开连接然后重新连接,获取新的连接,这个时候修改的权限才会更改,才能使用新的权限
这也是有很多人说我修改权限后为何还是权限还没有修改的原因

4 . 后续状态

如果你没有后续的动作,那么这个连接就会处于空闲状态,可以通过show processlist 命令来查看,下图可以看出,在Command列中,只有一个连接是运行时,其他连接都是**“Sleep”** ,就表示这些都是空闲连接,当然,客户端如果太久没有操作,连接器就会断开
这个断开时间是由 wait_timeout 来控制的,默认时间是8小时
如果连接断开了,客户端再次发送请求的话,就会收到一个错误提示 “Lost connection to MySQL server during query” 这个时候还要继续,就需要重新连接,然后再执行请求了

这里有两个小概念 :
长连接 : 指连接成功后,如果客户端持续有请求,则一直使用同一个连接
短链接 : 则是指每次执行完很少的几次查询就断开连接,下次查询再重新建立一个连接
当然长连接和短连接各有优势,因为建立连接的过程通常是比较复杂的,即你要先经过客户端和数据库的连接,所以再连接的过程中要尽量减少连接的动作,就是少用短链接,尽量使用长连接

当然全部使用长连接后,你就会发现,有时候MySQL占用内存涨的特别快,这是因为MySQL在执行过程中临时使用的内存是管理在连接对象里面的,这些资源会在连接断开的时候才释放掉,所以如果长连接累积下来,就会导致内存占用太大,被系统OOM - 即强行杀死,从现象来看就是MySQL重启了
当然有了发现了问题,那么也会开始处理问题 :

定期断开长连接,使用一段时间,或者程序里面判断执行过一个占用内存的大查询后,断开连接,之后重新连接
如果版本大于 5.7 ,可以在每次执行一个比较大的操作后,通过执行 mysql_reset-connection 来重新初始化连接资源,这个过程不需要重新连接和重新权限验证,只是将连接恢复到刚刚创建的时候

二 . 查询缓存

在这里插入图片描述
这一个区域是存储一些你操作过的SQL操作,方便下次查询你的,比如你执行select操作,那么他就会先到查询缓存里面查询你之前是否执行过,这里的存储结构是以 key - value 对的形式存储的,key 是查询的语句, value 是查询的结果,如果你的select在缓存key中找到,那么就会直接返回value给你,如果缓存中没有,就会继续执行后面的操作,执行完成后执行的结果就会被存入缓存中

优势 : 提高了数据库的效率,因为不用执行后面操作就可以直接返回查询结果了
劣势 : 虽然效率提高了,但是查询缓存往往弊大于利
查询缓存失效非常频繁,只要有一个对表的更新,这个表上所有的查询缓存都会被清空,对于更新压力大的数据库来说,查询缓存的命中率会非常低,除非你的业务就是有一张静态表,很长时间才会更新一次,eg:一个系统配置表,那么这张表的查询才适合使用查询缓存
那么你就想问如何不用查询缓存呢

在MySQL 8.0之前可以通过 query_cache_type 设置为 REMAND ,这样就不会使用查询缓,而你要使用查询缓存的时候你可以显式的调用,但是在8.0以后就彻底没有这个功能了

mysql> select SQL_CACHE * from T where ID=10；

三 , 分析器

解析器处理语法和解析查询, 生成一课对应的解析树。

预处理器进一步检查解析树的合法
在这里插入图片描述

如果没有命中缓存,那么就只能从存储中查询数据了,因为MySQL其实并不知道你输入的语句是什么,所以要执行操作必须先对SQL语句进行解析
eg:

select id from user
inser into user value(id)

分析器要做的就对这些语句进行"词法分析"

分析器首先是进行词法分析,对于你输入的多个字符串和空格组成的一条SQL 语句, 分析器会根据你输入的 “select” 根据这个关键字识别为查询操作,’'insert" 识别为插入操作, ''id" 识别为列 ‘‘user’’ 识别为表等等
分析器第二步是进行 “语法分析” : 根据词法分析的结果,语法分析器会根据语法规则,判断你输入的这个SQL语句是否满足MySQL语法
这里如果你的语法不对,就会收到 “You have an error in your SQL syntax” 的错误提醒,当然一般语法错误会提示第一个出现错误的位置,所以你可以很简单的查询到你错误的位置