tiflash查出数据为空

为提高效率,请提供以下信息,问题描述清晰能够更快得到解决:
【 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得出结果。

1赞
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
2赞
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
2赞

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

2赞

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
2赞

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
2赞
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;
2赞

收到,我们这边分析下。

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