Jak Poprawiłem Wydajność Laravela - 2453 Zapytań Do 4

Witam wszystkich.

Mam kod laravel, który faktycznie wizualizuje listę użytkowników, którzy są przedstawieni na platformie. Niestety, ten kod wymaga 2453 zapytań, aby załadować te dane do administratora. Dzisiaj pokażę Ci, jak możesz go poprawić od tego momentu do zaledwie 4.

Podsumowanie

Obecnie mam ten kod, który ma następujący wynik:

Spis treści

• Badanie kodu

  • Obserwacja:

• Refaktoryzacja

  • Krok 1
  • Krok 2
  • Krok 3
  • Krok 4
  • Krok 5

• Ocena po refaktoryzacji

• Pełny kod

• Samouczek

wideoBadanie

kodu Niestety ten kod nie jest napisany w zoptymalizowany sposób, dlatego widzę duplikację kodu, zrzucanie wszystkich rekordów na jednej stronie (bez paginacji) itp.

Oto kontroler:

public function showUsersFeatured(Request $request)
{
    if ($request->segment(2) == 'unitedstates') {
        $usersfeatured = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'unitedstates')->get();
        $usersfeaturedman = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'unitedstates')->where('gender', '=', 'man')->get();
        $usersfeaturedwoman = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'unitedstates')->where('gender', '=', 'woman')->get();
        $country = 'United States';
        return view('admin.featured-users.showfeatured')->with([
            'usersfeatured' => $usersfeatured,
            'usersfeaturedman' => $usersfeaturedman,
            'usersfeaturedwoman' => $usersfeaturedwoman,
            'country' => $country,
        ]);
    } elseif ($request->segment(2) == 'singapore') {
        $usersfeatured = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'singapore')->get();
        $usersfeaturedman = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'singapore')->where('gender', '=', 'man')->get();
        $usersfeaturedwoman = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'singapore')->where('gender', '=', 'woman')->get();
        $country = 'Singapore';
        return view('admin.featured-users.showfeatured')->with([
            'usersfeatured' => $usersfeatured,
            'usersfeaturedman' => $usersfeaturedman,
            'usersfeaturedwoman' => $usersfeaturedwoman,
            'country' => $country,
        ]);
    } elseif ($request->segment(2) == 'thailand') {
        $usersfeatured = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'thailand')->get();
        $usersfeaturedman = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'thailand')->where('gender', '=', 'man')->get();
        $usersfeaturedwoman = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'thailand')->where('gender', '=', 'woman')->get();
        $country = 'Thailand';
        return view('admin.featured-users.showfeatured')->with([
            'usersfeatured' => $usersfeatured,
            'usersfeaturedman' => $usersfeaturedman,
            'usersfeaturedwoman' => $usersfeaturedwoman,
            'country' => $country,
        ]);
    } elseif ($request->segment(2) == 'indonesia') {
        $usersfeatured = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'indonesia')->get();
        $usersfeaturedman = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'indonesia')->where('gender', '=', 'man')->get();
        $usersfeaturedwoman = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'indonesia')->where('gender', '=', 'woman')->get();
        $country = 'Indonesia';
        return view('admin.featured-users.showfeatured')->with([
            'usersfeatured' => $usersfeatured,
            'usersfeaturedman' => $usersfeaturedman,
            'usersfeaturedwoman' => $usersfeaturedwoman,
            'country' => $country,
        ]);
    } elseif ($request->segment(2) == 'home') {
        $usersfeatured = User::with(['usercity', 'tempphoto'])->where('featurehome', '!=', 0)->get();
        $usersfeaturedman = User::with(['usercity', 'tempphoto'])->where('featurehome', '!=', 0)->where('gender', '=', 'man')->get();
        $usersfeaturedwoman = User::with(['usercity', 'tempphoto'])->where('featurehome', '!=', 0)->where('gender', '=', 'woman')->get();
        $country = 'Home';
        return view('admin.featured-users.showfeatured')->with([
            'usersfeatured' => $usersfeatured,
            'usersfeaturedman' => $usersfeaturedman,
            'usersfeaturedwoman' => $usersfeaturedwoman,
            'country' => $country,
        ]);
    }
    $usersfeatured = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'malaysia')->get();
    $usersfeaturedman = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'malaysia')->where('gender', '=', 'man')->get();
    $usersfeaturedwoman = User::with(['usercity', 'tempphoto'])->where('isfeatured', '!=', 0)->where('featuredin', '=', 'malaysia')->where('gender', '=', 'woman')->get();
    $country = 'Malaysia';
    return view('admin.featured-users.showfeatured')->with([
        'usersfeatured' => $usersfeatured,
        'usersfeaturedman' => $usersfeaturedman,
        'usersfeaturedwoman' => $usersfeaturedwoman,
        'country' => $country,
    ]);
    return view('admin.featured-users.showfeatured')->with([
        'usersfeatured' => $usersfeatured,
        'usersfeaturedman' => $usersfeaturedman,
        'usersfeaturedwoman' => $usersfeaturedwoman,
        'country' => $country,
    ]);
}

Obserwacja:

• Podobny wzorzec kodu
• Wielokrotne wracanie do widoków
• Blok kodu if-else
• Martwy kod (nieużywany)

Refaktoryzacja

Będę robił refaktoryzację krok po kroku.

Krok 1

: Wybierz odpowiedni kraj na podstawie swojego segmentu.

$segment = $request->segment(2);
$countries = [
    'unitedstates' => ['name' => 'United States', 'featuredin' => 'unitedstates'],
    'singapore' => ['name' => 'Singapore', 'featuredin' => 'singapore'],
    'thailand' => ['name' => 'Thailand', 'featuredin' => 'thailand'],
    'indonesia' => ['name' => 'Indonesia', 'featuredin' => 'indonesia'],
    'home' => ['name' => 'Home', 'featuredin' => null, 'featurehome' => true],
    'malaysia' => ['name' => 'Malaysia', 'featuredin' => 'malaysia'], // Default country
];
$countryData = $countries[$segment] ?? $countries['malaysia'];

