MySQL解析过程、执行过程

转载：https://student-lp.iteye.com/blog/2152601

https://www.cnblogs.com/cdf-opensource-007/p/6502556.html

说说一条查询sql的执行流程和底层原理？

执行SQL更新时,其底层经历了哪些操作?

MySQL解析过程

  • 客户端发送一条查询给服务器；
  • 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
  • 服务器段进行SQL解析、预处理，在优化器生成对应的执行计划；
  • mysql根据优化器生成的执行计划，调用存储引擎的API来执行查询。
  • 将结果返回给客户端。

实际上mysql执行的每一步都比较复杂，具体的过程如下：

1、mysql客户端和服务器通讯

    mysql客户端和服务器之间的通讯协议是“半双工”的，这意味着，在任何一个时刻，要么由服务器向客户端发送数据，要么由客户端向服务器发送数据，这两个动作不能同时发生。这种协议让mysql通信简单快速，但也限制了mysql。一个明显的限制是，这意味着没办法进行流量限制。一旦一端开始发生消息，另一端要接收完整个消息才能响应他。

    客户端用一个单独的数据包将查询传给服务器。一旦客户端发送了请求，他能做的事情就只是等待结果了。

    相反的，一般服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整的接受整个返回结果，而不是简单的只收取前面几条结果，然后让服务器停止发送数据。

    多数连接mysql的库函数都可以获得全部结果并缓存到内存里，还可以逐行获取所需要的数据。默认一般是获得全部结果并缓存到内存中。mysql通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接受全部结果并缓存通常可以减少服务器的压力，让查询能够早点结束、早点释放对应的资源。

2、　　查询状态

    对于mysql连接，任何时刻都有一个状态，该状态表示了mysql当前正在做什么。使用show full processlist命令查看当前状态。在一个查询生命周期中，状态会变化很多次，下面是这些状态的解释：

  • sleep：线程正在等待客户端发送新的请求；
  • query：线程正在执行查询或者正在将结果发送给客户端；
  • locked：在mysql服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中。对于MyISAM来说这是一个比较典型的状态。
  • analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划；
  • copying to tmp table：线程在执行查询，并且将其结果集复制到一个临时表中，这种状态一般要么是做group by操作，要么是文件排序操作，或者union操作。如果这个状态后面还有on disk标记，那表示mysql正在将一个内存临时表放到磁盘上。
  • sorting Result：线程正在对结果集进行排序。
  • sending data：线程可能在多个状态间传送数据，或者在生成结果集，或者在想客户端返回数据。

3、查询缓存

    在解析一个查询语句之前，如果查询缓存是打开的，那么mysql会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同，那也不会匹配缓存结果，这种情况下查询就会进入下一阶段的处理。

    如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前mysql会检查一次用户权限。这仍然是无须解析查询SQL语句的，因为在查询缓存中已经存放了当前 查询需要访问的表信息。如果权限没有问题，mysql会跳过所有其他阶段，直接从缓存中拿到结果并返回给客户端。这种情况下，查询不会被解析，不用生成执行计划，不会被执行。

但是缓存不好用啊。因为：

  • 只要有对一个表的更新，这个表上所有的查询缓存都会被清空
  • SQL任何字符上的不同,如空格,注释,都会导致缓存不命中

因此，我能想到用查询缓存的表，只有一种情况，那就是配置表。其他的业务表，根本是无法利用查询缓存的特性，或许Mysql团队也是觉得查询缓存的使用场景过于局限，就无情的将它剔除。

4、解析器

　　将请求的sql生存一颗语法树。如：

　　select username from userinfo

　　先通过词法分析：

从左到右一个字符、一个字符地输入，然后根据构词规则识别单词。你将会生成4个Token,如下所示。"

    面试官：说说一条查询sql的执行流程和底层原理？
　　接下来，进行语法解析，判断输入的这个 SQL 语句是否满足 MySQL 语法。然后生成下面这样一颗语法树：

　　如果语法不对，会报错。

5、处理器

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

6、优化器

　　当语法树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

　　执行计划：mysql不会生成查询字节码来执行查询，mysql生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。

 

    　　查询的生命周期的下一步是将一个SQL转换成一个执行计划，mysql在依照这个执行计划和存储引擎进行交互。这包含多个子阶段：解析SQL、预处理、优化SQL执行计划。这个过程中任何错误都可能终止查询。

  • 语法解析器和预处理：首先mysql通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。mysql解析器将使用mysql语法规则验证和解析查询；预处理器则根据一些mysql规则进一步检查解析数是否合法。
  • 查询优化器：当语法树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
  • 执行计划：mysql不会生成查询字节码来执行查询，mysql生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。

