18 советов по оптимизации запросов к базе данных

Если ваше приложение медленно работает или делает много запросов к БД, то используйте наши советы, чтобы сократить время его загрузки. Мы исследуем методы оптимизации MySQL-, eloquent- и просто сырых запросов к базе данных.

1.Используйте Chunk для получения массивных наборов данных

Совет направлен на улучшение использования памяти. Если вашему приложению требуется обрабатывать большой набор записей, то вместо получения их всех сразу, вы можете работать с ними по небольшим частям.

Например, чтобы получить все данные из таблицы posts, мы обычно поступаем так, как показано ниже.

При использовании Eloquent

$posts = Post::all();

foreach ($posts as $post){
// обрабатываем
}

При использовании Конструктора запросов

$posts = DB::table('posts')->get();

foreach ($posts as $post){
// обрабатываем
}

Приведенные выше примеры извлекут все записи из таблицы и обработают их. А если в этой таблице миллионы строк? У вас быстро закончится память.

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

При использовании Eloquent

$posts = Post::chunk(100, function($posts){
    foreach ($posts as $post){
     // обрабатываем
    }
});

При использовании Конструктора запросов

$posts = DB::table('posts')->chunk(100, function ($posts){
    foreach ($posts as $post){
     // обрабатываем
    }
});

В этом примере из таблицы извлекается 100 записей, они обрабатываются, затем извлекается еще 100 записей и они снова обрабатываются. Этот цикл будет продолжаться до тех пор, пока не будут обработаны все записи.

Такой подход приведет к увеличению количества запросов к базе данных, но при этом память будет использоваться очень эффективно. Обычно обработка таких массивных данных выполняется в фоновом режиме, поэтому количество запросов к базе данных не так важно, как важно избежать нехватки памяти.

2. Выбирайте только нужные поля

Обычно для получения результатов из таблицы базы данных мы делаем следующее.

// eloquent
$posts = Post::find(1);

// конструктор запросов
$posts = DB::table('posts')->where('id','=',1)->first();

Приведенный выше код сделает такой запрос:

select * from posts where id = 1 limit 1

Как видите, запрос делает select *. Это означает, что будут получены все поля из таблицы. Это хорошо, если нам действительно нужны все поля.

Но если нам нужны только определенные (id, title), то мы можем получить только их:

// eloquent
$posts = Post::select(['id','title'])->find(1);

// конструктор запросов
$posts = DB::table('posts')->where('id','=',1)->select(['id','title'])->first();

Приведенный выше код сделает такой запрос:

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

3. Используйте pluck, если нужно одно-два поля из таблицы

В этом совете больше внимания уделяется времени потраченному после получения результатов из базы данных. Совет не влияет на фактическое время запроса.

Как я уже упоминал выше, для получения определенных полей мы делаем так:

// eloquent
$posts = Post::select(['title','slug'])->get();

// конструктор запросов
$posts = DB::table('posts')->select(['title','slug'])->get();

Когда этот код выполняется, то происходит следующее:

Выполняется запрос select title, slug from posts к базе данных
Создается новый объект модели Post для каждой полученной строки (для конструктора запросов создается стандартный PHP-объект)
Создается новая коллекция с моделями Post
Возвращается коллекция

Теперь, чтобы получить доступ к результатам, мы делаем:

foreach ($posts as $post){
    // $post это модель Post или стандартный php-объект
    $post->title;
    $post->slug;
}

Этот подход требует дополнительных накладных расходов на создание модели Post для каждой строки и создание коллекции для этих объектов. Хорошо, если вам действительно нужна модель Post. Но если вам нужны только пара значений, то вы можете сделать следующее.

// eloquent
$posts = Post::pluck('title', 'slug');

// конструктор запросов
$posts = DB::table('posts')->pluck('title','slug');

Когда этот код выполняется, то он делает следующее:

Выполняется запрос select title, slug from posts к базе данных
Создается массив с title в качестве значения и slug в качестве ключа
Возвращается массив в формате: [ slug => title, slug => title ])

Теперь, чтобы получить доступ к результатам, мы делаем

