Паттерны улучшения производительности Eloquent

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

Первый шаг в оптимизации производительности базы данных в приложении Laravel — это эффективное измерение её производительности. В этом могут помочь несколько инструментов, наиболее распространённым из которых является Laravel Debug Bar. Кроме того, настоятельно рекомендую использовать Laravel Telescope для более глубокого анализа.

Рассмотрим три ключевых показателя, которые необходимо отслеживать при использовании Laravel Debug Bar или Telescope для оптимизации производительности базы данных:

Использование памяти

Если приложение на базе Laravel 12 потребляет значительно больше 1MB оперативной памяти на один запрос, виновниками этого зачастую являются неэффективные запросы к базе данных.

Запросы к базе данных

Время выполнения запроса и дубликаты запросов (или N+1) — наиболее важные метрики, помогающие понять производительность приложения Laravel.

Загружаемые модели

Иногда запросы к базе данных могут быть быстрыми и не содержать дубликатов, но при этом возвращать большое количество моделей Eloquent. Это может привести к большим затратам памяти в приложении. Распространённое решение — переложить тяжёлые логические операции на уровень базы данных.

Минимизируйте использования памяти

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

// ❌ Вместо

Post::get();

Post::find(id: 1);

// ✅ Используется

Post::get(columns: ['id', 'title', 'published_at']);

Post::find(id: 1, columns: ['id', 'title', 'body', 'published_at']);

Используйте подзапросы

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

При оптимизации запросов к базе данных для повышения производительности основное внимание следует уделять минимизации количества запросов к базе данных и использованию памяти. Хорошей отправной точкой для минимизации количества запросов является решение N+1 запросов. Следующий шаг — сокращение использования памяти запросами к базе данных, и здесь на помощь приходят подзапросы.

Допустим, необходимо загрузить данные, когда пользователь в последний раз логинился в приложении. Стандартным решением будет определение отношения HasMany между моделью User и моделью Login, записывающей действия пользователя при логине.

class User extends Model
{
    public function loginActivity(): HasMany
    {
        return $this->hasMany(Login::class)
    }
}

Теперь можно выполнять жадную загрузку активности логина и отсортировать её для получения самой последней.

//❌ Не делайте так

$user = User::with('loginActivity')->first();

$lastLoginAt = $user->loginActivity->sortByDesc('created_at')->first()->created_at;

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

Решить проблему с памятью можно используя подзапросы. Подзапросы могут возвращать только один столбец, а это значит, что можно загрузить дату последнего логина в качестве столбца в модели User с помощью метода Eloquent addSelect(). Поскольку возвращается только один столбец, необходимо упорядочить активность по последнему логину и взять первую запись.

//✅ Сделайте так

$user = User::addSelect(['last_login_at'=> Login::select('created_at')
            ->whereColumn('user_id', 'users.id')
            ->latest()
            ->take(1)
        ])->first();

$lastLoginAt = $user->last_login_at;

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

Динамические связи с помощью подзапросов

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

Например, если нужна дополнительная информация, такая как IP-адрес логина, тип устройства и браузер, то запрос будет выглядеть следующим образом:

//❌ Не делайте так

$user = User::query()
            ->addSelect(['last_login_at'=> Login::select('created_at')
                ->whereColumn('user_id', 'users.id')
                ->latest()
                ->take(1)
            ])
            ->addSelect(['ip_address'=> Login::select('ip_address')
                ->whereColumn('user_id', 'users.id')
                ->latest()
                ->take(1)
            ])
            ->addSelect(['browser'=> Login::select('browser')
                ->whereColumn('user_id', 'users.id')
                ->latest()
                ->take(1)
            ])
            ->first();

$lastLoginAt = $user->last_login_at;
$ipAddress = $user->ip_address;
$browser = $user->browser;

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

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

Например, если необходимо загрузить последний логин в качестве модели, необходимо определить связь BelongsTo в модели User, возвращающую модель Login.

class User extends Model
{
    public function lastLoginActivity(): BelongsTo
    {
        return $this->belongsTo(Login::class)
    }
}

Чтобы эта связь работала, таблица users должна включать столбец last_login_id в качестве внешнего ключа. Однако используя подзапрос, можно получить этот столбец, не прибегая к нормализации таблицы users. С помощью подзапроса можно предоставить столбец внешнего ключа, а с помощью жадной загрузки можно загрузить динамическое отношение lastLoginActivity, используя столбец last_login_id, предоставленный подзапросом. Это выглядит следующим образом:

