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, мы обычно делаем так, как показано ниже.

$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

Приведенный выше код извлекает все сообщения и отображает заголовок сообщения и его автора на веб-странице, и предполагается, что у вас есть отношения author в вашей модели сообщения.

Выполнение приведенного выше кода приведет к выполнению следующих запросов.

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.

Мы можем реорганизовать это, чтобы избежать функции sql date, как показано ниже

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.