foreach ($posts as $slug => $title){
    // $title это title из post
    // $slug это slug из post
}

Если нужно получить только одно поле, то вы можете сделать так

// eloquent
$posts = Post::pluck('title');

// конструктор запросов
$posts = DB::table('posts')->pluck('title');

foreach ($posts as  $title){
    // $title это title из post
}

Этот подход исключает создание объекта Post для каждой строки. Таким образом сокращается использование памяти и время, затрачиваемое на обработку результатов запроса.

Я бы рекомендовал использовать описанный выше подход только для свеженаписанного кода. Возвращаться и рефакторить старый код по этому методу не стоит потраченного времени. Рефакторите, только если ваш код обрабатывает большие наборы данных или если у вас есть свободное время.

4. Подсчет строк с помощью запроса вместо коллекции

Чтобы подсчитать общее количество строк в таблице, мы обычно делаем:

// eloquent
$posts = Post::all()->count();

// конструктор запросов
$posts = DB::table('posts')->get()->count();

Это сгенерирует следующий запрос

select * from posts

Будут извлечены все строки из таблицы, загружены в collection-объект и подсчитан общий результат. Это отлично работает для небольших таблиц. Но по мере их роста у нас быстро закончится память.

Вместо этого мы можем напрямую подсчитать общее количество строк в самой базе данных.

// eloquent
$posts = Post::count();

// конструктор запросов
$posts = DB::table('posts')->count();

Это сгенерирует следующий запрос

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

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

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

select * from posts // предположим, что запрос вернул 5 сообщений
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 запросов для получения автора для каждого сообщения). Обычно это называется проблема N+1.

Чтобы избежать этого, нужно сразу загрузить информацию об авторах, как показано ниже.

// избегайте делать так
$posts = Post::all();

// лучше делайте так
$posts = Post::with(['author'])->get();

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

select * from posts // предположим, что запрос вернул 5 сообщений
select * from authors where id in( { post1.author_id }, { post2.author_id }, { post3.author_id }, { post4.author_id }, { post5.author_id } )

6. Вложенные отношения «жадной загрузки»

Дополнительно к выше приведённому примеру, предположим, что автор принадлежит к команде и вы также хотите отобразить название команды. В blade-шаблоне сделаем так:

@foreach($posts as $post)
    <li>
        <h3>{{ $post->title }}</h3>
        <p>Автор: {{ $post->author->name }}</p>
        <p>Команда автора: {{ $post->author->team->name }}</p>
    </li>
@endforeach

Теперь делаем следующее

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

Результатом будут следующие запросы

select * from posts // предположим, что запрос вернул 5 сообщений
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 }

Как видите, несмотря на то, что мы заранее загрузили отношения authors, все равно появилась проблема N+1. Это потому, что мы не использовали жадную загрузку для отношений team к authors.

Исправим это, выполнив следующие действия.

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

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

select * from posts // предположим, что запрос вернул 5 сообщений
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 до 3.

7. Не загружайте отношение belongsTo, если вам нужен только его идентификатор

Представим, что у нас есть две таблицы posts и authors. В таблице posts есть поле author_id, которое определяет отношение belongsTo к таблице authors.

Чтобы получить идентификатор автора сообщения, мы обычно делаем:

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

Что приведет к выполнению двух запросов.:

select * from posts where id = limit 1
select * from authors where id = limit 1

Вместо этого можно напрямую получить идентификатор автора, выполнив следующее:

$post = Post::findOrFail();
$post->author_id; // в таблице posts есть поле author_id, содержащее идентификатор автора

Когда я могу использовать этот подход?
Когда уверены, что поле имеется в таблице авторов и на нее есть ссылка в таблице сообщений.

8. Избегайте лишних запросов

Часто мы делаем запросы к базе данных, в которых нет необходимости. Рассмотрим пример ниже:

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

Этот код извлекает записи из двух разных таблиц (posts, private_posts) и передает их в шаблон, который выглядит следующим образом:

// posts/index.blade.php

