mysql> explain analyze SELECT * FROM `files` f0 LEFT JOIN `files` `f1` force index(uk_file_project) ON `f0`.`id` = `f1`.`parent_id` AND `f1`.`name` = "ffff" LEFT JOIN `files` `f2` force index(uk_file_project) ON `f1`.`id` = `f2`.`parent_id` AND `f2`.`name` = "bbbb" LEFT JOIN `files` `f3` force index(uk_file_project) ON `f2`.`id` = `f3`.`parent_id` AND `f3`.`name` = "xxxx" WHERE `f0`.`parent_id` = 0 AND `f0`.`name` = "/" AND `f0`.`project` = "hhh";
+------------------------------------------+---------------+------------+-----------+----------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------------------+---------------+------------+-----------+----------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| HashJoin_16 | 60.64 | 1 | root | | time:8m12.4s, loops:2, RU:9627243.168347, build_hash_table:{total:1.41ms, fetch:1.4ms, build:4.59µs}, probe:{concurrency:5, total:41m2s, max:8m12.4s, probe:50.8µs, fetch:41m2s} | left outer join, equal:[eq(godepo_hz.files.id, godepo_hz.files.parent_id)] | 123.9 KB | 0 Bytes |
| ├─Point_Get_17(Build) | 1.00 | 1 | root | table:files, index:idx_unique_name_per_dir(project, parent_id, name) | time:1.29ms, loops:2, Get:{num_rpc:2, total_time:1.21ms}, total_process_time: 204.7µs, total_wait_time: 380.2µs, tikv_wall_time: 633.3µs, scan_detail: {total_process_keys: 2, total_process_keys_size: 284, total_keys: 2, get_snapshot_time: 322.9µs, rocksdb: {block: {cache_hit_count: 23}}} | | N/A | N/A |
| └─HashJoin_18(Probe) | 60.64 | 128 | root | | time:8m12.4s, loops:2, build_hash_table:{total:8m12.2s, fetch:8m12.2s, build:7.78µs}, probe:{concurrency:5, total:41m1.9s, max:8m12.4s, probe:240.3µs, fetch:41m1.9s} | left outer join, equal:[eq(godepo_hz.files.id, godepo_hz.files.parent_id)] | 246.6 KB | 0 Bytes |
| ├─IndexJoin_28(Build) | 0.00 | 1 | root | | time:8m12.2s, loops:2, inner:{total:57.3ms, concurrency:5, task:1, construct:5.12µs, fetch:57.3ms, build:183ns}, probe:7.93µs | left outer join, inner:IndexLookUp_27, 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) | 123.5 KB | N/A |
| │ ├─IndexLookUp_42(Build) | 0.00 | 1 | root | | time:8m12.1s, loops:3, index_task: {total_time: 8m12.1s, fetch_handle: 8m12.1s, build: 684ns, wait: 2.18µs}, table_task: {total_time: 25.1ms, num: 1, concurrency: 5}, next: {wait_index: 8m12.1s, wait_table_lookup_build: 2.95ms, wait_table_lookup_resp: 22ms} | | 13.1 KB | N/A |
| │ │ ├─Selection_41(Build) | 0.00 | 1 | cop[tikv] | | time:8m12.1s, loops:3, cop_task: {num: 3588, max: 0s, min: 0s, avg: 2.05s, p95: 4.01s, tot_proc: 36m40.8s, tot_wait: 15.2s, copr_cache_hit_ratio: 0.01, build_task_duration: 5.14ms, max_distsql_concurrency: 15}, rpc_info:{Cop:{num_rpc:3588, total_time:2h2m50.1s}}, backoff{regionMiss: 2ms}, tikv_task:{proc max:0s, min:0s, avg: 2.06s, p80:3.35s, p95:4.01s, iters:2615691, tasks:3588}, scan_detail: {total_process_keys: 2643606632, total_process_keys_size: 267205223023, total_keys: 2761709282, get_snapshot_time: 6.45s, rocksdb: {delete_skipped_count: 2258280, key_skipped_count: 2763966105, block: {cache_hit_count: 3543713, read_count: 4444498, read_byte: 32.2 GB, read_time: 3m31.2s}}} | eq(godepo_hz.files.name, "xxxx") | N/A | N/A |
| │ │ │ └─IndexFullScan_39 | 2661622282.00 | 2661846276 | cop[tikv] | table:f2, index:uk_file_project(parent_id, name, project) | tikv_task:{proc max:0s, min:0s, avg: 1.99s, p80:3.26s, p95:3.92s, iters:2615691, tasks:3588} | keep order:false | N/A | N/A |
| │ │ └─TableRowIDScan_40(Probe) | 0.00 | 1 | cop[tikv] | table:f2 | time:22ms, loops:2, cop_task: {num: 1, max: 21.9ms, proc_keys: 1, tot_proc: 12.2ms, tot_wait: 625.5µs, copr_cache_hit_ratio: 0.00, build_task_duration: 20.3µs, max_distsql_concurrency: 1, max_extra_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:21.9ms}}, tikv_task:{time:24ms, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 197, total_keys: 1, get_snapshot_time: 615.4µs, rocksdb: {block: {cache_hit_count: 7, read_count: 5, read_byte: 592.8 KB, read_time: 11.5ms}}} | keep order:false | N/A | N/A |
| │ └─IndexLookUp_27(Probe) | 0.00 | 0 | root | | time:57.2ms, loops:1 | | 356 Bytes | N/A |
| │ ├─Selection_26(Build) | 0.00 | 0 | cop[tikv] | | time:57.2ms, loops:1, cop_task: {num: 1, max: 57.1ms, proc_keys: 0, tot_proc: 8.46ms, tot_wait: 48.3ms, copr_cache_hit_ratio: 0.00, build_task_duration: 19.3µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:57.1ms}}, tikv_task:{time:8ms, loops:1}, scan_detail: {total_keys: 1, get_snapshot_time: 1.38ms, rocksdb: {block: {cache_hit_count: 11, read_count: 4, read_byte: 80.3 KB, read_time: 7.91ms}}} | not(isnull(godepo_hz.files.parent_id)) | N/A | N/A |
| │ │ └─IndexRangeScan_24 | 0.00 | 0 | cop[tikv] | table:f3, index:uk_file_project(parent_id, name, project) | tikv_task:{time:8ms, loops:1} | range: decided by [eq(godepo_hz.files.parent_id, godepo_hz.files.id) eq(godepo_hz.files.name, xxxx)], keep order:false | N/A | N/A |
| │ └─TableRowIDScan_25(Probe) | 0.00 | 0 | cop[tikv] | table:f3 | | keep order:false | N/A | N/A |
| └─IndexLookUp_23(Probe) | 60.64 | 128 | root | | time:8m12.4s, loops:2, index_task: {total_time: 8m12.1s, fetch_handle: 8m12.1s, build: 609ns, wait: 2.06µs}, table_task: {total_time: 320.8ms, num: 1, concurrency: 5}, next: {wait_index: 8m12.1s, wait_table_lookup_build: 3.02ms, wait_table_lookup_resp: 317.8ms} | | 48.4 KB | N/A |
| ├─Selection_22(Build) | 60.64 | 128 | cop[tikv] | | time:8m12.1s, loops:3, cop_task: {num: 3588, max: 0s, min: 0s, avg: 2.05s, p95: 4.01s, tot_proc: 36m44.1s, tot_wait: 15.6s, copr_cache_hit_ratio: 0.01, build_task_duration: 5.46ms, max_distsql_concurrency: 15}, rpc_info:{Cop:{num_rpc:3588, total_time:2h2m50.1s}}, backoff{regionMiss: 2ms}, tikv_task:{proc max:0s, min:0s, avg: 2.06s, p80:3.35s, p95:4s, iters:2615691, tasks:3588}, scan_detail: {total_process_keys: 2643606632, total_process_keys_size: 267205223023, total_keys: 2761709282, get_snapshot_time: 6.52s, rocksdb: {delete_skipped_count: 2258280, key_skipped_count: 2763966105, block: {cache_hit_count: 3528309, read_count: 4459909, read_byte: 32.3 GB, read_time: 3m32.8s}}} | eq(godepo_hz.files.name, "dddd") | N/A | N/A |
| │ └─IndexFullScan_20 | 2661622282.00 | 2661846276 | cop[tikv] | table:f1, index:uk_file_project(parent_id, name, project) | tikv_task:{proc max:0s, min:0s, avg: 1.99s, p80:3.26s, p95:3.9s, iters:2615691, tasks:3588} | keep order:false | N/A | N/A |
| └─TableRowIDScan_21(Probe) | 60.64 | 128 | cop[tikv] | table:f1 | time:317.7ms, loops:2, cop_task: {num: 96, max: 299.5ms, min: 0s, avg: 21ms, p95: 179.5ms, max_proc_keys: 15, p95_proc_keys: 3, tot_proc: 3.37s, tot_wait: 3.15s, copr_cache_hit_ratio: 0.00, build_task_duration: 555.6µs, max_distsql_concurrency: 1, max_extra_concurrency: 8, store_batch_num: 76}, rpc_info:{Cop:{num_rpc:20, total_time:2.01s}}, tikv_task:{proc max:192ms, min:0s, avg: 43.1ms, p80:84ms, p95:176ms, iters:96, tasks:96}, scan_detail: {total_process_keys: 128, total_process_keys_size: 34059, total_keys: 128, get_snapshot_time: 2.44s, rocksdb: {block: {cache_hit_count: 1282, read_count: 454, read_byte: 203.1 MB, read_time: 3.2s}}} | keep order:false | N/A | N/A |
+------------------------------------------+---------------+------------+-----------+----------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
16 rows in set (8 min 12.39 sec)