前言

前篇已经从全局多个维度对MySQL的性能调优进行了分析总结，那废话不多说，接下来我们就先从客户端的sql语句优化作为开始，来打开MySQL原理内幕的大门。

场景

通常实际开发中经常能碰到这些业务场景：需要对业务日志中检测到的一些慢sql进行优化，减少它所执行的时间，来提高业务接口的响应效率，也就是降低接口的RT（Response Time）。

一般sql的执行时间优化到毫秒级别基本就满足业务需求了，当然某些场景下有时候会落实到更具体的一个需求，比如sql的执行时间需要降低到指定的ms内以满足某个业务接口的RT需求。

解决方案

通常情况下，这些优化的sql多以查询语句为主，而剩余的sql基本为批量更新操作。

我们以查询sql优化为例，排查解决步骤如下：

1. 检查sql中是否查询了不需要的字段数据或者某个字段数据过大等；
2. 使用explain解释分析sql语句的执行结果，再进行相应的优化，包括像是否是复杂关联查询、是否额外扫表（笛卡尔积）、是否使用及命中索引等等。

这里不会介绍具体有哪些优化手段应用，像某些特定类型的查询优化、切分查询、关联查询的分解等，相信很多书籍、博客上都能查阅到，但是最推荐的还是官方文档，而我们更关注的是对原理的深入了解。（后面如果有时间会单独再写这些实际优化经验）

执行原理

上面对于如何优化sql介绍了几点，像排查过程、sql执行扫表、索引等，这些我们都需要慢慢去深入了解，所谓不但要知其然还要知其所以然。

那我们先从基础开始，也就是一条sql是怎么执行的？我们知道sql的执行分为两种，一种是查询（查），一种是更新（增删改），它们的执行流程部分是不相同的，同时在不同的存储引擎中也是有差异化的。所以本文会从系统架构的层面介绍sql的执行原理，而关联到存储引擎的方面则会在写完存储引擎之后再进行深入分析。

所以，一条sql是怎么执行的？

image-20211025155634158

而下文主要还是以查询sql为为例来了解其原理，更新sql虽然执行过程一样，但是重点的差异还是在存储引擎之中。

缓存

相信缓存这个概念对大家来说都非常熟悉，它通常是为了提高上游服务的查询效率以及减少下游服务的压力。而MySQL为了提高数据库查询效率也加入了缓存的机制，但是却不尽人意，拥有很多限制。这里我们得明白一个道理：缓存只是提供了提高性能的机制，但并不是所有情况都适用，不当的使用反而带来性能的下降。

那我们先来了解下查询缓存，首先MySQL的查询缓存是将查询数据缓存在内存当中（这带来了两个问题：内存大小的限制、内存碎片化）；先将数据存放在一个引用表中，通过缓存数据的Key进行引用，而这个Key是对整个sql语句字符串、当前要查询的数据、客户端协议版本等的哈希值（其中sql语句必须逐字节完全相同，如果sql语句中大小写不一致、多余空格符号等，甚至使用不同数据库、不同协议版本或不同默认字符集的查询都不会命中，相信这对于开发编写sql来说是致命的；同时这也代表了如果sql中使用一些变量函数也会无法命中，具体参考说明），当sql查询命中缓存数据的Key时，则会跳过后续解析、优化、执行的阶段，立即将结果集返回给客户端。

我们知道缓存的一个典型不适用的场景就是频繁更新的数据，这会带来效率的降低以及数据的不一致性，所以MySQL的查询缓存中有一个机制：为了在代价很小的情况下解决这个问题，对数据涉及的表如果发生更改，则该表的所有相关缓存数据都将失效，像insert、update、delete、alter table、drop table、truncate table、drop database的语句。这也代表了它无法对缓存数据进行更细粒度的控制。

所以MySQL中缓存是默认关闭的，可以通过query_cache_type参数来开启，同时还提供了一些其他的参数来进行响应配置，像缓存的内存大小，内存分配的最新单位（降低碎片化）等，这里不在一一介绍。

