18 wskazówek, jak zoptymalizować zapytania do bazy danych Laravel

Jeśli Twoja aplikacja działa wolno lub wykonuje wiele zapytań do bazy danych, postępuj zgodnie z poniższymi wskazówkami dotyczącymi optymalizacji wydajności, aby skrócić czas ładowania aplikacji.

1. Pobieranie dużych zbiorów danych

Ta wskazówka koncentruje się głównie na poprawie wykorzystania pamięci przez Twoją aplikację w przypadku dużych zbiorów danych.

If your application needs to process a large set of records, instead of retrieving all at once, you can retrieve a
a subset of results and process them in groups.

Aby pobrać wiele wyników z tabeli o nazwie posty, zwykle zrobimy tak, jak poniżej.

$posts = Post::all(); // when using eloquent
$posts = DB::table('posts')->get(); // when using query builder
 
foreach ($posts as $post){
 // Process posts
}

Powyższe przykłady pozwolą pobrać wszystkie rekordy z tabeli postów i przetworzyć je. Co jeśli ta tabela ma 1 milion wierszy? Szybko zabraknie nam pamięci.

Aby uniknąć problemów podczas pracy z dużymi zbiorami danych, możemy pobrać podzbiór wyników i przetworzyć je jak poniżej.

Opcja 1: Używanie fragmentu

// when using eloquent
$posts = Post::chunk(100, function($posts){
    foreach ($posts as $post){
     // Process posts
    }
});
 
// when using query builder
$posts = DB::table('posts')->chunk(100, function ($posts){
    foreach ($posts as $post){
     // Process posts
    }
});

Powyższy przykład pobiera 100 rekordów z tabeli posts, przetwarza je, pobiera kolejne 100 rekordów i przetwarza je. Ta iteracja będzie kontynuowana, dopóki wszystkie rekordy nie zostaną przetworzone.

This approach will create more database queries but be more memory efficient. Usually, the processing of large datasets should
be doe in the background. So it is ok to make more queries when running in the background to avoid running out of memory when processing large datasets.

Opcja 2: za pomocą kursora

// when using eloquent
foreach (Post::cursor() as $post){
   // Process a single post
}
 
// when using query builder
foreach (DB::table('posts')->cursor() as $post){
   // Process a single post
}

Powyższy przykład utworzy jedno zapytanie do bazy danych, pobierze wszystkie rekordy z tabeli i uwodni modele Eloquent jeden po drugim. Takie podejście spowoduje, że tylko jedno zapytanie do bazy danych spowoduje pobranie wszystkich postów. Używa jednak generatora php do optymalizacji wykorzystania pamięci.

kiedy możesz tego użyć?

Though this greatly optimizes the memory usage on the application level, Since we are retrieving all the entries from a table,
the memory usage on the database instance will still be higher.

Lepiej jest użyć kursora, jeśli aplikacja internetowa, na której działa aplikacja, ma mniej pamięci, a instancja bazy danych ma więcej pamięci. Jeśli jednak Twoja instancja bazy danych nie ma wystarczającej ilości pamięci, lepiej trzymać się fragmentów.

opcja 3: Używanie chunkById

// when using eloquent
$posts = Post::chunkById(100, function($posts){
    foreach ($posts as $post){
     // Process posts
    }
});
 
// when using query builder
$posts = DB::table('posts')->chunkById(100, function ($posts){
    foreach ($posts as $post){
     // Process posts
    }
});

The major difference between chunk and chunkById is that chunk retrieves based on offset and limit. Whereas
chunkById retrieves database results based on an id field. This id field usually be an integer field, and in most cases it would be an auto-incrementing field.

Zapytania wykonane przez chunk i chunkById były następujące.

fragment

select * from posts offset 0 limit 100

select * from posts offset 101 limit 100

chunkById

select * from posts order by id asc limit 100

select * from posts where id > 100 order by id asc limit 100

Ogólnie rzecz biorąc, używanie limitu z przesunięciem jest wolniejsze i powinniśmy starać się go unikać. Ten artykuł szczegółowo wyjaśnia problem z użyciem offsetu.

Ponieważ chunkById używa pola id, które jest liczbą całkowitą, a zapytanie używa klauzuli where, zapytanie będzie znacznie szybsze.

Kiedy możesz używać chunkById?

If your database table has a primary key column column, which is an auto-incrementing field.

