Laravel Eloquent查询优化：添加clicks_count字段提速排序查询

问题根源 #

原查询依赖withCount('clicks')生成的动态聚合字段clicks_count进行排序，数据库无法对这个实时计算的字段使用索引，在百万级数据量下需要遍历所有匹配记录并重新计算聚合值，直接导致查询性能暴跌。

具体实现步骤 #

1. 给ShortUrl表新增clicks_count字段

生成迁移文件：

php artisan make:migration add_clicks_count_to_short_urls_table --table=short_urls

编辑迁移文件的up方法：

public function up()
{
    Schema::table('short_urls', function (Blueprint $table) {
        // 用无符号整数存储点击数，默认值设为0
        $table->unsignedInteger('clicks_count')->default(0);
    });
}

运行迁移生效：

php artisan migrate

2. 维护clicks_count的实时同步

由于ShortUrl和Click是一对多关联，需要在Click记录新增/删除时同步更新对应ShortUrl的点击数，推荐用模型观察者实现：

生成Click模型观察者：

php artisan make:observer ClickObserver --model=Click

编辑app/Observers/ClickObserver.php：

namespace App\Observers;

use App\Models\Click;

class ClickObserver
{
    public function created(Click $click)
    {
        // 新增点击记录时，对应短链接的点击数+1
        $click->shortUrl()->increment('clicks_count');
    }

    public function deleted(Click $click)
    {
        // 删除点击记录时，对应短链接的点击数-1
        $click->shortUrl()->decrement('clicks_count');
    }
}

在app/Providers/AppServiceProvider.php的boot方法中注册观察者：

use App\Models\Click;
use App\Observers\ClickObserver;

public function boot()
{
    Click::observe(ClickObserver::class);
}

注意：如果有批量新增/删除Click的操作，需要手动调用increment/decrement或者批量更新clicks_count，避免数据不一致。

3. 修改查询代码并添加复合索引

替换原查询，直接使用数据库字段clicks_count，无需再调用withCount：

$shorturls = ShortUrl::where([
    ["url_id", "=", $id->id],
    ["user_id", "=", Auth::id()]
])->orderBy("clicks_count", "desc")->paginate(10);

为了让数据库直接通过索引完成筛选和排序，添加复合索引：
生成索引迁移文件：

php artisan make:migration add_index_to_short_urls_for_user_url_clicks --table=short_urls

编辑迁移文件：

public function up()
{
    Schema::table('short_urls', function (Blueprint $table) {
        // 复合索引匹配查询条件+排序规则，最大化查询效率
        $table->index(['user_id', 'url_id', 'clicks_count']);
    });
}

运行迁移：

php artisan migrate

4. 初始化历史数据的clicks_count

对于已存在的ShortUrl记录，需要批量计算并更新clicks_count，推荐用原生SQL执行，效率远高于ORM循环：

UPDATE short_urls su
JOIN (
    SELECT short_url_id, COUNT(*) as count
    FROM clicks
    GROUP BY short_url_id
) c ON su.id = c.short_url_id
SET su.clicks_count = c.count;

可以通过Laravel的DB::statement()执行，或者直接在数据库客户端运行。

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