关于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()
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;
//
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 社区助手 微信好友，发送验证信息 加群。