7、查询执行引擎

    在解析和优化阶段，mysql将生成查询对应的执行计划，mysql的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构，而不是和很多其他的关系型数据库那样对应的字节码。

    mysql简单的根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储引擎实现的接口来完成。为了执行查询，mysql只需要重复执行计划中的各个操作，直到完成所有的数据查询。

8、返回结果给客户端

    查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果给客户端，mysql仍然会返回这个查询的一些信息，如该查询影响到的行数。如果查询可以被缓存，那么mysql在这个阶段也会将结果放到查询缓存中。

    mysql将结果集返回客户端是一个增量、逐步返回的过程。这样有两个好处：服务器端无须存储太多的结果，也就不会因为返回太多结果而消耗太多的内存；这样处理也让msyql客户端第一时间获得返回的结果。

    结果集中的每一行都会以一个满足mysql客户端/服务器通信协议的包发送，再通过tcp协议进行传输，在tcp传输的过程中，可能对mysql的封包进行缓存然后批量传输。

9、读 写过程的异同

读的过程，大致就是上面的流程。写的话，也会走上面的流程：连接、缓存、解析器、处理器、优化器、执行器。

不同的是，写会把缓存清空。接下来，解析器会通过词法和语法解析知道这是一条更新语句。优化器决定要使用 哪个索引。然后，执行器负责具体执行，找到这一行，然后更新。

与读流程不一样的是，写流程还涉及两个重要的日志模块，它们正是：redo log（重做日志）和 binlog（归档日志）。

------执行器和 InnoDB 引擎在执行update语句时的内部流程

例如： update T set c=c+1 where ID=2;

  1. 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
  2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
  3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redolog 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
  4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
  5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。这里我给出这个 update 语句的执行流程图，图中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的。

 

 最后三步看上去有点“绕”，将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。

为什么必须有“两阶段提交”呢？这是为了让两份日志之间的逻辑一致。

MySQL执行过程

SQL是一套标准，全称结构化查询语言，是用来完成和数据库之间的通信的编程语言，SQL语言是脚本语言，直接运行在数据库上。同时，SQL语句与数据在数据库上的存储方式无关，只是不同的数据库对于同一条SQL语句的底层实现不同罢了，但结果相同。这有点类似于java中接口的作用，一个接口可以有不同的实现类，不同的实现类对于接口中方法的实现方式可以不同，结果可以相同。这里SQL语言的作用就类似于java中的接口，数据库就类似于java中接口的实现类，SQL语句就类似于java接口中的方法。不同的是java中接口的不同实现类对于接口中方法的执行结果可以相同，也可以不同，而不同的数据库对于同一条SQL语句的执行是相同的。（这里只是做一个类比，方便我们理解）

　　一般情况下，大部分SQL语句在不同的数据库上是通用的，但我们知道每个数据库都有自己独有的特性，像在MySql数据库中，可以使用substr(取字符串)，trim(去空格)，ifnull(空值处理函数)，还可以使用limit语句对数据库表进行截取，但这些都是oracle数据库没有的。（类比接口实现类中，实现类独有的方法，而接口中没有的）

　　这里简单介绍一下mysql数据库，mysql数据库是一款关系型数据库，所谓关系型数据库就是以二维表的形式存储数据，使用行和列方便我们对数据的增删改查。

　　这篇博客，我们以mysql数据库为例，对一条sql语句的执行流程进行分析。（本篇博客不涉及到表连接）

　　首先，创建一张student表，字段有自增主键id，学生姓名name，学科subject，成绩grade

　　建表语句：

