mysql查询一条工单时间需要10秒。优化sql语句得以解决。

后台运行一个查询接口，需要执行10秒。

项目背景：物业运维APP，打开巡检工单，需要10秒。一个

一个巡检工单包含多个task，每个task包含多个option，需要连表查。

分析原因： 每个工单有约60个task，每个task有8个option。每查一个option，需要遍历option表（该表有10万+行记录）。合计 60*8*10万

原来的mapper中的sql

<resultMap id="orderTaskMap" type="com.zfzn.cwyms.property.entity.WyOrderPatrolTaskEntity">
<result property="areaName" column="areaName" javaType="String"/>
<result property="deviceName" column="deviceName" javaType="String"/>
<result property="areaAdress" column="areaAdress" javaType="String"/>
<result property="deviceAdress" column="deviceAdress" javaType="String"/>
<result property="patrolType" column="patrolType" javaType="String"/>
<result property="isException" column="isException" javaType="Integer"/>
<result property="dealTime" column="dealTime" javaType="Date"/>
<result property="state" column="state" javaType="String"/>
<result property="stateCode" column="stateCode" javaType="Integer"/>
<collection property="results" ofType="com.zfzn.cwyms.property.entity.WyOrderPlanTaskRlt"
select="findResultsByTaskId" column="taskId"/>
</resultMap>

<select id="findOrderTasksByOrderId" resultMap="orderTaskMap">
SELECT
<include refid="sqlSelectOrderPTaskColumn"/>
FROM wy_order_patrol_task task
LEFT OUTER JOIN wy_region_config rgn ON task.area_id = rgn.region_id
LEFT OUTER JOIN wy_build_floor af ON rgn.build_floor = af.floor_id
LEFT OUTER JOIN wy_build_config ab ON af.build = ab.build_id
LEFT OUTER JOIN wy_device_info dev ON task.device_id = dev.device_id
LEFT OUTER JOIN wy_build_config db ON dev.build = db.build_id
LEFT OUTER JOIN wy_region_config dgn ON dev.region = dgn.region_id
LEFT OUTER JOIN wy_build_floor df ON dev.build_floor = df.floor_id
LEFT OUTER JOIN wy_patrol_type tye ON task.task_type = tye.type
LEFT OUTER JOIN wy_order_patrol_task_state sta ON task.state = sta.state
WHERE task.patrol_order_id = #{orderId}
</select>

<select id="findResultsByTaskId" resultMap="resultMap">
SELECT
<include refid="sqlSelectOrderPTResultColumn"/>
FROM wy_order_patrol_result
WHERE task_id = #{taskId}
</select>

分析:在resultMap中有个collection，包含一个查询。

外部查询没查到一个任务，就会执行一遍colletion中查询（该查询去查那个最大的option表），

解决办法：把collection中查询去掉。放在外层查询。

 SELECT
        <include refid="sqlSelectOrderPTaskColumn"/>,RST.id AS rstId,RST.type AS optionType,RST.name AS
        optionName,RST.position,RST.name_positive AS posName,RST.name_negative AS negName,RST.unit,RST.alarm_min AS
        valueMin,RST.alarm_max AS valueMax,RST.choice_value AS choiceValue,RST.input_value AS inputValue
        FROM wy_order_patrol_result RST
        INNER JOIN wy_order_patrol_task task ON task.task_id = RST.task_id
        LEFT OUTER JOIN wy_region_config rgn ON task.area_id = rgn.region_id
        LEFT OUTER JOIN wy_build_floor af ON rgn.build_floor = af.floor_id
        LEFT OUTER JOIN wy_build_config ab ON af.build = ab.build_id
        LEFT OUTER JOIN wy_device_info dev ON task.device_id = dev.device_id
        LEFT OUTER JOIN wy_build_config db ON dev.build = db.build_id
        LEFT OUTER JOIN wy_region_config dgn ON dev.region = dgn.region_id
        LEFT OUTER JOIN wy_build_floor df ON dev.build_floor = df.floor_id
        LEFT OUTER JOIN wy_patrol_type tye ON task.task_type = tye.type
        LEFT OUTER JOIN wy_order_patrol_task_state sta ON task.state = sta.state
        WHERE task.patrol_order_id = #{orderId}



<resultMap id="orderTaskMap" type="com.zfzn.cwyms.property.entity.WyOrderPatrolTaskEntity">
        <result property="areaName" column="areaName" javaType="String"/>
        <result property="deviceName" column="deviceName" javaType="String"/>
        <result property="areaAdress" column="areaAdress" javaType="String"/>
        <result property="deviceAdress" column="deviceAdress" javaType="String"/>
        <result property="patrolType" column="patrolType" javaType="String"/>
        <result property="isException" column="isException" javaType="Integer"/>
        <result property="dealTime" column="dealTime" javaType="Date"/>
        <result property="state" column="state" javaType="String"/>
        <result property="stateCode" column="stateCode" javaType="Integer"/>
        <collection property="results" resultMap="resultMap" ofType="com.zfzn.cwyms.property.entity.WyOrderPlanTaskRlt"/>
    </resultMap>

 

修改以后，从10秒降低到2秒