前景提示

遇到一个分页查询的问题，我需要做一个查询的接口，数据需要从多个表中查询，这里分为A,B,C表，我需要先从A表中查询出满足条件的数据，然后根据A中的外键去B和C表查询，但是！，A中数据和B，C表的数据是一对多的关系，所以这个外键是有重复的，我不能直接在A表的SQL中进行分页，因为这样在去重后，数据条数是不足的，我首先用了呆瓜模式，先把A中符合条件的数据全查出来，然后去重后手动分页，但是问题就来了，数据量很大的情况下，无论是查询还是去重都是比较慢的，所以经过我的不断努力，用了一条SQL完成了查询。

问题解决

手动分页代码

public Pager<PrepareRecordSkuVo> getSkuByparam(PrepareRecordSkuParam param) {
       if (null == param ) {
           return Pager.getPager(1, 10);
       }

       //1. 根据参数查询 准入SKu列表
       //1.1 设置查询model
       //1.2 查询 明细表 里面有重复的SkuNo
        List<ProductPrepareCommitRecordDetailModel> allDetailMdList = this.myDAO.getByParam(param);

       // 去重
       List<ProductPrepareCommitRecordDetailModel> validList = allDetailMdList.stream()
               .filter(detail -> detail != null && detail.getSkuNo() != null)
               .collect(Collectors.collectingAndThen(
                       Collectors.toCollection(() -> new TreeSet<>(
                               Comparator.comparing(ProductPrepareCommitRecordDetailModel::getSkuNo)
                       )),
                       ArrayList::new
               ));


       // 手动分页
       int pageNum = param.getPageNum();
       int pageSize = param.getPageSize();
       int fromIndex = (pageNum - 1) * pageSize;
       int toIndex = Math.min(fromIndex + pageSize, validList.size());
       List<ProductPrepareCommitRecordDetailModel> detailMdList =
               validList.subList(fromIndex, toIndex);
       // 获取SKU编号集合
       List<String> skuNos = detailMdList.stream()
               .map(ProductPrepareCommitRecordDetailModel::getSkuNo)
               .collect(Collectors.toList());
       // 查询sku 信息
      List<ProductSkuModel> skuMdList = productSkuService.getSkuAndAuditBySkuNoList(skuNos);
       Map<String, ProductSkuModel> skuMap = skuMdList
               .stream()
               .collect(Collectors.toMap(ProductSkuModel::getSkuNo, Function.identity()));

       // 整合返回数据
       List<PrepareRecordSkuVo> skuList = new ArrayList<>();
       for (ProductPrepareCommitRecordDetailModel detailMd : detailMdList) {
           PrepareRecordSkuVo skuVo = new PrepareRecordSkuVo();
           skuVo.setRecordNo(detailMd.getRecordNo());
           skuVo.setCategoryName(detailMd.getCategoryName());
           skuVo.setCategoryNo(detailMd.getCategoryNo());
           skuVo.setSpuNo(detailMd.getSpuNo());
           skuVo.setSkuNo(detailMd.getSkuNo());
           skuVo.setUnit(detailMd.getMainUnit());
           ProductSkuModel productSkuMd = skuMap.get(detailMd.getSkuNo());
           skuVo.setSpecDetail(productSkuMd.getSpecDetailStr());
           skuVo.setBrandName(productSkuMd.getBrandName());
           skuVo.setCreateOpeTime(detailMd.getCreateOpeTime());
           skuVo.setPrepareType(productSkuMd.getPrepareType());
           skuList.add(skuVo);
       }
       // 构造 Pager
       Pager<PrepareRecordSkuVo> pager = new Pager<>();
       pager.setResults(skuList);
       pager.setTotalNum(skuList.size());
       pager.setNowPage(param.getPageNum());
       pager.setPageShow(param.getPageSize());
       return pager;
   }

SQL连表查询后的方法

public Pager<PrepareRecordSkuVo> getSkuByparam(PrepareRecordSkuParam param) {
     if (null == param ) {
         return Pager.getPager(1, 10);
     }
     // 数据量大，改成用sql连表查
     List<PrepareRecordSkuVo> skuList = this.myDAO.getSkuByParamWithSkuInfo(param);
         // 构造 Pager
     Pager<PrepareRecordSkuVo> pager = new Pager<>();
     pager.setResults(skuList);
     pager.setTotalNum(skuList.size());
     pager.setNowPage(param.getPageNum());
     pager.setPageShow(param.getPageSize());
     return pager;

SQL改造

SELECT t.recordNo,
     t.categoryName,
     t.categoryNo,
     t.spuNo,
     t.skuNo,
     t.mainUnit AS unit,
     ps.specDetail,
     ps.brandName,
     ps.prepareType,
     t.createOpeTime
     FROM (
     SELECT skuNo,
     MIN(recordNo) AS recordNo,
     MIN(categoryName) AS categoryName,
     MIN(categoryNo) AS categoryNo,
     MIN(spuNo) AS spuNo,
     MIN(mainUnit) AS mainUnit,
     MIN(createOpeTime) AS createOpeTime
     FROM product_prepare_commit_record_detail
     <where>
         skuNo IS NOT NULL
         <if test="param.recordNo != null">
             AND recordNo = #{param.recordNo}
         </if>
         <if test="param.spuNo != null">
             AND spuNo = #{param.spuNo}
         </if>
         <if test="param.skuNo != null">
             AND skuNo = #{param.skuNo}
         </if>
         <if test="param.createOpeTime != null">
             AND createOpeTime &gt;= #{param.createOpeTime}
         </if>
         <if test="param.createOper != null">
             AND createOper = #{param.createOper}
         </if>
         <if test="param.brandName != null">
             AND brandName = #{param.brandName}
         </if>
     </where>
     GROUP BY skuNo
     ) t
     LEFT JOIN (
     SELECT skuNo, specDetail, brandName, prepareType
     FROM product_sku
     UNION ALL
     SELECT skuNo, specDetail, brandName, prepareType
     FROM product_sku_audit
     ) ps ON t.skuNo = ps.skuNo
     ORDER BY t.skuNo
     LIMIT #{pageSize} OFFSET #{offset}

复盘知识

UNION 和 UNION ALL 用来合并两个或多个 SELECT 查询的结果集，要求每个 SELECT 查询的列数和列类型必须一致。

操作	功能	去重情况	性能
`UNION`	合并结果集，并自动去掉重复行	会去重	较慢（需要排序去重）
`UNION ALL`	合并结果集，不去重	不去重	快（直接合并）

1
2
3

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

总结

在数据量较小1w以内可以使用java代码手动分页。在数据量很大很大之后使用java代码手动分页会消耗很多内存，此时使用数据库性能会较快一些。
下课！