综上我们能知道MySQL的查询缓存使用有比较多的限制，所以从 MySQL 5.7.20 开始不推荐使用查询缓存（弃用），并在 MySQL 8.0 中删除。这也代表它已经成为一个历史。

解析器

如果查询的sql没有命中缓存的话，接下里就需要交给了解析器，就像开发的语言一样，我们基于语言语法规范进行代码编写，最后交给程序去执行，那在执行前必然需要先对我们编写的代码进行编译解析，以满足语法规范它才能正常执行。

所以这也是我们能够想到，MySQL服务器在执行我们的sql语句前，肯定会进行相应解析。解析器会先将文本格式的sql转换为二进制结构，然后再对其解析，其中解析分为了两个部分：

1. sql解析：首先会通过词法分析将一些关键字分解，然后根据语法规则按照一定顺序进行组合生成一颗解析树（它能够有效的存储用于执行sql中任何可能需要的信息），同时它会对关键字是否使用错误、顺序是否正确等进行校验。虽然它的作用几句话就可以介绍完，但是其里面对词法和语法解析的处理实现还是相当复杂的。
2. 预处理：生成完解析树后，仅仅代表当前sql的语法正确性，但并不代表能正常执行，比如查询的表和数据否存在，客户端的数据权限等，所以它就是为了进一步来检查其合法性，最终建立从解析树到数据库物理对象的链接。

所以在解析器阶段，主要作用就是为了生成一个正确且合法的解析树，可以理解为sql的元数据。

image-20211025160608374

优化器Optimizer

我们通过流程图能看到，在得到一个解析树之后，会交给优化器并转换生成一条执行计划。

那除此以外，优化器还有什么作用呢？

其实我们能够想到一点，就是一条查询相同结果的sql是可以有多种写法的，甚至查询效率也会差距巨大，这样的设计对客户端来说是非常灵活开放的，但也注定服务端需要承担更合理的选择平衡。那么有没有一种机制去帮我们计算当前sql的执行消耗以及改良呢？

所以优化器另一个作用就在于此，它使用了成本模型，基于计算当前查询的多种可能查询操作的成本（Last_query_cost）来选择其中成本最小的一种，也就是最好的执行计划（当然这里面的计算依据是相当复杂的，也牵扯到所使用的存储引擎中的内存及索引结构等，暂时不进行深入）。虽然它已经非常智能了，但是并不是万能的，也会有很多原因导致错误的选择，进而错过实际最优的执行计划。

image-20211019144229239

而为了生成最佳的执行计划，它当中使用了很多的优化策略，简单可以可以分为两种，

• 静态优化：直接对解析树进行分析优化，仅在第一次优化完成后一直有效。
• 动态优化：和查询的上下文及其他可能因素有关，每次查询都需要重新计算评估。

那优化器是生成执行计划的呢？官方文档提供了具体逻辑实现的流程，如下：

handle_select()
   mysql_select()
     JOIN::prepare()
       setup_fields()
     JOIN::optimize()  /* 开始优化 */
       /* 执行转换 */
       optimize_cond() 
       opt_sum_query() 
       /* 统计索引相关的信息 */
       make_join_statistics()
         get_quick_record_count()
         choose_plan()
           /* 发现用户指定的访问表的最佳方式 */
           optimize_straight_join()
             best_access_path()
           /* 在所有可能的查询计划的中找到一个最优计划 */
           greedy_search()
             best_extension_by_limited_search()
               best_access_path()
           /* 彻底搜索最优计划 */
           find_best()
       make_join_select()
     JOIN::exec()

这里面主要分为三个部分：

1. prepare阶段：准备阶段，用于上下文分析，元数据的设置，以及一些子查询变换。
2. optimize阶段：优化阶段，这就是优化器进行优化转换，生成执行计划的阶段。
3. exec：执行阶段，交给执行引擎进行执行。

那接下来我们来看看优化器会优化哪些类型呢？如下图所示：

image-20211019144943730

综上所述，优化器主要执行了两个任务：

• 优化转换：将解析树优化同时转换为等效的树，执行起来更简单、更高效。
• 生成执行计划：包括对连接顺序的评估和访问所用表的方法的初始化。同时在这一步，执行计划的一部分也会被附加到解析树上。

所以整个流程结束，它的核心任务就是为了找到执行sql查询的最佳计划。

查询执行引擎

相对于解析器、优化器，查询执行引擎做的事就简单很多了，它的主要作用就是在拿到优化器生成的执行计划之后，根据执行计划去查询不同的存储引擎提供的统一接口来查询并返回结果。

功能看起来很简单啊，那为什么不直接在上一阶段直接调用存储引擎的接口，而是通过一个查询执行引擎来完成呢？

既然这样设计，那肯定有它的道理。我们知道存储引擎有很多种，每种存储引擎都通过底层API接口来提供服务，而执行计划在逐步执行的过程中，就会调用这些接口来完成操作，如果直接调用的化，那增加新的存储引擎或更新底层API时，已经应用的数据库服务怎么去兼容呢？所以查询执行引擎就是为了解决这个问题，通过让解析的sql和不同的存储引擎之间分离，来降低耦合性，同时实现存储引擎可热插拔的插件式架构；你可能发现这与Servlet的设计类似，都是通过提供统一的接口规范，这里让不同的存储引擎各自实现查询引擎锁提供的统一接口，从而使前面的流程无需关心底层是如何调用实现的。

存储引擎

通过前面的逐步分析与了解，我们肯定知道最终的存储引擎才是整个sql执行中的重中之重，而前面流程所做的工作都是为了结果查询去做准备，存储引擎才是数据存储与查询的底层支撑。

然而它其实并不神秘，我们在设计每个表的时候都可以指定使用相应的存储引擎，任何不同的存储引擎存储数据的方式都是通过将数据写入到磁盘文件或者写入到内存（少部分）来完成的；其中不同的存储引擎存储数据的格式也是不相同的，如果你打开数据库服务所在的目录文件下能看到，像InnoDB使用.ibd后缀名的文件来保存数据和索引信息，MyISAM使用.frm后缀名，CSV使用.CSV .CSM后缀名等。

其中最常用的就是默认的InnoDB存储引擎，下面我会简要列出一些比较常用的存储引擎：

• InnoDB：支持事务安全、索引、外键约束等功能，是一种高可靠性和高性能的通用存储引擎，所以它也是MySQL 5.7 中的默认存储引擎。（后续关于存储引擎也会重点分析它的架构设计及原理）
• MyISAM：在 MySQL 5.5 版本之前默认的存储引擎，相比InnoDB而言，应用范围比较小，通常用于读的操作；批量插入和查询速度较快，但不支持事务。
• Memory：将所有的数据存储在内存中，因此查询非常快速，但是也会带来内存大小的限制及数据持久化的问题；所以它更适用于临时数据缓存的场景需求。
• CSV：与csv文本格式一样，数据存储为带有逗号分隔的文本文件，支持csv格式的数据导入和导出，不支持索引。
• Archive：不支持索引和更新操作，多用于存储和检索一些历史、存档或安全审计数据。

等等，还有很多其他的存储引擎，这里不在一一列出，他们都有各自优缺点及试用场景；当然除此之外，你也可以通过Example这个官方提供的模板存储引擎去自己编写实现一个新的存储引擎，来满足当前的业务场景需求。

总结

通过上面的分析，相信我们已经清楚了sql执行过程的原理，总结下来就是两步，第一步sql解析准备，第二步存储引擎执行，而其中最为重要的就是有关存储引擎方面。那既然我们现在已经拥有了“性能优化维度”的地图，也通过“sql执行过程及原理”这把钥匙打开了MySQL的大门了，接下来就是要对“底层存储引擎”宫殿进行一番深入探究了。

文章中如有不妥之处，请批评指正，非常感谢。

把一件事做到极致就是天分！