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

1.与数据库建立连接

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

1.1 通信类型和连接方式

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

通信类型：同步或者异步

1）同步通信的特点：

2）异步跟同步相反：

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

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

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%';

有了连接数，怎么知道当前连接的状态？可以使用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自带的缓存的应用场景有限，需要满足以下两个要求：

所以缓存这一块，我们还是交给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的环节里面有个预处理器。它会检查生成的解析树，解决解析器无法解析的语义。比如，它会检查表和列名是否存在，检查名字和别名mysql原理，保证没有歧义。预处理之后得到一个新的解析树。

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

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

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

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

4.1 什么是优化器？

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

show status like 'Last_query_cost';

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!