【 TiDB 使用环境】生产环境
【 TiDB 版本】5.0.4
表索引结构：
时用到索引
数据量：2亿条
执行计划：
MySQL [rd_yrisk_control_engine]> EXPLAIN ANALYZE /*+ STREAM_AGG(),AGG_TO_COP() */ select CH_DEALER_ID,CH_USER_ID_CARD_ENCRYPT,max(I_AMOUNT) group_month from risk_payee_month where I_GROUP_MONTH>= '201909' AND I_GROUP_MONTH < '201910' group by CH_DEALER_ID,CH_USER_ID_CARD_ENCRYPT ;
+------------------------------------+------------+---------+-----------+-----------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------------+------------+---------+-----------+-----------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| Projection_5 | 1554397.13 | 5886179 | root | | time:13.7s, loops:5754, Concurrency:8 | rd_yrisk_control_engine.risk_payee_month.ch_dealer_id, rd_yrisk_control_engine.risk_payee_month.ch_user_id_card_encrypt, Column#52 | 1.45 MB | N/A |
| └─HashAgg_22 | 1554397.13 | 5886179 | root | | time:13.7s, loops:5754, partial_worker:{wall_time:8.8730768s, concurrency:8, task_num:5749, tot_wait:54.618898289s, tot_exec:13.841985702s, tot_time:1m10.802779125s, max:8.873047233s, p95:8.873047233s}, final_worker:{wall_time:13.731023546s, concurrency:8, task_num:64, tot_wait:1m10.65196393s, tot_exec:37.365916086s, tot_time:1m48.017917807s, max:13.730988384s, p95:13.730988384s} | group by:rd_yrisk_control_engine.risk_payee_month.ch_dealer_id, rd_yrisk_control_engine.risk_payee_month.ch_user_id_card_encrypt, funcs:max(Column#57)->Column#52, funcs:firstrow(rd_yrisk_control_engine.risk_payee_month.ch_dealer_id)->rd_yrisk_control_engine.risk_payee_month.ch_dealer_id, funcs:firstrow(rd_yrisk_control_engine.risk_payee_month.ch_user_id_card_encrypt)->rd_yrisk_control_engine.risk_payee_month.ch_user_id_card_encrypt | 3.32 GB | N/A |
| └─IndexLookUp_23 | 1554397.13 | 5886179 | root | | time:7.37s, loops:5750, index_task: {total_time: 8.05s, fetch_handle: 964.5ms, build: 355.5µs, wait: 7.09s}, table_task: {total_time: 1m7s, num: 149, concurrency: 8} | | 92.6 MB | N/A |
| ├─IndexRangeScan_20(Build) | 7233512.74 | 5886179 | cop[tikv] | table:risk_payee_month, index:idx_group_dealer(I_GROUP_MONTH, CH_DEALER_ID) | time:258.5ms, loops:5755, cop_task: {num: 7, max: 597.5ms, min: 60.1ms, avg: 384.2ms, p95: 597.5ms, max_proc_keys: 1233023, p95_proc_keys: 1233023, tot_proc: 2.55s, rpc_num: 7, rpc_time: 2.69s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:547ms, min:56ms, p80:403ms, p95:547ms, iters:5780, tasks:7}, scan_detail: {total_process_keys: 5886179, total_keys: 5886186, rocksdb: {delete_skipped_count: 0, key_skipped_count: 5886179, block: {cache_hit_count: 4226, read_count: 7, read_byte: 303.6 KB}}} | range:[201909,201909], keep order:false | N/A | N/A |
| └─HashAgg_7(Probe) | 1554397.13 | 5886179 | cop[tikv] | | time:1m1.7s, loops:1764, cop_task: {num: 3042, max: 952.1ms, min: 437.7µs, avg: 47.6ms, p95: 275.2ms, max_proc_keys: 40960, p95_proc_keys: 11951, tot_proc: 2m6.6s, tot_wait: 7.78s, rpc_num: 3042, rpc_time: 2m24.6s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:871ms, min:0s, p80:29ms, p95:257ms, iters:7745, tasks:3042}, scan_detail: {total_process_keys: 5886179, total_keys: 6568056, rocksdb: {delete_skipped_count: 0, key_skipped_count: 2973557, block: {cache_hit_count: 70241163, read_count: 229, read_byte: 1.59 MB}}} | group by:rd_yrisk_control_engine.risk_payee_month.ch_dealer_id, rd_yrisk_control_engine.risk_payee_month.ch_user_id_card_encrypt, funcs:max(rd_yrisk_control_engine.risk_payee_month.i_amount)->Column#57 | N/A | N/A |
| └─TableRowIDScan_21 | 7233512.74 | 5886179 | cop[tikv] | table:risk_payee_month | tikv_task:{proc max:822ms, min:0s, p80:28ms, p95:246ms, iters:7745, tasks:3042}, scan_detail: {total_process_keys: 0, total_keys: 0, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} | keep order:false | N/A | N/A |
+------------------------------------+------------+---------+-----------+-----------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
6 rows in set (13.73 sec)
MySQL [rd_yrisk_control_engine]> EXPLAIN ANALYZE /*+ STREAM_AGG() */ select CH_DEALER_ID,CH_USER_ID_CARD_ENCRYPT,max(I_AMOUNT) group_month from risk_payee_month where I_GROUP_MONTH>= '201909' AND I_GROUP_MONTH < '201910' group by CH_DEALER_ID,CH_USER_ID_CARD_ENCRYPT ;
+------------------------------------+------------+---------+-----------+-----------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------------+------------+---------+-----------+-----------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
| Projection_5 | 1554397.13 | 5886179 | root | | time:12.8s, loops:5754, Concurrency:8 | db.table1.ch_dealer_id, db.table1.ch_user_id_card_encrypt, Column#52 | 1.46 MB | N/A |
| └─HashAgg_22 | 1554397.13 | 5886179 | root | | time:12.8s, loops:5754, partial_worker:{wall_time:8.273685576s, concurrency:8, task_num:5749, tot_wait:52.11571047s, tot_exec:11.462284482s, tot_time:1m5.98873633s, max:8.273609274s, p95:8.273609274s}, final_worker:{wall_time:12.847093339s, concurrency:8, task_num:64, tot_wait:1m5.812118454s, tot_exec:36.527406153s, tot_time:1m42.339561045s, max:12.847032589s, p95:12.847032589s} | group by:db.table1.ch_dealer_id, db.table1.ch_user_id_card_encrypt, funcs:max(Column#57)->Column#52, funcs:firstrow(db.table1.ch_dealer_id)->db.table1.ch_dealer_id, funcs:firstrow(db.table1.ch_user_id_card_encrypt)->db.table1.ch_user_id_card_encrypt | 3.32 GB | N/A |
| └─IndexLookUp_23 | 1554397.13 | 5886179 | root | | time:7.01s, loops:5750, index_task: {total_time: 7.49s, fetch_handle: 374.5ms, build: 371.1µs, wait: 7.11s}, table_task: {total_time: 1m2.4s, num: 149, concurrency: 8} | | 88.6 MB | N/A |
| ├─IndexRangeScan_20(Build) | 7233512.74 | 5886179 | cop[tikv] | table:risk_payee_month, index:idx_group_dealer(I_GROUP_MONTH, CH_DEALER_ID) | time:13.8ms, loops:5754, cop_task: {num: 7, max: 520.6ms, min: 708.1µs, avg: 305.7ms, p95: 520.6ms, max_proc_keys: 1233023, p95_proc_keys: 1233023, tot_proc: 2.04s, tot_wait: 6ms, rpc_num: 7, rpc_time: 2.14s, copr_cache_hit_ratio: 0.14}, tikv_task:{proc max:469ms, min:54ms, p80:412ms, p95:469ms, iters:5780, tasks:7}, scan_detail: {total_process_keys: 4979640, total_keys: 4979646, rocksdb: {delete_skipped_count: 0, key_skipped_count: 4979640, block: {cache_hit_count: 3555, read_count: 2, read_byte: 49.0 KB}}} | range:[201909,201909], keep order:false | N/A | N/A |
| └─HashAgg_7(Probe) | 1554397.13 | 5886179 | cop[tikv] | | time:59.1s, loops:1745, cop_task: {num: 3081, max: 911.5ms, min: 422.1µs, avg: 43.5ms, p95: 250.3ms, max_proc_keys: 40960, p95_proc_keys: 11764, tot_proc: 2m1s, tot_wait: 2.19s, rpc_num: 3081, rpc_time: 2m13.9s, copr_cache_hit_ratio: 0.01}, tikv_task:{proc max:872ms, min:0s, p80:28ms, p95:237ms, iters:7780, tasks:3081}, scan_detail: {total_process_keys: 5880081, total_keys: 6562066, rocksdb: {delete_skipped_count: 0, key_skipped_count: 2973785, block: {cache_hit_count: 70093428, read_count: 124, read_byte: 2.94 MB}}} | group by:db.table1.ch_dealer_id, db.table1.CH_DEALER_ID , funcs:max(db.table1.i_amount)->Column#57 | N/A | N/A |
| └─TableRowIDScan_21 | 7233512.74 | 5886179 | cop[tikv] | table:risk_payee_month | tikv_task:{proc max:824ms, min:0s, p80:27ms, p95:225ms, iters:7780, tasks:3081}, scan_detail: {total_process_keys: 0, total_keys: 0, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} | keep order:false | N/A | N/A |
+------------------------------------+------------+---------+-----------+-----------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+------+
6 rows in set (12.85 sec)