子查询sql，oracle 2秒查出结果，tidb 30分钟没查出结果

[wenyi]

select ca.patient_id, ca.card_number
from base.T_DCH_PATIENT_CARD ca
where ca.id in (select max(aa.id) as bid
from base.T_DCH_PATIENT_CARD aa
where aa.card_type = 4
and aa.card_state = 1
and aa.patient_id = ca.patient_id
group by aa.patient_id)
and ca.hosp_id = 1517
这条sql语句，在oracle 2秒出结果，在tidb 查询很慢很慢

tidb执行计划

[xfworld (魔幻之翼)]

这张表多大的数据量呢？
结构可以分享下么？

[wenyi]

[xfworld (魔幻之翼)]

嗯，数据量太小了，可以和 mysql 对比下，会比较有参考意义

tidb 的算子和计划优化能力，肯定比不上oracle 的，所以不到一定的数据规模，体现不出来任何优势
想要优化还是有办法的：

红框的地方，没算子支持，等于是聚合到 tidb 了部分处理之后，又通过 tiflash 完成了一次全表扫描，然后在聚合

聚合了两次… 中间还有等待…

[realcp1018]

可以尝试下以下几种办法：

1. 去除sql_mode当中的only_full_group_by，然后将SQL改写为如下格式：

SELECT
	max( id ),
	patient_id,
	card_number 
FROM
	base.T_DCH_PATIENT_CARD 
WHERE
	card_type = 4 
	AND card_state = 1 
	AND hosp_id = 1517 
GROUP BY
	patient_id;

2. 默认的sql_mode格式下，将SQL改写为如下格式：

SELECT
	t.id,
	t.patient_id,
	t.card_number 
FROM
	base.T_DCH_PATIENT_CARD t 
WHERE
	t.id IN (
	SELECT
		max( id ) AS id 
	FROM
		base.T_DCH_PATIENT_CARD 
	WHERE
		card_type = 4 
		AND card_state = 1 
		AND hosp_id = 1517 
	GROUP BY
	patient_id 
	)

如果发现走tiflash更慢，先禁用tiflash再试一次。

[tidb菜鸟一只 (小菜一颗)]

你这个sql在tidb上走tiflash肯定是有问题的，建议hint指定不走tiflash试试，另外sql可以改写成这个看看
select patient_id,card_number from
(
select patient_id,card_number,rank() over (partition by patient_id order by id desc) row_num
from base.T_DCH_PATIENT_CARD ca
where ca.card_type = 4
and ca.card_state = 1
and ca.hosp_id = 1517)
where row_num=1
;

[人如其名]

贴出oracle的执行计划看看？ 只看这里tidb没有对关联子查询进行优化。看ca表过滤后结果集还有十几万，假设子查询中解关联记录数很少，那么应该是优化掉子查询效率会高不少，但是最好还是要贴出oracle的执行计划看是怎么走的。