Krok 2

Kategorie typowych zachowań i umieść je w jednym miejscu.

$baseQuery = User::where('isfeatured', '!=', 0);
if (isset($countryData['featurehome'])) {
    $baseQuery->where('featurehome', '!=', 0);
} else {
    $baseQuery->where('featuredin', '=', $countryData['featuredin']);
}

Te 6 linijek kodu w rzeczywistości zmniejsza 30 linii kodu i wiele zduplikowanych kodów.

Krok 3

Podziel rekordy relacji na strony, a następnie użyj leniwego ładowania rekordów $baseQuery relacji.

$usersfeatured = $baseQuery->paginate(50);
$usersfeatured->load(['usercity', 'tempphoto']);
$usersfeaturedman = $usersfeatured->where('gender', 'man');
$usersfeaturedwoman = $usersfeatured->where('gender', 'woman');

Następnie zastosowałem warunek uzyskania wyróżnionego użytkownika dla mężczyzny i kobiety.

Krok 4

Na koniec chciałbym przekazać te dane do widoku.

return view('admin.featured-users.showfeatured')->with([
    'usersfeatured' => $usersfeatured,
    'usersfeaturedman' => $usersfeaturedman,
    'usersfeaturedwoman' => $usersfeaturedwoman,
    'country' => $countryData['name'],
]);

Pełna zrefaktoryzowana metoda tutaj:

public function index(Request $request)
{
    $segment = $request->segment(2);
    $countries = [
        'unitedstates' => ['name' => 'United States', 'featuredin' => 'unitedstates'],
        'singapore' => ['name' => 'Singapore', 'featuredin' => 'singapore'],
        'thailand' => ['name' => 'Thailand', 'featuredin' => 'thailand'],
        'indonesia' => ['name' => 'Indonesia', 'featuredin' => 'indonesia'],
        'home' => ['name' => 'Home', 'featuredin' => null, 'featurehome' => true],
        'malaysia' => ['name' => 'Malaysia', 'featuredin' => 'malaysia'], // Default country
    ];
    $countryData = $countries[$segment] ?? $countries['malaysia'];
    $baseQuery = User::where('isfeatured', '!=', 0);
    if (isset($countryData['featurehome'])) {
        $baseQuery->where('featurehome', '!=', 0);
    } else {
        $baseQuery->where('featuredin', '=', $countryData['featuredin']);
    }
    $usersfeatured = $baseQuery->paginate(50);
    $usersfeatured->load(['usercity', 'tempphoto']);  // Lazy eager load after main fetch
    $usersfeaturedman = $usersfeatured->where('gender', 'man');
    $usersfeaturedwoman = $usersfeatured->where('gender', 'woman');
    return view('admin.featured-users.showfeatured')->with([
        'usersfeatured' => $usersfeatured,
        'usersfeaturedman' => $usersfeaturedman,
        'usersfeaturedwoman' => $usersfeaturedwoman,
        'country' => $countryData['name'],
    ]);
}

Krok 5

Poprawiłem również nieco plik widoku bloku (⚠️ co nie poprawia wydajności).

@foreach($usersfeatured as $key => $userfeatured)
  <tr class="even:bg-gray-50">
      <td class="whitespace-nowrap py-4 pl-4 pr-3 text-sm font-medium text-gray-900 sm:pl-3">{{ ++$key }}</td>
      <td class="whitespace-nowrap px-3 py-4 text-sm text-gray-500">{{ $userfeatured->username }}</td>
      <td class="whitespace-nowrap px-3 py-4 text-sm text-gray-500">{{ $userfeatured->featuredsince ? $userfeatured->featuredsince->format('d M, Y') : '' }} </td>
      <td class="whitespace-nowrap px-3 py-4 text-sm text-gray-500">{{ $userfeatured->featuredin }}</td>
      <td class="whitespace-nowrap px-3 py-4 text-sm text-gray-500">{{ $userfeatured->created_at->diffForHumans() }}</td>
      <td class="whitespace-nowrap px-3 py-4 text-sm text-gray-500">{{ $userfeatured->membershiptype }}</td>
      <td class="whitespace-nowrap px-3 py-4 text-sm text-gray-500">{{ $userfeatured->gender }}</td>
      <td class="whitespace-nowrap px-3 py-4 text-sm text-gray-500">{{ optional($userfeatured->usercity)->currentcountry }}</td>
      <td class="whitespace-nowrap px-3 py-4 text-sm text-gray-500">
          @if($userfeatured->tempphoto)
              <a href="" target="_blank">
                  <img src="{{ $userfeatured->profilePhoto() }}" alt="" width="50" height="50">
              </a>
          @endif
      </td>
      <td
          class="relative whitespace-nowrap py-4 pl-3 pr-4 text-right text-sm font-medium sm:pr-3">
          <a href="#" class="text-indigo-600 hover:text-indigo-900">Unfeature</a>
      </td>
  </tr>
@endforeach

Ocena po refaktoryzacji Po refaktoryzacji

, oto wynik, który znalazłem.

Pełny kod

Utworzyłem pull request na githubie. Pełny kod można znaleźć tutaj.

Oto moje proponowane rozwiązanie

Samouczek

wideo Jeśli jesteś wzrokowcem zamiast tekstu, możesz obejrzeć ten film. Zawsze doceniam, jeśli masz inne pomysły, aby to poprawić.

Mam nadzieję, że to rozwiązanie pomoże Ci w przyszłości.

Dziękuję za przeczytanie.