//✅ Сделайте так

$user = User::addSelect(['last_login_id'=> Login::select('id')
            ->whereColumn('user_id', 'users.id')
            ->latest()
            ->take(1)
        ])
        ->with('lastLoginActivity')
        ->first();

$lastLoginAt = $user->last_login_at;
$ipAddress = $user->ip_address;
$browser = $user->browser;

Подзапросы с использованием области Eloquent

С помощью области Eloquent можно очистить подзапросы и сделать их универсальными. Давайте начнём с добавления области в модель User для столбца last_login_at из первого примера подзапроса.

class User extends Model
{
    public function scopeWithLastLoginAt(Builder, $query): BelongsTo
    {
        $query->addSelect(['last_login_at'=> Login::select('created_at')
            ->whereColumn('user_id', 'users.id')
            ->latest()
            ->take(1)
        ]);
    }
}

Теперь будем использовать эту область всякий раз, когда необходимо подключить колонку last_login_at для пользователя. Например:

$user = User::withLastLoginAt()->first();

$lastLoginAt = $user->last_login_at;

Для второго примера подзапроса можно сделать то же самое, создав область, жадную загрузку динамической связи lastLoginActivity, как показано ниже:

class User extends Model
{
    public function scopeWithLastLoginActivity(Builder, $query): BelongsTo
    {
        $query->addSelect(['last_login_id'=> Login::select('id')
            ->whereColumn('user_id', 'users.id')
            ->latest()
            ->take(1)
        ])->with('lastLoginActivity');
    }
}

Теперь можем загрузить динамические связи следующим образом:

$user = User::withLastLoginActivity()->first();

$lastLoginAt = $user->last_login_at;
$ipAddress = $user->ip_address;
$browser = $user->browser;

Циклические связи с использованием Chaperone()

Все связи указывают друг на друга, что означает круговую взаимосвязь. Например, если у нас есть модель Book и Category, мы знаем, что книга принадлежит категории, а в категории может быть много книг.

Циклические связи обычно приводят к N+1 запросам, что происходит, когда мы пытаемся получить доступ к родительской модели из дочерней модели, перебирая дочерние модели, как в этом случае:

$categories = Category::with('books')->get();

foreach ($categories as $category) {
    foreach ($category->books as $book) {
        echo $book->category->title; // N+1 запросы, из-за ленивой загрузки связей category
    }
}

Теперь возникла проблема с N+1 запросом, из-за ленивой загрузки category, родительских связей модели Book.

В Laravel 11 появился метод chaperone(), решающий эту проблему путём гидратации родительских моделей на дочерние без повторной загрузки их из базы данных. Метод chaperone() можно вызвать во время выполнения следующим образом:

$categories = Category::with(['books' =>  fn ($books) => $books->chaperone()])->get();

Также можно вызвать метод chaperone(), когда определяются дочерние отношения. В приведённом выше примере с категорией книг это выглядит следующим образом:

class Category extends Model
{
    public function books(): HasMany
    {
        return $this->hasMany(Book::class)->chaperone();
    }
}

Используйте индексы

Индексы — специальные таблицы поиска, используемые поисковой системой базы данных для ускорения поиска данных.

Индексация столбцов — концепция, повышающая производительность любого приложения, подключённого к базе данных, и Laravel предоставляет элегантный способ добавления индексов, но не говорит, где и когда их добавлять.

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

Продемонстрирую несколько моментов, на которые следует обратить внимание, когда думаете об индексации.

Поиск возможных индексов с помощью SQL оператора EXPLAIN

Выражение EXPLAIN предоставляет информацию, как движок базы данных выполняет запросы, в том числе указывает, где нужно добавить индексы в таблицу, чтобы запрос выполнялся быстрее.

В конструкторах и Eloquent запросах в Laravel есть метод explain(), позволяющий получить дамп SQL-запроса и проанализировать его выполнение. Например:

Category::select(['id', 'name'])->explain()->dd();

Результат выглядит так:

array:1 [
  0 => {#3708
    +"id": 1
    +"select_type": "SIMPLE"
    +"table": "categories"
    +"partitions": null
    +"type": "ALL"
    +"possible_keys": null
    +"key": null
    +"key_len": null
    +"ref": null
    +"rows": 29
    +"filtered": 10.0
    +"Extra": "Using where"
  }
]

