优化Laravel查询：深入理解Left Join中where与on的性能陷阱

慢查询现象分析

在laravel应用开发中，开发者可能会遇到一个令人困惑的性能问题：一个包含leftjoin和paginate的查询在laravel中执行缓慢（例如17秒），但将其生成的sql语句直接复制到数据库管理工具（如phpmyadmin）中执行时，却能迅速完成（例如0.2秒）。这种性能差异通常指向laravel查询构建器中对sql语句的生成方式存在某种误解或不当使用。

以下是一个典型的慢查询示例：

$sql = DB::table('inventorysku_tb as isku')
    ->leftJoin('inventorytrackingmodules_tb as itm', function ($join) {
        $join->where('itm.coID', '=', 4)
             ->whereBetween('itm.effectiveDate', ['2021-05-01', '2021-05-31'])
             ->on('itm.skuID', '=', 'isku.ID');
    });

// 此时执行 paginate 会非常慢
$results = $sql->paginate(25);

通过toSql()方法检查上述Laravel代码生成的SQL语句，并手动替换问号占位符后，得到的SQL可能如下：

select *
from `inventorysku_tb` as `isku`
    left join `inventorytrackingmodules_tb` as `itm`
        on `itm`.`skuID` = `isku`.`ID`
        and `itm`.`coID` = 4
        and `itm`.`effectiveDate` between '2021-05-01' and '2021-05-31'

尽管生成的SQL看起来与在phpMyAdmin中快速执行的SQL一致，但Laravel的内部处理机制，尤其是在leftJoin的闭包中混合使用where和on时，可能会导致意想不到的行为。一个关键的观察是，如果将leftJoin改为innerJoin，查询速度会显著提升，这进一步暗示了问题可能出在leftJoin条件下where子句的处理上。

on与where在Join条件中的区别

在Laravel的查询构建器中，join方法的闭包内部，on和where方法虽然都能用于添加条件，但它们的语义和最终生成的SQL语句在某些情况下存在微妙但关键的区别。

• on方法： 专用于在ON子句中定义连接条件。它期望接收两个列名和操作符，或者一个列名、操作符和一个具体值。当使用on时，Laravel会明确将其转换为SQL的ON子句的一部分。
• where方法： 在join闭包内部，where方法通常用于对被连接的表进行额外的过滤，但其行为可能不如on那样直接和可预测，尤其是在与字面量值进行比较时。在某些复杂的场景下，Laravel可能会将其解释为额外的WHERE子句（在主查询的WHERE部分），而不是ON子句。虽然在简单情况下，where和on可能生成相同的SQL，但在leftJoin中，如果where子句用于定义连接条件且涉及字面量，它可能会导致查询优化器无法有效利用索引，或者Laravel在构建查询时未能将其完全融入ON子句，从而导致性能下降。

根据Laravel官方文档，on子句可以链式调用，例如：

$join->on('contacts.user_id', '=', 'users.id')
     ->on('contacts.info_id', '=', 'info.id');
// 这将生成：on contacts.user_id = users.id and contacts.info_id = info.id

这明确指出，所有连接条件都应通过on方法来构建。

优化方案：正确使用on方法

解决上述性能问题的关键在于确保所有连接条件都通过on方法明确地定义在ON子句中。对于涉及字面量值或范围的条件，如whereBetween，也需要将其封装在on方法内部的闭包中。

蓝心千询

蓝心千询是vivo推出的一个多功能AI智能助手

34

查看详情

以下是优化后的Laravel查询代码：

$sql = DB::table('inventorysku_tb as isku')
    ->leftJoin('inventorytrackingmodules_tb as itm', function ($join) {
        // 使用 on 定义所有连接条件
        $join->on('itm.coID', '=', DB::raw(4)) // 确保字面量值也被正确处理
             ->on('itm.skuID', '=', 'isku.ID')
             ->on(function ($query) {
                 // 将 whereBetween 封装在 on 的闭包中
                 $query->whereBetween('itm.effectiveDate', ['2021-05-01', '2021-05-31']);
             });
    });

// 此时执行 paginate 应该会显著加快
$results = $sql->paginate(25);

代码解释：

1. $join->on('itm.coID', '=', DB::raw(4)): 我们使用on来定义coID的连接条件。为了确保字面量值4被正确处理，我们使用DB::raw(4)，尽管在某些情况下直接使用4也可能有效，但DB::raw提供了一种更明确的方式来插入原始值。
2. $join->on('itm.skuID', '=', 'isku.ID'): 这是标准的列与列之间的连接条件，直接使用on。
3. $join->on(function ($query) { ... }): 对于像whereBetween这样需要更复杂逻辑的条件，我们可以将一个闭包传递给on方法。在这个闭包内部，我们使用$query对象来构建这些更复杂的条件。这样，whereBetween的逻辑就会被正确地包含在ON子句中。

通过这种方式，Laravel查询构建器将生成与phpMyAdmin中快速执行的SQL语句完全一致的ON子句，从而允许数据库优化器有效利用相关索引，显著提升查询性能。

注意事项与最佳实践

1. 验证生成的SQL： 始终使用->toSql()方法和dd()来检查Laravel实际生成的SQL语句。这是调试性能问题的最有效手段之一。

   $sqlQuery = $sql->toSql();
   $bindings = $sql->getBindings();
   dd($sqlQuery, $bindings);

   登录后复制

   然后手动将绑定值代入SQL，并在数据库管理工具中执行，以确认其性能。

2. 理解on、where、orOn、orWhere： 在join闭包中，清晰理解这些方法的用途至关重要。on和orOn用于ON子句，而where和orWhere在某些情况下可能被解释为WHERE子句。
3. 数据库索引： 确保所有参与连接的列（如skuID, coID, effectiveDate）都建立了适当的数据库索引。这是任何查询性能优化的基石。
4. paginate的影响： paginate方法本身会添加LIMIT和OFFSET子句，这通常会增加查询的开销，尤其是在大型数据集上。然而，如果基础的JOIN操作本身效率低下，paginate只会使问题更加突出。优化JOIN是解决根本问题的关键。
5. 避免过度复杂化： 尽量保持连接条件的简洁和清晰。如果连接逻辑变得异常复杂，可能需要考虑重新设计数据库模式或使用视图来简化查询。

总结

Laravel的查询构建器是一个强大且灵活的工具，但它要求开发者对SQL底层原理和Laravel的内部实现有清晰的理解。在处理leftJoin操作时，尤其需要注意on与where方法的正确使用。通过将所有连接条件，包括涉及字面量值和范围的条件，都通过on方法（或其闭包形式）来定义，我们可以确保Laravel生成高效且符合预期的SQL语句，从而避免潜在的性能陷阱，尤其是在结合paginate使用时。始终验证生成的SQL是诊断和解决此类性能问题的最佳实践。

以上就是优化Laravel查询：深入理解Left Join中where与on的性能陷阱的详细内容，更多请关注php中文网其它相关文章！