SQL-performance bewaken met automatische detectie en regressietests in Laravel

Trage queries vinden en oplossen is één ding, maar hoe voorkom je dat ze steeds opnieuw de codebase insluipen? In dit artikel laten we zien hoe je SQL-performance structureel bewaakt met geautomatiseerde tests in Laravel.

De uitdaging: performance regressies

Als Laravel-developer herken je het waarschijnlijk wel. Je hebt tijd gestoken in het optimaliseren van je queries, indexes toegevoegd, N+1-problemen opgelost, en je applicatie draait als een zonnetje. Totdat drie sprints later iemand een nieuwe feature bouwt en ongemerkt dezelfde problemen weer introduceert.

Bij Oh Dear hebben we dit patroon zelf ervaren. In deel 1 van deze serie beschreven we hoe je de juiste queries identificeert om te optimaliseren, met tools als de Laravel Debug Bar, MySQL's slow query log en pt-query-digest. In deel 2 doken we diep in EXPLAIN-output, composite indexes en de impact van kolomvolgorde op query-performance.

Het resultaat? Response times van 5+ seconden naar onder de 50 milliseconden. Queries van 32ms naar 3ms. Maar de vraag bleef: hoe houd je dat vast?

De oplossing: query assertions in je testsuite

Om performance regressies structureel te voorkomen, hebben we een Laravel-package gebouwd: phpunit-query-count-assertions. Het idee is simpel: als je al feature tests schrijft voor je applicatie, kun je in diezelfde tests ook asserties doen over de queries die worden uitgevoerd.

De package vereist PHP 8.2+ en Laravel 11 of 12. Installeren doe je via Composer:

composer require mattiasgeniar/phpunit-query-count-assertions --dev

Vervolgens voeg je de trait toe aan je testklasse:

use Mattiasgeniar\PhpunitQueryCountAssertions\AssertsQueryCounts;

class CertificateHealthCheckTest extends TestCase
{
    use AssertsQueryCounts;
}

Vanaf dat moment heb je toegang tot een reeks assertions die je SQL-gedrag valideren.

De belangrijkste assertion: assertQueriesAreEfficient()

De krachtigste methode is assertQueriesAreEfficient(). Deze combineert drie controles in één aanroep:

public function test_checking_certificate_health(): void
{
    $monitor = Monitor::factory()->create();

    $this->assertQueriesAreEfficient(function () use ($monitor) {
        $monitor->checkCertificateHealth();
    });
}

Achter de schermen controleert deze assertion:

1. N+1 detectie: worden dezelfde queries herhaaldelijk uitgevoerd met verschillende parameters? Dan heb je waarschijnlijk een with() vergeten.
2. Duplicate queries: worden identieke queries meerdere keren gedraaid? Dit duidt vaak op redundante refresh()-calls of dezelfde data die vanuit verschillende service classes wordt opgehaald.
3. Index-gebruik: via EXPLAIN wordt gecontroleerd of je queries daadwerkelijk indexes gebruiken in plaats van full table scans.

Je kunt deze controles ook los aanroepen als je specifieker wilt testen.

Losse assertions voor gerichte controles

N+1 en lazy loading detectie

$this->assertNoLazyLoading(function () {
    $monitors = Monitor::all();
    // Als hier $monitor->team wordt aangeroepen zonder
    // eager loading, faalt de test
    $monitors->each->checkUptime();
});

Duplicate queries opsporen

$this->assertNoDuplicateQueries(function () {
    $processor = new CronCheckProcessor($monitor);
    $processor->process();
});

Index-gebruik valideren

$this->assertAllQueriesUseIndexes(function () {
    Monitor::where('team_id', $team->id)
        ->where('is_enabled', true)
        ->get();
});

Query-aantallen bewaken

Soms wil je simpelweg het aantal queries begrenzen om te voorkomen dat het ongemerkt groeit:

$this->assertQueryCountLessThan(10, function () {
    $controller->index($request);
});

$this->assertQueryCountBetween(5, 15, function () {
    $monitor->runAllChecks();
});

$this->assertQueryCountMatches(3, function () {
    $monitor->checkUptime();
});

Wat gebeurt er onder de motorkap?

EXPLAIN-analyse

Bij assertAllQueriesUseIndexes() en assertQueriesAreEfficient() draait de package voor iedere SELECT-query automatisch een EXPLAIN-statement. De output wordt geanalyseerd op basis van een severity-model:

