【求助】SQL 查询条件的时间间隔增加一个月，TiKV 执行时间却变成10倍

just_coder · 2022 年7 月 8 日 02:30

为提高效率，请提供以下信息，问题描述清晰能够更快得到解决：
【 TiDB 使用环境】

【概述】场景 + 问题概述
查询条件由 v.recdate>=‘2022-01-01’ and v.recdate<=‘2022-07-01’ 变为 v.recdate>=‘2021-12-01’ and v.recdate<=‘2022-07-01’ 后速度大幅度下降

【背景】做过哪些操作

【现象】业务和数据库现象
TiKV 查询某段时间间隔内的数据

【问题】当前遇到的问题

【业务影响】
SQL语句中日期查询条件时间间隔增加了一个月，查询速度慢了10倍
慢查询日志如下：

【TiDB 版本】 V5.4.1

【应用软件及版本】

【附件】相关日志及配置信息

TiUP Cluster Display 信息
TiUP CLuster Edit config 信息

监控（https://metricstool.pingcap.com/)

TiDB-Overview Grafana监控
TiDB Grafana 监控
TiKV Grafana 监控
PD Grafana 监控
对应模块日志（包含问题前后 1 小时日志）

若提问为性能优化、故障排查类问题，请下载脚本运行。终端输出的打印结果，请务必全选并复制粘贴上传。

just_coder · 2022 年7 月 8 日 02:30

