Trage MySQL query oplossen: slow query log en EXPLAIN

Als je een MySQL-database beheert, ken je het gevoel: een query die normaal in milliseconden draait, blijft plotseling seconden hangen. Vaak ligt de oorzaak in een ontbrekende index of een volle tabelscan die je met een paar gerichte stappen kunt opsporen.

Standaard drempelwaarde trage query (long_query_time): 10 seconden ·
Standaardstatus slow query log: Uitgeschakeld ·
Maximum querytijd voor loggen (log_queries_not_using_indexes): Optioneel: queries zonder index ·
Aanbevolen minimale indexdekking: Alle kolommen in WHERE- en JOIN-clausules

Vijf kernfeiten geven een snel inzicht in waar je op moet letten.

Het patroon is duidelijk: een paar eenvoudige instellingen en checks kunnen de grootste problemen verhelpen.

Hoe los je een trage query in MySQL op?

1. Stap 1: Slow query logging inschakelen
   • De slow query log is standaard uitgeschakeld in MySQL (MySQL 9.7 Reference Manual (officiële documentatie)).
   • Gebruik SET GLOBAL slow_query_log = ON; om de log in te schakelen, zonder server herstart (MySQL 9.7 Reference Manual (officiële documentatie)).
   • Stel een geschikte drempel in met SET GLOBAL long_query_time = 1; (1 seconde is een goede start) (OneUptime (MySQL performance handleiding)).
2. Stap 2: Trage queries identificeren
   • Lees de slow query log uit (standaard bestand hostname-slow.log) om de langzaamste statements te vinden (MySQL 8.0 Reference Manual (officiële documentatie)).
   • Gebruik pt-query-digest van Percona om de log te aggregeren en te analyseren (Percona (database performance consultancy)).
   Waarom dit belangrijk is

   Zonder slow query log ben je blind voor de echte boosdoeners. Een query die 10 seconden duurt, weegt veel zwaarder dan duizend snelle queries.

3. Stap 3: Query-analyse met EXPLAIN
   • Voer EXPLAIN uit op de trage query om het uitvoeringsplan te zien: join-volgorde, indexgebruik en rijschattingen (MySQL 8.0 Reference Manual (officiële documentatie)).
   • Gebruik EXPLAIN ANALYZE (MySQL 8.0.18+) om werkelijke timing en iteratie-informatie te krijgen (MySQL 8.4 Reference Manual (officiële documentatie)).
   • Let op de kolom type: ALL betekent een full table scan – een teken dat er een index ontbreekt.

   “EXPLAIN ANALYZE is een profiling tool die laat zien waar tijd wordt besteed en waarom.”

   — MySQL Blog (officiële MySQL-ontwikkelaarsblog)

4. Stap 4: Optimalisatie toepassen
   • Voeg indexen toe op kolommen in WHERE, JOIN en ORDER BY clausules (CREATE INDEX idx_naam ON tabel(kolom);).
   • Herschrijf subquery’s naar JOIN’s waar mogelijk – dat is vaak efficiënter.
   • Beperk het aantal geretourneerde rijen met LIMIT en gebruik paginering.

   Het patroon: een query die een full table scan doet, kun je bijna altijd versnellen met een index.

Hoe versnel je een MySQL-query?

Indexstrategieën

• Indexen versnellen zoekopdrachten aanzienlijk door het aantal te doorzoeken rijen te verkleinen (MySQL 8.0 Reference Manual (officiële documentatie)).
• Gebruik composite indexen (index op meerdere kolommen) voor query’s die meerdere kolommen filteren.
• Controleer met EXPLAIN of de index daadwerkelijk wordt gebruikt (type = ref of range).

Query-herschrijftechnieken

• Vermijd SELECT *; selecteer alleen de benodigde kolommen om de overhead te verminderen.
• Functies op kolommen in WHERE (bijv. WHERE YEAR(datum) = 2023) verhinderen indexgebruik. Gebruik in plaats daarvan een bereik: WHERE datum >= '2023-01-01' AND datum < '2024-01-01'.
• Subquery’s kunnen vaak efficiënter worden geschreven als JOIN’s (DBA Stack Exchange (community Q&A)).

Gebruik van LIMIT en paginering

• Voeg altijd LIMIT toe aan query’s die bedoeld zijn om een subset te tonen. Zonder LIMIT kan de database miljoenen rijen doorzoeken en weggooien.
• Gebruik voor paginering OFFSET met mate; overweeg keyset pagination (WHERE id > laatste_id) voor betere prestaties.

Vermijden van functies in WHERE

