我觉得可以收集一下表的统计信息看一下，我试了下在 v8.5.3 也是 IndexRageScan
mysql> DESC SELECT *
-> FROM `files` f0
-> LEFT JOIN `files` `f1` force index(uk_file_project) ON `f0`.`id` = `f1`.`parent_id` AND `f1`.`name` = "bbbb"
-> LEFT JOIN `files` `f2` force index(uk_file_project) ON `f1`.`id` = `f2`.`parent_id` AND `f2`.`name` = "xxxx"
-> WHERE `f0`.`parent_id` = 0
-> AND `f0`.`name` = "/"
-> AND `f0`.`project` = "hhh";
+---------------------------------+---------+-----------+----------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------------+---------+-----------+----------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| IndexJoin_14 | 1.56 | root | | left outer join, inner:IndexReader_13, outer key:test.files.id, inner key:test.files.parent_id, equal cond:eq(test.files.id, test.files.parent_id) |
| ├─IndexHashJoin_29(Build) | 1.25 | root | | left outer join, inner:IndexReader_26, outer key:test.files.id, inner key:test.files.parent_id, equal cond:eq(test.files.id, test.files.parent_id) |
| │ ├─Point_Get_37(Build) | 1.00 | root | table:files, index:idx_unique_name_per_dir(project, parent_id, name) | |
| │ └─IndexReader_26(Probe) | 1.25 | root | | index:Selection_25 |
| │ └─Selection_25 | 1.25 | cop[tikv] | | not(isnull(test.files.parent_id)) |
| │ └─IndexRangeScan_24 | 1.25 | cop[tikv] | table:f1, index:uk_file_project(parent_id, name, project) | range: decided by [eq(test.files.parent_id, test.files.id) eq(test.files.name, bbbb)], keep order:false, stats:pseudo |
| └─IndexReader_13(Probe) | 1.56 | root | | index:Selection_12 |
| └─Selection_12 | 1.56 | cop[tikv] | | not(isnull(test.files.parent_id)) |
| └─IndexRangeScan_11 | 1.56 | cop[tikv] | table:f2, index:uk_file_project(parent_id, name, project) | range: decided by [eq(test.files.parent_id, test.files.id) eq(test.files.name, xxxx)], keep order:false, stats:pseudo |
+---------------------------------+---------+-----------+----------------------------------------------------------------------+---------------------------------------------------------------------------------------------