tidb version 7.5.6
问题：线上业务一个多次自关联查询，执行的很慢，看执行计划发现没有达到预期，加上force index也是一样。
表结构如下
mysql> show create table files\G
*************************** 1. row ***************************
Table: files
Create Table: CREATE TABLE `files` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`project` varchar(32) DEFAULT NULL,
`parent_id` bigint(20) 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
1 row in set (0.01 sec)
mysql>
业务sql如下，通过下面的执行计划可以知道f1表是走了全索引扫描，导致整个执行很慢，这个有点搞不明白为什么会走了全索引扫描。
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 |
+--------------------------------------+---------------+-----------+----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashJoin_12 | 60.64 | root | | left outer join, equal:[eq(godepo_hz.files.id, godepo_hz.files.parent_id)] |
| ├─Point_Get_13(Build) | 1.00 | root | table:files, index:idx_unique_name_per_dir(project, parent_id, name) | |
| └─IndexJoin_18(Probe) | 60.64 | root | | left outer join, inner:IndexLookUp_17, outer key:godepo_hz.files.id, inner key:godepo_hz.files.parent_id, equal cond:eq(godepo_hz.files.id, godepo_hz.files.parent_id) |
| ├─IndexLookUp_32(Build) | 60.64 | root | | |
| │ ├─Selection_31(Build) | 60.64 | cop[tikv] | | eq(godepo_hz.files.name, "gph") |
| │ │ └─IndexFullScan_29 | 2661622282.00 | cop[tikv] | table:f1, index:uk_file_project(parent_id, name, project) | keep order:false |
| │ └─TableRowIDScan_30(Probe) | 60.64 | cop[tikv] | table:f1 | keep order:false |
| └─IndexLookUp_17(Probe) | 0.00 | root | | |
| ├─Selection_16(Build) | 0.00 | cop[tikv] | | not(isnull(godepo_hz.files.parent_id)) |
| │ └─IndexRangeScan_14 | 0.00 | cop[tikv] | table:f2, index:uk_file_project(parent_id, name, project) | range: decided by [eq(godepo_hz.files.parent_id, godepo_hz.files.id) eq(godepo_hz.files.name, zq_smoke_stable_ios)], keep order:false |
| └─TableRowIDScan_15(Probe) | 0.00 | cop[tikv] | table:f2 | keep order:false |
+--------------------------------------+---------------+-----------+----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
11 rows in set (0.00 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"
-> WHERE `f0`.`parent_id` = 0
-> AND `f0`.`name` = "/"
-> AND `f0`.`project` = "hhh";
+---------------------------------+---------+-----------+----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------------+---------+-----------+----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| IndexHashJoin_14 | 60.64 | root | | left outer join, inner:IndexLookUp_11, outer key:godepo_hz.files.id, inner key:godepo_hz.files.parent_id, equal cond:eq(godepo_hz.files.id, godepo_hz.files.parent_id) |
| ├─Point_Get_23(Build) | 1.00 | root | table:files, index:idx_unique_name_per_dir(project, parent_id, name) | |
| └─IndexLookUp_11(Probe) | 60.64 | root | | |
| ├─Selection_10(Build) | 60.64 | cop[tikv] | | not(isnull(godepo_hz.files.parent_id)) |
| │ └─IndexRangeScan_8 | 60.64 | cop[tikv] | table:f1, index:uk_file_project(parent_id, name, project) | range: decided by [eq(godepo_hz.files.parent_id, godepo_hz.files.id) eq(godepo_hz.files.name, gph)], keep order:false |
| └─TableRowIDScan_9(Probe) | 60.64 | cop[tikv] | table:f1 | keep order:false |
+---------------------------------+---------+-----------+----------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)