Mysql调优大全梳理（涵盖90%需要调优的场景）_for each row in t2如果存在100个元素-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_45455361/article/details/123033908

前言

学习这个文章需要具备一定的理论基础，不妨先来看一下我这篇文章《一文道尽数据库底层原理，探讨Mysql调优之道》

1.Join语句优化

Join语句相关算法

算法1：Nested-Loop Join（NLJ）

这种算法称为“嵌套循环Join”，大致是这样玩的：

Table	Join Type
t1	range
t2	ref
t3	All

for each row in t1 matching range{
   
	for each row in t2 matching reference key {
   
		for each row in t3 {
   
			if row satisfies join conditions, send to client
		}
	}
}

如上，比如我们有三张表，大致流程是这样的，先查询出t1中符合条件数据，然后用一个for循环遍历，在循环里面查询出t2表中符合条件的数据，并使用reference key 去匹配，也就是join 时候的on字段，接着再查询出t3表中符合条件的数据，由于第三张表示全表扫描，所以就循环t3中所有数据做判断。

我们不难发现，这个算法是比较粗暴的，外层循环的结果集越多，内层循环的次数也就越多。

算法2：Block Nested-Loop Join（BNLJ）

翻译成中文，这种算法称为”块循环嵌套Join”，它的玩法是这样的：

我们还是拿上面算法1中的表为例，它的伪代码大致是这样的：

for each row in t1 matching range {
	for each row in t2 matching reference key {
		store used columns from t1, t2 in join buffer
		if buffer is full {
			for each row in t3 {
				for each t1, t2 combination in join buffer {
					if row satisfies join conditions, send to client
				}
			}
			empty join buffer
		}
	}
}
if buffer is not empty {
	for each row in t3 {
		for each t1, t2 combination in join buffer {
			if row satisfies join conditions, send to client
		}
	}
}

查询t1，t2和上面算法一样，但是在查询第三张表的时候就有区别了，它把t1以及t2表需要用到的字段，存到了一个叫 join buffer 的地方，这个join buffer叫做连接缓存，依次往join buffer里塞数据，当join buffer里满了的时候，再循环t3，然后用t3里的数据和join buffer里比较，如果匹配就返回给客户端。

我们比较一下这两种算法，我们看第一种，如果for each row in t2里有100个元素，那么就需要执行100次for each row in t3，那么如果使用BNLJ的话，mysql会把这100行数据缓存到join buffer，如果join buffer足够大，会把这100行数据都存放到缓存，那么只需要执行一次for each row in t3就可以了，大幅度减少了内存循环的表扫描次数。那么如果这100条数据不能一次存放到join，那么需要执行多少次for each row in t3 呢？这里有个计算公式：(S * C)/join_buffer_size + 1，其中S是缓存的t1或t2表的一行数据，C是缓存的行数，S*C就表示如果缓存所有数据需要多大空间，join_buffer_size是join buffer的大小，用这个公式就能得到需要扫描的次数了。

那么我们就可以知道，BNLJ主要是引入join buffer，从而减少内存扫描表的次数，进而提升性能的，但是使用join buffer是有条件的，只有符合某些条件才能使用join buffer：

连接类型是ALL、index或range
第一个nonconst table 不会分配join buffer，即使类型是ALL或者index
join buffer 只会缓存需要的字段，而非整行数据，即使用的是store used columns，而不是t1、t2所有字段。
每个能被缓存的join 都会配分配一个join buffer，一个查询可能拥有多个join buffer
join buffer会在执行联接之前分配，在查询完成后释放。

你可以通过join_buffer_size变量设置join buffer大小：

show variables like 'join_buffer_size'; //查看join buffer大小
set join_buffer_size = 1024 * 1024 * 50; //设置当前session的join buffer为50M
set global join_buffer_size = 1024 * 1024 * 50; //设置全局的join buffer为50M（一般不建议设置太大）

如何知道sql里有没有使用join buffer呢？看下图：

在这里插入图片描述

算法3：Batched Key Access Join（BKA）

这种算法翻译成中文叫做“批量键值访问”

MySQL 5.6引入
BKA的基石：Multi Range Read（MRR）

算法4：HASH JOIN

MySQL 8.0.18引入，用来替代BNLJ
join buffer缓存外部循环的hash表，内层循环遍历时到hash表匹配
官方文档原理解析：https://dev.mysql.com/blog-archive/hash-join-in-mysql-8/

使用注意：

MySQL版本必须>= 8.0.18，而且有很多限制，比如不能作用于外连接，比如left join/right join等等。从8.0.20开始，限制少了很多，建议用8.0.20或更高版本。
从MySQL8.0.18开始，hash join的join buffer是递增分配的，这意味着，你可以为将join_buffer_size设置的比较大。而在MySQL8.0.18中，如果你使用了外连接，外连接没法用hash join，此时join_buffer_size会按照你设置的值直接分配内存。因此join_buffer_size还是得谨慎设置。
从8.0.20开始，BNLJ已经被删除，用hash join代替了BNLJ

有关HASH JOIN强烈建议大家阅读这个文章：https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html 里面列出了hash jion在mysql各个版本之间的区别。