mysql> show create table edges\G
*************************** 1. row ***************************
Table: edges
Create Table: CREATE TABLE `edges` (
`id` bigint NOT NULL AUTO_INCREMENT,
`from_event_id` varchar(64) NOT NULL,
`to_entity_id` varchar(64) NOT NULL,
`to_entity_type` int NOT NULL,
`from_event_type` int NOT NULL,
`client_id` varchar(64) NOT NULL,
`payload` varchar(255) DEFAULT 'padding',
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
KEY `idx_to_entity_id` (`to_entity_id`),
KEY `idx_merge_type` (`to_entity_id`,`to_entity_type`),
KEY `idx_merge_event` (`to_entity_id`,`from_event_type`),
KEY `idx_merge_client` (`to_entity_id`,`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=290898
1 row in set (0.01 sec)
EXPLAIN ANALYZE
SELECT /*+ use_index_merge(edges, idx_merge_type, idx_merge_event, idx_merge_client) */ * FROM edges
WHERE
to_entity_id = 'entity_heavy_burden'
AND (
to_entity_type IN (1, 2, 3) -- 对应 idx_merge_type
OR from_event_type IN (1, 2, 3) -- 对应 idx_merge_event
OR client_id = 'abc' -- 对应 idx_merge_client
)
LIMIT 200;
+----------------------------------+-----------+---------+-----------+-------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------+-----------+---------+-----------+-------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------+---------+------+
| Limit_9 | 28.00 | 20 | root | | time:439.6ms, loops:2, RU:1976.82 | offset:0, count:200 | N/A | N/A |
| └─IndexMerge_17 | 28.00 | 20 | root | | time:439.6ms, loops:2, index_task:{fetch_handle:212.913668ms, merge:260.818583ms}, table_task:{num:10, concurrency:5, fetch_row:1.775594875s, wait_time:407.751166ms} | type: intersection | 20.2 MB | N/A |
| ├─IndexRangeScan_11(Build) | 200000.00 | 200000 | cop[tikv] | table:edges, index:idx_merge_type(to_entity_id, to_entity_type) | time:66.7ms, loops:198, cop_task: {num: 5, max: 19.3ms, min: 9.17ms, avg: 13.9ms, p95: 19.3ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 67.3ms, tot_wait: 275.2µs, copr_cache_hit_ratio: 0.00, build_task_duration: 8.75µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:5, total_time:69.7ms}}, tikv_task:{proc max:19ms, min:8ms, avg: 13ms, p80:19ms, p95:19ms, iters:216, tasks:5}, scan_detail: {total_process_keys: 200000, total_process_keys_size: 22800000, total_keys: 200005, get_snapshot_time: 138.1µs, rocksdb: {delete_skipped_count: 10000, key_skipped_count: 210000, block: {cache_hit_count: 51, read_count: 248, read_byte: 7.73 MB, read_time: 7.96ms}}}, time_detail: {total_process_time: 67.3ms, total_suspend_time: 49.3µs, total_wait_time: 275.2µs, total_kv_read_wall_time: 65ms, tikv_wall_time: 68ms} | range:["entity_heavy_burden","entity_heavy_burden"], keep order:false | N/A | N/A |
| ├─IndexRangeScan_12(Build) | 200000.00 | 200000 | cop[tikv] | table:edges, index:idx_merge_event(to_entity_id, from_event_type) | time:66.9ms, loops:198, cop_task: {num: 5, max: 18.1ms, min: 9.08ms, avg: 13.3ms, p95: 18.1ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 64.8ms, tot_wait: 219.8µs, copr_cache_hit_ratio: 0.00, build_task_duration: 10.3µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:5, total_time:66.6ms}}, tikv_task:{proc max:16ms, min:9ms, avg: 12.4ms, p80:16ms, p95:16ms, iters:216, tasks:5}, scan_detail: {total_process_keys: 200000, total_process_keys_size: 22800000, total_keys: 200005, get_snapshot_time: 103.7µs, rocksdb: {delete_skipped_count: 10000, key_skipped_count: 210000, block: {cache_hit_count: 40, read_count: 250, read_byte: 7.79 MB, read_time: 8.27ms}}}, time_detail: {total_process_time: 64.8ms, total_suspend_time: 40.9µs, total_wait_time: 219.8µs, total_kv_read_wall_time: 62ms, tikv_wall_time: 65.2ms} | range:["entity_heavy_burden","entity_heavy_burden"], keep order:false | N/A | N/A |
| ├─IndexRangeScan_13(Build) | 200000.00 | 200000 | cop[tikv] | table:edges, index:idx_merge_client(to_entity_id, client_id) | time:66.9ms, loops:198, cop_task: {num: 5, max: 19.2ms, min: 9.65ms, avg: 13.9ms, p95: 19.2ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 67.7ms, tot_wait: 293.2µs, copr_cache_hit_ratio: 0.00, build_task_duration: 7.58µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:5, total_time:69.5ms}}, tikv_task:{proc max:19ms, min:8ms, avg: 13ms, p80:19ms, p95:19ms, iters:216, tasks:5}, scan_detail: {total_process_keys: 200000, total_process_keys_size: 23599986, total_keys: 200005, get_snapshot_time: 125.3µs, rocksdb: {delete_skipped_count: 10000, key_skipped_count: 210000, block: {cache_hit_count: 60, read_count: 260, read_byte: 8.10 MB, read_time: 8.93ms}}}, time_detail: {total_process_time: 67.7ms, total_suspend_time: 37.4µs, total_wait_time: 293.2µs, total_kv_read_wall_time: 65ms, tikv_wall_time: 68.3ms} | range:["entity_heavy_burden","entity_heavy_burden"], keep order:false | N/A | N/A |
| └─Limit_16(Probe) | 28.00 | 20 | cop[tikv] | | time:1.72s, loops:19, cop_task: {num: 10, max: 176.2ms, min: 164.9ms, avg: 172.3ms, p95: 176.2ms, max_proc_keys: 20000, p95_proc_keys: 20000, tot_proc: 1.26s, tot_wait: 383.9ms, copr_cache_hit_ratio: 0.00, build_task_duration: 6.04ms, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:10, total_time:1.72s}}, tikv_task:{proc max:168ms, min:159ms, avg: 163.9ms, p80:168ms, p95:168ms, iters:240, tasks:10}, scan_detail: {total_process_keys: 200000, total_process_keys_size: 27688876, total_keys: 217443, get_snapshot_time: 165.8µs, rocksdb: {delete_skipped_count: 341, key_skipped_count: 37051, block: {cache_hit_count: 241359}}}, time_detail: {total_process_time: 1.26s, total_suspend_time: 383.5ms, total_wait_time: 383.9ms, total_kv_read_wall_time: 1.62s, tikv_wall_time: 1.68s} | offset:0, count:200 | N/A | N/A |
| └─Selection_15 | 28.00 | 20 | cop[tikv] | | tikv_task:{proc max:168ms, min:159ms, avg: 163.9ms, p80:168ms, p95:168ms, iters:240, tasks:10} | or(in(test.edges.to_entity_type, 1, 2, 3), or(in(test.edges.from_event_type, 1, 2, 3), eq(test.edges.client_id, "abc"))) | N/A | N/A |
| └─TableRowIDScan_14 | 200000.00 | 200000 | cop[tikv] | table:edges | tikv_task:{proc max:166ms, min:158ms, avg: 162.2ms, p80:166ms, p95:166ms, iters:240, tasks:10} | keep order:false | N/A | N/A |
+----------------------------------+-----------+---------+-----------+-------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------+---------+------+
8 rows in set (0.44 sec)
explain analyze (SELECT * FROM edges
WHERE to_entity_id = 'entity_heavy_burden'
AND to_entity_type IN (1, 2, 3) LIMIT 200)
UNION
(SELECT * FROM edges
WHERE to_entity_id = 'entity_heavy_burden'
AND from_event_type IN (1, 2, 3) LIMIT 200)
UNION
(SELECT * FROM edges
WHERE to_entity_id = 'entity_heavy_burden'
AND client_id = 'abc' LIMIT 200)
LIMIT 200;
+----------------------------------------+---------+---------+-----------+-------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+----------------------------------------+---------+---------+-----------+-------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+
| Limit_29 | 28.00 | 20 | root | | time:845.9µs, loops:6, RU:3.12 | offset:0, count:200 | N/A | N/A |
| └─HashAgg_30 | 28.00 | 20 | root | | time:842.9µs, loops:6, partial_worker:{wall_time:823µs, concurrency:5, task_num:3, tot_wait:2.224084ms, tot_exec:40.375µs, tot_time:4.072543ms, max:816.709µs, p95:816.709µs}, final_worker:{wall_time:842.875µs, concurrency:5, task_num:12, tot_wait:4.29µs, tot_exec:2.124µs, tot_time:4.152833ms, max:836.334µs, p95:836.334µs} | group by:Column#22, Column#23, Column#24, Column#25, Column#26, Column#27, Column#28, funcs:firstrow(Column#22)->Column#22, funcs:firstrow(Column#23)->Column#23, funcs:firstrow(Column#24)->Column#24, funcs:firstrow(Column#25)->Column#25, funcs:firstrow(Column#26)->Column#26, funcs:firstrow(Column#27)->Column#27, funcs:firstrow(Column#28)->Column#28 | 37.3 KB | 0 Bytes |
| └─Union_31 | 28.00 | 20 | root | | time:797.4µs, loops:4 | | N/A | N/A |
| ├─IndexLookUp_42 | 12.00 | 3 | root | | time:650.4µs, loops:2, index_task: {total_time: 372.3µs, fetch_handle: 371.8µs, build: 83ns, wait: 375ns}, table_task: {total_time: 245.6µs, num: 1, concurrency: 5}, next: {wait_index: 395.8µs, wait_table_lookup_build: 25.6µs, wait_table_lookup_resp: 221.8µs} | limit embedded(offset:0, count:200) | 4.95 KB | N/A |
| │ ├─Limit_41(Build) | 12.00 | 3 | cop[tikv] | | time:371.5µs, loops:3, cop_task: {num: 1, max: 364.5µs, proc_keys: 3, tot_proc: 96.8µs, tot_wait: 60.3µs, copr_cache_hit_ratio: 0.00, build_task_duration: 7.33µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:361.8µs}}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 3, total_process_keys_size: 342, total_keys: 6, get_snapshot_time: 48.7µs, rocksdb: {key_skipped_count: 3, block: {cache_hit_count: 12}}}, time_detail: {total_process_time: 96.8µs, total_wait_time: 60.3µs, tikv_wall_time: 210.1µs} | offset:0, count:200 | N/A | N/A |
| │ │ └─IndexRangeScan_39 | 12.00 | 3 | cop[tikv] | table:edges, index:idx_merge_type(to_entity_id, to_entity_type) | tikv_task:{time:0s, loops:1} | range:["entity_heavy_burden" 1,"entity_heavy_burden" 1], ["entity_heavy_burden" 2,"entity_heavy_burden" 2], ["entity_heavy_burden" 3,"entity_heavy_burden" 3], keep order:false | N/A | N/A |
| │ └─TableRowIDScan_40(Probe) | 12.00 | 3 | cop[tikv] | table:edges | time:214.3µs, loops:2, cop_task: {num: 1, max: 189.9µs, proc_keys: 3, tot_proc: 58.6µs, tot_wait: 18.2µs, copr_cache_hit_ratio: 0.00, build_task_duration: 10.9µs, max_distsql_concurrency: 1, max_extra_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:187.3µs}}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 3, total_process_keys_size: 417, total_keys: 3, get_snapshot_time: 8.38µs, rocksdb: {block: {cache_hit_count: 3}}}, time_detail: {total_process_time: 58.6µs, total_wait_time: 18.2µs, tikv_wall_time: 106.8µs} | keep order:false | N/A | N/A |
| ├─IndexLookUp_53 | 12.00 | 10 | root | | time:763.7µs, loops:2, index_task: {total_time: 373µs, fetch_handle: 372.3µs, build: 374ns, wait: 334ns}, table_task: {total_time: 354.8µs, num: 1, concurrency: 5}, next: {wait_index: 396.2µs, wait_table_lookup_build: 26.5µs, wait_table_lookup_resp: 333.2µs} | limit embedded(offset:0, count:200) | 6.35 KB | N/A |
| │ ├─Limit_52(Build) | 12.00 | 10 | cop[tikv] | | time:371.8µs, loops:3, cop_task: {num: 1, max: 357µs, proc_keys: 10, tot_proc: 61µs, tot_wait: 108.3µs, copr_cache_hit_ratio: 0.00, build_task_duration: 5.29µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:352.3µs}}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 10, total_process_keys_size: 1140, total_keys: 13, get_snapshot_time: 100.3µs, rocksdb: {key_skipped_count: 10, block: {cache_hit_count: 12}}}, time_detail: {total_process_time: 61µs, total_wait_time: 108.3µs, tikv_wall_time: 197.9µs} | offset:0, count:200 | N/A | N/A |
| │ │ └─IndexRangeScan_50 | 12.00 | 10 | cop[tikv] | table:edges, index:idx_merge_event(to_entity_id, from_event_type) | tikv_task:{time:0s, loops:1} | range:["entity_heavy_burden" 1,"entity_heavy_burden" 1], ["entity_heavy_burden" 2,"entity_heavy_burden" 2], ["entity_heavy_burden" 3,"entity_heavy_burden" 3], keep order:false | N/A | N/A |
| │ └─TableRowIDScan_51(Probe) | 12.00 | 10 | cop[tikv] | table:edges | time:327.5µs, loops:2, cop_task: {num: 1, max: 305.3µs, proc_keys: 10, tot_proc: 163.4µs, tot_wait: 15.2µs, copr_cache_hit_ratio: 0.00, build_task_duration: 9.79µs, max_distsql_concurrency: 1, max_extra_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:301.1µs}}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 10, total_process_keys_size: 1379, total_keys: 10, get_snapshot_time: 9.33µs, rocksdb: {block: {cache_hit_count: 18}}}, time_detail: {total_process_time: 163.4µs, total_wait_time: 15.2µs, tikv_wall_time: 214.1µs} | keep order:false | N/A | N/A |
| └─IndexLookUp_64 | 4.00 | 7 | root | | time:716.8µs, loops:2, index_task: {total_time: 355.9µs, fetch_handle: 355µs, build: 542ns, wait: 416ns}, table_task: {total_time: 331.3µs, num: 1, concurrency: 5}, next: {wait_index: 381.4µs, wait_table_lookup_build: 23.2µs, wait_table_lookup_resp: 305.3µs} | limit embedded(offset:0, count:200) | 5.81 KB | N/A |
| ├─Limit_63(Build) | 4.00 | 7 | cop[tikv] | | time:354.5µs, loops:3, cop_task: {num: 1, max: 329.9µs, proc_keys: 7, tot_proc: 50.5µs, tot_wait: 57.1µs, copr_cache_hit_ratio: 0.00, build_task_duration: 5.21µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:326.7µs}}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 7, total_process_keys_size: 812, total_keys: 8, get_snapshot_time: 50.1µs, rocksdb: {key_skipped_count: 7, block: {cache_hit_count: 4}}}, time_detail: {total_process_time: 50.5µs, total_wait_time: 57.1µs, tikv_wall_time: 156.6µs} | offset:0, count:200 | N/A | N/A |
| │ └─IndexRangeScan_61 | 4.00 | 7 | cop[tikv] | table:edges, index:idx_merge_client(to_entity_id, client_id) | tikv_task:{time:0s, loops:1} | range:["entity_heavy_burden" "abc","entity_heavy_burden" "abc"], keep order:false | N/A | N/A |
| └─TableRowIDScan_62(Probe) | 4.00 | 7 | cop[tikv] | table:edges | time:298.9µs, loops:2, cop_task: {num: 1, max: 288.3µs, proc_keys: 7, tot_proc: 138.9µs, tot_wait: 16.6µs, copr_cache_hit_ratio: 0.00, build_task_duration: 11.9µs, max_distsql_concurrency: 1, max_extra_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:284.4µs}}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 7, total_process_keys_size: 956, total_keys: 7, get_snapshot_time: 7.67µs, rocksdb: {block: {cache_hit_count: 12}}}, time_detail: {total_process_time: 138.9µs, total_wait_time: 16.6µs, tikv_wall_time: 194.6µs} | keep order:false | N/A | N/A |
+----------------------------------------+---------+---------+-----------+-------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------+
15 rows in set (0.00 sec)
Created
January 25, 2026 02:22
-
-
Save mzhang77/dc3bef609ae64af1c2ff75fb46503e2f to your computer and use it in GitHub Desktop.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment