EXPLAIN ANALYZE
SELECT
SUM(remain_amt_no_tax) AS remain_amt_no_tax,
SUM(remain_amt) AS remain_amt ,
SUM(remain_qty) AS remain_qty
FROM tbname
WHERE shipper_code = ‘ssi’
AND product_code = ‘2070’
AND location_code = ‘W0A3’
AND reservoir_area_code = ‘TS01’
AND remain_qty > 0
AND in_or_out = 0 ;
多列索引，表行数6亿：
shipper_code
location_code
reservoir_area_code
product_code
慢sql里截取，有一部分不正常，执行时间30秒+：
id task estRows operator info memory disk
HashAgg_7 root 1 funcs:sum(dbname.tbname.remain_amt_no_tax)->Column#47, funcs:sum(dbname.tbname.remain_amt)->Column#48, funcs:sum(dbname.tbname.remain_qty)->Column#49 1 time:38.3s, loops:2, partial_worker:{wall_time:38.337114128s, concurrency:5, task_num:2, tot_wait:3m11.685218878s, tot_exec:45.455µs, tot_time:3m11.685271105s, max:38.337062683s, p95:38.337062683s}, final_worker:{wall_time:38.33842776s, concurrency:5, task_num:2, tot_wait:3m11.685428908s, tot_exec:24.56µs, tot_time:3m11.685455922s, max:38.337104935s, p95:38.337104935s} 77.7 KB N/A
└─UnionScan_9 root 73.17 eq(dbname.tbname.in_or_out, 0), eq(dbname.tbname.location_code, "W0A3"), eq(dbname.tbname.product_code, "620"), eq(dbname.tbname.reservoir_area_code, "TS01"), eq(dbname.tbname.shipper_code, "ssi"), eq(dbname.tbname.shipper_code, "ssi"), gt(dbname.tbname.remain_qty, 0) 62 time:38.3s, loops:3 N/A N/A
└─IndexLookUp_16 root 73.17 63 time:38.3s, loops:3, index_task: {total_time: 32.7s, fetch_handle: 132.2ms, build: 22.9µs, wait: 32.6s}, table_task: {total_time: 3m5.5s, num: 13, concurrency: 5} 3.07 MB N/A
├─IndexRangeScan_13 cop[tikv] 668.01 table:tbname, index:tbname_key_index(shipper_code, location_code, reservoir_area_code, product_code), range:["ssi" "W0A3" "TS01" "620","ssi" "W0A3" "TS01" "620"], keep order:false 186753 time:123.2ms, loops:185, cop_task: {num: 1, max: 122.4ms, proc_keys: 186753, tot_proc: 112ms, rpc_num: 1, rpc_time: 122.4ms, copr_cache_hit_ratio: 0.00}, tikv_task:{time:108ms, loops:187}, scan_detail: {total_process_keys: 186753, total_keys: 225612, rocksdb: {delete_skipped_count: 5, key_skipped_count: 225616, block: {cache_hit_count: 146, read_count: 30, read_byte: 620.4 KB}}} N/A N/A
└─Selection_15 cop[tikv] 73.17 eq(dbname.tbname.in_or_out, 0), gt(dbname.tbname.remain_qty, 0) 63 time:3m4.7s, loops:14, cop_task: {num: 2733, max: 9.88s, min: 296.9µs, avg: 946.5ms, p95: 5s, max_proc_keys: 616, p95_proc_keys: 133, tot_proc: 8m49.9s, tot_wait: 34m14.1s, rpc_num: 2733, rpc_time: 43m6.8s, copr_cache_hit_ratio: 0.45}, tikv_task:{proc max:5.46s, min:0s, p80:143ms, p95:722ms, iters:5466, tasks:2733}, scan_detail: {total_process_keys: 95076, total_keys: 106973, rocksdb: {delete_skipped_count: 546, key_skipped_count: 21556, block: {cache_hit_count: 1221863, read_count: 45710, read_byte: 509.8 MB}}} N/A N/A
└─TableRowIDScan_14 cop[tikv] 668.01 table:tbname, keep order:false 186753 tikv_task:{proc max:5.46s, min:0s, p80:143ms, p95:722ms, iters:5466, tasks:2733} N/A N/A
手动在mysql 客户端explain ，实际实行不超过3秒
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
±---------------------------------±--------±----------±-------------------------------------------------------------------------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_18 | 1.00 | root | | funcs:sum(Column#54)->Column#47, funcs:sum(Column#55)->Column#48, funcs:sum(Column#56)->Column#49 |
| └─IndexLookUp_19 | 1.00 | root | | |
| ├─IndexRangeScan_15(Build) | 668.05 | cop[tikv] | table:tbname, index:tbname_key_index(shipper_code, location_code, reservoir_area_code, product_code) | range:[“ssi” “W0A3” “TS01” “620”,“ssi” “W0A3” “TS01” “620”], keep order:false |
| └─HashAgg_7(Probe) | 1.00 | cop[tikv] | | funcs:sum(dbname.tbname.remain_amt_no_tax)->Column#54, funcs:sum(dbname.tbname.remain_amt)->Column#55, funcs:sum(dbname.tbname.remain_qty)->Column#56 |
| └─Selection_17 | 73.18 | cop[tikv] | | eq(dbname.tbname.in_or_out, 0), gt(dbname.tbname.remain_qty, 0) |
| └─TableRowIDScan_16 | 668.05 | cop[tikv] | table:tbname | keep order:false |
±---------------------------------±--------±----------±-------------------------------------------------------------------------------------------------------------------------------------------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
截取dashboard的，看起来没有unionscan，和上面一样，但是去掉(build和probe)
id task estRows operator info actRows execution info memory disk
HashAgg_18 root 1 funcs:sum(Column#54)->Column#47, funcs:sum(Column#55)->Column#48, funcs:sum(Column#56)->Column#49 1 time:13.3s, loops:2, partial_worker:{wall_time:13.316232144s, concurrency:5, task_num:1, tot_wait:1m6.580827478s, tot_exec:7.149µs, tot_time:1m6.580840483s, max:13.31618473s, p95:13.31618473s}, final_worker:{wall_time:13.316322338s, concurrency:5, task_num:1, tot_wait:1m6.58101627s, tot_exec:43.153µs, tot_time:1m6.581062035s, max:13.316225622s, p95:13.316225622s} 45.9 KB N/A
└─IndexLookUp_19 root 1 3 time:13.3s, loops:2, index_task: {total_time: 963.9ms, fetch_handle: 109.6ms, build: 15.1µs, wait: 854.3ms}, table_task: {total_time: 50.5s, num: 8, concurrency: 5} 1.78 MB N/A
├─IndexRangeScan_15 cop[tikv] 668.17 table:tbname, index:tbname_key_index(shipper_code, location_code, reservoir_area_code, product_code), range:["ssi" "W0A3" "TS01" "2070","ssi" "W0A3" "TS01" "2070"], keep order:false 77491 time:101.4ms, loops:78, cop_task: {num: 1, max: 100.9ms, proc_keys: 77491, tot_proc: 97ms, tot_wait: 1ms, rpc_num: 1, rpc_time: 100.9ms, copr_cache_hit_ratio: 0.00}, tikv_task:{time:95ms, loops:80}, scan_detail: {total_process_keys: 77491, total_keys: 82224, rocksdb: {delete_skipped_count: 4471, key_skipped_count: 86694, block: {cache_hit_count: 4, read_count: 74, read_byte: 1.68 MB}}} N/A N/A
└─HashAgg_7 cop[tikv] 1 funcs:sum(dbname.tbname.remain_amt_no_tax)->Column#54, funcs:sum(dbname.tbname.remain_amt)->Column#55, funcs:sum(dbname.tbname.remain_qty)->Column#56 3 time:49.9s, loops:9, cop_task: {num: 2770, max: 2.23s, min: 337.6µs, avg: 252.4ms, p95: 1.05s, max_proc_keys: 203, p95_proc_keys: 67, tot_proc: 1m59.1s, tot_wait: 9m37.5s, rpc_num: 5478, rpc_time: 23m9.4s, copr_cache_hit_ratio: 0.14}, tikv_task:{proc max:638ms, min:0s, p80:68ms, p95:159ms, iters:2770, tasks:2770}, scan_detail: {total_process_keys: 65415, total_keys: 73350, rocksdb: {delete_skipped_count: 261, key_skipped_count: 11084, block: {cache_hit_count: 803106, read_count: 51595, read_byte: 576.6 MB}}} N/A N/A
└─Selection_17 cop[tikv] 73.23 eq(dbname.tbname.in_or_out, 0), gt(dbname.tbname.remain_qty, 0) 7 tikv_task:{proc max:638ms, min:0s, p80:68ms, p95:159ms, iters:2770, tasks:2770} N/A N/A
└─TableRowIDScan_16 cop[tikv] 668.17 table:tbname, keep order:false 77491 tikv_task:{proc max:638ms, min:0s, p80:68ms, p95:159ms, iters:2770, tasks:2770} N/A N/A
帮忙看下，感谢