MySQL Index Optimalisatie: Trage Queries Oplossen

Correct gebruikt verandert een MySQL index een query die seconden duurt in eentje van milliseconden; verkeerd gebruikt doet hij niets en kan hij je schrijfacties zelfs vertragen. Vooral op gameservers groeien tabellen voor sessies, logs, inventaris en ranglijsten snel uit tot miljoenen rijen. Op dat moment moet je de bottleneck meten in plaats van gokken. In deze gids lopen we door het diagnosticeren van trage queries met EXPLAIN, het bepalen welke kolommen je moet indexeren en het vermijden van de meest voorkomende fouten.

Waarom indexen werken

In een tabel zonder index doorloopt MySQL de hele tabel van begin tot eind om de gewenste rijen te vinden (een full table scan). Naarmate de tabel groeit, wordt dit lineair trager. Een index is in de meeste gevallen een B-tree-structuur: hij houdt de waarden van de geïndexeerde kolom gesorteerd en laat MySQL met een logica vergelijkbaar met binair zoeken direct naar het relevante blok springen, zelfs over miljoenen rijen.

Dat heeft een prijs: elke INSERT, UPDATE en DELETE moet ook de index bijwerken. Een index versnelt dus het lezen, vertraagt het schrijven een beetje en kost schijfruimte. Daarom is het doel niet "alles indexeren" maar de juiste kolommen indexeren.

De trage query vinden: de slow query log

Eerst moet je weten welke query echt traag is. De slow query log van MySQL registreert elke query die langer duurt dan een drempel die je instelt. Je kunt hem snel per sessie inschakelen:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;        -- queries langer dan 1 seconde
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Nadat het een tijdje heeft gedraaid, vat je de meest voorkomende en traagste queries samen met mysqldumpslow:

mysqldumpslow -s t /var/log/mysql/slow.log | head -20

Het doel is om een concrete query te vinden om te optimaliseren. Werk met echt verkeer, niet met gokken.

Diagnose met EXPLAIN

Zodra je een trage query hebt, gebruik je EXPLAIN om te zien hoe MySQL die uitvoert. Een voorbeeldquery van een gameserver:

EXPLAIN SELECT * FROM player_logs
WHERE account_id = 4815 AND action = 'login'
ORDER BY created_at DESC
LIMIT 20;

Let in de uitvoer vooral op deze kolommen:

• type: de toegangsmethode. ALL is slecht (volledige scan), ref en range zijn goed, const/eq_ref zijn het beste.
• key: de index die MySQL daadwerkelijk gebruikte. NULL betekent dat er helemaal geen index is gebruikt.
• rows: het geschatte aantal rijen dat MySQL verwacht te scannen. Lager is beter.
• Extra: zie je Using filesort of Using temporary, dan wordt er extra werk gedaan voor sorteren of groeperen.

Voor een gedetailleerder plan voert EXPLAIN ANALYZE (MySQL 8.0.18+) de query echt uit en toont de gemeten tijden — je ziet de werkelijke kosten in plaats van een schatting.

De juiste index ontwerpen

De bovenstaande query filtert op account_id en action en sorteert op created_at. De ideale index is een samengestelde (composite) index die precies die volgorde weerspiegelt:

CREATE INDEX idx_logs_acc_action_time
  ON player_logs (account_id, action, created_at);

Het kernbegrip hier is de leftmost prefix-regel. Als een samengestelde index (a, b, c) is, kan MySQL hem gebruiken voor zoekopdrachten op a, a+b of a+b+c, maar niet voor b of c alleen. Daarom is de kolomvolgorde belangrijk:

• Gelijkheidsfilters (=) komen eerst (hier account_id, action).
• Daarna komt de range- en ORDER BY-kolom (created_at).

Dankzij deze volgorde verwerkt MySQL zowel het filteren als het sorteren via de index, en verdwijnt Using filesort.

Veelgemaakte fouten

• Een kolom in een functie verpakken: WHERE DATE(created_at) = '2026-06-27' schrijven verhindert het gebruik van de index. Gebruik in plaats daarvan een range: WHERE created_at >= '2026-06-27' AND created_at < '2026-06-28'.
• Voorloop-wildcard: LIKE '%abc' schakelt de index uit, terwijl LIKE 'abc%' hem wel kan gebruiken.
• Type-mismatch: een numerieke kolom vergelijken met een string zoals WHERE id = '123' kan een impliciete conversie en een volledige scan veroorzaken.
• Over-indexeren: zelden bevraagde of weinig selectieve kolommen (bijvoorbeeld een vlag met alleen 0/1) op zichzelf indexeren helpt meestal niets.
• Onnodige SELECT *: als je alleen de kolommen selecteert die je nodig hebt, voldoet soms de index zelf aan de hele query (een covering index) en wordt de tabel nooit aangeraakt.

Controleren en onderhouden

Voer na het toevoegen van een index dezelfde EXPLAIN opnieuw uit: type moet van ALL naar ref/range gaan, rows moet flink dalen en filesort moet verdwijnen. Voer af en toe ANALYZE TABLE player_logs; uit om de statistieken actueel te houden. Ongebruikte indexen kun je opsporen en verwijderen via de view sys.schema_unused_indexes om de schrijflast te verlagen.

Onthoud: indexoptimalisatie is geen eenmalige klus maar een doorlopende cyclus — meten, indexeren, controleren, herhalen.

Veelgestelde vragen

Hoeveel indexen kan ik aan een tabel toevoegen?

Hoewel de technische limiet hoog is, hangt de praktische limiet af van je werklast. Houd op tabellen met veel schrijfacties weinig en gerichte indexen; op leesintensieve rapportagetabellen kunnen meer indexen redelijk zijn. Onthoud dat elke index een schrijf- en opslagkost heeft.

Zijn aparte indexen of een samengestelde index beter?

Als een query meerdere kolommen samen filtert, is één correct geordende samengestelde index bijna altijd beter dan losse indexen op één kolom. In de meeste gevallen gebruikt MySQL per query één index zo efficiënt mogelijk.

Is de primary key al een index?

Ja. In InnoDB is de primary key tevens de clustered index, en de rijgegevens worden fysiek in die volgorde opgeslagen. Daarom is het kiezen van een korte, sequentiële en onveranderlijke primary key (zoals een auto-increment) belangrijk voor de prestaties.

Stop met gokken wanneer je database trager wordt. Gameserver of webapplicatie, het maakt niet uit — laten we samen je trage queries meten en de juiste indexstrategie opbouwen. Neem contact op en laten we je database versnellen.