一次or查询的优化

情形：多个条件放到一个搜索框查询两个表的数据

优化前

<sql id="condition">
    <if test="map.mesItemId != null and map.mesItemId != ''">
        AND ovet.item_id =#{map.mesItemId}
    </if>
    <if test="map.carMaterialCodeOrVinOrOrderNo != null and map.carMaterialCodeOrVinOrOrderNo !=''">
        AND (
        item.car_material_code =#{map.carMaterialCodeOrVinOrOrderNo}
        OR ovet.order_no = #{map.carMaterialCodeOrVinOrOrderNo}
        OR ovet.vin = #{map.carMaterialCodeOrVinOrOrderNo}
        )
    </if>
</sql>

<!-- 分页 -->
<select id="findVinByMap1" resultType="me.cf81.onestep.mes.dto.MesOvetDto">
    SELECT ovet.order_no,ovet.vin,item.car_material_code FROM epc_mes_ovet ovet
    LEFT JOIN epc_mes_item item ON item.id=ovet.item_id
    WHERE
    <include refid="condition"/>
    ORDER BY ovet.create_time DESC
    LIMIT #{pageable.offset}, #{pageable.pageSize}
</select>

<!--查询总条数-->
<select id="countVINByMap1" resultType="java.lang.Long">
    select count(1)
    from
    <include refid="condition"/>
</select>

优化后

<sql id="isNullMesId">
    <if test="map.mesItemId != null and map.mesItemId != ''">
        AND ovet.item_id =#{map.mesItemId}
    </if>
</sql>
<sql id="condition1">
    <include refid="isNullMesId"/>
    AND item.car_material_code =#{map.carMaterialCodeOrVinOrOrderNo}
</sql>
<sql id="condition2">
    <include refid="isNullMesId"/>
    AND ovet.order_no = #{map.carMaterialCodeOrVinOrOrderNo}
</sql>
<sql id="condition3">
    <include refid="isNullMesId"/>
    AND ovet.vin = #{map.carMaterialCodeOrVinOrOrderNo}
</sql>

<sql id="selectJoinOvetAndItme">
    SELECT ovet.order_no,ovet.vin,item.car_material_code FROM epc_mes_ovet ovet
    LEFT JOIN epc_mes_item item ON item.id=ovet.item_id
</sql>

<sql id="unionAll">
    <include refid="selectJoinOvetAndItme"/>
    <where>
        <include refid="condition1"/>
    </where>
    union all
    <include refid="selectJoinOvetAndItme"/>
    <where>
        <include refid="condition2"/>
    </where>
    union all
    <include refid="selectJoinOvetAndItme"/>
    <where>
        <include refid="condition3"/>
    </where>
</sql>

<!-- 如果mesItemId存在 则car_material_code唯一，输入其他的不显示 -->
<!-- 分页 -->
<select id="findVinByMap" resultType="me.cf81.onestep.mes.dto.MesOvetDto">
    <choose>
        <when test="map.carMaterialCodeOrVinOrOrderNo != null and map.carMaterialCodeOrVinOrOrderNo !=''">
            select order_no,vin,car_material_code
            from (
            <include refid="unionAll"/>
            ) t
        </when>
        <otherwise>
            <include refid="selectJoinOvetAndItme"/>
            <where>
                <include refid="isNullMesId"/>
            </where>
        </otherwise>
    </choose>
    LIMIT #{pageable.offset}, #{pageable.pageSize}
</select>

<!--查询总条数-->
<select id="countVINByMap" resultType="java.lang.Long">
    <choose>
        <when test="map.carMaterialCodeOrVinOrOrderNo != null and map.carMaterialCodeOrVinOrOrderNo !=''">
            select count(1)
            from (
            <include refid="unionAll"/>
            ) t
        </when>
        <otherwise>
            SELECT count(1) FROM epc_mes_ovet ovet
            LEFT JOIN epc_mes_item item ON item.id=ovet.item_id
            <where>
                <include refid="isNullMesId"/>
            </where>
        </otherwise>
    </choose>
</select>

重点：when otherwise的使用 union all 的使用

结果是不用or，使得查询使用了索引，大大提高了速度