【MySQL】运行原理（二）：查询 SQL 的执行过程分析

￡神魔★判官ぃ 2022-11-20 08:29 228阅读 0赞

## 1.与数据库建立连接 ##

首先，MySQL 必须要运行一个服务，监听默认的3306端口。在我们开发系统跟第三方对接的时候，必须要弄清楚的有两件事。

*  第一个就是通信协议，比如我们是用HTTP还是WebService还是TCP？
 *  第二个是消息格式，比如我们用XML格式，还是JSON格式，还是定长格式？报文头长度多少，包含什么内容，每个字段的详细含义？

### 1.1 通信类型和连接方式 ###

MySQL 是支持多种通信协议的，可以使用同步/异步的方式，支持长连接/短连接。这里我们拆分来看。

**通信类型：同步或者异步**

1）同步通信的特点：

*  同步通信依赖于被调用方，受限于被调用方的性能。也就是说，应用操作数据库，线程会阻塞，等待数据库的返回
 *  一般只能做到一对一，很难做到一对多的通信

2）异步跟同步相反：

*  异步可以避免应用阻塞等待，但是不能节省SQL执行的时间
 *  如果异步存在并发，每一个SQL的执行都要单独建立一个连接，避免数据混乱。但是这样会给服务端带来巨大的压力（一个连接就会创建一个线程，线程间切换会占用大量CPU资源）。另外异步通信还带来了编码的复杂度，所以一般不建议使用。如果要异步，必须使用连接池，排队从连接池获取连接而不是创建新连接

一般来说我们连接数据库都是同步连接。

**连接方式：长连接或者短连接**

MySQL既支持短连接，也支持长连接。短连接就是操作完毕以后，马上close 掉。长连接可以保持打开，减少服务端创建和释放连接的消耗，后面的程序访问的时候还可以使用这个连接。一般我们会在连接池中使用长连接。

保持长连接会消耗内存。长时间不活动的连接，MySQL服务器会断开。那这个超时时间怎么查看呢？

show global variables like 'wait_timeout'; -- 非交互式超时时间，如 JDBC 程序 
    show global variables like' interactive_timeout'; -- 交互式超时时间，如数据库工具

执行结果如下图。默认都是28800秒，8小时

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TkdvrRHQ-1603815339151)(Untitled.assets/image-20201027132844956.png)\]][img-TkdvrRHQ-1603815339151_Untitled.assets_image-20201027132844956.png]

--------------------

我们怎么查看MySQL当前有多少个连接？可以用show status命令：

show global status like 'Thread%';

![在这里插入图片描述][20201028002141158.png_pic_center]

*  Threads\_cached：缓存中的线程连接数
 *  Threads\_connected：当前打开的连接数
 *  Threads\_created：为处理连接创建的线程数
 *  Threads\_running：非睡眠状态的连接数，通常指并发连接数

--------------------

有了连接数，怎么知道当前连接的状态？可以使用show processlist命令，（root用户）查看SQL的执行状态。

SHOW PROCESSLIST;

![在这里插入图片描述][20201028002200728.png_pic_center]

从上面的Threads\_connected可以看到当前有4个连接，所以这里的显示了4个连接状态。那Command这一列是什么意思呢？一些常见的状态：

<table> 
 <thead> 
  <tr> 
   <th>状态</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Sleep</td> 
   <td>线程正在等待客户端，以向它发送一个新语句</td> 
  </tr> 
  <tr> 
   <td>Query</td> 
   <td>线程正在执行查询或往客户端发送数据</td> 
  </tr> 
  <tr> 
   <td>Locked</td> 
   <td>该查询被其它查询锁定</td> 
  </tr> 
  <tr> 
   <td>Copying to tmp tableondisk</td> 
   <td>临时结果集合大于 tmp_table_size。线程把临时表从存储器内部格式改 变为磁盘模式，以节约存储器</td> 
  </tr> 
  <tr> 
   <td>Sendingdata</td> 
   <td>线程正在为 SELECT 语句处理行，同时正在向客户端发送数据</td> 
  </tr> 
  <tr> 
   <td>Sortingforgroup</td> 
   <td>线程正在进行分类，以满足 GROUPBY 要求</td> 
  </tr> 
  <tr> 
   <td>Sortingfororder</td> 
   <td>线程正在进行分类，以满足 ORDERBY 要求</td> 
  </tr> 
 </tbody> 
</table>

--------------------

还有个问题，MySQL服务允许的最大连接数是多少呢？

show variables like 'max_connections';

![在这里插入图片描述][2020102800221538.png_pic_center]

