如何优化Laravel Eloquent中含大数组的WHERE NOT IN查询？

需求 #

找出所有关联person_id=1的照片，但这些照片的faces表中不存在person_id=1的记录（用于个人生活存档，非恶意用途）。

当前问题 #

现有实现先查询出约2万个person_id=1对应的photo_id数组，再通过WHERE NOT IN排除这些ID，导致查询耗时约5秒。核心原因是大数组会让数据库无法高效利用索引，同时带来额外内存开销。

数据库结构 #

• faces表：id、photo_id（已建索引）、person_id（已建索引）
• photos表：id
• people表：id
• person_photo表：photo_id（已建索引）、person_id（已建索引）

当前代码与问题SQL #

当前代码 #

$faces = Face::where('person_id',"=",1)
                ->pluck('photo_id')->toArray();
  
$photos = Photo::with('people','faces.person')
                ->whereHas('people', function($q) {
                    $q->where('people.id','=',1);
                })
                ->whereHas('faces')
                ->whereNotIn('id',$faces)
                ->paginate(21);

生成的问题SQL #

select count(*) as aggregate from `photos`
 where exists (select * from `people` inner join `person_photo` on `people`.`id` = `person_photo`.`person_id` where `photos`.`id` = `person_photo`.`photo_id` and `people`.`id` = 1)
 and exists (select * from `faces` where `photos`.`id` = `faces`.`photo_id` and `faces`.`deleted_at` is null) 
and `id` not in ('约2万个ID的大数组') and `photos`.`deleted_at` is null

EXPLAIN结果 #

优化方案 #

1. 用子查询替代WHERE NOT IN #

将两次查询合并为一次，通过子查询直接排除存在person_id=1的face的照片，避免加载大数组到内存，同时让数据库优化器利用索引高效处理：

$photos = Photo::with('people','faces.person')
    ->whereHas('people', function($q) {
        $q->where('people.id', 1);
    })
    ->whereHas('faces')
    ->whereNotIn('id', function($subquery) {
        $subquery->select('photo_id')
            ->from('faces')
            ->where('person_id', 1);
    })
    ->paginate(21);

此方案生成的SQL会用子查询替代IN列表，数据库可直接通过索引关联筛选，性能大幅提升。

2. 用LEFT JOIN + IS NULL 替代NOT IN #

这种写法性能通常比NOT IN更稳定，通过左关联需要排除的face记录，筛选关联为空的照片：

$photos = Photo::with('people','faces.person')
    ->join('person_photo', 'photos.id', '=', 'person_photo.photo_id')
    ->where('person_photo.person_id', 1)
    ->leftJoin('faces as exclude_faces', function($join) {
        $join->on('photos.id', '=', 'exclude_faces.photo_id')
            ->where('exclude_faces.person_id', 1);
    })
    ->whereNull('exclude_faces.id')
    ->whereHas('faces') // 确保照片至少包含一个face记录
    ->distinct() // 避免关联产生重复行
    ->paginate(21);

若person_photo与photos的关联不会产生重复，也可去掉distinct()改用groupBy('photos.id')。

3. 优化复合索引 #

现有索引基础上，添加以下复合索引进一步提升查询效率：

• 给faces表创建复合索引：CREATE INDEX idx_faces_person_photo ON faces(person_id, photo_id);
  子查询筛选person_id=1时可直接获取photo_id，无需回表查询。
• 给person_photo表创建复合索引：CREATE INDEX idx_person_photo_person_photo ON person_photo(person_id, photo_id);
  查询关联person_id=1的照片时，索引覆盖查询字段，无需回表。

4. 优化分页的count计算 #

原分页的count查询因处理大NOT IN列表较慢，可手动指定count值避免重复计算：

// 先构建查询逻辑
$query = Photo::whereHas('people', function($q) {
        $q->where('people.id', 1);
    })
    ->whereHas('faces')
    ->whereNotIn('id', function($subquery) {
        $subquery->select('photo_id')
            ->from('faces')
            ->where('person_id', 1);
    });

// 单独计算总数
$total = $query->count();

// 执行分页查询并传入总数
$photos = $query->with('people','faces.person')
    ->paginate(21, ['*'], 'page', request('page'), $total);

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