Laravel应用SQL查询远慢于控制台执行的原因及性能评估

项目控制器中编写的Laravel Eloquent查询逻辑如下：

'students' => Auth::user()->site 
    ->students()
    ->leftJoin('student_meals as m', function ($join) {
        $join->on('m.student_id', '=', 'students.id')
            ->where('date_served', Request::only( 'serviceDate') )
            ->where('meal_type_id', Request::only( 'mealType'));
        })
    ->where('enter_date', '<=',Request::only( 'serviceDate') )
    ->where('exit_date', '>=',Request::only( 'serviceDate') )
    ->OrderByName()
    ->filter(Request::only('search', 'serviceDate', 'grade', 'hr'))
    ->select('students.id as studentId', 'first_name', 'last_name', 'grade', 'hr', 'students.sfa_id as sfaId', 'students.site_id as siteId', 'sisId', 'm.id as mealId', 'm.meal_type_id', 'm.created_at as created_at', 'void')
    ->get()
    ->map(fn ($students) => [
        'studentId' => $students->studentId,
        'name' => $students->name,
        'grade' => $students->grade,
        'hr' => $students->hr,
        'sfaId' => $students->sfaId,
        'siteId' => $students->siteId,
        'sisId' => $students->sisId,
        'mealId' => $students->mealId,
        'mealType' => $students->meal_type_id,
        'created' => $students->created_at,
        'voided' => $students->void
    ]),    

上述代码生成的原生SQL如下：

SELECT `students`.`id` as `studentId`, `first_name`, `last_name`, `grade`, `hr`, `students`.`sfa_id` as `sfaId`, `students`.`site_id` as `siteId`, `sisId`, `m`.`id` as `mealId`, `m`.`meal_type_id`, `m`.`created_at` as `created_at`, `void` 
FROM `students` 
left join `student_meals` as `m` on `m`.`student_id` = `students`.`id` and `date_served` = '2022-08-15' and `meal_type_id` = '2' 
WHERE `students`.`site_id` = 12 and `students`.`site_id` IS not NULL 
and `enter_date` <= '2022-08-15' and `exit_date` >= '2022-08-15' 
and `students`.`deleted_at` IS NULL

当前业务库中students表有超1万条记录，student_meals表有超100万条记录。测试发现同一条SQL在Sequel Pro客户端执行仅耗时约37ms，但应用中Clockwork统计的数据库层耗时超过1秒（已确认统计口径仅包含数据库执行时间，不含PHP侧数据处理耗时）。

1. 客户端与应用执行同一条SQL耗时差异巨大的原因 #

两者耗时差这么多，基本逃不过这几个原因，按出现概率从高到低：

• 结果集拉取策略差异：绝大多数数据库客户端默认开启分页拉取，比如Sequel Pro默认只拉取前1000条结果显示，不会把所有符合条件的行全部传输到本地；而代码里调用->get()是拉取所有匹配的学生记录，如果符合条件的学生有几百上千条，全量数据从数据库传输到应用的网络开销会被完整统计，这部分耗时客户端是没有的。
• 连接开销差异：Sequel Pro这类客户端建立连接后会一直复用长连接，没有重复建连成本；而Laravel默认每次请求都会新建数据库连接（除非手动开启持久连接），TCP握手、数据库权限校验、会话参数初始化的开销很容易冲到几百毫秒，这部分开销在客户端测试时不会体现。另外如果应用和数据库不在同网段、走了公网或者数据库代理，网络延迟会进一步放大这部分差距。
• 缓存命中差异：在客户端反复执行同一条SQL时，很容易命中InnoDB缓冲池的热数据，甚至是MySQL查询缓存，速度会越来越快；而应用侧如果是冷启动、或者之前没有执行过相同查询，需要从磁盘加载索引和数据到内存，冷查询耗时自然高很多。另外很多客户端的耗时统计只算SQL执行时间，不算结果集网络传输时间，和Clockwork的统计口径本身就不一致。
• 实际执行SQL不一致：不要完全相信Eloquent打印的SQL，应用实际执行时会走PDO预处理流程（prepare -> bind -> execute三步），部分场景下还会自动附加会话级参数，和客户端直接拼接裸字符串执行的流程不完全一致，耗时自然有差。可以打开MySQL的general日志抓应用实际发送的完整语句，和客户端执行的语句做对比，大概率能找到细微差异。

2. 37ms耗时的性能水平判断与优化建议 #

• 性能水平判断：37ms的执行速度在百万级数据量下只能算及格，不算优秀。如果是后台低频次使用的管理端接口，这个速度完全够用，不用额外折腾；但如果是面向高频访问场景、或者接口QPS超过50，这个查询很容易成为数据库瓶颈，建议做针对性优化。
• 具体优化方向：
  • 先补索引：给student_meals表建联合索引(student_id, date_served, meal_type_id)，覆盖join条件，避免join时回表；给students表建联合索引(site_id, deleted_at, enter_date, exit_date)，直接通过索引过滤符合条件的学生，不用回表拉取学生数据。优化后正常这条SQL的耗时可以压到10ms以内。
  • 修复代码bug：你现在直接把Request::only()的返回值传给where条件是有隐患的，Request::only()返回的是关联数组，不是具体的字段值，当前SQL能正常执行是数组转字符串的巧合，后续参数变更时很容易出SQL错误，建议改成Request::input('xxx')获取具体的标量值。
  • 如果结果集特别大，可以考虑用游标查询->cursor()代替->get()，减少PHP侧内存占用的同时，也能降低全量结果拉取的等待开销。

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