在5.7版本中默认是151个，最大可以设置成16384（2^14）。

set global max_connections=1000;

![在这里插入图片描述][20201028002228816.png_pic_center]

### 1.2 MySQL支持哪些通信协议 ###

第一种是Unix Socket。比如我们在Linux服务器上，如果没有指定-h参数，它就用socket方式登录。如下图（省略  
了-S /var/lib/mysql/mysql.sock）

![在这里插入图片描述][20201028002243322.png_pic_center]

它不用通过网络协议，也可以连接到MySQL的服务器，它需要用到服务器上的一个物理文件（/var/lib/mysql/mysql.sock）

select @@socket;

--------------------

如果有参数-h指定主机，就会用第二种方式，TCP/IP协议。

mysql -h192.168.8.211 -uroot -p123456

我们的编程语言的连接模块都是用 TCP 协议连接到 MySQL 服务器的，比如 mysql-connector-java-x.x.xx.jar。

![在这里插入图片描述][20201028002300565.png_pic_center]

### 1.3 MySQL采用什么通信方式 ###

通信方式分为以下三种：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center]

*  单工：在两台计算机通信的时候，数据的传输是单向的。比如遥控器
 *  半双工：在两台计算机之间，数据传输是双向的，你可以给我发送，我也可以给你发送，但是在这个通讯连接里面，同一时间只能有一台服务器在发送数据，也就是你要给我发的话，也必须等我发给你完了之后才能给我发。比如对讲机
 *  全双工：数据的传输是双向的，并且可以同时传输。比如打电话

那MySQL应该采用哪种通信方式呢？半双工的通信方式，因为客户端与服务端肯定是双向通信的，而且要么是客户端向服务端发送数据，要么是服务端向客户端发送数据，这两个动作不能同时发生。

客户端发送SQL语句给服务端的时候，（在一次连接里面）数据是不能分成小块发送的，不管你的SQL语句有多大，都是一次性发送。比如我们用MyBatis动态SQL生成了一个批量插入的语句， 插入10万条数据， values后面跟了一长串的内容，或者where条件in里面的值太多，会出现问题。这个时候我们必须要调整MySQL服务器配置 max\_allowed\_packet 参数的值（默认是4M），把它调大，否则就会报错。

![在这里插入图片描述][20201028002331810.png_pic_center]

另一方面，对于服务端来说，也是一次性发送所有的数据，不能因为你已经取到了想要的数据就中断操作，这个时候会对网络和内存产生大量消耗。所以，我们一定要在程序里面避免不带limit 的这种操作，比如一次把所有满足条件的数据全部查出来，一定要先count一下。如果数据量的话，可以分批查询。

执行一条查询语句，客户端跟服务端建立连接之后呢？下一步要做什么？

## 2.查询缓存 ##

MySQL内部自带了一个缓存模块。缓存的作用我们应该很清楚了，把数据以KV的形式放到内存里面，可以加快数据的读取速度，也可以减少服务器处理的时间。但是MySQL的缓存我们好像比较陌生，从来没有去配置过，也不知道它什么时候生效？假如 t\_user 表有500万行数据，没有索引。我们在没有索引的字段上执行同样的查询，大家觉得第二次会快吗？

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 1]

可以看到MySQL缓存是默认关闭的。默认关闭的意思就是不推荐使用，为什么MySQL不推荐使用它自带的缓存呢？

主要是因为MySQL自带的缓存的应用场景有限，需要满足以下两个要求：

*  它要求SQL语句必须一模一样，中间多一个空格，字母大小写不同都被认为是不同的的SQL。
 *  表里面任何一条数据发生变化的时候，这张表所有缓存都会失效，所以对于有大量数据更新的应用，也不适合。

所以缓存这一块，我们还是交给ORM框架（比如MyBatis默认开启了一级缓存），或者独立的缓存服务，比如Redis来处理更合适。在MySQL 8.0中，查询缓存已经被移除了。

我们没有使用缓存的话，就会跳过缓存的模块，下一步我们要做什么呢？

## 3. 语法解析和预处理 ##

这里我会有一个疑问，为什么我的一条SQL语句能够被识别呢？假如我随便执行一个字符串penyuyan，服务器报了一个1064的错：

![在这里插入图片描述][2020102800240345.png_pic_center]

它是怎么知道我输入的内容是错误的？这个就是MySQL的Parser解析器和Preprocessor预处理模块。这一步主要做的事情是对语句基于SQL语法进行词法和语法分析和语义的解析。

### 3.1 词法分析 ###

