【 TiDB 使用环境】生产环境 /测试/ Poc
【 TiDB 版本】7.1.2
【遇到的问题：问题现象及影响】
sql使用了with as
最终是3个select 结果union all
结果：
第一个select语句返回5155条
第二个select语句返回275条,去掉第三个和第一个union all 结果是5430条，条数正确
第二个select语句返回8条，3个语句union all结果5163条，出现了bug
有问题的sql.txt (12.8 KB)
WITH t_draw AS
(SELECT gd.draw_no,game_id,gd.draw_id,gd.draw_time,gd.paid_begin_time,gd.paid_end_time
FROM game_draw gd
WHERE '2023-08-01' BETWEEN gd.sale_begin_time AND gd.paid_end_time),
t_clerk AS
(SELECT clerk_id, s.rdc_id, s.rdc_id AS p_id,s.shop_id
FROM clerk c ,shop s
where c.shop_id = s.shop_id
and s.rdc_id = 11),
t_rdc AS
(SELECT
1 AS typ,
operator_bj_id AS clerk_id,
rdc_id AS province_center_id,
0 AS branch_id
FROM
rdc_operator a
UNION ALL
SELECT
3 AS typ,
a.operator_bj_id,
a.province_center_id,
a.branch_id
FROM
country_branch_operator a),
t_sale AS
(
SELECT '2023-08-01' AS report_date,
p_id,
gd.game_id,
gd.draw_no,
shop_id,
MAX(gd.draw_id) AS draw_id,
COUNT(ticket_no) AS sale_ticket,
SUM(t.multiple * t.chances) AS sale_cnt,
SUM(t.multiple * t.chances * 2) AS sale_amt
FROM ticket t
JOIN t_draw gd ON t.draw_id = gd.draw_id
JOIN t_clerk tc ON t.clerk_id = tc.clerk_id
WHERE sale_time BETWEEN '2023-08-01 00:00:00' AND '2023-08-01 23:59:59'
AND gd.game_id IN (200, 210, 230, 240, 250, 260, 270)
GROUP BY DATE(sale_time), p_id, gd.game_id, gd.draw_no, shop_id),
t_win_ticket AS
(SELECT wt.draw_id,
wt.ticket_no,
MAX(p_id) AS p_id,
MAX(td.game_id) AS game_id,
MAX(draw_no) AS draw_no,
MAX(tc.shop_id) AS shop_id,
MAX(wt.paid_operator_id) AS paid_operator_id,
MAX(wt.clerk_id) AS clerk_id,
MAX(wt.paid_time) AS paid_time,
MAX(paid_end_time) AS paid_end_time,
MAX(IF(wt.paid_type IS NULL OR wt.paid_type = 0, 0, 1)) AS prz_ticket,
SUM(IF(wt.paid_type IS NULL OR wt.paid_type = 0, 0, wt.prz_amt)) AS prz_amt,
SUM(IF(wt.paid_type IS NULL OR wt.paid_type = 0, 0, wt.prz_cnt)) AS prz_cnt,
SUM(tax_amt) AS tax_amt,
MAX(IF(wt.paid_type IS NULL OR wt.paid_type = 0, 0, IF(wt.withdraw_amt IS NULL OR wt.withdraw_amt = 0, 0, 1))) AS withdraw_ticket,
SUM(IF(wt.paid_type IS NULL OR wt.paid_type = 0, 0, IFNULL(wt.withdraw_amt, 0) / 2)) AS withdraw_cnt,
SUM(IF(wt.paid_type IS NULL OR wt.paid_type = 0, 0, IFNULL(wt.withdraw_amt, 0))) AS withdraw_amt,
MAX(wt.paid_type) AS paid_type,
MAX(IF(wtp.paid_type IS NULL OR wtp.paid_type = 0, 0, 1)) AS prize_ticket,
MAX(IF(wtp.paid_type IS NULL OR wtp.paid_type = 0, 0, IFNULL(prize_amt, 0))) AS prize_amt,
MAX(IF(wtp.paid_type IS NULL OR wtp.paid_type = 0, 0, IFNULL(prize_cnt, 0))) AS prize_cnt,
MAX(IFNULL(prize_tax, 0)) AS prize_tax
FROM win_ticket wt
JOIN t_draw td ON wt.draw_id = td.draw_id
JOIN t_clerk tc ON wt.clerk_id = tc.clerk_id
LEFT JOIN win_ticket_prize wtp ON wt.draw_id = wtp.draw_id AND wt.ticket_no = wtp.ticket_no
WHERE wt.paid_time BETWEEN '2023-08-01 00:00:00' AND '2023-08-01 23:59:59'
GROUP BY wt.draw_id, wt.ticket_no),
t_toto_win AS
(SELECT DATE(wt.paid_time) AS paid_time,
wt.p_id,
game_id,
draw_no,
tc.shop_id,
MAX(draw_id) AS draw_id,
SUM(prz_ticket) AS prz_ticket,
SUM(prz_cnt) AS prz_cnt,
SUM(prz_amt) AS prz_amt,
SUM(prize_ticket) AS prize_ticket,
SUM(prize_cnt) AS prize_cnt,
SUM(prize_amt) AS prize_amt,
SUM(withdraw_ticket) AS withdraw_ticket,
SUM(withdraw_cnt) AS withdraw_cnt,
SUM(withdraw_amt) AS withdraw_amt
FROM t_win_ticket wt
JOIN t_clerk tc ON wt.paid_operator_id = tc.clerk_id
WHERE wt.paid_type = 2
GROUP BY DATE(wt.paid_time), wt.p_id, wt.game_id, wt.draw_no, tc.shop_id)
-- 主查询
-- 第一个查询结果5155条
SELECT '2023-08-01' AS report_date
FROM t_sale ts
LEFT JOIN t_toto_win ttw ON (ts.report_date = ttw.paid_time AND ts.p_id = ttw.p_id AND
ts.game_id = ttw.game_id AND ts.draw_no = ttw.draw_no AND
ts.shop_id = ttw.shop_id)
UNION ALL
-- 第二个查询结果275条 和第一个合计5430条
SELECT '2023-08-01' AS report_date
FROM t_toto_win ttw
WHERE NOT EXISTS (SELECT 1
FROM t_sale ts
WHERE ts.report_date = ttw.paid_time
AND ts.p_id = ttw.p_id
AND ts.game_id = ttw.game_id
AND ts.draw_no = ttw.draw_no
AND ts.shop_id = ttw.shop_id)
UNION ALL
-- 第三个查询结果8条 三个合计应为5438条，实际为5163条
SELECT '2023-08-01' AS report_date
FROM t_win_ticket wt
JOIN t_rdc tc ON wt.paid_operator_id = tc.clerk_id AND wt.paid_type = tc.typ
WHERE wt.paid_type IN (1, 3)
GROUP BY DATE_FORMAT(paid_time, '%Y%m%d'),
wt.p_id,
wt.game_id,
wt.draw_no,
tc.branch_id;
执行计划.txt (136.7 KB)
补充一下：
把前两个sql 用select * () 括起来，再和第三个union all结果还是一样错的。
把3个查询结果数据导入3个表，union all结果是对的
5163是第一个selete和第三个select条数和
补充下少了的275条记录的查询sql
explain ANALYZE
WITH t_draw AS
(SELECT gd.draw_no,game_id,gd.draw_id,gd.draw_time,gd.paid_begin_time,gd.paid_end_time
FROM game_draw gd
WHERE '2023-08-01' BETWEEN gd.sale_begin_time AND gd.paid_end_time),
t_clerk AS
(SELECT clerk_id, s.rdc_id, s.rdc_id AS p_id,s.shop_id
FROM clerk c ,shop s
where c.shop_id = s.shop_id
and s.rdc_id = 11),
t_rdc AS
(SELECT
1 AS typ,
operator_bj_id AS clerk_id,
rdc_id AS province_center_id,
0 AS branch_id
FROM
rdc_operator a
UNION ALL
SELECT
3 AS typ,
a.operator_bj_id,
a.province_center_id,
a.branch_id
FROM
country_branch_operator a),
t_sale AS
(
SELECT '2023-08-01' AS report_date,
p_id,
gd.game_id,
gd.draw_no,
shop_id,
MAX(gd.draw_id) AS draw_id,
COUNT(ticket_no) AS sale_ticket,
SUM(t.multiple * t.chances) AS sale_cnt,
SUM(t.multiple * t.chances * 2) AS sale_amt
FROM ticket t
JOIN t_draw gd ON t.draw_id = gd.draw_id
JOIN t_clerk tc ON t.clerk_id = tc.clerk_id
WHERE sale_time BETWEEN '2023-08-01 00:00:00' AND '2023-08-01 23:59:59'
AND gd.game_id IN (200, 210, 230, 240, 250, 260, 270)
GROUP BY DATE(sale_time), p_id, gd.game_id, gd.draw_no, shop_id),
t_win_ticket AS
(SELECT wt.draw_id,
wt.ticket_no,
MAX(p_id) AS p_id,
MAX(td.game_id) AS game_id,
MAX(draw_no) AS draw_no,
MAX(tc.shop_id) AS shop_id,
MAX(wt.paid_operator_id) AS paid_operator_id,
MAX(wt.clerk_id) AS clerk_id,
MAX(wt.paid_time) AS paid_time,
MAX(paid_end_time) AS paid_end_time,
MAX(IF(wt.paid_type IS NULL OR wt.paid_type = 0, 0, 1)) AS prz_ticket,
SUM(IF(wt.paid_type IS NULL OR wt.paid_type = 0, 0, wt.prz_amt)) AS prz_amt,
SUM(IF(wt.paid_type IS NULL OR wt.paid_type = 0, 0, wt.prz_cnt)) AS prz_cnt,
SUM(tax_amt) AS tax_amt,
MAX(IF(wt.paid_type IS NULL OR wt.paid_type = 0, 0, IF(wt.withdraw_amt IS NULL OR wt.withdraw_amt = 0, 0, 1))) AS withdraw_ticket,
SUM(IF(wt.paid_type IS NULL OR wt.paid_type = 0, 0, IFNULL(wt.withdraw_amt, 0) / 2)) AS withdraw_cnt,
SUM(IF(wt.paid_type IS NULL OR wt.paid_type = 0, 0, IFNULL(wt.withdraw_amt, 0))) AS withdraw_amt,
MAX(wt.paid_type) AS paid_type,
MAX(IF(wtp.paid_type IS NULL OR wtp.paid_type = 0, 0, 1)) AS prize_ticket,
MAX(IF(wtp.paid_type IS NULL OR wtp.paid_type = 0, 0, IFNULL(prize_amt, 0))) AS prize_amt,
MAX(IF(wtp.paid_type IS NULL OR wtp.paid_type = 0, 0, IFNULL(prize_cnt, 0))) AS prize_cnt,
MAX(IFNULL(prize_tax, 0)) AS prize_tax
FROM win_ticket wt
JOIN t_draw td ON wt.draw_id = td.draw_id
JOIN t_clerk tc ON wt.clerk_id = tc.clerk_id
LEFT JOIN win_ticket_prize wtp ON wt.draw_id = wtp.draw_id AND wt.ticket_no = wtp.ticket_no
WHERE wt.paid_time BETWEEN '2023-08-01 00:00:00' AND '2023-08-01 23:59:59'
GROUP BY wt.draw_id, wt.ticket_no),
t_toto_win AS
(SELECT DATE(wt.paid_time) AS paid_time,
wt.p_id,
game_id,
draw_no,
tc.shop_id,
MAX(draw_id) AS draw_id,
SUM(prz_ticket) AS prz_ticket,
SUM(prz_cnt) AS prz_cnt,
SUM(prz_amt) AS prz_amt,
SUM(prize_ticket) AS prize_ticket,
SUM(prize_cnt) AS prize_cnt,
SUM(prize_amt) AS prize_amt,
SUM(withdraw_ticket) AS withdraw_ticket,
SUM(withdraw_cnt) AS withdraw_cnt,
SUM(withdraw_amt) AS withdraw_amt
FROM t_win_ticket wt
JOIN t_clerk tc ON wt.paid_operator_id = tc.clerk_id
WHERE wt.paid_type = 2
GROUP BY DATE(wt.paid_time), wt.p_id, wt.game_id, wt.draw_no, tc.shop_id)
-- 第二个查询结果275条 和第一个合计5430条
SELECT '2023-08-01' AS report_date
FROM t_toto_win ttw
WHERE NOT EXISTS (SELECT 1
FROM t_sale ts
WHERE ts.report_date = ttw.paid_time
AND ts.p_id = ttw.p_id
AND ts.game_id = ttw.game_id
AND ts.draw_no = ttw.draw_no
AND ts.shop_id = ttw.shop_id)
执行计划
+------------------------------------------------------+-------------+----------+-----------+-------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
+------------------------------------------------------+-------------+----------+-----------+-------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
| Projection_152 | 37.25 | 275 | root | | time:5.94s, loops:6, RU:253042.174315, Concurrency:OFF | 2023-08-01->Column#431 | 0 Bytes | N/A |
| └─HashJoin_153 | 37.25 | 275 | root | | time:5.94s, loops:6, build_hash_table:{total:4.81s, fetch:4.81s, build:3ms}, probe:{concurrency:5, total:29.7s, max:5.94s, probe:2.76ms, fetch:29.7s} | anti semi join, equal:[eq(Column#358, z_qianyi.shop.rdc_id) eq(Column#359, z_qianyi.game_draw.game_id) eq(Column#360, z_qianyi.game_draw.draw_no) eq(z_qianyi.shop.shop_id, z_qianyi.shop.shop_id) eq(Column#392, Column#437)] | 663.2 KB | 0 Bytes |
| ├─Projection_177(Build) | 72.75 | 5155 | root | | time:4.81s, loops:8, Concurrency:OFF | z_qianyi.shop.rdc_id, z_qianyi.game_draw.game_id, z_qianyi.game_draw.draw_no, z_qianyi.shop.shop_id, 2023-08-01 00:00:00.000000->Column#437 | 63.4 KB | N/A |
| │ └─HashAgg_178 | 72.75 | 5155 | root | | time:4.81s, loops:8, partial_worker:{wall_time:4.80216536s, concurrency:5, task_num:33, tot_wait:23.981014154s, tot_exec:25.963145ms, tot_time:24.009428803s, max:4.802134401s, p95:4.802134401s}, final_worker:{wall_time:4.807785239s, concurrency:5, task_num:25, tot_wait:24.008472546s, tot_exec:19.536721ms, tot_time:24.028023524s, max:4.807753505s, p95:4.807753505s} | group by:Column#457, Column#458, Column#459, Column#460, Column#461, funcs:firstrow(Column#453)->z_qianyi.game_draw.draw_no, funcs:firstrow(Column#454)->z_qianyi.game_draw.game_id, funcs:firstrow(Column#455)->z_qianyi.shop.rdc_id, funcs:firstrow(Column#456)->z_qianyi.shop.shop_id, funcs:count(1)->Column#442 | 5.44 MB | N/A |
| │ └─Projection_192 | 72.75 | 32826 | root | | time:4.8s, loops:34, Concurrency:OFF | z_qianyi.game_draw.draw_no, z_qianyi.game_draw.game_id, z_qianyi.shop.rdc_id, z_qianyi.shop.shop_id, date(z_qianyi.ticket.sale_time)->Column#457, z_qianyi.shop.rdc_id, z_qianyi.game_draw.game_id, z_qianyi.game_draw.draw_no, z_qianyi.shop.shop_id | 1.18 MB | N/A |
| │ └─Projection_179 | 72.75 | 32826 | root | | time:4.8s, loops:34, Concurrency:OFF | z_qianyi.ticket.sale_time, z_qianyi.game_draw.draw_no, z_qianyi.game_draw.game_id, z_qianyi.shop.rdc_id, z_qianyi.shop.shop_id | 1.19 MB | N/A |
| │ └─HashJoin_181 | 72.75 | 32826 | root | | time:4.8s, loops:34, build_hash_table:{total:4.78s, fetch:4.78s, build:4.51ms}, probe:{concurrency:5, total:23.9s, max:4.8s, probe:17.9ms, fetch:23.9s} | inner join, equal:[eq(z_qianyi.ticket.draw_id, z_qianyi.game_draw.draw_id)] | 1.74 MB | 0 Bytes |
| │ ├─HashJoin_183(Build) | 58.20 | 32826 | root | | time:4.78s, loops:35, build_hash_table:{total:705.7ms, fetch:699.3ms, build:6.47ms}, probe:{concurrency:5, total:23.9s, max:4.78s, probe:135.1ms, fetch:23.8s} | inner join, equal:[eq(z_qianyi.clerk.clerk_id, z_qianyi.ticket.clerk_id)] | 1.97 MB | 0 Bytes |
| │ │ ├─Selection_184(Build) | 46.56 | 19115 | root | | time:700.6ms, loops:20 | not(isnull(z_qianyi.clerk.clerk_id)) | 33.0 KB | N/A |
| │ │ │ └─CTEFullScan_185 | 58.20 | 19115 | root | CTE:t_clerk AS tc | time:698.4ms, loops:23 | data:CTE_1 | N/A | N/A |
| │ │ └─TableReader_188(Probe) | 676526.22 | 335904 | root | | time:4.77s, loops:328, cop_task: {num: 132, max: 2s, min: 2.89ms, avg: 447.2ms, p95: 1.8s, max_proc_keys: 554929, p95_proc_keys: 519430, tot_proc: 35.3s, tot_wait: 93.3ms, rpc_num: 132, rpc_time: 59s, copr_cache_hit_ratio: 0.00, build_task_duration: 18.3ms, max_distsql_concurrency: 15} | data:Selection_187 | 1015.3 KB | N/A |
| │ │ └─Selection_187 | 676526.22 | 335904 | cop[tikv] | | tikv_task:{proc max:2s, min:0s, avg: 444.7ms, p80:852ms, p95:1.79s, iters:26989, tasks:132}, scan_detail: {total_process_keys: 27061049, total_process_keys_size: 6167855307, total_keys: 27061181, get_snapshot_time: 64.9ms, rocksdb: {key_skipped_count: 27061426, block: {cache_hit_count: 102418, read_count: 89725, read_byte: 1.43 GB, read_time: 712.8ms}}} | ge(z_qianyi.ticket.sale_time, 2023-08-01 00:00:00.000000), le(z_qianyi.ticket.sale_time, 2023-08-01 23:59:59.000000) | N/A | N/A |
| │ │ └─TableFullScan_186 | 27061049.00 | 27061049 | cop[tikv] | table:t | tikv_task:{proc max:1.96s, min:0s, avg: 431.6ms, p80:832ms, p95:1.76s, iters:26989, tasks:132} | keep order:false, stats:pseudo | N/A | N/A |
| │ └─Selection_189(Probe) | 967.13 | 66 | root | | time:263.7µs, loops:2 | in(z_qianyi.game_draw.game_id, 200, 210, 230, 240, 250, 260, 270), not(isnull(z_qianyi.game_draw.draw_id)) | 4.51 KB | N/A |
| │ └─CTEFullScan_190 | 1208.92 | 66 | root | CTE:t_draw AS gd | time:197.6µs, loops:3 | data:CTE_0 | N/A | N/A |
| └─Projection_154(Probe) | 46.56 | 1631 | root | | time:5.94s, loops:6, Concurrency:OFF | date(Column#364)->Column#392, Column#358, Column#359, Column#360, z_qianyi.shop.shop_id | 79.2 KB | N/A |
| └─HashAgg_155 | 46.56 | 1631 | root | | time:5.94s, loops:6, partial_worker:{wall_time:5.935260018s, concurrency:5, task_num:5, tot_wait:29.661274725s, tot_exec:6.244359ms, tot_time:29.668061532s, max:5.935046079s, p95:5.935046079s}, final_worker:{wall_time:5.935649535s, concurrency:5, task_num:25, tot_wait:29.672034051s, tot_exec:5.549346ms, tot_time:29.677595074s, max:5.935578304s, p95:5.935578304s} | group by:Column#448, Column#449, Column#450, Column#451, Column#452, funcs:firstrow(Column#443)->Column#358, funcs:firstrow(Column#444)->Column#359, funcs:firstrow(Column#445)->Column#360, funcs:firstrow(Column#446)->Column#364, funcs:firstrow(Column#447)->z_qianyi.shop.shop_id, funcs:count(1)->Column#441 | 1.85 MB | N/A |
| └─Projection_191 | 46.56 | 4979 | root | | time:5.93s, loops:6, Concurrency:OFF | Column#358, Column#359, Column#360, Column#364, z_qianyi.shop.shop_id, date(Column#364)->Column#448, Column#358, Column#359, Column#360, z_qianyi.shop.shop_id | 264.8 KB | N/A |
| └─Projection_156 | 46.56 | 4979 | root | | time:5.93s, loops:6, Concurrency:OFF | Column#358, Column#359, Column#360, Column#364, z_qianyi.shop.shop_id | 264.8 KB | N/A |
| └─HashJoin_158 | 46.56 | 4979 | root | | time:5.93s, loops:6, build_hash_table:{total:705.1ms, fetch:699.1ms, build:5.99ms}, probe:{concurrency:5, total:29.7s, max:5.93s, probe:6.28ms, fetch:29.7s} | inner join, equal:[eq(z_qianyi.clerk.clerk_id, Column#362)] | 1.97 MB | 0 Bytes |
| ├─Selection_159(Build) | 46.56 | 19115 | root | | time:700.1ms, loops:20 | not(isnull(z_qianyi.clerk.clerk_id)) | 33.0 KB | N/A |
| │ └─CTEFullScan_160 | 58.20 | 19115 | root | CTE:t_clerk AS tc | time:697.7ms, loops:23 | data:CTE_1 | 1.38 MB | 0 Bytes |
| └─Selection_161(Probe) | 46.56 | 4979 | root | | time:5.93s, loops:6 | eq(Column#373, 2), not(isnull(Column#362)) | 64.9 KB | N/A |
| └─HashAgg_162 | 58.20 | 5010 | root | | time:5.93s, loops:8, partial_worker:{wall_time:5.927382368s, concurrency:5, task_num:5, tot_wait:29.617825007s, tot_exec:11.163989ms, tot_time:29.630244858s, max:5.927347297s, p95:5.927347297s}, final_worker:{wall_time:5.929723838s, concurrency:5, task_num:25, tot_wait:29.630784773s, tot_exec:16.190393ms, tot_time:29.646989024s, max:5.929698931s, p95:5.929698931s} | group by:z_qianyi.win_ticket.draw_id, z_qianyi.win_ticket.ticket_no, funcs:max(z_qianyi.shop.rdc_id)->Column#358, funcs:max(z_qianyi.game_draw.game_id)->Column#359, funcs:max(z_qianyi.game_draw.draw_no)->Column#360, funcs:max(z_qianyi.win_ticket.paid_operator_id)->Column#362, funcs:max(z_qianyi.win_ticket.paid_time)->Column#364, funcs:max(z_qianyi.win_ticket.paid_type)->Column#373 | 4.41 MB | N/A |
| └─Projection_164 | 72.75 | 5011 | root | | time:5.92s, loops:6, Concurrency:OFF | z_qianyi.win_ticket.draw_id, z_qianyi.win_ticket.ticket_no, z_qianyi.win_ticket.paid_type, z_qianyi.win_ticket.paid_time, z_qianyi.win_ticket.paid_operator_id, z_qianyi.game_draw.draw_no, z_qianyi.game_draw.game_id, z_qianyi.shop.rdc_id | 551.5 KB | N/A |
| └─HashJoin_166 | 72.75 | 5011 | root | | time:5.92s, loops:6, build_hash_table:{total:5.92s, fetch:5.92s, build:728.3µs}, probe:{concurrency:5, total:29.6s, max:5.92s, probe:5.52ms, fetch:29.6s} | inner join, equal:[eq(z_qianyi.win_ticket.draw_id, z_qianyi.game_draw.draw_id)] | 710.8 KB | 0 Bytes |
| ├─HashJoin_168(Build) | 58.20 | 5011 | root | | time:5.92s, loops:9, build_hash_table:{total:705.9ms, fetch:698.8ms, build:7.1ms}, probe:{concurrency:5, total:29.6s, max:5.92s, probe:22.1ms, fetch:29.6s} | inner join, equal:[eq(z_qianyi.clerk.clerk_id, z_qianyi.win_ticket.clerk_id)] | 1.97 MB | 0 Bytes |
| │ ├─Selection_169(Build) | 46.56 | 19115 | root | | time:700.5ms, loops:20 | not(isnull(z_qianyi.clerk.clerk_id)) | 33.0 KB | N/A |
| │ │ └─CTEFullScan_170 | 58.20 | 19115 | root | CTE:t_clerk AS tc | time:698.3ms, loops:23 | data:CTE_1 | N/A | N/A |
| │ └─TableReader_173(Probe) | 600211.49 | 46530 | root | | time:5.92s, loops:47, cop_task: {num: 136, max: 2.08s, min: 3.8ms, avg: 630.9ms, p95: 1.7s, max_proc_keys: 343977, p95_proc_keys: 325021, tot_proc: 51s, tot_wait: 133.7ms, rpc_num: 136, rpc_time: 1m25.8s, copr_cache_hit_ratio: 0.00, build_task_duration: 35.2ms, max_distsql_concurrency: 15} | data:Selection_172 | 904.8 KB | N/A |
| │ └─Selection_172 | 600211.49 | 46530 | cop[tikv] | | tikv_task:{proc max:2.08s, min:0s, avg: 628.5ms, p80:964ms, p95:1.7s, iters:24073, tasks:136}, scan_detail: {total_process_keys: 24032492, total_process_keys_size: 8512170481, total_keys: 24032628, get_snapshot_time: 106.8ms, rocksdb: {key_skipped_count: 45986364, block: {cache_hit_count: 119723, read_count: 180755, read_byte: 2.07 GB, read_time: 1.21s}}} | ge(z_qianyi.win_ticket.paid_time, 2023-08-01 00:00:00.000000), le(z_qianyi.win_ticket.paid_time, 2023-08-01 23:59:59.000000), not(isnull(z_qianyi.win_ticket.clerk_id)) | N/A | N/A |
| │ └─TableFullScan_171 | 24032492.00 | 24032492 | cop[tikv] | table:wt | tikv_task:{proc max:2.05s, min:0s, avg: 611.7ms, p80:948ms, p95:1.68s, iters:24073, tasks:136} | keep order:false, stats:pseudo | N/A | N/A |
| └─Selection_174(Probe) | 967.13 | 66 | root | | time:125.4µs, loops:2 | not(isnull(z_qianyi.game_draw.draw_id)) | 4.51 KB | N/A |
| └─CTEFullScan_175 | 1208.92 | 66 | root | CTE:t_draw AS td | time:96.9µs, loops:3 | data:CTE_0 | 9.33 KB | 0 Bytes |
| CTE_1 | 58.20 | 19115 | root | | time:698.4ms, loops:23 | Non-Recursive CTE | N/A | N/A |
| └─Projection_107(Seed Part) | 58.20 | 19115 | root | | time:696.8ms, loops:22, Concurrency:OFF | z_qianyi.clerk.clerk_id, z_qianyi.shop.rdc_id, z_qianyi.shop.rdc_id, z_qianyi.shop.shop_id | 508.8 KB | N/A |
| └─Projection_108 | 58.20 | 19115 | root | | time:696.7ms, loops:22, Concurrency:OFF | z_qianyi.clerk.clerk_id, z_qianyi.shop.shop_id, z_qianyi.shop.rdc_id | 508.8 KB | N/A |
| └─IndexHashJoin_114 | 58.20 | 19115 | root | | time:696.6ms, loops:22, inner:{total:2.01s, concurrency:5, task:7, construct:3.53ms, fetch:1.99s, build:1.5ms, join:16.9ms} | inner join, inner:IndexReader_111, outer key:z_qianyi.shop.shop_id, inner key:z_qianyi.clerk.shop_id, equal cond:eq(z_qianyi.shop.shop_id, z_qianyi.clerk.shop_id) | 1.56 MB | N/A |
| ├─TableReader_139(Build) | 46.56 | 6035 | root | | time:611.9ms, loops:13, cop_task: {num: 6, max: 604.8ms, min: 1.26ms, avg: 102.3ms, p95: 604.8ms, max_proc_keys: 24544, p95_proc_keys: 24544, tot_proc: 18.7ms, tot_wait: 6.9ms, rpc_num: 6, rpc_time: 613.7ms, copr_cache_hit_ratio: 0.83, build_task_duration: 530.4µs, max_distsql_concurrency: 1} | data:Selection_138 | 51.1 KB | N/A |
| │ └─Selection_138 | 46.56 | 6035 | cop[tikv] | | tikv_task:{proc max:1.08s, min:4ms, avg: 294.7ms, p80:600ms, p95:1.08s, iters:70, tasks:6}, scan_detail: {total_process_keys: 24544, total_process_keys_size: 2737545, total_keys: 24545, get_snapshot_time: 729.6µs, rocksdb: {key_skipped_count: 24544, block: {cache_hit_count: 100}}} | eq(z_qianyi.shop.rdc_id, 11) | N/A | N/A |
| │ └─TableFullScan_137 | 46562.00 | 46562 | cop[tikv] | table:s | tikv_task:{proc max:1.08s, min:4ms, avg: 294.7ms, p80:600ms, p95:1.08s, iters:70, tasks:6} | keep order:false, stats:pseudo | N/A | N/A |
| └─IndexReader_111(Probe) | 58.20 | 19115 | root | | time:1.98s, loops:29, cop_task: {num: 26, max: 582.7ms, min: 3.24ms, avg: 76ms, p95: 580.8ms, max_proc_keys: 3040, p95_proc_keys: 2016, tot_proc: 128.1ms, tot_wait: 22.7ms, rpc_num: 26, rpc_time: 1.98s, copr_cache_hit_ratio: 0.00, build_task_duration: 218.9µs, max_distsql_concurrency: 1} | index:IndexRangeScan_110 | 6.80 KB | N/A |
| └─IndexRangeScan_110 | 58.20 | 19115 | cop[tikv] | table:c, index:idx_clerk_shop_id(shop_id) | tikv_task:{proc max:580ms, min:0s, avg: 73.5ms, p80:20ms, p95:580ms, iters:109, tasks:26}, scan_detail: {total_process_keys: 19115, total_process_keys_size: 879290, total_keys: 25169, get_snapshot_time: 2.45ms, rocksdb: {key_skipped_count: 19115, block: {cache_hit_count: 36343}}} | range: decided by [eq(z_qianyi.clerk.shop_id, z_qianyi.shop.shop_id)], keep order:false, stats:pseudo | N/A | N/A |
| CTE_0 | 1208.92 | 66 | root | | time:197.6µs, loops:3 | Non-Recursive CTE | N/A | N/A |
| └─Projection_100(Seed Part) | 1208.92 | 66 | root | | time:134.1µs, loops:2, Concurrency:5 | z_qianyi.game_draw.draw_no, z_qianyi.game_draw.game_id, z_qianyi.game_draw.draw_id, z_qianyi.game_draw.draw_time, z_qianyi.game_draw.paid_begin_time, z_qianyi.game_draw.paid_end_time | 29.1 KB | N/A |
| └─TableReader_103 | 1208.92 | 66 | root | | time:68.3µs, loops:2, cop_task: {num: 1, max: 14.5ms, proc_keys: 10913, tot_proc: 13.4ms, tot_wait: 496.1µs, rpc_num: 1, rpc_time: 14.5ms, copr_cache_hit_ratio: 0.00, build_task_duration: 330.1µs, max_distsql_concurrency: 1} | data:Selection_102 | 4.28 KB | N/A |
| └─Selection_102 | 1208.92 | 66 | cop[tikv] | | tikv_task:{time:12ms, loops:15}, scan_detail: {total_process_keys: 10913, total_process_keys_size: 4682171, total_keys: 10914, get_snapshot_time: 473.1µs, rocksdb: {key_skipped_count: 16920, block: {cache_hit_count: 165}}} | ge(2023-08-01 00:00:00.000000, z_qianyi.game_draw.sale_begin_time), le(2023-08-01 00:00:00.000000, z_qianyi.game_draw.paid_end_time), or(1, and(1, in(z_qianyi.game_draw.game_id, 200, 210, 230, 240, 250, 260, 270))) | N/A | N/A |
| └─TableFullScan_101 | 10913.00 | 10913 | cop[tikv] | table:gd | tikv_task:{time:12ms, loops:15} | keep order:false, stats:pseudo | N/A | N/A |
+------------------------------------------------------+-------------+----------+-----------+-------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+---------+
48 rows in set (6.00 sec)
