tidb 对应时间点的数据在所在时间范围内查询不到

hello_tidb · 2022 年12 月 2 日 05:47

【 TiDB 使用环境】测试
【 TiDB 版本】v6.1.2
【遇到的问题：问题现象及影响】
我们在11月10号 2022-11-10 00:59:59.996存在一条数据时候该字段 > "2022-11-10 00:00:00.000"查询不处来，数据库的时区和本地时区均为UTC

mysql> SELECT  start_time FROM history_wti_alarm WHERE start_time>='2022-11-09 00:00:00.000'  and  alarm_id='xxx-1668041999996-FCTA-1-WTI' \G;
*************************** 1. row ***************************
start_time: 2022-11-10 00:59:59.996
1 row in set (0.04 sec)

ERROR:
No query specified

mysql> SELECT  start_time FROM history_wti_alarm WHERE start_time>='2022-11-10 00:00:00.000'  and  alarm_id='xxx-1668041999996-FCTA-1-WTI' \G;
Empty set (0.03 sec)

ERROR:
No query specified

mysql> select UNIX_TIMESTAMP("2022-11-10 00:59:59.996");
+-------------------------------------------+
| UNIX_TIMESTAMP("2022-11-10 00:59:59.996") |
+-------------------------------------------+
|                            1668041999.996 |
+-------------------------------------------+
1 row in set (0.03 sec)

mysql> select UNIX_TIMESTAMP("2022-11-10 00:00:00.000");
+-------------------------------------------+
| UNIX_TIMESTAMP("2022-11-10 00:00:00.000") |
+-------------------------------------------+
|                            1668038400.000 |
+-------------------------------------------+
1 row in set (0.03 sec)

mysql>

我们有进行start_time 的range 分区操作

