提高查询效率

最新推荐文章于 2022-12-16 15:03:16 发布

最新推荐文章于 2022-12-16 15:03:16 发布 · 130 阅读

文章标签：

#优化查询

数据库同时被 2 个专栏收录

18 篇文章

订阅专栏

mysql

7 篇文章

订阅专栏

本文介绍了一种通过减少重复SQL查询来优化应用性能的方法。通过对批量数据进行一次查询，而非针对每个元素单独查询的方式，显著降低了数据库负载并提高了程序运行效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

原来的:

 List<Convention> conventions = test2Boy.getConventions();
            for (Convention convention : conventions) {
                //因为在html中\n不会换行,所以要把\n转化为br
                convention.setAnswer(ConventionUtil.convertBr(convention.getAnswer()));
                VoteLog voteLogTmp=this.voteLogDao.get( "user.id", user2.getId(),"convention.id",convention.getId());
                if(null!=voteLogTmp){
                    convention.setHasStar(true);
                }
            }

conventions 有多少个元素,就得执行多少次sql语句

优化:

 List<Convention> conventions = test2Boy.getConventions();
            int size=conventions.size();
            Object[]objects=new Object[size];
            for (int i=0;i<size;i++){
                Convention convention=conventions.get(i);
                objects[i]=convention.getId();
            }
            List voteLogTmps=this.voteLogDao.getList("user.id", user2.getId(),"convention.id",objects);
            int voteLogSize=voteLogTmps.size();
            for (int j=0;j<voteLogSize;j++){
                VoteLog voteLogTmp=(VoteLog)voteLogTmps.get(j);
                Convention convention=voteLogTmp.getConvention();
                if(null!=convention){
                    convention.setHasStar(true);
                }
            }

执行的sql语句为:

/* criteria query */ select
        this_.id as id1_8_2_,
        this_.conventionId as conventi4_8_2_,
        this_.status as status2_8_2_,
        this_.userId as userId5_8_2_,
        this_.vote_time as vote3_8_2_,
        convention2_.id as id1_2_0_,
        convention2_.answer as answer2_2_0_,
        convention2_.pic as pic3_2_0_,
        convention2_.stars as stars4_2_0_,
        convention2_.status as status5_2_0_,
        convention2_.update_time as update6_2_0_,
        user3_.id as id1_7_1_,
        user3_.email as email2_7_1_,
        user3_.level as level3_7_1_,
        user3_.nickname as nickname4_7_1_,
        user3_.password as password5_7_1_,
        user3_.potrait as potrait6_7_1_,
        user3_.username as username7_7_1_ 
    from
        t_vote_log this_ 
    left outer join
        t_convention convention2_ 
            on this_.conventionId=convention2_.id 
    left outer join
        t_user user3_ 
            on this_.userId=user3_.id 
    where
        this_.userId=? 
        and this_.conventionId in (
            ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
        )