MySQL基础架构之查询语句执行流程

188次阅读

没有评论

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

这篇笔记主要记录 MySQL 的基础架构，一条查询语句是如何执行的。

比如，在我们从 student 表中查询一个 id= 2 的信息

select * from student where id=2;

在解释这条语句执行流程之前，我们看看 MySQL 的基础架构。

MySQL 基础架构之查询语句执行流程

图来自极客时间的 mysql 实践, 该图是描述的是 MySQL 的逻辑架构。

server 层包括连接器、查询缓存、分析器、优化器、执行器涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视等。
存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎，平常我们比较常用的是 innoDB 引擎

MySQL 基础架构之查询语句执行流程

连接器

我们在使用数据库之前，需要连接到数据库，连接语句是

mysql -h $ip -u $username -p $password

而我们的连接器就是处理这个过程的，连接器的主要功能是 负责跟客户端建立连接、获取权限、维持和管理连接，连接器在使用的过程中如果该用户的权限改变，是不会马上生效的，因为用户权限是在连接的时候读取的，只能重新连接才可以更新权限

连接器与客户端通信的协议是 tcp 协议的，连接以后可以使用 show processlist; 看到执行的连接数

MySQL 基础架构之查询语句执行流程

同时在连接时间内超过 8 小时是 sleep 的状态会自动断开，这个是 mysql 默认设置，如果一直不断开，那么这个过程可以叫做一个 长连接。

与之对应的有 短连接，短连接是指在执行一条或几条的以后断开连接。

当不断使用长连接的时候会占用很大的内存资源，在 mysql5.7 以后可以使用 mysql_reset_connection 语句来重新初始化资源。

查询缓存

经过连接以后，就连接上数据库了，这个时候可以执行语句了。

执行语句的时候，mysql 首先是去查询缓存，之前有没有执行过这样的语句，mysql 会将之前执行过的 语句和结果 以key-value的形式存储起来（当然有一定的存储和实效时间）。如果存在缓存，则直接返回缓存的结果。

缓存的工作流程是

服务器接收 SQL，以 SQL 和一些其他条件为 key 查找缓存表
如果找到了缓存，则直接返回缓存
如果没有找到缓存，则执行 SQL 查询，包括原来的 SQL 解析，优化等。
执行完 SQL 查询结果以后，将 SQL 查询结果缓存入缓存表

当然，如果这个表修改了，那么使用这个表中的所有缓存将不再有效，查询缓存值得相关条目将被清空。所以在一张被反复修改的表中进行语句缓存是不合适的，因为缓存随时都会实效，这样查询缓存的命中率就会降低很多，不是很划算。

当这个表正在写入数据，则这个表的缓存（命中缓存，缓存写入等）将会处于失效状态，在 Innodb 中，如果某个事务修改了这张表，则这个表的缓存在事务提交前都会处于失效状态，在这个事务提交前，这个表的相关查询都无法被缓存。

一般来说，如果是一张静态表或者是很少变化的表就可以进行缓存，这样的命中率就很高。

下面来说说缓存的使用时机，衡量打开缓存是否对系统有性能提升是一个很难的话题

通过缓存命中率判断, 缓存命中率 = 缓存命中次数 (Qcache_hits) / 查询次数 (Com_select)
通过缓存写入率, 写入率 = 缓存写入次数 (Qcache_inserts) / 查询次数 (Qcache_inserts)
通过命中 - 写入率判断, 比率 = 命中次数 (Qcache_hits) / 写入次数 (Qcache_inserts), 高性能 MySQL 中称之为比较能反映性能提升的指数, 一般来说达到 3:1 则算是查询缓存有效, 而最好能够达到 10:1

分析器

在查询缓存实效或者是无缓存的时候，这个时候 MySQL 的 server 就会利用分析器来分析语句，分析器也叫解析器。

MySQL 分析器由两部分组成，第一部分是用来词法分析扫描字符流, 根据构词规则识别单个单词，MySQL 使用 Flex 来生成词法扫描程序在 sql/lex.h 中定义了 MySQL 关键字和函数关键字, 用两个数组存储；第二部分的功能是语法分析在词法分析的基础上将单词序列组成语法短语, 最后生成语法树, 提交给优化器语法分析器使用 Bison, 在 sql/sql_yacc.yy 中定义了语法规则。然后根据关系代数理论生成语法树。

上面解释分析器太官方和复杂了，其实分析器主要是用来进行“词法分析”然后知道这个数据库语句是要干嘛，代表啥意思。

这个时候如果分析器分析出这个语句有问题的时候会报错，比如ERROR 1064 (42000): You have an error in your SQL syntax

优化器

在分析器分析完了以后知道这个语句是干嘛的时候，接下来是专门用一个优化器进行语句优化，优化器的任务是发现执行 SQL 查询的最佳方案。大多数查询优化器，包括 MySQL 的查询优化器，总或多或少地在所有可能的查询评估方案中搜索最佳方案。

优化器主要是选择一个最佳的执行方案，执行方案是为了减少开销，提高执行效率。

MySQL 的优化器是一个非常复杂的部件，它使用了非常多的优化策略来生成一个最优的执行计划：

重新定义表的关联顺序（多张表关联查询时，并不一定按照 SQL 中指定的顺序进行，但有一些技巧可以指定关联顺序）
优化 MIN()和 MAX()函数（找某列的最小值，如果该列有索引，只需要查找 B +Tree 索引最左端，反之则可以找到最大值，具体原理见下文）
提前终止查询（比如：使用 Limit 时，查找到满足数量的结果集后会立即终止查询）
优化排序（在老版本 MySQL 会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于 I / O 密集型应用，效率会高很多）

随着 MySQL 的不断发展，优化器使用的优化策略也在不断的进化，这里仅仅介绍几个非常常用且容易理解的优化策略而已。