2.Wybierz tylko potrzebne kolumny

Zwykle, aby pobrać wyniki z tabeli bazy danych, zrobimy co następuje.

$posts = Post::find(1); //When using eloquent
$posts = DB::table('posts')->where('id','=',1)->first(); //When using query builder

Powyższy kod spowoduje zapytanie jak poniżej

select * from posts where id = 1 limit 1

As you can see, the query is doing a select *. This means it is retrieving all the columns from the database table.
This is fine if we really need all the columns from the table.

Zamiast tego, jeśli potrzebujemy tylko określonych kolumn (id, title), możemy pobrać tylko te kolumny, jak poniżej.

$posts = Post::select(['id','title'])->find(1); //When using eloquent
$posts = DB::table('posts')->where('id','=',1)->select(['id','title'])->first(); //When using query builder

Powyższy kod spowoduje zapytanie jak poniżej

select id,title from posts where id = 1 limit 1

3.Użyj pluck, gdy potrzebujesz dokładnie jednej lub dwóch kolumn z bazy danych

Ta wskazówka skupia się bardziej na czasie spędzonym po pobraniu wyników z bazy danych. Nie ma to wpływu na rzeczywisty czas zapytania.

Jak wspomniałem powyżej, aby pobrać określone kolumny, zrobimy to

$posts = Post::select(['title','slug'])->get(); //When using eloquent
$posts = DB::table('posts')->select(['title','slug'])->get(); //When using query builder

Kiedy powyższy kod jest wykonywany, za kulisami wykonuje następujące czynności.

Executes select title, slug from posts query on the database
Creates a new Post model object for each row it retrieved(For query builder, it creates a PHP standard object)
Creates a new collection with the Post models
Returns the collection

Teraz, aby uzyskać dostęp do wyników, zrobimy to

foreach ($posts as $post){
    // $post is a Post model or php standard object
    $post->title;
    $post->slug;
}

Powyższe podejście wiąże się z dodatkowym obciążeniem związanym z uwadnianiem modelu post dla każdego wiersza i tworzeniem kolekcji dla tych obiektów. Byłoby to najlepsze, jeśli naprawdę potrzebujesz instancji modelu Post zamiast danych.

Ale jeśli potrzebujesz tylko tych dwóch wartości, możesz wykonać następujące czynności.

$posts = Post::pluck('title', 'slug'); //When using eloquent
$posts = DB::table('posts')->pluck('title','slug'); //When using query builder

Kiedy powyższy kod jest wykonywany, za kulisami wykonuje następujące czynności.

Executes select title, slug from posts query on the database
Creates an array with title as array value and slug as array key.
Returns the array(array format: [ slug => title, slug => title ])

Teraz, aby uzyskać dostęp do wyników, zrobimy to

foreach ($posts as $slug => $title){
    // $title is the title of a post
    // $slug is the slug of a post
}

Jeśli chcesz pobrać tylko jedną kolumnę, możesz to zrobić

$posts = Post::pluck('title'); //When using eloquent
$posts = DB::table('posts')->pluck('title'); //When using query builder
foreach ($posts as  $title){
    // $title is the title of a post
}

The above approach eliminates the creation of Post objects for every row. Thus reducing the memory usage and
time spent on processing the query results.

I would recommend using the above approach on new code only. I personally feel going back and refactoring your code
to follow the above tip is not worthy of the time spent on it. Refactor existing code only if your code is processing large
datasets or if you have free time to spare.

4.Policz wiersze za pomocą zapytania zamiast zbioru

Aby policzyć całkowitą liczbę wierszy w tabeli, normalnie zrobilibyśmy

$posts = Post::all()->count(); //When using eloquent
$posts = DB::table('posts')->get()->count(); //When using query builder

Spowoduje to wygenerowanie następującego zapytania

select * from posts

The above approach will retrieve all the rows from the table, load them into a collection object, and counts the results. This works fine when there are less rows in the database table. But we will quickly run out of memory as the
table grows.

Zamiast powyższego podejścia, możemy bezpośrednio policzyć całkowitą liczbę wierszy w samej bazie danych.

$posts = Post::count(); //When using eloquent
$posts = DB::table('posts')->count(); //When using query builder

Spowoduje to wygenerowanie następującego zapytania

select count(*) from posts

