原理浅析_Mysql底层执行原理，程序员不能不懂

实施过程

一般来说，MySQL可以分为两部分：数据库层和存储引擎层。

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

存储引擎层：是可插拔的设计，即我们可以随意选择特定的存储引擎。 客户端通过API与存储引擎进行通信，这些接口屏蔽了不同存储引擎之间的差异。 支持、、、等多种存储引擎。 从MySQL 5.5开始，已经成为MySQL的默认存储引擎。

接下来我们用一张来自MySQL 45的图来解释整个执行过程。

图像连接器

第一步是连接到数据库。 连接器负责与客户端建立连接、获取权限、维护和管理连接。 连接命令通常这样写：

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

我们可以通过TCP/IP、命名管道和共享内存、Unix域套接字文件等方式建立连接。当连接到服务器时，服务器会验证密码和用户名。

如果用户名或密码不正确，您将收到“for user”错误，并且客户端程序将终止执行。

如果用户名和密码验证成功，连接器将在权限表中查找您拥有的权限。 之后这个连接中的权限判断逻辑就会依赖于此时读取到的权限。

查询缓存

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

当MySQL收到查询请求时，它会首先检查查询缓存，看看该语句之前是否已经执行过。 之前执行的语句及其结果可以以键值对的形式直接缓存在内存中。 key是查询语句，value是查询结果。 如果你的查询可以直接在这个缓存中找到key，那么value就会直接返回给客户端。

但在大多数情况下我建议您不要使用查询缓存。 为什么？ 因为查询缓存往往弊大于利。 查询缓存失效的情况非常频繁。 只要一张表被更新，该表上的所有查询缓存都会被清除。

幸运的是，MySQL也提供了这种“按需”的方法。 您可以将该参数设置为OFF以关闭查询缓存。 您也可以显式指定它，如以下语句：

mysql> select SQL_CACHE * from T where ID=10；

MySQL 8.0版本直接删除了查询缓存的整个功能，也就是说从8.0开始这个功能彻底消失了。

分析仪

如果查询缓存没有命中，则该语句将被真正执行。 首先，MySQL需要知道你想要做什么，因此它需要解析SQL语句。

分析器首先会进行“词法分析”。 你输入一条由多个字符串和空格组成的SQL语句，MySQL需要识别这些字符串是什么以及它们代表什么。

MySQL 会将您输入的关键字“”识别为查询语句。 它还将字符串“T”识别为“表名 T”，将字符串“ID”识别为“列 ID”。

完成这些识别后，还需要做“句法分析”。 根据词法分析的结果，语法分析器会根据语法规则判断你输入的SQL语句是否符合MySQL语法。

如果您的语句不正确，您将收到一条错误消息“您的 SQL 中有错误”。 例如，以下语句缺少首字母“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 close”之后的内容。

优化器

分析器之后，MySQL 知道你想做什么。 在开始执行之前，首先必须经过优化器的处理。

当表中有多个索引时，优化器决定使用哪个索引，或者当语句连接多个表时，优化器决定表的连接顺序。 例如，如果执行以下语句，则该语句连接两个表：

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

1、可以先从表t1中取出c=10的记录的ID值，然后根据ID值与表t2进行关联，然后判断t2中d的值是否等于20。

2、也可以先从表t2中取出c=20的记录的ID值，然后根据ID值与t1进行关联，然后判断t1中c的值是否等于10。

这两种执行方式的逻辑结果是一样的，但是执行效率会有所不同。 优化器的作用是决定使用哪种解决方案。

优化器阶段完成后，确定该语句的执行计划，然后进入执行器阶段。

执行器

MySQL通过分析器知道你要做什么，通过优化器知道怎么做，于是进入执行器阶段，开始执行语句。

开始执行时，首先要判断自己是否有权限对表T执行查询，如果没有，会返回没有权限的错误，如下所示。

mysql> select * from T where ID=10;
ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

