sql语句中使用强制索引

最新推荐文章于 2024-01-24 09:46:48 发布

回忆是昨天里的海

最新推荐文章于 2024-01-24 09:46:48 发布

阅读量1.1k

点赞数

分类专栏：开发 mybatis

本文链接：https://blog.csdn.net/qq_43318174/article/details/117411805

版权

开发同时被 2 个专栏收录

28 篇文章 3 订阅

订阅专栏

mybatis

5 篇文章 0 订阅

订阅专栏

后台管理进行分页查询时，发现其他人都是一个条件加一个索引，然后在sql语句里强制使用一个联合索引

索引

mybatis中的sql语句

    <select id="listRecommenderByCondition" parameterType="com.wscar.xny.recommender.domain.dto.DaoRecommenderQueryDTO"
            resultMap="BaseResultMap">
        SELECT
        *
        FROM
        recommender
        FORCE INDEX(`idx_phone`,`idx_name`,`idx_city`,`idx_orderCount`,`idx_gmtCreate`)
        <include refid="WhereColumn"/>
        ORDER BY
        ${sortColumn} ${sortType}
        LIMIT
        #{limit,jdbcType=BIGINT},#{rows,jdbcType=BIGINT}
    </select>
  <sql id="WhereColumn">
        <where>
            <if test="name != null and name != ''">
                AND
                name LIKE #{name,jdbcType=VARCHAR}
            </if>
            <if test="phone != null and phone != ''">
                AND
                phone LIKE #{phone,jdbcType=VARCHAR}
            </if>
            <if test="cityCode != null and cityCode > 0">
                AND
                cityCode = #{cityCode,jdbcType=INTEGER}
            </if>
            <if test="gmtCreateStart != null and gmtCreateStart > 0">
                AND
                gmtCreate &gt;= #{gmtCreateStart,jdbcType=BIGINT}
            </if>
            <if test="gmtCreateEnd != null and gmtCreateEnd > 0">
                AND
                gmtCreate &lt;= #{gmtCreateEnd,jdbcType=BIGINT}
            </if>
        </where>
    </sql>
 <resultMap id="BaseResultMap" type="com.wscar.xny.recommender.domain.entity.RecommenderDO">
        <!--
          WARNING - @mbg.generated
        -->
        <id column="id" jdbcType="BIGINT" property="id"/>
        <result column="phone" jdbcType="VARCHAR" property="phone"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="companyId" jdbcType="INTEGER" property="companyId"/>
        <result column="companyName" jdbcType="VARCHAR" property="companyName"/>
        <result column="serviceyStationName" jdbcType="VARCHAR" property="serviceyStationName"/>
        <result column="positionName" jdbcType="VARCHAR" property="positionName"/>
        <result column="cityCode" jdbcType="INTEGER" property="cityCode"/>
        <result column="orderCount" jdbcType="BIGINT" property="orderCount"/>
        <result column="status" jdbcType="TINYINT" property="status"/>
        <result column="gmtCreate" jdbcType="BIGINT" property="gmtCreate"/>
        <result column="gmtModified" jdbcType="BIGINT" property="gmtModified"/>
        <result column="creater" jdbcType="BIGINT" property="creater"/>
        <result column="updater" jdbcType="BIGINT" property="updater"/>
    </resultMap>