一条sql执行过程

客户端/服务器通信流程

My SQL客户端和服务器之间的通信协议是“半双工”的,这意味着,在任何一个时刻,要么是由服务器向客户端发送数据,要么是由客户端向服务器发送数据,这两个动作不能同时发生。

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

客户端缓存服务端返回的结果，Mysql服务端只有所有数据都发送给客户端才能释放这条查询所占用的资源。

查询状态

对于一个 My SQL连接,或者说一个线程,任何时刻都有一个状态,该状态表示了MySQL当前正在做什么。有很多种方式能查看当前的状态,最简单的是使用 SHOW FULL PROCESSLIST命令。

在一个查询的生命周期中,状态会变化很多次。 My SQL官方手册中对这些状态值的含义有最权威的解释,下面将这些状态列出来,并做一个简单的解释。

Sleep 线程正在等待客户端发送新的请求。
Query 线程正在执行查询或者正在将结果发送给客户端。
Locked 在MySQL服务器层,该线程正在等待表锁。在存储引擎级别实现的锁,例如InnodB的行锁,并不会体现在线程状态中。
Analyzing and statistics 线程正在收集存储引擎的统计信息,并生成查询的执行计划。
Copying to tmp table [on disk] 线程正在执行查询,并且将其结果集都复制到一个临时表中,这种状态一般要么是在做 GROUP BY操作,要么是文件排序操作,或者是UNION操作。如果这个状态后面
还有“ on disk”标记,那表示 MySQL正在将一个内存临时表放到磁盘上
Sorting result 线程正在对结果集进行排序
Sending data 这表示多种情况:线程可能在多个状态之间传送数据,或者在生成结果集,或者在向客户端返回数据。

查询缓存

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

如果当前的查询恰好命中了查询缓存,那么在返回查询结果之前 My SQL会检查一次用户权限。如果权限没有问题, MySQL会跳过所有其他阶段,直接从缓存中拿到结果并返回给客户端。这种情况下,查询不会被解析,不用生成执行计划,不会被执行。

查询优化处理

查询的生命周期的下一步是将一个SQL转换成一个执行计划, MySQL再依照这个执行计划和存储引擎进行交互。这包括多个子阶段:解析SQL、预处理、优化SQL执行计划。

这个过程中任何错误(例如语法错误)都可能终止查询,在实际执行中,这几部分可能一起起执行，也可能单独执行。

语法解析器

MySQL通过关键字将SQL语句进行解析,并生成一棵对应的“解析树”MySQL解析器将使用 MySQL语法规则验证和解析查询。

预处理器

预处理器则根据一些MySQL规则进一步检査解析树是否合法,例如,这里将检查数据表和数据列是否存在,还会解析名字和别名,看看它们是否有歧义。

下一步预处理器会验证权限。

查询优化器

一条查询可以有很多种执行方式,最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

MySQL使用基于成本的优化器,它将尝试预测一个查询使用某种执行计划时的成本,并选择其中成本最小的一个。

执行计划的成本是由mysql根据一系列的统计信息计算得来的:每个表或者索引的页面个数、索引的基数(索引中不同值的数量)、索引和数据行的长度、索引分布情况。

有很多种原因会导致 MySQL优化器选择错误的执行计划,如统计信息不准确、执行计划中的成本估算不等同于实际执行的成本、优化器有时候无法去估算所有可能的执行计划,所以它可能错过实际上最优的执行计划。

优化策略

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

优化策略可以简单地分为两种。

静态优化

静态优化可以直接对解析树进行分析,并完成优化。例如,优化器可以通过一些简单的代数变换将WHERE条件转换成另一种等价形式。静态优化在第一次完成后就一直有效,即使使用不同的参数重复执行查询也不会发生变化。可以认为这是一种“编译时优化”。
动态优化

动态优化则和查询的上下文有关,也可能和很多其他因素有关,例如 WHERE条件中的取值、索引中条目对应的数据行数等。这需要在每次查询的时候都重新评估,可以认为这是“运行时优化”。

优化器能够处理的优化类型

重新定义关联表的顺序
将外连接转为内连接

使用等价变换规则

( a\<b AND b=c) AND a=5 会被改写为 b>5

优化COUNT(),MIN(),MAX()
覆盖索引扫描
子查询优化
等值传播
提前终止查询

生成执行计划

MySQL生成査询的一棵指令树,然后通过存储引擎执行完成这棵指令树并返回结果。

