如何重构Laravel中未使用模型的5表关联慢DB查询

原查询性能问题分析 #

• 滥用select *：一次性查询6张表的所有字段，大量无用字段会增加数据传输和内存开销
• 前导模糊匹配失效索引：network like '%xxx%'的前导%会导致network字段的索引完全失效，只能全表扫描
• 左连接逻辑冗余：对data_bundle_category使用leftJoin后又添加了该表的非空where条件，实际等价于内连接，无谓增加了查询逻辑开销
• 缺少必要索引：关联字段、where过滤字段无索引的话，每次关联都会全表扫描，表数据量大的时候速度会指数级下降

优化步骤 #

1. 调整查询逻辑 #

• 把select *替换为仅查询业务需要的字段，减少无效数据读取
• 若业务允许，将模糊匹配改为前缀匹配like "xxx%"，可走network字段索引；若必须前后模糊匹配，建议给network字段建全文索引或者改为枚举值存储
• 把data_bundle_category的leftJoin改为join，匹配实际业务逻辑减少结果集

2. 新增数据库索引 #

以下索引是性能提升的核心，必须添加：

• data_bundles表：新建联合索引 idx_status_network_rel(status, network, vendor_id, product_id, user_id)，覆盖where过滤和关联用的所有字段
• vendors表：vendor_id设为主键/唯一索引
• ussd_strings表：给product_id新建普通索引
• products表：product_id设为主键/唯一索引
• users表：user_id设为主键/唯一索引
• data_bundle_category表：新建联合索引 idx_prod_category(product_id, data_category)，覆盖关联和过滤字段

3. 重构后的查询代码 #

// 先筛选主表再关联，大幅减少关联的数据量
$sub = DB::table('data_bundles')
    ->where('status', 0)
    // 业务允许的话请去掉前导%走索引
    ->where('network', 'like', '%' . $request->network . '%')
    // 只选主表需要用到的字段
    ->select('id', 'phone_number as data_bundles_phone_number', 'vendor_id', 'product_id', 'user_id');

$data_bundle_transaction = DB::table(DB::raw("({$sub->toSql()}) as data_bundles"))
    ->mergeBindings($sub)
    ->join('vendors', 'vendors.vendor_id', '=', 'data_bundles.vendor_id')
    ->select('vendors.需要的字段1', 'vendors.需要的字段2') // 按需添加vendor表需要的字段
    ->join('ussd_strings', 'data_bundles.product_id', '=', 'ussd_strings.product_id')
    ->addSelect('ussd_strings.需要的字段') // 按需添加ussd_strings表需要的字段
    ->join('products', 'data_bundles.product_id', '=', 'products.product_id')
    ->addSelect('products.需要的字段') // 按需添加products表需要的字段
    ->join('users', 'users.user_id', '=', 'data_bundles.user_id')
    ->addSelect('users.需要的字段') // 按需添加users表需要的字段
    ->join('data_bundle_category', 'data_bundle_category.product_id', '=', 'data_bundles.product_id')
    ->where('data_bundle_category.data_category', '=', $request->type)
    ->addSelect('data_bundle_category.需要的字段') // 按需添加分类表需要的字段
    ->first();

如果业务确实需要保留左连接、仅当分类存在时才匹配筛选条件，可以把where条件移到join的on逻辑中，避免过滤掉分类为空的行：

->leftJoin('data_bundle_category', function ($join) use ($request) {
    $join->on('data_bundle_category.product_id', '=', 'data_bundles.product_id')
         ->where('data_bundle_category.data_category', '=', $request->type);
})

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