Zliczanie wierszy w sql jest powolnym procesem i działa słabo, gdy tabela bazy danych ma tak wiele wierszy. Lepiej unikać liczenia rzędów tak często, jak to możliwe.

5. Unikaj zapytań N+1 dzięki gorliwemu ładowaniu relacji

Możliwe, że słyszałeś o tej wskazówce milion razy. Postaraj się więc, aby była jak najkrótsza i najprostsza.Załóżmy, że masz następujący scenariusz

class PostController extends Controller
{
    public function index()
    {
        $posts = Post::all();
        return view('posts.index', ['posts' => $posts ]);
    }
}

// posts/index.blade.php file
 
@foreach($posts as $post)
    <li>
        <h3>{{ $post->title }}</h3>
        <p>Author: {{ $post->author->name }}</p>
    </li>
@endforeach

Powyższy kod pobiera wszystkie posty i wyświetla tytuł posta oraz jego autora na stronie internetowej i zakłada, że masz relację autor w swoim modelu postu.

Wykonanie powyższego kodu spowoduje uruchomienie następujących zapytań.

select * from posts // Assume this query returned 5 posts
select * from authors where id = { post1.author_id }
select * from authors where id = { post2.author_id }
select * from authors where id = { post3.author_id }
select * from authors where id = { post4.author_id }
select * from authors where id = { post5.author_id }

Jak widać, mamy jedno zapytanie do pobrania postów i 5 zapytań do pobrania autorów postów (ponieważ założyliśmy, że mamy 5 postów). Tak więc dla każdego pobranego posta tworzy jedno oddzielne zapytanie do pobrania jego autor.

Więc jeśli jest N postów, wykona N+1 zapytań (1 zapytanie do pobrania postów i N zapytań do pobrania autora dla każdego posta).

Często wykonujemy zapytania do bazy danych, które nie są konieczne. Rozważ poniższy przykład.

$posts = Post::all(); // Avoid doing this
$posts = Post::with(['author'])->get(); // Do this instead

Problem polega na tym, kiedy robimy

select * from posts // Assume this query returned 5 posts
select * from authors where id in( { post1.author_id }, { post2.author_id }, { post3.author_id }, { post4.author_id }, { post5.author_id } )

Możemy zmodyfikować naszą logikę poniżej, aby uniknąć tego dodatkowego zapytania.

From the above example, consider the author belongs to a team, and you wish to display the team name as well. So in the
blade file you would do as below.

@foreach($posts as $post)
    <li>
        <h3>{{ $post->title }}</h3>
        <p>Author: {{ $post->author->name }}</p>
        <p>Author's Team: {{ $post->author->team->name }}</p>
    </li>
@endforeach

Zmieniając naszą logikę na powyższą, tworzymy dwa zapytania dla administratora i jedno zapytanie dla wszystkich pozostałych użytkowników.

$posts = Post::with(['author'])->get();

9.Scal podobne zapytania

select * from posts // Assume this query returned 5 posts
select * from authors where id in( { post1.author_id }, { post2.author_id }, { post3.author_id }, { post4.author_id }, { post5.author_id } )
select * from teams where id = { author1.team_id }
select * from teams where id = { author2.team_id }
select * from teams where id = { author3.team_id }
select * from teams where id = { author4.team_id }
select * from teams where id = { author5.team_id }

As you can see, even though we are eager loading authors relationship, it is still making more queries. Because we
are not eager loading the team relationship on authors.

Czasami musimy tworzyć zapytania, aby pobrać różne rodzaje wierszy z tej samej tabeli.

$posts = Post::with(['author.team'])->get();

Wykonanie powyższego kodu spowoduje uruchomienie następujących zapytań.

select * from posts // Assume this query returned 5 posts
select * from authors where id in( { post1.author_id }, { post2.author_id }, { post3.author_id }, { post4.author_id }, { post5.author_id } )
select * from teams where id in( { author1.team_id }, { author2.team_id }, { author3.team_id }, { author4.team_id }, { author5.team_id } )

11.Użyj simplePaginate zamiast Paginate

Paginując wyniki, zwykle robimy to

Imagine you have two tables posts and authors. Posts table has a column author_id which represents a belongsTo
relationship on the authors table.

Spowoduje to utworzenie dwóch zapytań, pierwszego do pobrania wyników podzielonych na strony, a drugiego do zliczenia całkowitej liczby wierszy w tabeli. Liczenie wierszy w tabeli jest powolną operacją i negatywnie wpłynie na wydajność zapytania.

