mysql - Laravel eloquent 需要 6-12 秒才能返回结果
问题描述
我有我想要提取的这些数据,但我在数据库中有很多行(超过 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。还有其他想法吗?重构也许?
解决方案
在 EXPLAIN 上优化 SQL Base:
尝试使用->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 优化器。
- 我发现你有
user_id
foreign_key 字段和 scan executed_at 字段,看来你需要索引它。喜欢
$table->index('user_id')
$table->index('executed_at')
然后使用explain查看结果。
- 你有多个条件。我认为您可以尝试使用复合索引进行优化,例如
$table->index(['user_id', 'executed_at', 'pending'])
但是,你需要忽略upper方法,如果你已经有user_id
索引和executed_at
索引,你需要先删除那些索引。
并建立这个复合索引,然后通过explain
.
我认为这个查询会有所改善,选择这个。
PS:请注意综合指数的顺序。把你的user_id
第一个,然后其他简单的查询只能使用user_id
索引,并且explain
,选择最好的。
请记住,默认情况下,关系数据库会忽略 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);
希望这可以帮到你。
推荐阅读
- nginx - nginx反向代理+rundeck没有按预期工作
- c++ - c++17 中的 is_constructible 用于没有用户定义的构造函数的类型
- c# - AForge.NET:未触发 NewFrameEventHandler 参数方法
- kotlin - 如何使用 Jetpack Compose 按钮后面的视图检测点击?
- bash - 复制目录并保留文件名,但所有内容都相同
- php - 页面 mim 类型 png - 上传/打开对话框加载 php/文本文件
- ubuntu - 如何在 ROS 凉亭中加载 kuka iiwa R820?
- swift - 使用 Swift 取消选择任务控制上的窗口
- python - 如何使用 Miniconda 创建纯 Python 虚拟环境?
- laravel-blade - 使用刀片 (Livewire) 调用动态内容