使用Mybatis进行连表查询、left join

目前有个需求：
条件筛选出一个A表的结果list，然后需要增加一个字段，另一张B表的uuid，
他们之间的关联关系是 a.uuid=b.releaseUuid,
若不存在则返回null.

因此考虑用左连接,不存在字段也不影响总的A表条数

---

本来实体

---

dao:

/** * t_table dao * @author Mingchenchen * */public interface ReleaseDao{    /**     * 获取条件筛选结果的总数     * @param parameterMap     * @return     */    public Integer getCountByCondition(Map<String, Object> parameterMap);    /**     * 列表 条件筛选     * @param releaseEntity     * @return     */    public List<ReleaseEntity> getReleaseListByCondition(Map<String, Object> parameterMap);}

---

Entity:

import java.util.Date;import javax.persistence.Column;import javax.persistence.Id;import javax.persistence.Table;/** * 实体 * @author Mingchenchen * */@Table(name = "t_table")public class ReleaseEntity {    @Id    private String uuid;    @Column    private String name;    @Column(name = "app_name")    private String appName;    @Column(name = "app_uuid")    private String appUuid;    @Column(name="deploy_type")    private String deployType;//    @Column(name="department_code")    private String departmentCode;//    @Column(name="department_name")    private String departmentName;//    @Column(name = "status")    private int status;//状态 0:已创建 1:已编排 2:已部署    @Column(name = "flag")    private int releaseType;//    @Column(name="delete_flag")    private int deleteFlag;//    @Column(name = "create_time")    private Date   createTime;    @Column(name = "create_user")    private String createUser;    @Column(name = "update_time")    private Date   updateTime;    @Column(name = "update_user")    private String updateUser;    @Column(name = "remark")    private String remark;    /** 多余返还的字段 不要注解column 否则insert会报错 相当于让mybatis忽略此字段 */    private String departmentCode;//所属业务线的简码    private String departmentName;//所属业务线的名称    private String deployType;//发布单的部署类型:QA TEST DEV PRODUCT    private String namespace;    /*    * Getter and setter    */}

---

Mybatis xml sql:

<?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="com.xxx.dao.ReleaseDao">    <!-- 结果集:1 -->    <resultMap id="microServiceResultMap" type="MicroServiceEntity">          <id property="uuid" column="uuid" />          <result property="name" column="name" />         <result property="version" column="version" />           <result property="ports" column="port_metadata" />      </resultMap>      <!-- 结果集:2 -->    <resultMap id="releaseResultMap" type="ReleaseEntity">      <id property="uuid" column="uuid" />      <result property="name" column="name"/>      <result property="appName" column="app_name"/>      <result property="appUuid" column="app_uuid"/>      <result property="deployType" column="deploy_type"/>      <result property="departmentCode" column="department_code"/>      <result property="departmentName" column="department_name"/>      <result property="status" column="status"/>      <result property="qps" column="qps"/>      <result property="releaseType" column="dms_flag"/>      <result property="deleteFlag" column="delete_flag"/>      <result property="createTime" column="create_time"/>      <result property="createUser" column="create_user"/>      <result property="updateTime" column="update_time"/>      <result property="updateUser" column="update_user"/>      <result property="remark" column="remark"/>      <result property="layoutTemplateUuid" column="layout_template_uuid"/>//第二张表的字段    </resultMap>    <!-- 列表总数 -->    <select id="getCountByCondition" parameterType="java.util.Map" resultType="java.lang.Integer">          select count(*)         from dms_release         where 1=1         <if test="releaseStatu != null and releaseStatu != -1">                and status = #{releaseStatu}        </if>        <if test="appName != null and appName !=''">                and app_name like #{appName}        </if>          <if test="deployType != null and deployType != ''">                and deploy_type = #{deployType}        </if>          <if test="departmentCode != null and departmentCode != ''">                and department_code = #{departmentCode}        </if>         and delete_flag = 0    </select>      <!-- 发布单列表 条件查询 -->    <select id="getReleaseListByCondition" parameterType="java.util.Map"                                             resultMap="releaseResultMap">          SELECT           d.department_code,d.department_name,        d.status,d.qps,d.dms_flag,d.delete_flag,d.create_time,        d.create_user,d.update_time,d.update_user,        d.remark,        l.uuid as layout_template_uuid //需要第二张表的uuid        FROM         d_table as d        LEFT JOIN l_table as l        ON d.uuid=l.release_uuid        AND l.delete_flag=0  //注意此处join的那张表的查询条件必须写在ON后不能写在最后面        WHERE 1=1         <if test="releaseStatu != null and releaseStatu != -1">                and d.status = #{releaseStatu}        </if>        <if test="appName != null and appName !=''">                and d.app_name like #{appName}        </if>          <if test="deployType != null and deployType != ''">                and d.deploy_type = #{deployType}        </if>          <if test="departmentCode != null and departmentCode != ''">                and d.department_code = #{departmentCode}        </if>         AND d.delete_flag = 0         ORDER BY d.create_time         DESC LIMIT #{pn},#{ps}        //pn是开始下表 从0开始        //ps是总条数 比如是10,10 即从10条开始,共需10条 ,而不是10,20    </select>  </mapper>

---

优化

before:

SELECT a.name,a.uuid,a.app_name,a.app_uuid,a.release_uuid,a.release_name,a.layout_template_name,a.layout_template_uuid,a.layout_template_version,a.cluster_uuid,a.status,a.remark,a.delete_flag,a.create_time,a.create_user,a.update_time,a.update_user,d.department_code,d.deploy_type,d.department_name,d.namespaceFROM app_instance as aLEFT JOIN dms_release as dON a.release_uuid = d.uuidAND d.delete_flag=0WHERE a.delete_flag=0ORDER BY a.create_time DESC LIMIT 1,2

after：

SELECT a.name,a.uuid,a.app_name,a.app_uuid,a.release_uuid,a.release_name,a.layout_template_name,a.layout_template_uuid,a.layout_template_version,a.cluster_uuid,a.status,a.remark,a.delete_flag,a.create_time,a.create_user,a.update_time,a.update_user,d.department_code,d.deploy_type,d.department_name,d.namespaceFROM (        select * from app_instance where delete_flag=0         ORDER BY create_time desc LIMIT 1,2     ) as aLEFT JOIN dms_release as dON a.release_uuid = d.uuidAND d.delete_flag=0

ALTER TABLE app_instance ADD INDEX index_release_uuid (release_uuid)

思路:
1.先筛选出符合条件的再进行join,而不是先全表join再筛选
2.加入关键选项的索引