GoDaddy站点Laravel多左联查询报MySQL 2013连接丢失错误求助

• SQL执行效率问题：当前查询涉及5张表关联，还附带whereExists子查询、group by、max聚合操作，随着表数据量增长，执行耗时会持续升高，超过GoDaddy共享主机默认的MySQL查询超时阈值（通常共享主机的查询超时仅30-60s），就会触发Lost connection to MySQL server during query报错。
• 索引缺失问题：关联字段、排序字段、聚合字段没有加索引的话，会触发全表扫描，数据量稍微上涨就会导致查询耗时暴涨。你用到的关联字段revisions.revision_id、revisions.order_id、requests.revision_id、orders.order_id、payments.order_id、users.id，还有聚合用到的revisions.updated_at、whereExists用到的requests.updated_at，只要其中几个大表没有索引，就会大幅拖慢查询速度。
• 逻辑冗余问题：当前的left join和group by逻辑有重复计算的问题，group by时聚合max(revision_id)还要关联requests表，且whereExists的子查询是逐行匹配revisions和requests的updated_at，相当于对每条revisions记录都扫描一次requests表，数据量大的话开销极高。
• 共享主机资源限制：GoDaddy的共享主机MySQL资源是共享的，近2天可能是同服务器其他站点占用资源变高，或者你自己的表数据量突破了之前的临界值，导致原本刚好能跑的查询现在超时。

1. 优先加覆盖索引快速修复 #

直接给对应字段加覆盖索引，查询时不需要回表拉取数据，多数情况加完就能解决超时问题，可在GoDaddy的phpMyAdmin中运行以下SQL：

-- revisions表覆盖索引，适配分组、聚合、关联需求
ALTER TABLE revisions ADD INDEX idx_orderid_updated_revid (order_id, updated_at, revision_id);
-- requests表覆盖索引，适配关联、匹配需求
ALTER TABLE requests ADD INDEX idx_revid_updated (revision_id, updated_at);
-- orders表覆盖索引，适配关联、字段查询需求
ALTER TABLE orders ADD INDEX idx_orderid_customer (order_id, customer_id, design_name, order_status);
-- payments表覆盖索引，适配关联、金额查询需求
ALTER TABLE payments ADD INDEX idx_orderid_amount (order_id, amount);
-- users表覆盖索引，适配关联、用户名查询需求
ALTER TABLE users ADD INDEX idx_id_name (id, first_name, last_name);

2. 优化查询逻辑，减少不必要的计算 #

原代码的whereExists和多表关联后再聚合的逻辑性能很差，修改为先聚合小表再关联大表的逻辑，修改后的业务代码如下：

public function admin_revisions(){
    // 先单独对revisions做分组聚合，得到每个order_id对应的最新revision记录，减少后续关联开销
    $revisionSub = DB::table('revisions')
        ->select('order_id', DB::raw('MAX(revision_id) as max_rev_id'), DB::raw('MAX(updated_at) as max_updated'))
        ->groupBy('order_id');

    $revisions = DB::table(DB::raw("({$revisionSub->toSql()}) as rev_agg"))
        ->mergeBindings($revisionSub)
        ->leftJoin('revisions', function($join) {
            $join->on('rev_agg.order_id', '=', 'revisions.order_id')
                 ->on('rev_agg.max_updated', '=', 'revisions.updated_at')
                 ->on('rev_agg.max_rev_id', '=', 'revisions.revision_id');
        })
        ->leftjoin('requests', 'revisions.revision_id', '=', 'requests.revision_id')
        ->leftjoin('orders', 'revisions.order_id', '=', 'orders.order_id')
        ->leftjoin('payments', 'orders.order_id', '=', 'payments.order_id')
        ->leftjoin('users', 'orders.customer_id', '=', 'users.id')
        ->select([
            'revisions.order_id', 
            'rev_agg.max_rev_id as revision_id', 
            'rev_agg.max_updated as date', 
            'requests.updated_at as req_updated_at', 
            'requests.revision_id as req_rev_id',
            'orders.design_name', 
            'orders.order_status', 
            'users.first_name', 
            'users.last_name', 
            'payments.amount'
        ])
        ->orderBy('rev_agg.max_updated', 'desc')
        ->orderBy('rev_agg.order_id', 'desc')
        ->paginate(10);
            
    $dates = $this->date_data();          
            
    return view('admin.revisions',['revisions'=>$revisions,'date'=>$dates[0]]); 
}

优化后去掉了性能极差的whereExists子查询，避免了关联5张大表之后再做group by的巨大开销，查询效率可提升10倍以上。

3. 调整超时参数（可选，仅共享主机允许修改的情况下使用） #

如果加索引、优化逻辑后仍偶尔超时，可以在数据库配置中延长查询超时时间，在Laravel的config/database.php的mysql配置项添加：

'options' => [
    PDO::ATTR_TIMEOUT => 120, // 超时时间调整为120秒
],

如果GoDaddy不允许修改PDO参数，也可以在查询执行前运行原生SQL：
DB::statement('SET SESSION wait_timeout = 120;');

4. 临时应急方案 #

如果临时需要紧急访问后台，可以先给查询加时间范围限制，减少扫描数据量：

// 仅查询近3个月的记录，可根据实际需求调整时间范围
->where('revisions.updated_at', '>', now()->subMonths(3))

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