如果有权限，打开表并继续执行。 当你打开表时，优化器会根据表的引擎定义使用引擎提供的接口。

比如我们例子中的表T，ID字段没有索引，所以执行器的执行流程如下：

1、调用引擎接口获取该表的第一行，判断ID值是否为10，如果不是则跳过。 如果是，则将该行保存到结果集中。

2、调用引擎接口获取“下一行”，重复相同的判断逻辑，直到获取到表格的最后一行。

3、执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

索引查询的执行逻辑类似。 第一次调用是获取满足条件的第一行，然后循环获取满足条件的下一行。 这些接口已经在存储引擎中定义。

慢查询日志有一个字段指示语句执行期间扫描了多少行。 每次执行器调用引擎获取数据时都会累加这个数字。 但是，有时引擎在一次执行程序调用期间会扫描多行，因此该数字与行数并不完全相关。

存储引擎

MySQL的存储引擎是可插拔的设计。 该层通过一些设定的API与存储引擎交互，存储引擎实现这些API。 常见的存储引擎有，，。

特征：

64TB

支持事务

行级锁

支持多版本并发控制机制（MVCC）

支持聚集索引

支持数据缓存

支持外键

数据库文件：

缺点：

不支持交易

最小粒度锁：表级

读和写互相阻塞。 不能边写边读，也不能边读边写。

不支持MVCC（支持多版本并发控制机制）

不支持聚集索引

不支持数据缓存，仅支持索引缓存

不支持外键

崩溃恢复能力差

优势：

支持最大256TB存储空间

读取数据更快，占用资源更少

直接记录count(0)的值

引擎存储文件：

适用场景：5.5之前默认数据库引擎，适用于数据只读（或写入较少）、表较小（可以接受较长修复操作）的场景。

所有数据都存储在内存中，不需要磁盘 I/O。 重启后表结构会保留，但数据会丢失。

表格在很多场景下都能发挥很好的作用：

为什么更快？

1、只缓存索引块，减少缓存换入换出的频率。

2、表结构不是聚集索引，而是聚集索引。 二级索引需要找到id回表查主索引，所有索引都直接指向数据行的存储位置。

图像

3、MVCC的一致性也需要保持； 虽然你的场景没有，但是还是需要检查和维护，表锁牺牲了写性能，提高了读性能。

实现原理

SQL语句的底层执行细节如图所示。

图像

用例：将 id=1 更新为 id=2

1. 更新数据

1-1. 将id=1的信息写入回滚段undo log中；

1-2. undo log在系统表空间或者undo log表空间中，它自己的数据页也在缓冲池中。 此时，undo log的物理页数据变化也需要写入redo log，但这不是主要过程；

1-3. 将缓冲池中的id=1改为id=2；

2. 将数据页的变化写入重做日志。 根据配置写入重做日志：批量顺序写入/主动写入/被动写入磁盘

3、将数据变更SQL写入日志。 写入备库是否成功由配置决定，写入如下：数据接收成功/中继日志成功/执行成功；

4. 提交交易；

以上是数据更新的大致步骤。

实施计划

我们首先执行一条SQL语句：* from user where id = 2;。 执行后我们可以看到结果如下：

图像

imgid 字段

id表示执行的查询语句的序号。 它是SQL语句执行顺序的标识符。 SQL语句按照id从大到小执行。 具有相同id的语句被分组在一起并从上到下执行。

类型字段

图像

type字段表示SQL关联的类型或访问的类型。 从这个字段我们可以确定这条SQL在数据库表中查找的记录的大致范围，这直接体现了该SQL的效率。

类型字段的类型有很多种，最常见的有：const、ref、range、index 和 ALL。 其性能从高到低。 > const > > ref > 范围 > 索引 > 全部。 我们来详细谈谈这个属性。

它是const的一种特例，“表示表中只有一行记录”。 这种情况几乎不会发生，所以只要理解它即可。

常量

const 表示通过索引查找一次数据。 一般const出现在“在唯一索引或主键索引中使用等值查询”中。 由于表中只有一条数据匹配，因此查找速度非常快。 例如： * from user where id =2;

