MySQL中exists和join的区别

今天在公司项目中发现call一条API的数据，居然要4.9S，通过查看其SQL语句以及网上查找相关资料，发现是：EXISTS和join的区别:

SELECT T.*
FROM TOPIC T
WHERE 1=1
AND T.REL_TYPE = 'G'

AND EXISTS(
SELECT 1 FROM USER_GROUP_REL R WHERE
R.GROUP_ID = T.REL_ID
AND R.USER_ID = 'EvyUzm'
)
AND (T.SIGN != 'hide' OR T.SIGN IS NULL)
AND
T.STATE != 'DEL'
ORDER BY
T.SIGN DESC,T.SIGN_TIME asc,
T.CREATE_TIME
desc

EXISTS在命中率高的情况下查询速度较快，像这种需要判断的表的数据量较大，而条件表的数据量较少时不宜使用。INNER JOIN相对较稳定一些，不会随命中率的变化而影响性能

SELECT T.*

FROM TOPIC T
        inner join USER_GROUP_REL R on T.REL_ID = R.GROUP_ID 
        AND R.USER_ID = 'EvyUzm'
        AND T.REL_TYPE ='G'
AND (T.SIGN != 'hide' OR T.SIGN IS NULL)
AND T.STATE != 'DEL'
ORDER BY
T.SIGN DESC,T.SIGN_TIME asc,
T.CREATE_TIME

desc;