慢sql优化，相同sql在mysql上执行正常

xnwjx · 2022 年2 月 16 日 07:28

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

【TiDB 版本】 5.1.2

【问题描述】
SQL语句：
SELECT
p.STATUS,
count( p.id ) AS count
FROM
pu_order_change_suggestion p
WHERE
p.dr = 0
AND item_no IN (
SELECT
bi.item_no
FROM
iscp_iteminfo.ba_item bi
WHERE
bi.class_no IN (
SELECT
bits.class_no
FROM
iscp_iteminfo.ba_item_tech_station bits
WHERE
bits.tqe IN ( ‘10132711’, ‘10132711’, ‘10132711’, ‘10132711’ )
OR bits.tech_quality_director IN ( ‘10132711’, ‘10132711’, ‘10132711’, ‘10132711’ )
OR bits.mqa_eng IN ( ‘10132711’, ‘10132711’, ‘10132711’, ‘10132711’ )
OR bits.auth_eng IN ( ‘10132711’, ‘10132711’, ‘10132711’, ‘10132711’ )
OR bits.auth_director IN ( ‘10132711’, ‘10132711’, ‘10132711’, ‘10132711’ )
OR bits.quality_check_eng IN ( ‘10132711’, ‘10132711’, ‘10132711’, ‘10132711’ )
OR bits.category_director IN ( ‘10132711’, ‘10132711’, ‘10132711’, ‘10132711’ )
)
OR (
bi.discusser_card_no IN ( ‘10132711’, ‘10132711’, ‘10132711’, ‘10132711’ )
OR bi.buyer_card_no IN ( ‘10132711’, ‘10132711’, ‘10132711’, ‘10132711’ )
)
)
AND dr = 0
GROUP BY
p.STATUS