tiflash查出数据为空

LI-ldc · 2021 年8 月 30 日 03:22

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

【概述】场景 + 问题概述
SELECT DISTINCT
d.cdate,
user_id
FROM
db_zzz_dictionary.ts_user_store a,
(
SELECT DISTINCT
from_unixtime( ctime, ‘%Y%m%d’ ) AS cdate
FROM
db_zzz_dictionary.ts_user_store a
WHERE
a.ctime >= UNIX_TIMESTAMP( ‘20210827’ )
) d
WHERE
a.ctime < UNIX_TIMESTAMP(DATE_SUB( d.cdate, INTERVAL - 1 DAY ))

在tikv可以查出结果，在tiflash查不出来，去掉“a.ctime < UNIX_TIMESTAMP(DATE_SUB( d.cdate, INTERVAL - 1 DAY )) ”，可以在tiflash得出结果。

LI-ldc · 2021 年8 月 30 日 03:25

TableReader_75	760591.15	root		data:ExchangeSender_74
└─ExchangeSender_74	760591.15	batchCop[tiflash]		ExchangeType: PassThrough
└─Projection_70	760591.15	batchCop[tiflash]		Column#31, db_zzz_dictionary.ts_user_store.user_id
└─HashAgg_71	760591.15	batchCop[tiflash]		group by:Column#31, db_zzz_dictionary.ts_user_store.user_id, funcs:firstrow(Column#31)->Column#31, funcs:firstrow(db_zzz_dictionary.ts_user_store.user_id)->db_zzz_dictionary.ts_user_store.user_id
└─ExchangeReceiver_73	760591.15	batchCop[tiflash]
└─ExchangeSender_72	760591.15	batchCop[tiflash]		ExchangeType: HashPartition, Hash Cols: Column#31, db_zzz_dictionary.ts_user_store.user_id
└─HashAgg_15	760591.15	batchCop[tiflash]		group by:Column#31, db_zzz_dictionary.ts_user_store.user_id,
└─Projection_69	15901050311.98	batchCop[tiflash]		db_zzz_dictionary.ts_user_store.user_id, Column#31
└─HashJoin_63	15901050311.98	batchCop[tiflash]		CARTESIAN inner join, other cond:lt(cast(db_zzz_dictionary.ts_user_store.ctime, decimal(20,0) BINARY), unix_timestamp(date_sub(Column#31, -1, “DAY”)))
├─ExchangeReceiver_29(Build)	12690.65	batchCop[tiflash]
│ └─ExchangeSender_28	12690.65	batchCop[tiflash]		ExchangeType: Broadcast
│ └─Projection_24	12690.65	batchCop[tiflash]		Column#31
│ └─HashAgg_25	12690.65	batchCop[tiflash]		group by:Column#34, funcs:firstrow(Column#34)->Column#31
│ └─ExchangeReceiver_27	12690.65	batchCop[tiflash]
│ └─ExchangeSender_26	12690.65	batchCop[tiflash]		ExchangeType: HashPartition, Hash Cols: Column#34
│ └─HashAgg_21	12690.65	batchCop[tiflash]		group by:Column#44,
│ └─Projection_79	12991.11	batchCop[tiflash]		from_unixtime(cast(db_zzz_dictionary.ts_user_store.ctime, decimal(20,0) BINARY), %Y%m%d)->Column#44
│ └─Selection_23	12991.11	batchCop[tiflash]		ge(db_zzz_dictionary.ts_user_store.ctime, 1629993600)
│ └─TableFullScan_22	1252974.00	batchCop[tiflash]	table:a	keep order:false
└─TableFullScan_30(Probe)	1252974.00	batchCop[tiflash]	table:a	keep order:false

LI-ldc · 2021 年8 月 30 日 03:28

HashAgg_12	760593.58	root		group by:Column#31, db_zzz_dictionary.ts_user_store.user_id, funcs:firstrow(Column#31)->Column#31, funcs:firstrow(db_zzz_dictionary.ts_user_store.user_id)->db_zzz_dictionary.ts_user_store.user_id
└─Projection_13	15901151837.31	root		db_zzz_dictionary.ts_user_store.user_id, Column#31
└─HashJoin_15	15901151837.31	root		CARTESIAN inner join, other cond:lt(cast(db_zzz_dictionary.ts_user_store.ctime, decimal(20,0) BINARY), unix_timestamp(date_sub(Column#31, -1, “DAY”)))
├─HashAgg_16(Build)	12690.69	root		group by:Column#35, funcs:firstrow(Column#34)->Column#31
│ └─Projection_25	12991.16	root		from_unixtime(cast(db_zzz_dictionary.ts_user_store.ctime, decimal(20,0) BINARY), %Y%m%d)->Column#34, from_unixtime(cast(db_zzz_dictionary.ts_user_store.ctime, decimal(20,0) BINARY), %Y%m%d)->Column#35
│ └─IndexReader_20	12991.16	root		index:IndexRangeScan_19
│ └─IndexRangeScan_19	12991.16	cop[tikv]	table:a, index:ctime_idx(ctime)	range:[1629993600,+inf], keep order:false
└─TableReader_24(Probe)	1252978.00	root		data:TableFullScan_23
└─TableFullScan_23	1252978.00	cop[tikv]	table:a	keep order:false

这道题我不会 · 2021 年8 月 30 日 06:25

1.麻烦反馈下 explain analyze + SQL 语句的真实执行计划，包括分别走 tikv 和 tiflash 两种引擎的；
2.麻烦提供下 db_zzz_dictionary.ts_user_store 表结构信息。

LI-ldc · 2021 年8 月 30 日 06:47

TIFLASH

id	estRows	actRows	task	access object	execution info	operator info	memory	disk
TableReader_75	760694.34	0	root		time:9.73s, loops:1, cop_task: {num: 1, max: 0s, proc_keys: 0, copr_cache_hit_ratio: 0.00}	data:ExchangeSender_74	N/A	N/A
└─ExchangeSender_74	760694.34	0	batchCop[tiflash]		tiflash_task:{time:9.73s, loops:0, threads:1}	ExchangeType: PassThrough	N/A	N/A
└─Projection_70	760694.34	0	batchCop[tiflash]		tiflash_task:{time:9.73s, loops:0, threads:1}	Column#31, db_zzz_dictionary.ts_user_store.user_id	N/A	N/A
└─HashAgg_71	760694.34	0	batchCop[tiflash]		tiflash_task:{time:9.73s, loops:0, threads:1}	group by:Column#31, db_zzz_dictionary.ts_user_store.user_id, funcs:firstrow(Column#31)->Column#31, funcs:firstrow(db_zzz_dictionary.ts_user_store.user_id)->db_zzz_dictionary.ts_user_store.user_id	N/A	N/A
└─ExchangeReceiver_73	760694.34	0	batchCop[tiflash]		tiflash_task:{time:9.73s, loops:0, threads:24}		N/A	N/A
└─ExchangeSender_72	760694.34	0	batchCop[tiflash]		tiflash_task:{time:9.73s, loops:1, threads:24}	ExchangeType: HashPartition, Hash Cols: Column#31, db_zzz_dictionary.ts_user_store.user_id	N/A	N/A
└─HashAgg_15	760694.34	0	batchCop[tiflash]		tiflash_task:{time:9.73s, loops:1, threads:1}	group by:Column#31, db_zzz_dictionary.ts_user_store.user_id,	N/A	N/A
└─Projection_69	15905365425	0	batchCop[tiflash]		tiflash_task:{time:9.72s, loops:1, threads:24}	db_zzz_dictionary.ts_user_store.user_id, Column#31	N/A	N/A
└─HashJoin_63	15905365425	0	batchCop[tiflash]		tiflash_task:{time:9.72s, loops:1, threads:24}	CARTESIAN inner join, other cond:lt(cast(db_zzz_dictionary.ts_user_store.ctime, decimal(20,0) BINARY), unix_timestamp(date_sub(Column#31, -1, “DAY”)))	N/A	N/A
├─ExchangeReceiver_29(Build)	12692.37	4	batchCop[tiflash]		tiflash_task:{time:9ms, loops:1, threads:24}		N/A	N/A
│ └─ExchangeSender_28	12692.37	4	batchCop[tiflash]		tiflash_task:{time:26.2ms, loops:1, threads:1}	ExchangeType: Broadcast	N/A	N/A
│ └─Projection_24	12692.37	4	batchCop[tiflash]		tiflash_task:{time:26.2ms, loops:1, threads:1}	Column#31	N/A	N/A
│ └─HashAgg_25	12692.37	4	batchCop[tiflash]		tiflash_task:{time:26.2ms, loops:1, threads:1}	group by:Column#34, funcs:firstrow(Column#34)->Column#31	N/A	N/A
│ └─ExchangeReceiver_27	12692.37	4	batchCop[tiflash]		tiflash_task:{time:24.2ms, loops:1, threads:24}		N/A	N/A
│ └─ExchangeSender_26	12692.37	4	batchCop[tiflash]		tiflash_task:{time:24.4ms, loops:1, threads:2}	ExchangeType: HashPartition, Hash Cols: Column#34	N/A	N/A
│ └─HashAgg_21	12692.37	4	batchCop[tiflash]		tiflash_task:{time:24.4ms, loops:1, threads:1}	group by:Column#44,	N/A	N/A
│ └─Projection_79	12992.88	2017	batchCop[tiflash]		tiflash_task:{time:24.4ms, loops:1, threads:2}	from_unixtime(cast(db_zzz_dictionary.ts_user_store.ctime, decimal(20,0) BINARY), %Y%m%d)->Column#44	N/A	N/A
│ └─Selection_23	12992.88	2017	batchCop[tiflash]		tiflash_task:{time:24.4ms, loops:1, threads:2}	ge(db_zzz_dictionary.ts_user_store.ctime, 1629993600)	N/A	N/A
│ └─TableFullScan_22	1253144	20737	batchCop[tiflash]	table:a	tiflash_task:{time:24.4ms, loops:2, threads:2}	keep order:false	N/A	N/A
└─TableFullScan_30(Probe)	1253144	1253143	batchCop[tiflash]	table:a	tiflash_task:{time:68ms, loops:21, threads:2}	keep order:false	N/A	N/A

LI-ldc · 2021 年8 月 30 日 06:47

TIKV

id	estRows	actRows	task	access object	execution info	operator info	memory	disk
HashAgg_12	760699.81	3038916	root		time:9.44s, loops:2972, partial_worker:{wall_time:6.203693058s, concurrency:5, task_num:4894, tot_wait:24.849155526s, tot_exec:5.167112317s, tot_time:30.930780377s, max:6.203636734s, p95:6.203636734s}, final_worker:{wall_time:9.441363243s, concurrency:5, task_num:25, tot_wait:30.885566765s, tot_exec:15.898509154s, tot_time:46.784092739s, max:9.441319168s, p95:9.441319168s}	group by:Column#31, db_zzz_dictionary.ts_user_store.user_id, funcs:firstrow(Column#31)->Column#31, funcs:firstrow(db_zzz_dictionary.ts_user_store.user_id)->db_zzz_dictionary.ts_user_store.user_id	1.01 GB	N/A
└─Projection_17	15905593888	5010145	root		time:5.96s, loops:4895, Concurrency:5	db_zzz_dictionary.ts_user_store.user_id, Column#31	284.6 KB	N/A
└─HashJoin_20	15905593888	5010145	root		time:5.97s, loops:4895, build_hash_table:{total:3.08ms, fetch:3.07ms, build:8.71µs}, probe:{concurrency:5, total:30s, max:6s, probe:28.5s, fetch:1.53s}	CARTESIAN inner join, other cond:lt(cast(db_zzz_dictionary.ts_user_store.ctime, decimal(20,0) BINARY), unix_timestamp(date_sub(Column#31, -1, “DAY”)))	1.84 KB	0 Bytes
├─HashAgg_30(Build)	12692.46	4	root		time:2.97ms, loops:4, partial_worker:{wall_time:2.947506ms, concurrency:5, task_num:3, tot_wait:13.49967ms, tot_exec:397.664µs, tot_time:13.911931ms, max:2.883885ms, p95:2.883885ms}, final_worker:{wall_time:2.977182ms, concurrency:5, task_num:5, tot_wait:14.600919ms, tot_exec:69.86µs, tot_time:14.673486ms, max:2.948859ms, p95:2.948859ms}	group by:Column#41, funcs:firstrow(Column#40)->Column#31	103.8 KB	N/A
│ └─Projection_60	12992.97	2031	root		time:2.69ms, loops:4, Concurrency:5	from_unixtime(cast(db_zzz_dictionary.ts_user_store.ctime, decimal(20,0) BINARY), %Y%m%d)->Column#40, from_unixtime(cast(db_zzz_dictionary.ts_user_store.ctime, decimal(20,0) BINARY), %Y%m%d)->Column#41	54.6 KB	N/A
│ └─IndexReader_38	12992.97	2031	root		time:1.22ms, loops:4, cop_task: {num: 1, max: 1.46ms, proc_keys: 2031, tot_proc: 1ms, rpc_num: 1, rpc_time: 1.44ms, copr_cache_hit_ratio: 0.00}	index:IndexRangeScan_37	16.2 KB	N/A
│ └─IndexRangeScan_37	12992.97	2031	cop[tikv]	table:a, index:ctime_idx(ctime)	tikv_task:{time:1ms, loops:7}, scan_detail: {total_process_keys: 2031, total_keys: 2334, rocksdb: {delete_skipped_count: 1, key_skipped_count: 2332, block: {cache_hit_count: 15, read_count: 1, read_byte: 63.9 KB}}}	range:[1629993600,+inf], keep order:false	N/A	N/A
└─TableReader_54(Probe)	1253153	1253157	root		time:309.3ms, loops:1226, cop_task: {num: 2, max: 458.7ms, min: 300.7ms, avg: 379.7ms, p95: 458.7ms, max_proc_keys: 737646, p95_proc_keys: 737646, tot_proc: 720ms, rpc_num: 2, rpc_time: 759.3ms, copr_cache_hit_ratio: 0.00}	data:TableFullScan_53	19.2 MB	N/A
└─TableFullScan_53	1253153	1253157	cop[tikv]	table:a	tikv_task:{proc max:395ms, min:259ms, p80:395ms, p95:395ms, iters:1233, tasks:2}, scan_detail: {total_process_keys: 1253157, total_keys: 1370486, rocksdb: {delete_skipped_count: 9, key_skipped_count: 1370447, block: {cache_hit_count: 2508, read_count: 0, read_byte: 0 Bytes}}}	keep order:false	N/A	N/A

LI-ldc · 2021 年8 月 30 日 06:53

CREATE TABLE `ts_user_store` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`user_id` int(11) NOT NULL DEFAULT '0' ,
`store_id` int(11) NOT NULL DEFAULT '0' ,
`position` tinyint(1) DEFAULT '0' ,
`status` tinyint(4) DEFAULT NULL ,
`is_restrict` tinyint(1) NOT NULL DEFAULT '1' ,
`con_status` tinyint(4) NOT NULL DEFAULT '0' ,
`con_time` int(11) NOT NULL DEFAULT '0',
`logo` varchar(255) DEFAULT NULL ,
`apply_time` int(11) NOT NULL DEFAULT '0',
`entry_time` int(11) NOT NULL DEFAULT '0',
`leave_time` int(11) NOT NULL DEFAULT '0',
`buy_car_type` tinyint(1) NOT NULL DEFAULT '0',
`ctime` int(11) NOT NULL DEFAULT '0',
`mtime` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY `user_store_uk` (`user_id`,`store_id`),
KEY `store_idx` (`store__id`),
KEY `ctime_idx` (`ctime`),
KEY `mtime` (`mtime`),
KEY `idx_con_status` (`con_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=2456487;

这道题我不会 · 2021 年8 月 30 日 07:05

收到，我们这边分析下。

这道题我不会 · 2021 年8 月 30 日 07:45

目前分析下来应该是已知问题，详见：https://github.com/pingcap/tidb/issues/27336#issuecomment-901012497
预计会在下一个小版本中会修复这个问题；
临时解决方法：在 date_sub 的第一个参数上套一层 cast as date 。

system · 2022 年10 月 31 日 19:15

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。