一条SQL查询语句是如何执行的？

2022-06-08 13:47:57 浏览数 (1)

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当前所有的连接？

代码语言：javascript复制
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小时。

代码语言：javascript复制
-- 非交互式超时时间，如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。

代码语言：javascript复制
mysql> show variables like 'max_connections';
 ----------------- ------- 
| Variable_name   | Value |
 ----------------- ------- 
| max_connections | 151   |
 ----------------- -------

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

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

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

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

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

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

代码语言：javascript复制
show VARIABLES like 'autocommit';
set autocommit = on;

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

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

2. 解析与优化

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

2.1 查询缓存

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

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

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

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

代码语言：javascript复制
SELECT * FROM t_user WHERE user_name = '蝉沐风'

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

代码语言：javascript复制
mysql> show variables like 'query_cache_type';
 ------------------------------ --------- 
| Variable_name                | Value   |
 ------------------------------ --------- 
| query_cache_type             | OFF     |
 ------------------------------ ---------

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

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

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

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

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

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

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

代码语言：javascript复制
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语句为例

代码语言：javascript复制
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报了语法错误，就是在语法分析这一步。

代码语言：javascript复制
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等。

2.2.3 预处理器

如果我们写了一条语法和词法都没有问题的SQL，但是字段名和表名却不存在，这个错误是在哪一个阶段爆出的呢？

词法解析和语法分析是无法知道数据库里有什么表，有哪些字段的。要知道这些信息还需要解析阶段的另一个工具——预处理器。

它会检查生成的解析树，解决解析器无法解析的语义。比如，它会检查表和列名是否存在，检查名字和别名，保证没有歧义。预处理之后得到一个新的解析树。

本质上，解析和预处理是一个编译过程，涉及到词法解析、语法和语义分析，更多细节我们不会探究，感兴趣的读者可以看一下编译原理方面的书籍。

2.3 查询优化器（Optimizer）与查询执行计划

到了这一步，MySQL终于知道我们想查询的表和列以及相应的搜索条件了，是不是可以直接进行查询了？

还不行。MySQL作者担心我们写的SQL太垃圾，所以有设计出一个叫做查询优化器的东东，辅助我们提高查询效率。

2.3.1 什么是查询优化器？

一条 SQL语句是不是只有一种执行方式？或者说数据库最终执行的 SQL是不是就是我们发送的 SQL?

不是。一条 SQL 语句是可以有很多种执行方式的，最终返回相同的结果，他们是等价的。

举一个非常简单的例子，比如你执行下面这样的语句：

代码语言：javascript复制
SELECT * FROM t1, t2 WHERE t1.id = 10 AND t2.id = 20
  • 既可以先从表 t1 里面取出 id=10 的记录，再根据 id 值关联到表 t2，再判断 t2 里面 id 的值是否等于 20。
  • 也可以先从表 t2 里面取出 id=20 的记录，再根据 id 值关联到表 t1，再判断 t1 里面 id 的值是否等于 10。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，如果有这么多种执行方式，这些执行方式怎么得到的？最终选择哪一种去执行？根据什么判断标准去选择？

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

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

2.3.2 优化器究竟做了什么？

举两个简单的例子∶

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

实际上，对于每一种数据库来说，优化器的模块都是必不可少的，他们通过复杂的算法实现尽可能优化查询效率。

往细节上说，查询优化器主要做了下面几方面的优化：

  • 子查询优化
  • 等价谓词重写
  • 条件化简
  • 外连接消除
  • 嵌套连接消除
  • 连接消除
  • 语义优化

本文不会对优化的细节展开讲解，大家先对MySQL的整体架构有所了解就可以了，具体细节之后单独开篇介绍

但是优化器也不是万能的，如果SQL语句写得实在太垃圾，再牛的优化器也救不了你了。因此大家在编写SQL语句的时候还是要有意识地进行优化。

2.3.3 执行计划

优化完之后，得到一个什么东西呢？优化器最终会把解析树变成一个查询执行计划。

