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

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

引用

CSDN

1.

https://blog.csdn.net/wzc200406/article/details/143168672

在数据库开发中，多表联表查询是一个常见的需求场景，但同时也可能成为性能瓶颈。本文将介绍三种优化多表联表查询的常见方法，包括使用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>