优化多表联表查询的常见方法归纳
目录
一、使用mybatis的嵌套查询
二、添加表冗余字段，减少联表查询需求
三、分表预处理，前端再匹配
一、使用mybatis的嵌套查询
【场景说明】
前端需要展示一张列表，其中的字段来源于多张表，如何进行查询优化？
【需求分析】
传统的联表查询使用左外连接连接四张表，整体查询从笛卡儿积开始优化，效率着实是低，我们可以使用mybatis 嵌套查询。所谓嵌套查询，就是将 原来多表的联合查询语句拆成多个单表的查询，再使用mybatis的语法嵌套在一起。
【实现方式】
首先你得了解Mybatis嵌套查询的一些语法情况：
1. association： 处理一对一、一对多嵌套、返回一个查询实体
2. collection：处理多对多嵌套、返回多个查询实体
3. resultMap：用于映射查询数据的实体类
【实现步骤】
分析该场景需求只需要通过id字段去其他表查找对应数据，属于一对多模型，使用association
（其中由于设备表只需要一个统计数量的字段，直接联表就好了）
1. 编写实现基本功能的语句：
SELECT
n.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
FROM
tb_node n
LEFT JOIN
tb_vending_machine v ON n.id = v.node_id
GROUP BY
n.id;2. 实现基本的Mybatis语句：
注意不用resultType 而是使用 resultMap
<select id="selectNodeVoList" parameterType="Node" resultMap="NodeVoResult">
SELECT
n.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
FROM
tb_node n
LEFT JOIN
tb_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 BY
n.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>