MySQL基础知识

一code当先

已于 2023-03-24 15:47:03 修改

阅读量341

点赞数

分类专栏：数据库文章标签： MySQL 原理 MySQL原理 MySQL查询

于 2019-06-28 11:30:23 首次发布

本文链接：https://blog.youkuaiyun.com/xp_xpxp/article/details/93978900

版权

数据库专栏收录该内容

6 篇文章

订阅专栏

1.MySQL逻辑架构

在这里插入图片描述

连接层

连接层是客户端和连接服务,包括本地sock 通信和大多数基于客户端/服务端工具实现的,类似于tcp/ip 的通信。主要完成一些类似于链接处理、授权认证、以及相关的安全方案。在该层上引入了连接池的概念,为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL 的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

服务层

服务层主要完成大多数的核心服务功能,如SQL 接口,并完成缓存的查询,SQL 的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层上实现,如过程,函数等。在该层,服务器会解析并创建相应的内部解析树,并完成相应的优化如确定查询表的顺序,是否利用索引等,最后生成对应的执行操作。如果是select 语句,服务器还会查询内部的缓存。如果缓存空间足够大,这样在解决大量读操作的环境中能够很好的提升系统的性能。

引擎层

在存储引擎层,存储引擎真正的负责了MySQL中数据的存储和提取,服务器通过API 与存储引擎进行通信。不同的存储引擎具有不同的功能,这样使得我们可以根据自己的实际需求选取合适的存储引擎。

存储层

数据存储层,主要将数据存储在运行于裸设备上的文件系统上,并完成与存储引擎的交互。

2.MySQL客户端/服务端通信协议

MySQL客户端/服务端通信协议是半双工的;在任一时刻,要么是服务器向客户端发送数据,要么是客户端向服务器发送数据,这两个动作不能同时发生。一旦一端开始发送消息,另一端要接收完整个消息才能响应它,所以我们无法也无须将一个消息切成小块独立发送,也没有办法进行流量控制

客户端用一个单独的数据包将查询请求发送给服务器,所以当查询语句很长的时候,需要设置max_allowed_packet参数。但是需要注意的是,如果查询实在是太大,服务端会拒绝接收更多数据并抛出异常。

与之相反的是,服务器响应给用户的数据通常会由多个数据包组成。但是当服务器响应客户端请求时,客户端必须完整的接收整个返回结果,而不能简单的只取前面几条结果,然后让服务器停止发送。因而在实际开发中,尽量保持查询简单且只返回必需的数据,减小通信间数据包的大小和数量是一个非常好的习惯,这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一

3.MySQL查询过程

如图:
在这里插入图片描述

MySQL整个查询执行过程,总的来说分为以下几个步骤:

客户端向MySQL服务器发送一条查询请求
服务器首先检查查询缓存,如果命中缓存,则立刻返回存储在缓存中的结果;否则进入下一阶段
服务器进行SQL解析、预处理、再由优化器生成对应的执行计划
MySQL根据执行计划,调用存储引擎的API来执行查询
将结果返回给客户端,同时缓存查询结果！

MySQL查询缓存

在解析一个查询语句前,如果查询缓存是打开的,那么MySQL会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存,在检查一次用户权限后直接返回缓存中的结果。这种情况下,查询不会被解析,也不会生成执行计划,更不会执行。
MySQL将缓存存放在一个引用表（可以认为是类似于HashMap的数据结构）,通过一个哈希值索引,这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（例如:空格、注释）,都会导致缓存不会命中。
如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表,其查询结果都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间,返回不同的查询结果,再比如包含CURRENT_USER或者CONNECION_ID()的查询语句会因为不同的用户而返回不同的结果,将这样的查询结果缓存起来没有任何的意义。
既然是缓存,就会失效,那查询缓存何时失效呢？MySQL的查询缓存系统会跟踪查询中涉及的每个表,如果这些表（数据或结构）发生变化,那么和这张表相关的所有缓存数据都将失效。正因为如此,在任何的写操作时,MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多,这个操作就可能带来很大的系统消耗,甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作,读操作也不例外
A.任何的查询语句在开始之前都必须经过检查,即使这条SQL语句永远不会命中缓存
B.如果查询结果可以被缓存,那么执行完成后会将结果存入缓存,也会带来额外的系统消耗
基于此,我们要知道并不是什么情况下查询缓存都会提高系统性能,缓存和失效都会带来额外消耗,只有当缓存带来的资源节约大于其本身消耗的资源时,才会给系统带来性能提升。如果系统确实存在一些性能问题,可以尝试打开查询缓存,并在数据库设计上做一些优化,比如:
A.多个小表代替一个大表,注意不要过度设计
B.批量插入代替循环单条插入
C.合理控制缓存空间大小,一般来说其大小设置为几十兆比较合适
D.可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存

最后的忠告是不要轻易打开查询缓存,特别是写密集型应用。如果你实在是忍不住,可以将query_cache_type设置为DEMAND,这时只有加入SQL_CACHE的查询才会走缓存,其他查询则不会,这样可以非常自由地控制哪些查询需要被缓存。

语法解析和预处理

MySQL通过关键字将SQL语句进行解析,并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。

查询优化

经过语法解析和预处理过程生成的语法树被认为是合法的了,并且由优化器将其转化成查询计划。多数情况下,一条查询可以有很多种执行方式,最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。

MySQL使用基于成本的优化器,它尝试预测一个查询使用某种执行计划时的成本,并选择其中成本最小的一个。在MySQL可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本。

例如:

mysql> select * from t_message limit 10;
...省略结果集

mysql> show status like 'last_query_cost';
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 6391.799000 |
+-----------------+-------------+

查询执行引擎

在完成解析和优化阶段以后,MySQL会生成对应的执行计划,查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成,这些接口被称为handler API。查询过程中的每一张表由一个handler实例表示。实际上,MySQL在查询优化阶段就为每一张表创建了一个handler实例,优化器可以根据这些实例的接口来获取表的相关信息,包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能,但其底层仅有几十个接口,这些接口像搭积木一样完成了一次查询的大部分操作。

返回结果给客户端

查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据,MySQL仍然会返回这个查询的相关信息,比如该查询影响到的行数以及执行时间等等;如果查询缓存被打开且这个查询可以被缓存,MySQL也会将结果存放到缓存中。
结果集返回客户端是一个增量且逐步返回的过程。有可能MySQL在生成第一条结果时,就开始向客户端逐步返回结果集了。这样服务端就无须存储太多结果而消耗过多内存,也可以让客户端第一时间获得返回结果。需要注意的是,结果集中的每一行都会以一个满足客户端/服务器通信协议的数据包发送,再通过TCP协议进行传输,在传输过程中,可能对MySQL的数据包进行缓存然后批量发送。

4.MySQL常用的存储引擎–MYISAM和INNODB

查看存储引擎语法

mysql> show engines;

例如:
在这里插入图片描述
***注意:MySQL5.5.x版本之后默认使用的是INNODB存储引擎!!!

MYISAM和INNODB存储引擎的区别

对比项	MYISAM	INNODB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁,即使操作一条记录也会锁住整个表,不适合高并发	行锁,操作时只锁定某一条记录,对其他记录没有影响,适合高并发
缓存	只缓存索引,不缓存真实数据	不仅缓存索引还要缓存真实数据,对内存要求较高,而且内存大小对性能有决定性的影响
表空间	小	大
关注点	性能	事务
默认安装	Y	Y