TIDB 查询慢以及返回NULL

TiDBer_PT · 2024 年9 月 3 日 11:24

【 TiDB 使用环境】生产环境 /测试/ Poc
【 TiDB 版本】
【复现路径】做过哪些操作出现的问题
【遇到的问题：问题现象及影响】
【资源配置】进入到 TiDB Dashboard -集群信息 (Cluster Info) -主机(Hosts) 截图此页面
【附件：截图/日志/监控】

表结构：

CREATE TABLE `articles_of_video_day` (
  `multiMediaType` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `os` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `clicks_of_today` int(4) NOT NULL,
  `all_clicks` int(4) NOT NULL,
  `ts` int(4) NOT NULL,
  `appVersion` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sourceType` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sourceValue` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `applang` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  KEY `ts_index` (`ts`),
  KEY `idx_os` (`os`),
  KEY `idx_m` (`multiMediaType`(50))
) ENGINE=InnoDB

查询语句：

select  sum(`clicks_of_today`)  as `c59654b6-6dfc-4a62-83e7-ff471c6b16f9`   from `articles_of_video_day` 
where  (  from_unixtime( if(`ts` >9999999999, `ts`/1000, `ts`), '%Y-%m-%d %H:%i:%s')  
between  
str_to_date('2024-08-03 00:00:00' , '%Y-%m-%d %H:%i:%s')  
and  
str_to_date('2024-09-01 23:59:59' , '%Y-%m-%d %H:%i:%s')  )  
and (  `os` in  ( 'Android' )  );

tidb 返回：

+--------------------------------------+
| c59654b6-6dfc-4a62-83e7-ff471c6b16f9 |
+--------------------------------------+
|                                 NULL |
+--------------------------------------+
1 row in set, 65535 warnings (2 min 33.08 sec)

warning 显示：

| Warning | 1690 | DECIMAL value is out of range in '(9, 4)' |

mysql 返回：

+--------------------------------------+
| c59654b6-6dfc-4a62-83e7-ff471c6b16f9 |
+--------------------------------------+
| 180                                  |
+--------------------------------------+
1 row in set (31.34 sec)

我看tidb是支持 from_unixtime 和 if 的，但是两者结合，好像就不行了，请求大家帮忙解惑，多谢。。

大家不要关注烂SQL，只关注为啥返回NULL就行了！！！我知道SQL很烂！！！！

Miracle · 2024 年9 月 3 日 12:31

你应该把你的时间条件转换成ts，而不是把ts转换成datetime来比较。

TiDBer_xTvoCh2f · 2024 年9 月 3 日 12:34

ERROR 1105 (HY000): runtime error: index out of range [6] with length 1
也遇到一个only_full_group_by的问题…

小龙虾爱大龙虾 · 2024 年9 月 3 日 12:56

那一堆 warning 都是啥呢？另外把数据搞成很少几行看看喽

koby · 2024 年9 月 4 日 00:20

表示还看不懂啊，观摩观摩。

TiDBer_PT · 2024 年9 月 4 日 01:44

*************************** 1. row ***************************
 multiMediaType: tidb 是最牛的数据库
             os: Android
clicks_of_today: 0
     all_clicks: 38
             ts: 1546272000
     appVersion: 3.0.0
     sourceType:
    sourceValue:
        applang: CN
*************************** 2. row ***************************
 multiMediaType: 所有的数据库都不如tidb  
             os: iOS
clicks_of_today: 0
     all_clicks: 3
             ts: 1546272000
     appVersion: 3.0.0
     sourceType:
    sourceValue:
        applang: CN
2 rows in set (0.01 sec)

数据是类似这样的

db_user · 2024 年9 月 4 日 01:58

有没有可能你给出的数据就没有符合条件的，所以为null了，至少从你现在给出的数据计算的时间为2019-01-01 00:00:00，不在你下面的时间范围呢，你确定你表里有满足这个查询条件的行数据

TiDBer_PT · 2024 年9 月 4 日 02:01

不是的，给出的是最早的两条数据，有昨天的数据，我把 if 去掉就可以正常返回

db_user · 2024 年9 月 4 日 02:03

给几条合适的数据，warning的报错是因为你的这个值9999999999超出了范围

TiDBer_PT · 2024 年9 月 4 日 02:05

*************************** 1. row ***************************
 multiMediaType:  ssssssss
             os: iOS
clicks_of_today: 0
     all_clicks: 3
             ts: 1725292800
     appVersion: 3.0.0
     sourceType:
    sourceValue:
        applang: CN
*************************** 2. row ***************************
 multiMediaType: yyyyyyyyyy
             os: Android
clicks_of_today: 0
     all_clicks: 4
             ts: 1725292800
     appVersion: 3.1.0
     sourceType:
    sourceValue:
        applang: CN
2 rows in set (0.01 sec)

上面是最近的数据。没有ts大于9999999999的记录

db_user · 2024 年9 月 4 日 02:23

不是说> 9999999999,你看warning信息，| Warning | 1690 | DECIMAL value is out of range in ‘(9, 4)’ |，这里是把ts当成了一个decimal(9,4)的值，而9999999999有10位，所以超出了decimal(9,4)的范围，但是为什么会转换成decimal的比较呢，关键点就在于你if给的其中一个结果(ts/1000），这是一个decimal的值，所以我这里做了一下处理，换成了 cast(ts/1000 as signed)，这样查询就正常了，可以试下，不过这个应该算个bug,稍等我验证下mysql中的现象

select sum(clicks_of_today) as c59654b6-6dfc-4a62-83e7-ff471c6b16f9 from articles_of_video_day where ( from_unixtime( if(ts >9999999999, cast(ts/1000 as signed), ts), ‘%Y-%m-%d %H:%i:%s’) between str_to_date(‘2024-08-03 00:00:00’ , ‘%Y-%m-%d %H:%i:%s’) and str_to_date(‘2024-09-04 23:59:59’ , ‘%Y-%m-%d %H:%i:%s’) );

TiDBer_PT · 2024 年9 月 4 日 02:43

select sum(clicks_of_today) as `c59654b6-6dfc-4a62-83e7-ff471c6b16f9` from articles_of_video_day where ( from_unixtime( if(ts >9999999999, cast(ts/1000 as signed), ts), '%Y-%m-%d %H:%i:%s') between str_to_date('2024-08-03 00:00:00' , '%Y-%m-%d %H:%i:%s') and str_to_date('2024-09-04 23:59:59' , '%Y-%m-%d %H:%i:%s') );
+--------------------------------------+
| c59654b6-6dfc-4a62-83e7-ff471c6b16f9 |
+--------------------------------------+
|                                  279 |
+--------------------------------------+
1 row in set (1 min 16.54 sec)

这个倒是返回结果了。

db_user · 2024 年9 月 4 日 02:45

嗯么，应该算是一个bug,我提个issue吧，暂时先用这种方式绕过吧

TiDBer_PT · 2024 年9 月 4 日 02:47

主要是我们这程序不再开发了，要改这个sql太麻烦了，想看看能不能在不改动sql语句的情况下给出结果

db_user · 2024 年9 月 4 日 02:50

也可以改成非int类型的字段。比如alter table articles_of_video_day modify ts decimal(20,0) NOT NULL; 或者alter table articles_of_video_day modify ts varchar(20) NOT NULL;但是这样有些索引可能就失效了，慎重尝试

TiDBer_PT · 2024 年9 月 4 日 02:55

真服了这些开发工程师们，写完程序也不看看查询时间。最近想把MySQL数据库迁移到tidb里面，有好多不兼容的。而且MySQL那边还有没有主键的，没有索引的。头都快炸了。就比如上面这个表，没有主键，添加个主键的话，还得改程序(程序写入数据库的时候没有指定字段名)。tidb里面如果没有主键，是不是会有问题？

db_user · 2024 年9 月 4 日 02:58

会有问题，而且没有主键的话，同步也有问题，这种烂表结构在那种类型的数据库都会疯，除非业务量特别特别小

TiDBer_PT · 2024 年9 月 4 日 03:00

多谢解答了，还有个问题请教一下，是否建议添加资源管控？如何分配RU呢？另外对压测TIDB有啥建议，我使用tiup bench压测了一下，但不知道观察哪些关键指标。

db_user · 2024 年9 月 4 日 03:04

资源管控的话得看具体的业务需求了，如果都是些小业务这种，合用一个大集群还是比较适合的，RU具体分配得看具体业务的tps,qps来判断，压测的话最好还是业务压,bench只能压一个大体的情况，主要观测的是overview监控面板，和tikv detail监控面板，观测项有duration的几个相关值，落盘延迟这些

system · 2024 年9 月 11 日 03:05

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