【Mysql】【子查询-left join改写】前端SQL调优

相关数据已经脱敏处理

-- 原SQL
SELECT *
  FROM (SELECT a.*,
               (SELECT sum(CASE
                             WHEN (b.confidence < c.THRESHOLD * d.THRESHOLD AND
                                  b.KEY_NAME = d.TYPE_NAME) THEN
                              1
                             ELSE
                              0
                           END)
                  FROM tableAAA_detail     b,
                       tableBBB        c,
                       tableBBB_detail d
                 WHERE c.TYPE_NAME = 'tableAAA'
                   AND c.ID = d.TYPE_ID
                   AND b.ANNO_ID = a.ANNO_ID
                   AND b.RECORD_ID = a.RECORD_ID
                   AND (a.ACTIVE_FLG <> 1 OR a.ACTIVE_FLG IS NULL)
                   AND (b.ACTIVE_FLG <> 1 OR b.ACTIVE_FLG IS NULL)
                   AND b.KEY_NAME = d.TYPE_NAME) AS checkFlg
          FROM tableAAA a
         WHERE (a.QA_DROP_FLG IS NULL OR a.QA_DROP_FLG <> 1)
         ORDER BY a.ACTIVE_FLG ASC,
                  CASE
                    WHEN checkFlg > 0 THEN
                     0
                    ELSE
                     1
                  END ASC) p ;


-- Join改写（不等价）
SELECT a.*,sum(CASE WHEN (b.confidence < c.THRESHOLD * d.THRESHOLD AND
                                  b.KEY_NAME = d.TYPE_NAME) THEN
                              1
                             ELSE
                              0
                           END) AS checkFlg
          FROM tableAAA a join tableAAA_detail b on ( b.ANNO_ID = a.ANNO_ID
                   AND b.RECORD_ID = a.RECORD_ID)
join tableBBB_detail d on (b.KEY_NAME = d.TYPE_NAME)
join tableBBB        c on (c.ID = d.TYPE_ID)
WHERE (a.QA_DROP_FLG IS NULL OR a.QA_DROP_FLG <> 1)
AND (a.ACTIVE_FLG <> 1 OR a.ACTIVE_FLG IS NULL)
AND (b.ACTIVE_FLG <> 1 OR b.ACTIVE_FLG IS NULL)
and c.TYPE_NAME = 'tableAAA' 
group by a.ANNO_ID, a.RECORD_ID
/*
ORDER BY a.ACTIVE_FLG ASC,
                  CASE
                    WHEN checkFlg > 0 THEN
                     0
                    ELSE
                     1
                  END ASC
*/


--left join 改写
SELECT a.*,b.checkFlg 
from tableAAA a left join (
SELECT    b.ANNO_ID,b.RECORD_ID,sum(CASE
             WHEN (b.confidence < c.THRESHOLD * d.THRESHOLD AND
                  b.KEY_NAME = d.TYPE_NAME) THEN
              1
             ELSE
              0
           END) AS checkFlg
  FROM tableAAA_detail     b,
       tableBBB        c,
       tableBBB_detail d
 WHERE c.TYPE_NAME = 'tableAAA'
   AND c.ID = d.TYPE_ID
   AND (b.ACTIVE_FLG <> 1 OR b.ACTIVE_FLG IS NULL)
   AND b.KEY_NAME = d.TYPE_NAME
group by b.ANNO_ID,b.RECORD_ID
) b
on (b.ANNO_ID = a.ANNO_ID AND b.RECORD_ID = a.RECORD_ID)
where (a.QA_DROP_FLG IS NULL OR a.QA_DROP_FLG <> 1)
ORDER BY a.ACTIVE_FLG ASC, checkFlg desc