如何优化Laravel中处理千万级数据的嵌套Eloquent查询？

1. 索引优化（优先级最高，解决3000万条数据全表扫描问题） #

你的查询性能瓶颈本质是关联查询没有命中有效索引，导致EXISTS子查询对每条balances记录都要全表扫描balance_accounts，3000万条数据下计算量指数级增长。需要新增两个联合索引：

• 给balances表加联合索引 (balanceable_type, balanceable_id, id)：覆盖WHERE条件的balanceable_type过滤、关联用的balanceable_id、以及排序用的id字段，属于覆盖索引，查询时无需回表读取行数据。
• 给balance_accounts表加联合索引 (id, maincheck)：覆盖子查询的关联条件id和过滤条件maincheck，子查询可以直接通过索引返回结果，无需回表。

索引添加SQL参考（直接在数据库执行即可，不影响Eloquent代码）：

ALTER TABLE `balances` ADD INDEX idx_balances_type_id_id (`balanceable_type`, `balanceable_id`, `id`);
ALTER TABLE `balance_accounts` ADD INDEX idx_accounts_id_maincheck (`id`, `maincheck`);

2. Eloquent查询逻辑优化 #

保持Eloquent用法不变的前提下，可以调整查询写法进一步降低开销：

2.1 调整scope实现（可选，和EXISTS性能一致，写法更直观） #

如果你原来的maincheck scope用whereExists实现，可以改成JOIN写法避免子查询，性能基本一致，兼容性更好：

// 放在Balance模型中
public function scopeMaincheck($query)
{
    return $query->where('balances.balanceable_type', BalanceAccount::class) // 替换为你实际的balanceable_type值
        ->join('balance_accounts', 'balances.balanceable_id', '=', 'balance_accounts.id')
        ->where('balance_accounts.maincheck', 1) // 替换为你实际的maincheck过滤值
        ->select('balances.*'); // 避免join后字段冲突，仅查询balances表数据
}

注意：如果balance_accounts表的id是主键（唯一），不会出现重复数据，否则需要在查询末尾加->distinct()去重。

2.2 分页方式优化 #

如果你的Laravel版本 >= 8.x，推荐用cursorPaginate代替simplePaginate，游标分页基于排序字段id做分页锚点，不需要计算大偏移量，海量数据下分页性能提升非常明显：

Balance::orderBy('id', 'asc')->maincheck()->cursorPaginate(300);

如果版本较低，可以手动实现基于id的分页，每次查询时加条件where('id', '>', $lastId)，$lastId为上一页最后一条数据的id，完全避免offset开销。

2.3 冗余字段裁剪 #

如果不需要balances表的所有字段，不要用SELECT *，可以在查询时指定需要的字段，减少数据传输和内存开销：

Balance::select('id', 'amount', 'balanceable_id', 'created_at') // 按需加字段
    ->orderBy('id', 'asc')
    ->maincheck()
    ->cursorPaginate(300);

3. 效果验证 #

优化完成后可以通过以下方式确认优化效果：

// 输出带参数的原生SQL，放到数据库执行EXPLAIN确认索引是否命中
dd(
    Balance::orderBy('id', 'asc')
        ->maincheck()
        ->toRawSql()
);

执行EXPLAIN 你的原生SQL，如果key列显示你新增的两个索引，type列显示为ref或range，说明索引已经生效，查询性能会有数量级提升。

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