Database-index: wat het is en wanneer je het gebruikt

Een database-index is een extra datastructuur die de rijen van een tabel gesorteerd houdt op een bepaalde kolom of groep kolommen — net als de index achter in een boek laat hij je direct naar de gezochte waarde springen in plaats van elke pagina door te bladeren. Naarmate je applicatie groeit, is het antwoord op "waarom werd deze query traag?" heel vaak een ontbrekende of slecht ontworpen index. In dit artikel leg ik uit hoe indexen queries versnellen, wat ze kosten en wanneer je ze echt moet gebruiken, met concrete voorbeelden.

Zonder index: de volledige tabelscan

Wanneer je zoekt op een kolom zonder index, is de database gedwongen om een volledige tabelscan (full table scan) uit te voeren: hij leest elke rij van begin tot eind en houdt de rijen die voldoen. Bij een tabel van 1.000 rijen merk je dit nooit, maar bij 5 miljoen rijen betekent elke query dat er miljoenen rijen van schijf worden gelezen.

SELECT * FROM users WHERE email = 'aslain@example.com';

Als er geen index op email staat, loopt de engine de hele tabel door om slechts één rij te vinden. De complexiteit is ruwweg O(n): de tijd groeit lineair met het aantal rijen. Met een index daalt de opzoeking naar O(log n) — een enorm verschil bij miljoenen rijen.

Hoe een index echt werkt: de B-tree

De klassieke index in relationele databases is een B-tree (preciezer een B+tree). Waarden worden in gesorteerde volgorde in takken geplaatst; elke opzoeking daalt af van de wortel naar de bladeren en elimineert bij elke stap meer dan de helft van de resterende kandidaten. Daarom bereik je je doelwaarde in slechts een handvol stappen, zelfs tussen miljoenen records.

• Gelijkheidsopzoekingen (=) en bereikopzoekingen (<, >, BETWEEN) zijn snel op een B-tree.
• Sorteren (ORDER BY) en groeperen kunnen bijna gratis zijn omdat de index al gesorteerd is.
• Prefix-zoekopdrachten zoals LIKE 'aslain%' profiteren van de index, maar LIKE '%aslain' (jokerteken vooraan) niet.

Voor zoeken binnen tekst (full-text) of voor geografische queries worden andere indextypes gebruikt dan een gewone B-tree — bijvoorbeeld FULLTEXT-indexen voor tekst.

Een index aanmaken en het effect meten

Een index op één kolom aanmaken is eenvoudig:

CREATE INDEX idx_users_email ON users (email);

Gok niet of hij echt werkt — meet het. In MySQL en PostgreSQL toont het commando EXPLAIN het queryplan:

EXPLAIN SELECT * FROM users WHERE email = 'aslain@example.com';

In de MySQL-uitvoer betekent een type van ALL een volledige scan (slecht); ref of const betekent dat een index wordt gebruikt (goed). De waarde rows schat hoeveel rijen de engine verwacht te scannen; met de juiste index daalt dit getal dramatisch.

Samengestelde indexen en de "meest linkse" regel

Wanneer je meerdere kolommen samen bevraagt, gebruik je een samengestelde (composite) index. De kolomvolgorde is cruciaal, want de B-tree sorteert de waarden in de volgorde die jij opgeeft:

CREATE INDEX idx_orders_user_status
  ON orders (user_id, status);

Deze index versnelt WHERE user_id = 5 en WHERE user_id = 5 AND status = 'paid'. Maar hij versnelt WHERE status = 'paid' op zichzelf niet — dit is de regel van het meest linkse prefix (leftmost prefix). Denk aan een telefoonboek gesorteerd op achternaam en dan voornaam: alleen de voornaam kennen laat geen snelle zoekopdracht toe.

Tip: zet de meest selectieve kolom die je met gelijkheid bevraagt links, en plaats bereikvoorwaarden rechts.

De kosten: indexen zijn niet gratis

Een index is geen toverknop die elk probleem oplost; hij heeft reële kosten:

• Schrijfbewerkingen worden trager: elke INSERT, UPDATE en DELETE moet ook de betrokken indexen bijwerken. Heeft een tabel 10 indexen, dan betekent elke invoeging 10 extra structuren om te onderhouden.
• Ze verbruiken schijf en geheugen: indexen nemen aparte ruimte in; veel onnodige indexen blazen de database op en vervuilen de cache.
• Ze helpen niet bij lage selectiviteit: op een kolom met slechts twee waarden (bijv. is_active) levert een index meestal geen voordeel op; de engine kiest toch voor een volledige scan.

De regel is dus eenvoudig: als er veel gelezen wordt en de voorwaarde selectief is, indexeer; voeg niet blindelings een index toe aan elke kolom.

Praktische aanbevelingen

• Primaire sleutels (PRIMARY KEY) en unieke beperkingen zijn al geïndexeerd; herindexeer ze niet.
• Indexeer de foreign-key-kolommen die vaak in JOIN- en WHERE-voorwaarden worden gebruikt.
• Beschouw kolommen waarop je constant sorteert met ORDER BY als indexkandidaten.
• Zet de slow query log aan om trage queries op te sporen, en verifieer daarna met EXPLAIN.
• Spoor ongebruikte indexen op en verwijder ze; onderhoud is ook deel van optimalisatie.

Veelgestelde vragen

Is het een goed idee om aan elke kolom een index toe te voegen?

Nee. Onnodige indexen vertragen schrijfbewerkingen, verbruiken schijfruimte en kunnen de queryplanner in de war brengen. Indexeer alleen de kolommen die daadwerkelijk worden bevraagd en een hoge selectiviteit hebben, en bevestig je beslissing met EXPLAIN.

Waarom versnelde de index mijn query niet?

Veelvoorkomende oorzaken: je hebt de kolom in een functie gestopt (WHERE YEAR(created_at) = 2026 schakelt de index uit), je hebt de meest-linkse-regel op een samengestelde index geschonden, of de selectiviteit van de kolom is te laag. De uitvoer van EXPLAIN onthult meestal de reden.

Zijn een primaire sleutel en een index hetzelfde?

Een primaire sleutel is een speciale index: hij is zowel uniek als niet-nullbaar (geen NULL), en in de meeste engines bepaalt hij de fysieke ordening van de tabel (de clustered index). Dus elke primaire sleutel is een index, maar niet elke index is een primaire sleutel.

Wordt je database traag en kun je niet achterhalen waarom? Ik kan je queries analyseren, de juiste indexstrategie ontwerpen en slecht presterende queries herschrijven. Neem contact op voor hulp.