MySQL [test]> explain analyze select lesson_id,group_level_8,sum(col_1),sum(col_2),sum(col_3),sum(col_4),sum(col_5),sum(col_6),sum(col_7),sum(col_8),sum(col_9),sum(col_10),sum(col_11),sum(col_12),sum(col_13),sum(col_14),sum(col_15),sum(col_16),sum(col_17),sum(col_18),sum(col_19),sum(col_20),sum(col_21),sum(col_22),sum(col_23),sum(col_24),sum(col_25),sum(col_26),sum(col_27),sum(col_28),sum(col_29),sum(col_30),sum(col_31),sum(col_32),sum(col_33),sum(col_34),sum(col_35),sum(col_36),sum(col_37),sum(col_38),sum(col_39),sum(col_40) from tbldatasopcal_1000r_100c where index1 in (1,2,3) and index2 in (1,2,3) and index4 in (1,2,3) and index5 in (1,2,3) group by lesson_id,group_level_8 limit 1\G
*************************** 1. row ***************************
id: Projection_8
estRows: 1.00
actRows: 1
task: root
access object:
execution info: time:3.52s, loops:2, Concurrency:OFF
operator info: test.tbldatasopcal_1000r_100c.lesson_id, test.tbldatasopcal_1000r_100c.group_level_8, Column#102, Column#103, Column#104, Column#105, Column#106, Column#107, Column#108, Column#109, Column#110, Column#111, Column#112, Column#113, Column#114, Column#115, Column#116, Column#117, Column#118, Column#119, Column#120, Column#121, Column#122, Column#123, Column#124, Column#125, Column#126, Column#127, Column#128, Column#129, Column#130, Column#131, Column#132, Column#133, Column#134, Column#135, Column#136, Column#137, Column#138, Column#139, Column#140, Column#141
memory: 866.8 KB
disk: N/A
*************************** 2. row ***************************
id: └─Limit_11
estRows: 1.00
actRows: 1
task: root
access object:
execution info: time:3.52s, loops:2
operator info: offset:0, count:1
memory: N/A
disk: N/A
*************************** 3. row ***************************
id: └─HashAgg_33
estRows: 1.00
actRows: 422
task: root
access object:
execution info: time:3.52s, loops:1, partial_worker:{wall_time:3.51754035s, concurrency:5, task_num:67, tot_wait:17.38629974s, tot_exec:192.316962ms, tot_time:17.579897943s, max:3.517496181s, p95:3.517496181s}, final_worker:{wall_time:3.524737952s, concurrency:5, task_num:25, tot_wait:17.575486944s, tot_exec:44.791716ms, tot_time:17.620290142s, max:3.524709495s, p95:3.524709495s}
operator info: group by:test.tbldatasopcal_1000r_100c.group_level_8, test.tbldatasopcal_1000r_100c.lesson_id, funcs:sum(Column#188)->Column#102, funcs:sum(Column#189)->Column#103, funcs:sum(Column#190)->Column#104, funcs:sum(Column#191)->Column#105, funcs:sum(Column#192)->Column#106, funcs:sum(Column#193)->Column#107, funcs:sum(Column#194)->Column#108, funcs:sum(Column#195)->Column#109, funcs:sum(Column#196)->Column#110, funcs:sum(Column#197)->Column#111, funcs:sum(Column#198)->Column#112, funcs:sum(Column#199)->Column#113, funcs:sum(Column#200)->Column#114, funcs:sum(Column#201)->Column#115, funcs:sum(Column#202)->Column#116, funcs:sum(Column#203)->Column#117, funcs:sum(Column#204)->Column#118, funcs:sum(Column#205)->Column#119, funcs:sum(Column#206)->Column#120, funcs:sum(Column#207)->Column#121, funcs:sum(Column#208)->Column#122, funcs:sum(Column#209)->Column#123, funcs:sum(Column#210)->Column#124, funcs:sum(Column#211)->Column#125, funcs:sum(Column#212)->Column#126, funcs:sum(Column#213)->Column#127, funcs:sum(Column#214)->Column#128, funcs:sum(Column#215)->Column#129, funcs:sum(Column#216)->Column#130, funcs:sum(Column#217)->Column#131, funcs:sum(Column#218)->Column#132, funcs:sum(Column#219)->Column#133, funcs:sum(Column#220)->Column#134, funcs:sum(Column#221)->Column#135, funcs:sum(Column#222)->Column#136, funcs:sum(Column#223)->Column#137, funcs:sum(Column#224)->Column#138, funcs:sum(Column#225)->Column#139, funcs:sum(Column#226)->Column#140, funcs:sum(Column#227)->Column#141, funcs:firstrow(test.tbldatasopcal_1000r_100c.lesson_id)->test.tbldatasopcal_1000r_100c.lesson_id, funcs:firstrow(test.tbldatasopcal_1000r_100c.group_level_8)->test.tbldatasopcal_1000r_100c.group_level_8
memory: 36.5 MB
disk: N/A
*************************** 4. row ***************************
id: └─IndexLookUp_34
estRows: 1.00
actRows: 67676
task: root
access object:
execution info: time:3.51s, loops:68, index_task: {total_time: 3.25s, fetch_handle: 219.3ms, build: 165.5µs, wait: 3.03s}, table_task: {total_time: 16.4s, num: 77, concurrency: 5}
operator info:
memory: 21.2 MB
disk: N/A
*************************** 5. row ***************************
id: ├─IndexRangeScan_21(Build)
estRows: 1490744.26
actRows: 1499535
task: cop[tikv]
access object: table:tbldatasopcal_1000r_100c, index:index1_idx(index1)
execution info: time:151.2ms, loops:1468, cop_task: {num: 2, max: 505ms, min: 148ms, avg: 326.5ms, p95: 505ms, max_proc_keys: 1149318, p95_proc_keys: 1149318, tot_proc: 627ms, tot_wait: 1ms, rpc_num: 2, rpc_time: 652.9ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:465ms, min:136ms, p80:465ms, p95:465ms, iters:1474, tasks:2}, scan_detail: {total_process_keys: 1499535, total_process_keys_size: 68978610, total_keys: 1499539, rocksdb: {delete_skipped_count: 0, key_skipped_count: 1499535, block: {cache_hit_count: 942, read_count: 3, read_byte: 161.0 KB}}}
operator info: range:[1,1], [2,2], [3,3], keep order:false
memory: N/A
disk: N/A
*************************** 6. row ***************************
id: └─HashAgg_13(Probe)
estRows: 1.00
actRows: 67676
task: cop[tikv]
access object:
execution info: time:15.9s, loops:178, cop_task: {num: 316, max: 472.1ms, min: 1.99ms, avg: 116.2ms, p95: 272.6ms, max_proc_keys: 19850, p95_proc_keys: 11144, tot_proc: 35.2s, tot_wait: 66ms, rpc_num: 316, rpc_time: 36.7s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:460ms, min:1ms, p80:161ms, p95:261ms, iters:1622, tasks:316}, scan_detail: {total_process_keys: 1499535, total_process_keys_size: 566250903, total_keys: 1883176, rocksdb: {delete_skipped_count: 13191, key_skipped_count: 1011522, block: {cache_hit_count: 12369987, read_count: 2401, read_byte: 24.9 MB}}}
operator info: group by:test.tbldatasopcal_1000r_100c.group_level_8, test.tbldatasopcal_1000r_100c.lesson_id, funcs:sum(test.tbldatasopcal_1000r_100c.col_1)->Column#188, funcs:sum(test.tbldatasopcal_1000r_100c.col_2)->Column#189, funcs:sum(test.tbldatasopcal_1000r_100c.col_3)->Column#190, funcs:sum(test.tbldatasopcal_1000r_100c.col_4)->Column#191, funcs:sum(test.tbldatasopcal_1000r_100c.col_5)->Column#192, funcs:sum(test.tbldatasopcal_1000r_100c.col_6)->Column#193, funcs:sum(test.tbldatasopcal_1000r_100c.col_7)->Column#194, funcs:sum(test.tbldatasopcal_1000r_100c.col_8)->Column#195, funcs:sum(test.tbldatasopcal_1000r_100c.col_9)->Column#196, funcs:sum(test.tbldatasopcal_1000r_100c.col_10)->Column#197, funcs:sum(test.tbldatasopcal_1000r_100c.col_11)->Column#198, funcs:sum(test.tbldatasopcal_1000r_100c.col_12)->Column#199, funcs:sum(test.tbldatasopcal_1000r_100c.col_13)->Column#200, funcs:sum(test.tbldatasopcal_1000r_100c.col_14)->Column#201, funcs:sum(test.tbldatasopcal_1000r_100c.col_15)->Column#202, funcs:sum(test.tbldatasopcal_1000r_100c.col_16)->Column#203, funcs:sum(test.tbldatasopcal_1000r_100c.col_17)->Column#204, funcs:sum(test.tbldatasopcal_1000r_100c.col_18)->Column#205, funcs:sum(test.tbldatasopcal_1000r_100c.col_19)->Column#206, funcs:sum(test.tbldatasopcal_1000r_100c.col_20)->Column#207, funcs:sum(test.tbldatasopcal_1000r_100c.col_21)->Column#208, funcs:sum(test.tbldatasopcal_1000r_100c.col_22)->Column#209, funcs:sum(test.tbldatasopcal_1000r_100c.col_23)->Column#210, funcs:sum(test.tbldatasopcal_1000r_100c.col_24)->Column#211, funcs:sum(test.tbldatasopcal_1000r_100c.col_25)->Column#212, funcs:sum(test.tbldatasopcal_1000r_100c.col_26)->Column#213, funcs:sum(test.tbldatasopcal_1000r_100c.col_27)->Column#214, funcs:sum(test.tbldatasopcal_1000r_100c.col_28)->Column#215, funcs:sum(test.tbldatasopcal_1000r_100c.col_29)->Column#216, funcs:sum(test.tbldatasopcal_1000r_100c.col_30)->Column#217, funcs:sum(test.tbldatasopcal_1000r_100c.col_31)->Column#218, funcs:sum(test.tbldatasopcal_1000r_100c.col_32)->Column#219, funcs:sum(test.tbldatasopcal_1000r_100c.col_33)->Column#220, funcs:sum(test.tbldatasopcal_1000r_100c.col_34)->Column#221, funcs:sum(test.tbldatasopcal_1000r_100c.col_35)->Column#222, funcs:sum(test.tbldatasopcal_1000r_100c.col_36)->Column#223, funcs:sum(test.tbldatasopcal_1000r_100c.col_37)->Column#224, funcs:sum(test.tbldatasopcal_1000r_100c.col_38)->Column#225, funcs:sum(test.tbldatasopcal_1000r_100c.col_39)->Column#226, funcs:sum(test.tbldatasopcal_1000r_100c.col_40)->Column#227
memory: N/A
disk: N/A
*************************** 7. row ***************************
id: └─Selection_23
estRows: 619518.68
actRows: 1387537
task: cop[tikv]
access object:
execution info: tikv_task:{proc max:388ms, min:1ms, p80:135ms, p95:221ms, iters:1622, tasks:316}
operator info: in(test.tbldatasopcal_1000r_100c.index2, 1, 2, 3), in(test.tbldatasopcal_1000r_100c.index4, 1, 2, 3), in(test.tbldatasopcal_1000r_100c.index5, 1, 2, 3)
memory: N/A
disk: N/A
*************************** 8. row ***************************
id: └─TableRowIDScan_22
estRows: 1490744.26
actRows: 1499535
task: cop[tikv]
access object: table:tbldatasopcal_1000r_100c
execution info: tikv_task:{proc max:385ms, min:1ms, p80:134ms, p95:220ms, iters:1622, tasks:316}
operator info: keep order:false
memory: N/A
disk: N/A
8 rows in set (3.53 sec)