id                           	task     	estRows   	operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                	actRows	execution info                                                                                                                                                                                                                                                                                                                                                                  	memory  	disk
Projection_7                 	root     	456.49    	yd_194_result.feature_dev_moment.recdate, yd_194_result.feature_dev_moment.aid, yd_194_result.feature_dev_moment.oid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.osid, yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.pt, Column#66, Column#67, Column#68, Column#69, Column#70, Column#71, Column#72, Column#73, Column#74, Column#75, Column#76, Column#77, Column#78, Column#79, Column#80, Column#81, Column#82, Column#83, Column#84, Column#85, Column#86, Column#87, Column#88, Column#89, Column#90, Column#91, Column#92, Column#98, Column#99, Column#100, Column#101, 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#93, Column#94, Column#95, Column#96, Column#97                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               	136331 	time:2.13s, loops:135, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                          	7.65 MB 	N/A
└─Sort_8                     	root     	456.49    	yd_194_result.feature_dev_moment.payment:desc                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                	136331 	time:2.11s, loops:135                                                                                                                                                                                                                                                                                                                                                           	207.5 MB	0 Bytes
  └─Projection_10            	root     	456.49    	yd_194_result.feature_dev_moment.recdate, yd_194_result.feature_dev_moment.aid, yd_194_result.feature_dev_moment.oid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.osid, yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.pt, Column#66, Column#67, Column#68, Column#69, Column#70, Column#71, Column#72, Column#73, Column#74, Column#75, Column#76, Column#77, Column#78, Column#79, Column#80, Column#81, Column#82, Column#83, Column#84, Column#85, Column#86, Column#87, Column#88, Column#89, Column#90, Column#91, Column#92, 0->Column#98, 0->Column#99, 0->Column#100, 0->Column#101, 0->Column#102, 0->Column#103, 0->Column#104, 0->Column#105, 0->Column#106, 0->Column#107, 0->Column#108, 0->Column#109, 0->Column#110, 0->Column#111, 0->Column#112, 0->Column#113, 0->Column#114, 0->Column#115, Column#93, Column#94, Column#95, Column#96, Column#97, yd_194_result.feature_dev_moment.payment                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               	136331 	time:1.3s, loops:136, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                           	178.4 MB	N/A
    └─HashAgg_29             	root     	456.49    	group by:yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.pt, yd_194_result.feature_dev_moment.recdate, funcs:sum(Column#236)->Column#66, funcs:sum(Column#237)->Column#67, funcs:sum(Column#238)->Column#68, funcs:sum(Column#239)->Column#69, funcs:sum(Column#240)->Column#70, funcs:sum(Column#241)->Column#71, funcs:sum(Column#242)->Column#72, funcs:sum(Column#243)->Column#73, funcs:sum(Column#244)->Column#74, funcs:sum(Column#245)->Column#75, funcs:sum(Column#246)->Column#76, funcs:sum(Column#247)->Column#77, funcs:sum(Column#248)->Column#78, funcs:sum(Column#249)->Column#79, funcs:sum(Column#250)->Column#80, funcs:sum(Column#251)->Column#81, funcs:sum(Column#252)->Column#82, funcs:sum(Column#253)->Column#83, funcs:sum(Column#254)->Column#84, funcs:sum(Column#255)->Column#85, funcs:sum(Column#256)->Column#86, funcs:sum(Column#257)->Column#87, funcs:sum(Column#258)->Column#88, funcs:sum(Column#259)->Column#89, funcs:sum(Column#260)->Column#90, funcs:sum(Column#261)->Column#91, funcs:sum(Column#262)->Column#92, funcs:sum(Column#263)->Column#93, funcs:sum(Column#264)->Column#94, funcs:sum(Column#265)->Column#95, funcs:sum(Column#266)->Column#96, funcs:sum(Column#267)->Column#97, funcs:firstrow(yd_194_result.feature_dev_moment.recdate)->yd_194_result.feature_dev_moment.recdate, funcs:firstrow(Column#269)->yd_194_result.feature_dev_moment.osid, funcs:firstrow(yd_194_result.feature_dev_moment.gameid)->yd_194_result.feature_dev_moment.gameid, funcs:firstrow(yd_194_result.feature_dev_moment.cid)->yd_194_result.feature_dev_moment.cid, funcs:firstrow(Column#272)->yd_194_result.feature_dev_moment.aid, funcs:firstrow(Column#273)->yd_194_result.feature_dev_moment.oid, funcs:firstrow(yd_194_result.feature_dev_moment.pt)->yd_194_result.feature_dev_moment.pt, funcs:firstrow(Column#275)->yd_194_result.feature_dev_moment.payment                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         	136331 	time:1.26s, loops:136, partial_worker:{wall_time:451.107219ms, concurrency:5, task_num:134, tot_wait:261.029605ms, tot_exec:1.802961541s, tot_time:2.189461206s, max:450.915149ms, p95:450.915149ms}, final_worker:{wall_time:1.310306374s, concurrency:5, task_num:25, tot_wait:2.103021226s, tot_exec:4.322647411s, tot_time:6.425723777s, max:1.310116859s, p95:1.310116859s}	503.9 MB	N/A
      └─IndexLookUp_30       	root     	456.49    	                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             	136580 	time:355.2ms, loops:135, index_task: {total_time: 290.7ms, fetch_handle: 236.1ms, build: 365.4µs, wait: 54.2ms}, table_task: {total_time: 1.45s, num: 91, concurrency: 5}                                                                                                                                                                                                      	61.0 MB 	N/A
        ├─Selection_25       	cop[tikv]	2110794.50	not(in(yd_194_result.feature_dev_moment.gameid, "1604385436956830", "1621669402391940", "1632711274294320", "1636099609615800", "1639364435557330", "1639126762567720", "1616571507848110", "1639110127366330", "1640159205240000", "1633765571609960"))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     	1783943	time:7.57ms, loops:1928, cop_task: {num: 4, max: 2.22ms, min: 1.75ms, avg: 1.88ms, p95: 2.22ms, rpc_num: 4, rpc_time: 7.38ms, copr_cache_hit_ratio: 1.00}, tikv_task:{proc max:1.34s, min:515ms, p80:1.34s, p95:1.34s, iters:2148, tasks:4}                                                                                                                                     	N/A     	N/A
        │ └─IndexRangeScan_23	cop[tikv]	2150268.84	table:v, index:PRIMARY(recdate, osid, gameid, cid, aid, oid, pt, moment), range:[2022-01-01,2022-07-01], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    	2181886	tikv_task:{proc max:1.11s, min:419ms, p80:1.11s, p95:1.11s, iters:2148, tasks:4}                                                                                                                                                                                                                                                                                                	N/A     	N/A
        └─HashAgg_12         	cop[tikv]	456.49    	group by:yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.pt, yd_194_result.feature_dev_moment.recdate, funcs:sum(yd_194_result.feature_dev_moment.installcount)->Column#236, funcs:sum(yd_194_result.feature_dev_moment.regdevcount)->Column#237, funcs:sum(yd_194_result.feature_dev_moment.newdevcount)->Column#238, funcs:sum(yd_194_result.feature_dev_moment.newdidrolecount)->Column#239, funcs:sum(yd_194_result.feature_dev_moment.didrolecount)->Column#240, funcs:sum(yd_194_result.feature_dev_moment.reglogincount)->Column#241, funcs:sum(yd_194_result.feature_dev_moment.newlogincount)->Column#242, funcs:sum(yd_194_result.feature_dev_moment.newuserrolecount)->Column#243, funcs:sum(yd_194_result.feature_dev_moment.userrolecount)->Column#244, funcs:sum(yd_194_result.feature_dev_moment.userlive1)->Column#245, funcs:sum(yd_194_result.feature_dev_moment.userlive3)->Column#246, funcs:sum(yd_194_result.feature_dev_moment.userlive4)->Column#247, funcs:sum(yd_194_result.feature_dev_moment.userlive5)->Column#248, funcs:sum(yd_194_result.feature_dev_moment.userlive6)->Column#249, funcs:sum(yd_194_result.feature_dev_moment.userlive7)->Column#250, funcs:sum(yd_194_result.feature_dev_moment.userlive8)->Column#251, funcs:sum(yd_194_result.feature_dev_moment.userlive9)->Column#252, funcs:sum(yd_194_result.feature_dev_moment.userlive10)->Column#253, funcs:sum(yd_194_result.feature_dev_moment.userlive11)->Column#254, funcs:sum(yd_194_result.feature_dev_moment.userlive12)->Column#255, funcs:sum(yd_194_result.feature_dev_moment.userlive13)->Column#256, funcs:sum(yd_194_result.feature_dev_moment.userlive14)->Column#257, funcs:sum(yd_194_result.feature_dev_moment.userlive15)->Column#258, funcs:sum(yd_194_result.feature_dev_moment.userlive16)->Column#259, funcs:sum(yd_194_result.feature_dev_moment.userlive30)->Column#260, funcs:sum(yd_194_result.feature_dev_moment.userlive60)->Column#261, funcs:sum(yd_194_result.feature_dev_moment.userlive90)->Column#262, funcs:sum(yd_194_result.feature_dev_moment.newuserincome)->Column#263, funcs:sum(yd_194_result.feature_dev_moment.newuserpaypeople)->Column#264, funcs:sum(yd_194_result.feature_dev_moment.todayincome)->Column#265, funcs:sum(yd_194_result.feature_dev_moment.todaypaypeople)->Column#266, funcs:sum(yd_194_result.feature_dev_moment.todaypay6people)->Column#267, funcs:firstrow(yd_194_result.feature_dev_moment.osid)->Column#269, funcs:firstrow(yd_194_result.feature_dev_moment.aid)->Column#272, funcs:firstrow(yd_194_result.feature_dev_moment.oid)->Column#273, funcs:firstrow(yd_194_result.feature_dev_moment.payment)->Column#275	136580 	time:318.2ms, loops:193, cop_task: {num: 102, max: 5.09ms, min: 1.43ms, avg: 1.88ms, p95: 2.2ms, rpc_num: 102, rpc_time: 188.1ms, copr_cache_hit_ratio: 0.99}, tikv_task:{proc max:562ms, min:26ms, p80:417ms, p95:530ms, iters:1792, tasks:102}                                                                                                                                	N/A     	N/A
          └─TableRowIDScan_24	cop[tikv]	2110794.50	table:v, keep order:false

just_coder · 2022 年7 月 8 日 02:32

id                           	task     	estRows   	operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                	actRows	execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        	memory  	disk
Projection_7                 	root     	531.57    	yd_194_result.feature_dev_moment.recdate, yd_194_result.feature_dev_moment.aid, yd_194_result.feature_dev_moment.oid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.osid, yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.pt, Column#66, Column#67, Column#68, Column#69, Column#70, Column#71, Column#72, Column#73, Column#74, Column#75, Column#76, Column#77, Column#78, Column#79, Column#80, Column#81, Column#82, Column#83, Column#84, Column#85, Column#86, Column#87, Column#88, Column#89, Column#90, Column#91, Column#92, Column#98, Column#99, Column#100, Column#101, 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#93, Column#94, Column#95, Column#96, Column#97                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               	160649 	time:45.3s, loops:158, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                	7.65 MB 	N/A
└─Sort_8                     	root     	531.57    	yd_194_result.feature_dev_moment.payment:desc                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                	160649 	time:45.2s, loops:158                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 	237.6 MB	296.8 MB
  └─Projection_10            	root     	531.57    	yd_194_result.feature_dev_moment.recdate, yd_194_result.feature_dev_moment.aid, yd_194_result.feature_dev_moment.oid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.osid, yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.pt, Column#66, Column#67, Column#68, Column#69, Column#70, Column#71, Column#72, Column#73, Column#74, Column#75, Column#76, Column#77, Column#78, Column#79, Column#80, Column#81, Column#82, Column#83, Column#84, Column#85, Column#86, Column#87, Column#88, Column#89, Column#90, Column#91, Column#92, 0->Column#98, 0->Column#99, 0->Column#100, 0->Column#101, 0->Column#102, 0->Column#103, 0->Column#104, 0->Column#105, 0->Column#106, 0->Column#107, 0->Column#108, 0->Column#109, 0->Column#110, 0->Column#111, 0->Column#112, 0->Column#113, 0->Column#114, 0->Column#115, Column#93, Column#94, Column#95, Column#96, Column#97, yd_194_result.feature_dev_moment.payment                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               	160649 	time:7.01s, loops:161, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                	209.5 MB	N/A
    └─HashAgg_29             	root     	531.57    	group by:yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.pt, yd_194_result.feature_dev_moment.recdate, funcs:sum(Column#236)->Column#66, funcs:sum(Column#237)->Column#67, funcs:sum(Column#238)->Column#68, funcs:sum(Column#239)->Column#69, funcs:sum(Column#240)->Column#70, funcs:sum(Column#241)->Column#71, funcs:sum(Column#242)->Column#72, funcs:sum(Column#243)->Column#73, funcs:sum(Column#244)->Column#74, funcs:sum(Column#245)->Column#75, funcs:sum(Column#246)->Column#76, funcs:sum(Column#247)->Column#77, funcs:sum(Column#248)->Column#78, funcs:sum(Column#249)->Column#79, funcs:sum(Column#250)->Column#80, funcs:sum(Column#251)->Column#81, funcs:sum(Column#252)->Column#82, funcs:sum(Column#253)->Column#83, funcs:sum(Column#254)->Column#84, funcs:sum(Column#255)->Column#85, funcs:sum(Column#256)->Column#86, funcs:sum(Column#257)->Column#87, funcs:sum(Column#258)->Column#88, funcs:sum(Column#259)->Column#89, funcs:sum(Column#260)->Column#90, funcs:sum(Column#261)->Column#91, funcs:sum(Column#262)->Column#92, funcs:sum(Column#263)->Column#93, funcs:sum(Column#264)->Column#94, funcs:sum(Column#265)->Column#95, funcs:sum(Column#266)->Column#96, funcs:sum(Column#267)->Column#97, funcs:firstrow(yd_194_result.feature_dev_moment.recdate)->yd_194_result.feature_dev_moment.recdate, funcs:firstrow(Column#269)->yd_194_result.feature_dev_moment.osid, funcs:firstrow(yd_194_result.feature_dev_moment.gameid)->yd_194_result.feature_dev_moment.gameid, funcs:firstrow(yd_194_result.feature_dev_moment.cid)->yd_194_result.feature_dev_moment.cid, funcs:firstrow(Column#272)->yd_194_result.feature_dev_moment.aid, funcs:firstrow(Column#273)->yd_194_result.feature_dev_moment.oid, funcs:firstrow(yd_194_result.feature_dev_moment.pt)->yd_194_result.feature_dev_moment.pt, funcs:firstrow(Column#275)->yd_194_result.feature_dev_moment.payment                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         	160649 	time:6.96s, loops:161, partial_worker:{wall_time:6.018231429s, concurrency:5, task_num:158, tot_wait:27.712163989s, tot_exec:2.180316903s, tot_time:30.01636946s, max:6.018077734s, p95:6.018077734s}, final_worker:{wall_time:10.131924536s, concurrency:5, task_num:25, tot_wait:29.989720826s, tot_exec:11.264212713s, tot_time:41.25398566s, max:10.131719169s, p95:10.131719169s}                                                                                                                                                                                	591.0 MB	N/A
      └─IndexLookUp_30       	root     	531.57    	                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             	160926 	time:5.96s, loops:159, index_task: {total_time: 5.45s, fetch_handle: 282.7ms, build: 751.6µs, wait: 5.17s}, table_task: {total_time: 29.3s, num: 107, concurrency: 5}                                                                                                                                                                                                                                                                                                                                                                                                	40.5 MB 	N/A
        ├─Selection_25       	cop[tikv]	2457958.79	not(in(yd_194_result.feature_dev_moment.gameid, "1604385436956830", "1621669402391940", "1632711274294320", "1636099609615800", "1639364435557330", "1639126762567720", "1616571507848110", "1639110127366330", "1640159205240000", "1633765571609960"))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     	2112984	time:10.6ms, loops:2250, cop_task: {num: 4, max: 4.49ms, min: 4ms, avg: 4.18ms, p95: 4.49ms, rpc_num: 4, rpc_time: 16.4ms, copr_cache_hit_ratio: 1.00}, tikv_task:{proc max:1.69s, min:515ms, p80:1.69s, p95:1.69s, iters:2475, tasks:4}                                                                                                                                                                                                                                                                                                                              	N/A     	N/A
        │ └─IndexRangeScan_23	cop[tikv]	2503925.50	table:v, index:PRIMARY(recdate, osid, gameid, cid, aid, oid, pt, moment), range:[2021-12-01,2022-07-01], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    	2516881	tikv_task:{proc max:1.41s, min:419ms, p80:1.41s, p95:1.41s, iters:2475, tasks:4}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      	N/A     	N/A
        └─HashAgg_12         	cop[tikv]	531.57    	group by:yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.pt, yd_194_result.feature_dev_moment.recdate, funcs:sum(yd_194_result.feature_dev_moment.installcount)->Column#236, funcs:sum(yd_194_result.feature_dev_moment.regdevcount)->Column#237, funcs:sum(yd_194_result.feature_dev_moment.newdevcount)->Column#238, funcs:sum(yd_194_result.feature_dev_moment.newdidrolecount)->Column#239, funcs:sum(yd_194_result.feature_dev_moment.didrolecount)->Column#240, funcs:sum(yd_194_result.feature_dev_moment.reglogincount)->Column#241, funcs:sum(yd_194_result.feature_dev_moment.newlogincount)->Column#242, funcs:sum(yd_194_result.feature_dev_moment.newuserrolecount)->Column#243, funcs:sum(yd_194_result.feature_dev_moment.userrolecount)->Column#244, funcs:sum(yd_194_result.feature_dev_moment.userlive1)->Column#245, funcs:sum(yd_194_result.feature_dev_moment.userlive3)->Column#246, funcs:sum(yd_194_result.feature_dev_moment.userlive4)->Column#247, funcs:sum(yd_194_result.feature_dev_moment.userlive5)->Column#248, funcs:sum(yd_194_result.feature_dev_moment.userlive6)->Column#249, funcs:sum(yd_194_result.feature_dev_moment.userlive7)->Column#250, funcs:sum(yd_194_result.feature_dev_moment.userlive8)->Column#251, funcs:sum(yd_194_result.feature_dev_moment.userlive9)->Column#252, funcs:sum(yd_194_result.feature_dev_moment.userlive10)->Column#253, funcs:sum(yd_194_result.feature_dev_moment.userlive11)->Column#254, funcs:sum(yd_194_result.feature_dev_moment.userlive12)->Column#255, funcs:sum(yd_194_result.feature_dev_moment.userlive13)->Column#256, funcs:sum(yd_194_result.feature_dev_moment.userlive14)->Column#257, funcs:sum(yd_194_result.feature_dev_moment.userlive15)->Column#258, funcs:sum(yd_194_result.feature_dev_moment.userlive16)->Column#259, funcs:sum(yd_194_result.feature_dev_moment.userlive30)->Column#260, funcs:sum(yd_194_result.feature_dev_moment.userlive60)->Column#261, funcs:sum(yd_194_result.feature_dev_moment.userlive90)->Column#262, funcs:sum(yd_194_result.feature_dev_moment.newuserincome)->Column#263, funcs:sum(yd_194_result.feature_dev_moment.newuserpaypeople)->Column#264, funcs:sum(yd_194_result.feature_dev_moment.todayincome)->Column#265, funcs:sum(yd_194_result.feature_dev_moment.todaypaypeople)->Column#266, funcs:sum(yd_194_result.feature_dev_moment.todaypay6people)->Column#267, funcs:firstrow(yd_194_result.feature_dev_moment.osid)->Column#269, funcs:firstrow(yd_194_result.feature_dev_moment.aid)->Column#272, funcs:firstrow(yd_194_result.feature_dev_moment.oid)->Column#273, funcs:firstrow(yd_194_result.feature_dev_moment.payment)->Column#275	160926 	time:27.9s, loops:229, cop_task: {num: 122, max: 678.9ms, min: 1.53ms, avg: 236.4ms, p95: 532.1ms, max_proc_keys: 21001, p95_proc_keys: 20897, tot_proc: 27.2s, tot_wait: 44ms, rpc_num: 122, rpc_time: 28.8s, copr_cache_hit_ratio: 0.37}, tikv_task:{proc max:638ms, min:27ms, p80:438ms, p95:524ms, iters:2120, tasks:122}, scan_detail: {total_process_keys: 1341314, total_process_keys_size: 659446998, total_keys: 1738600, rocksdb: {delete_skipped_count: 0, key_skipped_count: 3078006, block: {cache_hit_count: 15282, read_count: 0, read_byte: 0 Bytes}}}	N/A     	N/A
          └─TableRowIDScan_24	cop[tikv]	2457958.79	table:v, keep order:false

just_coder · 2022 年7 月 8 日 02:36

执行计划对比后发现查询速度大幅度下降的查询中，【tot_wait】、【rpc_time】时间都很长，【copr_cache_hit_ratio】命中率也很低，请问下一步应该如何排查问题呢？

Christophe · 2022 年7 月 9 日 09:21

tikv节点有多少个？

HACK · 2022 年7 月 9 日 12:40

看看扫描的key跟你执行时间少的时间范围，有没有明显差距非常大的现象。

just_coder · 2022 年7 月 11 日 01:09

3个 TiKV 节点

just_coder · 2022 年7 月 11 日 02:04

执行时间少的

慢查询的

h5n1 · 2022 年7 月 11 日 02:33

执行慢的这个SQL是每次执行都会慢吗

just_coder · 2022 年7 月 11 日 02:54

是的，每次查询都很慢，查看慢日志发现Cop_time的值非常大

just_coder · 2022 年7 月 11 日 02:58

目前确定的是查询条件中的时间间隔只要达到了7个月，查询耗时就会由6个月的4s左右变为40-50s，但是查看TiKV的CPU和内存消耗也不高

h5n1 · 2022 年7 月 11 日 03:16

show creata table发下

张雨齐0720 · 2022 年7 月 11 日 03:42

那一个月的数据量有多少？还有就是存储的region在哪些TiKV，是不是那一个月数据量过大，存储的节点较集中

just_coder · 2022 年7 月 11 日 03:53

| feature_dev_moment | CREATE TABLE feature_dev_moment (
recdate date NOT NULL COMMENT ‘日期’,
osid smallint(6) NOT NULL COMMENT ‘操作系统版本1-iOS 2-安卓’,
gameid varchar(255) NOT NULL DEFAULT ‘’,
cid varchar(20) NOT NULL DEFAULT ‘’ COMMENT ‘渠道ID’,
aid varchar(20) NOT NULL DEFAULT ‘’ COMMENT ‘广告位ID’,
oid varchar(20) NOT NULL DEFAULT ‘’ COMMENT ‘落地页ID’,
pt int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘平台ID’,
moment varchar(5) NOT NULL DEFAULT ‘0’ COMMENT ‘时刻’,
payment decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘投入’,
installCount int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘安装设备数-激活设备数’,
regLoginCount int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘注册账号数(平台登录)’,
regDevCount int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘注册设备数(平台登录)’,
newLoginCount int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘新登账户数’,
newDevCount int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘新登设备数’,
loginCount int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘总登录’,
newUserRoleCount int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘当天创角账号数’,
userRoleCount int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘总创角账号数’,
didNewCount int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘设备新登数(当天设备首登去重)’,
newUserIncome decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘当日充值金额’,
newUserPaypeople int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘当日充值人数’,
todayPay6people int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘至今充值6元的人数’,
todayIncome decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘至今充值金额’,
todayPaypeople int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘至今充值人数’,
newUserIncome_after decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘当日充值金额-账后’,
todayIncome_after decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘至今充值金额-账后’,
userLive1 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘次留人数’,
userLive3 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘3留人数’,
userLive4 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘4留人数’,
userLive5 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘5留人数’,
userLive6 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘6留人数’,
userLive7 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘7留人数’,
userLive8 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘8留人数’,
userLive9 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘9留人数’,
userLive10 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘10留人数’,
userLive11 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘11留人数’,
userLive12 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘12留人数’,
userLive13 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘13留人数’,
userLive14 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘14留人数’,
userLive15 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘15留人数’,
userLive16 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘16留人数’,
userLive30 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘30留人数’,
userLive60 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘60留人数’,
userLive90 int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘90留人数’,
ltv1Icome decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘等时1充值’,
ltv1Icome_after decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘等时账后充值’,
ltv3Icome decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘等时3充值’,
ltv3Icome_after decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘等时账后充值’,
ltv7Icome decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘等时7充值’,
ltv7Icome_after decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘等时7账后充值’,
ltv15Icome decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘等时15充值’,
ltv15Icome_after decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘等时15账后充值’,
ltv30Icome decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘等时30充值’,
ltv30Icome_after decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘等时30账后充值’,
ltv45Icome decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘等时45充值’,
ltv45Icome_after decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘等时45账后充值’,
ltv60Icome decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘等时60充值’,
ltv60Icome_after decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘等时60账后充值’,
ltv90Icome decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘等时90充值’,
ltv90Icome_after decimal(15,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘等时90账后充值’,
inner_time timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT ‘timestamp’,
newDidRoleCount int(11) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘新登创角设备数’,
didRoleCount int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘总创角设备数’,
field_1 int(11) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘备用字段’,
field_2 decimal(10,0) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘备用字段’,
PRIMARY KEY (recdate,osid,gameid,cid,aid,oid,pt,moment) /*T![clustered_index] NONCLUSTERED */,
KEY index_id (gameid,cid,aid,oid,moment,pt)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT=‘用户特征报表-时间时刻’

h5n1 · 2022 年7 月 11 日 03:59

慢的这个要处理307万的历史版本key，explain analyze 单独查下2021-12月的数据看看

just_coder · 2022 年7 月 11 日 05:57

单独查是很快的，现在问题是只要时间间隔达到七个月以上，查询就要几十秒，即使是查20210401-20211101也是一样慢

id                           	task     	estRows  	operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                	actRows	execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             	memory 	disk
Projection_7                 	root     	65.34    	yd_194_result.feature_dev_moment.recdate, yd_194_result.feature_dev_moment.aid, yd_194_result.feature_dev_moment.oid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.osid, yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.pt, Column#66, Column#67, Column#68, Column#69, Column#70, Column#71, Column#72, Column#73, Column#74, Column#75, Column#76, Column#77, Column#78, Column#79, Column#80, Column#81, Column#82, Column#83, Column#84, Column#85, Column#86, Column#87, Column#88, Column#89, Column#90, Column#91, Column#92, Column#98, Column#99, Column#100, Column#101, 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#93, Column#94, Column#95, Column#96, Column#97                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               	24318  	time:2.48s, loops:25, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      	7.65 MB	N/A
└─Sort_8                     	root     	65.34    	yd_194_result.feature_dev_moment.payment:desc                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                	24318  	time:2.47s, loops:25                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       	38.2 MB	0 Bytes
  └─Projection_10            	root     	65.34    	yd_194_result.feature_dev_moment.recdate, yd_194_result.feature_dev_moment.aid, yd_194_result.feature_dev_moment.oid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.osid, yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.pt, Column#66, Column#67, Column#68, Column#69, Column#70, Column#71, Column#72, Column#73, Column#74, Column#75, Column#76, Column#77, Column#78, Column#79, Column#80, Column#81, Column#82, Column#83, Column#84, Column#85, Column#86, Column#87, Column#88, Column#89, Column#90, Column#91, Column#92, 0->Column#98, 0->Column#99, 0->Column#100, 0->Column#101, 0->Column#102, 0->Column#103, 0->Column#104, 0->Column#105, 0->Column#106, 0->Column#107, 0->Column#108, 0->Column#109, 0->Column#110, 0->Column#111, 0->Column#112, 0->Column#113, 0->Column#114, 0->Column#115, Column#93, Column#94, Column#95, Column#96, Column#97, yd_194_result.feature_dev_moment.payment                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               	24318  	time:2.26s, loops:26, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      	32.9 MB	N/A
    └─HashAgg_29             	root     	65.34    	group by:yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.pt, yd_194_result.feature_dev_moment.recdate, funcs:sum(Column#236)->Column#66, funcs:sum(Column#237)->Column#67, funcs:sum(Column#238)->Column#68, funcs:sum(Column#239)->Column#69, funcs:sum(Column#240)->Column#70, funcs:sum(Column#241)->Column#71, funcs:sum(Column#242)->Column#72, funcs:sum(Column#243)->Column#73, funcs:sum(Column#244)->Column#74, funcs:sum(Column#245)->Column#75, funcs:sum(Column#246)->Column#76, funcs:sum(Column#247)->Column#77, funcs:sum(Column#248)->Column#78, funcs:sum(Column#249)->Column#79, funcs:sum(Column#250)->Column#80, funcs:sum(Column#251)->Column#81, funcs:sum(Column#252)->Column#82, funcs:sum(Column#253)->Column#83, funcs:sum(Column#254)->Column#84, funcs:sum(Column#255)->Column#85, funcs:sum(Column#256)->Column#86, funcs:sum(Column#257)->Column#87, funcs:sum(Column#258)->Column#88, funcs:sum(Column#259)->Column#89, funcs:sum(Column#260)->Column#90, funcs:sum(Column#261)->Column#91, funcs:sum(Column#262)->Column#92, funcs:sum(Column#263)->Column#93, funcs:sum(Column#264)->Column#94, funcs:sum(Column#265)->Column#95, funcs:sum(Column#266)->Column#96, funcs:sum(Column#267)->Column#97, funcs:firstrow(yd_194_result.feature_dev_moment.recdate)->yd_194_result.feature_dev_moment.recdate, funcs:firstrow(Column#269)->yd_194_result.feature_dev_moment.osid, funcs:firstrow(yd_194_result.feature_dev_moment.gameid)->yd_194_result.feature_dev_moment.gameid, funcs:firstrow(yd_194_result.feature_dev_moment.cid)->yd_194_result.feature_dev_moment.cid, funcs:firstrow(Column#272)->yd_194_result.feature_dev_moment.aid, funcs:firstrow(Column#273)->yd_194_result.feature_dev_moment.oid, funcs:firstrow(yd_194_result.feature_dev_moment.pt)->yd_194_result.feature_dev_moment.pt, funcs:firstrow(Column#275)->yd_194_result.feature_dev_moment.payment                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         	24318  	time:2.25s, loops:26, partial_worker:{wall_time:2.057477579s, concurrency:5, task_num:24, tot_wait:9.848699288s, tot_exec:373.551603ms, tot_time:10.244315181s, max:2.057303528s, p95:2.057303528s}, final_worker:{wall_time:2.265677267s, concurrency:5, task_num:25, tot_wait:10.194230485s, tot_exec:1.099307156s, tot_time:11.293589357s, max:2.265470875s, p95:2.265470875s}                                                                                                                                                                          	88.8 MB	N/A
      └─IndexLookUp_30       	root     	65.34    	                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             	24338  	time:2.03s, loops:25, index_task: {total_time: 1.76s, fetch_handle: 752.7ms, build: 103.8µs, wait: 1s}, table_task: {total_time: 6.25s, num: 20, concurrency: 5}                                                                                                                                                                                                                                                                                                                                                                                          	15.7 MB	N/A
        ├─Selection_25       	cop[tikv]	302142.09	not(in(yd_194_result.feature_dev_moment.gameid, "1604385436956830", "1621669402391940", "1632711274294320", "1636099609615800", "1639364435557330", "1639126762567720", "1616571507848110", "1639110127366330", "1640159205240000", "1633765571609960"))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     	329041 	time:706.1ms, loops:329, cop_task: {num: 1, max: 701.4ms, proc_keys: 334995, tot_proc: 655ms, tot_wait: 3ms, rpc_num: 1, rpc_time: 701.4ms, copr_cache_hit_ratio: 0.00}, tikv_task:{time:641ms, loops:332}, scan_detail: {total_process_keys: 334995, total_process_keys_size: 47904285, total_keys: 334996, rocksdb: {delete_skipped_count: 0, key_skipped_count: 334995, block: {cache_hit_count: 352, read_count: 0, read_byte: 0 Bytes}}}                                                                                                              	N/A    	N/A
        │ └─IndexRangeScan_23	cop[tikv]	307792.50	table:v, index:PRIMARY(recdate, osid, gameid, cid, aid, oid, pt, moment), range:[2021-12-01,2021-12-31], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    	334995 	tikv_task:{time:495ms, loops:332}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          	N/A    	N/A
        └─HashAgg_12         	cop[tikv]	65.34    	group by:yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.cid, yd_194_result.feature_dev_moment.gameid, yd_194_result.feature_dev_moment.pt, yd_194_result.feature_dev_moment.recdate, funcs:sum(yd_194_result.feature_dev_moment.installcount)->Column#236, funcs:sum(yd_194_result.feature_dev_moment.regdevcount)->Column#237, funcs:sum(yd_194_result.feature_dev_moment.newdevcount)->Column#238, funcs:sum(yd_194_result.feature_dev_moment.newdidrolecount)->Column#239, funcs:sum(yd_194_result.feature_dev_moment.didrolecount)->Column#240, funcs:sum(yd_194_result.feature_dev_moment.reglogincount)->Column#241, funcs:sum(yd_194_result.feature_dev_moment.newlogincount)->Column#242, funcs:sum(yd_194_result.feature_dev_moment.newuserrolecount)->Column#243, funcs:sum(yd_194_result.feature_dev_moment.userrolecount)->Column#244, funcs:sum(yd_194_result.feature_dev_moment.userlive1)->Column#245, funcs:sum(yd_194_result.feature_dev_moment.userlive3)->Column#246, funcs:sum(yd_194_result.feature_dev_moment.userlive4)->Column#247, funcs:sum(yd_194_result.feature_dev_moment.userlive5)->Column#248, funcs:sum(yd_194_result.feature_dev_moment.userlive6)->Column#249, funcs:sum(yd_194_result.feature_dev_moment.userlive7)->Column#250, funcs:sum(yd_194_result.feature_dev_moment.userlive8)->Column#251, funcs:sum(yd_194_result.feature_dev_moment.userlive9)->Column#252, funcs:sum(yd_194_result.feature_dev_moment.userlive10)->Column#253, funcs:sum(yd_194_result.feature_dev_moment.userlive11)->Column#254, funcs:sum(yd_194_result.feature_dev_moment.userlive12)->Column#255, funcs:sum(yd_194_result.feature_dev_moment.userlive13)->Column#256, funcs:sum(yd_194_result.feature_dev_moment.userlive14)->Column#257, funcs:sum(yd_194_result.feature_dev_moment.userlive15)->Column#258, funcs:sum(yd_194_result.feature_dev_moment.userlive16)->Column#259, funcs:sum(yd_194_result.feature_dev_moment.userlive30)->Column#260, funcs:sum(yd_194_result.feature_dev_moment.userlive60)->Column#261, funcs:sum(yd_194_result.feature_dev_moment.userlive90)->Column#262, funcs:sum(yd_194_result.feature_dev_moment.newuserincome)->Column#263, funcs:sum(yd_194_result.feature_dev_moment.newuserpaypeople)->Column#264, funcs:sum(yd_194_result.feature_dev_moment.todayincome)->Column#265, funcs:sum(yd_194_result.feature_dev_moment.todaypaypeople)->Column#266, funcs:sum(yd_194_result.feature_dev_moment.todaypay6people)->Column#267, funcs:firstrow(yd_194_result.feature_dev_moment.osid)->Column#269, funcs:firstrow(yd_194_result.feature_dev_moment.aid)->Column#272, funcs:firstrow(yd_194_result.feature_dev_moment.oid)->Column#273, funcs:firstrow(yd_194_result.feature_dev_moment.payment)->Column#275	24338  	time:6.02s, loops:42, cop_task: {num: 22, max: 525.6ms, min: 53.1ms, avg: 280ms, p95: 476.1ms, max_proc_keys: 20946, p95_proc_keys: 20507, tot_proc: 5.74s, tot_wait: 17ms, rpc_num: 43, rpc_time: 12.3s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:493ms, min:42ms, p80:391ms, p95:448ms, iters:328, tasks:22}, scan_detail: {total_process_keys: 329041, total_process_keys_size: 161876063, total_keys: 658262, rocksdb: {delete_skipped_count: 0, key_skipped_count: 986945, block: {cache_hit_count: 3891, read_count: 0, read_byte: 0 Bytes}}}	N/A    	N/A
          └─TableRowIDScan_24	cop[tikv]	302142.09	table:v, keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    	329041 	tikv_task:{proc max:205ms, min:17ms, p80:153ms, p95:182ms, iters:328, tasks:22}                                                                                                                                                                                                                                                                                                                                                                                                                                                                            	N/A    	N/A

h5n1 · 2022 年7 月 11 日 06:39

执行慢的SQL时看看3个tikv的CPU使用率

just_coder · 2022 年7 月 11 日 07:17

tidb狂热爱好者 · 2022 年7 月 11 日 16:34

你这个是统计查询.得用tilfash.你自己也说了查询7个月的数据.这种是clickhouse的功能

tidb狂热爱好者 · 2022 年7 月 11 日 16:35

而且你这张表摆明了是数据分析表.htap的功能.用tikv不合适