关于mysql:如何干涉MySQL优化器使用hash-join

GreatSQL社区原创内容未经受权不得随便应用，转载请分割小编并注明起源。
GreatSQL是MySQL的国产分支版本，应用上与MySQL统一。
前言
试验
总结

前言

数据库的优化器相当于人类的大脑，大部分时候都能做出正确的决策，制订正确的执行打算，走出一条高效的路，然而它毕竟是基于某些固定的规定、算法来做的判断，有时候并没有咱们人脑思维灵便，当咱们确定优化器抉择执行打算谬误时该怎么办呢，语句上加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在管制表连贯形式上没有提供那么多丰盛的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()BEGINdeclare i int;set i=1;while i<10001 doinsert into t1 values(i,i,i);insert into t2 values(i,i,i);SET i = i + 1;end while;END;//delimiter ;

查问一下两表应用主键字段关联查问时理论的执行打算，如下图所示：

查问一下两表应用非索引字段关联查问时理论的执行打算，如下图所示：

从执行打算能够看出，被驱动表的关联字段上有索引，优化器在抉择表连贯形式时会偏向于抉择Nest Loop Join，当没有可用索引时偏向于抉择hash join。

基于这一点那咱们能够应用no_index提醒来禁止语句应用关联字段的索引。

从下面的执行打算能够看出应用no_index提醒后，优化器抉择了应用hash join。

当索引的选择性不好时，优化器抉择应用索引做Nest Loop Join是效率是很低的。

咱们将试验的两个表中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);create index idx_t2 on t2(c1);

当咱们执行sql ：

select t1.*,t2.* from t1 join t2 on t1.c1=t2.c1;

这个查问后果会返回大量数据，被驱动表的关联字段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。

Enjoy GreatSQL :)

## 对于 GreatSQL

GreatSQL是由万里数据库保护的MySQL分支，专一于晋升MGR可靠性及性能，反对InnoDB并行查问个性，是实用于金融级利用的MySQL分支版本。

相干链接： GreatSQL社区 Gitee GitHub Bilibili

GreatSQL社区：

欢送来GreatSQL社区发帖发问
https://greatsql.cn/

技术交换群：

微信：扫码增加GreatSQL社区助手微信好友，发送验证信息加群。