如何干涉MySQL优化器使用hash join？

发布时间：2025-11-05 13:06:27 来源：码上建站 作者：域名

数据库的何干优化器相当于人类的大脑，大部分时候都能做出正确的优化用决策，制定正确的器使执行计划，走出一条高效的何干路，但是优化用它毕竟是基于某些固定的规则、算法来做的器使判断，有时候并没有我们人脑思维灵活，何干当我们确定优化器选择执行计划错误时该怎么办呢，优化用语句上加hint，器使提示它选择哪条路是何干一种常见的优化方法。

我们知道Oracle提供了比较灵活的优化用hint提示来指示优化器在多表连接时选择哪种表连接方式，比如use_nl，器使no_use_nl控制是何干否使用Nest Loop Join，use_hash,优化用no_use_hash控制是否使用hash join。

但是器使MySQL长期以来只有一种表连接方式，那就是Nest Loop Join，直到MySQL8.0.18版本才出现了hash join， 所以MySQL在控制表连接方式上没有提供那么多丰富的云南idc服务商hint给我们使用，hash_join与no_hash_join的hint只是惊鸿一瞥，只在8.0.18版本存在，8.0.19及后面的版本又将这个hint给废弃了，那如果我们想让两个表做hash join该怎么办呢？

我们来以MySQL8.0.25的单机环境做一个实验。建两个表，分别插入10000行数据，使用主键做这两个表的关联查询。

create table t1(id int primary key,c1 int,c2 int);

create table t2(id int primary key,c1 int,c2 int);

delimiter //

CREATE PROCEDURE p_test()

BEGIN

declare i int;

set i=1;

while i<10001 do

insert into t1 values(i,i,i);

insert into t2 values(i,i,i);

SET i = i + 1;

end while;

END;

//

查询一下两表使用主键字段关联查询时实际的执行计划，如下图所示：

查询一下两表使用非索引字段关联查询时实际的执行计划，如下图所示：

从执行计划可以看出，被驱动表的关联字段上有索引，优化器在选择表连接方式时会倾向于选择Nest Loop Join，当没有可用索引时倾向于选择hash join。

基于这一点那我们可以使用no_index提示来禁止语句使用关联字段的索引。

从上面的执行计划可以看出使用no_index提示后，优化器选择了使用hash join。

当索引的选择性不好时，优化器选择使用索引做Nest Loop Join是效率是很低的。b2b信息网

我们将实验的两个表中c1列的数据做一下更改,使其选择性变差，并在c1列上建普通索引。

update t1 set c1=1 where id<5000;

update t2 set c1=1 where id<5000;

create index idx_t1 on t1(c1);

当我们执行sql ：

这个查询结果会返回大量数据，被驱动表的关联字段c1列的索引选择性差，此时选择hash join是更明智的选择，但是优化器会选择走Nest Loop Join。我们可以通过实验验证一下hash join 与 Nest Loop Join的性能差异。

可以看出使用hash join的耗时是使用Nest Loop Join的1/6，但是优化器根据成本估算时，使用Nest Loop Join的成本要比使用hash join的成本低很多，所以会去选择Nest Loop Join，这个时候就需要加上hint 提示禁止使用关联字段的索引，被驱动表上每次都全表扫描的代价是很高的，这样优化器估算后就会选择走hash join。

MySQL官方文档里提到用BNL，源码下载NO_BNL的hint提示来影响hash join的优化，但是经过实验证明，在表连接关联字段上没有可用索引时，优化器估算成本后不会对被驱动表使用BNL全表扫描的方式做嵌套循环连接，而是会选择使用hash join，那这样NO_BNL在这个场景下就没有用武之地了。

那么既然不用这个索引，把这个索引去掉不就可以了吗？为什么非要使用no_index的hint提示呢，我们要知道业务使用的场景何其多，此处不用，别处使用了这个索引效率可能会有大的提升啊，这个时候就凸显了hint的优势，只需要控制此语句的使用就好了。

Nest Loop Join有其优势，它是response最快的连接方式，适用于返回数据量小的场景。当两个大表连接，返回大量数据，且关联字段的索引比较低效时，使用hash join就会比较高效，我们可以使用no_index的hint提示禁用关联字段的低效索引，促使优化器选择hash join。

相关文章