Laravel 5.6 如何优化子查询实现5万行候选人数据快速查询

场景说明 #

现有查询用于从5万行数据的候选人表中拉取候选人详情，关联岗位、区域、雇佣类型信息，本地运行耗时2-3秒，生产环境耗时过高，可通过以下方案优化：

优化方案 #

1. 补充必要索引（优先级最高） #

索引缺失是关联、过滤、排序慢的最常见原因，需添加以下索引：

• 关联字段索引：candidates(user_id)、candidates(candidate_status_id)、candidates(employment_types_id)、candidate_jobs(candidate_id)、candidate_regions(candidate_id)、role_users(user_id)
• 过滤排序联合索引：给candidates表添加联合索引(is_deleted, candidate_status_id, first_name, id)，覆盖where条件、排序、分组字段，避免全表扫描和回表查询
• 关联过滤联合索引：给role_users表添加联合索引(role_id, user_id)，关联时先过滤role_id=6的记录再做关联，减少关联数据量

2. 替换逐行执行的子查询为预聚合关联 #

原查询中岗位、区域的GROUP_CONCAT是写在SELECT字段中的子查询，每查一行候选人就会执行2次子查询，5万行就要执行10万次子查询，性能损耗极高。改为提前聚合子表再关联，仅需执行2次聚合查询：
优化后的Laravel代码示例：

// 预聚合候选人对应的岗位ID
$jobAgg = DB::table('candidate_jobs')
    ->select('candidate_id', DB::raw("GROUP_CONCAT(job_id SEPARATOR ',') as job_ids"))
    ->groupBy('candidate_id');

// 预聚合候选人对应的区域
$regionAgg = DB::table('candidate_regions')
    ->join('regions', 'candidate_regions.region_id', '=', 'regions.id')
    ->select('candidate_id', DB::raw("GROUP_CONCAT(regions.name SEPARATOR ',') as regions"))
    ->groupBy('candidate_id');

// 主查询关联预聚合结果
$candidates = DB::table('candidates')
    ->join('role_users', function($join){
        $join->on('candidates.user_id', '=', 'role_users.user_id');
        $join->where('role_users.role_id', 6);    
    })
    // 原逻辑中candidate_statuses表关联后未使用任何字段，直接移除该关联
    ->join('employment_types', 'candidates.employment_types_id', '=', 'employment_types.id')
    ->leftJoinSub($jobAgg, 'job_agg', 'candidates.id', '=', 'job_agg.candidate_id')
    ->leftJoinSub($regionAgg, 'region_agg', 'candidates.id', '=', 'region_agg.candidate_id')
    ->select(
        'candidates.id', 
        'candidates.user_id', 
        'candidates.candidate_code', 
        'candidates.full_name as name', 
        'employment_types.title AS employment_type',
        'job_agg.job_ids',
        'region_agg.regions',
        'role_users.email',
        'role_users.login_at',
        'candidates.is_deleted'
    )
    ->where('candidates.candidate_status_id', '!=' , 6)
    ->where('candidates.is_deleted', $request->is_deleted)
    ->orderBy('candidates.first_name')
    ->groupBy('candidates.id')
    ->paginate(10);

3. 移除冗余逻辑 #

• 原SQL中重复查询了两次role_users.email，删除重复字段减少数据传输开销
• 原逻辑中关联了candidate_statuses表但未使用该表的任何字段，直接删除该关联，减少一次表连接开销

4. 优化分页逻辑 #

如果前端不需要展示总条数、总页数，可将paginate(10)改为simplePaginate(10)，省去Laravel默认的总条数count查询，响应速度可提升50%以上。

5. 生产环境缓存优化 #

• 候选人列表属于低频更新数据，可将分页查询结果存入Redis，设置5-10分钟的过期时间，避免频繁请求数据库
• 若使用MySQL5.7及以下版本，可开启查询缓存，重复查询直接返回缓存结果

内容的提问来源于stack exchange，提问作者Prasanth Premzon