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

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小时

---

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

show global status like 'Thread%';

• Threads_cached：缓存中的线程连接数
• Threads_connected：当前打开的连接数
• Threads_created：为处理连接创建的线程数
• Threads_running：非睡眠状态的连接数，通常指并发连接数

---

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

SHOW PROCESSLIST; 

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

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

---

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

show variables like 'max_connections';

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

set global max_connections=1000;

1.2 MySQL支持哪些通信协议

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

它不用通过网络协议，也可以连接到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。

1.3 MySQL采用什么通信方式

通信方式分为以下三种：

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

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

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

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

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

2.查询缓存

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

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

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

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

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

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

3. 语法解析和预处理

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

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

3.1 词法分析

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

select username from t_user where id = 1;

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

3.2 语法分析

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

任何数据库的中间件，比如 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官网关于这里的参考，想了解更多的同学可以看看

4.2 优化器可以做什么？

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

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

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

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

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

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

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

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

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

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

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

select * from information_schema.optimizer_trace\G;

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

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

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

那具体的优化计划在哪呢？在优化阶段（“join_optimization”）中的 considered_execution_plans

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

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

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

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

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

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

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

5.执行引擎、存储引擎

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

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

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

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

总结