@if( request()->user()->isAdmin() )
    <h2>Приватные сообщения</h2>
    <ul>
        @foreach($private_posts as $post)
            <li>
                <h3>{{ $post->title }}</h3>
                <p>Опубликовано: {{ $post->published_at }}</p>
            </li>
        @endforeach
    </ul>
@endif

<h2>Сообщения</h2>
<ul>
    @foreach($posts as $post)
        <li>
            <h3>{{ $post->title }}</h3>
            <p>Опубликовано: {{ $post->published_at }}</p>
        </li>
    @endforeach
</ul>

Как вы видите, $private_posts видны только админам. Остальные пользователи не могут видеть эти сообщения.

Проблема здесь в том, когда мы делаем

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

то происходит два запроса: один для получения записей из таблицы posts, а второй для получения записей из таблицы private_posts.

Записи из private_posts видны только администраторам. Но мы делаем запрос на получение этих записей для всех пользователей, даже если им они не видны.

Нужно изменить логику кода, чтобы избежать этого лишнего запроса.

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

Теперь мы делаем два запроса для администратора и один запрос для всех остальных пользователей.

9. Объединяте похожие запросы

Иногда нам нужно делать запросы для получения разных записей из одной и той же таблицы.

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

Этот код извлекает записи с разным статусом из одной и той же таблицы и приводит к выполнению следующих запросов:

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

Как видите, он выполняет 3 разных запроса к одной и той же таблице. Нужно отрефакторить этот код, чтобы был только один запрос к базе данных.

$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' )

Также, путем фильтрации, будут созданы отдельные коллекции для каждого статуса. Таким образом, у нас по-прежнему будут три разные переменные и всего один запрос.

10. Добавьте индекс к часто запрашиваемым полям

Если вы делаете запросы, добавляя условие where к string-полю, то добавьте индекс. Запросы выполняются намного быстрее при запросе индексированных полей.

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

В этому примере мы запрашиваем записи с определенным статусом. Мы можем улучшить производительность запроса, добавив следующую миграцию:

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

11. Используйте simplePaginate вместо Paginate

При разбивке результатов на страницы мы обычно делаем так:

$posts = Post::paginate(20);

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

Почему Laravel подсчитывает общее количество строк?

Они нужны для создания ссылок на страницы. Когда сгенерированы ссылки для пагинации, вы заранее знаете, сколько страниц там будет и какая страница последняя, чтобы можно было легко перейти к любой нужной.

Использование же simplePaginate будет более быстрым, так как не будет посчитано общее количество строк. Но тогда вы ну получите номер последней страницы и возможность переходить на другие.

Если в вашей таблице очень много записей, то избегайте paginate, и используйте, вместо неё, simplePaginate.

// генерирует ссылки для всех страниц
$posts = Post::paginate(20);

// генерирует только ссылки на следующую и предыдущую страницы
$posts = Post::simplePaginate(20);

Когда использовать Paginate, а когда SimplePaginate?

Определить подходящий метод можно по следующим определениям:

в таблице мало записей и в будущем их количество сильно не увеличится — paginate/simplePaginate
в таблице много записей и их количество быстро растёт — simplePaginate
необходимость обязательно предоставить пользователю возможность перехода на определенные страницы — paginate
необходимость обязательно показать пользователю общее количество страниц — paginate
пагинация используется редко — simplePaginate
UI/UX не пострадает при переключении пагинации с «номера страниц» к «предыщуая/следущая» — simplePaginate
использование ссылок «загрузить еще» или использовании «бесконечной прокрутки» для разбивки на страницы — simplePaginate

12. Избегайте использования подстановочных знаков в начале (LIKE ключевое_слово)

При запросе результатов, соответствующих определенному шаблону, обычно мы используем:

select * from table_name where column like %keyword%

Этот запрос приведет к полному сканированию таблицы. Но если мы знаем, что ключевое слово встречается в начале поля, то можем запросить результаты следующим образом:

select * from table_name where column like keyword%

13. Избегайте использования SQL-функций в условии where

Так как они приводят к полному сканированию таблицы. Давайте посмотрим на примере. Чтобы получить результаты на основе определенной даты, обычно мы делаем:

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

