优化循环中的sql语句

问题描述：在很多新手开发过程中，然后也有些比较偷懒的喜欢把sql语句写在循环中，这样开发起来比较快；代码易懂简介清晰，对于访问量少的 这么写当然没有问题了，可以一旦访问量 比较高的时候那就 呵呵了...

代码如下：

    /**
     * @desc 获取评论列表 
     * @version 3.0
     * @author wzh
     * @date 2017-02-20
     */
    public function getCommentList(){
        $cat_id = (int) $this -> input -> get('cat_id');
        $cat_id_2 = (int) $this -> input -> get('cat_id_2');
        $id = (int) $this -> input -> get('id');
        $page = (int) $this -> input -> get('page');        
        $page = $page == 0 ? 1 : $page;
        $pagenum = $pagenum == 0 ? 10 : $pagenum;
        $total = DB::result_first("select count(*) from app_video_comment where video_id = $id and comment_id = 0 and is_delete = 0");
        if(!$total){
            $this -> error('暂无数据');
        }
        $data['total'] = $total;
        $data['maxpage'] = ceil($total / $pagenum);
        $data['page'] = $page;
        $start = ($page - 1) * $pagenum;
        $sql = " select id,uid,comment_count,zan_count,content,video_id,addtime from app_video_comment where video_id = $id and comment_id = 0 and is_delete = 0 ";
        $sql .= " order by comment_count desc,zan_count desc limit $start,$pagenum ";
        $list = (array) DB::fetch_array($sql);

        foreach ($list as $key => $value) {
            $user = DB::row_first("select uid,avatar,nickname from app_user where uid = '{$value['uid']}' ");
            $value['nickname'] = $user['nickname'];
            $value['avatar'] = $user['avatar'] == '' ? $this -> domain . '/static/images/defaultavatar.jpg' : $this -> domain . $value['avatar'];
            //查看是否已经赞过了
            $value['comment_status'] = (int) DB::row_first("select count(*) from app_video_comment_zan where uid = '$uid' and comment_id = '{$value['id']}' ");
        }
        $data['comment_list'] = $list;
        $this -> json_return($data);
    }
    /**
     * @desc 获取评论列表 
     * @version 3.0
     * @author wzh
     * @date 2017-02-20
     */
    public function getCommentList2(){
        $cat_id = (int) $this -> input -> get('cat_id');
        $cat_id_2 = (int) $this -> input -> get('cat_id_2');
        $id = (int) $this -> input -> get('id');
        $page = (int) $this -> input -> get('page');        
        $page = $page == 0 ? 1 : $page;
        $pagenum = $pagenum == 0 ? 10 : $pagenum;
        $total = DB::result_first("select count(*) from app_video_comment where video_id = $id and comment_id = 0 and is_delete = 0");
        if(!$total){
            $this -> error('暂无数据');
        }
        $data['total'] = $total;
        $data['maxpage'] = ceil($total / $pagenum);
        $data['page'] = $page;
        $start = ($page - 1) * $pagenum;
        $sql = " select id,uid,comment_count,zan_count,content,video_id,addtime from app_video_comment where video_id = $id and comment_id = 0 and is_delete = 0 ";
        $sql .= " order by comment_count desc,zan_count desc limit $start,$pagenum ";
        $list = (array) DB::fetch_array($sql);
        $uidArr = $commentArr = array();
        foreach ($list as $value) {
            $uidArr['uid'] = (int) $value['uid'];
            $commentArr[] = (int) $value['id'];
        }
        $uidstr = empty($uidArr) ? 0 : implode(',', $uidArr);
        $commentstr = empty($commentArr) ? 0 : implode(',',$commentArr);

        $uidList = DB::fetch_array("select uid,avatar,nickname from app_user where uid in ($uidstr)");
        $uidArr = array();
        foreach ($uidList as $value) {
            $uidArr[$value['uid']] = $value;
        }

        $commentList = DB::fetch_array("select comment_id,count(*) as cnt from app_video_comment_zan where uid = '$uid' and comment_id in ($commentstr) group by comment_id ");
        $commentArr = array();
        foreach ($commentList as $value) {
            $commentArr[$value['comment_id']] = $value['cnt'];
        }
        foreach ($list as $key => $value) {
            
            $value['nickname'] = $uidArr[$value['uid']]['nickname'];
            $value['avatar'] = $uidArr[$value['uid']]['avatar'] == '' ? $this -> domain . '/static/images/defaultavatar.jpg' : $this -> domain . $uidArr[$value['uid']]['avatar'];
            //查看是否已经赞过了
            $value['comment_status'] = (int) $commentArr[$value['comment_id']];
        }
        $data['comment_list'] = $list;
        $this -> json_return($data);
    }

 

上述代码中 尽管第二个方法比第一个方法 优化了许多，把循环查询编程了一次查询，但是还没有达到最优的效果；还可以进一步优化，目前这里就不讲了，留下思考空间