复制代码
DROP TABLE IF EXISTS student;
CREATE TABLE `student` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `subject` varchar(10) DEFAULT NULL,
  `grade` double(4,1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8;
复制代码

　　初始化数据：

复制代码
INSERT INTO student(`name`,`subject`,grade)VALUES('aom','语文',88);
INSERT INTO student(`name`,`subject`,grade)VALUES('aom','数学',99);
INSERT INTO student(`name`,`subject`,grade)VALUES('aom','外语',55);

INSERT INTO student(`name`,`subject`,grade)VALUES('jack','语文',67);
INSERT INTO student(`name`,`subject`,grade)VALUES('jack','数学',44);
INSERT INTO student(`name`,`subject`,grade)VALUES('jack','外语',55);

INSERT INTO student(`name`,`subject`,grade)VALUES('susan','语文',56);
INSERT INTO student(`name`,`subject`,grade)VALUES('susan','数学',35);
INSERT INTO student(`name`,`subject`,grade)VALUES('susan','外语',77);

INSERT INTO student(`name`,`subject`,grade)VALUES('alice','语文',88);
INSERT INTO student(`name`,`subject`,grade)VALUES('alice','数学',77);
INSERT INTO student(`name`,`subject`,grade)VALUES('alice','外语',100);

INSERT INTO student(`name`,`subject`,grade)VALUES('rajo','语文',33);
INSERT INTO student(`name`,`subject`,grade)VALUES('rajo','数学',55);
INSERT INTO student(`name`,`subject`,grade)VALUES('rajo','外语',55);
复制代码

下面我们来看一下，数据在数据库中的存储形式。

　　（图1.0）

现在针对这张student表中的数据提出一个问题：要求查询出挂科数目多于两门（包含两门）的前两名学生的姓名，如果挂科数目相同按学生姓名升序排列。

下面是这条查询的sql语句

SELECT `name`,COUNT(`name`) AS num FROM student WHERE grade < 60 GROUP BY `name` HAVING num >= 2 ORDER BY num DESC,`name` ASC LIMIT 0,2;

执行结果：

　　图（1.1）

以上这条sql语句基本上概括了单表查询中所有要注意的点，那么我们就以这条sql为例来分析一下一条语句的执行流程。

1，一条查询的sql语句先执行的是 FROM student 负责把数据库的表文件加载到内存中去

如图1.0中所示。（mysql数据库在计算机上也是一个进程，cpu会给该进程分配一块内存空间，在计算机‘服务’中可以看到，该进程的状态）

　　图（1.2）

2，WHERE grade < 60

会把（图1.0）所示表中的数据进行过滤，取出符合条件的记录行，生成一张临时表，如下图所示。

　　图（1.3）

3，GROUP BY `name`

会把图（1.3）的临时表切分成若干临时表，我们用下图来表示内存中这个切分的过程。

        　 　　　   

　　图（1.4)　　　　　　　　　　　　　　图（1.5)　　　　　　　　　　　　　　　图（1.6)　　　　　　　　　　　　　 图（1.7)

4，SELECT 的执行读取规则分为sql语句中有无GROUP BY两种情况。

　　（1）当没有GROUP BY时，SELECT 会根据后面的字段名称对内存中的一张临时表整列读取。

　　（2）当查询sql中有GROUP BY时，会对内存中的若干临时表分别执行SELECT，而且只取各临时表中的第一条记录，然后再形成新的临时表。这就决定了查询sql使用GROUP BY的场景下，SELECT后面跟的一般是参与分组的字段和聚合函数，否则查询出的数据要是情况而定。另外聚合函数中的字段可以是表中的任意字段，需要注意的是聚合函数会自动忽略空值。

　　我们还是以本例中的查询sql来分析，现在内存中有四张被GROUP BY `name`切分成的临时表，我们分别取名为 tempTable1,tempTable2,tempTable3,tempTable4分别对应图（1.4)、图（1.5)、图（1.6)，图（1.7)下面写四条"伪SQL"来说明这个查询过程。

SELECT `name`,COUNT(`name`) AS num FROM tempTable1;
SELECT `name`,COUNT(`name`) AS num FROM tempTable2;
SELECT `name`,COUNT(`name`) AS num FROM tempTable3;
SELECT `name`,COUNT(`name`) AS num FROM tempTable4;

最后再次成新的临时表，如下图：

　　图（1.8)

5，HAVING num >= 2对上图所示临时表中的数据再次过滤

与WHERE语句不同的是HAVING 用在GROUP BY之后，WHERE是对FROM student从数据库表文件加载到内存中的原生数据过滤，而HAVING 是对SELECT 语句执行之后的临时表中的数据过滤，所以说column AS otherName ,otherName这样的字段在WHERE后不能使用，但在HAVING 后可以使用。但HAVING的后使用的字段只能是SELECT 后的字段，SELECT后没有的字段HAVING之后不能使用。HAVING num >= 2语句执行之后生成一张临时表，如下：

 　　图（1.9)

6，ORDER BY num DESC

`name` ASC对以上的临时表按照num，name进行排序。

7，LIMIT 0,2

取排序后的前两个。

以上就是一条sql的执行过程，同时我们在书写查询sql的时候应当遵守以下顺序。

SELECT XXX FROM XXX WHERE XXX GROUP BY XXX HAVING XXX ORDER BY XXX LIMIT XXX;
posted on 2019-01-14 19:22  反光的小鱼儿  阅读(11488)  评论(1)  编辑  收藏  举报