使用union all优化or查询速度

甲方又提新需求了，虽然心里已经有了解决办法，还是表现得好难的样子，按惯例先bb一通有多复杂，多难实现。

前情提要：

数据库：mysql

数据库框架：mybatis

已经有了两个搜索条件，此时又要新增一个查询条件，查询条件变为三个。但是输入框只有一个，此时三个条件只能选取其中一个。

难点：

因为三个查询条件只能选取其中一个，首先想到的是or查询，但是这样的方法必然很慢，因此想到用 union all 优化or查询速度。

上代码

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="me.cf.me.mes.dao.MesItemMapper">
    <sql id="allColumns">
        id,
        create_time,
        update_time,
        order_no,
        car_material_code,
        car_material_name,
        carseries_code,
        model,
        short_name
    </sql>
    <sql id="selectBasePage">
        SELECT <include refid="allColumns"/>
        FROM epc_mes_item
    </sql>
    <sql id="selectByCarMaterialCode">
        <where>
            AND car_material_code = #{map.carMaterialCodeOrOrderNoOrCarseriesCode}
        </where>
    </sql>
    <sql id="selectByOrderNo">
        <where>
            AND order_no = #{map.carMaterialCodeOrOrderNoOrCarseriesCode}
        </where>
    </sql>
    <sql id="selectByCarseriesCode">
        <where>
            AND carseries_code = #{map.carMaterialCodeOrOrderNoOrCarseriesCode}
        </where>
    </sql>

    <!--查询总条数-->
    <select id="getPageCount" resultType="java.lang.Long">
        <choose>
            <when test="map.carMaterialCodeOrOrderNoOrCarseriesCode != null and map.carMaterialCodeOrOrderNoOrCarseriesCode !=''">                SELECT COUNT(1) FROM (
                    <include refid="selectBasePage"/>
                    <include refid="selectByCarMaterialCode"/>
                    UNION ALL
                    <include refid="selectBasePage"/>
                    <include refid="selectByOrderNo"/>
                    UNION ALL
                    <include refid="selectBasePage"/>
                    <include refid="selectByCarseriesCode"/>
                )t
            </when>
            <otherwise>
                SELECT COUNT(1) FROM epc_mes_item
            </otherwise>
        </choose>
    </select>

    <!-- 分页 -->
    <select id="getPage" resultType="me.cf81.onestep.mes.model.MesItem">
      <choose>
          <when test="map.carMaterialCodeOrOrderNoOrCarseriesCode != null and map.carMaterialCodeOrOrderNoOrCarseriesCode !=''">
              <include refid="selectBasePage"/>
              <include refid="selectByCarMaterialCode"/>
              UNION ALL
              <include refid="selectBasePage"/>
              <include refid="selectByOrderNo"/>
              UNION ALL
              <include refid="selectBasePage"/>
              <include refid="selectByCarseriesCode"/>
          </when>
          <otherwise>
              <include refid="selectBasePage"/>
          </otherwise>
      </choose>
      ORDER BY create_time DESC
      LIMIT #{pageable.offset}, #{pageable.pageSize}
    </select>
</mapper>

这里你首先需要清楚 union 和union all的用法及区别

三个条件字段需要加上索引，不然效果不明显。

这次改动的遇到的问题的地方在查询分页数量上，并不能简单的直接的count，而是要先根据条件查数据创建临时表，然后统计临时表的数量。

还有一个不知道是否可以优化：在查询分页数量的时候，临时表的字段可以只查询一个，但是我不知道在查询分页的时候会不会用到查分页数量的sql缓存，所以就没有优化

作者：帮我的鸵鸟盖个章
链接：https://www.jianshu.com/p/8e03b1565c7a