查询执行计划展示了接下来执行查询的具体方式，比如多张表关联查询，先查询哪张表，在执行查询的时候有多个索引可以使用，实际上该使用哪些索引。

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

代码语言：javascript复制
mysql> EXPLAIN SELECT * FROM t_user WHERE user_name = '';
 ---- ------------- -------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
 ---- ------------- -------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
|  1 | SIMPLE      | t_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
 ---- ------------- -------- ------------ ------ --------------- ------ --------- ------ ------ ---------- -------------

如果要得到更加详细的信息，还可以用FORMAT=JSON，或者开启optimizer trace。

代码语言：javascript复制
mysql> EXPLAIN FORMAT=JSON SELECT * FROM t_user WHERE user_name = '';

文本不会带大家详细了解执行计划的每一个参数，内容很庞杂，大家先对MySQL的整体架构有所了解就可以了，具体细节之后单独开篇介绍

3. 存储引擎

经历千辛万苦，MySQL终于算出了最终的执行计划，然后就可以直接执行了吗？

好吧。。。依然还不可以。

我们知道，表是由一行一行的记录组成的，但这只是逻辑上的概念，或者说只是看上去是这样而已。

3.1 什么是存储引擎

到底该把数据存储在什么位置，是内存还是磁盘？怎么从表里读取数据，以及怎么把数据写入具体的表中，这都是存储引擎 负责的事情。

好吧，看到这里或许你还不知道存储引擎到底是什么。毕竟存储引擎这个名字听起来太玄乎了，它的前身叫做表处理器，是不是就接地气了许多呢？

3.2 为什么需要存储引擎

因为存储的需求不同。

试想一下：

  • 如果一张表，需要很高的访问速度，而不需要考虑持久化的问题，是不是最好把数据放在内存呢？
  • 如果一张表，是用来做历史数据存档的，不需要修改，也不需要索引，那是不是要支持数据的压缩?
  • 如果一张表用在读写并发很多的业务中，是不是要支持读写互不干扰，而且要保证比较高的数据一致性呢?

大家应该明白了，为什么要支持这么多的存储引擎，因为一种存储引擎不能提供所有的特性。

存储引擎是计算机抽象的典型代表，它的功能就是接受上层指令，然后对表中数据进行读取和写入，而这些操作对上层完全是屏蔽的。你甚至可以查阅MySQL文档定义自己的存储引擎，只要对外实现同样的接口就可以了。

存储引擎就是MySQL对数据进行读写的插件而已，可以根据不同目的随意更换（插拔）

3.3 存储引擎怎么用

3.3.1 创建表的时候指定存储引擎

在创建表的时候可以指定当前表的存储引擎，如果没有指定，默认的存储引擎为InnoDB，如果想显式指定存储引擎，可以这样

代码语言：javascript复制
CREATE TABLE `t_user_innodb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=innodb DEFAULT CHARSET=utf8mb4;
3.3.2 修改表的存储引擎
代码语言：javascript复制
ALTER TABLE 表名 ENGINE = 存储引擎名称;

3.4 存储引擎底层区别

下面我们分别创建3张设置了不同存储引擎的表，t_user_innodb、t_user_myisam、t_user_memory

我们看一下不同存储引擎在底层存储方面的差异，首先找到MySQL的数据存储目录

代码语言：javascript复制
mysql> show variables like 'datadir';
 --------------- ----------------- 
| Variable_name | Value           |
 --------------- ----------------- 
| datadir       | /var/lib/mysql/ |
 --------------- -----------------

进入到目标目录之后，找到当前数据库对应的目录（MySQL会为一个数据库创建一个同名的目录），数据库中表的存储结构如下

不同的存储引擎存放数据的方式不一样，产生的文件数量和格式也不一样，InnoDB文件包含2个，MEMORY文件包含1个，MYISAM文件包含3个。

3.5 常见存储引擎比较

首先我们查看一下当前MySQL服务器支持的存储引擎都有哪一些。

代码语言：javascript复制
mysql> SHOW ENGINES;
 -------------------- --------- -------------- ------ ------------ 
| Engine             | Support | Transactions | XA   | Savepoints |
 -------------------- --------- -------------- ------ ------------ 
| InnoDB             | DEFAULT | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | NO           | NO   | NO         |
| MEMORY             | YES     | NO           | NO   | NO         |
| BLACKHOLE          | YES     | NO           | NO   | NO         |
| MyISAM             | YES     | NO           | NO   | NO         |
| CSV                | YES     | NO           | NO   | NO         |
| ARCHIVE            | YES     | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | NO           | NO   | NO         |
| FEDERATED          | NO      | NULL         | NULL | NULL       |
 -------------------- --------- -------------- ------ ------------

其中，

  • Support表示该存储引擎是否可用；
  • DEFAULT表示当前MySQL服务器默认的存储引擎；
  • Transactions表示该存储引擎是否支持事务；
  • XA表示该存储引擎是否支持分布式事务；
  • Savepoints表示该存储引擎是否支持事务的部分回滚。
3.5.1 MylSAM

应用范围比较小，表级锁定限制了读/写的性能，因此在Web和数据仓库配置中，通常用于只读或以读为主的工作。

特点:

  • 支持表级别的锁（插入和更新会锁表），不支持事务；
  • 拥有较高的插入（insert）和查询（select）速度；
  • 存储了表的行数（count速度更快）。

怎么快速向数据库插入100万条数据？

可以先用MylSAM插入数据，然后修改存储引擎为InnoDB。

3.5.2 InnoDB

MySQL 5.7及更新版中的默认存储引擎。InnoDB是一个事务安全（与ACID兼容）的MySQL 存储引擎，它具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB行级锁（不升级为更粗粒度的锁）和Oracle风格的一致非锁读提高了多用户并发性。InnoDB将用户数据存储在聚集索引中，以减少基于主键的常见查询的I/O。为了保持数据完整性，InnoDB还支持外键引用完整性约束。

特点：

  • 支持事务，支持外键，因此数据的完整性、一致性更高；
  • 支持行级别的锁和表级别的锁；
  • 支持读写并发，写不阻塞读（MVCC）；
  • 特殊的索引存放方式，可以减少IO，提升査询效率。

番外：InnoDB本来是InnobaseOy公司开发的，它和MySQL AB公司合作开源了InnoDB的代码。但是没想到MySQL的竞争对手Oracle把InnobaseOy收购了。后来08年Sun公司（开发Java语言的Sun）收购了MySQL AB，09年Sun公司又被Oracle收购了，所以MySQL和 InnoDB又是一家了。有人觉得MySQL越来越像Oracle,其实也是这个原因。

3.5.3 Memory

将所有数据存储在RAM中，以便快速访问。这个引擎以前被称为堆引擎。

特点：

  • 把数据放在内存里面，读写的速度很快，但是数据库重启或者崩溃，数据会全部消失；
  • 只适合做临时表。
3.5.4 CSV

它的表实际上是带有逗号分隔值的文本文件。csv表允许以CSV格式导入或转储数据, 以便与读写相同格式的脚本和应用程序交换数据。因为CSV表没有索引，所以通常在正常操作期间将数据保存在InnoDB表中，只在导入或导出阶段使用csv表。

特点：

  • 不允许空行，不支持索引；
  • 格式通用，可以直接编辑，适合在不同数据库之间导入导出。
3.5.5 Archive

专用与存档，空间经过压缩，用于存储和检索大量很少引用的信息。

特点：

  • 不支持索引；
  • 不支持update、delete。

3.6 如何选择存储引擎

  • 如果对数据一致性要求比较高，需要事务支持，可以选择InnoDB。
  • 如果数据查询多更新少，对查询性能要求比较高，可以选择MyISAM。
  • 如果需要一个用于查询的临时表，可以选择Memory。

如果所有的存储引擎都不能满足你的需求，并且技术能力足够，可以根据官网内部手册用C语言开发一个存储引擎：https://dev.mvsql.com/doc/internals/en/custom-engine.html

数据库数据库 mysql sql

0 人点赞