SQL: Geclusterde en niet-geclusterde index
Het kan soms voorkomen dat het veel tijd kost voordat een bepaalde query resultaten teruggeeft. Dit kan liggen aan een scala aan problemen, zoals het gebruik van veel joins. Wat de query kan helpen versnellen, is het aanmaken van indexen op de tabel. Maar wat zijn dit nu precies?
Index
Laten we eerst kijken naar hoe SQL werkt zonder het gebruik van een index. Als een tabel geen index heeft, begint SQL met de eerste rij en vanuit daar loopt SQL de gehele tabel door totdat het de opgevraagde informatie gevonden heeft. Als je gebruik maakt van een grote tabel met veel rijen, dan kun je je voorstellen dat dit proces veel tijd kost.
Als een tabel een index heeft, dan kan SQL veel sneller de opgevraagde informatie terugvinden. Een index bevat namelijk informatie over de structuur van de tabel. Een index bestaat uit één of meerdere keys met informatie over één of meerdere kolommen uit de tabel. Deze informatie wordt opgeslagen in de vorm van een zogenaamde B-tree (Lees hier meer over B-trees: https://use-the-index-luke.com/sql/anatomy/the-tree). Op basis van de informatie uit de B-tree kan SQL snel een rij of rijen vinden met informatie die geassocieerd zijn met een query. Dit gebeurd alleen als een tabel een geclusterde (clustured) index heeft, anders wordt informatie over de tabel ongesorteerd opgeslagen, een heap genaamd.
Je kunt het gebruik van een index vergelijken met het gebruik van telefoonboek. Zonder index zou je het hele telefoonboek moeten doorzoeken totdat je het telefoonnummer hebt gevonden van de persoon waar je naar opzoek was (full table search). Met gebruik van de index kun je gemakkelijk de op achternaam opzoeken waar het telefoonnummer van de persoon te vinden is. Een key in dit voorbeeld zijn bijv. achternaam, voornaam.
Er zijn twee soorten indexes:
- Geclusterde (Clustered) index
- Niet-geclusterde (non-clustered) index
Bij een geclusterde index wordt de data op volgorde opgeslagen, denk aan bijv. de alfabetische volgorde van een telefoonboek. Er kan maar één kolom een geclusterde index krijgen per tabel, aangezien de geclusterde key rekening houdt met volgorde. Als een tabel een geclusterde index heeft wordt dit ook wel een geclusterde (clustured) tabel genoemd.
Een niet-geclusterde index houdt geen rekening met de exacte volgorde van de kolommen en wordt opgeslagen los van de tabel. Hier wijst de key van een index naar de corresponderende waarde in de tabel. Dit wordt ook wel een row locator genoemd. Omdat er voor het vinden van de data nog een extra stap nodig is, is de niet-geclusterde index trager dan de geclusterde index. Een niet-geclusterde index kan vergelijken worden met de appendix van een boek. Hier kun je op alfabet zoeken naar het onderwerp waarnaar je opzoek bent met het corresponderende paginanummer. Daarna kun je de pagina opzoeken van de gezochte informatie. Hier zie je de 2 stappen weer terug: 1. Vinden op welke bladzijde de informatie staat die je zoekt en 2. Deze bladzijde opzoeken. Een tabel kan meerdere niet-geclusterde indexen bevatten.
Welke index je het beste kunt gebruiken om je queries te versnellen, is afhankelijk van de casus. Gaat het om data die vaak veranderd wordt en welke vaak gebruikt wordt in bijv. where clausules? Gebruik dan een non-clustured index. Als data vaak veranderd en er vaak update queries worden uitgevoerd, moet bij het gebruik van een geclusterde index de volgorde van de data vaak aangepast worden, wat weer tijd kost.
Gaat het om een query waarbij je bijna alle kolommen van de tabel wilt teruggeven? Gebruik een geclusterde index. Als je hier een niet-geclusterde index zou gebruiken, moet SQL telkens een extra look-up doen voor alle kolommen die je wilt teruggeven.
Meer lezen over geclusterde en niet-geclusterde indexes:
