json虚拟列 做排序的时候顺序错乱 ,感觉好像是 按字符串格式排序的 原始列排序正常

为提高效率,请提供以下信息,问题描述清晰能够更快得到解决:
【 TiDB 使用环境】
tidb5.1.2

CREATE TABLE dealer_log_access_sql (
id int(11) NOT NULL AUTO_INCREMENT,
json_data_details json NOT NULL,
create_time bigint(22) NOT NULL,
req_enter_time bigint(22) NOT NULL,
**time_cost** bigint(22) GENERATED ALWAYS AS (json_unquote(json_extract(json_data_details, _utf8mb4’$.time_cost’))) VIRTUAL,
PRIMARY KEY (id,req_enter_time) /*T![clustered_index] NONCLUSTERED */,
KEY req_enter_time (req_enter_time),
KEY time_cost (time_cost)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=91560001
PARTITION BY RANGE ( req_enter_time ) (
PARTITION p20211101 VALUES LESS THAN (1636041600000),
PARTITION p20211105 VALUES LESS THAN (1636473600000),
PARTITION p20211110 VALUES LESS THAN (1636905600000),
PARTITION p20211115 VALUES LESS THAN (1637337600000),
PARTITION p20211120 VALUES LESS THAN (1637769600000),
PARTITION p20211125 VALUES LESS THAN (1638288000000),
PARTITION p20211201 VALUES LESS THAN (1638633600000),
PARTITION p20211205 VALUES LESS THAN (1639065600000),
)

【概述】 场景 + 问题概述
使用虚拟列排序不正常 : 感觉好像是 按字符串格式排序的
SELECT id,s.*,FROM_UNIXTIME(req_enter_time/1000,’%Y-%m-%d %H:%i:%S’) ,json_data_details->’$.time_cost’,json_data_details,time_cost
FROM dealer_log_access_sql s
WHERE 1=1
AND req_enter_time >=UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL 0 DAY ))*1000 AND req_enter_time <UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL -1 DAY ))*1000
ORDER BY time_cost DESC LIMIT 100

使用虚拟列排序 加上 AND time_cost>=0 排序正常 :
SELECT id,s.*,FROM_UNIXTIME(req_enter_time/1000,’%Y-%m-%d %H:%i:%S’) ,json_data_details->’$.time_cost’,json_data_details,time_cost
FROM dealer_log_access_sql s
WHERE 1=1 AND time_cost>=0
AND req_enter_time >=UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL 0 DAY ))*1000 AND req_enter_time <UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL -1 DAY ))*1000
ORDER BY time_cost DESC LIMIT 100


原始列排序正常:
SELECT FROM_UNIXTIME(req_enter_time/1000,’%Y-%m-%d %H:%i:%S’) ,json_data_details->’$.time_cost’,json_data_details
FROM dealer_log_access_sql
WHERE 1=1
AND req_enter_time >=UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL 0 DAY ))*1000 AND req_enter_time <UNIX_TIMESTAMP(DATE_SUB(CURDATE(),INTERVAL -1 DAY ))*1000
ORDER BY json_data_details->’$.time_cost’ DESC LIMIT 100

【背景】 做过哪些操作

【现象】 业务和数据库现象

【问题】 当前遇到的问题

【业务影响】

【 TiDB 版本】

【附件】 相关日志及监控(https://metricstool.pingcap.com/)


若提问为性能优化、故障排查类问题,请下载脚本运行。终端输出的打印结果,请务必全选并复制粘贴上传。

您好,麻烦用 explain 看下使用虚拟列排序以及加上 AND time_cost>=0 后的查询计划。