“表示使用唯一索引或主键索引扫描作为表链接匹配条件。对于每个索引键，表中只有一条记录与其匹配。” 例如： * from user left join on user.id = 。 在 .=role.id 上左连接角色；

ref 性能较差，“它还表示表的链接匹配条件，即使用哪些表字段作为查询索引列上的值”。 ref 和 ref 的区别是：使用唯一索引或主键索引。 ref扫描的结果可能会找到多行满足条件的行。 它本质上是返回匹配行的索引访问。 例如：* from user where name = '张三';

图像

这里type是ALL，也就是说name字段没有索引，所以我们需要给name字段添加普通索引。

图像

下次执行时，可以发现name已经采用了正常的索引。

“Range 使用索引来检索给定范围内的行数据。一般情况下，Range 出现在 where、in 等查询语句之后。” 例如：* from user where id > 2;

“索引表示将遍历索引树。” 索引避免了ALL，速度更快，但是索引的出现意味着你需要检查你的索引是否被正确使用。 例如：来自用户的 id；

“ALL和index的区别在于ALL是从硬盘读取，而index是从索引文件读取。” ALL全表扫描是指Mysql将从头到尾扫描表。 这通常意味着需要添加索引进行优化，或者查询不使用索引作为查询条件，例如： * from user;

领域

该字段显示SQL查询的附加信息，主要包括以下几种情况：Using index、Using where、Using、Using、Using join、where、away

指数

表示查询的列被索引覆盖。 这是高查询性能的体现。 即通过在索引中查找即可获得要查询的信息，而不需要返回表。 索引使用正确。 例如：来自用户的 id，其中 id =2；

图像

如果using where同时出现，则表示该索引用于进行索引键值查找； 如果没有出现using where，则说明索引是用来读取数据而不是执行查询动作。

使用索引

表示仅使用索引下推的条件。 也就是说，如果搜索条件包含在复合索引覆盖的列中，则在返回表之前会使用条件将其过滤掉，减少表返回次数。 有关索引下推的详细信息，请参阅功能部分。

使用地点

该属性与使用索引相反。 查询的列没有被索引覆盖。 非索引的前导列用在 where 条件之后。 它只是使用 where 条件。 例如：user.* 来自 user,role，其中 user.id = 。 和角色.id=.;

“使用是指使用临时表来存储中间结果。通常情况下，对结果进行排序时会使用临时表。” 例如：排序依据和分组查询分组依据。 示例：* from (name from user union name from role) a where a.name = '张三';

using表示文件排序，这意味着MySQL使用外部索引而不是表中的索引对数据进行排序。 例如：*来自用户按姓名排序；

加入

使用 join 意味着使用连接缓存。 例如：user.* 来自 user,role，其中 user.id = 。 和角色.id=。 ;

图像

它强调在获取连接条件时，不使用索引，而是使用连接缓冲区来存储中间结果。 如果出现这个值，一般意味着需要添加索引进行优化。

离开

表示该语句返回数据而不遍历表或索引。 例如：来自用户的min(id)；

图像

Extra字段中还有其他属性，但大部分很少见到，这里就不展示了，所以在这里解释一下。 有兴趣的可以自行了解一下。 这里只列出常见的。

关键词执行过程

select * from T where age=12 and name Like '小%';

对于这样的查询，如果我们使用年龄索引，我们的执行流程如下。

存储引擎在二级索引中定位到第一条age=12的记录，从表中取出完整的记录信息，返回给图层。

该层获取记录后，会判断记录名称是否满足“small%”等条件。 如果是，则直接返回给客户端。

这和我们想象的不一样吗？ 我们认为该层会找到所有结果并统一返回给客户端。 事实上，事实并非如此。

重新引入

然后调用API接口查找存储引擎获取下一条满足age=12的记录，并在该层判断“小%”等条件，并将结果返回给客户端。