SQL语句中的IN和NOT IN子查询理解起来很直观,和实际的业务也很匹配,所有经常被开发人员使用,数据量不大的表还好,如果数据量太大将导致性能问题。
原SQL:
SELECT COUNT(DISTINCT T.ID)
FROM TASK T
WHERE T.TENANT_KEY= 'TP18SQUME1'
AND T.ID IN(
SELECT ENTITY_ID
FROM SHARE_ENTRY
WHERE ENTRY_TYPE= 'user'
AND SID= 8005824118306255410)
AND T.ID NOT IN(
SELECT DISTINCT S.TARGET_ID
FROM STREAM S
WHERE S.OPT_USER= 8005824118306255410
AND S.TARGET_ID IS NOT NULL)
AND T.CREATOR!= 8005824118306255410
AND T.STATUS= 'todo'
由于IN和NOT IN会全表扫描,所有以上查询性能很低,耗时4s左右;
优化后的SQL:
SELECT COUNT(DISTINCT T.ID) FROM ((select ID from TASK WHERE TENANT_KEY= 'TP18SQUME1' AND CREATOR!= 8005824118306255410 AND STATUS= 'todo') T
left join (SELECT TARGET_ID FROM STREAM WHERE OPT_USER= 8005824118306255410) S ON T.ID = S.TARGET_ID
left join (SELECT ENTITY_ID FROM SHARE_ENTRY WHERE ENTRY_TYPE= 'user' AND SID= 8005824118306255410) E ON T.ID=E.ENTITY_ID)
WHERE S.TARGET_ID IS NULL AND E.ENTITY_ID IS NOT NULL
用join链接查询代替IN和NOT IN查询可以大大提高效率,优化后的查询耗时0.2s。