你一定要知道的MySQL的运行机制

共 3529字，需浏览 8分钟

· 2021-06-07

每次当我们执行一条sql的时候，比如：

mysql> select * from T where ID=10；
复制代码

大家肯定会很好奇，这条mysql到底是怎么解析，从数据库查询到对应的数据的呢？这节课我跟大家一起解读一下MySQL的运行原理

大体来说，MySQL可以分为Server 层和存储引擎层两部分。

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

而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

也就是说，你执行 create table 建表的时候，如果不指定引擎类型，默认使用的就是 InnoDB。不过，你也可以通过指定存储引擎的类型来选择别的引擎，比如在 create table 语句中使用 engine=memory, 来指定使用内存引擎创建表。不同存储引擎的表数据存取方式不同，支持的功能也不同。不同的存储引擎，共有一个Server层，也就是从连接器到执行器的部分。下面，先从Server层开始讲起。

Server层

建立连接(Connectors&Connection Pool)

通过客户端/服务器通信协议与MySQL建立连接。MySQL 客户端与服务端的通信方式是 “ 半双工 ”。对于每一个 MySQL 的连接，时刻都有一个线程状态来标识这个连接正在做什么。

通讯机制：

全双工:能同时发送和接收数据，例如平时打电话。
半双工:指的某一时刻，要么发送数据，要么接收数据，不能同时。例如早期对讲机
单工:只能发送数据或只能接收数据。例如单行道

线程状态: show processlist; //查看用户正在运行的线程信息，root用户能查看所有线程，其他用户只能看自己的

查询缓存(Cache&Buffer)

连接建立完成之后，就可以执行select语句了，这时候就到了第二步逻辑：查询缓存了。这是MySQL一个可以优化的地方，如果开启了查询缓存且在查询缓存过程中查询到完全相同的SQL语句，则将查询结果直接返回给客户端，如果没有开启查询缓存或者没有查询到完全相同的SQL语句则会交给解析器进行语法语义解析，并生成"解析树"

show variables like '%query_cache%'; //查看查询缓存是否启用，空间大小，限制等

show status like 'Qcache%'; //查看更详细的缓存参数，可用缓存空间，缓存块，缓存多少等

这里查询缓存虽然是一个可以优化的点，但是我觉得查询缓存能别用还是尽量别用，因为查询缓存的弊大于利。

查询缓存的失效特别频繁，只要有对一个表的更新，这个表上所有的查询缓存都会清空。因此你可能前一秒存上了，后一秒就清空了。
对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。

好在 MySQL 也提供了这种“按需使用”的方式。你可以将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定，像下面这个语句一样

mysql> select SQL_CACHE * from T where ID=10；
复制代码

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

解析器(Parser)

如果没有命中缓存，或者查询缓存根本没有开启，就到了解析器这一步。这里解析器也可以叫做分析器，将客户端发送的SQL进行语法解析，生成"解析树"。

语法解析：一条SQL语句是由多个字符串和空格组成的，MySQL需要识别里面字符分别是什么，代表什么。

MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”。

做完了这些识别以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法

如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒，比如下面这个语句 select 少打了开头的字母“s”。


mysql> elect * from t where ID=1;

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 where ID=1' at line 1
复制代码

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

解析树：解析树是通过解析器来解析SQL的关键字和非关键字，比如select username from userinfo按照关键字和非关键字进行分类，生成树：

预处理器：根据一些mysql规则进一步检查解析树是否合法。如检查查询的表名、列名是否正确，是否有表的权限等

优化器(Optimizer)

根据解析树生成最优的执行计划。MySQL使用很多优化策略生成最优的执行计划，可以分为两类：静态优化（编译时的优化），动态优化（运行时的优化）

等价变换策略

5=5 and a>5 改成 a > 5
a < b and a=5 改成b>5 and a=5
基于联合索引，调整条件位置等

优化count、min、max等函数

InnoDB引擎min函数只需要找索引最左边
InnoDB引擎max函数只需要找索引最右边
MyISAM引擎count(*)，不需要计算，直接返回

提前终止查询

使用了limit查询，获取limit所需的数据，就不在继续遍历后面数据

in的优化

MySQL对in查询，会先进行排序，再采用二分法查找数据。比如where id in (2,1,3)，变

成 in (1,2,3)

比如：

mysql> select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;
复制代码

既可以先从表 t1 里面取出 c=10 的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2 里面 d 的值是否等于 20。
也可以先从表 t2 里面取出 d=20 的记录的 ID 值，再根据 ID 值关联到 t1，再判断 t1 里面 c 的值是否等于 10。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。

执行引擎

查询执行引擎负责执行 SQL 语句，开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误，如下所示 (在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限)。如果有权限就会打开表继续执行，此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的API接口与底层存储引擎缓存或者物理文件的交互，得到查询结果并返回给客户端。若开启用查询缓存，这时会将SQL 语句和结果完整地保存到查询缓存(Cache&Buffer)中，以后若有相同的 SQL 语句执行则直接返回结果。

到此，MySQL的运行机制就结束了。