记一次项目中的sql优化

ゞ浴缸里的玫瑰 2024-02-19 20:47 14阅读 0赞

由于得到的结果数据需要从各个表中抽取出来，所以需要连接多张表，导致sql查询的很慢，所以自己就想优化一下

1.优化过后的代码

<select id="findBySarningList" resultType="com.pti.prosecution.web.dto.Vo.PromptVo">
            SELECT
            IFNULL(a.VISIT_PERS_NM,'') as visitPersNm,
            IFNULL(a.ID_CARD,'') as idCard,
            IFNULL(a.PERS_SEX,'') as persSex,
            IFNULL(a.PERS_AGE,'') as persAge,
            IFNULL(a.PHONE,'') as phone,
            IFNULL(a.VISIT_CNT,'') as visitCnt,
            IFNULL(b.VISIT_SRC,'') as visitSrc,
            IFNULL(b.VISIT_TIME,'') as visitTime,
            IFNULL(b.CASE_NM,'') as caseNm,
            IFNULL(b.VISIT_NUM,'') as visitNum,
            IFNULL(b.NOW_STAT,'') as nowStat,
            IFNULL(b.VISIT_DISP_TYP,'') as  visitDispTyp
            from tb_pers_info a  left join tb_visit_info b on a.PERS_INFO_ID=b.PERS_INFO_ID
            where b.CRT_USR_ID=#{usrId}
            <if test="startTime!=null and startTime !=''">
                and b.VISIT_TIME >=#{startTime}
            </if>
             <if test="startTime!=null  and  startTime !='' and endTime!=null and endTime!=''">
              
                 and b.VISIT_TIME between #{startTime} and #{endTime}
             </if>
             <if test="keyWord!=null and keyWord!=''">
                 and b.ADDR_DICT_ID like concat(#{keyWord},'%')
              
                 or b.CASE_NM like concat(#{keyWord},'%')
              
                 or b.NOW_STAT like concat(#{keyWord},'%')
                
                 or b.VISIT_DISP_TYP like concat(#{keyWord},'%')
               
                 or a.VISIT_PERS_NM like concat(#{keyWord},'%')
             </if>
            GROUP BY a.VISIT_PERS_NM ORDER BY b.VISIT_TIME
        </select>

![在这里插入图片描述][2019071518264752.png]2.优化之前的代码，主要是模糊查找

<select id="findBySarningList" resultType="com.pti.prosecution.web.dto.Vo.PromptVo">
            SELECT
            IFNULL(a.VISIT_PERS_NM,'') as visitPersNm,
            IFNULL(a.ID_CARD,'') as idCard,
            IFNULL(a.PERS_SEX,'') as persSex,
            IFNULL(a.PERS_AGE,'') as persAge,
            IFNULL(a.PHONE,'') as phone,
            IFNULL(a.VISIT_CNT,'') as visitCnt,
            IFNULL(b.VISIT_SRC,'') as visitSrc,
            IFNULL(b.VISIT_TIME,'') as visitTime,
            IFNULL(b.CASE_NM,'') as caseNm,
            IFNULL(b.VISIT_NUM,'') as visitNum,
            IFNULL(b.NOW_STAT,'') as nowStat,
            IFNULL(b.VISIT_DISP_TYP,'') as  visitDispTyp
            from tb_pers_info a  left join tb_visit_info b on a.PERS_INFO_ID=b.PERS_INFO_ID
            where b.CRT_USR_ID=#{usrId}
            <if test="startTime!=null and startTime !=''">
                and b.VISIT_TIME >=#{startTime}
            </if>
             <if test="startTime!=null  and  startTime !='' and endTime!=null and endTime!=''">
              
                 and b.VISIT_TIME between #{startTime} and #{endTime}
             </if>
             <if test="keyWord!=null and keyWord!=''">
    
                 and b.ADDR_DICT_ID like concat('%',#{keyWord},'%')
              
                 or b.CASE_NM like concat('%',#{keyWord},'%')
              
                 or b.NOW_STAT like concat('%',#{keyWord},'%')
               
                 or b.VISIT_DISP_TYP like concat('%',#{keyWord},'%')
               
                 or a.VISIT_PERS_NM like concat('%',#{keyWord},'%')
             </if>
            GROUP BY a.VISIT_PERS_NM ORDER BY b.VISIT_TIME
        </select>

[2019071518264752.png]: https://img-blog.csdnimg.cn/2019071518264752.png