Laravel使用mysql索引 [英] Laravel use mysql indexing

问题描述

我有这个不好的代码(抱歉)，它使用太多的MySQL查询对不起它的时间太长了

I have this bad code (sorry) which using too many of MySQL queries sorry its too long

    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页面上的查询的查询占用了我所有的时间，我想使用索引，但是我使用的是->这里有太多的地方，太多的特定东西对您有帮助吗?我不太确定如何优化它，因为加载页面需要花费很多时间

Cuz of the queries on MySQL page take forever to load I'm thinking to use indexing but I'm using ->Where too much here and too many specific things any help? I'm not really sure how to optimize it cuz it takes so many to load the page

推荐答案

好的！来吧.

首先，我强烈建议使用barryvdh/laravel-debugbar( GitHub ).这将准确告诉您触发了多少个查询以及每个查询花费了多少时间.

First of all, I highly recommend using barryvdh/laravel-debugbar (GitHub). This will tell you exactly how many queries were fired and how much time each one took.

现在，让我们谈谈优化.

• 尽可能使用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().
  • Use select() whenever possible. If a table has 20 columns and about 1000 rows and all your are doing is count() or sum() then fetching all the data doesn't make sense.
  • Your \App\Models\Drivers is being used multiple times. Here's what I recommend:
    • Query 1 -> \App\Models\Drivers::where('is_approved', 1)->count();
    • Query 2 -> \App\Models\Drivers::where('is_approved', 0)->count();
    • Solution -> $drivers = \App\Models\Drivers::whereIn('is_approved', [0, 1])->get();
      Here you can take adcantage of Laravel Collection
      $drivers->where('is_approved', 1)->count()
$drivers->where('is_approved', 0)->count()
      Before your query was running twice and now with collect() only once.

    希望这会有所帮助.干杯！

    Hope this helps. Cheers!

    这篇关于Laravel使用mysql索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！