关于mysql:MySQL系列-select-查询语句到底是怎么执行的

4次阅读

共计 3585 个字符，预计需要花费 9 分钟才能阅读完成。

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;

要想了解这个问题就必须要晓得 mysql 的外部架构。为此，我画了张 mysql 的架构图（你也能够了解为 sql 查问语句的执行过程），如下所示：

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

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

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

首先，数据库要执行 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 新的权限才会失效。

连贯实现，如果后续没有做任何事件，这个连贯就处于闲暇状态。你能够用 show processlist; 命令查看 mysql 的连贯信息，如下图，我的数据库连贯都是 Sleep 状态的，除了执行 show processlist 操作的连贯。

如果客户端太长时间没有操作，此连贯将会主动断开。这个工夫默认是 8 小时，由参数 wait_timeout 管制。如果断开当前持续操作就会收到 “Lost connection to MySQL server during query” 的谬误。这时就必须重连能力执行申请。

数据库外面有长短连贯之分，长连贯：连贯胜利后一直有申请，就会始终应用同一连贯。短连贯：每次执行完几次申请就断开连接，下次须要再建设。

因为建设连贯是比拟耗时的操作，所以倡议应用长连贯。但这会有个问题 长连贯始终连着就会导致内存占用过大，被零碎强行沙雕。从而导致 MySQL 异样重启。如何解决呢？两个办法：

定期断开长连贯。应用特定工夫，或者程序判断执行一个占用内存大的操作后，断开连接。之后须要操作就重连。
mySQL 5.7 或以上版本，能够在每次执行一个占用内存大的操作后，执行 mysql_reset_connection 来从新连贯资源，此时不需重连或从新做权限认证，但会把连贯状态复原到刚创立完时。

连贯建设当前能够执行 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 及以上版本把查问缓存删掉了，之后再也没有这块性能了。

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

首先依据 “select” 辨认出这是查问语句。字符串 ”user” 辨认成 ” 表名 user”、字符串 ”id” 辨认成 ” 列名 id”。

之后进行 语法分析，它会依据输出的语句剖析是不是合乎 MySQL 的语法。具体表现就是 select、where、from 等关键字少了个字母，显著不合乎 MySQL 语法，这次就会报个语法错误的异样：它个别会提醒谬误行数，关注 ”use near” 前面即可。

过了分析器，就来到了优化器。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 值是否 = “ 狗哥 ”。

两种计划的执行后果是一样的，然而效率不一样、占用的资源也就不一样。优化器就是在抉择执行的计划。它优化的是索引应该用哪个？多表联查应该先查哪个表？怎么连贯等等。

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

开始执行，判断是否有相应的权限。比方该账户对 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/col…

本文通过一条简略的 SQL 查问语句，引出 MySQL 的构造以及这条 sql 查问语句的执行流程。置信你看完会对 SQL 有更深的了解。

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

正文完