优化多表联表查询的常见方法归纳

一、使用mybatis的嵌套查询

二、添加表冗余字段，减少联表查询需求

三、分表预处理，前端再匹配

一、使用mybatis的嵌套查询

【场景说明】

前端需要展示一张列表，其中的字段来源于多张表，如何进行查询优化？

【需求分析】

传统的联表查询使用左外连接连接四张表，整体查询从笛卡儿积开始优化，效率着实是低，我们可以使用mybatis 嵌套查询。所谓嵌套查询，就是将原来多表的联合查询语句拆成多个单表的查询，再使用mybatis的语法嵌套在一起。

【实现方式】

首先你得了解Mybatis嵌套查询的一些语法情况：

1. association：处理一对一、一对多嵌套、返回一个查询实体

2. collection：处理多对多嵌套、返回多个查询实体

3. resultMap：用于映射查询数据的实体类

【实现步骤】

分析该场景需求只需要通过id字段去其他表查找对应数据，属于一对多模型，使用association

（其中由于设备表只需要一个统计数量的字段，直接联表就好了）

1. 编写实现基本功能的语句：

SELECTn.id,n.node_name,n.address,n.business_type,n.region_id,n.partner_id,n.create_time,n.update_time,n.create_by,n.update_by,n.remark,COUNT(v.id) AS vm_count
FROMtb_node nLEFT JOINtb_vending_machine v ON n.id = v.node_id
GROUP BYn.id;

2. 实现基本的Mybatis语句：

