mysql8hash join测试

两个表结构如下：

CREATE TABLE `t1` (

`id` int(11) NOT NULL,

`a` int(11) DEFAULT NULL,

`b` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `b` (`b`)

) ENGINE=InnoDB; --------100万行数据

CREATE TABLE `t2` (

`id` int(11) NOT NULL,

`a` int(11) DEFAULT NULL,

`b` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `b` (`b`)

) ENGINE=InnoDB; ---------100万行数据

1.inner join

两个表where列都无无索引：

explain analyze select * from t1 join t2 on t1.a=t2.a;

走hash join.

一个表加索引:

create index a on t1(a); 或 create index a on t2(a);

explain analyze select * from t1 join t2 on t1.a=t2.a;

走索引扫描.

两个表都加索引：

create index a on test_data(a);

create index a on test_data2(a);

explain analyze select * from t1 join t2 on t1.a=t2.a;

走索引扫描

1. left/right join

两表字段都无索引:

explain analyze select * from t1 left join t2 on t1.a=t2.a;

走hash join.

左表加索引:

create index a on t1(a);

explain analyze select * from t1 left join t2 on t1.a=t2.a;

还是走hash join.

右边表加索引，左边不加:

create index a on t2(a);

Drop index a on t1;

explain analyze select * from t1 left join t2 on t1.a=t2.a;

走索引扫描.

两个表都加索引测试结果：

explain analyze select * from t1 left join t2 on t1.a=t2.a;

走索引扫描

结论：只要右边表where字段不加索引，就走hash join

1. semi join

两个表都无索引：

Explain analyze select * from t1 where exists (select 1 from t2 where t1.a=t2.a);

走hash join

外部表加索引：

Create index a on t1(a);

Explain analyze select * from t1 where exists (select 1 from t2 where t1.a=t2.a);

没有走hash join

子查询表加索引：

Explain analyze select * from t1 where exists (select 1 from t2 where t1.a=t2.a);

没有走hash join

两个表都加索引：

Explain analyze select * from t1 where exists (select 1 from t2 where t1.a=t2.a);

结论：只要有索引就不走hash join ，但子查询加索引速度更快与hash差不多

1. anti join

两表相关字段都无索引

explain analyze select * from t1 where not exists (select 1 from t2 where t1.a=t2.a);

没有走hash join ???????

1. Non-equi join

explain analyze select * from t3 join t4 on t1.a