我们这边看了下，不能走 IndexJoin 的原因是现在优化器会把所有的等值条件用做 Join Key，也包含这里的 substr(...)，但是又因为 IndexJoin 不支持 substr(...)，所以如果把 substr(...) 放到 Join Key 里面，就没法生成 IndexJoin，而只能走 HashJoin 了。
实际上一个更好的处理方式是把 substr(...) 不放到 Join Key 里面，放到 Other Key 里面，比如如下图这样，这样就能用上 IndexJoin 了：
我们这边记录了一个 issue，我们会在下一个 LTS v7.5 之前修复这个问题：https://github.com/pingcap/tidb/issues/47233 @人如其名