今天在生产环境上遇到一个SQL 明明join表的关联字段上有索引，MySQL就是不走，由于版本又是8.0.13之前的版本。没有hash join 只能走BNL 让整个SQL奇慢无比
执行计划是这样的
执行需要93S 非常非常慢
但实际我在关联字段上不仅有索引，而且两表关联关系是1对1的。
我加了force index后 执行只需要 1.47S
为了弄清楚原因。我在测试环境进行了数据构造进行重现
构造测试数据
create table test1
(
table_schema varchar(50),
tablename varchar(200),
ver int,
id int primary key
);
create table test2
(
table_schema varchar(50),
tablename varchar(200),
ver int,
fromid int,
id int primary key
);
set @i:=0;
insert into test1(table_schema,tablename,ver,id)
select a.table_schema,a.table_name,a.version ,@i := @i+ 1 as id
from information_schema.tables a, information_schema.tables b;
set @i:=0;
insert into test2(table_schema,tablename,ver,id)
select a.table_schema,a.table_name,a.version ,@i := @i+ 1 as id
from information_schema.tables a, information_schema.tables b;
update test2 set fromid = 0;
update test2 set fromid = id where mod(id,2000000)= 0;
alter table test2 add index idx_fromid(fromid);
analyze table test2;
explain
select * from test1 a
left join test2 b on a.id = b.fromid
where a.id
