MySQL中一条SQL查询语句是如何执行的-木庄网络博客

本文整理自网络，侵删。

前言

MySQL是典型的C/S架构（客户端/服务器架构），客户端进程向服务端进程发送一段文本（MySQL指令），服务器进程进行语句处理然后返回执行结果。

问题来了。服务器进程对客户端发送的请求究竟做了什么处理呢？本文以查询请求为例，讲解MySQL服务器进程的处理流程。

如下图所示，服务器进程在处理客户端请求的时候，大致需要进行3个步骤：

处理连接
解析与优化
存储引擎

接下来我们来详细了解一下这3步具体都做了什么。

1. 处理连接

客户端向服务器发送请求并最终收到响应，本质上是一个进程间通信的过程。

MySQL有专门用于处理连接的模块——连接器。

1.1 客户端和服务端的通信方式

1.1.1 TCP/IP协议

TCP/IP协议是MySQL客户端和服务器最常用的通信方式。

我们平时所说的MySQL服务器默认监听的端口是3306，这句话的前提是客户端进程和服务器进程使用的是TCP/IP协议进行通信。

我们在使用mysql命令启动客户端程序时，只要在-h参数后跟随IP地址作为服务器进程所在的主机地址，那么通讯方式便是TCP/IP协议。

如果客户端进程和服务器进程位于同一台主机，且要使用TCP/IP协议进行通信，则IP地址需要指定为127.0.0.1，而不能使用localhost

1.1.2 UNIX域套接字

如果客户端进程和服务器进程都位于类UNIX操作系统（MacOS、Centos、Ubuntu等）的主机之上，并且在启动客户端程序时没有指定主机名，或者指定的主机名为localhost，又或者指定了--protocol=socket的启动参数，那么客户端进程和服务器进程就会使用UNIX域套接字进行进程间通信。

MySQL服务器进程默认监听的UNIX域套接字文件为/temp/mysql.sock，客户端进程启动时也默认会连接到这个UNIX域套接字文件之上。

如果不明白UNIX域套接字到底是什么也没关系，只要知道这是进程之间的一种通讯方式就可以了，这里提及的主要目的是希望读者知晓MySQL客户端和进程通讯方式不止于TCP/IP协议

1.1.3 命名管道和共享内存

如果你的MySQL是安装在Windows主机之上，客户端和服务器进程可以使用命名管道和共享内存的方式进行通信。

不过使用这些通信方式需要在服务端和客户端启动时添加一些启动参数。

使用命名管道进行通信。需要在启动服务器时添加--enable-named-pipe参数，同时在启动客户端进程时添加--pipe或者--protocol=pipe参数
使用共享内存进行通信。需要在启动服务器时添加--shared-memory参数，启动成功后，共享内存便成为本地客户端程序的默认连接方式；也可以在启动客户端进程的命令中加上--protocol=memory参数明确指定使用共享内存进行通信

如果不明白命名管道和共享内存到底是什么没关系，只要知道这是进程之间的一种通讯方式就可以了，这里提及的主要目的是希望读者知晓MySQL客户端和进程通讯方式不止于TCP/IP协议

1.2 权限验证

确认通信方式并且成功建立连接之后，连接器就要开始验证你的身份了，使用的信息就是你的用户名和密码。

如果用户名或者密码错误，客户端连接会立即断开
如果用户名密码认证通过，连接器会到权限表里面查出当前登陆用户拥有的权限。之后这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

1.3 查看MySQL连接

每当一个客户端连接到服务端时，服务端进程都会创建一个单独的线程来处理当前客户端的交互操作。

那么如何查看MySQL当前所有的连接？

mysql>?show?global?status?like?'Thread%';

+-------------------+-------+
|?Variable_name?????|?Value?|
+-------------------+-------+
|?Threads_cached????|?0?????|
|?Threads_connected?|?1?????|
|?Threads_created???|?1?????|
|?Threads_running???|?1?????|
+-------------------+-------+

各字段含义如下表

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

建立连接之后，除非客户端主动断开连接，否则服务器会等待客户端发送请求。但是线程的创建和保持是需要消耗服务器资源的，因此服务器会把长时间不活动的客户端连接断开。

有2个参数控制这个自动断开连接的行为，每个参数都默认为28800秒，8小时。

--?非交互式超时时间，如JDBC连接
mysql>?show?global?variables?like?'wait_timeout';
+---------------+-------+
|?Variable_name?|?Value?|
+---------------+-------+
|?wait_timeout??|?28800?|
+---------------+-------+

--?交互式超时时间，如数据库查看工具Navicat等
mysql>?show?global?variables?like?'interactive_timeout';
+---------------------+-------+
|?Variable_name???????|?Value?|
+---------------------+-------+
|?interactive_timeout?|?28800?|
+---------------------+-------+

既然连接消耗资源，那是不是MySQL的最大连接数也有默认限制呢？没错！默认最大连接数为151。

