查询时利用字典来优化速度

平时在查询时，很容易碰到在循环中再查询的情况，因为重复查询表，导致速度变慢。

以我曾做过的一个项目为例，以下是我没优化前的代码：

<?php
//...
//本次考试下的所有科目
$query = new Query();
$query->select(['es.se_course_id','bc.name'])
    ->from(['es'=>SchExamSingle::tableName()])
    ->leftJoin(['bc'=>BaseCourse::tableName()],"es.se_course_id=bc.course_id")
    ->where(['me_id'=>$me_id]);
$schSingleSubjects = $query->all();
if(empty($schSingleSubjects)){
    $this->addError(__METHOD__,'单科考试信息不存在！',ApiConst::RESPONSE_STATUS_ERROR_PARAM);
    return false;
}
$subjects = [];
foreach($schSingleSubjects as $subject){
    $subjects[$subject['se_course_id']]=$subject['name'];
}
$subQuery = new Query();
$subQuery->select(['mess.me_id','mess.mess_score','mess.mess_order','mess.student_id','mess.student_name','mess.class_id','mess.school_id','mess.school_name','bsc.name AS class_name','bsg.name as grade_name'])
    ->from(['mess'=>SchMultiExamStudentScore::tableName()])
    ->leftJoin(['bsc'=>BaseSchoolClass::tableName()],"mess.school_id=bsc.school_id AND mess.class_id=bsc.code")
    ->leftJoin(['bsg'=>BaseGrade::tableName()],"bsc.grade_id=bsg.grade_id")
    ->where(['mess.me_id'=>$me_id,'bsc.grade_id'=>$schExamMulti->me_grade_id]);
//这儿省略了一些权限判断之类的...
$subQuery->orderBy(['mess.mess_score'=>SORT_DESC]);
//$list = $subQuery->all();
$result = [];
foreach($subQuery->each() as $key=>$list){
    //foreach ($list as $key=>$val){
    $result[$key]['准考证号'] = $list['student_id'];
    $result[$key]['姓名'] = $list['student_name'];
    $result[$key]['班级'] = $list['grade_name'].$list['class_name'];
    $result[$key]['学校'] = $list['school_name'];
    foreach ($subjects as $subid => $subname){
        $query = new Query();
        $query->select(['ess.ess_score'])
            ->from(['ess'=>SchSingleExamStudentScore::tableName()])
            ->leftJoin(['es'=>SchExamSingle::tableName()],"es.se_id=ess.se_id")
            //->leftJoin(['bc'=>BaseCourse::tableName()],"bc.course_id=es.se_course_id")
            ->where(['ess.student_id'=>$list['student_id'],'ess.school_id'=>$list['school_id'],'es.se_course_id'=>$subid,'ess.me_id'=>$me_id]);
        $score = $query->one();
        $result[$key][$subname] = $score['ess_score'];
    }
    $result[$key]['总分'] = $list['mess_score'];
    $result[$key]['联考排名'] = $list['mess_order'];
}
return $result;

由上代码可见，特别是77-86行间，循环中再查询。整个接口下来最终耗时约14分钟的样子。

然后今天用了下字典：

<?php
...
# 获取年级字典 ['grade_id'=>'grade_name', ...]
$gradeDict = (new MGrade())->getGradeNameDict();
# 获取科目字典 ['course_id'=>'course_name', ...]
$courseDict = (new MCourse())->getCourseDict();
# 获取某次考试下的信息字典 ['se_id'=>['se_id','se_course_id','...']]
$singExamDict = $this->getSingExamDict($me_id);
foreach ($singExamDict as $item){
    $head[] = $courseDict[$singExamDict[$item['se_id']]['se_course_id']];
}
# 获取某次考试所有学生成绩信息
/**
 * ['student_id1'=>
 *      [
 *          'se_id1'=>[...],
 *          'se_id2'=>[...],
 *          'se_id3'=>[...],
 *          ...
 *      ]
 *  ...
 * ]
 */
$studentSingExamScoreDict = $this->getStudentSingScoreDict($me_id);
$subQuery = new Query();
$subQuery->select(['mess.me_id','mess.mess_score','mess.mess_order','mess.student_id','mess.student_name','mess.class_id','mess.school_id','mess.school_name','bsc.name AS class_name','bsc.grade_id'])
    ->from(['mess'=>SchMultiExamStudentScore::tableName()])
    ->leftJoin(['bsc'=>BaseSchoolClass::tableName()],"mess.school_id=bsc.school_id AND mess.class_id=bsc.code")
    //->leftJoin(['bsg'=>BaseGrade::tableName()],"bsc.grade_id=bsg.grade_id")
    ->where(['mess.me_id'=>$me_id,'bsc.grade_id'=>$schExamMulti->me_grade_id]);
//这儿省略了一些权限判断筛选之类的..
$subQuery->orderBy(['mess.mess_score'=>SORT_DESC]);
//$list = $subQuery->all();
$content = [];
foreach($subQuery->each() as $key=>$list){
    //foreach ($list as $key=>$val){
    $content[$key][] = $list['student_id'];
    $content[$key][] = $list['student_name'];
    # 学校
    $content[$key][] = $list['school_name'];
    # 年级班级
    if(array_key_exists($list['grade_id'],$gradeDict)){
        $gradeName = $gradeDict[$list['grade_id']];
    }else{
        $gradeName = '-';
    }
    $content[$key][] = $gradeName.$list['class_name'];
    # 单科成绩
    foreach ($singExamDict as $exam){
        $se_id = $exam['se_id']; //单科id
        $student_id = $list['student_id']; //学生id
        //$course_name = $courseDict[$exam['se_course_id']]; //课程名称
        if(isset($studentSingExamScoreDict[$student_id][$se_id])){
            $content[$key][] = $studentSingExamScoreDict[$student_id][$se_id]['ess_score'];
        }else{
            $content[$key][] = '-';
        }
    }
    $content[$key][] = $list['mess_score'];
    $content[$key][] = $list['mess_order'];
}
$result['head'] = $head;
$result['content'] = $content;
return $result;

最终下来整个接口只用到4-5分钟的样子，比之前快了3倍，还是很厉害的。
当然这个速度肯定还是慢了，还需要优化，我再想看看有没有其它好方法再说。

然后贴一下其中一个字典的代码：

<?php
/**
 * 获取学生某次考试的单科考试成绩信息
 * @param $me_id
 * @return array
 */
private function getStudentSingScoreDict($me_id)
{
    $dict = [];
    $query = new Query();
    $query->select([
        'student_id',
        'se_id',
        'ess_score',//总分
        'ess_class_order',//班级排名
        'ess_status',//状态
        'ess_grade_order',//年级排名
        'ess_order',//所有排名
        'ess_province_order',//省排名
        'ess_city_order',//市排名
        'ess_district_order',//地区排名
    ]);
    $query->from(SchSingleExamStudentScore::tableName());
    $query->where(['me_id'=>$me_id]);
    $query->orderBy(['se_id'=>SORT_ASC]);
    $res = $query->all();
    foreach ($res as $item){
        $key = $item['student_id'];
        $dict[$key][$item['se_id']] = $item;
    }
    return $dict;
}