Laravel查询用户各相册图片数量的实现及性能问题

首先，先梳理下你已经完成的工作：你需要统计当前登录用户每个相册的图片数量，最初通过关联users_albums和albums表拿到了用户的相册基础信息，后来通过添加关联子查询的方式解决了图片数量统计的核心问题——不过这种方案在数据量上来之后确实会有性能瓶颈，咱们来一步步优化它。

先回顾你当前的可行实现 #

你之前的代码大概是这样的（整理后）：

$userId = auth()->user()->id;
$albums = DB::table('users_albums')
    ->join('albums', 'users_albums.albumId', '=', 'albums.id')
    ->where('users_albums.userId', $userId)
    ->select(
        'albums.name', 
        'albums.owner', 
        'albums.id', 
        'albums.created_at',
        DB::raw('(SELECT COUNT(I.id) FROM images I WHERE I.albumId = albums.id) as numberOfImages')
    )
    ->get();

这个方案的问题在于：关联子查询会为每一条相册记录单独执行一次COUNT查询，当用户相册数量多或者图片量大时，数据库会执行N+1次查询，性能会直线下降。

优化方案1：用JOIN + GROUP BY替代子查询 #

把关联查询和统计合并成一次SQL查询，只需要遍历数据一次就能完成统计，效率会高很多：

$userId = auth()->user()->id;
$albums = DB::table('users_albums')
    ->join('albums', 'users_albums.albumId', '=', 'albums.id')
    // 用LEFT JOIN确保没有图片的相册也会被统计（数量为0）
    ->leftJoin('images', 'albums.id', '=', 'images.albumId')
    ->where('users_albums.userId', $userId)
    ->select(
        'albums.name', 
        'albums.owner', 
        'albums.id', 
        'albums.created_at',
        DB::raw('COUNT(images.id) as numberOfImages')
    )
    // 按相册的唯一字段分组，确保每个相册只统计一次
    ->groupBy('albums.id', 'albums.name', 'albums.owner', 'albums.created_at')
    ->get();

这个方案只需要执行一次SQL查询，数据库可以利用关联和分组操作高效完成统计。

优化方案2：添加索引提升查询速度 #

不管用哪种查询方式，给关联字段添加索引都是提升性能的关键。你可以给以下字段创建索引：

-- 加速用户相册的关联查询
CREATE INDEX idx_users_albums_userid ON users_albums(userId);
CREATE INDEX idx_users_albums_albumid ON users_albums(albumId);

-- 加速图片按相册分组统计
CREATE INDEX idx_images_albumid ON images(albumId);

这些索引能让数据库快速定位到需要关联的数据，避免全表扫描，大幅提升查询速度。

优化方案3：用Eloquent关联+withCount简化代码（如果用ORM的话） #

如果你的项目用Laravel的Eloquent模型，还可以通过定义关联关系来让代码更简洁，同时自动获得性能优化：

1. 在User模型中定义相册关联：

public function albums()
{
    return $this->belongsToMany(Album::class, 'users_albums', 'userId', 'albumId');
}

2. 在Album模型中定义图片关联：

public function images()
{
    return $this->hasMany(Image::class, 'albumId');
}

3. 查询时直接用withCount统计图片数量：

$albums = auth()->user()->albums()
    ->select('name', 'owner', 'id', 'created_at')
    ->withCount('images')
    ->get();

withCount会自动生成高效的GROUP BY查询，和手动写JOIN的性能差不多，但代码更易读、易维护。

总结 #

优先选择JOIN+GROUP BY或者Eloquent withCount的方案替代关联子查询，再配合添加必要的索引，就能很好地解决性能问题。

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