最近项目中的一次SQL查询数据缓慢优化的一点总结

当数据量增多之后，多表关联查询，又增加了模糊查询条件，导致查询缓慢，由于是微服务项目，服务之间调用超时，从而引起连接超时异常，导致服务不可用！

一、优化方案

1、将SQL拆分、分2次查询
2、将两次查询的结果，重新进行封装组合

原始SQL如下

<select id="getUserGroupPage"
            resultType="com.xxx.service.api.response.CmsUserGroupResp">
        select
            b.id,
            b.del_flag,
            b.create_time,
            b.is_delete,
            b.type,
            b.name,
            b.update_time,
            b.create_user,
            b.group_id,
            b.personnel_number,
            (select count(*) from cms_push_task_user_logs as e where c.id = e.push_task_logs_id ) as delivery_user_nums ,
            (select count(*) from cms_push_task_user_logs as d where d.del_flag=0 and d.push_status = 1 and a.id=d.push_task_id) as total_push_user_nums
        from
            cms_user_group b
        left join cms_push_task  as a on a.user_group_id = b.group_id
        left join cms_push_task_logs as c on c.push_task_id = a.id
        where
             1 = 1
             <if test="entity.userGroupOrCreator !=null and entity.userGroupOrCreator != ''">
                 <bind name="userGroupOrCreator" value="'%'+entity.userGroupOrCreator+'%'"/>
                 and (b.create_user like #{userGroupOrCreator} or b.name like #{userGroupOrCreator})
             </if>
             <if test="entity.type != null ">
                 and b.type = #{entity.type}
             </if>
         order by b.create_time desc
    </select>

计算delivery_user_nums和total_push_user_nums时候，查询严重变慢，导致接口访问超时。
特此将这两个字段单独拿出来查询，优化后的SQL代码

<select id="getUserGroupPage"  resultType="com.xxx.service.api.response.CmsUserGroupResp">
         SELECT
             b.id,
             b.del_flag,
             b.create_time,
             b.is_delete,
             b.type,
             b.name,
             b.update_time,
             b.create_user,
             b.group_id,
             b.personnel_number
         FROM cms_user_group AS b
        where
             1 = 1
             and b.del_flag=0
             <if test="entity.userGroupOrCreator !=null and entity.userGroupOrCreator != ''">
                 <bind name="userGroupOrCreator" value="'%'+entity.userGroupOrCreator+'%'"/>
                 and b.name like #{userGroupOrCreator}
             </if>
             <if test="entity.userGroupOrCreator !=null and entity.userGroupOrCreator != ''">
                 or b.group_id = #{entity.userGroupOrCreator}
             </if>

             <if test="entity.type != null ">
                 and b.type = #{entity.type}
             </if>
         order by b.update_time desc
    </select>

 <select id="getDeliveryCount" resultType="com.xxx.service.api.response.CmsUserGroupResp">
        select
                (SELECT COUNT(*) FROM cms_push_task_user_logs AS e WHERE e.del_flag=0 AND e.user_group_id=#{groupId}) AS delivery_user_nums ,
                (SELECT COUNT(*) FROM cms_push_task_user_logs AS d WHERE d.del_flag=0 AND d.push_status = 1 AND d.user_group_id=#{groupId} ) AS total_push_user_nums
    </select>

原始的具体实现类

public PagingResult<CmsUserGroupResp> getUserGroupPage(CmsUserGroupReq req) throws CommonException {
        IPage<CmsUserGroupResp> page = new Page<>(req.getPage(),req.getPageSize());
        Page<CmsUserGroupResp> tasks = baseMapper.getUserGroupPage(page,req);
       
        return PagingUtils.getPagingResult(tasks);
    }

改造后的实现类，将计算出的delivery_user_nums和total_push_user_nums，再次放到tasks中

public PagingResult<CmsUserGroupResp> getUserGroupPage(CmsUserGroupReq req) throws CommonException {
        IPage<CmsUserGroupResp> page = new Page<>(req.getPage(),req.getPageSize());
        Page<CmsUserGroupResp> tasks = baseMapper.getUserGroupPage(page,req);
        if(!Objects.isNull(tasks.getRecords())){
             List<CmsUserGroupResp> records = tasks.getRecords();
             records.parallelStream().forEach(v->{
                 CmsUserGroupResp deliveryCount = baseMapper.getDeliveryCount(v.getGroupId());
                 v.setDeliveryUserNums(deliveryCount.getDeliveryUserNums());
                 v.setTotalPushUserNums(deliveryCount.getTotalPushUserNums());
             });
            tasks.setRecords(records);
        }
        return PagingUtils.getPagingResult(tasks);
    }

二、具体实现代码

1、CmsUserGroupResp用户组列表返回值包装类

@Data
public class CmsUserGroupResp implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty("用户表id")
    private Long id;

    @ApiModelProperty("用户组Id")
    private Integer groupId;

    @ApiModelProperty("删除状态：0-否，1-是")
    private Boolean delFlag;

    @ApiModelProperty("同步用户组删除标志（0删除 1正常）")
    private String isDelete;

    @ApiModelProperty("用户组名称")
    private String name;

    @ApiModelProperty("用户组类型:0-静态 1-动态")
    private String type;

    @ApiModelProperty("用户数量")
    private Integer personnelNumber;

    @ApiModelProperty("创建时间")
    private Date createTime;

    @ApiModelProperty("更新时间")
    private Date updateTime;

    @ApiModelProperty("创建人")
    private String createUser;

    @ApiModelProperty("送达用户数")
    private Long deliveryUserNums;

    @ApiModelProperty("总送达用户数")
    private Long totalPushUserNums;

    @ApiModelProperty("送达率")
    private String deliveryRate;

    /**
    * 计算历史送达率
    * */
    public void setTotalPushUserNums(Long totalPushUserNums) {
        this.totalPushUserNums = totalPushUserNums;
        this.setDeliveryRate();
    }

    public void setDeliveryUserNums(Long deliveryUserNums) {
        this.deliveryUserNums = deliveryUserNums;
        this.setDeliveryRate();
    }

    /**
     * 通过返回结果，计算历史送达率，省去了在SQL中复杂计算
     */
    public void setDeliveryRate() {
        if(LongUtil.isNotEmpty(deliveryUserNums)&&LongUtil.isNotEmpty(totalPushUserNums)){
            this.deliveryRate = String.format("%.2f",totalPushUserNums.doubleValue() * 100 / deliveryUserNums.doubleValue()).concat("%");
        }
    }
}

2、CmsUserGroupController

@RestController
public class CmsUserGroupController implements ICmsUserGroupController {
    @Autowired
    private CmsUserGroupService cmsUserGroupService;
    /**
     * 分页查询用户组列表
     */
     @Override
    public MthResponse<PagingResult<CmsUserGroupResp>> getUserGroupPage(CmsUserGroupReq req) throws CommonException {
        return ResponseUtils.success(cmsUserGroupService.getUserGroupPage(req));
    }
}    

3、CmsUserGroupService

public interface CmsUserGroupService extends IService<CmsUserGroup> {
   /**
     * 分页查询用户组列表
    */
    PagingResult<CmsUserGroupResp> getUserGroupPage(CmsUserGroupReq req) throws CommonException;

    /**
     * 单独查询delivery_user_nums和total_push_user_nums
     */
    CmsUserGroupResp getDeliveryCount(Integer groupId);
}

4、CmsUserGroupServiceImpl

@Service
public class CmsUserGroupServiceImpl extends ServiceImpl<CmsUserGroupMapper, CmsUserGroup>
    implements CmsUserGroupService {
    /**
     * 分页查询用户组列表
     */
     @Override
    public PagingResult<CmsUserGroupResp> getUserGroupPage(CmsUserGroupReq req) throws CommonException {
        IPage<CmsUserGroupResp> page = new Page<>(req.getPage(),req.getPageSize());
        Page<CmsUserGroupResp> tasks = baseMapper.getUserGroupPage(page,req);
        if(!Objects.isNull(tasks.getRecords())){
             List<CmsUserGroupResp> records = tasks.getRecords();
             records.parallelStream().forEach(v->{
                 CmsUserGroupResp deliveryCount = baseMapper.getDeliveryCount(v.getGroupId());
                 v.setDeliveryUserNums(deliveryCount.getDeliveryUserNums());
                 v.setTotalPushUserNums(deliveryCount.getTotalPushUserNums());
             });
            tasks.setRecords(records);
        }
        //PagingUtils是封装的工具类
        return PagingUtils.getPagingResult(tasks);
    }
}

总结

本次只是出版优化，将查询分2次，然后对两次查询的结果进行重新组装。下次优化，准备发力SQL优化。有时候换一种解决思路，往往可以收到不一样的效果。