CREATE TABLE `history_wti_alarm` (
  `id` bigint not null,
  `alarm_id` varchar(64) NOT NULL  COMMENT 'unique alarm id',
  `start_time` timestamp(3) NOT NULL,
  `end_time` timestamp(3) NULL DEFAULT NULL COMMENT 'Last sample timestamp',
  `
  PRIMARY KEY (`alarm_id`,`start_time`),
  UNIQUE KEY `uniq_alarm_id` (`alarm_id`,`start_time`),
  KEY `idx_start_time` (`start_time`),
  KEY `idx_wti_st` (`wti_code`,`start_time`),
  KEY `idx_end_time` (`start_time`)
) PARTITION BY RANGE(floor(UNIX_TIMESTAMP(start_time))) (
    PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-01 00:00:00')),
    PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-10 00:00:00')),
    PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-20 00:00:00')),
    PARTITION p4 VALUES LESS THAN (UNIX_TIMESTAMP('2022-01-31 00:00:00')),
    PARTITION p5 VALUES LESS THAN (UNIX_TIMESTAMP('2022-02-01 00:00:00')),
    PARTITION p6 VALUES LESS THAN (UNIX_TIMESTAMP('2022-02-10 00:00:00')),
    PARTITION p7 VALUES LESS THAN (UNIX_TIMESTAMP('2022-02-20 00:00:00')),
    PARTITION p8 VALUES LESS THAN (UNIX_TIMESTAMP('2022-02-28 00:00:00')),
    PARTITION p9 VALUES LESS THAN (UNIX_TIMESTAMP('2022-03-01 00:00:00')),
    PARTITION p10 VALUES LESS THAN (UNIX_TIMESTAMP('2022-03-10 00:00:00')),
    PARTITION p11 VALUES LESS THAN (UNIX_TIMESTAMP('2022-03-20 00:00:00')),
    PARTITION p12 VALUES LESS THAN (UNIX_TIMESTAMP('2022-03-31 00:00:00')),
    PARTITION p13 VALUES LESS THAN (UNIX_TIMESTAMP('2022-04-01 00:00:00')),
    PARTITION p14 VALUES LESS THAN (UNIX_TIMESTAMP('2022-04-10 00:00:00')),
    PARTITION p15 VALUES LESS THAN (UNIX_TIMESTAMP('2022-04-20 00:00:00')),
    PARTITION p16 VALUES LESS THAN (UNIX_TIMESTAMP('2022-04-30 00:00:00')),
    PARTITION p17 VALUES LESS THAN (UNIX_TIMESTAMP('2022-05-01 00:00:00')),
    PARTITION p18 VALUES LESS THAN (UNIX_TIMESTAMP('2022-05-10 00:00:00')),
    PARTITION p19 VALUES LESS THAN (UNIX_TIMESTAMP('2022-05-20 00:00:00')),
    PARTITION p20 VALUES LESS THAN (UNIX_TIMESTAMP('2022-05-31 00:00:00')),
    PARTITION p21 VALUES LESS THAN (UNIX_TIMESTAMP('2022-06-01 00:00:00')),
    PARTITION p22 VALUES LESS THAN (UNIX_TIMESTAMP('2022-06-10 00:00:00')),
    PARTITION p23 VALUES LESS THAN (UNIX_TIMESTAMP('2022-06-20 00:00:00')),
    PARTITION p24 VALUES LESS THAN (UNIX_TIMESTAMP('2022-06-30 00:00:00')),
    PARTITION p25 VALUES LESS THAN (UNIX_TIMESTAMP('2022-07-01 00:00:00')),
    PARTITION p26 VALUES LESS THAN (UNIX_TIMESTAMP('2022-07-10 00:00:00')),
    PARTITION p27 VALUES LESS THAN (UNIX_TIMESTAMP('2022-07-20 00:00:00')),
    PARTITION p28 VALUES LESS THAN (UNIX_TIMESTAMP('2022-07-31 00:00:00')),
    PARTITION p29 VALUES LESS THAN (UNIX_TIMESTAMP('2022-08-01 00:00:00')),
    PARTITION p30 VALUES LESS THAN (UNIX_TIMESTAMP('2022-08-10 00:00:00')),
    PARTITION p31 VALUES LESS THAN (UNIX_TIMESTAMP('2022-08-20 00:00:00')),
    PARTITION p32 VALUES LESS THAN (UNIX_TIMESTAMP('2022-08-31 00:00:00')),
    PARTITION p33 VALUES LESS THAN (UNIX_TIMESTAMP('2022-09-01 00:00:00')),
    PARTITION p34 VALUES LESS THAN (UNIX_TIMESTAMP('2022-09-10 00:00:00')),
    PARTITION p35 VALUES LESS THAN (UNIX_TIMESTAMP('2022-09-20 00:00:00')),
    PARTITION p36 VALUES LESS THAN (UNIX_TIMESTAMP('2022-09-30 00:00:00')),
    PARTITION p37 VALUES LESS THAN (UNIX_TIMESTAMP('2022-10-01 00:00:00')),
    PARTITION p38 VALUES LESS THAN (UNIX_TIMESTAMP('2022-10-10 00:00:00')),
    PARTITION p39 VALUES LESS THAN (UNIX_TIMESTAMP('2022-10-20 00:00:00')),
    PARTITION p40 VALUES LESS THAN (UNIX_TIMESTAMP('2022-10-31 00:00:00')),
    PARTITION p41 VALUES LESS THAN (UNIX_TIMESTAMP('2022-11-01 00:00:00')),  
    PARTITION p42 VALUES LESS THAN (UNIX_TIMESTAMP('2022-11-10 00:00:00')),
    PARTITION p43 VALUES LESS THAN (UNIX_TIMESTAMP('2022-11-20 00:00:00')),
    PARTITION p44 VALUES LESS THAN (UNIX_TIMESTAMP('2022-11-30 00:00:00')),
    PARTITION p45 VALUES LESS THAN (UNIX_TIMESTAMP('2022-12-01 00:00:00')),
    PARTITION p46 VALUES LESS THAN (UNIX_TIMESTAMP('2022-12-10 00:00:00')),
    PARTITION p47 VALUES LESS THAN (UNIX_TIMESTAMP('2022-12-20 00:00:00')),
    PARTITION p48 VALUES LESS THAN (UNIX_TIMESTAMP('2022-12-31 00:00:00')),
     PARTITION p49 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-01 00:00:00')),
     PARTITION p50 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-10 00:00:00')),
     PARTITION p51 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-20 00:00:00')),
     PARTITION p52 VALUES LESS THAN (UNIX_TIMESTAMP('2023-01-31 00:00:00')),
     PARTITION p53 VALUES LESS THAN (UNIX_TIMESTAMP('2023-02-01 00:00:00')),
     PARTITION p54 VALUES LESS THAN (UNIX_TIMESTAMP('2023-02-10 00:00:00')),
     PARTITION p55 VALUES LESS THAN (UNIX_TIMESTAMP('2023-02-20 00:00:00')),
     PARTITION p56 VALUES LESS THAN (UNIX_TIMESTAMP('2023-02-28 00:00:00')),
     PARTITION p57 VALUES LESS THAN (UNIX_TIMESTAMP('2023-03-01 00:00:00')),
     PARTITION p58 VALUES LESS THAN (UNIX_TIMESTAMP('2023-03-10 00:00:00')),
     PARTITION p59 VALUES LESS THAN (UNIX_TIMESTAMP('2023-03-20 00:00:00')),
     PARTITION p60 VALUES LESS THAN (UNIX_TIMESTAMP('2023-03-31 00:00:00')),
     PARTITION p61 VALUES LESS THAN (UNIX_TIMESTAMP('2023-04-01 00:00:00')),
     PARTITION p62 VALUES LESS THAN (UNIX_TIMESTAMP('2023-04-10 00:00:00')),
     PARTITION p63 VALUES LESS THAN (UNIX_TIMESTAMP('2023-04-20 00:00:00')),
     PARTITION p64 VALUES LESS THAN (UNIX_TIMESTAMP('2023-04-30 00:00:00')),
     PARTITION p65 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-01 00:00:00')),
     PARTITION p66 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-10 00:00:00')),
     PARTITION p67 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-20 00:00:00')),
     PARTITION p68 VALUES LESS THAN (UNIX_TIMESTAMP('2023-05-31 00:00:00')),
     PARTITION p69 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-01 00:00:00')),
     PARTITION p70 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-10 00:00:00')),
     PARTITION p71 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-20 00:00:00')),
     PARTITION p72 VALUES LESS THAN (UNIX_TIMESTAMP('2023-06-30 00:00:00')),
     PARTITION p73 VALUES LESS THAN (UNIX_TIMESTAMP('2023-07-01 00:00:00')),
     PARTITION p74 VALUES LESS THAN (UNIX_TIMESTAMP('2023-07-10 00:00:00')),
     PARTITION p75 VALUES LESS THAN (UNIX_TIMESTAMP('2023-07-20 00:00:00')),
     PARTITION p76 VALUES LESS THAN (UNIX_TIMESTAMP('2023-07-31 00:00:00')),
     PARTITION p77 VALUES LESS THAN (UNIX_TIMESTAMP('2023-08-01 00:00:00')),
     PARTITION p78 VALUES LESS THAN (UNIX_TIMESTAMP('2023-08-10 00:00:00')),
     PARTITION p79 VALUES LESS THAN (UNIX_TIMESTAMP('2023-08-20 00:00:00')),
     PARTITION p80 VALUES LESS THAN (UNIX_TIMESTAMP('2023-08-31 00:00:00')),
     PARTITION p81 VALUES LESS THAN (UNIX_TIMESTAMP('2023-09-01 00:00:00')),
     PARTITION p82 VALUES LESS THAN (UNIX_TIMESTAMP('2023-09-10 00:00:00')),
     PARTITION p83 VALUES LESS THAN (UNIX_TIMESTAMP('2023-09-20 00:00:00')),
     PARTITION p84 VALUES LESS THAN (UNIX_TIMESTAMP('2023-09-30 00:00:00')),
     PARTITION p85 VALUES LESS THAN (UNIX_TIMESTAMP('2023-10-01 00:00:00')),
     PARTITION p86 VALUES LESS THAN (UNIX_TIMESTAMP('2023-10-10 00:00:00')),
     PARTITION p87 VALUES LESS THAN (UNIX_TIMESTAMP('2023-10-20 00:00:00')),
     PARTITION p88 VALUES LESS THAN (UNIX_TIMESTAMP('2023-10-31 00:00:00')),
     PARTITION p90 VALUES LESS THAN (UNIX_TIMESTAMP('2023-11-01 00:00:00')),
     PARTITION p91 VALUES LESS THAN (UNIX_TIMESTAMP('2023-11-10 00:00:00')),
     PARTITION p92 VALUES LESS THAN (UNIX_TIMESTAMP('2023-11-20 00:00:00')),
     PARTITION p93 VALUES LESS THAN (UNIX_TIMESTAMP('2023-11-30 00:00:00')),
     PARTITION p94 VALUES LESS THAN (UNIX_TIMESTAMP('2023-12-01 00:00:00')),
     PARTITION p95 VALUES LESS THAN (UNIX_TIMESTAMP('2023-12-10 00:00:00')),
     PARTITION p96 VALUES LESS THAN (UNIX_TIMESTAMP('2023-12-20 00:00:00')),
     PARTITION p97 VALUES LESS THAN (UNIX_TIMESTAMP('2023-12-31 00:00:00')),
     PARTITION p1024 VALUES LESS THAN MAXVALUE
);

执行计划

mysql> EXPLAIN analyze SELECT  count(*) FROM history_wti_alarm WHERE start_time>='2022-11-10 00:00:00' and start_time<'2022-11-10 01:00:00';
+-----------------------------+-------------+---------+-----------+-----------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------+-----------+------+
| id                          | estRows     | actRows | task      | access object                                             | execution info                                                                                                                                                                                                                          | operator info                                                                           | memory    | disk |
+-----------------------------+-------------+---------+-----------+-----------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------+-----------+------+
| StreamAgg_34                | 1.00        | 1       | root      |                                                           | time:2.82ms, loops:2                                                                                                                                                                                                                    | funcs:count(Column#48)->Column#45                                                       | 388 Bytes | N/A  |
| └─IndexReader_35            | 1.00        | 0       | root      | partition:p43                                             | time:2.82ms, loops:1, cop_task: {num: 1, max: 2.78ms, proc_keys: 0, rpc_num: 1, rpc_time: 2.77ms, copr_cache_hit_ratio: 0.00}                                                                                                           | index:StreamAgg_10                                                                      | 206 Bytes | N/A  |
|   └─StreamAgg_10            | 1.00        | 0       | cop[tikv] |                                                           | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 0, total_process_keys_size: 0, total_keys: 1, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 9, read_count: 1, read_byte: 64.0 KB}}} | funcs:count(1)->Column#48                                                               | N/A       | N/A  |
|     └─IndexRangeScan_32     | 44906239.45 | 0       | cop[tikv] | table:history_wti_alarm, index:idx_start_time(start_time) | tikv_task:{time:0s, loops:1}                                                                                                                                                                                                            | range:[2022-11-10 00:00:00.000,2022-11-10 01:00:00.000), keep order:false, stats:pseudo | N/A       | N/A  |
+-----------------------------+-------------+---------+-----------+-----------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------+-----------+------+
4 rows in set (0.04 sec)

更绝的是下面这句:

mysql> SELECT  start_time FROM history_wti_alarm WHERE start_time<='2022-11-10 00:00:00.000'  and  alarm_id='xxx-1668041999996-FCTA-1-WTI' \G;
Empty set (0.03 sec)

ERROR:
No query specified

mysql> SELECT  start_time FROM history_wti_alarm WHERE start_time>='2022-11-10 00:00:00.000'  and  alarm_id='xxx-1668041999996-FCTA-1-WTI' \G;
Empty set (0.06 sec)

ERROR:
No query specified

【资源配置】
【附件：截图/日志/监控】

我是咖啡哥 · 2022 年12 月 2 日 07:31

能把你说的查询不到的异常数据insert语句提供下吗？

xfworld · 2022 年12 月 2 日 07:33

PRIMARY KEY (alarm_id,start_time),
UNIQUE KEY uniq_alarm_id (alarm_id,start_time),

这个重复了，另外
KEY idx_start_time (start_time), 会导致索引热点

不过这个和你提的问题，没啥关系

如果从 parttion 直接读取呢？是否能读到，类似这样子
SELECT * FROM employees PARTITION (p1) where …

Kongdom · 2022 年12 月 2 日 08:02

有还原过数据么？上次遇到这种问题，还是还原数据导致的数据混乱

hello_tidb · 2022 年12 月 2 日 08:08

大佬理论上应该数据落在p43分区的，实际上在p42上才能查到.
PARTITION p41 VALUES LESS THAN (UNIX_TIMESTAMP(‘2022-11-01 00:00:00’)),
PARTITION p42 VALUES LESS THAN (UNIX_TIMESTAMP(‘2022-11-10 00:00:00’)),
PARTITION p43 VALUES LESS THAN (UNIX_TIMESTAMP(‘2022-11-20 00:00:00’)),

数据时间： 2022-11-10 00:59:59.996

xfworld · 2022 年12 月 2 日 08:11

两种情况：

不分区是否有这个问题？
那如果用 DateTime，不用 TIMESTAMP 呢？

建议试试，如果DateTime 可以的话，估计是分区上有bug了…

hello_tidb · 2022 年12 月 2 日 08:12

k咖啡哥，是tidb dm同步过来的。insert （） values()

xfworld · 2022 年12 月 2 日 08:14

直接在建一套结构，
insert into 新表 select * from 这个表

hello_tidb · 2022 年12 月 2 日 08:14

数据量巨大，20亿级别希望使用分区裁剪提高查询性能
timestamp与时区关联，作为分区更合适一些，datetime拿来做过分区但是不支持分区裁剪踩坑过来的

xfworld · 2022 年12 月 2 日 08:15

那我怀疑是精度问题导致的

问题再这，你试试看
floor(UNIX_TIMESTAMP(start_time))

hello_tidb · 2022 年12 月 2 日 08:19

mysql> select floor(UNIX_TIMESTAMP(“2022-11-10 00:59:59.996”))
→ ;
±-------------------------------------------------+
| floor(UNIX_TIMESTAMP(“2022-11-10 00:59:59.996”)) |
±-------------------------------------------------+
| 1668041999 |
±-------------------------------------------------+

看起来没有问题

xfworld · 2022 年12 月 2 日 08:21

这个定义出来的时间和你给入的时间，值是不一样的…

所以错位了？

hello_tidb · 2022 年12 月 2 日 08:21

*************************** 1. row ***************************
floor(UNIX_TIMESTAMP(start_time)): 1668041999

hello_tidb · 2022 年12 月 2 日 08:22

YES，数据落错分区了。

xfworld · 2022 年12 月 2 日 08:22

然后就查不出来了呗… 坑阿

hello_tidb · 2022 年12 月 2 日 08:24

数据是通过tidb dm同步过来的。在数据对比的过程中发现这种问题不是所有的数据都这样，只是部分数据。

优点是tidb的优势分布式不需要分表，tiflash查询性能很高，缺点是目前踩坑的地方比较多，基本上每个环节都跑到这里提问了。

裤衩儿飞上天 · 2022 年12 月 2 日 08:26

我来观摩学习大佬怎么处理这种诡异问题

xfworld · 2022 年12 月 2 日 08:28

CEILING()

CEILING(UNIX_TIMESTAMP(“2022-11-10 00:59:59.996”))

试试这个…

hello_tidb · 2022 年12 月 2 日 08:30

mysql> select CEILING(UNIX_TIMESTAMP(“2022-11-10 00:59:59.996”));
±---------------------------------------------------+
| CEILING(UNIX_TIMESTAMP(“2022-11-10 00:59:59.996”)) |
±---------------------------------------------------+
| 1668042000 |
±---------------------------------------------------+

hello_tidb · 2022 年12 月 2 日 08:31

floor和ceil影响都是秒级别的，我这个数据已经在00:59:59.996了再怎么floor或者ceil也是应该落在p43