MySQL与Laravel关联查询优化请求：提升加载速度

一、索引优化（核心提速手段） #

针对当前查询的过滤、关联、分组逻辑，添加以下复合索引：

1. site_products表：创建(product_id, site_id)复合索引

   CREATE INDEX idx_sp_product_site ON site_products (product_id, site_id);
   

   理由：先通过product_id快速过滤目标数据，再用site_id直接关联wp_versions表，避免全表扫描。

2. wp_versions表：创建(site_id, updated_at, version)复合索引

   CREATE INDEX idx_wp_site_updated_version ON wp_versions (site_id, updated_at, version);
   

   理由：覆盖关联条件site_id、过滤条件updated_at，以及分组字段version，实现索引覆盖查询，无需回表读取原始数据，大幅提升查询效率。

二、查询语句优化 #

1. 修正时间范围判断逻辑 #

将between的闭区间改为左闭右开的范围判断，避免遗漏毫秒级时间数据：

select `wp`.`version` as `wp_version`, count(*) as total 
from `site_products` as `sp` 
inner join `wp_versions` as `wp` on `sp`.`site_id` = `wp`.`site_id` 
where `sp`.`product_id` = 3 
  and `wp`.`updated_at` >= '2023-10-22' 
  and `wp`.`updated_at` < '2023-11-23' 
group by `wp`.`version` 
order by `total` desc

2. 可选：提前过滤wp_versions表数据 #

如果wp_versions表数据量极大，可先过滤时间范围再关联，减少关联的数据量：

select wp.version as wp_version, count(*) as total
from site_products as sp
inner join (
    select site_id, version 
    from wp_versions 
    where updated_at >= '2023-10-22' and updated_at < '2023-11-23'
) as wp on sp.site_id = wp.site_id
where sp.product_id = 3
group by wp.version
order by total desc

三、Laravel代码优化 #

1. 优化时间范围处理 #

使用Carbon类处理时间，避免手动拼接字符串，同时保证时间范围的准确性：

use Carbon\Carbon;

$startDate = Carbon::parse($dates['start'])->startOfDay();
$endDate = Carbon::parse($dates['end'])->addDay()->startOfDay(); // 等价于 < 次日00:00:00

$data['wp_vers'] = DB::table('site_products AS sp')
    ->join('wp_versions as wp', 'sp.site_id', '=', 'wp.site_id')
    ->select('wp.version as wp_version', DB::raw('count(*) as total'))
    ->where('sp.product_id', $product_id)
    ->where('wp.updated_at', '>=', $startDate)
    ->where('wp.updated_at', '<', $endDate)
    ->groupBy('wp.version')
    ->orderByDesc('total')
    ->get()
    ->toArray();

2. 可选：简化查询写法 #

用selectRaw替代select+DB::raw的组合，代码更简洁：

->selectRaw('wp.version as wp_version, count(*) as total')

3. 索引有效性验证 #

开启查询日志或使用explain分析执行计划，确认索引是否生效：

// 查看查询日志
DB::enableQueryLog();
// 执行查询后输出日志
dd(DB::getQueryLog());

// 分析执行计划
DB::table('site_products AS sp')
    ->join('wp_versions as wp', 'sp.site_id', '=', 'wp.site_id')
    ->where('sp.product_id', $product_id)
    ->where('wp.updated_at', '>=', $startDate)
    ->where('wp.updated_at', '<', $endDate)
    ->groupBy('wp.version')
    ->orderByDesc('total')
    ->explain();

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