18 порад щодо оптимізації ваших запитів до бази даних Laravel

Якщо ваша програма працює повільно або робить багато запитів до бази даних, дотримуйтеся наведених нижче порад щодо оптимізації продуктивності, щоб зменшити час завантаження програми.

1. Отримання великих наборів даних

Ця порада головним чином спрямована на покращення використання пам’яті вашою програмою під час роботи з великими наборами даних.

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.

Щоб отримати багато результатів із таблиці під назвою публікації, ми зазвичай робимо так, як показано нижче.

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

У наведених вище прикладах буде отримано всі записи з таблиці публікацій і оброблено їх. Що робити, якщо в цій таблиці 1 мільйон рядків? У нас швидко закінчиться пам'ять.

Щоб уникнути проблем під час роботи з великими наборами даних, ми можемо отримати підмножину результатів і обробити їх, як показано нижче.

Варіант 1: використання блоку

// 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
    }
});

Наведений вище приклад отримує 100 записів із таблиці публікацій, обробляє їх, отримує ще 100 записів і обробляє їх. Ця ітерація триватиме, доки не буде оброблено всі записи.

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.

Варіант 2: використання курсору

// 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
}

Наведений вище приклад зробить єдиний запит до бази даних, отримає всі записи з таблиці та гідратує моделі Eloquent одну за одною. Цей підхід зробить лише один запит до бази даних для отримання всіх публікацій. Але використовує php-генератор для оптимізації використання пам’яті.

коли це можна використовувати?

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.

Краще використовувати курсор, якщо у вашій веб-програмі, на якій працює ваша програма, менше пам’яті, а екземпляр бази даних має більше пам’яті. Однак, якщо ваш екземпляр бази даних не має достатньо пам’яті, краще дотримуватися фрагментів.

варіант 3: використання 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.

Запити, зроблені chunk і chunkById, були такими.

кусок

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

Як правило, використання обмеження зі зсувом є повільнішим, тому нам слід намагатися уникати його використання. У цій статті детально пояснюється проблема використання зсуву.

Оскільки chunkById використовує поле ідентифікатора, яке є цілим числом, а запит використовує пропозицію where, запит буде набагато швидшим.

Коли можна використовувати chunkById?

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

2.Виберіть лише потрібні стовпці

Зазвичай, щоб отримати результати з таблиці бази даних, ми робимо наступне.

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

Наведений вище код призведе до наступного запиту

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.

Натомість, якщо нам потрібні лише певні стовпці (id, title), ми можемо отримати лише ці стовпці, як показано нижче.

$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

Наведений вище код призведе до наступного запиту

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

3.Використовуйте pluck, коли вам потрібен рівно один або два стовпці з бази даних

Ця порада більше зосереджується на часі, витраченому після отримання результатів із бази даних. Це не впливає на фактичний час запиту.

Як я вже згадував вище, ми б зробили це для отримання певних стовпців

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

Коли наведений вище код виконується, він робить наступне за лаштунками.

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

Тепер, щоб отримати доступ до результатів, ми б зробили

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

Наведений вище підхід має додаткові витрати на зволоження моделі Post для кожного рядка та створення колекції для цих об’єктів. Це було б найкраще, якщо вам дійсно потрібен екземпляр моделі Post замість даних.

Але якщо вам потрібні лише ці два значення, ви можете зробити наступне.

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

Коли наведений вище код виконується, він робить наступне за лаштунками.

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 ])

Тепер, щоб отримати доступ до результатів, ми б зробили

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

Якщо ви хочете отримати лише один стовпець, це можна зробити

$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.Підрахуйте рядки за допомогою запиту замість колекції

Звичайно ми це робимо, щоб підрахувати загальну кількість рядків у таблиці

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

Це створить наступний запит

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.

Замість наведеного вище підходу ми можемо безпосередньо підрахувати загальну кількість рядків у самій базі даних.

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

Це створить наступний запит

select count(*) from posts

Підрахунок рядків у sql є повільним процесом і працює погано, коли таблиця бази даних має так багато рядків. Краще уникати підрахунку рядків, наскільки це можливо.

5. Уникайте запитів N+1 через відношення активного завантаження

Можливо, ви чули про цю пораду мільйон разів. Тому я буду максимально коротким і простим.Припустімо, що у вас такий сценарій

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

Наведений вище код отримує всі публікації та відображає назву публікації та її автора на веб-сторінці, і він припускає, що у вашій моделі публікації є зв’язок автор.

Виконання наведеного вище коду призведе до виконання наступних запитів.

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 }

Як бачите, у нас є один запит для отримання дописів і 5 запитів для отримання авторів дописів (оскільки ми припустили, що маємо 5 дописів.) Отже, для кожної отриманої допису він робить один окремий запит для отримання. її автор.

Отже, якщо є N дописів, він зробить N+1 запит (1 запит для отримання дописів і N запитів для отримання автора для кожного допису).

Часто ми робимо непотрібні запити до бази даних. Розглянемо наведений нижче приклад.

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

Проблема полягає в тому, коли ми робимо

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 } )

Ми можемо змінити нашу логіку, щоб уникнути цього додаткового запиту.

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

Змінюючи нашу логіку на наведену вище, ми робимо два запити для користувача адміністратора та один запит для всіх інших користувачів.

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

9.Об’єднати подібні запити

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.

Іноді нам потрібно робити запити, щоб отримати різні типи рядків з однієї таблиці.

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

Виконання наведеного вище коду призведе до виконання наступних запитів.

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.Використовуйте simplePaginate замість Paginate

Як правило, ми це робимо, розбиваючи результати на сторінки

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

Це створить два запити: перший для отримання результатів із розбиттям на сторінки, а другий для підрахунку загальної кількості рядків у таблиці. Підрахунок рядків у таблиці є повільною операцією, яка негативно вплине на продуктивність запиту.

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

То чому laravel підраховує загальну кількість рядків?

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

Для створення посилань на сторінки Laravel підраховує загальну кількість рядків. Отже, коли генеруються посилання на розбивку сторінок, ви заздалегідь знаєте, скільки там буде сторінок і який номер попередньої сторінки. Тож ви можете легко переходити до будь-якої потрібної сторінки.

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

З іншого боку, виконання simplePaginate не рахуватиме загальну кількість рядків, і запит буде набагато швидшим, ніж підхід paginate. Але ви втратите можливість знати номер останньої сторінки та переходити на інші сторінки.

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.

Якщо у вашій таблиці бази даних так багато рядків, краще уникати paginate і натомість використовувати simplePaginate.

Коли використовувати розбиття на сторінки, а не просте?

<?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.

Подивіться на наведену нижче порівняльну таблицю та визначте, чи підходить вам розбивка на сторінки чи проста розбивка на сторінки

$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.Уникайте використання початкових символів підстановки (ключове слово LIKE)

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

Коли ми намагаємося запитати результати, які відповідають певному шаблону, ми зазвичай використовуємо

13. уникайте використання функцій SQL у пропозиції where

Це призведе до запиту, подібного до наведеного нижче

$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.

Ми можемо змінити це, щоб уникнути функції date sql, як показано нижче

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.уникайте додавання забагато стовпців до таблиці

Додавання забагато стовпців до таблиці збільшить довжину окремого запису та сповільнить сканування таблиці. Коли ви виконуєте запит select * , ви отримаєте купу стовпців, які вам справді не потрібні.

$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.