Как обнаружить проблемы с SQL-запросами N+1 на ранней стадии для проектов Laravel
20 ноября 2022 г.В этой статье я опишу свои любимые инструменты для выявления проблем N+1 в запросах к базе данных для проектов Laravel.
Это пригодится при разработке новых проектов или при отладке медленных ответов из любого устаревшего кода, с которым вам придется работать.
Пример цели
У нас есть устройства в дикой природе (Модель устройства), сообщающие о температуре (Образец модели). Каждый образец хранится в базе данных.
Наша задача: создать API для возврата JSON с последними 100 сэмплами по всей платформе и предоставить идентификатор оборудования и местоположение устройства для каждого семпла.
Структура ответа:
{
"data": [
{
"temp": 18,
"hardware_id": "8381fa1a-d2b3-3c67-815a-6884b80099d4",
"location": "Rauport",
"datetime": "2022-07-26 16:03:32"
},
{
"temp": -8,
"hardware_id": "e934c789-2326-37ab-82cc-37aec840fcff",
"location": "Braxtonhaven",
"datetime": "2022-07-26 16:05:02"
},
{
"temp": -5,
"hardware_id": "4694d32b-4ce2-3971-94f7-76664fbf872c",
"location": "Thaliaberg",
"datetime": "2022-07-26 16:09:59"
}
]
}
Я намеренно создам запрос N+1 внутри объекта сопоставления ресурсов, а затем мы рассмотрим способы отладки проблем такого типа и их раннее обнаружение.
Глобальный обзор
Модель устройства
<?php
declare(strict_types=1);
namespace AppModels;
use IlluminateDatabaseEloquentFactoriesHasFactory;
use IlluminateDatabaseEloquentModel;
use IlluminateDatabaseEloquentRelationsHasMany;
class Device extends Model
{
use HasFactory;
protected $fillable = [
'hardware_id',
'location',
];
public function samples(): HasMany
{
return $this->hasMany(Sample::class);
}
}
Образец модели с отношением устройства
<?php
declare(strict_types=1);
namespace AppModels;
use IlluminateDatabaseEloquentFactoriesHasFactory;
use IlluminateDatabaseEloquentModel;
use IlluminateDatabaseEloquentRelationsBelongsTo;
class Sample extends Model
{
use HasFactory;
public $timestamps = false;
protected $fillable = [
'device_id',
'temp',
'created_at',
];
public function device(): BelongsTo
{
return $this->belongsTo(Device::class);
}
}
Класс сеялки, чтобы иметь некоторые данные
<?php
declare(strict_types=1);
namespace DatabaseSeeders;
use AppModelsDevice;
use AppModelsSample;
use IlluminateDatabaseSeeder;
class DatabaseSeeder extends Seeder
{
public function run(): void
{
$devices = Device::factory()->count(20)->create();
$ids = $devices->pluck('id');
for ($i = 0; $i < 100; $i++) {
Sample::factory()->create([
'device_id' => $ids->random(1)->first(),
]);
}
}
}
Ресурс JSON
<?php
declare(strict_types=1);
namespace AppHttpResources;
use AppModelsSample;
use IlluminateContractsSupportArrayable;
use IlluminateHttpResourcesJsonJsonResource;
/**
* @property-read Sample $resource
*/
class SampleResource extends JsonResource
{
public function toArray($request): array|JsonSerializable|Arrayable
{
return [
'temp' => $this->resource->temp,
'hardware_id' => $this->resource->device?->hardware_id,
'location' => $this->resource->device?->location,
'datetime' => $this->resource->created_at,
];
}
}
Контроллер (сломанная версия)
<?php
declare(strict_types=1);
namespace AppHttpControllers;
use AppHttpResourcesSampleResource;
use AppModelsSample;
use IlluminateHttpResourcesJsonAnonymousResourceCollection;
class SampleController extends Controller
{
public function last100(): AnonymousResourceCollection
{
return SampleResource::collection(
Sample::latest()->limit(100)->get()
);
}
}
Как видите, здесь мы представили задачу запроса N+1. В этом случае исправить очень просто: нужно просто добавить ->with(['device']).
Контроллер (фиксированная версия)
<?php
declare(strict_types=1);
namespace AppHttpControllers;
use AppHttpResourcesSampleResource;
use AppModelsSample;
use IlluminateHttpResourcesJsonAnonymousResourceCollection;
class SampleController extends Controller
{
public function last100(): AnonymousResourceCollection
{
return SampleResource::collection(
Sample::latest()->with(['device'])->limit(100)->get()
);
}
}
Наши варианты
Для решения такого рода проблем у нас есть следующие варианты:
* Laravel 8, Laravel 9+ - отключена ленивая загрузка * Отладочная панель Laravel * Телескоп * Журнал запросов к БД
Laravel 8, Laravel 9 — отключена ленивая загрузка
https://laravel.com/docs/9.x /eloquent-relationships#preventing-lazy-loading?embedable=true
Хороший вариант, когда вы начинаете с нуля.
Включите это глобально для непроизводственной среды.
Это все, что вам когда-либо понадобится.
Не вариант, если вы наследуете уже большой проект, поэтому вы можете начать отключать отложенную загрузку в своих новых тестовых примерах (и некоторых старых) и посмотреть, не сломается ли он.
Пример теста:
<?php
/** @test */
public function last100_lazy_load_disabled_when_correct_request_then_has_expected_query_count(): void
{
Model::preventLazyLoading();
$this->seed(DatabaseSeeder::class);
// act
$this->withoutExceptionHandling();
$response = $this->getJson(route('api.last100'));
// assert
$response->assertOk();
}
Пока наш маршрут не исправлен, мы получим непройденный тест.
**IlluminateDatabaseLazyLoadingViolationException: попытка отложенной загрузки [устройство] в модели [AppModelsSample], но отложенная загрузка отключена.**
Все упомянутые далее пакеты полезны в повседневной разработке, а их API можно внедрить в тесты, чтобы найти узкие места и изучить поведение вашего приложения.
Панель отладки Laravel
https://github.com/barryvdh/laravel-debugbar?embedable=true
Тестовый пример Laravel Debugbar:
<?php
/** @test */
public function last100_debugbar_when_correct_request_then_has_expected_query_count(): void
{
$this->seed(DatabaseSeeder::class);
$debugbar = new LaravelDebugbar();
$debugbar->boot();
// act
$this->getJson(route('api.last100'));
// assert
$queryCount = count($debugbar->collect()['queries']['statements']);
$this->assertSame(2, $queryCount);
}
Мы ожидаем, что наш маршрут будет использовать только 2 запроса: первый — выбрать 100 образцов, второй — выбрать все устройства с помощью whereIn(…devices_id…).
Пока наш маршрут не исправлен, мы получим непройденный тест.
**_Не удалось подтвердить, что 101 идентично 2._**
ПРОФЕССИОНАЛЬНЫЙ СОВЕТ:
Используйте DEBUGBAR_ENABLED=false в файле phpunit.xml или .env.testing, чтобы тесты не требовали дополнительных затрат на отладку (когда это не требуется).
Телескоп
https://laravel.com/docs/9.x/telescope?embedable=true
Тестовый пример телескопа:
<?php
/** @test */
public function last100_telescope_when_correct_request_then_has_expected_query_count(): void
{
// phpunit.xml: change => <env name="TELESCOPE_ENABLED" value="true"/>
$this->seed(DatabaseSeeder::class);
/** @var EntriesRepository $storage */
$storage = resolve(EntriesRepository::class);
// act
$this->getJson(route('api.last100'));
// assert
$entries = $storage->get(
EntryType::QUERY,
(new EntryQueryOptions())->limit(100)
);
// finds all queries executed in SampleResource file
$queryCount = $entries->filter(fn($e) => str_contains($e->content['file'], 'SampleResource'))
->count();
$this->assertSame(0, $queryCount);
}
Мы ожидали, что наш файл SampleResource не будет выполнять никаких запросов. При запуске этого теста с нашим сломанным контроллером мы получаем отказ:
**_Не удалось подтвердить, что 100 идентично 0._**
ПРОФЕССИОНАЛЬНЫЙ СОВЕТ:
Используйте TELESCOPE_ENABLED=false в файле phpunit.xml или .env.testing, чтобы тесты не требовали дополнительных затрат на отладку (когда это не требуется).
НО вам нужно установить значение true, чтобы мой тестовый пример работал.
Дайте мне знать, если у вас есть решение этой проблемы. Я хочу, чтобы TELESCOPE_ENABLED=false включался на разовой основе во время тестового примера.
Фасад БД — журнал запросов
Используя фасад БД, мы можем включить журнал запросов на разовой основе и получить подсказку о том, что происходит во время нашего запроса.
Тестовый пример фасада БД:
<?php
/** @test */
public function last100_dbquerylog_when_correct_request_then_has_expected_query_count(): void
{
$this->seed(DatabaseSeeder::class);
// act
DB::enableQueryLog();
$this->getJson(route('api.last100'));
DB::disableQueryLog();
// assert
$queryLog = DB::getQueryLog();
$queryCount = collect($queryLog)->filter(
fn($log) => str_contains($log['query'], 'select * from "devices" where "devices"."id"')
)->count();
// we expected only 1 query for all devices
$this->assertSame(1, $queryCount);
}
Мы ожидаем только 1 запрос, который извлекает все устройства, но мы получаем 100:
**_Не удалось подтвердить, что 100 идентично 1._**
Пример репозитория для этой статьи можно найти здесь.
Бонус: тестовое покрытие для количества запросов
Просто идея: если вы хотите быть уверены, что ваш API не создаст проблем N+1 во время рефакторинга и/или нового выпуска, проведите их тестирование с помощью аналогичных случаев, которые сравнивают ожидаемое количество запросов с фактическим количеством запросов. .
Также опубликовано здесь
Оригинал