MySQL45讲——学习极客时间MySQL实战45讲笔记—— 01 | 基础架构：一条SQL查询语句是如何执行的

📖 01 | 基础架构：一条SQL查询语句是如何执行的？

本文是 MySQL 实战 45 讲 (geekbang.org) 的学习笔记

请各位一定要支持作者大大，写的太棒了~

这里只是小付对文章学习的笔记~

也可以免费学习五小节哦~

📑 MySQL 的基础架构

我们为什么要从架构这里开始呢？

看一个事儿千万不要直接陷入细节里，你应该先鸟瞰其全貌，这样能够帮助你从高维度理解问题。同样，对于 MySQL 的学习也是这样。平时我们使用数据库，看到的通常都是一个整体。

比如，你有个最简单的表，表里只有一个 ID 字段，在执行下面这个查询语句时：

mysql> select * from T where ID=10;

我们看到的只是输入一条语句，返回一个结果，却不知道这条语句在 MySQL 内部的执行过程。

我们为什么不能将MySQL拆解成各个 零件 ，希望借由这个拆解过程，让你对 MySQL 有更深入的理解。这样当我们碰到 MySQL 的一些异常或者问题时，就能够直戳本质，更为快速地定位并解决问题。

MySQL的逻辑架构示意图

📚 总结：由图可知，咱们可以将MySQL分为 Server 层 和 存储引擎层 两大部分

🔖 Server 层

Server 层的组成部分

Server 层 包括了 连接器、查询缓存、分析器、优化器、执行器等，涵盖了MySQL的大多数 核心服务功能，以及内置函数（如 日期 、时间、数学和加密函数等），所有 跨存储引擎 的功能都在这一层实现 ， 比如存储过程 、 触发器 、 视图等。

🔖 存储引擎 层

存储引擎 层的作用与功能

存储引擎 层作用 主要是负责数据的存储和提取。

其架构模式 是类似于插件式的。支持 InnoDB 、MyISAM、Memory 等多个存储引擎。自 MySQL 5.5.5开始InnoDB成为了默认的存储引擎。

创建表时，如果不对引擎指定类型的话，默认采用的就是 InnoDB。当然我们可以在创建表的时候指定存储引擎的类型，engine = memory ，指定使用内存引擎创建该表。不同存储引擎的表数据存取方式不同，支持的功能也不同，在后面的文章中，我们会讨论到引擎的选择。

📚 注意：不同的存储引擎共用同一个 Server 层

📑 连接器

我们需要连接到数据库才可以对我们的数据库中的表进行相关操作，此时首先和我们打交道的就是连接器。

$ mysql -h$ip -P$port -u$user -p

$ip : 是指需要连接到数据库的 ip 地址

$port：是指对应数据库的端口号

$user：是指对应登录数据库中的哪个用户的用户名

连接命令中的 mysql 是客户端工具，用来跟服务端建立连接。在完成经典的 TCP 握手后，连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码。

📚 注意点一：如果用户名或者密码错误 则会在命令行爆出 Access denied for user 的错误提示。

📚 注意点二：如果用户名密码认证通过，连接器 此时会去 mysql.user 这个权限列表中查出你所拥有的权限。之后，这个连接里面的权限判断逻辑，都会依赖于此时读到的权限。

📚注意点三：一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

连接完成之后，如果没有后续的操作，此时该连接就处于空闲状态。

我们可以通过如下命令，查看当前进程列表

mysql> show processlist;

如图所示：其中 Command 显示为 Sleep 的行数据，表示当前系统中有一个空闲的连接。

客户端如果太长时间没动静，连接器就会自动将它断开。这个时间是由参数 wait_timeout 控制的，默认值是 8 小时。

📚 如果连接断开之后，客户端再次发送请求的话，会爆出一个错误提醒：Lost connection to MySQL server during query。 如果你要继续，就需要重连，然后再执行请求了。

长连接：是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。

短连接：是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

📚 注意：建立连接的过程通常是比较复杂的，所以我建议你在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。

🔖 问题：如果全部使用长连接，有时MySQL占用内存会涨的过快，其原因是？以及解决措施方案？

原因

MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。

解决方案

• MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。
• 定期断开长连接，在使用一段时间的长连接之后，或者程序当中判断执行过一个占用内存较大的查询时就断开连接，之后需要查询时再次重新连接。

📑 查询缓存

当我们在和连接器打完交道后，我们就进入到了 mysql 中的命令行之中了，此时我们就可以去执行 select 语句 进行我们所需要的数据查询了。 而在MySQL 的基础架构中 执行逻辑就会来到第二步：查询缓存。

MySQL 在接收到查询请求时，会先去 查询缓存中进行搜索，缓存中是否存在对应的查询请求。

📚 执行过的查询请求可能会在缓存中以 key-value的形式直接缓存在内存当中。

key ：是查询请求语句

value：是查询请求的结果集

如果查询请求可以直接命中查询缓存中的 key ，此时value 就会被直接返回给客户端。

🔖 问题：大多数情况下建议不要使用查询缓存，为什么？

原因：

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。