词法分析就是把一个完整的SQL语句打碎成一个个的单词。比如一个简单的SQL语句：

select username from t_user where id = 1;

它会打碎成8个符号，每个符号是什么类型，从哪里开始到哪里结束。

### 3.2 语法分析 ###

第二步就是语法分析，语法分析会对SQL做一些语法检查，比如单引号有没有闭合，然后根据MySQL定义的语法规则，根据SQL语句生成一个数据结构。这个数据结构我们把它叫做解析树（select\_lex）。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 2]

任何数据库的中间件，比如 Mycat，Sharding-JDBC（用到了Druid Parser），都必须要有词法和语法分析功能，在市面上也有很多的开源的词法解析的工具（比如LEX，Yacc）。

### 3.3 预处理器 ###

问题：如果我写了一个词法和语法都正确的SQL，但是表名或者字段不存在，会在哪里报错？是在数据库的执行层还是解析器？比如：

select * from penyuyan;

解析器可以分析语法，但是它怎么知道数据库里面有什么表，表里面有什么字段呢？实际上还是在解析的时候报错，解析SQL的环节里面有个预处理器。它会检查生成的解析树，解决解析器无法解析的语义。比如，它会检查表和列名是否存在，检查名字和别名，保证没有歧义。预处理之后得到一个新的解析树。

## 4.查询优化得到执行计划 ##

得到解析树之后，是不是执行SQL语句了呢？这里我们有一个问题，一条SQL语句是不是只有一种执行方式？或者说数据库最终执行的SQL是不是就是我们发送的SQL？

这个答案是否定的。一条SQL语句是可以有很多种执行方式的，最终返回相同的结果，他们是等价的。但是如果有这么多种执行方式，这些执行方式怎么得到的？最终选择哪一种去执行？根据什么判断标准去选择？

这个就是MySQL的查询优化器的模块（Query Optimizer）。

### 4.1 什么是优化器？ ###

查询优化器的目的就是根据解析树生成不同的执行计划（ExecutionPlan），然后选择一种最优的执行计划。MySQL里面使用的是基于开销（cost）的优化器，哪种执行计划开销最小，就用哪种。可以使用这个命令查看查询的开销：

show status like 'Last_query_cost';

mysql官网关于这里的[参考][Link 1]，想了解更多的同学可以看看

### 4.2 优化器可以做什么？ ###

MySQL的优化器能处理哪些优化类型呢？举两个简单的例子：

1.  当我们对多张表进行关联查询的时候，以哪个表的数据作为基准表
2.  有多个索引可以使用的时候，选择哪个索引

实际上，对于每一种数据库来说，优化器的模块都是必不可少的，他们通过复杂的算法实现尽可能优化查询效率的目标。如果对于优化器的细节感兴趣，可以看看《数据库查询优化器的艺术-原理解析与SQL性能优化》。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 3]

但是优化器也不是万能的，并不是再垃圾的SQL语句都能自动优化，也不是每次都能选择到最优的执行计划，大家在编写SQL语句的时候还是要注意。

如果我们想知道优化器是怎么工作的，它生成了几种执行计划，每种执行计划的cost是多少，应该怎么做？

### 4.3 优化器得到执行计划的过程？ ###

首先，我们要启用优化器的追踪（默认是关闭的）：

SHOW VARIABLES LIKE 'optimizer_trace'; 
    set optimizer_trace = 'enabled=on';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 4]

注意，开启这开关是会消耗性能的，因为它要把优化分析的结果写到表里面，所以不要轻易开启，或者查看完之后关闭它（改成off）。接着我们执行一个SQL语句，优化器会生成执行计划（下面是一个两表联查）：

SELECT d.username,i.phone from user_info i,user_detail d WHERE i.id=d.user_id;

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oSXC0mOj-1603815339177)(Untitled.assets/image-20201027153751514.png)\]][img-oSXC0mOj-1603815339177_Untitled.assets_image-20201027153751514.png]

这个时候优化器分析的过程已经记录到系统表里面了，我们可以查询

select * from information_schema.optimizer_trace\G;

如果是直接在Navicat中执行，那么得到结果只是短短一行数据，并没有完整的执行计划

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Aps4PSkV-1603815339179)(Untitled.assets/image-20201027154026350.png)\]][img-Aps4PSkV-1603815339179_Untitled.assets_image-20201027154026350.png]

所以，此处应该是在命令行中执行：

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GjjuxeGM-1603815339180)(Untitled.assets/image-20201027164718018.png)\]][img-GjjuxeGM-1603815339180_Untitled.assets_image-20201027164718018.png]

