Laravel/MySQL 8聚合数据过滤的实现方案咨询

背景与需求 #

• 项目存在StatisticAggregate模型，存储按小时/日/周聚合的total_submits等数据，通过多态关联绑定Company、Buyer、BuyerTier三个层级模型（层级关系：BuyerTier → Buyer → Company）
• 提交数据时，会为每个层级的对应周期生成StatisticAggregate记录，例如单条提交会为关联的Company生成total_submits=2的记录（对应2个下属Buyer）
• 前端以嵌套表格展示报表，当过滤特定BuyerTier时，需动态计算对应Company的total_submits聚合值，禁止手动循环求和；数据库日均300万条数据，必须保证查询效率
• 受多租户全局逻辑限制，无法直接给statistic_aggregates表添加company_id等外键，需通过层级模型关联实现过滤后的高效聚合计算

数据表结构 #

Schema::create('statistic_aggregates', function (Blueprint $table) {
    $table->ulid('id')->primary();
    $table->tinyInteger('for');
    $table->tinyInteger('product');
    $table->tinyInteger('country');
    $table->unsignedInteger('bucket');
    $table->mediumInteger('period')->default(60);
    $table->morphs('modelable');
    $table->char('key_hash', 32)->virtualAs("
        MD5(CONCAT(
            `for`, '',
            `product`, '',
            `country`, '',
            `bucket`, '',
            `period`, '',
            `modelable_type`, '',
            `modelable_id`, '',
            DATE_FORMAT(`bucket_starts_at`, '%Y-%m-%d %H:%i:%s'), '',
            DATE_FORMAT(`bucket_ends_at`, '%Y-%m-%d %H:%i:%s')
        ))
    ");

    $table->bigInteger('total_submits')->default(0);
    $table->dateTime('bucket_starts_at');
    $table->dateTime('bucket_ends_at');
    $table->timestamps();

    $table->index('bucket_starts_at'); // For date filtering...
    $table->index('bucket_ends_at'); // For date filtering...
    $table->index('created_at'); // For fast sorting...
    $table->index('key_hash'); // For upserts records...
    $table->index('period'); // For period categorisation...

    // For duplicate rows...
    $table->unique([
        'for',
        'product',
        'country',
        'bucket',
        'period',
        'modelable_type',
        'modelable_id',
        'bucket_starts_at',
        'bucket_ends_at',
        'key_hash'
    ], 'unique_statistic_aggregates_index');
});

方案1：层级关联子查询聚合 #

思路 #

通过Laravel模型关联，用子查询定位目标BuyerTier对应的Company，再直接对Company层级的统计记录做聚合计算，避免跨层级全表扫描。

代码示例 #

首先确保各层级模型的关联关系正确：

// BuyerTier.php
public function buyer()
{
    return $this->belongsTo(Buyer::class);
}

// Buyer.php
public function company()
{
    return $this->belongsTo(Company::class);
}

// StatisticAggregate.php
public function modelable()
{
    return $this->morphTo();
}

然后编写查询逻辑：

$targetTierId = '你的BuyerTier ID';

$companyStats = StatisticAggregate::query()
    ->where('modelable_type', Company::class)
    ->whereIn('modelable_id', function ($subQuery) use ($targetTierId) {
        $subQuery->select('companies.id')
            ->from('buyer_tiers')
            ->join('buyers', 'buyer_tiers.buyer_id', '=', 'buyers.id')
            ->join('companies', 'buyers.company_id', '=', 'companies.id')
            ->where('buyer_tiers.id', $targetTierId);
    })
    ->groupBy('bucket_starts_at', 'period')
    ->selectRaw('bucket_starts_at, period, SUM(total_submits) as calculated_total_submits')
    ->get();

性能优化 #

• 给buyer_tiers.buyer_id、buyers.company_id添加普通索引，确保子查询秒级返回
• 依赖已有的bucket_starts_at、period索引，加速分组过滤

方案2：预缓存层级映射关系 #

思路 #

由于层级关系（BuyerTier→Buyer→Company）相对稳定，可提前将每个BuyerTier对应的Company ID缓存到Redis，查询时直接用缓存的ID集合过滤StatisticAggregate，避免每次查询都走三层关联。

代码示例 #

1. 定时更新缓存（可通过Laravel任务调度实现）：

$tierCompanyMap = BuyerTier::query()
    ->join('buyers', 'buyer_tiers.buyer_id', '=', 'buyers.id')
    ->join('companies', 'buyers.company_id', '=', 'companies.id')
    ->pluck('companies.id', 'buyer_tiers.id')
    ->toArray();

// 多租户场景需给缓存键添加租户标识，如"tenant_{$tenantId}_tier_company_map"
Redis::set('tier_company_map', json_encode($tierCompanyMap));

2. 查询时直接使用缓存：

$targetTierId = '你的BuyerTier ID';
$tierCompanyMap = json_decode(Redis::get('tier_company_map'), true);
$targetCompanyId = $tierCompanyMap[$targetTierId] ?? null;

if ($targetCompanyId) {
    $companyStats = StatisticAggregate::query()
        ->where('modelable_type', Company::class)
        ->where('modelable_id', $targetCompanyId)
        ->groupBy('bucket_starts_at', 'period')
        ->selectRaw('bucket_starts_at, period, SUM(total_submits) as calculated_total_submits')
        ->get();
}

性能优化 #

• 缓存有效期根据层级变更频率设置，比如1小时更新一次
• 多租户场景下，缓存键必须带上租户标识，避免数据串扰

方案3：数据库虚拟列+索引优化 #

思路 #

虽然不能直接加company_id外键，但可以添加虚拟列存储modelable对应的顶级Company ID，并给虚拟列加索引，直接通过虚拟列过滤查询，大幅提升效率。

代码示例 #

1. 添加虚拟列和索引（需MySQL 5.7+或其他支持虚拟列的数据库）：

Schema::table('statistic_aggregates', function (Blueprint $table) {
    // 多租户场景需在子查询中添加tenant_id过滤条件
    $table->unsignedBigInteger('company_id')->virtualAs("
        CASE 
            WHEN modelable_type = 'App\\Models\\Company' THEN modelable_id
            WHEN modelable_type = 'App\\Models\\Buyer' THEN (SELECT company_id FROM buyers WHERE id = modelable_id)
            WHEN modelable_type = 'App\\Models\\BuyerTier' THEN (SELECT company_id FROM buyers WHERE id = (SELECT buyer_id FROM buyer_tiers WHERE id = modelable_id))
            ELSE NULL
        END
    ");
    $table->index('company_id');
});

2. 查询时直接过滤虚拟列：

$targetTierId = '你的BuyerTier ID';
$targetCompanyId = BuyerTier::find($targetTierId)->buyer->company->id;

$companyStats = StatisticAggregate::query()
    ->where('modelable_type', Company::class)
    ->where('company_id', $targetCompanyId)
    ->groupBy('bucket_starts_at', 'period')
    ->selectRaw('bucket_starts_at, period, SUM(total_submits) as calculated_total_submits')
    ->get();

注意事项 #

• 虚拟列会增加数据库写入时的计算开销，但查询效率会大幅提升
• 层级数据变更时，虚拟列的值会自动同步更新

方案4：只读视图封装聚合逻辑 #

思路 #

创建数据库视图，封装层级关联和聚合逻辑，Laravel中直接查询视图即可，简化代码同时利用数据库优化视图查询。

代码示例 #

1. 创建视图：

CREATE VIEW company_statistics_with_tier_filter AS
SELECT
    sa.bucket_starts_at,
    sa.period,
    c.id AS company_id,
    SUM(sa.total_submits) AS calculated_total_submits
FROM statistic_aggregates sa
JOIN companies c ON sa.modelable_type = 'App\\Models\\Company' AND sa.modelable_id = c.id
JOIN buyers b ON c.id = b.company_id
JOIN buyer_tiers bt ON b.id = bt.buyer_id
GROUP BY sa.bucket_starts_at, sa.period, c.id;

2. Laravel中创建视图模型：

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class CompanyStatisticsView extends Model
{
    protected $table = 'company_statistics_with_tier_filter';
    public $timestamps = false;
}

3. 查询视图：

$targetTierId = '你的BuyerTier ID';

$companyStats = CompanyStatisticsView::query()
    ->whereIn('company_id', function ($subQuery) use ($targetTierId) {
        $subQuery->select('c.id')
            ->from('buyer_tiers bt')
            ->join('buyers b', 'bt.buyer_id', '=', 'b.id')
            ->join('companies c', 'b.company_id', '=', 'c.id')
            ->where('bt.id', $targetTierId);
    })
    ->get();

性能优化 #

• 给视图关联的字段（buyers.company_id、buyer_tiers.buyer_id）添加索引
• 若使用物化视图，需根据数据更新频率定时刷新

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