最终的执行计划包含了重构查询的全部信息。如果对某个查询执行 EXPLATN EXIENDED后,再执行SHOW WARNINGS,就可以看到重构出的查询。

任何多表查询都可以使用一棵树表示,例如

MySQL总是从一个表开始一直嵌套循环、回溯完成所有表关联。(见mysql如何关联表)

MySQL的执行计划总是一棵左测深度优先的树。

关联查询优化器

My SQL优化器最重要的一部分就是关联查询优化,它决定了多个表关联时的顺序。

通常多表关联的时候,可以有多种不同的关联顺序来获得相同的执行结果。关联查询优化器则通过评估不同顺序时的成本来选择一个代价最小的关联顺序。

mysql会自动优化多表关联时的关联顺序，但是写sql的时候还是自己心里要有数

如果有超过n个表的关联,那么需要检查n的阶乘种关联顺序。我们称之为所有可能的执行计划的“搜索空间”,搜索空间的增长速度非常块—一例如,若
是10个表的关联,那么共有3628800种不同的关联顺序!当搜索空间非常大的时候,优化器不可能逐一评估毎一种关联顺序的成本。这时,优化器选择使用“贪婪”搜索的方式查找“最优”的关联顺序。

这也是之前提的，mysql使用了错误的执行计划原因之一，优化器有时候无法去估算所有可能的执行计划,所以它可能错过实际上最优的执行计划。

排序

排序都是一个成本很高的操作,所以从性能角度考虑,应尽可能避免排序或者尽可能避免对大量数据进行排序。
如果需要排序，MySQL会对查询出来的结果进行排序。

通过索引排序
无法使用索引排序的时候，进行文件排序

MySQL如何关联查询(JOIN原理)

MySQL中“关联(join)”一词所包含的意义比一般意义上理解的要更广泛。