• Zoals hierboven: functies op kolommen maken indexen onbruikbaar. Dit is een van de meest voorkomende fouten.

De implicatie: kleine herschrijvingen van een query kunnen het verschil maken tussen seconden en milliseconden.

Waarom zijn MySQL-queries zo traag?

Ontbrekende indexen

• Een query zonder index op de WHERE-conditie leidt tot een full table scan – de database moet elke rij lezen (MySQL 8.0 Reference Manual (officiële documentatie)).
• Dit is de nummer één oorzaak van trage queries in de praktijk.

Grote dataset zonder filtering

• Zonder WHERE of LIMIT worden alle rijen verwerkt. Hoe groter de tabel, hoe langer de query duurt.
• Partitionering van grote tabellen kan helpen (Oracle/MySQL - How to Analyze and Tune MySQL Queries).

Inefficiënte JOIN-operaties

• JOIN’s zonder index op de gekoppelde kolommen dwingen nested-loop scans af die extreem traag kunnen zijn.
• Zorg dat elke JOIN-kolom geïndexeerd is.

Locking en concurrency

• Table locks en row locks kunnen vertraging veroorzaken bij veel gelijktijdige gebruikers. InnoDB gebruikt row-level locking, maar lange transacties kunnen nog steeds blokkades veroorzaken.

Wat dit betekent: vier hoofdoorzaken, maar in de praktijk is het bijna altijd een combinatie van ontbrekende indexen en onnodig grote result sets.

Hoe zorg je dat een MySQL-query sneller loopt?

Optimaliseer SELECT met indexen

• Indexen zijn de krachtigste tool voor leesprestaties. Zorg dat elke veelgebruikte filterconditie een index heeft.
• Gebruik EXPLAIN om te controleren of de index wordt gebruikt.

Gebruik query cache (indien van toepassing)

• Query cache is in MySQL 8.0 verwijderd (MySQL 9.7 Reference Manual (officiële documentatie)). Gebruik in plaats daarvan application-level caching (bijv. Redis, Memcached).

Partitioneer grote tabellen

• Partitionering verdeelt gegevens over kleinere delen, waardoor query’s alleen de relevante partitie hoeven te scannen.

Herschrijf subquery’s naar JOIN’s

• Subquery’s in WHERE kunnen vaak worden omgezet naar JOIN’s, die de optimizer beter kan plannen.

Het patroon: elke optimalisatie begint met het verminderen van het aantal rijen dat de database moet lezen.

Hoe versnel je trage queries?

Analyseer het executionplan

• EXPLAIN en EXPLAIN ANALYZE geven exact aan waar de tijd in gaat zitten (MySQL Blog (officiële MySQL-ontwikkelaarsblog)).
• Kijk naar de kolommen rows (geschatte rijen) en Extra (bijv. “Using temporary”, “Using filesort”).

Voeg composite indexen toe

• Een composite index op meerdere kolommen (bijv. INDEX(land, stad, postcode)) kan query’s met meerdere filters versnellen.
• Let op de volgorde: plaats de meest selectieve kolom vooraan.

Optimaliseer serverconfiguratie

• Verhoog innodb_buffer_pool_size (InnoDB) voor betere caching van data en indexen. Dit is vaak de grootste winst op serverniveau.
• Pas sort_buffer_size en join_buffer_size aan voor specifieke workloads.

Gebruik een monitoringtool

• Percona Monitoring and Management (PMM) biedt dashboards voor query-analyse en servergezondheid (Percona (database performance consultancy)).
• MySQL Workbench heeft een ingebouwde performance dashboard.

Voor de Nederlandse ontwikkelaar is de boodschap: investeer een uur in het opzetten van slow query logging en EXPLAIN-analyse, en je bespaart dagen aan giswerk.

“De slow query log is een nuttig hulpmiddel voor het identificeren van performanceproblemen in MySQL-databases.”

— Percona (database performance consultancy)

“MySQL heeft geen ingebouwde functie om automatisch EXPLAIN-output te loggen voor alle queries in de slow query log.”

— DBA Stack Exchange (community Q&A)

Voor de Nederlandse ontwikkelaar die met MySQL werkt, is de keuze duidelijk: investeer tijd in het configureren van de slow query log en het analyseren van EXPLAIN-output, of blijf worstelen met onverklaarbare prestatieproblemen. De stappen zijn eenvoudig, de tools zijn gratis, en de winst in applicatiesnelheid is direct merkbaar.

Voor wie de Nederlandse uitleg wil aanvullen met een Duitse variant, biedt de Duitse gids voor trage MySQL-querys een vergelijkbare stapsgewijze aanpak.

Veelgestelde vragen