MySQL 查询执行内幕-基础篇

发表于 更新于

0X00 前言

这篇博文概述了一次查询从一条 SQL 到拿到数据的过程，是掌握了基础的 CRUD 后想要进阶 MySQL 的一条必经路（当然我是说这个只知识是必经路，并不是我这篇文章）。如果有兴趣的话就继续看下去吧~

我们首先看一下下面这张图，其实并不复杂。我们很多人都已经知道了其中的一部分，比如缓存、存储引擎、数据这些。

图源自：高性能 MySQL

我们先来看一下粗略的流程

  1. 首先客户端发送一条查询给服务器；
  2. 服务器检查缓存，如果命中缓存则直接返回；否则继续执行；
  3. 服务器解析 SQL、预处理、由优化器生成执行计划；
  4. MySQL 根据执行计划，调用存储引擎 API 执行查询；
  5. 将结果返回给客户端；

虽然每一条都比上面描述的、比我们想象的要复杂得多。但是因为这里只是基础篇，所以我们只是了解一下基础流程和原理就好，如果需要深入了解某部分的细节的话，可以查阅更详细更深层的资料。

0X01 客户端 <-> 服务器

首先我们需要知道的一点是：客户端和服务器之间的通信是半双工的。这一点其实就能解释我们工作中遇到的一个问题：当我们主动发起一个查询请求后，并不能再次主动 cancel，只能等待查询结束。

大家学过计算机网络都应该知道，半双工就意味着要么发送数据、要么接收数据，并不能两件事一起做

还有需要注意的一点，我们“理所当然”地以为客户端发起连接，推送 SQL 到服务器，等待处理再将结果拉回客户端。但是这样其实并不对，实际上是 MySQL 服务器在向客户端推数据，再加上半双工的限制导致客户端在接收数据的时候只能等待数据全部推送完毕。

我们初步了解的时候并不需要学习整个客户端和服务器交互协议的详细内容，所以了解到这里就差不多了，我们知道“半双工”和“推拉”基本就能解决一些初级问题了。

然后一个与此相关的是“查询状态”。我们都知道show full processlist可以看到当前系统中运行的查询的状态，但是有些状态是不能望文生义的，还是需要了解一下具体到底是什么含义

Sleep：等待客户端发来请求；Query：正在查询或者正在将结果发送给客户端；Locked：被锁住了，正在等待表锁（存储引擎级别的锁并不会显示出来，例如 InnoDB 中的行锁）；Analyzing and statistics：正在收集存储引擎的统计信息，生成查询的执行计划；Copying to temp table[on disk]：正在查询，并将届国际复制到临时表中（通常是在 group by，或者 Union 或者文件排序），标记了 on disk就标识当前在磁盘上操作；sorting result：正在对结果排序；sending data：可能是多个状态间传递数据、或者在生成结果集或者在向客户端返回数据。

其他的都还好，重点就在与Query和sending data，之前很多人以为Query就是正在查询，sending data就是发送数据回到客户端，这其实是不对的。

0X02 查询缓存

MySQL 自带就有一个查询缓存，但是比通常我们自己写的缓存要严格得多，MySQL 自己使用哈希做的缓存。也就是说，当缓存开启的情况下，每次来了一条 SQL 都会去计算这个 SQL 的哈希，然后拿哈希去对比，如果命中缓存就校验权限，权限没问题就直接返回数据了。

这里需要注意的一点是，MySQL 会有一个机制来控制缓存的刷新，这也是我上面提到“严格”的第一处表现：只要这个表被更新过，缓存就集体失效。因为如果数据存在延迟的话，一致性就不能得到保障，单机都保障不了一致性的话也就没法用了。

这里还需要注意的一点，也就是“严格”的第二处表现：因为是用 hash 做的缓存，所以只要你的 SQL 跟上次不完全一致，即使只差了一个字节，都会导致 hash 不同，最终不触发缓存。

0X03 查询优化

首先查询优化阶段会将 SQL 语句进行解析，得到一棵“解析树”，然后在配合预处理器的配合下最终将合法的解析树交给查询优化器。查询优化器会将其转化成“执行计划”，然后将执行计划交给下一步的“查询执行引擎”。

毕竟这一步是叫做“查询优化”的，是因为在这个过程中可以对查询进行一些优化，比如下面几种：

  1. 当手动写了一个比较烂的 JOIN 时，可能内部真实的关联顺序并没有按照你的 SQL 执行，而是自己寻找了一个更好的关联顺序（MySQL 都看不下去了 hhhhh）；
  2. 等价变换：比如有人写了3=3 AND age>18，就会直接在这个过程中把3=3给优化掉；还有这种(a<b AND b=c) AND a=5就会被优化成b>5 AND b=c AND a=5；
  3. 比如你在 InnoDB 表上给自增 id 建了索引，然后取MAX(id)，那优化器就会直接取到最大值（因为本来就是排好序的，没必要一个个去找）；

等等这些。当然这部分是整个查询里最复杂的部分，我自己了解的也不够多，所以只能说清楚这么多，再多的话我也是似懂非懂更不敢乱说了。不过这部分确实是比较重要的内容，大家有兴趣的话可以自行搜索更有深度的资料，或者直接去看《高性能 MySQL》就好了。

0X04 查询执行引擎

查询执行引擎拿到的是上一步传递过来的“执行计划”，这里的执行计划是一个数据结构。MySQL 只需要根据里面给出的指令逐步执行，执行的过程中会有很多操作需要调用存储引擎来实现（那可不嘛，要不数据从哪儿来）。具体调用会跟存储引擎相关，不同的存储引擎提供的 API 也不一样，这里就是 MySQL 主程序去调用存储引擎的 API 把最终的结果集查询出来。

0X05 返回结果给客户端

查询的最后一步当然就是将查到的结果返回给客户端，如果开启了缓存的话顺便再写一下缓存。

MySQL 返回数据是一个增量、逐步的过程。也就是说当生成第一条结果集数据的时候就开始返回了，这样可以保证服务端不用存储过多的数据，也可以让客户端更早得到结果。