注意不用resultType 而是使用 resultMap

    <select id="selectNodeVoList" parameterType="Node" resultMap="NodeVoResult">SELECTn.id,n.node_name,n.address,n.business_type,n.region_id,n.partner_id,n.create_time,n.update_time,n.create_by,n.update_by,n.remark,COUNT(v.id) AS vm_countFROMtb_node nLEFT JOINtb_vending_machine v ON n.id = v.node_id<where><if test="nodeName != null  and nodeName != ''"> and n.node_name like concat('%', #{nodeName}, '%')</if><if test="regionId != null "> and n.region_id = #{regionId}</if><if test="partnerId != null "> and n.partner_id = #{partnerId}</if></where>GROUP BYn.id</select>

3. 编写resultMap

<resultMap id="NodeVoResult" type="NodeVo"><result property="id"    column="id"    /><result property="nodeName"    column="node_name"    /><result property="address"    column="address"    /><result property="businessType"    column="business_type"    /><result property="regionId"    column="region_id"    /><result property="partnerId"    column="partner_id"    /><result property="createTime"    column="create_time"    /><result property="updateTime"    column="update_time"    /><result property="createBy"    column="create_by"    /><result property="updateBy"    column="update_by"    /><result property="remark"    column="remark"    /><result property="vmCount" column="vm_count"    /><association property="region" javaType="Region" column="region_id" select="com.dkd.manage.mapper.RegionMapper.selectRegionById"/><association property="partner" javaType="Partner" column="partner_id" select="com.dkd.manage.mapper.PartnerMapper.selectPartnerById"/></resultMap>

4. VO对象说明

NodeVo对象包含了 Node实体、Region实体、Partner实体三个实体、vmCount设备数量字段

@Data
@AllArgsConstructor
@NoArgsConstructor
public class NodeVo extends Node{// 区域private Region region;// 合作商private Partner partner;// 设备数量private Integer vmCount;
}

二、添加表冗余字段，减少联表查询需求

【场景说明】

前端还是需要展示这一张表，包含了员工表、角色表、区域表等多表字段

【实现方式】

主体是员工数据，对于其他两张表数据使用并不多。基于此我们这次可以做这样一种处理：在设计数据表结构的时候，将region表的归属区域字段、role表的角色字段以冗余字段的形式一并设计入emp表。这样原先需要联表的需求就退化成单表查询了！

【实体说明】

/*** 人员列表对象 tb_emp* * @author weizhicong* @date 2024-10-21*/
public class Emp extends BaseEntity
{private static final long serialVersionUID = 1L;/** 主键 */private Long id;/** 人员名称 */@Excel(name = "人员名称")private String userName;/** 所属区域Id */private Long regionId;/** 归属区域 */@Excel(name = "归属区域")private String regionName;/** 角色id */private Long roleId;/** 角色编号 */private String roleCode;/** 角色名称 */@Excel(name = "角色名称")private String roleName;/** 联系电话 */@Excel(name = "联系电话")private String mobile;...........    }

【方法缺陷说明】数据一致性问题！

这种实现方式虽然优化了查询效率问题。但提高了数据库表设计的耦合度。具体表现在：当需要修改区域表名称字段、或者修改角色表的角色字段信息时，可能会导致数据的不同步，违反了数据一致性的问题。

【改进步骤】

为了解决这个问题，我们需要进行同步更新操作，具体地，在每次更新区域表或角色表字段的同时，调用更新员工表的方法，进行同步更新，同时注意事务的处理。

1. 在EmpMapper中定义好更新员工表的方法：

/*** 根据区域ID更新区域信息*/@Update("update tb_emp set region_name = #{regionName} where region_id = #{regionId}")public int updateByRegionId(@Param("regionName") String regionName, @Param("regionId") Long regionId);

2. 在修改区域表的时候，注入EmpMapper，并调用该方法

注意添加事务注解！！

    /*** 修改区域管理* * @param region 区域管理* @return 结果*/@Override@Transactional(rollbackFor = Exception.class)public int updateRegion(Region region){// 1. 更新区域信息region.setUpdateTime(DateUtils.getNowDate());int result =  regionMapper.updateRegion(region);//2. 同步更新员工表的区域名称empMapper.updateByRegionId(region.getRegionName(),region.getId());return result;}

3. 所以该方法适用于其余表字段修改较少的情况

三、分表预处理，前端再匹配

【场景说明】

前端还是需要展示一张涉及多张表数据的列表：

【实现方式】

这一次，我们后端同学不服了，凭啥老是让我们后端同学来处理这个问题，于是问题丢给了前端。前端如何实现这个需求呢？如果前端能像查字典一样匹配每张表的数据，然后组装在一起如何呢？显然可以。简单来说，前端预先分别调用多张列表的查询接口，将表数据预存到页面的响应式数据中形成字典，在进行v-for展示时，匹配字典，得到数据。这样一来，联表查询也被优化成单表查询了。

【实现步骤】

1. 预查询合作商表数据：

import {listPartner} from "@/api/manage/partner";
import {loadAllParams} from "@/api/page";// 定义
const partnerlist = ref([]);/** 查询合作商列表 */
function getPartnerList() {listPartner(loadAllParams).then(respone => {partnerlist.value = respone.rows;})
}// 使用
getPartnerList();

2. 预查询设备类型表：

import {listVmType} from "@/api/manage/vmType";
import {loadAllParams} from "@/api/page";// 定义
const vmTypeList = ref([]);/** 查询设备类型列表 */
function getVmTypeList() {listVmType(loadAllParams).then(respone => {vmTypeList.value = respone.rows;})
}// 使用
getVmTypeList();

3. 页面匹配调用：

<!--表格--><el-table v-loading="loading" :data="vmList" @selection-change="handleSelectionChange"><!--省略--><el-table-column label="设备型号" align="center" prop="vmTypeId"><!--字典--><template #default="scope"><div v-for="item in vmTypeList" :key="item.id"><span v-if="item.id == scope.row.vmTypeId">{{ item.name }}</span></div></template></el-table-column><el-table-column label="合作商" align="center" prop="partnerId"><!--字典--><template #default="scope"><div v-for="item in partnerlist" :key="item.id"><span v-if="item.id == scope.row.partnerId">{{ item.partnerName }}</span></div></template></el-table-column><!--省略--></el-table>