如何优化Laravel关联查询性能？现有查询加载耗时2秒

我在Laravel中使用以下查询统计最近10个事件里访客的性别及对应人数：

$visitors = Visitor::select('visitors.sex', 'event_visitor.event_id', DB::raw('count(*) as num_visits'))
    ->join('event_visitor', 'visitors.id', '=', 'event_visitor.visitor_id')
    ->whereIn('event_visitor.event_id', $events_id->take(10))
    ->groupBy('visitors.sex', 'event_visitor.event_id')
    ->get();

查询结果示例：

sex   event_id num_visits   
female  1       10056
male    1        9965
female  2        9894
male    2        9894

目前visitors表有60万条数据，events表20条数据，每个事件关联3万位访客，当前查询耗时约2秒。对应的MySQL原生查询语句如下：

select visitors.sex,
       event_visitor.event_id,
       count(*) as num_visits 
from visitors 
inner join event_visitor on visitors.id = event_visitor.visitor_id 
where event_visitor.event_id in (1,2,3,4,5,6,7,8,9,10) 
and visitors.deleted_at is null 
group by visitors.sex, event_visitor.event_id

从查询执行计划来看，当前查询可能存在全表扫描或回表查询的情况，导致性能瓶颈。以下是几种可行的优化方案：

优化方案 #

1. 添加组合索引，消除全表扫描与回表 #

• 给event_visitor表创建(event_id, visitor_id)组合索引：该索引能快速定位目标事件的所有关联访客ID，避免对event_visitor表的全表扫描。

  CREATE INDEX idx_event_visitor_event_visitor ON event_visitor(event_id, visitor_id);
  

• 给visitors表创建(id, deleted_at, sex)覆盖索引：查询需要通过id关联、筛选deleted_at IS NULL并获取sex字段，这个索引让数据库直接从索引中读取所需数据，无需回表查询原数据行。

  CREATE INDEX idx_visitors_id_deleted_sex ON visitors(id, deleted_at, sex);
  

2. 调整查询逻辑，提前缩小数据范围 #

先从event_visitor中筛选出目标事件的访客关联数据，再关联visitors表统计，减少关联的数据量：
Laravel代码调整示例：

// 先获取目标事件的访客关联记录
$eventVisitorSub = EventVisitor::select('visitor_id', 'event_id')
    ->whereIn('event_id', $events_id->take(10));

// 关联统计性别
$visitors = Visitor::select('visitors.sex', 'ev.event_id', DB::raw('count(*) as num_visits'))
    ->joinSub($eventVisitorSub, 'ev', function ($join) {
        $join->on('visitors.id', '=', 'ev.visitor_id');
    })
    ->whereNull('visitors.deleted_at')
    ->groupBy('visitors.sex', 'ev.event_id')
    ->get();

对应的原生SQL：

SELECT v.sex, ev.event_id, COUNT(*) AS num_visits
FROM (
    SELECT visitor_id, event_id 
    FROM event_visitor 
    WHERE event_id IN (1,2,3,4,5,6,7,8,9,10)
) ev
JOIN visitors v ON ev.visitor_id = v.id
WHERE v.deleted_at IS NULL
GROUP BY v.sex, ev.event_id;

3. 引入缓存，减少重复查询 #

如果统计数据不需要实时更新，可将查询结果缓存，降低数据库压力：

use Illuminate\Support\Facades\Cache;

$visitors = Cache::remember('event_visitor_sex_stats', 3600, function () use ($events_id) {
    return Visitor::select('visitors.sex', 'event_visitor.event_id', DB::raw('count(*) as num_visits'))
        ->join('event_visitor', 'visitors.id', '=', 'event_visitor.visitor_id')
        ->whereIn('event_visitor.event_id', $events_id->take(10))
        ->groupBy('visitors.sex', 'event_visitor.event_id')
        ->get();
});

4. 优化MySQL配置 #

调整MySQL的内存配置，比如增大innodb_buffer_pool_size，让数据库能缓存更多索引和数据，减少磁盘IO操作，提升查询效率。

内容的提问来源于stack exchange，提问作者Abdoullah al-hajj