Created
January 19, 2026 14:46
-
-
Save mzhang77/776e9bb5ef20bfc52dd501b5e34da897 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| mysql> show create table t\G | |
| *************************** 1. row *************************** | |
| Table: t | |
| Create Table: CREATE TABLE `t` ( | |
| `id` int NOT NULL, | |
| `a` int DEFAULT NULL, | |
| `b` int DEFAULT NULL, | |
| `c` int DEFAULT NULL, | |
| `padding` varchar(100) DEFAULT NULL, | |
| PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */, | |
| KEY `idx_a_c` (`a`,`c`), | |
| KEY `idx_b_c` (`b`,`c`) | |
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin | |
| 1 row in set (0.01 sec) | |
| # currently working | |
| mysql> explain analyze SELECT * FROM t WHERE (a = 1 OR b = 1 ) ORDER BY c LIMIT 5; | |
| +--------------------------------+---------+---------+-----------+------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------+---------+------+ | |
| | id | estRows | actRows | task | access object | execution info | operator info | memory | disk | | |
| +--------------------------------+---------+---------+-----------+------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------+---------+------+ | |
| | IndexMerge_27 | 5.00 | 5 | root | | time:1.26ms, loops:2, RU:2.49, index_task:{fetch_handle:1.557584ms, merge:818.417µs}, table_task:{num:1, concurrency:5, fetch_row:395.333µs, wait_time:845.75µs} | type: union, limit embedded(offset:0, count:5) | 33.8 KB | N/A | | |
| | ├─Limit_25(Build) | 3.34 | 5 | cop[tikv] | | time:774.9µs, loops:1, cop_task: {num: 2, max: 471.5µs, min: 262.8µs, avg: 367.1µs, p95: 471.5µs, max_proc_keys: 5, p95_proc_keys: 5, tot_proc: 81.3µs, tot_wait: 154.2µs, copr_cache_hit_ratio: 0.00, build_task_duration: 11.2µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:2, total_time:720.5µs}}, tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:2, tasks:2}, scan_detail: {total_process_keys: 5, total_process_keys_size: 275, total_keys: 7, get_snapshot_time: 117µs, rocksdb: {key_skipped_count: 5, block: {cache_hit_count: 10}}}, time_detail: {total_process_time: 81.3µs, total_wait_time: 154.2µs, tikv_wall_time: 357.9µs} | offset:0, count:5 | N/A | N/A | | |
| | │ └─IndexRangeScan_22 | 3.34 | 5 | cop[tikv] | table:t, index:idx_a_c(a, c) | tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:2, tasks:2} | range:[1,1], keep order:true | N/A | N/A | | |
| | ├─Limit_26(Build) | 3.33 | 5 | cop[tikv] | | time:772µs, loops:1, cop_task: {num: 2, max: 459µs, min: 286.1µs, avg: 372.6µs, p95: 459µs, max_proc_keys: 5, p95_proc_keys: 5, tot_proc: 90µs, tot_wait: 158.3µs, copr_cache_hit_ratio: 0.00, build_task_duration: 13µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:2, total_time:734µs}}, tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:2, tasks:2}, scan_detail: {total_process_keys: 5, total_process_keys_size: 275, total_keys: 7, get_snapshot_time: 131.5µs, rocksdb: {key_skipped_count: 5, block: {cache_hit_count: 10}}}, time_detail: {total_process_time: 90µs, total_wait_time: 158.3µs, tikv_wall_time: 368.6µs} | offset:0, count:5 | N/A | N/A | | |
| | │ └─IndexRangeScan_23 | 3.33 | 5 | cop[tikv] | table:t, index:idx_b_c(b, c) | tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:2, tasks:2} | range:[1,1], keep order:true | N/A | N/A | | |
| | └─TableRowIDScan_24(Probe) | 5.00 | 5 | cop[tikv] | table:t | time:356.5µs, loops:2, cop_task: {num: 1, max: 344µs, proc_keys: 5, tot_proc: 115.3µs, tot_wait: 13.9µs, copr_cache_hit_ratio: 0.00, build_task_duration: 7.17µs, max_distsql_concurrency: 1, max_extra_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:326.3µs}}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 5, total_process_keys_size: 500, total_keys: 5, get_snapshot_time: 8.63µs, rocksdb: {block: {cache_hit_count: 11}}}, time_detail: {total_process_time: 115.3µs, total_wait_time: 13.9µs, tikv_wall_time: 174.1µs} | keep order:false | N/A | N/A | | |
| +--------------------------------+---------+---------+-----------+------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------+---------+------+ | |
| 6 rows in set (0.01 sec) | |
| # the issue | |
| mysql> explain analyze SELECT /*+ use_index_merge(t, idx_a_c, idx_b_c) */ * FROM t WHERE (a = 1 OR b in (1,2) ) ORDER BY c LIMIT 5; | |
| +----------------------------------+-----------+---------+-----------+------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------+---------+---------+ | |
| | id | estRows | actRows | task | access object | execution info | operator info | memory | disk | | |
| +----------------------------------+-----------+---------+-----------+------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------+---------+---------+ | |
| | TopN_9 | 5.00 | 5 | root | | time:1.53s, loops:2, RU:3912.89 | test.t.c, offset:0, count:5 | 28.9 KB | 0 Bytes | | |
| | └─IndexMerge_17 | 5.00 | 250 | root | | time:1.53s, loops:2, index_task:{fetch_handle:117.108668ms, merge:142.95025ms}, table_task:{num:50, concurrency:5, fetch_row:7.398857625s, wait_time:83.225288ms} | type: union | 19.4 MB | N/A | | |
| | ├─IndexRangeScan_13(Build) | 501814.00 | 499669 | cop[tikv] | table:t, index:idx_a_c(a, c) | time:41.8ms, loops:491, cop_task: {num: 12, max: 18.3ms, min: 506.1µs, avg: 11.9ms, p95: 18.3ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 132ms, tot_wait: 922.5µs, copr_cache_hit_ratio: 0.00, build_task_duration: 10.9µs, max_distsql_concurrency: 2}, rpc_info:{Cop:{num_rpc:12, total_time:143.1ms}}, tikv_task:{proc max:14ms, min:0s, avg: 10.4ms, p80:14ms, p95:14ms, iters:534, tasks:12}, scan_detail: {total_process_keys: 499669, total_process_keys_size: 27481795, total_keys: 499681, get_snapshot_time: 558.8µs, rocksdb: {key_skipped_count: 499669, block: {cache_hit_count: 821}}}, time_detail: {total_process_time: 132ms, total_suspend_time: 172.2µs, total_wait_time: 922.5µs, total_kv_read_wall_time: 125ms, tikv_wall_time: 137.6ms} | range:[1,1], keep order:false | N/A | N/A | | |
| | ├─IndexRangeScan_14(Build) | 501247.06 | 500911 | cop[tikv] | table:t, index:idx_b_c(b, c) | time:34.8ms, loops:492, cop_task: {num: 12, max: 20.5ms, min: 465µs, avg: 13.9ms, p95: 20.5ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 158ms, tot_wait: 930.4µs, copr_cache_hit_ratio: 0.00, build_task_duration: 11.1µs, max_distsql_concurrency: 2}, rpc_info:{Cop:{num_rpc:12, total_time:166ms}}, tikv_task:{proc max:19ms, min:0s, avg: 12.7ms, p80:16ms, p95:19ms, iters:535, tasks:12}, scan_detail: {total_process_keys: 500911, total_process_keys_size: 27550105, total_keys: 500924, get_snapshot_time: 487.1µs, rocksdb: {key_skipped_count: 500911, block: {cache_hit_count: 66, read_count: 762, read_byte: 3.44 MB, read_time: 4.19ms}}}, time_detail: {total_process_time: 158ms, total_suspend_time: 177.7µs, total_wait_time: 930.4µs, total_kv_read_wall_time: 152ms, tikv_wall_time: 160.2ms} | range:[1,1], [2,2], keep order:false | N/A | N/A | | |
| | └─TopN_16(Probe) | 5.00 | 250 | cop[tikv] | | time:7.15s, loops:100, cop_task: {num: 50, max: 213.8ms, min: 38.9ms, avg: 142.6ms, p95: 208.2ms, max_proc_keys: 20480, p95_proc_keys: 20480, tot_proc: 5.33s, tot_wait: 1.5s, copr_cache_hit_ratio: 0.00, build_task_duration: 34.5ms, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:50, total_time:7.13s}}, tikv_task:{proc max:206ms, min:38ms, avg: 136.7ms, p80:192ms, p95:198ms, iters:758, tasks:50}, scan_detail: {total_process_keys: 750419, total_process_keys_size: 76229756, total_keys: 762858, get_snapshot_time: 1.31ms, rocksdb: {delete_skipped_count: 662, key_skipped_count: 25782, block: {cache_hit_count: 920557}}}, time_detail: {total_process_time: 5.33s, total_suspend_time: 1.5s, total_wait_time: 1.5s, total_kv_read_wall_time: 6.82s, tikv_wall_time: 7.02s} | test.t.c, offset:0, count:5 | N/A | N/A | | |
| | └─TableRowIDScan_15 | 751528.27 | 750419 | cop[tikv] | table:t | tikv_task:{proc max:206ms, min:38ms, avg: 136.3ms, p80:192ms, p95:198ms, iters:758, tasks:50} | keep order:false | N/A | N/A | | |
| +----------------------------------+-----------+---------+-----------+------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------+---------+---------+ | |
| 6 rows in set (1.53 sec) | |
| # workaround | |
| mysql> explain analyze SELECT * FROM ( | |
| -> SELECT tmp.*, | |
| -> -- 使用 Window Function 去除因为 UNION ALL 产生的重复行 | |
| -> -- (例如某一行既满足 a=1 又满足 b=1，会被扫出来两次) | |
| -> ROW_NUMBER() OVER (PARTITION BY id ORDER BY c) as rn | |
| -> FROM ( | |
| -> -- 分支 1: 处理 a = 1 | |
| -> -- 利用 idx_a_c，a固定，c有序 | |
| -> (SELECT /*+ USE_INDEX(t idx_a_c) */ * FROM t | |
| -> WHERE a = 1 | |
| -> ORDER BY c LIMIT 5) | |
| -> | |
| -> UNION ALL | |
| -> | |
| -> -- 分支 2: 处理 b in (1,2) | |
| -> (SELECT /*+ USE_INDEX(t idx_b_c) */ * FROM t | |
| -> WHERE b in (1,2) | |
| -> ORDER BY c LIMIT 5) | |
| -> ) AS tmp | |
| -> ) AS final_t | |
| -> WHERE rn = 1 -- 取去重后的第一条 | |
| -> ORDER BY c -- 全局再次排序 (这时数据量已经极少) | |
| -> LIMIT 5; | |
| +------------------------------------------------+-----------+---------+-----------+------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------+---------+---------+ | |
| | id | estRows | actRows | task | access object | execution info | operator info | memory | disk | | |
| +------------------------------------------------+-----------+---------+-----------+------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------+---------+---------+ | |
| | TopN_28 | 5.00 | 5 | root | | time:119.3ms, loops:2, RU:462.65 | Column#14, offset:0, count:5 | 2.52 KB | 0 Bytes | | |
| | └─Selection_33 | 8.00 | 8 | root | | time:119.3ms, loops:3 | eq(Column#17, 1) | 2.48 KB | N/A | | |
| | └─Shuffle_61 | 10.00 | 10 | root | | time:119ms, loops:6, ShuffleConcurrency:5 | execution info: concurrency:5, data sources:[Union_35] | N/A | N/A | | |
| | └─Window_34 | 10.00 | 10 | root | | time:593.7ms, loops:9 | row_number()->Column#17 over(partition by Column#11 order by Column#14 rows between current row and current row) | N/A | N/A | | |
| | └─Sort_60 | 10.00 | 10 | root | | time:592.5ms, loops:9 | Column#11, Column#14 | 2.17 KB | 0 Bytes | | |
| | └─ShuffleReceiver_64 | 10.00 | 10 | root | | time:592.3ms, loops:9 | | N/A | N/A | | |
| | └─Union_35 | 10.00 | 10 | root | | time:118.7ms, loops:3 | | N/A | N/A | | |
| | ├─IndexLookUp_49 | 5.00 | 5 | root | | time:1.75ms, loops:2, index_task: {total_time: 973.5µs, fetch_handle: 965.2µs, build: 6.21µs, wait: 2.13µs}, table_task: {total_time: 710.7µs, num: 1, concurrency: 5}, next: {wait_index: 1.02ms, wait_table_lookup_build: 45.2µs, wait_table_lookup_resp: 666.8µs} | limit embedded(offset:0, count:5) | 3.88 KB | N/A | | |
| | │ ├─Limit_48(Build) | 5.00 | 5 | cop[tikv] | | time:964µs, loops:1, cop_task: {num: 2, max: 673.1µs, min: 259.2µs, avg: 466.1µs, p95: 673.1µs, max_proc_keys: 5, p95_proc_keys: 5, tot_proc: 150.8µs, tot_wait: 228.6µs, copr_cache_hit_ratio: 0.00, build_task_duration: 17.8µs, max_distsql_concurrency: 1}, rpc_info:{Cop:{num_rpc:2, total_time:921.6µs}}, tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:2, tasks:2}, scan_detail: {total_process_keys: 5, total_process_keys_size: 275, total_keys: 7, get_snapshot_time: 177.1µs, rocksdb: {key_skipped_count: 5, block: {cache_hit_count: 10}}}, time_detail: {total_process_time: 150.8µs, total_wait_time: 228.6µs, tikv_wall_time: 597.1µs} | offset:0, count:5 | N/A | N/A | | |
| | │ │ └─IndexRangeScan_46 | 5.00 | 5 | cop[tikv] | table:t, index:idx_a_c(a, c) | tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:2, tasks:2} | range:[1,1], keep order:true | N/A | N/A | | |
| | │ └─TableRowIDScan_47(Probe) | 5.00 | 5 | cop[tikv] | table:t | time:651.4µs, loops:2, cop_task: {num: 1, max: 602µs, proc_keys: 5, tot_proc: 407.8µs, tot_wait: 15.2µs, copr_cache_hit_ratio: 0.00, build_task_duration: 20.5µs, max_distsql_concurrency: 1, max_extra_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:598.6µs}}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 5, total_process_keys_size: 498, total_keys: 5, get_snapshot_time: 7.71µs, rocksdb: {block: {cache_hit_count: 10}}}, time_detail: {total_process_time: 407.8µs, total_wait_time: 15.2µs, tikv_wall_time: 467.7µs} | keep order:false | N/A | N/A | | |
| | └─TopN_52 | 5.00 | 5 | root | | time:118.6ms, loops:2 | test.t.c, offset:0, count:5 | 2.14 KB | 0 Bytes | | |
| | └─IndexLookUp_59 | 5.00 | 5 | root | | time:118.6ms, loops:2, index_task: {total_time: 118ms, fetch_handle: 118ms, build: 708ns, wait: 2.17µs}, table_task: {total_time: 454.5µs, num: 1, concurrency: 5}, next: {wait_index: 118.1ms, wait_table_lookup_build: 51.4µs, wait_table_lookup_resp: 403.5µs} | | 3.70 KB | N/A | | |
| | ├─TopN_58(Build) | 5.00 | 5 | cop[tikv] | | time:118ms, loops:3, cop_task: {num: 2, max: 118ms, min: 552.6µs, avg: 59.3ms, p95: 118ms, max_proc_keys: 500911, p95_proc_keys: 500911, tot_proc: 117.5ms, tot_wait: 213.3µs, copr_cache_hit_ratio: 0.00, build_task_duration: 10.7µs, max_distsql_concurrency: 2}, rpc_info:{Cop:{num_rpc:2, total_time:118.5ms}}, tikv_task:{proc max:118ms, min:0s, avg: 59ms, p80:118ms, p95:118ms, iters:491, tasks:2}, scan_detail: {total_process_keys: 500911, total_process_keys_size: 27550105, total_keys: 500914, get_snapshot_time: 119µs, rocksdb: {key_skipped_count: 500911, block: {cache_hit_count: 777}}}, time_detail: {total_process_time: 117.5ms, total_suspend_time: 44.9µs, total_wait_time: 213.3µs, total_kv_read_wall_time: 108ms, tikv_wall_time: 118ms} | test.t.c, offset:0, count:5 | N/A | N/A | | |
| | │ └─IndexRangeScan_56 | 501247.06 | 500911 | cop[tikv] | table:t, index:idx_b_c(b, c) | tikv_task:{proc max:108ms, min:0s, avg: 54ms, p80:108ms, p95:108ms, iters:491, tasks:2} | range:[1,1], [2,2], keep order:false | N/A | N/A | | |
| | └─TableRowIDScan_57(Probe) | 5.00 | 5 | cop[tikv] | table:t | time:395.1µs, loops:2, cop_task: {num: 1, max: 381.4µs, proc_keys: 5, tot_proc: 115.3µs, tot_wait: 30.6µs, copr_cache_hit_ratio: 0.00, build_task_duration: 20.6µs, max_distsql_concurrency: 1, max_extra_concurrency: 1}, rpc_info:{Cop:{num_rpc:1, total_time:376.2µs}}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 5, total_process_keys_size: 498, total_keys: 5, get_snapshot_time: 15.8µs, rocksdb: {block: {cache_hit_count: 11}}}, time_detail: {total_process_time: 115.3µs, total_wait_time: 30.6µs, tikv_wall_time: 200.6µs} | keep order:false | N/A | N/A | | |
| +------------------------------------------------+-----------+---------+-----------+------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------+---------+---------+ | |
| 16 rows in set (0.12 sec) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment