《MySQL》系列 - select 查询语句到底是怎么执行的？

梦里梦外; 2022-11-14 11:59 197阅读 0赞

mysql 作为一个关系型数据库，在国内使用应该是最广泛的。也许你司使用 Oracle、Pg 等等，但是大多数互联网公司，比如我司使用得最多的还是 Mysql，重要性不言而喻。

事情是这样的，某天我司小胖问我执行**select \* from table**，数据库底层到底发生了啥？从而我们得到数据呢？以下把我给问住了，为此我查阅了大量的书籍、博客。于是就有了这篇文章。

假设现在我有张 user 表，只有两列，一列 id 自增的，一列 name 是 varchar 类型。建表语句是这样的：

CREATE TABLE IF NOT EXISTS `user`(
       `id` INT UNSIGNED AUTO_INCREMENT,
       `name` VARCHAR(100) NOT NULL,
       PRIMARY KEY ( `id` )
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;

小胖的问题就是下面这个语句的执行过程。

select * from user where id = 1;

# 01 mysql 架构概览 #

要想理解这个问题就必须要知道 mysql 的内部架构。为此，我画了张 mysql 的架构图（你也可以理解为 sql 查询语句的执行过程），如下所示：

![Mysql架构图][Mysql]

首先 msql 分为 server 层和存储引擎层两个部分。server 层包括四个功能模块，分别是：**连接器、查询缓存、优化器、执行器**。这一层负责了 mysql 的所有核心工作，比如：内置函数、存储过程、触发器以及视图等。

而存储引擎层则是负责**数据的存取**。注意，存储引擎在 mysql 是可选的，常见的还有：**InnoDB、MyISAM 以及 Memory等**，最常用的就是 InnoDB。现在默认的存储引擎也是它（从 mysql 5.5.5 版本开始），大家可以看到我上面的建表语句就是指定了 InnoDB 引擎。当然，你不指定的话默认也是它。

**由于存储引擎是可选的，所以 mysql 中，所有的存储引擎其实是共用一个 server层的**。回到正题，我们就以这张图的流程来解决一下小胖的问题。

## 1.1 连接器 ##

首先，数据库要执行 sql，肯定要先连接数据库吧。这部分工作就是由连接器完成。它负责**校验账户密码、获取权限、管理连接数，最终与客户端建立连接**等工作。mysql 链接数据库是这样写的：

mysql -h 127.0.0.1 -P 3306 -u root -p
    # 127.0.0.1 : ip 3306 : 端口 root : 用户名

运行命令之后需要输入密码，当然也可以跟在 -p 后面。不过不建议这么做，会有密码泄露的风险。

输入命令后，连接器**根据你的账户名密码验证身份**。这是会出现两种情况：

*  账号或密码不对，服务端会返回一个 “ERROR 1045 (28000): Access denied for user ‘root’@‘127.0.0.1’ (using password: YES)” 的错误，退出连接。
 *  验证通过，连接器就会到权限表查出你的权限。**之后你有啥权限都要通过这时读到的权限进行判断**。

注意，我说的是**此时查到的权限**。就算你用管理员账号修改了当前用户的权限，**此时已连接上的当前用户不受影响，必须要重启 mysql 新的权限才会生效**。

### 1.1.1 查看连接状态 ###

连接完成，如果后续没有做任何事情，这个连接就处于空闲状态。你可以用 **show processlist;** 命令查看 mysql 的连接信息，如下图，我的数据库连接都是 Sleep 状态的，除了执行 show processlist 操作的连接。

![show processlist;][show processlist]

### 1.1.2 控制连接 ###

如果客户端太长时间没有操作，此连接将会自动断开。这个时间默认是 8 小时，由参数 **wait\_timeout** 控制。如果断开以后继续操作就会收到 "Lost connection to MySQL server during query"的错误。这时就必须重连才能执行请求。

数据库里面有长短连接之分，长连接：连接成功后不断有请求，就会一直使用同一连接。短连接：每次执行完几次请求就断开连接，下次需要再建立。

由于建立连接是比较耗时的操作，所以建议使用长连接。但这会有个问题**长连接一直连着就会导致内存占用过大，被系统强行沙雕。从而导致 MySQL 异常重启**。如何解决呢？两个方法：

*  定期断开长连接。使用特定时间，或者程序判断执行一个占用内存大的操作后，断开连接。之后需要操作就重连。
 *  mySQL 5.7 或以上版本，可以在每次执行一个占用内存大的操作后，执行**mysql\_reset\_connection**来重新连接资源，此时不需重连或重新做权限认证，但会把连接状态恢复到刚创建完时。

## 1.2 查询缓存 ##

连接建立以后可以执行 select 语句了。这就会来到第二步：查询缓存。

查询缓存中存储的数据是 key-value 的形式，key 是查询语句，value 是查询的结果。逻辑是这样的：先看看查询缓存有没该语句对应的 value？有则直接取出返回客户端，无则继续到数据库执行语句。查出结果后会放一份到缓存中，再返回客户端。

你可能发现缓存真的香，但是并不建议使用查询缓存，因为有弊端。**查询缓存的失效非常频繁，只有某个表有更新。它马上失效了，对于经常更新的表来说，命中缓存的概率极低。它仅仅适用于那些不经常更新的表**。

而 MySQL 似乎也考虑到这点了。提供了 query\_cache\_type 参数，把它设置为 DEMAND 就不再适用韩村。而对于要使用缓存的语句则可用 SQL\_CACHE 显示指定，像这样：

select SQL_CACHE * from user where id = 1;

PS：MySQL 8.0 及以上版本把查询缓存删掉了，之后再也没有这块功能了。

## 1.3 分析器 ##

如果没有命中缓存就进入分析器，这里就是对 sql 进行分析。分析器会做**词法分析**。你输入的 sql 是啥，由啥组成，MySQL 都需要知道它们代表什么。

首先根据 “select” 识别出这是查询语句。字符串"user"识别成"表名 user"、字符串"id"识别成"列名id"。

之后进行**语法分析**，它会根据输入的语句分析是不是符合 MySQL 的语法。具体表现就是 select、where、from 等关键字少了个字母，明显不符合 MySQL 语法，这次就会报个语法错误的异常：它一般会提示错误行数，关注"use near"后面即可。

![语法错误][d1339abf94e9ff5ab5d0cc7ce57cadf7.png]

## 1.4 优化器 ##

过了分析器，就来到了优化器。MySQL 是个聪明的仔，再执行之前会自己优化下客户端传过来的语句，看看那种执行起来不那么占内存、快一点。比如下面的 sql 语句：

select * from user u inner join role r on u.id = r.user_id where u.name = "狗哥" and r.id = 666

它可以先从 user 表拿出 name = “狗哥” 记录的 ID 值再跟 role 表内连接查询，再判断 role 表里面 id 的值是否 = 666

也可以反过来：先从 role 表拿出 id = 666 记录的 ID 值再跟 user 表内连接查询，在判断 user 表里面的 name 值是否 = “狗哥”。

两种方案的执行结果是一样的，但是效率不一样、占用的资源也就不一样。**优化器就是在选择执行的方案。它优化的是索引应该用哪个？多表联查应该先查哪个表？怎么连接等等**。

## 1.5 执行器 ##

分析器知道了做啥、优化器知道了应该怎么做。接下来就交给执行器去执行了。

开始执行，判断是否有相应的权限。比如该账户对 user 表没权限就返回无权限的错误，如下所示：

select * from user where id = 1;
    
    ERROR 1142 (42000): SELECT command denied to user 'nasus'@'localhost' for table 'user'

PS：如果命中缓存没走到执行器这里，那么在返回查询结果时做权限验证。

回到正题，如果有权限，继续打开表执行。执行器会根据表定义的引擎去使用对应接口。比如我们上面的 sql 语句执行流程是这样的：

*  走 id 索引、调用 InnoDB 引擎取"满足条件的第一行"接口，再循环调用"满足条件的下一行"接口（这些接口都是存储引擎定义好的），直到表中不再有满足条件的行。执行器就将上述遍历得到的行组成结果集返回给客户端。
 *  对于 id 不是索引的表，执行器只能调用"取表记录的第一行"接口，再判断 id 是否 = 1。如果不是则跳过，是则存在结果集中；再调存储引擎接口取"下一行"，重复判断逻辑，直到表的最后一行。

至此，整个 SQL 的执行流程完毕，小胖懂了吗？

**巨人的肩膀**

*  https://time.geekbang.org/column/article/68319

### 总结 ###

本文通过一条简单的 SQL 查询语句，引出 MySQL 的结构以及这条 sql 查询语句的执行流程。相信你看完会对 SQL 有更深的理解。

### 小福利 ###

如果看到这里，喜欢这篇文章的话，请帮点个好看。微信搜索**一个优秀的废人**，关注后回复**电子书**送你 1000+ 本编程电子书 ，包括 C、C++、Java、Python、GO、Linux、Git、数据库、设计模式、前端、人工智能、面试相关、数据结构与算法以及计算机基础，详情看下图。回复 **1024** 送你一套完整的 java 视频教程。

![资源][3323892cf40d77aaf278896c2e9ffb04.png]

[Mysql]: /images/20221022/0f7b0b3a97c24c748a927bc77020912f.png
[show processlist]: /images/20221022/a54e40e7aaa24a1fb5eaa35055480e48.png
[d1339abf94e9ff5ab5d0cc7ce57cadf7.png]: /images/20221022/fd670b8354b6472099dd94e68b2c0c70.png
[3323892cf40d77aaf278896c2e9ffb04.png]: /images/20221022/faa47204faf24b27a661e25048c42753.png