2020-06-19

一条SQL语句的奇妙旅程

MySQL的逻辑架构

  1.  连接器:负责用户的身份认证和权限校验。

  2. 查询缓存:这个在8.0以后的版本已经取缔了,但是不影响设计思想的了解,即:当有一个SQL进来的时候,先会去匹配SQL语句,如果本地已经有缓存,即直接读缓存,返回结果。乍一听挺好的功能,为什么会被取缔呢?这存在一些设计理念的问题,MySQL追求极高的性能,该功能在实际使用过程中弊大于利,写缓存/清缓存的过程可能会大幅度影响性能

  3. 分析器/解析器:这个模块主要是进行一些语法分析/词法分析,大家可能觉得与自己关系不太大,其实我们初学SQL语句的时候第一个打交道的就是这个模块,比如当你输入错误语句的时候,语法的错误在这层就已经发现了。

  4. 预处理器:这个模块会确定你输入的表和列是否真实存在,字段别名是否有歧义,主要是当你语法没错的时候,确定是否符合这个场景。

  5. 查询优化器:这可以说是server层最核心也是实战最重要的地方了,首先,我们每次可以通过explain+sql来查看当前语句的各个属性,这些属性能给我们调优很好的建议。这点会在下篇文章中细谈,这篇文章总体是为了打通全链路。
    其次,我们可以根据show warnings来查看查询优化器到底做了哪些优化,从而考虑优化是否得当。

  6. 执行引擎:这块主要是对结果进行过滤和排序,这个模块的命名有些歧义,其实真实的数据检索并非在这里进行,这层进行的是对检索的结果集进行一次过滤和排序。

  7. 存储引擎:这里就是真正的核心了,也就是我们一直所说的innodb、myisam等存储引擎工作的地方,我们所有的查询检索任务都是在这里进行的,所有的存储也是在这里进行。这个模块也会在之后的文章中分析。

现在来假设一条SQL来做全套了,那么它会是怎样的一个逻辑呢?

首先我们客户端与服务器之间会建立一个连接(这里的连接是指那几种进程通信方式中的一种,大部分情况下说的是TCP/IP进行连接的,过程中需要我们mysql -u root -p输入密码,就可以进入mysql。

此时我们可以通过SQL语句进行增删改查,当我们输入一条语句,倘若在5.8版本之前,这条语句会进入查询缓存,在这个模块中先对SQL语句进行匹配,倘若找到完全匹配的缓存,则读缓存返回结果,倘若没有匹配的,则会进入下一模块。查询到的结果会写进写缓存这也是之所以在5.8以后取缔掉查询缓存的原因:写缓存对性能是有一定的影响的,事实上这个影响甚至大于其对速度的提升。

进入下一模块以后,会对语句进入一些词法分析和语法分析,看看有没有语法上的错误,初学者经常被卡在这个模块,因为语句写的不规范之类的问题。

再到后来,这条语句会进入预处理器,也就是看看你虽然说的都对,语法都对,但是你搜索的列如果是我根本没有的,那相当于什么都没有,如果一切正常,就再往后走。

此时进入了server层最核心的查询优化器,这里MySQL有自己的想法, 他会对你输入的语句进行一些优化和重排,这里有一个很细节但是很核心的点:任何关联进入查询优化器都会变成嵌套循环关联,也就是说,很多复杂的关联都会变成类似于左连接之类的关联,其原因也非常简单,正如上述:执行引擎是对结果进行过滤和排序,那么如果我们可以通过优化语句提前对语句进行过滤,这样就可以大幅度提高性能。当我们查询Explain+sql语句的时候会出现很多的属性,我们需要注意的是要避免外部排序的产生,因为这样会产生巨大的性能影响。

查询优化器毕竟不是十全十美的,它的很多优化可能是好心没做好事,最典型的就是临时表了,首先临时表这个东西本身是非常好的,它将中间过程的一些结果集存储在内存上,很有利于查询过程的执行,但是有可能内存不足的情况下,会将数据存储在磁盘上,而磁盘参与读写一定会带来非必要的资源和性能消耗,因此我们必须结合实际情况考究某些属性的存在是否合理。

查询优化器之后,查询进入了执行引擎,在这里其实并未进行太多的事情,执行引擎主要是调度存储引擎,然后存储引擎在硬盘中去检索,然后拿到结果返回执行引擎,由执行引擎进行结果的过滤和排序,然后返回结果集回到客户端,如果是在5.8以前的话,会放入写缓存。

接下来就是核心的问题了:存储引擎。

我们常用的存储引擎大概是innodb和myisam,这两任默认引擎撑起了MySQL的半边天,现在我会比较粗略的介绍一下两个引擎,详细的文章会在后期输出。

innodb存储引擎与myisam存储引擎

首先我们先认识所谓存储引擎最重要的两个功能就是存储与查询,那么我们从存储和查询两个方面来分析一下这两个引擎的特点。

存储方面:
首先当我们存储数据的时候,因为这两个引擎都是基于磁盘的,innodb是将索引和数据放在一起的,这跟其索引结构有关,当进行存储的时候,innodb将所有的数据都存储在一起;而myisam则是将索引数据和真实数据分开存储,这样其实是通过索引进行检索,得到的结果是一个真实数据的指针,然后进入数据文件中进行随机io。这就决定了一点,如果我们是innodb存储引擎的话,主键尽量自增,因为如果非自增,会导致不能进行顺序io,性能会有很大的退化。而myisam的数据存储位置不能轻易移动,因为这样会导致索引失效,也会影响性能。

查询方面:
查询就像翻词典一样,索引就像目录和页码,这是非常重要的一部分,很多初学者觉得自己非常少用到索引,其实不然,我们经常用到的主键便是最标准的索引,当你的查询命中索引的时候,便可以进入高速列车,为什么呢?因为数据结构,在此强调一下,一直很认同程序=数据结构+算法,数据结构非常重要,如果我们能命中索引,就可以进入索引的数据结构,此时要么是哈希索引,要么是b+树索引,这两种索引一个的复杂度为O(1),一个是O(log(M)N),其中M为索引关键字,N为总关键字数量,很容易看到,这种速度比全表遍历好了太多了,所以如果命中索引的话,就可以大幅度提高速率。

当然这两点虽然重要,但是都不是两种引擎最大的区别,这两种存储引擎最大的差别在于事务性和锁的粒度上,这也是innodb弯道超车最重要的原因。

事务性

随着数据库的应用场景越来越多,我们对数据的安全性有了越来越多的需求,myisam不支持事务,这导致这种存储引擎很快的落时了。

锁粒度

并不是说myisam完全不考虑数据安全性,只是它的粒度有些太大了,它为了数据的安全直接动用了表级锁,直接导致性能影响太大。

一条SQL语句的奇妙旅程

No comments:

Post a Comment