laravel - Laravel 使用 mysql 索引
问题描述
我有这个错误的代码(对不起),它使用了太多的 MySQL 查询对不起它太长了
return view('dashboard.homepage', array(
'DriversNumberApproved' => \App\Models\Drivers::where('is_approved', 1)->count(),
'DriversNumberUNApproved' => \App\Models\Drivers::where('is_approved', 0)->count(),
'DriversOnline' => \App\Models\Drivers::where('is_active', 1)->count(),
'DriversOnlineShow' => \App\Models\Drivers::where('is_active', 1)->paginate(5)->appends(request()->query()),
'DriversOffline' => \App\Models\Drivers::where('is_active', 0)->where('is_approved', 1)->count(),
'TodayMoneyTotal' => \App\Models\Request::where('is_completed', 1)
->whereMonth('request_start_time', now()->month)
->whereDay('request_start_time', now()->day)
->whereYear('request_start_time', now()->year)->sum('total'),
'TodayMoneyTotalP' => \App\Models\Request::where('is_completed', 1)
->whereMonth('request_start_time', now()->month)
->whereDay('request_start_time', now()->day)
->whereYear('request_start_time', now()->year)->sum('total') / 5000 * 100,
//This Week
'TodayMoneyTotalWeek' => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time',
[Carbon::now()->startOfWeek(), Carbon::now()])->sum('total'),
'TodayMoneyTotalWeekP' => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time',
[Carbon::now()->startOfWeek(), Carbon::now()])->sum('total') / 5000 * 100,
//This Month
'TodayMoneyTotalMonth' => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time',
[Carbon::now()->startOfMonth(), Carbon::now()
->endOfMonth()])->sum('total'),
'TodayMoneyTotalMonthP' => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time',
[Carbon::now()->startOfMonth(), Carbon::now()
->endOfMonth()])->sum('total') / 5000 * 100,
// Last 30 days
// 'TodayMoneyTotal30Last' => \App\Models\Request::where('is_completed', 1)->whereDate('request_start_time', '=', Carbon::now()->subMonth()->month)->sum('total'),
// 'TodayMoneyTotal30LastP' => \App\Models\Request::where('is_completed', 1)->whereDate('request_start_time', '=', Carbon::now()->subMonth()->month)->sum('total') / 5000 * 100,
'TodayTripsTotal' => \App\Models\Request::where('is_completed', 1)
->whereMonth('request_start_time', now()->month)
->whereDay('request_start_time', now()->day)
->whereYear('request_start_time', now()->year)->count(),
'TodayTripsTotalP' => \App\Models\Request::where('is_completed', 1)
->whereMonth('request_start_time', now()->month)
->whereDay('request_start_time', now()->day)
->whereYear('request_start_time', now()->year)->count() / 100 * 100,
//This Week
'TodayTripsTotalWeek' => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time',
[Carbon::now()->startOfWeek(), Carbon::now()])->count(),
'TodayTripsTotalWeekP' => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time',
[Carbon::now()->startOfWeek(), Carbon::now()])->count() / 100 * 100,
//This Month
'TodayTripsTotalMonth' => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time',
[Carbon::now()->startOfMonth(), Carbon::now()
->endOfMonth()])->count(),
'TodayTripsTotalMonthP' => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time',
[Carbon::now()->startOfMonth(), Carbon::now()
->endOfMonth()])->count() / 100 * 100,
'TodayDistanceTotal' => \App\Models\Request::where('is_completed', 1)
->whereMonth('request_start_time', now()->month)
->whereDay('request_start_time', now()->day)
->whereYear('request_start_time', now()->year)->sum('distance'),
'TodayDistanceTotalP' => \App\Models\Request::where('is_completed', 1)
->whereMonth('request_start_time', now()->month)
->whereDay('request_start_time', now()->day)
->whereYear('request_start_time', now()->year)->sum('distance') / 500 * 100,
//This week
'TodayDistanceWeekTotal' => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time',
[Carbon::now()->startOfWeek(), Carbon::now()])->sum('distance'),
'TodayDistanceWeekTotalP' => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time',
[Carbon::now()->startOfWeek(), Carbon::now()])->sum('distance') / 500 * 100,
//This Month
'TodayDistanceMonthTotal' => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time',
[Carbon::now()->startOfMonth(), Carbon::now()
->endOfMonth()])->sum('distance'),
'TodayDistanceMonthTotalP' => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time',
[Carbon::now()->startOfMonth(), Carbon::now()
->endOfMonth()])->sum('distance') / 500 * 100,
'TodayTimeTotal' => \App\Models\Request::where('is_completed', 1)
->whereMonth('request_start_time', now()->month)
->whereDay('request_start_time', now()->day)
->whereYear('request_start_time', now()->year)->sum('time'),
'TodayTimeTotalP' => \App\Models\Request::where('is_completed', 1)
->whereMonth('request_start_time', now()->month)
->whereDay('request_start_time', now()->day)
->whereYear('request_start_time', now()->year)->sum('time') / 5000 * 100,
//This Week
'TodayTimeWeekTotal' => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time',
[Carbon::now()->startOfWeek(), Carbon::now()])->sum('time'),
'TodayTimeWeekTotalP' => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time',
[Carbon::now()->startOfWeek(), Carbon::now()])->sum('time') / 5000 * 100,
//This Month
'TodayTimeMonthTotal' => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time',
[Carbon::now()->startOfMonth(), Carbon::now()
->endOfMonth()])->sum('time'),
'TodayTimeMonthTotalP' => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time',
[Carbon::now()->startOfMonth(), Carbon::now()
->endOfMonth()])->sum('time') / 5000 * 100,
'TodayEarningsTotal' => \App\Models\Request::where('is_completed', 1)
->whereMonth('request_start_time', now()->month)
->whereDay('request_start_time', now()->day)
->whereYear('request_start_time', now()->year)->sum('refund_remaining'),
'TodayEarningsTotalP' => \App\Models\Request::where('is_completed', 1)
->whereMonth('request_start_time', now()->month)
->whereDay('request_start_time', now()->day)
->whereYear('request_start_time', now()->year)->sum('refund_remaining') / 5000 * 100,
//This Week
'TodayEarningsWeekTotal' => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time',
[Carbon::now()->startOfWeek(), Carbon::now()])->sum('refund_remaining'),
'TodayEarningsWeekTotalP' => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time',
[Carbon::now()->startOfWeek(), Carbon::now()])->sum('refund_remaining') / 5000 * 100,
//This Month
'TodayEarningsMonthTotal' => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time',
[Carbon::now()->startOfMonth(), Carbon::now()
->endOfMonth()])->sum('refund_remaining'),
'TodayEarningsMonthTotalP' => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time',
[Carbon::now()->startOfMonth(), Carbon::now()
->endOfMonth()])->sum('refund_remaining') / 5000 * 100,
//This Week
'ThisWeekDriverRegistered' => \App\Models\Drivers::where('is_approved', 1)->whereBetween('created_at',
[Carbon::now()->startOfWeek(), Carbon::now()])->count(),
'ThisWeekTrips' => \App\Models\Request::where('is_completed', 1)->whereBetween('request_start_time',
[Carbon::now()->startOfWeek(), Carbon::now()])->count(),
'ThisWeekAutoCancelledTrips' => \App\Models\Request::where('is_cancelled', 1)->whereBetween('request_start_time',
[Carbon::now()->startOfWeek(), Carbon::now()])->count(),
'ThisWeekDriverRegisteredMonday' => \App\Models\Drivers::where('is_approved', 1)
->whereDay('created_at', $monday)
->count() / 5000 * 100,
'ThisWeekTripsMonday' => \App\Models\Request::where('is_completed', 1)
->whereDay('request_start_time', $monday)->count()
/ 500 * 100,
'ThisWeekAutoCancelledTripsMonday' => \App\Models\Request::where('is_cancelled', 1)
->whereDay('request_start_time', $monday)->count()
/ 500 * 100,
'ThisWeekDriverRegisteredTuesday' => \App\Models\Drivers::where('is_approved', 1)
->whereDay('created_at', $tuesday)
->count() / 5000 * 100,
'ThisWeekTripsTuesday' => \App\Models\Request::where('is_completed', 1)
->whereDay('request_start_time', $tuesday)->count()
/ 500 * 100,
'ThisWeekAutoCancelledTripsTuesday' => \App\Models\Request::where('is_cancelled', 1)
->whereDay('request_start_time', $tuesday)->count()
/ 500 * 100,
'ThisWeekDriverRegisteredWednesday' => \App\Models\Drivers::where('is_approved', 1)
->whereDay('created_at', $wednesday)
->count() / 5000 * 100,
'ThisWeekTripsWednesday' => \App\Models\Request::where('is_completed', 1)
->whereDay('request_start_time', $wednesday)->count()
/ 500 * 100,
'ThisWeekAutoCancelledTripsWednesday' => \App\Models\Request::where('is_cancelled', 1)
->whereDay('request_start_time', $wednesday)->count()
/ 500 * 100,
'ThisWeekDriverRegisteredThursday' => \App\Models\Drivers::where('is_approved', 1)
->whereDay('created_at', $thursday)
->count() / 5000 * 100,
'ThisWeekTripsThursday' => \App\Models\Request::where('is_completed', 1)
->whereDay('request_start_time', $thursday)->count()
/ 500 * 100,
'ThisWeekAutoCancelledTripsThursday' => \App\Models\Request::where('is_cancelled', 1)
->whereDay('request_start_time', $thursday)->count()
/ 500 * 100,
'ThisWeekDriverRegisteredFriday' => \App\Models\Drivers::where('is_approved', 1)
->whereDay('created_at', $friday)
->count() / 5000 * 100,
'ThisWeekTripsFriday' => \App\Models\Request::where('is_completed', 1)
->whereDay('request_start_time', $friday)->count()
/ 500 * 100,
'ThisWeekAutoCancelledTripsFriday' => \App\Models\Request::where('is_cancelled', 1)
->whereDay('request_start_time', $friday)->count()
/ 500 * 100,
'ThisWeekDriverRegisteredSaturday' => \App\Models\Drivers::where('is_approved', 1)
->whereDay('created_at', $saturday)
->count() / 5000 * 100,
'ThisWeekTripsSaturday' => \App\Models\Request::where('is_completed', 1)
->whereDay('request_start_time', $saturday)->count()
/ 500 * 100,
'ThisWeekAutoCancelledTripsSaturday' => \App\Models\Request::where('is_cancelled', 1)
->whereDay('request_start_time', $saturday)->count()
/ 500 * 100,
'ThisWeekDriverRegisteredSunday' => \App\Models\Drivers::where('is_approved', 1)
->whereDay('created_at', $sunday)
->count() / 5000 * 100,
'ThisWeekTripsSunday' => \App\Models\Request::where('is_completed', 1)
->whereDay('request_start_time', $sunday)->count()
/ 500 * 100,
'ThisWeekAutoCancelledTripsSunday' => \App\Models\Request::where('is_cancelled', 1)
->whereDay('request_start_time', $sunday)->count()
/ 500 * 100,
'TotalBlockedDrivers' => \App\Models\Drivers::where('block', 1)->count(),
'role' => 'admin',
));
因为MySQL页面上的查询需要永远加载我正在考虑使用索引,但我正在使用->这里太多了,太多具体的东西有什么帮助吗?我不太确定如何优化它,因为加载页面需要很多时间
解决方案
好的!!我们开始做吧。
首先,我强烈推荐使用barryvdh/laravel-debugbar
(GitHub)。这将准确地告诉您触发了多少查询以及每个查询花费了多少时间。
现在,让我们谈谈优化。
select()
尽可能使用。如果一个表有 20 列和大约 1000 行,而您所做的只是 count() 或 sum(),那么获取所有数据是没有意义的。- 你
\App\Models\Drivers
的被多次使用。这是我推荐的:- 查询 1 ->
\App\Models\Drivers::where('is_approved', 1)->count();
- 查询 2 ->
\App\Models\Drivers::where('is_approved', 0)->count();
- 解决方案 ->
$drivers = \App\Models\Drivers::whereIn('is_approved', [0, 1])->get();
在这里您可以充分利用Laravel Collection
$drivers->where('is_approved', 1)->count()
$drivers->where('is_approved', 0)->count()
在您的查询运行两次之前,现在只使用 collect() 一次。
- 查询 1 ->
Indexing
是另一种解决方案。查看此堆栈溢出讨论。caching
如果可以,请使用。(文档)- 要考虑的另一件事是您如何显示这些数据。正如您所说,该页面花费的时间太长。也许,您正在使用嵌套循环。(不能说太多,因为我没有看到你的刀片文件)。
- 许多开发人员错过的另一件事是数据类型不匹配。如果您
is_approved
在 sql 中的列不是整数,那么在查询时where(string, int)
会导致加载时间。最好有相同的数据类型,即where(int, int)
希望这可以帮助。干杯!
推荐阅读
- python - Django Crud 帮助 - 更新和删除
- vue.js - Vue 3 处理根元素内的脚本标签
- matlab - 如何从文件夹中读取单个图像,然后使用经过训练的神经网络对其进行分类?
- python - 如何创建接受文本字符串并返回包含某些已定义字符(即使不存在)出现多少次的字典的函数?
- c# - 将 RichTextBox 的所有内容保存为位图
- shopify - Shopify 购买按钮
- android - 尝试导入库时在 DexPathList 上找不到类
- reactjs - 如何正确管理etag?
- windows - WinTrust 验证在某些特定的 exe 上花费大量时间
- google-cloud-platform - Java - 在 Google Workspace Marketplace API 中出现“未授权访问应用程序 ID”错误