Wat zijn SQL-indexen? Waarom ze belangrijk zijn en hoe ze werken

Indexen zijn een van de belangrijkste hulpmiddelen om de prestaties van databases te verbeteren.

In dit artikel leer je:

• Wat SQL-indexen zijn
• Waarom ze query's drastisch versnellen
• Hoe je ze maakt
• Een voorbeeld dat prestatieverschillen laat zien
• Hoe ze intern werken

Laten we indexen begrijpelijk maken.

Wat is een index?

Een index in SQL is een speciale datastructuur (meestal een B-tree) waarmee de database rijen veel sneller kan vinden zonder de hele tabel te scannen.

Je kunt een index zien als:

• Een gesorteerde lijst met waarden
• Elke waarde verwijst naar een rij in de hoofdtabel
• Zo georganiseerd dat de database snel kan zoeken (logaritmische tijd)

Zonder index → de database moet elke rij controleren.
Met index → de database springt rechtstreeks naar de overeenkomende waarden.

Waarom SQL-indexen zijn gemaakt

Indexen bestaan om één probleem op te lossen:

Query's versnellen die naar specifieke waarden zoeken.

Ze zijn gemaakt omdat volledige tabelscans te traag worden naarmate datasets groeien.
In een tabel met 10 rijen gaat het scannen van alle rijen snel.
In een tabel met 10 miljoen rijen wordt dit bij herhaling onwerkbaar.

Indexen maken het volgende mogelijk:

• Snel opzoeken
• Snel sorteren (ORDER BY)
• Snelle joins
• Snelle uniciteitscontroles (via een UNIQUE-index)

Databases zoals PostgreSQL, MySQL en SQLite vertrouwen er allemaal op.

Voorbeeld met JOINs

Indexen versnellen JOINs ook aanzienlijk.

Laten we de tabellen users en orders gebruiken:

SELECT users.name, orders.product
FROM orders
JOIN users ON orders.user_id = users.id
WHERE users.email = 'bob@example.com';

Aanbevolen indexen

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);

Zonder indexen: volledige tabelscans aan beide kanten

Wanneer er geen indexen op users.email en orders.user_id bestaan, moet de database:

• Elke rij scannen in users om rijen te vinden die aan de e-mailvoorwaarde voldoen
• Elke rij scannen in orders om rijen te vinden waarvan user_id overeenkomt met die gebruikers-ID's

Dit wordt een sequentiële scan of volledige tabelscan genoemd.

Voorbeeld van de omvang:

• Tabel users: 100.000 rijen
• Tabel orders: 1.000.000 rijen

De database moet het volgende onderzoeken:

• 100.000 gebruikersrijen
• 1.000.000 bestelrijen

Hoewel het eindresultaat misschien maar één rij is, zijn de zoekkosten erg hoog.

Benaderde prestaties (verschillen per machine):

• 100.000 gebruikers + 1 miljoen bestellingen controleren
• Zonder indexen: ongeveer 300 tot 600 ms
• Met indexen: ongeveer 1 tot 3 ms

Dat is een verbetering van 100× tot 500×.

Met indexen: snel en rechtstreeks opzoeken

Met de twee indexen:

• idx_users_email → maakt het direct opzoeken van de gebruiker met dat e-mailadres mogelijk
• idx_orders_user_id → maakt het direct opzoeken van alle bestellingen van die gebruiker mogelijk

In plaats van miljoenen rijen te scannen:

• Springt de database rechtstreeks naar de juiste vermeldingen in de index (opzoeken in een B-tree)
• Worden alleen de relevante rijen benaderd
• Wordt de JOIN extreem snel

Wat beïnvloedt de prestaties?

De exacte tijden verschillen per hardware, maar deze factoren op machineniveau zijn het belangrijkst:

Indexen zijn essentieel in applicaties omdat zelfs krachtige machines moeite hebben met herhaalde volledige tabelscans wanneer de hoeveelheid gegevens groeit.

• Zonder indexen: de database moet elke rij in beide tabellen lezen
• Met indexen: de database zoekt snel met efficiënte datastructuren
• Hierdoor kunnen JOINs opschalen naar miljoenen rijen met responstijden op millisecondenniveau

Hoe indexen intern werken (eenvoudige uitleg)

De meeste SQL-databases gebruiken **B-trees **, die:

• Waarden gesorteerd houden
• Zoeken mogelijk maken dat op binair zoeken lijkt
• Een zeer beperkte diepte hebben (meestal 3 tot 5 niveaus, zelfs bij enorme tabellen)

Dit betekent:

• Een rij vinden vereist maar een paar vergelijkingen
• Toegang is voorspelbaar en snel
• Indexen groeien logaritmisch in plaats van lineair

Je hoeft het algoritme niet diepgaand te begrijpen, maar dit is het belangrijkste idee:

Zoeken in een gesorteerde boom is veel sneller dan zoeken in een miljoen ongesorteerde rijen.

Wanneer moet je een index gebruiken?

Gebruik een index wanneer je:

• Vaak met SELECT op een kolom zoekt
• Filtert met WHERE
• Sorteert met ORDER BY
• Tabellen met JOIN koppelt via refererende sleutels
• Uniciteit afdwingt (UNIQUE INDEX)
• Zoekt met gelijkheids- of bereikquery's

Indexen zijn het waardevolst op:

• email (e-mailadressen)
• username (gebruikersnamen)
• created_at (aanmaaktijdstippen)
• refererende sleutels (user_id, post_id)
• slugs
• opzoekkolommen

Wanneer je een index niet moet gebruiken

Indexen brengen kosten met zich mee:

• Ze nemen schijfruimte in beslag
• Ze vertragen schrijfbewerkingen (INSERT/UPDATE/DELETE moeten de index bijwerken)

Vermijd indexering van:

• booleaanse kolommen
• kolommen met een zeer lage cardinaliteit (bijvoorbeeld geslacht, status = “actief”)
• kolommen die zelden in query's worden gebruikt
• zeer grote tekstkolommen