总的来说,MySQL认为任何一个查询都是一次“关联”一一并不仅仅是一个查询需要到两个表匹配才叫关联,所以在 MySQL中,每一个查询,每一个片段(包括子查询,甚至基于单表的 SELECT都可能是关联。

对于UNI0N查询, MySQL先将一系列的单个查询结果放到一个临时表中,然后再重新读出临时表数据来完成UNI0N查询。

MySQL对任何关联都执行嵌套循环关联操作,即My SQL先在一个表中循环取出单条数据,然后再嵌套循环到下一个表中寻找匹配的行,依次下去,直到找到所有表中匹配的行为止。然后根据各个表匹配的行,返回查询中需
要的各个列。

参考上面的图，mysql如何实现多表关联。

Join的过程中具体做了什么呢？

数据准备

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;

drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into t2 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

create table t1 like t2;
insert into t1 (select * from t2 where id<=100)

Index Nested-Loop Join

1	select * from t1 straight_join t2 on (t1.a=t2.a);

用straight_join让MySQL使用固定的连接方式执行查询，在这个语句里，t1 是驱动表，t2是被驱动表。

这条语句的explain结果

被驱动表t2的字段a上有索引，join过程用上了这个索引，因此这个语句的执行流程是这样的：

从表t1中读入一行数据 R；
从数据行R中，取出a字段到表t2里去查找；
取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；
重复执行步骤1到3，直到表t1的末尾循环结束。

这个过程是先遍历表t1，然后根据从表t1中取出的每行数据中的a值，去表t2中查找满足条件的记录。在形式上，这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所以我们称之为“Index Nested-Loop Join”，简称NLJ。

对驱动表t1做了全表扫描，这个过程需要扫描100行；而对于每一行R，根据a字段去表t2查找，走的是树搜索过程。由于我们构造的数据都是一一对应的，因此每次的搜索过程都只扫描一行，也是总共扫描100行；所以，整个执行流程，总扫描行数是200。

NLJ优化

MRR(Multi-Range Read),优化回表操作，根据索引找到主键后，根据主键排序后去回表查询，将原来的随机读写改成了顺序读写，极大的提高了读性能

Batched Key Access

理解了MRR性能提升的原理，我们就能理解MySQL在5.6版本后开始引入的Batched Key Acess(BKA)算法了。这个BKA算法，其实就是对NLJ算法的优化。

NLJ算法执行的逻辑是：从驱动表t1，一行行地取出a的值，再到被驱动表t2去做join。也就是说，对于表t2来说，每次都是匹配一个值。这时，MRR的优势就用不上了。

那怎么才能一次性地多传些值给表t2呢？方法就是，从表t1里一次性地多拿些行出来，一起传给表t2。

把表t1的数据取出来一部分，先放到一个临时内存。这个临时内存不是别人，就是join_buffer,一次性传给t2，t2可以匹配的时候可以根据索引顺序读。

一次性多传些值给t2，就是BAK算法。

Simple Nested-Loop Join(mysql不使用这种方式)

1	select * from t1 straight_join t2 on (t1.a=t2.b);

由于表t2的字段b上没有索引，因此再用图2的执行流程时，每次到t2去匹配的时候，就要做一次全表扫描。这样算来，这个SQL请求就要扫描表t2多达100次，总共扫描100*1000=10万行。

这还只是两个小表，如果t1和t2都是10万行的表（当然了，这也还是属于小表的范围），就要扫描100亿行，这个算法看上去太“笨重”了。

Block Nested-Loop Join

被驱动表上没有可用的索引,mysql使用Block Nested-Loop Join(BNL)算法
算法的流程是这样的：

把表t1的数据读入线程内存join_buffer中，由于我们这个语句中写的是select *，因此是把整个表t1放入了内存；
扫描表t2，把表t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。

这条SQL语句的explain结果如下所示：

在这个过程中，对表t1和t2都做了一次全表扫描，因此总的扫描行数是1100。由于join_buffer是以无序数组的方式组织的，因此对表t2中的每一行，都要做100次判断，总共需要在内存中做的判断次数是：100*1000=10万次。

因此，从时间复杂度上来说，这两个算法是一样的。但是，Block Nested-Loop Join算法的这10万次判断是内存操作，速度上会快很多，性能也更好。

要是表t1是一个大表，join_buffer放不下怎么办呢？
join_buffer的大小是由参数join_buffer_size设定的，默认值是256k。

如果放不下表t1的所有数据话，策略很简单，就是分段放。

扫描表t1，顺序读取数据行放入join_buffer中，放完第88行join_buffer满了，继续第2步；
扫描表t2，把t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回；
清空join_buffer；
继续扫描表t1，顺序读取最后的12行数据放入join_buffer中，继续执行第2步。

这个流程才体现出了这个算法名字中“Block”的由来，表示“分块去join”。

这时候由于表t1被分成了两次放入join_buffer中，导致表t2会被扫描两次。虽然分成两次放入join_buffer，但是判断等值条件的次数还是不变的，依然是(88+12)*1000=10万次。

如何选择驱动表

如果是Index Nested-Loop Join算法，应该选择小表做驱动表；
如果是Block Nested-Loop Join算法：
- 在join_buffer_size足够大的时候，是一样的；
- 在join_buffer_size不够大的时候（这种情况更常见），应该选择小表做驱动表。

总结：应该使用小表做驱动表。

什么是小表？

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

做表关联的时候，一定要去走被关联表的索引，这才能达到比较好的性能！！！

查询执行引擎

在解析和优化阶段, MySQL将生成査询对应的执行计划, MySQL的査询执行引擎则根据这个执行计划来完成整个查询。

查询执行阶段不是那么复杂: MySQL只是简单地根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中,有大量的操作需要通过调用
存储引擎实现的接口来完成,这些接口也就是我们称为“ handler api”的接口。

这种简单的接口模式,让 My SQL的存储引擎插件式架构成为可能,但是正
如前面的讨论,也给优化器带来了一定的限制。

返回结果给客户端

如果查询可以被缓存,那么 MySQL在这个阶段也会将结果存放到查询缓存中。

MySQL将结果集返回客户端是一个增量、逐步返回的过程。

我们回头看看前面的关联操作,一旦服务器处理完最后一个关联表,**开始生成第一条结果时, MySQL就可以开始向客户端逐步返回结果集了。**这样处理有两个好处:

服务器端无须存储太多的结果,也就不会因为要返回太多结果而消耗太多内存
这样的处理也让 MySQL客户端第一时间获得返回的结果。

如何处理超大结果集？

1
2
3

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
              java.sql.ResultSet.CONCUR_READ_ONLY); 
stmt.setFetchSize(Integer.MIN_VALUE);

https://stackoverflow.com/questions/63981002/jdbc-resultset-internal-mechanism-of-fetching-large-datasets

https://blog.csdn.net/weixin_38899094/article/details/120787529

Chenaa's Notes

mysql06_sql执行原理