适用于：静态业务表，很长时间才会更新一次的表，或者系统配置表，此时的查询缓存就非常高效且实用。

📚注意点：MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。

📑 分析器

什么是分析器？作用

1.识别传进来的字符串——词法分析

2.根据传进来的字符串用语法规则进行判断——语法分析

3.如果有错误，将会收到 You have an error in your SQL syntax 的报错

📚 例子

mysql> select * from T where ID=10;

还是那条语句，此时分析器会将 select 识别出来，说明该语句是一条查询语句。同时 它会把 T 识别为 表名T ,把字符串 ID 识别为列 ID——词法分析过程

做完上述识别之后，会对识别的语句进行 MySQL 的语法分析，如果不符合 MySQL 的规范语法就会爆出You have an error in your SQL syntax 的错误提示。

📚 注意点：一般语法错误会提示第一个出现错误的位置，所以你要关注的是紧接“use near”的内容。

📑 优化器

优化器是干什么的？

通过分析器，明白你要干啥后，数据库就要针对你的需求想一个最优的解决方案，也就是执行计划，这个最优方案选择的操作，这个就是优化器要做的事情了。

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

📚 小结：实际上优化器就是对索引选择的优化以及连接顺序的优化

举例

mysql> select * from table1 join table2 using(ID)  where table1.c=10 and table2.d=20;

按照一般的思路猜想：

1. 我们可以通过先从 table1 中取出 c = 10 的所有记录 ID 数据，再根据 ID 值关联到 table2 ,再次判断 table2中d的值是否等于 d = 20
2. 我们也可以通过先从 table2 中取出 d = 20 的所有记录 ID 数据，再根据 ID 值关联到 table1 ,再次判断 table1中c的值是否等于 c = 20

📚 注意：上述二者逻辑结果都是一样的，但是执行效率不一致，而优化器的作用就是选择哪一种方案更快，就选用哪一个。

🔖 问题：优化器的优化原则是什么？

原则是：尽可能扫描少的数据库行纪录来进行优化，来选择对应的索引进行查询。

同时：优化器会改写sql，包括join的连接顺序，匹配索引，找到最优sql策略

📑 执行器

执行器用于真正意义上的执行语句

开始执行之前，会进行权限验证你对该表 T 是否有查询的功能，如果没有，就会返回没有权限的错误信息如：

mysql> select * from T where ID=10;

ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

📚 注意点：

权限验证不仅仅在执行器这部分会做，在分析器之后，也就是知道了该语句要“干什么”之后，也会先做一次权限验证。叫做precheck。而precheck是无法对运行时涉及到的表进行权限验证的，比如使用了触发器的情况。因此在执行器这里也要做一次执行时的权限验证。

权限验证过后，就会去找到对应的表进行执行啦~

打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

📚 小结：到了执行器的这个时候才会使用对应的存储引擎，首先会进入到数据库引擎，然后执行器也是通过调用数据库引擎的API来进行数据操作的。也因此数据库引擎才会是插件形式的。

mysql> select * from T where ID=10;

🔖 没有索引时的执行过程

• 调用 InnoDB 引擎接口取这个表的第一行，判断其对应的 ID 是否为 10 ，如果不是则跳过，如果是则将这行记录在结果集中。
• 调用引擎接口获取“下一行”，重复相同的判断逻辑，直到取到该表的最后一行。
• 执行器将上述遍历过程中所有满足条件的行组成的结果集返回给客户端。

📚注意：扫描全表不是存储引擎做的哦，反而是执行器做的。

上述是按照 Server 层 和 存储引擎 层来划分的，存储引擎层直接指的是 InnoDB 这个存储 引擎，在 InnoDB 中是以 page 为一个单位的，每次读取一个 page 的数据，并不是一行，而 Server 层是每次调用 InnoDB 存储引擎API接口来读取内存中 page 中的一行数据，然后再判断逻辑是否符合条件，如果符合就将其插入到结果集当中。

📚 优化减少 Server 层 与 存储引擎 层交互：

ICP(Index condition pushdown) 就是将 逻辑判断的条件操作放到存储引擎中去进行处理，这样一来便减少了Server 层 和 存储引擎层之间的交互了。

🔖 存在索引时的执行过程

对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。

慢查询日志中，会存在一个 rows_examined 字段，表示的是在执行器每次调用存储引擎获取数据行的时候就累加的。

说白了就是调用 存储引擎 API 获取数据的次数

在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。

📑 课后习题

如果表 T 中没有字段 k，而你执行了这个语句 select * from T where k=1, 那肯定是会报“不存在这个列”的错误： “Unknown column ‘k’ in ‘where clause’”。你觉得这个错误是在我们上面提到的哪个阶段报出来的呢？

答案：《高性能mysql》里提到解析器和预处理器。 解析器处理语法和解析查询, 生成一课对应的解析树。 预处理器进一步检查解析树的合法。比如: 数据表和数据列是否存在, 别名是否有歧义等。如果通过则生成新的解析树，再提交给优化器。

位于《高性能MYSQL》——6.4.3查询优化处理