Выполнится следующий запрос:

select * from posts where date(created_at) >= 'здесь-текущая-временная-метка'

Который приведет к полному сканированию таблицы, поскольку условие where не применится до тех пор, пока функция date не будет вычислена.

Мы можем отрефакторить код, чтобы избежать использования sql-функции:

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

Выполнится запрос:

select * from posts where created_at >= 'здесь-текущая-временная-метка'

14. Не добавляйте слишком много полей в таблицу

Лучше ограничить общее количество полей в таблице. В реляционных базах данных, такие как MySQL, можно разделять большие таблицы на несколько мелких и, при необходимости, джойнить (join) их через первичные и внешние ключи.

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

15. Выделяйте поля с массивными данными в отдельную таблицу

Этот совет основан на личном опыте и не является стандартным способом построения таблиц базы данных. Я рекомендую использовать его, если в вашей таблице слишком много записей или планируется её быстрый рост.

Если в таблице есть поля, в которых хранятся большие объемы данных (например, TEXT-поля), лучше выделить их в отдельную таблицу, которая будет запрашиваться реже.

Когда в таблице есть подобные поля, размер отдельной записи становится очень большим, и это сильно влияет на время запроса.

Например, у вас есть таблица posts с полем content в котором хранится текст статьи. Статья, скорей всего будет огромная, а её содержимое будет нужно, только если пользователь зашел на страницу с ней.

Таким образом, вывод этого поля из таблицы значительно повысит производительность запроса.

16. Лучший способ для получения последней записи из таблицы

Когда мы хотим получить последнюю запись из таблицы, мы часто делаем так:

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

Это создаст следующий sql-запрос:

select * from posts order by created_at desc

Этот запрос просто сортирует записи в порядке убывания на основе поля created_at. Поскольку created_at это строковое поле, то такой способ сортировки не самый быстрый.

Если в вашей таблице есть первичный ключ с автоинкрементом, то в большинстве случаев последняя запись всегда будет иметь наибольший идентификатор. Поскольку поле id является целочисленным полем, а также первичным ключом, то гораздо быстрее отсортировать результаты на основе этого ключа. Соответственно, лучший способ получить последние записи:

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

получим sql-запрос:

select * from posts order by id desc

17. Оптимизируйте MySQL INSERTS

До этого мы рассматривали оптимизацию select-запросов для получения результатов из базы данных. Да и, в большинстве случаев, нам нужно оптимизировать только их. Но иногда возникает необходимость в оптимизации запросов insert и update. Я нашел интересную статью «Ускорение медленных insert’ов», обязательно ознакомьтесь с ней.

от переводчика: смысл статьи:
— используйте ssd
— увеличьте innodb_log_file_size до 500M
— установите innodb_flush_log_at_trx_commit = 2
— установите innodb_flush_log_at_timeout = 5

18. Проверяйте и оптимизируйте запросы

Не существует универсального решения для оптимизации запросов в Laravel. Только вы сами знаете, что делает ваше приложение, сколько запросов оно совершает и сколько из них фактически используется. Таким образом, проверка запросов, сделанных вашим приложением, поможет вам определить и уменьшить их общее количество.

Существуют определенные инструменты, которые помогут вам проверить совершенные запросы на каждой странице.

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

Laravel Debugbar — в нём есть вкладка database, которая отображает все запросы, выполняемые при посещении страницы. Зайдите на все страницы в своем приложении и просмотрите запросы, выполняемые на них.

Clockwork — это то же самое, что и Laravel Debugbar. Но вместо того, чтобы внедрять панель прямо в ваш веб-сайт, она будет отображать отладочную информацию в developer tools window или в отдельном интерфейсе по адресу /clockwork.

Laravel Telescope — прекрасный помощник для отладки при локальной разработке. После его установки вы получите панель управления по адресу /telescope. Перейдите на вкладку queries, на которой будут отображаться все запросы, выполняемые вашим приложением.

Автор: Srinath Dudi
Перевод: Алексей Широков

Наш Телеграм-канал — следите за новостями о Laravel.