一个join条件造成的1000倍的性能差距

TiDBer_O9yNdyY3 · 2024 年8 月 16 日 02:33

【 TiDB 使用环境】生产环境
【 TiDB 版本】V8.1
【复现路径】无
【遇到的问题：问题现象及影响】我们想把数据库从mysql移到tidb，同样的sql和索引，tidb很稳定，总是1.4S，mysql才200ms。看执行计划是left join 慢，tidb有什么特殊吗？
【资源配置】

【附件：截图/日志/监控】

TiDBer_O9yNdyY3 · 2024 年8 月 16 日 02:48

slq部分
LEFT JOIN biz_patient_med_mapping mm on p.ID=mm.PATIENT_ID and mm.ENABLE=1 and mm.IS_DEFAULT = 1
LEFT JOIN biz_inpatient_mapping im on p.ID=im.PATIENT_ID and im.ENABLE=1
LEFT JOIN dict_private_data dp on dp.HOSP_ID=mm.HOSPITAL_ID and dp.PRIVATE_ID = mm.MARK_TYPE and dp.TYPE=‘MARK_TYPE’
发现了 left join dict_private_data 这张表特别慢，但是这张表才32条记录。

TiDBer_O9yNdyY3 · 2024 年8 月 16 日 02:56

这个导致的慢，但是在mysql不会导致慢，不清楚tidb怎么搜素的

caiyfc · 2024 年8 月 16 日 03:07

先收集这几张表的统计信息，可能是统计信息不健康导致执行计划错误。直接analyze table table-name就行了。然后执行sql看看情况，还是比较慢的话，就把完整的执行计划贴出来看看。
根据上面的执行计划，感觉是应该走index join的，但是走成了hashjoin，导致扫了过多的key，所以很慢

TiDBer_O9yNdyY3 · 2024 年8 月 16 日 03:10

我已经执行了analyze，还是慢。执行计划比较糊，因为从堡垒机里截图的。

有猫万事足 · 2024 年8 月 16 日 03:12

最好复制一个文字的。这个看着很难受。图也有点糊。

caiyfc · 2024 年8 月 16 日 03:14

根据这个执行计划看，确实是因为走hashjoin导致的。

这里全表扫描了，然后取了400多万的数据到tidb节点，所以耗时很高。先加hint试试强行走indexjoin，hint写 inl_join(mm)

TiDBer_O9yNdyY3 · 2024 年8 月 16 日 03:21

我这前面的条件应该限制mm这张表的条数了，为啥还对mm全表扫描？

caiyfc · 2024 年8 月 16 日 03:22

因为走了hashjoin，而且你的过滤条件，过滤性很差，470万的数据只过滤掉了二三十万数据

TiDBer_O9yNdyY3 · 2024 年8 月 16 日 03:24

select

/*+ INL_JOIN(mm) */

加了也不行

caiyfc · 2024 年8 月 16 日 03:27

加了hint，执行计划没变的话看看这里的内容

然后检查一下关联条件有没有索引，关联条件的字段类型是否一致

TiDBer_O9yNdyY3 · 2024 年8 月 16 日 06:03

slq部分
LEFT JOIN biz_patient_med_mapping mm on p.ID=mm.PATIENT_ID and mm.ENABLE =1 and mm.IS_DEFAULT = 1
LEFT JOIN biz_inpatient_mapping im on p.ID=im.PATIENT_ID and im.ENABLE =1
LEFT JOIN dict_private_data dp on dp.HOSP_ID=mm.HOSPITAL_ID and dp.PRIVATE_ID = mm.MARK_TYPE and dp.TYPE=‘MARK_TYPE’
不加这个 dp.TYPE=‘MARK_TYPE’就特别快，dp一个32条记录小表，加上 dp.TYPE=‘MARK_TYPE’，就会造成全表扫面，是不是tidb自己的bug？

tidb菜鸟一只 · 2024 年8 月 16 日 06:13

加不加dp.TYPE=‘MARK_TYPE’这个条件的执行计划怎么变化的？

caiyfc · 2024 年8 月 16 日 06:34

不是bug，感觉像是优化器的锅，哈哈哈。
建议你先看看，去掉 dp.TYPE=‘MARK_TYPE’ 之后的执行计划，是不是走的indexjoin，然后加hint先把原sql优化了。最后等大佬来看看，这个问题是优化器的锅还是统计信息不正确的问题

TiDBer_O9yNdyY3 · 2024 年8 月 16 日 06:41

40efd8ae0c7c3476d86ae946708b59c1b0807ecc80b03585f66485f84b1d9400.txt (117.4 KB)
这是不加的执行计划
c336373cb476bb010799db235629eec08e52702eabb6fc9fafd5b2dee2350139.txt (101.1 KB)
下面是加上的执行计划

TiDBer_O9yNdyY3 · 2024 年8 月 16 日 06:42

不加就是rowidscan了，加了就是全表扫描。请帮忙看看上面的回复，是两个的执行计划

xiaohaozifeifeifei · 2024 年8 月 16 日 06:55

我也感觉是优化器的问题，但我不会优化

TiDBer_O9yNdyY3 · 2024 年8 月 16 日 07:05

求助，继续求助！

有猫万事足 · 2024 年8 月 16 日 07:19

/*+ LEADING(p, im，mm，dp) */

https://docs.pingcap.com/zh/tidb/stable/optimizer-hints#leadingt1_name--tl_name-
这样加hint试试看？因为你后面这个比较好的执行计划就是这个连接顺序。照搬过来，强行指定一下看看是否会好。

tidb菜鸟一只 · 2024 年8 月 16 日 07:36

用hint屏蔽掉dp上的索引试一下/*+ IGNORE_INDEX(dp,HOSP_ID_PRIVATE_ID_TYPE) */，这个带上索引导致你的join顺序变了，或者像猫猫说的那样直接指定join顺序