Mysql 对具有依赖子查询结果的2列使用等式比较来优化连接

我试图通过在 for each 嵌套循环找到一条记录时停止来优化联接.

MySQL版本为8.0.29.

我从MySQL JOIN with LIMIT 1 on joined table中发现，我可以使用单列相等比较来做到这一点，但我很难使用复合主键来做到这一点.

create table foo (
  id int unsigned, 
  fooValue int, 
  barValue binary(32), 
  primary key (id), 
  index fooV (fooValue)
);

create table bar (
  id int unsigned, 
  barValueChecksum int unsigned, 
  barValue binary(32), 
  primary key (id, barValueChecksum), 
  index checksum (barValueChecksum)
);

我想找出bar个S换foo个S有一定价值

explain select * from foo
    left join bar on (bar.id, bar.barValueChecksum)
        = (select b.id, b.barValueChecksum from bar b 
                   where b.barValueChecksum = crc32(foo.barValue)
                     and b.barValue = foo.barValue)
where foo.fooValue = 10;

但它又回来了

id|select_type       |table|partitions|type|possible_keys|key     |key_len|ref  |rows|filtered|Extra                                     |
--+------------------+-----+----------+----+-------------+--------+-------+-----+----+--------+------------------------------------------+
 1|PRIMARY           |foo  |          |ref |fooV         |fooV    |5      |const|   1|   100.0|                                          |
 1|PRIMARY           |bar  |          |ALL |             |        |       |     |   1|   100.0|Using where; Using join buffer (hash join)|
 2|DEPENDENT SUBQUERY|b    |          |ref |checksum     |checksum|4      |func |   1|   100.0|Using index condition; Using where        |

优化器try 使用散列联接，更糟糕的是，PRIMARY没有出现在表bar的possible_keys节中.

通过分离比较，我可以获得与我想要的结果类似的结果，但它运行子查询两次

explain select * from foo
    left join bar on (bar.id 
                            = (select b.id from bar b 
                                where b.barValueChecksum = crc32(foo.barValue) 
                                      and b.barValue = foo.barValue))
                 and (bar.barValueChecksum 
                            = (select b.barValueChecksum from bar 
                                b where b.barValueChecksum = crc32(foo.barValue) 
                                        and b.barValue = foo.barValue))
where foo.fooValue = 10;

这导致了

id|select_type       |table|partitions|type  |possible_keys   |key     |key_len|ref      |rows|filtered|Extra                             |
--+------------------+-----+----------+------+----------------+--------+-------+---------+----+--------+----------------------------------+
 1|PRIMARY           |foo  |          |ref   |fooV            |fooV    |5      |const    |   1|   100.0|                                  |
 1|PRIMARY           |bar  |          |eq_ref|PRIMARY,checksum|PRIMARY |8      |func,func|   1|   100.0|Using where                       |
 3|DEPENDENT SUBQUERY|b    |          |ref   |checksum        |checksum|4      |func     |   1|   100.0|Using index condition; Using where|
 2|DEPENDENT SUBQUERY|b    |          |ref   |checksum        |checksum|4      |func     |   1|   100.0|Using index condition; Using where|

解决方案是什么？