优化Eloquent查询：如何高效获取数据库统计数据

核心优化思路 #

原代码执行了7次独立数据库查询，我们可以通过合并聚合查询将查询次数降至2次，大幅减少数据库交互开销；同时提前计算固定时间参数，避免重复计算。

优化后的代码实现 #

// 提前计算去年年份，避免重复调用时间函数
$lastYear = now()->subYear()->year;

// 一次性获取Product表的两个统计值
$productStats = Product::selectRaw("
    COUNT(CASE WHEN status = 1 THEN 1 END) as products_count,
    COUNT(CASE WHEN YEAR(created_at) = ? THEN 1 END) as last_year_products_count
", [$lastYear])->first();

// 一次性获取OrderItem表的所有统计值
$orderItemStats = OrderItem::selectRaw("
    COUNT(DISTINCT product_id) as order_item_unique_count,
    COUNT(DISTINCT CASE WHEN YEAR(created_at) = ? THEN product_id END) as last_year_order_item_unique_count,
    SUM(CASE WHEN YEAR(created_at) = ? THEN quantity END) as last_year_order_items_count,
    AVG(quantity) as order_products_avg,
    AVG(CASE WHEN YEAR(created_at) = ? THEN quantity END) as last_year_order_products_avg
", [$lastYear, $lastYear, $lastYear])->first();

// 合并统计结果
$stats = [
    'products_count' => $productStats->products_count,
    'last_year_products_count' => $productStats->last_year_products_count,
    'last_year_order_items_count' => $orderItemStats->last_year_order_items_count,
    'order_item_unique_count' => $orderItemStats->order_item_unique_count,
    'last_year_order_item_unique_count' => $orderItemStats->last_year_order_item_unique_count,
    'order_products_avg' => $orderItemStats->order_products_avg,
    'last_year_order_products_avg' => $orderItemStats->last_year_order_products_avg,
];

额外性能建议 #

• 为products.status、products.created_at、order_items.created_at、order_items.product_id字段添加联合索引，进一步提升聚合查询的执行速度
• 如果使用MySQL 8.0+，可以用created_at BETWEEN ? AND ?替换YEAR(created_at) = ?，传入去年的起始和结束时间（如$lastYearStart = now()->subYear()->startOfYear(); $lastYearEnd = now()->subYear()->endOfYear();），这样能更好地利用created_at字段的索引

内容的提问来源于stack exchange，提问作者Yannick Dirbè