Из массива, выведенного выражением EXPLAIN, следует обратить внимание на элемент possible_keys, указывающий, может ли быть использован какой-либо индексированный столбец, и элемент key, указывающий на индексированный столбец, который был использован в запросе. Эта команда доступна как для MySQL, так и для PostgreSQL.

Многоколоночная индексация

Многоколоночная индексация очень эффективна, когда в одном выражении WHERE запрашивается несколько столбцов. Например, при выполнении поиска по столбцам first_name и last_name модели User запрос может выглядеть следующим образом:

$search = 'Jude';

$result = User::where(
        fn (Builder $query) => $query
            ->where('first_name', "$search%")
            ->orWhere('last_name', "$search%")
    )->get();

Определение отдельных индексов для этих столбцов не окажет никакого влияния на данный запрос. Чтобы оптимизировать такой запрос, необходимо создать многоколоночный индекс на столбцах в выражении WHERE, в данном случае на столбцах first_name и last_name. Это необходимо сделать внутри файла миграции таблицы users, как показано ниже:

// migration
Schema::create('users', function (Blueprint $table) {
    ...
    $table->index(['first_name', 'last_name']);
});

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

Использование Union для независимого выполнения запросов

При запросе нескольких таблиц в одном запросе SELECT, например в поисковом запросе, соответствующем термину в двух разных таблицах, использование UNION может дать более быстрые результаты. Это происходит потому, что запрос UNION состоит из отдельных запросов, каждый из которых может использовать преимущества индексов в отдельных таблицах.

Например, если есть таблица employees и таблица companies с более чем 100 000 записей, где каждый employee относится к company, и необходимо выполнить поиск по name сотрудника или name компании (с индексацией столбцов поиска соответственно), то первый подход, использующий подзапросы, будет выглядеть следующим образом:

//❌ Не делайте так

$searchTerm = 'taylor';

Employee::with('company')
    ->where(fn($query) => $query
    ->where('name', 'LIKE', "$searchTerm%")
    ->orWhereIn('company_id', fn($query) => $query
        ->select('id')
        ->from('companies')
        ->where('name', 'LIKE', "$searchTerm%")))
    ->get()

Проблема с подзапросом заключается в том, что он не использует индекс столбца name сотрудника в таблице employees, что, в свою очередь, делает запрос медленным, поскольку подзапрос для поиска названия компании не использует индекс столбца name. При тестировании на 200 000 записей на MacBook Pro 2021 M1 эти запросы — несмотря на то, что это всего два запроса — выполнялись более 300 мс.

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

//✅ Сделайте так

Employee::with('company')
    ->where(fn($query) => $query
        ->where('name', 'LIKE', "$searchTerm%")
        ->orWhereIn('company_id', Company::where('name', 'LIKE', "$searchTerm%")->pluck('id'))) // независимый запрос
    ->get()

Этот запрос работает лучше, чем первый, поскольку использует индекс столбца имени сотрудника, а независимый запрос для поиска названия компании сотрудника также использует индекс столбца имени в таблице компаний. Однако такой подход вводит третий запрос, хотя и улучшает время отклика, сокращая его с более чем 300 мс до 4 мс.

Третий подход заключается в использовании UNION для выполнения этих запросов независимо друг от друга в виде одной операции. Цель состоит в том, чтобы сократить количество запросов, используя при этом индексы, определённые для данных столбцов. Запрос UNION выглядит следующим образом:

//✅ Сделайте так

$employeeQuery = DB::query()
    ->select('id')
    ->from('employees')
    ->where('name', 'LIKE', "$searchTerm%");

$companyQuery = DB::query()
    ->select('employees.id')
    ->from('employees')
    ->join('companies', 'companies.id', '=', 'employees.company_id')
    ->where('companies.name', 'LIKE', "$searchTerm%");

Employee::with('company')
    ->whereIn('id', fn (Builder $query) => $query
        ->select('id')
        ->from(table: $employeeQuery->union($companyQuery), as: 'matches'))
    ->get();

С помощью запроса UNION количество запросов сократилось до двух, и все индексы были задействованы. Самое главное, улучшилось время отклика, сократив длительность запроса с 4 мс до 2 мс.

Как полностью избавиться N+1 запросов

Чтобы избавиться N+1 запросов в приложениях Laravel, особенно во время разработки, можно отключить ленивую загрузку. Это можно сделать так, чтобы не повлиять на окружение продакшн, добавив приведённый ниже фрагмент в метод boot провайдера AppServiceProvider:

public function boot()
{
    Model::preventLazyLoading(! app()->isProduction());
}

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

Комментарии