$post = Post::findOrFail(<post id>);
$post->author->id;

Dlaczego więc laravel liczy całkowitą liczbę rzędów?

select * from posts where id = <post id> limit 1
select * from authors where id = <post author id> limit 1

Aby wygenerować linki do stronicowania, Laravel zlicza całkowitą liczbę wierszy. Tak więc, gdy generowane są linki do stronicowania, wiesz z góry, ile będzie tam stron i jaki jest poprzedni numer strony. Dzięki temu możesz łatwo przejść do dowolnej strony.

$post = Post::findOrFail(<post id>);
$post->author_id; // posts table has a column author_id which stores id of the author

Z drugiej strony wykonanie simplePaginate nie zliczy całkowitej liczby wierszy, a zapytanie będzie znacznie szybsze niż podejście paginate. Ale stracisz możliwość poznania ostatniego numeru strony i przeskakiwania na różne strony.

You can use the above approach when you are confident that a row always exists in authors table if it is referenced
in posts table.

Jeśli Twoja tabela bazy danych ma tak wiele wierszy, lepiej jest unikać paginate i zamiast tego wykonać simplePaginate.

Kiedy używać paginacji, a kiedy prostej paginacji?

<?php
 
class PostController extends Controller
{
    public function index()
    {
        $posts = Post::all();
        $private_posts = PrivatePost::all();
        return view('posts.index', ['posts' => $posts, 'private_posts' => $private_posts ]);
    }
}

The above code is retrieving rows from two different tables(ex: posts, private_posts) and passing them to view.
The view file looks as below.

// posts/index.blade.php
 
@if( request()->user()->isAdmin() )
    <h2>Private Posts</h2>
    <ul>
        @foreach($private_posts as $post)
            <li>
                <h3>{{ $post->title }}</h3>
                <p>Published At: {{ $post->published_at }}</p>
            </li>
        @endforeach
    </ul>
@endif
 
<h2>Posts</h2>
<ul>
    @foreach($posts as $post)
        <li>
            <h3>{{ $post->title }}</h3>
            <p>Published At: {{ $post->published_at }}</p>
        </li>
    @endforeach
</ul>

As you can see above, $private_posts is visible to only a user who is an admin. Rest all the users cannot see
these posts.

Spójrz na poniższą tabelę porównawczą i ustal, czy paginacja czy prosta paginacja jest dla Ciebie odpowiednia

$posts = Post::all();
$private_posts = PrivatePost::all();

We are making two queries. One to get the records from posts table and another to get the records
from private_posts table.

Records from private_posts table are visible only to the admin user. But we are still making the query to retrieve
these records for all the users even though they are not visible.

12.Unikaj używania wiodących symboli wieloznacznych (słowo kluczowe LIKE)

$posts = Post::all();
$private_posts = collect();
if( request()->user()->isAdmin() ){
    $private_posts = PrivatePost::all();
}

Próbując wyszukać wyniki, które pasują do określonego wzorca, zwykle używamy

13. unikaj używania funkcji SQL w klauzuli where

Spowoduje to zapytanie podobne do poniższego

$published_posts = Post::where('status','=','published')->get();
$featured_posts = Post::where('status','=','featured')->get();
$scheduled_posts = Post::where('status','=','scheduled')->get();

The above code is retrieving rows with a different status from the same table. The code will result in making
following queries.

select * from posts where status = 'published'
select * from posts where status = 'featured'
select * from posts where status = 'scheduled'

As you can see, it is making three different queries to the same table to retrieve the records. We can refactor this code
to make only one database query.

$posts =  Post::whereIn('status',['published', 'featured', 'scheduled'])->get();
$published_posts = $posts->where('status','=','published');
$featured_posts = $posts->where('status','=','featured');
$scheduled_posts = $posts->where('status','=','scheduled');

select * from posts where status in ( 'published', 'featured', 'scheduled' )

The above code is making one query to retrieve all the posts which has any of the specified status and creating separate collections for each status by filtering the returned posts by their status. So we will still have
three different variables with their status and will be making only one query.

Możemy to zmienić, aby uniknąć funkcji data sql, jak poniżej

If you are making queries by adding a where condition on a string based column, it is better to add an index to
the column. Queries are much faster when querying rows with an index column.

$posts = Post::where('status','=','published')->get();

