select查询语句不返回结果，耗完内存

为提高效率，请提供以下信息，问题描述清晰能够更快得到解决：

【TiDB 版本】

V5.0.0

【问题描述】

SELECT
*
FROM
TCS_ORDER_ASSET_TRAN_RESPONSION oatr
WHERE
oatr.ORDER_ASSET_TRAN_ID = (
SELECT
oat.id
FROM
TCS_ORDER_ASSET_TRAN oat
WHERE
oat.ORDER_ID = ‘xxxx’
)
AND oatr.id = (
SELECT
max(t0.id)
FROM
TCS_ORDER_ASSET_TRAN_RESPONSION t0
WHERE
t0.ORDER_ASSET_TRAN_ID = oatr.ORDER_ASSET_TRAN_ID
);

image930×307 11.8 KB

image1267×400 22.2 KB

表结构如下：

CREATE TABLE tcs_order_asset_tran_responsion (
id bigint(12) NOT NULL AUTO_INCREMENT ,
order_asset_tran_id bigint(12) NOT NULL ,
seqno int(4) NOT NULL,
amount decimal(16,0) DEFAULT NULL ,
currency_code varchar(10) DEFAULT NULL ,
quty decimal(16,0) DEFAULT NULL ,
quty_scale int(5) DEFAULT NULL ,
status varchar(4) NOT NULL,
remark varchar(1024) DEFAULT NULL ,
create_time datetime NOT NULL ,
PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */,
KEY idx_tcs_order_asset_tran_responsion_roi (order_asset_tran_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=2087469 ;

CREATE TABLE tcs_order_asset_tran (
id bigint(12) NOT NULL AUTO_INCREMENT ,
order_id varchar(32) NOT NULL ,
ref_order_id varchar(32) DEFAULT NULL ,
amount decimal(16,0) DEFAULT NULL ,
currency_code varchar(10) DEFAULT NULL ,
quty decimal(16,0) DEFAULT NULL ,
quty_scale int(5) DEFAULT NULL ,
latest_status varchar(4) DEFAULT NULL ,
trans_code varchar(3) DEFAULT NULL ,
sub_trans_code varchar(6) DEFAULT NULL ,
create_time datetime NOT NULL ,
modify_time datetime NOT NULL,
PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */,
KEY idx_tcs_order_asset_tran_roi (order_id),
KEY idx_create_time (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=12115943 ;

执行计划：

image1404×389 29.5 KB

你好，slow query 页面右上角可以导出完整的 slow query 信息以帮助排查，如有敏感字段可以脱敏处理。

slowquery_0514165508_0514170100_853306841.csv (17.7 KB)

导出的慢sql信息

补充一下，同样的 sql 在 mysql 里返回的很快（mysql数据库内的表数据量更多），执行计划没问题如下：

image999×299 20.1 KB

1. 先确认下 SQL 相关的表统计信息健康度是否是 100 的,如果不是 100 的话,通过 analyze table 收集一下统计信息之后再执行 SQL 看下，提供一下统计信息健康度为 100 的情况下的 执行计划情况
   https://docs.pingcap.com/zh/tidb/stable/statistics#表的健康度信息

2. 可以导出一下统计信息，方便在本地做验证: https://docs.pingcap.com/zh/tidb/stable/statistics#统计信息的导入导出

1. 统计信息：

image849×189 6.29 KB

image716×171 5.45 KB

表的 healthy 均为 100.

执行计划如下：

完全healthy后执行计划.xlsx (10.4 KB)

2. 两张表的统计信息如下：

tcs_order_asset_tran.html (2.5 MB)

tcs_order_asset_tran_responsion.html (1.4 MB)

1. 是否方便也反馈下 MySQL 的执行计划，多谢。
2. 之前反馈的是 explain 的执行计划吧？ 能否反馈 explain analyze sql 的执行计划，多谢。

1. mysql执行计划如下：
   

   

   image1120×293 20.7 KB

2. explain analyze 一直在执行中，无法返回结果：

image958×432 25.4 KB

image1411×158 13.7 KB

执行 explain analyze 后，这个节点的cpu使用率飙升：

image892×207 18.5 KB

截止目前，该sql已执行550秒，还未返回结果。

请问这个 sql 是需要 TCS_ORDER_ASSET_TRAN_RESPONSION 表中 ORDER_ASSET_TRAN_ID 分组后的 最大 id 吗？ 能否使用 ROW_NUMBER() OVER (PARTITION 的方式改写试试？

类似如下sql，可以在测试环境先试下：
SELECT * FROM
(SELECT ORDER_ASSET_TRAN_ID, id, ROW_NUMBER() OVER (PARTITION BY ORDER_ASSET_TRAN_ID ORDER BY id DESC) as row_index )
WHERE(row_index = 1) FROM TCS_ORDER_ASSET_TRAN_RESPONSION

因为这条 sql 在产线 mysql 库跑了很久了，没什么问题，暂时不考虑通过窗口函数改下sql。
现在主要担心是 TiDB 优化器这块有问题，没有给出最优的执行计划，想看看官方除改写 sql 外有没有更好的建议。

1. 麻烦在执行查询时，每隔一会，执行一次。多采集几个 profile 信息。 注意要修改 profile名称，防止重复。
   curl -G http://{TiDBIP}:10080/debug/zip?seconds=30" > profile.zip
2. 我们先查看 profile 信息，另外是否有可能给一部分数据来复现这个问题，方便模拟查看呢？

这次跑了600多秒，没出结果，kill 掉了，期间打了4个 profile 文件：

profile_1.zip (579.5 KB) profile_2.zip (580.8 KB) profile_3.zip (579.3 KB) profile_4.zip (582.8 KB)

在93节点的 tidb 上跑的时候占用内存主键增加，cpu基本用满：

image1001×177 5.41 KB

你们可以先看下 profile，如果需要全量数据的话，我们这边可以提供。

多谢，我们先分析下，有进展会尽快同步

好的，等你们消息。

辛苦了，谢谢！

1. 您好，这里的问题应该是优化器的问题 ，导致有些条件没有下推。已经有 PR https://github.com/pingcap/tidb/issues/24853 可以关注下 PR ，多谢。
   2 目前可以改写 sql 吗？
   EXPLAIN SELECT*
   FROM (
   SELECT*
   FROM TCS_ORDER_ASSET_TRAN_RESPONSION oatr
   WHERE oatr.ORDER_ASSET_TRAN_ID = (
   SELECT oat.id
   FROM TCS_ORDER_ASSET_TRAN oat
   WHERE oat.ORDER_ID =‘xxxx’
   )) oatr,
   (
   SELECT MAX(t0.id) as id
   FROM TCS_ORDER_ASSET_TRAN_RESPONSION t0
   WHERE t0.ORDER_ASSET_TRAN_ID = (
   SELECT oat.id
   FROM TCS_ORDER_ASSET_TRAN oat
   WHERE oat.ORDER_ID =‘xxxx’
   )) t0
   WHERE t0.id = oatr.id;

好的，多谢，暂时先不改写sql，等问题修复了再试下。