记一次项目中的sql优化

最新推荐文章于 2023-06-25 11:11:25 发布

原创最新推荐文章于 2023-06-25 11:11:25 发布 · 278 阅读

0 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

19 篇文章

订阅专栏

博客内容主要展示了代码优化前后的情况，优化前代码主要用于模糊查找，之后进行了优化。体现了代码在模糊查找功能上的改进过程。

由于得到的结果数据需要从各个表中抽取出来，所以需要连接多张表，导致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 &gt;=#{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>

在这里插入图片描述 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 &gt;=#{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>