MySQL 8.0.29复合主键关联子查询的JOIN优化问题

问题场景 #

使用MySQL 8.0.29，需优化JOIN操作，实现嵌套循环中找到匹配记录即停止。已知单字段等值比较可实现该需求，但在复合主键场景下遇到执行效率或索引利用问题。

表结构 #

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)
);

需求 #

查询foo表中fooValue=10的所有记录对应的bar表数据。

已尝试方案及问题 #

方案1：复合字段子查询匹配 #

SQL语句：

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;

执行计划显示优化器采用哈希连接，bar表possible_keys未包含PRIMARY：

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        |

问题：未利用主键索引，哈希连接无法实现匹配即停止的逻辑。

方案2：拆分字段子查询匹配 #

SQL语句：

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|

问题：相同逻辑的子查询执行两次，造成性能冗余。

可行解决方案 #

方法1：使用LATERAL子查询（推荐） #

MySQL 8.0及以上支持LATERAL子查询，可关联外部表且仅执行一次，结合LIMIT 1实现匹配即停止：

explain select foo.*, b.* 
from foo
left join lateral (
    select * from bar b
    where b.barValueChecksum = crc32(foo.barValue)
      and b.barValue = foo.barValue
    limit 1
) as b on true
where foo.fooValue = 10;

• 执行计划会显示LATERAL子查询为DEPENDENT SUBQUERY，仅对foo表的每条记录执行一次
• LIMIT 1确保找到第一条匹配记录就停止查询，符合嵌套循环匹配即停的需求
• 可利用bar表的checksum索引快速定位数据

方法2：添加联合索引优化子查询 #

给bar表添加联合索引(barValueChecksum, barValue)，让子查询直接通过索引定位匹配行：

create index idx_bar_checksum_value on bar(barValueChecksum, barValue);

然后使用带LIMIT 1的复合字段子查询：

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
    limit 1
)
where foo.fooValue = 10;

• 联合索引可让子查询无需回表即可筛选数据，提升效率
• LIMIT 1确保子查询仅返回第一条匹配结果，避免多次执行

内容的提问来源于stack exchange，提问作者shklaurant