前言
相信大家用了这么久的MySQL,一定很好奇自己写的SQL是如何执行并返回结果的,今天我们就来一层一层剥开MySQL这颗洋葱。
首先我们通过一张图来了解下整个过程,然后再开始一步一步解析。
第一步:客户端发送SQL语句到MySQL服务端
假如项目中用到了Mybatis来操作数据库,那么Mybatis就会通过JDBC来连接数据库,并发送语句给数据库,因为一般运行Web后台服务的机器和MySQL服务都是物理上隔开的,是一个分布式架构,所以需要通过网络来访问,JDBC采用TCP连接的方式与MySQL服务端进行通信,通信的内容包括发送语句、接收执行结果等。虽然TCP是全双工的,但是Mysql的TCP是半双工的,这意味着同一时刻要么客户端在发送数据,要么服务端在发送数据。
第二步:验证连接合法性
JDBC与数据库建立的连接的时候,会要求输入用户名和密码,Mysql需要验证用户名是否存在,密码是否正确。验证通过后,再根据mysql.user表中的host字段来验证客户端IP是否是允许的IP,这个host字段相当于一个白名单。
前面的合法性都通过后,JDBC才会发送实际的SQL语句给MySQL服务端。
第三步:查询缓存
像上面这种SELECT语句,MySQL服务端收到这个SQL时,如果开启了查询缓存,就会根据SQL语句在查询缓存中查找,查找成功就直接返回查询缓存中的结果给客户端,而不会执行下面这些操作。
请注意,这里的查找方式是根据SQL语句进行hash运算,只要SQL中有一个字节不同都不会命中缓存。
第四步:语法解析和预处理
当查询缓存没有命中时,才会开始进行语法解析和预处理。语法解析就像一个编译程序一样,根据语句生成语法树,并检查语法树中的关键字是否正确,顺序是否正确,引号是否前后匹配等。
经过语法解析后,预处理就会检查sql中的表、列是否存在,列名是否有歧义等,同时预处理还会对SQL进行权限认证,比如该用户是否有SELECT权限、INSERT权限..., 是否有对应数据库的权限、表的权限等等。
第五步:查询优化
查询优化主要分为两部分,一是静态优化二是动态优化。静态优化可以把语句中一些where条件进行等价交换,比如:WHERE 1=1 AND a > 2将被替换为WHERE a > 2;静态优化不依赖sql语句的具体值,就像Java静态编译器的语法糖一样。
动态优化:因为动态优化以页为最小单元来评估成本,所以需要分析SQL语句所对应的表的索引页或者数据页的数量,以此来确定是走索引还是全表扫描。这些信息都是通过存储引擎来获得的,所以如果存储引擎给出的结果不精确,那么查询优化的执行计划可能就不是最优的。
因为一条sql可以选择的执行方式有很多种,比如一张表里有多个索引,SQL语句涉及多个表的连接查询,那么得到上述信息后,就需要评估使用哪些索引、哪个表关联的顺序是最优的,并以此来生成一条执行计划。这部分也是Mysql服务层最复杂的地方,因为需要考量的因素有很多,这里笔者只是列出了一小部分。
第六步:调用存储引擎执行
其实在MySQL中,真正决定怎么存储数据和查询数据的组件是存储引擎。所以在第五步中得到了执行计划后,MySQL会调用表所对应的存储引擎的API,来执行真正的查询。Mysql定义了一系列存储引擎接口,来让编写存储引擎的人来实现,所以只要符合接口定义的存储引擎都是可以放入MySQL中去使用的。其中使用最广泛的引擎莫过于InnoDB,InnoDB是一个支持事务、支持崩溃快速恢复的高性能存储引擎。
Mysql服务层和存储引擎层最大的区别是:服务层实现了一些不依赖于具体存储引擎的通用操作,比如上面的连接验证、SQL验证这些。而存储引擎则完成具体的查询存储操作,所以好的存储引擎是Mysql的关键。
第七步:将结果返回给客户端
容易想到的一种方式是MySQL服务端先把查询结果缓存到内存中,然后再一次性发送给客户端,可实际上不是这样的。实际是拿到符合条件的第一条数据就返回给客户端,这是一个增量过程。这样做的原因,是可以缓解服务端的内存压力。
如果开启了查询缓存,并且语句是UPDATE、DELETE、INSERT之类的操作,那么这个时候也会更新查询缓存。
总结
在整个过程中,最复杂的部分是第五步的查询优化和第六步中具体的存储引擎,实现细节是造就了MySQL长盛不衰的原因。如果想要优化MySQL的性能,有几步可以优化:
客户端使用连接池,这样可以让连接复用,因为MySQL每接收一个连接都要用一个线程去处理,和其他Web服务器的连接池解决的问题一样,这里也可以解决。
查询缓存虽然在查询时可以避免很多后续操作的成本,但是维护它的成本也挺高的,因为每次UPDATE、DELETE、INSERT都需要互斥地更新对应表的查询缓存,这会成为MySQL的可扩展性瓶颈。根据阿姆达尔定律,决定一个系统能否水平扩展的是程序串行的部分。在MySQL8.0以上版本中,默认禁用了查询缓存。所以除非你能确定查询缓存确实对吞吐量有帮助,否则禁用查询缓存是个好建议。
默认情况下,客户端在第七步的接收过程中,其实是在自己的内存里缓存了全部结果之后,才会解除阻塞,这些会创建很多对象,当并发增高时,可能会引起JVM的OOM。所以这里可以改为每次只接收部分数据,处理完后再接收部分。但这里服务端对于资源都是持有状态,所以是一个空间和时间上的权衡。
如果有必要,你可以干涉第五步的查询优化过程,MySQL提供一些hint语句,比如强制走规定的关联表顺序或者强制使用某些索引。但是大多数情况下,请不要以为自己比查询优化器更聪明,使用推荐的方案可能更好。
设计一个好的索引对于查询的性能影响非常之大,所以对于使用关系型数据库来说,索引设计是非常重要的一环。
免责声明:本平台仅供信息发布交流之途,请谨慎判断信息真伪。如遇虚假诈骗信息,请立即举报
举报