In the above example, we are querying records by adding a where condition to the status column. We can improve the
performance of the query by adding the following database migration.

Schema::table('posts', function (Blueprint $table) {
   $table->index('status');
});

14.unikaj dodawania zbyt wielu kolumn do tabeli

Dodanie zbyt wielu kolumn do tabeli zwiększy długość pojedynczego rekordu i spowolni skanowanie tabeli. Kiedy wykonujesz zapytanie select * , w efekcie uzyskasz kilka kolumn, których tak naprawdę nie potrzebujesz.

$posts = Post::paginate(20);

15.

$posts = Post::paginate(20); // Generates pagination links for all the pages
$posts = Post::simplePaginate(20); // Generates only next and previous pagination links

	paginate / simplePaginate
database table has only few rows and does not grow large	paginate / simplePaginate
database table has so many rows and grows quickly	simplePaginate
it is mandatory to provide the user option to jump to specific pages	paginate
it is mandatory to show the user total no of results	paginate
not actively using pagination links	simplePaginate
UI/UX does not affect from switching numbered pagination links to next / previous pagination links	simplePaginate
Using "load more" button or "infinite scrolling" for pagination	simplePaginate

select * from table_name where column like %keyword%

The above query will result in a full table scan. If We know the keyword occurs at the beginning of the column value,
We can query the results as below.

select * from table_name where column like keyword%

It is always better to avoid SQL functions in where clause as they result in full table scan. Let's look at the below
example. To query results based on the certain date, we would usually do

$posts = POST::whereDate('created_at', '>=', now() )->get();

select * from posts where date(created_at) >= 'timestamp-here'

The above query will result in a full table scan, because the where condition isn't applied until the date function
is evaluated.

$posts = Post::where('created_at', '>=', now() )->get();

select * from posts where created_at >= 'timestamp-here'

It is better to limit the total no of columns in a table. Relational databases like mysql, can be leveraged to split the tables with so many columns into multiple tables. They can be joined together by using their primary and
foreign keys.

This tip is from personal experience and is not a standard way of architecting your database tables. I recommend to
follow this tip only if your table has too many records or will grow rapidly.

If a table has columns which stores large amounts of data(ex: columns with a datatype of TEXT), it is better to separate
them into their own table or into a table which will be less frequently asked.

When the table has columns with large amounts of data in it, the size of an individual record grows really high. I
personally observed it affected the query time on one of our projects.

Consider a case where you have a table called posts with a column of content which stores the blog post content.
The content for blog post will be really huge and often times, you need this data only if a person is viewing this
particular blog post.

$posts = Post::latest()->get();
// or $posts = Post::orderBy('created_at', 'desc')->get();

select * from posts order by created_at desc

The query is basically ordering the rows in descending order based on the created_at column. Since created_at column is
a string based column, it is often slower to order the results this way.

If your database table has an auto incrementing primary key id, then in most cases, the latest row will always have the
highest id. Since id field is an integer field and also a primary key, it is much faster to order the results based on
this key. So the better way to retrieve latest rows is as below.

$posts = Post::latest('id')->get();
// or $posts = Post::orderBy('id', 'desc')->get();

select * from posts order by id desc

We so far looked into optimizing select queries for retrieving results from a database. Most cases we only need to optimize the read queries. But sometimes we find a need to optimize insert and update queries. I found an interesting article on optimizing mysql inserts
which will helps in optimizling slow inserts and updates.

There is no one universal solution when optimizing queries in laravel. Only you know what your application is doing,
how many queries it is making, how many of them are actually in use. So inspecting the queries made by your application
will help you determine and reduce the total number of queries made.

Note: It is recommended not to run any of these tools on your production environment. Running these on your production
apps will degrade your application performance and when compromised, unauthorized users will get access to sensitive information.

Laravel Debugbar - Laravel debugbar has a tab called database
which will display all the queries executed when you visit a page. Visit all the pages in your application and look at the queries executed on each page.
Clockwork - Clockwork is same as laravel debugbar. But instead of injecting a toolbar into your website, it will display the debug information in developer tools window or as a
standalone UI by visiting yourappurl/clockwork.
Laravel Telescope - Laravel telescope is a wonderful debug companion while developing laravel applications locally. Once Telescope is installed, you can access the dashboard by visiting
yourappurl/telescope. In the telescope dashboard, head over to queries tab, and it will display all the queries being executed by your application.