得到的优化器分析的过程是一个JSON类型的数据，主要分成三部分，准备阶段、优化阶段和执行阶段。

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lZdHzaii-1603815339181)(Untitled.assets/image-20201027165927031.png)\]][img-lZdHzaii-1603815339181_Untitled.assets_image-20201027165927031.png]

那具体的优化计划在哪呢？在优化阶段（“join\_optimization”）中的 considered\_execution\_plans

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pz0FOjyN-1603815339183)(Untitled.assets/image-20201027170451709.png)\]][img-pz0FOjyN-1603815339183_Untitled.assets_image-20201027170451709.png]

最后，分析完记得关掉它：

set optimizer_trace="enabled=off"; 
    SHOWVARIABLESLIKE'optimizer_trace';

优化完之后，得到一个什么东西呢？

### 4.4 如何查看最终执行计划？ ###

优化器最终会把解析树变成一个查询执行计划，查询执行计划是一个数据结构。

当然，这个执行计划是不是一定是最优的执行计划呢？不一定，因为MySQL也有可能覆盖不到所有的执行计划。我们怎么查看MySQL的执行计划呢？比如多张表关联查询，先查询哪张表？在执行查询的时候可能用到哪些索引，实际上用到了什么索引？

MySQL提供了一个执行计划的工具。我们在SQL语句前面加上EXPLAIN，就可以看到执行计划的信息。

## 5.执行引擎、存储引擎 ##

得到执行计划以后，SQL语句是不是终于可以执行了？问题又来了：

1.  执行计划在哪里执行？是谁去执行？答：执行引擎
2.  执行引擎要获取数据时，从逻辑的角度来说，我们的数据是放在哪里的，或者说放在一个什么结构里面？答：存储引擎

总的来说，执行引擎利用存储引擎提供的相应的API来完成操作。最后把数据返回给客户端，即使没有结果也要返回。

> PS：为什么就算修改了表的存储引擎，操作方式不需要做任何改变？因为不同功能的存储引擎实现的API是相同的。

## 总结 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 5]

[img-TkdvrRHQ-1603815339151_Untitled.assets_image-20201027132844956.png]: /images/20221120/e4deefef865243efb3cca9337ceeca99.png
[20201028002141158.png_pic_center]: /images/20221120/2cac660fc0c947a4b9beaf6a3544a882.png
[20201028002200728.png_pic_center]: /images/20221120/1db66ca319584ccfae938d5a5a05304d.png
[2020102800221538.png_pic_center]: /images/20221120/5ddab75f34964e6d8e32eb3ce3889a17.png
[20201028002228816.png_pic_center]: /images/20221120/d102b34f65eb4d398532f1bb79e416d9.png
[20201028002243322.png_pic_center]: /images/20221120/758bd44006dc4e11b37c89b655b67905.png
[20201028002300565.png_pic_center]: /images/20221120/172e410ca5bb4045bfd8355aeb8d7c47.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/1405f6a8845c4292966c3a1e0b50f215.png
[20201028002331810.png_pic_center]: /images/20221120/480327b9726c44c2b4e5dfa65a2d75ce.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/34a1c800f73a4098bff9f04ccd7c4f7a.png
[2020102800240345.png_pic_center]: /images/20221120/ef0dbeda005848f7812ecb7b670cd732.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221120/132107d236c84bb0b6c3014df27018f5.png
[Link 1]: https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221120/d59fc4f9f2014f96abfa9c6d2c8c7a59.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221120/5cf77c25e2d544968dc1351744312ae2.png
[img-oSXC0mOj-1603815339177_Untitled.assets_image-20201027153751514.png]: /images/20221120/212692e126c44d3390e2cd16b0af4c14.png
[img-Aps4PSkV-1603815339179_Untitled.assets_image-20201027154026350.png]: /images/20221120/99d3dc45bcc74517808f004625011c18.png
[img-GjjuxeGM-1603815339180_Untitled.assets_image-20201027164718018.png]: /images/20221120/08eb25b7b20b47a78cba65e673125969.png
[img-lZdHzaii-1603815339181_Untitled.assets_image-20201027165927031.png]: /images/20221120/c8b079b68c254875b2b2cefb01988ff7.png
[img-pz0FOjyN-1603815339183_Untitled.assets_image-20201027170451709.png]: /images/20221120/32a714216f7f4e2d8bd5b8cefa581e07.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw_size_16_color_FFFFFF_t_70_pic_center 5]: https://img-blog.csdnimg.cn/2020102800202620.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzkzNTkyNw==,size_16,color_FFFFFF,t_70#pic_center