sql查询优化

最近在学习高并发编程优化,突然想到一个场景

如何才能让 "查询出的一个列表(list)时,再对对应的单条(item)添加相关的列表" 最快

如返回如下的一个列表

@Data
public class CompanyAndCompanyAffixOutput {
    //返回公司列表
    private Company company;
    //改公司对应的附件列表 通过公司id关联
    private List<CompanyAffix> companyAffixes = null;

    public void add(CompanyAffix companyAffix) {
        if (null == companyAffixes) {
            synchronized (this) {
                if (null == companyAffixes) {
                    companyAffixes = Lists.newArrayList();
                }
            }
        }
        companyAffixes.add(companyAffix);
    }
}

凭借的以往的经验和习惯马上想到了以下3个种方法

方法1.公司列表遍历添加公司附件

方法2.公司列表sql中使用mybatis@Result中many关联公司附件sql

方法3.公司列表取出公司id 用IN语句查询再分组组装

先编写3个接口然后使用Druid Monitor统计接口响应时间,sql执行时间,sql执行次数

    方法1.公司列表遍历添加公司附件 
    /**
     * 返回公司列表
     * 公司列表遍历添加公司附件
     * @return
     */
    @ResponseBody
    @RequestMapping("/listByForeachProcess")
    public CaseRes listByForeachProcess() {
        return companyService.listByForeachProcess();
    }

    public CaseRes listByForeachProcess() {
        List<Company> companies = companyRepository.selectByAllCompany();

        Set<Integer> companyIds = companies.stream().map(Company::getId).collect(Collectors.toSet());

        //组装
        List<CompanyAndCompanyAffixOutput> outputs = new ArrayList<>();
        companies.forEach(e->{
            CompanyAndCompanyAffixOutput output=new CompanyAndCompanyAffixOutput();
            output.setCompany(e);
            output.setCompanyAffixes(companyAffixRepository.selectByCompanyIds(companyIds));
            outputs.add(output);
        });

        return CaseRes.data(outputs);
    }

    方法2.公司列表sql中使用mybatis@Result中many关联公司附件sql
    /**
     * 返回公司列表
     * 公司列表sql中使用mybatis@Result中many关联公司附件sql
     * @return
     */
    @ResponseBody
    @RequestMapping("/listByMybatisProcess")
    public CaseRes listByMybatisProcess() {
        return companyService.listByMybatisProcess();
    }

    public CaseRes listByMybatisProcess() {
        List<CompanyAndCompanyAffixOutput> outputs=companyRepository.selectByMybatisProcess();
        return CaseRes.data(outputs);
    }

    @Select({
            "select * from company",
    })
    @Results({
            @Result(column = "id", property = "company.id", jdbcType = JdbcType.INTEGER, id = true),
            //....为了观察方便省略次要字段注入
            @Result(column = "id", property = "companyAffixes", many = @Many(select = "selectListByResumeId")),
    })
    List<CompanyAndCompanyAffixOutput> selectByMybatisProcess();
    @Select({
            "select * from company_affix",
            "where company_id = #{companyId,jdbcType=INTEGER} "
    })
    @Results({
            @Result(column = "id", property = "id", jdbcType = JdbcType.BIGINT, id = true),
            @Result(column = "company_id", property = "companyId", jdbcType = JdbcType.INTEGER),
            //....为了观察方便省略次要字段注入
    })
    List<CompanyAffix> selectListByResumeId(@Param("companyId") Long companyId);

    @Select({
            "select * ",
            "from company_affix",
            "where company_id in (#{ids})"
    })
    @Results({
            @Result(column="id", property="id", jdbcType=JdbcType.BIGINT, id=true),
            @Result(column="company_id", property="companyId", jdbcType=JdbcType.INTEGER),
            //....为了观察方便省略次要字段注入
    })
    List<CompanyAffix> selectByCompanyIds(@Param("ids") String ids);

    方法3.公司列表取出公司id 用IN语句查询再分组组装
    /**
     * 返回公司列表
     * 公司列表取出公司id用IN语句查询再分组组装
     * @return
     */
    @ResponseBody
    @RequestMapping("/listByMemoryProcess")
    public CaseRes listByMemoryProcess() {
        return companyService.listByMemoryProcess();
    }

    public CaseRes listByMemoryProcess() {
        List<Company> companies = companyRepository.selectByAllCompany();

        Set<Integer> companyIds = companies.stream().map(Company::getId).collect(Collectors.toSet());
        //使用WHERE IN取出所有附件
        List<CompanyAffix> companyAffixes = companyAffixRepository.selectByCompanyIds(companyIds);

        //根据公司id分组公司附件
        Map<Integer, List<CompanyAffix>> idAffixes = companyAffixes.stream().collect(Collectors.groupingBy(CompanyAffix::getCompanyId));

        //组装
        List<CompanyAndCompanyAffixOutput> outputs = new ArrayList<>();
        companies.forEach(e -> {
            CompanyAndCompanyAffixOutput  output=new CompanyAndCompanyAffixOutput();
            output.setCompany(e);
            output.setCompanyAffixes(idAffixes.get(e.getId()));
            outputs.add(output);
        });

        return CaseRes.data(outputs);
    }

    @Select({
            "select * ",
            "from company_affix",
            "where company_id in (#{ids})"
    })
    @Results({
            @Result(column="id", property="id", jdbcType=JdbcType.BIGINT, id=true),
            @Result(column="company_id", property="companyId", jdbcType=JdbcType.INTEGER),
            //....为了观察方便省略次要字段注入
    })
    List<CompanyAffix> selectByCompanyIds(@Param("ids") String ids);

为了减少误差每个接口请求10次的到如下统计

从统计结果中得知

最快 3.公司列表取出公司id 用IN语句查询再分组组装   

中等 2.公司列表sql中使用mybatis@Result中many关联公司附件sql 

最慢 1.公司列表遍历添加公司附件

分析统计图得知1,2方法都执行了110条sql导致请求时间和是第3个方法的3倍,并且3方法有效减少读取行数

 

接口查询快总结:

1. 减少sql执行行数

2.减少读取不必要的行数

 

当然高并发查询还需要缓存来减少请求穿透到数据数据库层