Laravel使用join查询3000万条数据过慢，已加索引仍无效如何优化？

问题根因 #

你单独查询balances表时，数据库可直接通过条件索引筛选出分页所需的500条数据直接返回，无需全表扫描。添加join后，数据库执行计划大概率会优先完成多表关联再做筛选分页，相当于先对3000万条balances记录做全量关联，再截取前500条，因此耗时飙升。

索引优化方案 #

现有索引未命中覆盖查询场景，需调整为联合覆盖索引，避免关联时回表查询：

• balances表：将你whereBetween用到的字段、orderBy用到的字段、balanceable_id做成联合索引，格式为(where条件字段, 排序字段, balanceable_id)。该索引可直接覆盖你查询balances表的所有过滤、排序、关联需求，无需回表取数据。
• balance_accounts表：添加联合索引(id, currency_id, balance_type_id)，关联时可直接从索引中取出两个关联字段，无需回表查询整行记录。
• currencies表：添加联合索引(id, short_name)，覆盖主键关联和字段查询需求。
• balance_types表：添加联合索引(id, name)，覆盖主键关联和字段查询需求。

注意：必须保证关联字段的类型完全一致，比如balances.balanceable_id和balance_accounts.id如果一个是unsigned int、一个是普通int，会直接导致索引失效，这是最常见的隐性问题。

查询逻辑优化方案 #

可以调整查询逻辑，拆分查询避免全表关联，性能提升最明显：

1. 先单独查询balances表，按原有whereBetween、orderBy条件执行simplePaginate(500)，拿到分页数据和对应的500个balanceable_id集合。
2. 用whereIn查询balance_accounts表，同时关联currencies和balance_types，拿到对应short_name和name映射关系。
3. 手动将关联字段拼装到balances分页数据中即可。

对应Laravel代码示例：

// 第一步：分页查主表
$balances = Balances::whereBetween(/* 原有条件 */)
    ->orderBy(/* 原有排序规则 */)
    ->simplePaginate(500);

// 第二步：批量查关联数据
$balanceableIds = $balances->pluck('balanceable_id');
$accountMaps = BalanceAccount::whereIn('id', $balanceableIds)
    ->join('currencies', 'currencies.id', '=', 'balance_accounts.currency_id')
    ->join('balance_types', 'balance_types.id', '=', 'balance_accounts.balance_type_id')
    ->select('balance_accounts.id', 'currencies.short_name', 'balance_types.name')
    ->get()
    ->keyBy('id');

// 第三步：拼装数据
$balances->map(function ($item) use ($accountMaps) {
    $account = $accountMaps[$item->balanceable_id] ?? null;
    $item->short_name = $account?->short_name;
    $item->name = $account?->name;
    return $item;
});

验证手段 #

可在数据库执行EXPLAIN + 你的完整关联SQL语句，确认以下几点：

• key列显示用到了上述添加的联合索引
• rows列扫描行数接近500而非数百万级
• Extra列无Using filesort、Using temporary等耗性能标记

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