or等价改写union SQL案例

同事找我优化一些SQL，其中有个SQL比较经典，拿出来分享给大家，从原来执行2分钟，到1.4S出结果。

-- 原SQL 
SELECT
    count (*)
FROM (
        SELECT
            DISTINCT T1.id,
            T1.doc_no,
            T1.title,
            T1.type,
            T1.addTime,
            T1.urgency,
            T1.addUser,
            T1.fileNo,
            T1.fileNoID,
            T1.orgId,
            T1.deptId,
            T1.fromDept,
            T1.docType,
            T1.docTypeID,
            T1.isOld,
            T1.publicState,
            T1.oaPublicState,
            P.state AS activeState,
            P.cate_id AS cateID,
            P.id AS procInsID
        FROM
            A T1,
            B P
        WHERE
            T1.id = P.case_id
            AND T1.addTime >= '2021-11-17 00:00:00'
            AND T1.addTime <= '2022-11-17 23:59:59'
            AND T1.type IN ('receive', 'dispatch', 'draft')
            AND (T1.oapublicstate = '1'
                OR EXISTS (
                    SELECT
                        P.case_id AS wf_case_id
                    FROM
                        wf_proc_ins_handlers H
                    WHERE
                        P.id = H.proc_ins_id
                        AND H.handler_id = '704fa12e-0166-1000-e000-05a00a010169'))) x;

 这个节点循环了 47108 次，这个节点所需要的时间计算为 47108 * 2.695 = 126,956.06ms  ，2分钟，影响了整条SQL的执行时间。

对应的SQL语句是这段

 

这种语句使用OR 加任何索引都不好使，想要优化只能改写改SQL。

-- 改写SQL优化

with x as (
        SELECT
            DISTINCT T1.id,
            T1.doc_no,
            T1.title,
            T1.type,
            T1.addTime,
            T1.urgency,
            T1.addUser,
            T1.fileNo,
            T1.fileNoID,
            T1.orgId,
            T1.deptId,
            T1.fromDept,
            T1.docType,
            T1.docTypeID,
            T1.isOld,
            T1.publicState,
            T1.oaPublicState,
            P.state AS activeState,
            P.cate_id AS cateID,
            P.id AS procInsID
        FROM
            A T1,
            B P
        WHERE
            T1.id = P.case_id
            AND T1.addTime >= '2021-11-17 00:00:00'
            AND T1.addTime <= '2022-11-17 23:59:59'
            AND T1.type IN ('receive', 'dispatch', 'draft')

)
select count(*) from (
select x1.* from x x1 where x1.oapublicstate = '1'
union 
select x2.* from x x2 left join wf_proc_ins_handlers h on x2.procInsID = h.proc_ins_id where  h.handler_id = '704fa12e-0166-1000-e000-05a00a010169' and h.proc_ins_id is not null
);

 

 

SQL 执行计划

 

 　　修改之后只需要1.4S 就能出结果，两条语句比较 count(*) 结果集等价。