MySQL进阶(一)

之前已经介绍了最基础的操作,那么这里开始从mysql自身理解。

Mysql基础架构图

mysql基础架构图

简单介绍上图相关模块功能

  • 连接器:管理连接,权限验证

  • 查询缓存:命中缓存则直接返回结果

  • 语法解析:词法分析,语法分析
  • 查询优化:执行计划生成,索引选择
  • 执行器:操作引擎,返回结果
  • 存储引擎:存储数据,提供读写结构。

连接器

我们需要连接到mysql服务端才能进行各种操作。

1
2
# 使用密码登录到时候我们会使用这种格式。
mysql -h$ip -P$port -u$user -p

执行上述命令会提示输入密码。输入完密码后就进入了mysql。

  • 如果用户名或者密码输入错误,会提示一个Access denied for user到错误,然后客户端程序结束执行。

  • 如果用户名和密码认证通过,连接器会去权限表中查询该用户拥有的所有权限。此后该连接里面的权限逻辑判断都会依赖认证成功时候读取到的权限。

因此当修改用户的权限之后,如果被修改用户的在登录状态是不会修改已经连接的权限。需要让被修改权限用户从新登录。

  • 实际上连接的方式有多种,上面通过密码的方式只是其中的一种方式。当客户端与服务端mysql进程进程建立连接,服务器的进程就会创建一个单独的线程来专门处理与这个客户端的交互,当该客户断开与服务端连接时,服务端并不会马上销毁对应的交互线程,而是缓存起来。当另一个新的客户端连接进来的时候,在把这个缓存的线程分配给新的客户端。这样就避免的频繁创建和修改线程,节省系统的开销。但是连接线程多了也会影响服务端,所以也有默认的参数限制客户端的连接数量。
1
2
# 这里是查询服务端存在的连接。
SHOW PROCESSLIST;

我使用的阿里云的数据库,这里展示下结果集。这里需要注意,登录的账号需要有PROCESS的权限否则只能看到自己的连接信息(线程),我这里采用的root所以看到全部到。

连接完成后如果没有执行后续操作则 Command会显示Sleep的状态,长时间Sleep会导致连接自动断开。默认的时间是8小时,也就是参数 wait_timeout。当客户端连接断开后,若客户端再次发出请求就会提示一个

Lost connection to MySQL server during query,此时就只能重新建立连接了。

半双工
  • MySQL客户端/服务端通信协议是“半双工”的:在任一时刻,要么是服务器向客户端发送数据,要么是客户端向服务器发送数据,这两个动作不能同时发生。一旦一端开始发送消息,另一端要接收完整个消息才能响应它,所以我们无法也无须将一个消息切成小块独立发送,也没有办法进行流量控制。
  • 服务器响应给用户的数据通常会很多,由多个数据包组成。但是当服务器响应客户端请求时,客户端必须完整的接收整个返回结果,而不能简单的只取前面几条结果,然后让服务器停止发送。因而在实际开发中,尽量保持查询简单且只返回必需的数据,减小通信间数据包的大小和数量是一个非常好的习惯,这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。
长连接与短连接
  • 在数据库里面,长连接是指数据库建立连接,如果客户端有持续的请求则使用同一个连接,短连接是指每次执行几次短的查询就断开连接,下次查询再次创建连接。由于创建的过程复杂,一般来说尽量减少创建连接的动作,尽可能使用长连接。特别是在开发的时候出现大伙突然连接不上测试库,此时就检查下连接是不是太多了。因为连接多了会导致内存消耗特别快。

    • mysql的执行过程中临时使用的内存管理是在连接线程对象里面,服务端断开的时候才会释放,为了避免内存消耗过大,每次执行较大的操作之后,可以通过mysql_reset_connection来重新初始化连接资源。这个操作是不需要重新做权限,只是恢复到创建的初始状态。
  • MySQL mysql_reset_connection 官方文档

查询缓存

一个不被建议使用的功能,在新版的8.0中已经被删除了。总结就是弊大于利。

  • 建立完成后,假设执行一次SELECT语句,执行逻辑就会到第二部查询缓存。mysql拿到一个查询之后,会先到缓存中寻找释放有完全对应的,因为之前执行的查询语句和结果集会直接被缓存起来,以Keys-value的形式。keys是查询语句-value是查询结果集。如果能命中这个key则直接返回value。咋一看挺有用的,但实际上查询缓存的实效的太频繁。并且keys的命中条件太不聪明了,如果两次查询语句在任何的字符上存在不通(空格、大小写)都不会缓存命中。为什么会频繁失效,因为如果对查询语句中的表进行更新,就会导致缓存失效。比如INSERT、UPDATE 、DELETE 、TRUNCATE TABLE 、ALTER TABLE等等就会导致缓存失效。

语法解析

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

  • 本质上发送过来的是一个文本信息,这里就需要对该文本信息进行编译,涉及到词法解析语法解析语义解析等阶段。需要注意的是,这里会对sql语句进行一些检查,比如设计的相关表、表字段。这里检查通过的才会进入查询优化模块。

查询优化

通过之前的语法解析,基本可以判定语法树是合法的,此时mysql就知道文本内容要做什么。

  • mysql会对查询语句进行优化,优化的结果就是生成一个执行计划,这个计划会表明使用了那些查询索引,表之间的连接是什么样子的。有时候出现多种执行方式,只是效率不通,优化器会决定使用哪一个执行方案,这个时候就涉及到EXPLAIN语句,该语句可以查看sql到执行计划,这将涉及到查询优化。
  • MySQL的查询优化器是一个非常复杂的部件,它使用了非常多的优化策略来生成一个最优的执行计划:
    • 重新定义表的关联顺序(多张表关联查询时,并不一定按照SQL中指定的顺序进行,但有一些技巧可以指定关联顺序)
    • 优化函数
    • 提前终止查询(比如:使用Limit时,查找到满足数量的结果集后会立即终止查询)
    • 优化排序(在老版本MySQL会使用两次传输排序,即先读取行指针和需要排序的字段在内存中对其排序,然后再根据排序结果去读取数据行,而新版本采用的是单次传输排序,也就是一次读取所有的数据行,然后根据给定的列排序。对于I/O密集型应用,效率会高很多。

执行器

进入到执行sql到环节。

  • 通过之前到查询优化,进入执行阶段,此时会先判断该连接用户是否有相关表的权限,如果没有会返回权限错误。如果有权限就会打开表,执行器会根据表定义的引擎去使用这个引擎提供的接口完成流程。

存储引擎

关于存储引擎,这里明确提醒。不是三种也不是四种。而是多种因为随时可能出现新的引擎。

  • 我们常用的就是InnoDBMyISAM,其它的不常见就不做过多介绍。mysql现在默认的引擎是InnoDB所以主要也是了解InnoDB
1
2
# 查看当前服务支持的存储引擎
SHOW ENGINES;