一条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完全不考虑数据安全性，只是它的粒度有些太大了，它为了数据的安全直接动用了表级锁，直接导致性能影响太大。