mysql - Laravel Eloquent 需要 6-12 秒返回结果

Question

我有我要提取的数据，但我在数据库中有很多行(超过 100k)，因此可能需要 12 秒才能返回结果。关于如何提高性能的任何想法？

  $completedActivities = Activity::where('executed_at', '>', Carbon::now()->subDays(14))
        ->where('pending', false)
        ->where('user_id', auth()->user()->id)
        ->orderby('executed_at', 'desc')
        ->limit(100)->get();

表结构:

Schema::create('activities', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedInteger('user_id');
            $table->string('type');
            $table->boolean('pending')->default(true);
            $table->dateTime('pending_until')->nullable();
            $table->dateTime('executed_at')->nullable();
            $table->longText('execute')->nullable();
            $table->softDeletes();
            $table->timestamps();
        });

我尝试只提取过去 14 天的项目(以减少从数据库中提取所有事件)并尝试限制为 100 个。还有其他想法吗？也许重构？

Answer 1

基于 EXPLAIN 优化 SQL:

尝试使用 ->toSql() 或 DB::getQueryLog() 获取原始 SQL。

Activity::where('executed_at', '>', Carbon::now()->subDays(14))->where('pending', false)->where('user_id', auth()->user()->id)->orderby('executed_at', 'desc')->toSql()

并使用 explain 查看执行计划并将它们放入您的 MYSQL CLI。

EXPLAIN SELECT *
FROM activities
WHERE user_id = 1 AND pending = false AND executed_at > '2019-11-23 00:00:00'

这将显示表扫描消息，包括过滤时间、选择的类型、扫描行数等。Mysql Explain Doc

索引建议:

您可以使用两种方法，这取决于您的 sql 优化器。

1. 我发现你有user_id foreign_key字段和scan executed_at字段，看来你需要索引它。喜欢

$table->index('user_id')
$table->index('executed_at')

然后使用explain查看结果。

1. 您有多个条件。我觉得你可以试试复合索引来优化，比如

$table->index(['user_id', 'executed_at', 'pending'])

但是，你需要忽略上面的方法，如果你已经有了user_id索引和executed_at索引，您需要先删除这些索引。

并构建此复合索引，然后通过explain 尝试此操作。

我认为它会改进对于这个查询，选择这个。

PS:请注意复合索引的顺序。把你的user_id放在第一位，其他简单的查询只能使用user_id索引，并且解释，选择最好的。

请记住，默认情况下，关系数据库会忽略 NULL 值

限制数据计数。

100k+ 带索引的 SQL 可以非常容易和快速地扫描。然而，你把所有的结果都去掉是一个非常糟糕的主意。

您需要限制记录数和字段数，以优化IO成本。

$completedActivities = Activity::where('executed_at', '>', Carbon::now()->subDays(14))
        ->where('pending', false)
        ->where('user_id', auth()->user()->id)
        ->orderby('executed_at', 'desc')
        # => Limit the records' count, find the balance number fit to frontend
        ->limit(30)               
        # => Limit the fields [Select the fields that you want]
        ->select('id', 'title', ...)    
        ->get();

PS:如果你使用的是 Laravel 的 ->paginate()。它仍将使用 select count(*) from activities where ... 来获取总数据计数。您可以改用 ->simplePaginate()。

节省空间

我认为有些领域可以优化，比如节省空间。

type 似乎不是一个长字符串。您可以将其限制为特定长度。

$table->string('type', 20);

希望对您有所帮助。