【 TiDB 使用环境】测试环境
【 TiDB 版本】6.15
【复现路径】做过哪些操作出现的问题，在navicat上执行同一段sql，查看dashboard上的执行计划。
【遇到的问题：问题现象及影响】同样的数据，在5分钟内执行两次同样的sql，同样走tiflash，第一遍查询时间一分钟，第二遍查询时间695.5ms
【资源配置】进入到 TiDB Dashboard -集群信息 (Cluster Info) -主机(Hosts) 截图此页面
【附件：截图/日志/监控】
查询速度快的执行计划：
| id | task | estRows | operator info | actRows | execution info | memory | disk | |
|---|---|---|---|---|---|---|---|---|
| TableReader_97 | root | 1 | data:ExchangeSender_96 | 3 | time:687.8ms, loops:2, cop_task: {num: 4, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | N/A | N/A | |
| └─ExchangeSender_96 | cop[tiflash] | 1 | ExchangeType: PassThrough | 3 | tiflash_task:{proc max:684.9ms, min:684ms, p80:684.9ms, p95:684.9ms, iters:2, tasks:2, threads:40} | N/A | N/A | |
| └─Projection_7 | cop[tiflash] | 1 | crm_biz.t_coupon_item.member_id, crm_biz.t_coupon_item.coupon_type, crm_biz.t_coupon_item.create_time, case(gt(Column#55, 0), 已使用, 未使用)->Column#57, case(Column#55, 1, 0)->Column#58, Column#56 | 3 | tiflash_task:{proc max:684.9ms, min:684ms, p80:684.9ms, p95:684.9ms, iters:2, tasks:2, threads:40} | N/A | N/A | |
| └─Projection_91 | cop[tiflash] | 1 | Column#55, Column#56, crm_biz.t_coupon_item.member_id, crm_biz.t_coupon_item.coupon_type, crm_biz.t_coupon_item.create_time | 3 | tiflash_task:{proc max:684.9ms, min:684ms, p80:684.9ms, p95:684.9ms, iters:2, tasks:2, threads:40} | N/A | N/A | |
| └─HashAgg_89 | cop[tiflash] | 1 | group by:Column#115, Column#116, Column#117, funcs:count(Column#110)->Column#55, funcs:sum(Column#111)->Column#56, funcs:firstrow(Column#112)->crm_biz.t_coupon_item.member_id, funcs:firstrow(Column#113)->crm_biz.t_coupon_item.coupon_type, funcs:firstrow(Column#114)->crm_biz.t_coupon_item.create_time | 3 | tiflash_task:{proc max:684.9ms, min:684ms, p80:684.9ms, p95:684.9ms, iters:2, tasks:2, threads:2} | N/A | N/A | |
| └─Projection_98 | cop[tiflash] | 0.00 | if(eq(crm_biz.t_coupon_item.coupon_status, YHQDZT03), 1, )->Column#110, if(eq(crm_biz.t_coupon_item.coupon_status, YHQDZT03), crm_biz.t_coupon_item.amount, 0)->Column#111, crm_biz.t_coupon_item.member_id, crm_biz.t_coupon_item.coupon_type, crm_biz.t_coupon_item.create_time, crm_biz.t_coupon_item.member_id, crm_biz.t_coupon_item.coupon_id, crm_biz.t_coupon_item.create_time | 3 | tiflash_task:{proc max:684.9ms, min:683ms, p80:684.9ms, p95:684.9ms, iters:3, tasks:2, threads:40} | N/A | N/A | |
| └─ExchangeReceiver_68 | cop[tiflash] | 0.00 | 3 | tiflash_task:{proc max:683.9ms, min:682ms, p80:683.9ms, p95:683.9ms, iters:3, tasks:2, threads:40} | N/A | N/A | ||
| └─ExchangeSender_67 | cop[tiflash] | 0.00 | ExchangeType: HashPartition, Hash Cols: [name: crm_biz.t_coupon_item.member_id, collate: binary], [name: crm_biz.t_coupon_item.coupon_id, collate: binary], [name: crm_biz.t_coupon_item.create_time, collate: binary] | 3 | tiflash_task:{proc max:680.1ms, min:0s, p80:680.1ms, p95:680.1ms, iters:2, tasks:2, threads:40} | N/A | N/A | |
| └─Selection_66 | cop[tiflash] | 0.00 | eq(crm_biz.t_coupon_item.coupon_id, 1628558630971002882), eq(crm_biz.t_coupon_item.is_deleted, 0), eq(crm_biz.t_coupon_item.status, 1), eq(crm_biz.t_coupon_item.tenant_id, 135490), in(crm_biz.t_coupon_item.coupon_status, YHQDZT02, YHQDZT03), in(crm_biz.t_coupon_item.user_id, 1384806815250747393, 1384807774307717122, 1384807936941854722, 1384808144899641346, 1384808174008111105, 1384808207046643713, 1548974653208727554, 1552597006505000961, 1560492491632398338) | 3 | tiflash_task:{proc max:680.1ms, min:0s, p80:680.1ms, p95:680.1ms, iters:2, tasks:2, threads:40} | N/A | N/A | |
| └─TableFullScan_65 | cop[tiflash] | 75720941 | table:t_coupon_item, keep order:false | 76501629 | tiflash_task:{proc max:539.1ms, min:0s, p80:539.1ms, p95:539.1ms, iters:1210, tasks:2, threads:40} | N/A | N/A |
查询比较慢的执行计划：
| id | task | estRows | operator info | actRows | execution info | memory | disk | |
|---|---|---|---|---|---|---|---|---|
| TableReader_97 | root | 1 | data:ExchangeSender_96 | 0 | time:1m0.6s, loops:1 | N/A | N/A | |
| └─ExchangeSender_96 | cop[tiflash] | 1 | ExchangeType: PassThrough | 0 | N/A | N/A | ||
| └─Projection_7 | cop[tiflash] | 1 | crm_biz.t_coupon_item.member_id, crm_biz.t_coupon_item.coupon_type, crm_biz.t_coupon_item.create_time, case(gt(Column#55, 0), 已使用, 未使用)->Column#57, case(Column#55, 1, 0)->Column#58, Column#56 | 0 | N/A | N/A | ||
| └─Projection_91 | cop[tiflash] | 1 | Column#55, Column#56, crm_biz.t_coupon_item.member_id, crm_biz.t_coupon_item.coupon_type, crm_biz.t_coupon_item.create_time | 0 | N/A | N/A | ||
| └─HashAgg_89 | cop[tiflash] | 1 | group by:Column#115, Column#116, Column#117, funcs:count(Column#110)->Column#55, funcs:sum(Column#111)->Column#56, funcs:firstrow(Column#112)->crm_biz.t_coupon_item.member_id, funcs:firstrow(Column#113)->crm_biz.t_coupon_item.coupon_type, funcs:firstrow(Column#114)->crm_biz.t_coupon_item.create_time | 0 | N/A | N/A | ||
| └─Projection_98 | cop[tiflash] | 0.00 | if(eq(crm_biz.t_coupon_item.coupon_status, YHQDZT03), 1, )->Column#110, if(eq(crm_biz.t_coupon_item.coupon_status, YHQDZT03), crm_biz.t_coupon_item.amount, 0)->Column#111, crm_biz.t_coupon_item.member_id, crm_biz.t_coupon_item.coupon_type, crm_biz.t_coupon_item.create_time, crm_biz.t_coupon_item.member_id, crm_biz.t_coupon_item.coupon_id, crm_biz.t_coupon_item.create_time | 0 | N/A | N/A | ||
| └─ExchangeReceiver_68 | cop[tiflash] | 0.00 | 0 | N/A | N/A | |||
| └─ExchangeSender_67 | cop[tiflash] | 0.00 | ExchangeType: HashPartition, Hash Cols: [name: crm_biz.t_coupon_item.member_id, collate: binary], [name: crm_biz.t_coupon_item.coupon_id, collate: binary], [name: crm_biz.t_coupon_item.create_time, collate: binary] | 0 | N/A | N/A | ||
| └─Selection_66 | cop[tiflash] | 0.00 | eq(crm_biz.t_coupon_item.coupon_id, 1628558630971002882), eq(crm_biz.t_coupon_item.is_deleted, 0), eq(crm_biz.t_coupon_item.status, 1), eq(crm_biz.t_coupon_item.tenant_id, 135490), in(crm_biz.t_coupon_item.coupon_status, YHQDZT02, YHQDZT03), in(crm_biz.t_coupon_item.user_id, 1384806815250747393, 1384807774307717122, 1384807936941854722, 1384808144899641346, 1384808174008111105, 1384808207046643713, 1548974653208727554, 1552597006505000961, 1560492491632398338) | 0 | N/A | N/A | ||
| └─TableFullScan_65 | cop[tiflash] | 75720941 | table:t_coupon_item, keep order:false | 0 | N/A | N/A |
sql：
EXPLAIN ANALYZE
SELECT
member_id memberId,
coupon_type couponType,
create_time collectionTime,
CASE
WHEN count(
IF (coupon_status = ‘YHQDZT03’, 1, NULL)
) > 0 THEN ‘已使用’
ELSE ‘未使用’
END couponStatus,
CASE
WHEN count(
IF (coupon_status = ‘YHQDZT03’, 1, NULL)
) THEN 1
ELSE 0
END type,
sum(
IF (coupon_status = ‘YHQDZT03’, amount, 0)
) relateSales
FROM
(
SELECT
member_id,
coupon_type,
create_time,
coupon_status,
coupon_id,
amount
FROM
t_coupon_item
WHERE
STATUS = 1
AND is_deleted = 0
AND coupon_status IN (‘YHQDZT02’, ‘YHQDZT03’)
AND user_id IN (
1384806815250747393,
1384807774307717122,
1384807936941854722,
1384808144899641346,
1384808174008111105,
1384808207046643713,
1548974653208727554,
1552597006505000961,
1560492491632398338
)
AND coupon_id IN (1628558630971002882)
AND tenant_id = ‘135490’
) t1
GROUP BY
member_id,
coupon_id,
create_time;
