在 v7.5.6 测试也没有问题
mysql> CREATE TABLE `files` (
-> `id` bigint(20) NOT NULL AUTO_INCREMENT,
-> `project` varchar(32) DEFAULT NULL,
-> `parent_id` bigint(20) DEFAULT NULL,
-> `name` varchar(255) DEFAULT NULL,
-> PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
-> UNIQUE KEY `idx_unique_name_per_dir` (`project`,`parent_id`,`name`),
-> UNIQUE KEY `uk_file_project` (`parent_id`,`name`,`project`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1980949695643624753;
Query OK, 0 rows affected (0.09 sec)
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 |
+---------------------------------+---------+-----------+----------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.00 sec)
mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v7.5.6