根据Digest在cluster_slow_query中查询出来如下：
Master|root@172.16.5.174|INFORMATION_SCHEMA>SELECT * FROM cluster_slow_query WHERE Digest='daef51ee65908052cd54bf285ae71104e174e9afed92faec80f83a9ee3ebb7c5' LIMIT 1\G
*************************** 1. row ***************************
INSTANCE: 172.16.5.174:10080
Time: 2024-11-19 14:00:44.242619
Txn_start_ts: 454032370292228248
User: user_oms
Host: 172.16.5.204
Conn_ID: 345597844
Session_alias:
Exec_retry_count: 0
Exec_retry_time: 0
Query_time: 2.758635645
Parse_time: 0
Compile_time: 0.002668691
Rewrite_time: 0.000296643
Preproc_subqueries: 0
Preproc_subqueries_time: 0
Optimize_time: 0.001989683
Wait_TS: 0.000011555
Prewrite_time: 0
Wait_prewrite_binlog_time: 0
Commit_time: 0
Get_commit_ts_time: 0
Commit_backoff_time: 0
Backoff_types:
Resolve_lock_time: 0
Local_latch_wait_time: 0
Write_keys: 0
Write_size: 0
Prewrite_region: 0
Txn_retry: 0
Cop_time: 2.771301849
Process_time: 0.025745589
Wait_time: 0.098232068
Backoff_time: 0
LockKeys_time: 0
Request_count: 9
Total_keys: 3521
Process_keys: 3401
Rocksdb_delete_skipped_count: 0
Rocksdb_key_skipped_count: 3098
Rocksdb_block_cache_hit_count: 4617
Rocksdb_block_read_count: 0
Rocksdb_block_read_byte: 0
DB: oms_oms
Index_names: [bas_label_relation:idx_realation_search,bas_label_sub_relation:idx_labelId_pid]
Is_internal: 0
Digest: daef51ee65908052cd54bf285ae71104e174e9afed92faec80f83a9ee3ebb7c5
Stats: bas_label_relation:454028372491370564[7559;868],bas_label_sub_relation:454011929482756700[13201;888]
Cop_proc_avg: 0.002860621
Cop_proc_p90: 0.013937197
Cop_proc_max: 0.013937197
Cop_proc_addr: 172.16.5.224:20160
Cop_wait_avg: 0.010914674
Cop_wait_p90: 0.06056126
Cop_wait_max: 0.06056126
Cop_wait_addr: 172.16.5.1:20160
Mem_max: 1461766
Disk_max: 0
KV_total: 2.771379674
PD_total: 0.000004916
Backoff_total: 0
Write_sql_response_total: 0.000044764
Result_rows: 54
Warnings: [{"Level":"Note","Message":"[blr,idx_realation_search] remain after pruning paths for blr given Prop{SortItems: [{oms_oms.bas_label_relation.id asc}], TaskTp: rootTask}","IsExtra":true},{"Level":"Note","Message":"[idx_pid,idx_labelId_pid] remain after pruning paths for bas_label_sub_relation given Prop{SortItems: [{oms_oms.bas_label_sub_relation.pid asc}], TaskTp: rootTask}","IsExtra":true},{"Level":"Note","Message":"[blr,idx_realation_search] remain after pruning paths for blr given Prop{SortItems: [], TaskTp: rootTask}","IsExtra":true},{"Level":"Note","Message":"[idx_labelId_pid] remain after pruning paths for bas_label_sub_relation given Prop{SortItems: [], TaskTp: copSingleReadTask}","IsExtra":true},{"Level":"Note","Message":"[idx_labelId_pid] remain after pruning paths for bas_label_sub_relation given Prop{SortItems: [], TaskTp: rootTask}","IsExtra":true}]
Backoff_Detail:
Prepared: 1
Succ: 1
IsExplicitTxn: 0
IsWriteCacheTable: 0
Plan_from_cache: 0
Plan_from_binding: 0
Has_more_results: 0
Resource_group: default
Request_unit_read: 16.365423180664063
Request_unit_write: 0
Time_queued_by_rc: 0
Plan: id task estRows operator info actRows execution info memory disk
Projection_10 root 320.76 oms_oms.bas_label_relation.sourceid 54 time:2.76s, loops:2, Concurrency:OFF 1.88 KB N/A
└─Selection_11 root 320.76 or(eq(oms_oms.bas_label_relation.total, Column#15), eq(oms_oms.bas_label_relation.total, 0)) 54 time:2.76s, loops:2 24.7 KB N/A
└─HashJoin_14 root 400.95 left outer join, equal:[eq(oms_oms.bas_label_relation.id, oms_oms.bas_label_sub_relation.pid)] 551 time:2.76s, loops:7, build_hash_table:{total:19.3ms, fetch:19.1ms, build:153µs}, probe:{concurrency:10, total:27.6s, max:2.76s, probe:784.8µs, fetch:27.5s} 97.2 KB 0 Bytes
├─IndexLookUp_32(Build) root 400.95 551 time:19.1ms, loops:2, index_task: {total_time: 2.5ms, fetch_handle: 2.5ms, build: 612ns, wait: 1.73µs}, table_task: {total_time: 16.4ms, num: 1, concurrency: 8}, next: {wait_index: 2.92ms, wait_table_lookup_build: 0s, wait_table_lookup_resp: 16.1ms} 86.5 KB N/A
│ ├─Selection_30(Build) cop[tikv] 408.14 or(eq(oms_oms.bas_label_relation.factory, ""), eq(oms_oms.bas_label_relation.factory, "迅族")), or(eq(oms_oms.bas_label_relation.project, ""), eq(oms_oms.bas_label_relation.project, "迅族")) 556 time:2.46ms, loops:3, cop_task: {num: 3, max: 1.09ms, min: 423.6µs, avg: 795.2µs, p95: 1.09ms, max_proc_keys: 480, p95_proc_keys: 480, tot_proc: 917.5µs, tot_wait: 132.4µs, rpc_num: 3, rpc_time: 2.33ms, copr_cache_hit_ratio: 0.33, build_task_duration: 14.8µs, max_distsql_concurrency: 1}, tikv_task:{proc max:3ms, min:0s, avg: 1ms, p80:3ms, p95:3ms, iters:12, tasks:3}, scan_detail: {total_process_keys: 704, total_process_keys_size: 68816, total_keys: 712, get_snapshot_time: 34.1µs, rocksdb: {key_skipped_count: 710, block: {cache_hit_count: 27}}} N/A N/A
│ │ └─IndexRangeScan_28 cop[tikv] 1225.98 table:blr, index:idx_realation_search(type, total, project, factory, sourceId), range:[7,7], keep order:false 1221 tikv_task:{proc max:0s, min:0s, avg: 0s, p80:0s, p95:0s, iters:12, tasks:3} N/A N/A
│ └─Selection_31(Probe) cop[tikv] 400.95 or(eq(oms_oms.bas_label_relation.firmwarecode, ""), eq(oms_oms.bas_label_relation.firmwarecode, "msm8953_64-user 9 PKQ1.181105.001 18632.20230315.174956 test-keys_2023-03-15 17:49:47")) 551 time:16.1ms, loops:2, cop_task: {num: 1, max: 15.9ms, proc_keys: 556, tot_proc: 13.9ms, tot_wait: 44.7µs, rpc_num: 1, rpc_time: 15.9ms, copr_cache_hit_ratio: 0.00, build_task_duration: 39.2µs, max_distsql_concurrency: 1}, tikv_task:{time:14ms, loops:5}, scan_detail: {total_process_keys: 556, total_process_keys_size: 43366, total_keys: 660, get_snapshot_time: 14.3µs, rocksdb: {key_skipped_count: 247, block: {cache_hit_count: 4483}}} N/A N/A
│ └─TableRowIDScan_29 cop[tikv] 408.14 table:blr, keep order:false 556 tikv_task:{time:14ms, loops:5} N/A N/A
└─HashAgg_39(Probe) root 1560.31 group by:oms_oms.bas_label_sub_relation.pid, funcs:count(Column#18)->Column#15, funcs:firstrow(oms_oms.bas_label_sub_relation.pid)->oms_oms.bas_label_sub_relation.pid 1825 time:2.75s, loops:5, partial_worker:{wall_time:2.753839842s, concurrency:4, task_num:5, tot_wait:11.00941444s, tot_exec:3.459913ms, tot_time:11.0141704s, max:2.753811874s, p95:2.753811874s}, final_worker:{wall_time:2.754946882s, concurrency:4, task_num:16, tot_wait:11.013548657s, tot_exec:5.793496ms, tot_time:11.019351054s, max:2.754896822s, p95:2.754896822s} 1.19 MB N/A
└─IndexReader_40 root 1560.31 index:HashAgg_33 3401 time:2.75s, loops:6, cop_task: {num: 5, max: 2.65s, min: 597.8µs, avg: 550.7ms, p95: 2.65s, max_proc_keys: 1024, p95_proc_keys: 1024, tot_proc: 10.9ms, tot_wait: 98.1ms, rpc_num: 5, rpc_time: 2.75s, copr_cache_hit_ratio: 0.40, build_task_duration: 14.4µs, max_distsql_concurrency: 2} 32.5 KB N/A
└─HashAgg_33 cop[tikv] 1560.31 group by:oms_oms.bas_label_sub_relation.pid, funcs:count(oms_oms.bas_label_sub_relation.pid)->Column#18 3401 tikv_task:{proc max:2.63s, min:0s, avg: 530.2ms, p80:2.63s, p95:2.63s, iters:5, tasks:5}, scan_detail: {total_process_keys: 2141, total_process_keys_size: 117755, total_keys: 2149, get_snapshot_time: 58.9µs, rocksdb: {key_skipped_count: 2141, block: {cache_hit_count: 107}}} N/A N/A
└─IndexRangeScan_38 cop[tikv] 4674.63 table:bas_label_sub_relation, index:idx_labelId_pid(labelId, pid), range:[2,2], [7,7], [11,11], [13,13], [17,17], [19,19], [23,23], [27,27], [32,32], [34,34], [37,37], [41,41], [48,48], [50,50], [53,53], [56,56], [66,66], [69,69], [73,73], [75,75], [84,84], [87,87], [89,89], [93,93], [95,95], [99,99], [102,102], [105,105], [108,108], [118,118], [123,123], [125,125], [129,129], [132,132], [133,133], [150,150], [153,153], [1045,1045], [1049,1049], [1285,1285], [1377,1377], [991460,991460], [1291455,1291455], keep order:false 3744 tikv_task:{proc max:2.63s, min:0s, avg: 529.4ms, p80:2.63s, p95:2.63s, iters:5, tasks:5} N/A N/A
Plan_digest: f8481d979fbace312d6937e1e5be1c05ea2595e2029f5e8b4b6cd6aa2037ffc5
Binary_plan: /CZkCvcmCg1Qcm9qZWN0aW9uXzEwEvElCgxTZWwREZgxEsAkCgtIYXNoSm9pbl8xNBLNEQoOSW5kZXhMb29rVXBfMzISxwguNAAYMzAShAIKEQUk8EZSYW5nZVNjYW5fMjgh4BmnL5Jm/0Ap8l68Xucnk0AwxQk4AkACSlQKUgoKbWFwZ29vX29tcxIDYmxyGj8KFGlkeF9yZWFsYQWdVHNlYXJjaBIEdHlwZRIFdG90YWwSB3AJvVQSB2ZhY3RvcnkSCHNvdXJjZUlkUh1yAX3QOls3LDddLCBrZWVwIG9yZGVyOmZhbHNlakt0aWt2X3Rhc2s6e3Byb2MgbWF4OjBzLCBtaW4FCCxhdmc6IDBzLCBwODAFEQhwOTUFCCRpdGVyczoxMiwgAT4UczozfXD/EQEEAXgRCmT/ARoBASEq9wuEOCYAQSmNYhDeOYJ5QDCsBAH0IFLOAW9yKGVxKBn3OC5iYXNfbGFiZWxfcmVsYQH0AC4N1hgsICIiKSwgqi8AKOi/heaXjyIpKSwgjmgALUeeaAAZLxVobFoUdGltZToyLjQ2bXMsIGxvb3BzOjNijgJjb3ApZiAge251bTogMywlahAgMS4wOQEqNG1pbjogNDIzLjbCtXMsKXYQNzk1LjIFDyF0GSsIYXhfIasoX2tleXM6IDQ4MCwBIUIUAAh0b3QFFBg6IDkxNy41BUkBFCh3YWl0OiAxMzIuNAUUDHJwY18RlAEMBcIQIDIuMzMBmTxjb3ByX2NhY2hlX2hpdF9yIZIwOiAwLjMzLCBidWlsZAXXCF9kdQUaGG46IDE0LjgFWAGoZGRpc3RzcWxfY29uY3VycmVuY3k6IDF9aoUCTngCBXEhCkFwRXkAMQGDQXoFGyEHBQlGfAIILCBzYWAgZGV0YWlsOiB7ZRkF/whlc3MtKgw3MDQsIQIAYTYZACxfc2l6ZTogNjg4MTYRIClbADdB3ixnZXRfc25hcHNob3QtHAwzNC4xBeOocm9ja3NkYjoge2tleV9za2lwcGVkX2NvdW50OiA3MTAsIGJsb2NrOiB7YzVCDR4MMjd9fVoyAwgSyAYySgQ8MRKJAQoRVGFibGVSb3dJRIlKJDkhlINgfW0E60A+VgMMShMKEUJKBARSEEL8AwAePYQl9QQxNCFvSbUANVqdAFgaAQIh4+WU2Hqd60Apnf3Jm0YPeUAwp2nPAL+WzwMoaXJtd2FyZWNvZGWebAM6NADwPG1zbTg5NTNfNjQtdXNlciA5IFBLUTEuMTgxMTA1LjAwMSAxODYzMi4yMDIzMDMxNS4xNzQ5NTYgdGVzdC0h3VRfMjAyMy0wMy0xNSAxNzo0OTo0NyIpccAIMTYuRXopCwwyYsgBPsADBDEsQeAQOiAxNS5lwH2RBDU1SUdFfmFpCSJ5eww0NC43ST9xegQxLH16EVMAY1p6AwAwQVdOegMEMzmNL256AwTWAXb2AY5JAxHiVkkDDDQzMzYpAgRhbG1JDDY2MCxOSQMMMTQuMykKbkkDCDI0N2pJAww0NDgzYksDwX0YXotUyYqAxDauAggBQAGtrAQxOTbsARjkAWluZGV4hdeVSWUfDCAyLjVhISxmZXRjaF9oYW5kbGUVFSWSIDogNjEybnMsIK1kBC43Ad8AfeEjCGJsZU5TAAgxNi5ldaWBCDEsIDIpBSg4fSwgbmV4dDogewFRAF8FmQFyBDkyAYgFFAB0BVoYbG9va3VwXw2FoT9GHQAQcmVzcDoBcRwxbXN9cIC0Bf2lEBK9EAoKDuAIIEFnZ18zORLlCxrfCCxSZWFkZXJfNDAS+AgdIgwzErUFQt0IWDM4IdB1ZrVYQxBBKZ3Z1XqiQrJAMKAd4ekMSkcKRTKTBAAW+ecIc3Vi9esYGh8KD2lkeBYBCBxJZF9waWQSBxIOCChJZBIDcGlkUs0DchbRCBQyLDJdLCAa2AgUWzExLDExARAQMTMsMTMFCQg3LDEO8wgUWzE5LDE5ARIMMjMsMgUbDDI3LDIFGwwzMiwzBUYQMzQsMzQBJAwzNywzBRsMNDEsNAVREDQ4LDQ4ARsQNTAsNTAFCQgzLDUFSBA1Niw1NgESDDY2LDYJCQg5LDYFbAw3Myw3BSQQNzUsNzUBJAw4NCw4BWMMODcsOAVjDDg5LDgFLQw5Myw5BS0MOTUsOQUtDDk5LDkFGxQxMDIsMTAFpBQxMDUsMTAFHxQxMDgsMTAFlhQxMTgsMTEJCwwyMywxCeIUMTI1LDEyCSwQMjksMTIJTQwzMiwxCfEEMTMhKAV1EDE1MCwxCdoQMTUzLDEJ3AgxMDQBbwA0CUQMMDQ5LAEFAckIMTI4AV0AOAkaDDM3NywBBQEaGDk5MTQ2MCwJBwERFDEyOTE0NQEuBQhOgQoAWJ2PMHByb2MgbWF4OjIuNjM+hAoMNTI5LmUrDg8IDSEIcDk1EQsAaRKPCgA1YWsIc2tzYr8GUCGkqeuQfh4RQSmBbTxqQmGYQDDJGkGiMFJtZ3JvdXAgYnk6bWEehAsqjQoupgIsLnBpZCwgZnVuY3M65c9WwAo+MwA0KS0+Q29sdW1uIzE4apNSAgkN2DIECQw1MzAuZfCa+QCOwgUMMjE0MR7sCCF2HiUJGgwJEDExNzc1IUwOXwytxAwyMTQ5UsUFDDU4LjmKxQUFcgBiXg8JBDEwZhAJICEqmflMlPfDQDK0ARABQAFSEKkSlbkIM1oTpaoMMi43NSJ/Cwg2YohCfwsANe2/CDIuNgEpIXAMIDU5Nxq6ChJ7ChA1NTAuN6FnQWUVKTZ8CwwxMDI0Qn0LCRXh7yFaDDogMTA+AQgEOTihhOn0pfkANQkMBbwAIA29Xv8HADRe/wcEMTQa0QsBpVJ5CxQyfXD0gwK99w7NCRjgb4b9RIrGPV0EoQ4lXQSvAe4SA1XqXfYANXE1HGZpcnN0cm93ojgDkpMDTv0BXDVi1AJwYXJ0aWFsX3dvcmtlcjp7d2FsbClsIWscMzgzOTg0MnM2fAcOKgwIYXNrJZ9pCRY4DSwxMS4wMDk0MTQ0NHNlQTBfZXhlYzozLjQ1OTkxEp0MIfolygEsEDE0MTcwASuJ1xw3NTM4MTE4NwESQVgJgQkSFH0sIGZpbmKoABQ0OTQ2ODhuqAAavwwZqRwxMzU0ODY1Ny6qABg1Ljc5MzQ5Ep8ONqoAFDkzNTEwNQGaEawUNDg5NjgyAXMRrBESEH1wpolMXUkoIXTqboWd9tZAKZ4qEwxMAUABUmRsZWZ0IG91dGVyIGpvaW4OwA8QdWFsOluCxw+hZJL4AQQpXTn6ADZ19ww3YogBEhAJEF9oYXNoFigJADoWGA4O9AklqxLKCQEOADFh6QUuFDoxNTPCtSGNGHByb2JlOnsukgkO0g0AdKEUDDoyNy4Bay0qAQsJLwQ3OB7/DglcdDI3LjVzfXD4iQYhV2K81FQj2EApspehfDgMdEAwNiUiBGJvigMRBXYELCBxVgA1jggRDTQIMCkpTiABDDJw6MUuqBEYIR5HMn45Y0agAAAmUoUBIp0RFmoSAGl9fiEOHjoNBA9DKvoPSE9GRnCAD3j///////////8BGAE=
Prev_stmt:
Query: SELECT `blr`.`sourceId` FROM `bas_label_relation` `blr` LEFT JOIN ( SELECT count(pid) num,`pid` FROM `bas_label_sub_relation` WHERE ( `labelId` in(2,7,11,13,17,19,23,27,32,34,37,41,50,53,56,66,69,87,89,93,95,99,102,105,108,118,123,125,129,132,133,150,153,48,73,75,84,1045,1049,1285,1377,991460,1291455) ) GROUP BY `pid` ) blsr ON `blsr`.`pid`=`blr`.`id` WHERE `blr`.`type` = 7 AND ( blr.total = blsr.num OR blr.total = 0 ) AND ( (blr.project = "" OR blr.project = "？？") ) AND ( (blr.factory = "" OR blr.factory = "？？") ) AND ( (blr.firmwareCode = "" OR blr.firmwareCode = "msm8953_64-user 9 PKQ1.181105.001 18632.20230315.174956 test-keys_2023-03-15 17:49:47") ) ;
1 row in set (0.55 sec)
现在数据库正常的情况下的执行计划：
Master|root@172.16.5.174|oms>explain SELECT `blr`.`sourceId` FROM `bas_label_relation` `blr` LEFT JOIN ( SELECT count(pid) num,`pid` FROM `bas_label_sub_relation` WHERE ( `labelId` in(7,11,13,17,19,23,27,30,37,41,50,53,56,62,66, tal = blsr.num OR blr.total = 0 ) AND ( (blr.project = "" OR blr.project = "？？？") ) AND ( (blr.factory = "" OR blr.factory = "？？？") ) AND ( (blr.firmwareCode = "" OR blr.firmwareCode = "MJ_M9_P3_202005111827_2020-05-11
+-------------------------------------+---------+-----------+--------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------------+---------+-----------+--------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_10 | 792.89 | root | | oms.bas_label_relation.sourceid |
| └─Selection_11 | 792.89 | root | | or(eq(oms.bas_label_relation.total, Column#15), eq(oms.bas_label_relation.total, 0)) |
| └─HashJoin_14 | 991.11 | root | | left outer join, equal:[eq(oms.bas_label_relation.id, oms.bas_label_sub_relation.pid)] |
| ├─IndexLookUp_32(Build) | 991.11 | root | | |
| │ ├─Selection_30(Build) | 1008.87 | cop[tikv] | | or(eq(oms.bas_label_relation.factory, ""), eq(oms.bas_label_relation.factory, "智启程记录仪")), or(eq(oms.bas_label_relation.project, ""), eq(oms.bas_label_relation.project, "智启程")) |
| │ │ └─IndexRangeScan_28 | 2244.74 | cop[tikv] | table:blr, index:idx_realation_search(type, total, project, factory, sourceId) | range:[1,1], keep order:false |
| │ └─Selection_31(Probe) | 991.11 | cop[tikv] | | or(eq(oms.bas_label_relation.firmwarecode, ""), eq(oms.bas_label_relation.firmwarecode, "MJ_M9_P3_202005111827_2020-05-11 18:29:09")) |
| │ └─TableRowIDScan_29 | 1008.87 | cop[tikv] | table:blr | keep order:false |
| └─HashAgg_39(Probe) | 1964.71 | root | | group by:oms.bas_label_sub_relation.pid, funcs:count(Column#18)->Column#15, funcs:firstrow(oms.bas_label_sub_relation.pid)->oms.bas_label_sub_relation.pid |
| └─IndexReader_40 | 1964.71 | root | | index:HashAgg_33 |
| └─HashAgg_33 | 1964.71 | cop[tikv] | | group by:oms.bas_label_sub_relation.pid, funcs:count(oms.bas_label_sub_relation.pid)->Column#18 |
| └─IndexRangeScan_38 | 4708.95 | cop[tikv] | table:bas_label_sub_relation, index:idx_labelId_pid(labelId, pid) | range:[2,2], [7,7], [11,11], [13,13], [17,17], [19,19], [23,23], [27,27], [30,30], [34,34], [37,37], [41,41], [44,44], [47,47], [50,50], [53,53], [56,56], [62,62], [66,66], [69,69], [73,73], [76,76], [86,86], [89,89], [92,92], [96,96], [99,99], [101,101], [104,104], [108,108], [112,112], [119,119], [122,122], [125,125], [129,129], [132,132], [140,140], [141,141], [142,142], [153,153], [1045,1045], [1049,1049], [1063,1063], [1108,1108], [1285,1285], [1431,1431], [1438,1438], [1496,1496], [1500,1500], [721466,721466], keep order:false |
+-------------------------------------+---------+-----------+--------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
12 rows in set (0.01 sec)