一、引文最近我正在学习 MySQL 的面试相关八股打算单独开一个 MySQL 专栏来记录自己每天的学习内容主要来自小林coding和 JavaGuide。今天了解到了 MySQL 的架构和执行流程没想到一条简单的 sql 语句居然在 MySQL 中经历了这么多的流程。二、MySQL 执行流程MySQL 执行流程大概分为服务层和存储引擎层两部分服务层中主要涉及到建立连接、查询缓存、SQL语句解析、预处理、优化、执行几个阶段存储引擎层则是把执行结果拿到的数据返回。1.服务层1连接器连接器这一环主要是让客户端如 Java 程序、TablePlus等基于 TCP 连接到 MySQL 的服务端。第一步肯定是先启动 MySQL 服务然后基于 TCP 协议完成三次握手连接层要做的事情第一个就是校验你的用户名和密码是否正确只有你输入正确时才会建立连接同时记录你这个账号的权限并在此后该连接的过程中都会基于刚连接时保存的权限一直执行该权限的逻辑。在 MySQL 中也有长连接和短连接的概念所谓的短连接就是每建立一次 MySQL 客户端连接只能执行一条 SQL 语句然后就立马断开连接。而长连接就是一次客户端连接中可以执行多条 SQL 语句。长连接与短连接的性能差异极大在 MySQL 中频繁地创建销毁连接都是极其消耗资源的原因主要出在了巨大的“握手”开销上如网络层面的 TCP 三次握手、MySQL 协议握手、TLS/SSL握手以及每次创建连接 MySQL 都要去系统表查询用户权限并加载到内存中。与此同时 MySQL 的连接与内存是密切相关的。默认情况下连接器每建立一次新的连接MySQL 都会对应创建一个新的工作线程即便这个线程是 Sleep 状态也会占用相应的内存。为避免大量线程创建导致内存溢出以及 CPU 频繁切换线程MySQL 还提供了线程池功能让少量线程服务于大量连接进而减小内存损耗。一个连接占用的内存主要分为两部分固定内存和临时内存。(1) 固定内存 (Thread Static Memory)这是连接建立后立即分配的直到连接断开才释放线程栈 (Thread Stack)存储线程执行时的局部变量、函数调用信息。由参数thread_stack控制默认 256KB 左右。连接信息存储用户信息、权限、当前数据库名、状态变量等。网络缓存 (Net Buffer)用于存放客户端发送的 SQL 语句和服务器返回的结果。由net_buffer_length控制。(2) 会话级临时内存 (Session Private Memory)这是最容易导致内存激增的部分。当连接开始执行复杂的 SQL 时MySQL 会根据需要临时分配内存排序缓冲区 (Sort Buffer)执行ORDER BY或GROUP BY时使用。连接缓冲区 (Join Buffer)执行多表关联查询时使用。临时表内存 (Memory Temporary Table)执行复杂查询产生的中间结果。结果集缓存在数据发回客户端之前暂存数据的内存。关键点这些内存是按需分配的。如果一个 SQL 不需要排序就不会分配sort_buffer。但如果设置得太大成千上万个连接同时请求时内存会瞬间被榨干。管理与回收机制(1) 空闲连接的管理如果一个连接执行完任务后没有关闭长连接它会进入Sleep状态。保留资源它依然占用线程栈和基础的网络缓存。释放资源它会释放掉执行 SQL 时临时申请的sort_buffer等。超时断开MySQL 通过wait_timeout参数控制空闲连接的寿命。如果一个连接空闲时间超过这个值MySQL 会强制杀掉该连接并回收内存。(2) 线程缓存 (Thread Cache)为了避免频繁创建和销毁线程这是很消耗资源的MySQL 实现了一个Thread Cache当客户端断开连接时MySQL 不会直接销毁线程而是把它放入缓存池中。当下个连接进来时直接从池里捞出一个现成的线程使用。由thread_cache_size参数控制缓存的数量。常见指令show processlist // 查看 MySQL 服务端被多少客户端连接的情况show variables like wait_timeout // 查看 MySQL 中客户端连接的最大空闲时长show variables like max_connections // 查看 MySQL 中最大客户端连接数量kill connection id // 删除 MySQL 的某个客户端连接提供连接对应的 id 即可2查询缓存连接器建立连接之后客户端就可以发送 SQL 语句给 MySQL 服务端了拿到 SQL 语句先解析第一个字段查看是什么类型的 SQL 语句。如果发现是 SELECT那就路由到查询缓存里查询缓存里面的数据是k-v形式的键值对key为 SQL 查询语句value 为该查询语句返回的结果。如果拿着客户端发来的 SQL 语句命中了缓存那就可以直接返回 value反之再走下面的解析器层。也许是受到了 Redis 的影响让我一开始觉得这功能设计的很好。但仔细一想会发现MySQL 里面的表不同于 专门做缓存的 RedisMySQL 里面的数据肯定是更容易更新的一旦表中数据更新那就意味着查询缓存失效需要重建缓存。如果遇到一个重建条件非常苛刻的缓存好不容易重建完表又更新了这缓存还一次没用呢是不是就十分浪费资源。所以从 MySQL 8 起查询缓存这一环就直接被去除了。3解析器解析器主要做两件事第一件事是词法分析这一步主要是把输入转化成若干个Token其中Token包含key和非key。比如一个简单的SQL如下所示SELECT name FROM userInfo分析之后会得到4个Token其中有2个Keyword分别为select和from关键字非关键字关键字非关键字selectnamefromuserInfo第二件事就是语法分析拿到上一步词法分析的结果后再判断 SQL 语句是否符合语法规范如果没问题那就构建语法树方便后续获取 SQL 类型、表名、字段名。如果写错了如selec会报错You have an error in your SQL syntax。4预处理器如果写了一个语法完全正确的树但是表或者字段不存在还是在解析的时候报错因为解析器处理之后还有一个预处理器它用来判断解析树的语义是否正确也就是表名和字段名是否存在。预处理后生成一个新的解析树。此外它还可以进行扩展字段比如将select *中的*符号扩展为表上的所有列5优化器这是 MySQL 的“大脑”。当一个 SQL 有多种执行路径时优化器会决定使用哪一种。选择索引如果表有多个索引决定用哪个。多表关联Join决定表的连接顺序先查小表还是先查大表。目的寻找执行成本最低通常是 IO 最少的方案生成执行计划。由于距离学 MySQL 已经过去好几个月了早就已经忘记了 B 树索引、回表、覆盖索引这些概念了所以当时看上图的案例有点晕如果大家有相同的感觉可以跟我在下面一起复习Q两种 B 树索引的区别在 InnoDB 存储引擎中根据叶子节点存放内容的不同索引分为两类主键索引的 B 树又称聚簇索引 - Clustered Index键值表的主键id。叶子节点存什么存放的是完整的整行行记录。特点索引即数据。只要找到了主键id就能直接获取到这行数据的所有字段product_no,name,price等。二级索引的 B 树又称辅助索引 - Secondary Index键值你在哪列建索引键值就是哪列。比如图片中的name。叶子节点存什么存放的是对应记录的主键值。特点它不包含整行数据。如果你通过name查到了某条记录你只能顺便得到它的id。Q什么是“回表”通常情况下如果你执行SELECT price FROM product WHERE name apple;查询会在二级索引 (name)的 B 树中找到apple。从二级索引的叶子节点拿到对应的主键 id假设是 1。关键动作拿着id1再去主键索引的 B 树里查一遍为了拿到price字段。这个回到主键索引再查一遍的过程就叫“回表”。回表会增加磁盘 I/O降低性能。Q什么是覆盖索引覆盖索引并不是一种索引类型而是一种查询现象当一个索引包含覆盖了查询语句中需要的所有字段时MySQL 就可以直接从这个索引中返回数据而不需要回表。结合你图片中的例子查询语句SELECT id FROM product WHERE id 1 AND name LIKE i%;查询需要的字段id在 SELECT 里和name在 WHERE 里。二级索引name的内容它本身是按name排序的且它的叶子节点里存的就是id。结论这个name索引已经包含了你查询所需要的全部信息name和id。此时MySQL 引擎只需要扫描name这棵 B 树就能过滤出满足i%条件的记录并且直接从这棵树里把id拿出来返回。它根本不需要去翻主键索引那棵树。这就叫“覆盖索引”。复习了上述概念我们也就能明白为什么优化器最后帮我们决定使用普通索引做覆盖索引优化。而不是说直接查主键索引。一来是主键索引的叶子节点存储的信息过多查询主键索引导致的磁盘 I/O 也就更多自然更耗时间相较之下二级索引的叶子节点里面只存了主键和索引字段。另一个原因就是我们查的是主键ID它已经存在于二级索引的叶子节点了因此没必要回表节省了大笔开销。常见指令explain 查询 SQL 语句 // 输出这条 SQL 语句的执行计划6执行器开始执行 SQL。权限校验在执行前再次确认用户对该表是否有执行权限。调用接口根据优化器生成的执行计划循环调用存储引擎提供的 API 接口。索引下堆索引下堆是一种查询优化策略它能够减少二级索引查询过程中的回表操作其本质在于把应该由 Server 层做的判断交给了存储引擎层。在小林coding中案例如下使用索引下堆前后的关键我用红线标注了起来主要就是 reward 是否等于 1000000 这个判断由 Server 层转交给了存储引擎层它的好处在于不用为了让 Server 层判断这一条件而特意回表可以看到如果该条件不成立完全可以在存储引擎层就直接跳过该二级索引进而节省了一部分的回表操作开销。2.存储引擎层存储引擎是数据真正存放的地方。常见的引擎InnoDB默认支持事务、MyISAM。执行器每调用一次引擎接口引擎就会去磁盘或内存Buffer Pool中查找数据并将结果返回给执行器。