Een voorbeeld van de output wanneer een test faalt:

[ERROR] Query uses full table scan (type=ALL) with no index:
SELECT * FROM monitors WHERE team_id = 1 AND is_enabled = 1
  possible_keys: NULL
  key: NULL
  rows: 145832
  Extra: Using where

Dit is precies de informatie die je nodig hebt om het probleem op te lossen. In dit geval: een composite index op (team_id, is_enabled).

Kleine tabellen worden genegeerd

Een belangrijk detail: de package negeert standaard tabellen met minder dan 10 rijen. In testsituaties werk je vaak met minimale datasets, waardoor MySQL terecht een full table scan verkiest boven het raadplegen van een index. Dit is normaal gedrag en geen performance-probleem. De drempel is configureerbaar als je dat nodig hebt.

Multi-connection support

Werk je met meerdere database-connecties? De package monitort standaard alle database-connecties. Je kunt ook filteren op een specifieke connectie als dat relevanter is voor je test.

Wat we hiermee bij Oh Dear gevonden hebben

Na het activeren van deze assertions in onze eigen testsuite kwamen er direct een paar verborgen problemen boven water.

Dubbele refresh()-calls in de cron-checker. Een stuk legacy-code riep $model->refresh() aan terwijl dezelfde data eerder in de flow al was opgehaald. Dit was tijdens code review niet opgevallen omdat de calls in verschillende service classes zaten.

Lazy-loaded team-relaties bij certificaat-notificaties. Een N+1 patroon dat alleen optrad wanneer meerdere certificaten tegelijk verliepen. In de praktijk kwam dit zelden voor, waardoor het nooit was opgemerkt in monitoring. Maar wanneer het wel optrad, genereerde het tientallen extra queries.

Het netto resultaat: meer dan 15% reductie in SQL read queries, puur door issues die code review hadden overleefd.

Organisatiebrede uitrol

Wil je de package breed inzetten binnen je team? Dan is het slim om niet meteen alle bestaande tests te laten falen. Gebruik in plaats daarvan de test lifecycle hooks om de controles geleidelijk te activeren:

// In een base TestCase of via setUp()
protected function setUp(): void
{
    parent::setUp();

    // Activeer voor nieuwe tests, niet retroactief
    $this->trackQueries();
}

Begin met assertQueryCountLessThan() op een ruime limiet, en verscherp die naarmate je codebase opschoont. Voeg daarna assertNoDuplicateQueries() toe, en werk toe naar de volledige assertQueriesAreEfficient().

Beperkingen

Eerlijkheid gebiedt te zeggen: deze aanpak vangt niet alles. Een paar kanttekeningen:

• Kleine datasets in tests: je test-database bevat zelden miljoenen rijen. Een query die in je test prima werkt, kan in productie alsnog traag zijn.
• EXPLAIN is een schatting: het query-plan dat MySQL genereert bij EXPLAIN kan afwijken van het daadwerkelijke uitvoeringsplan, vooral bij complexe joins of subqueries.
• Testdata bepaalt de uitkomst: als je test scenario X niet dekt, worden de bijbehorende queries ook niet gecontroleerd. Volledige dekking vereist goede test coverage.
• Timing is niet getest: de package meet geen query-tijden. Het controleert structurele problemen (missing indexes, duplicates, N+1), niet of een specifieke query 5ms of 500ms duurt.

Onze inschatting is dat deze aanpak zo'n 80% van de performance-problemen onderschept voordat ze productie bereiken. De overige 20% vang je op met monitoring tools, slow query logs en APM-oplossingen.

Van reactief naar proactief

De combinatie van de drie delen in deze serie vormt een compleet verhaal. Met de technieken uit deel 1 vind je welke queries aandacht nodig hebben. Met de kennis uit deel 2 analyseer en fix je ze. En met de geautomatiseerde assertions uit dit artikel voorkom je dat dezelfde problemen terugkeren.

Het verschil zit in de mindset: in plaats van wachten tot je monitoring rood kleurt, verschuif je de detectie naar links in je development cycle. Je CI-pipeline wordt je eerste verdedigingslinie.

Probeer het eens uit in je eigen project. Begin met een paar kritieke feature tests, voeg assertQueriesAreEfficient() toe, en kijk wat er boven water komt. De kans is groot dat je verrast wordt.

De package is open source en beschikbaar op GitHub.