mysql>?show?variables?like?'max_connections';
+-----------------+-------+
|?Variable_name???|?Value?|
+-----------------+-------+
|?max_connections?|?151???|
+-----------------+-------+

题外话：细心的读者可能会发现MySQL某些查询语句带有global关键字，这个关键字有什么含义呢？

MySQL的系统变量有两个作用范围（不区分大小写），分别是

GLOBAL（全局范围）：变量的设置影响服务器和所有客户端
SESSION（会话范围）：变量的设置仅影响当前连接（会话）

但是并非每个参数都具有两个作用范围，比如允许同时连接到服务器的客户端的数量max_connections就只有全局级别。

当没有带作用范围关键字时，默认是SESSION级别，包括查询和修改操作。

比如修改一个参数之后，在当前窗口生效了，但是在其他窗口却没有生效

show?VARIABLES?like?'autocommit';
set?autocommit?=?on;

因此，如果只是临时修改，请使用SESSION级别，如果需要当前设置在其他会话中生效，需要使用GLOBAL关键字。

到此为止，服务器进程已经和客户端进程建立了连接，下一步将处理客户端传来的请求了。

2. 解析与优化

服务器收到客户端传来的请求之后，还需要经过查询缓存、词法语法解析和预处理、查询优化的处理。

2.1 查询缓存

如果我们两次都执行同一条查询指令，第二次的响应时间会不会比第一次的响应时间短一些？

之前使用过Redis缓存工具的读者应该会有这个很自然的想法，MySQL收到查询请求之后应该先到缓存中查看一下，看一下之前是不是执行过这条指令。如果缓存命中，则直接返回结果；否则重新进行查询，然后加入缓存。

MySQL确实内部自带了一个缓存模块。

现在有一张500W行且没有添加索引的数据表，我执行以下命令两次，第二次会不会变得很快？

SELECT?*?FROM?t_user?WHERE?user_name?=?'蝉沐风'

并不会！说明缓存没有生效，为什么？MySQL默认是关闭自身的缓存功能的，查看一下query_cache_type变量设置。

mysql>?show?variables?like?'query_cache_type';
+------------------------------+---------+
|?Variable_name????????????????|?Value???|
+------------------------------+---------+
|?query_cache_type?????????????|?OFF?????|
+------------------------------+---------+

默认关闭就意味着不推荐，MySQL为什么不推荐用户使用自己的缓存功能呢？

MySQL自带的缓存系统应用场景非常有限，它要求SQL语句必须一模一样，多一个空格，变一个大小写都被认为是两条不同的SQL语句
缓存失效非常频繁。只要一个表的数据有任何修改，针对该表的所有缓存都会失效。对于更新频繁的数据表而言，缓存命中率非常低！

所以缓存的功能还是交给专业的ORM框架（比如MyBatis默认开启一级缓存）或者独立的缓存服务Redis更加适合。

MySQL8.0已经彻底移除了缓存功能

2.2 解析器 & 预处理器（Parser & Preprocessor）

现在跳过缓存这一步了，接下来需要做什么了？

如果我随便在客户端终端里输入一个字符串chanmufeng，服务器返回了一个1064的错误

mysql>?chanmufeng;
ERROR?1064?(42000):?You?have?an?error?in?your?SQL?syntax;?check?the?manual?that?corresponds?to?your?MySQL?server?version?for?the?right?syntax?to?use?near?'chanmufeng'?at?line?1

服务器是怎么判断出我的输入是错误的呢？这就是MySQL的Parser解析器的作用了，它主要包含两步，分别是词法解析和语法分析。

2.2.1 词法解析

以下面的SQL语句为例

SELECT?*?FROM?t_user?WHERE?user_name?=?'蝉沐风'?AND?age?>?3;

分析器先会做“词法分析”，就是把一条完整的SQL语句打碎成一个个单词，比如一条简单的SQL语句，会打碎成8个符号，每个符号是什么类型，从哪里开始到哪里结束。

MySQL 从你输入的SELECT这个关键字识别出来，这是一个查询语句。它也要把字符串t_user识别成“表名 t_user”，把字符串user_name识别成“列 user_name"。

2.2.2 语法分析

做完词法解析，接下来需要做语法分析了。

根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法，比如单引号是否闭合，关键词拼写是否正确等。

解析器会根据SQL语句生成一个数据结构，这个数据结构我们成为解析树。

我故意拼错了SELECT关键字，MySQL报了语法错误，就是在语法分析这一步。

mysql>?ELECT?*?FROM?t_user?WHERE?user_name?=?'蝉沐风'?AND?age?>?3;
ERROR?1064?(42000):?You?have?an?error?in?your?SQL?syntax;?check?the?manual?that?corresponds?to?your?MySQL?server?version?for?the?right?syntax?to?use?near?'ELECT?*?FROM?t_user?WHERE?user_name?=?'蝉沐风''?at?line?1

词法语法分析是一个非常基础的功能，Java 的编译器、百度搜索引擎如果要识别语句，必须也要有词法语法分析功能。

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