Een trage SQL-query vraagt niet automatisch om een extra index. Leer hoe indexstructuren, queryplannen en de kenmerken van je workload samen bepalen waar de database tijd verliest en welke aanpassing zinvol is.
Je leest hoe je knelpunten herkent, indexkeuzes afweegt en onnodige bewerkingen onderscheidt van werk dat bij de query hoort.
Analyseer eerst de query en de workload
Een index beoordelen begint bij de vraag welke query langzaam is en onder welke omstandigheden dat gebeurt. Verzamel de SQL, de gebruikte parameters, de responstijd en het aantal keren dat de query wordt uitgevoerd. Een query die incidenteel enkele seconden duurt, stelt andere eisen dan een query die duizenden keren per minuut wordt aangeroepen. Ook de verhouding tussen lezen en schrijven is bepalend: een index die rapportages versnelt, kan transacties vertragen wanneer dezelfde tabel voortdurend wordt bijgewerkt.
Vergelijk waar mogelijk meerdere uitvoeringen. Een verschil tussen een warme en koude cache kan verklaren waarom een queryplan op de ene machine goed lijkt en op een andere niet. Noteer ook de omvang en groei van de betrokken tabellen, want een aanpak die werkt op een kleine dataset kan bij miljoenen rijen omslaan. Kijk niet alleen naar de langzaamste query, maar ook naar het totale aandeel in databasebelasting. Een relatief kleine vertraging die vaak voorkomt, kan meer capaciteit verbruiken dan een incidentele zware query.
Deze inventarisatie voorkomt dat een index wordt toegevoegd op basis van een losse indruk. Ze geeft bovendien context voor de interpretatie van het queryplan: de database optimaliseert voor de huidige statistieken en parameters, niet voor een abstracte SQL-instructie.
Hoe een database-index zoekwerk vermindert
Een relationele database bewaart tabelrijen doorgaans in een andere structuur dan de index die een zoekopdracht ondersteunt. Een veelgebruikte index is een B-tree: een gebalanceerde boom die waarden geordend houdt en zo gericht zoeken mogelijk maakt. Zonder bruikbare index kan de database een groot deel van de tabel doorlopen om rijen te vinden die aan een filter voldoen. Met een passende index kan zij naar een bereik van sleutelwaarden navigeren en alleen relevante delen lezen.
Een index bevat sleutels en verwijzingen naar tabelrijen, of in sommige databases de volledige rij wanneer de index geclusterd is. Dat verschil beïnvloedt hoeveel extra leeswerk nodig is. Als de gevraagde kolommen al in de index staan, kan een index-only scan mogelijk zijn. Anders haalt de database aanvullende gegevens op uit de tabel. Bij weinig gevonden rijen is dat vaak efficiënt; bij veel verspreide rijen kan het juist duur worden.
Indexen versnellen dus niet iedere query die een geïndexeerde kolom noemt. De database moet de index kunnen gebruiken voor de filter, sortering of join én moet inschatten dat dit goedkoper is dan alternatieven. Dat hangt af van de verdeling van waarden, de hoeveelheid geselecteerde data en de kosten van extra tabeltoegang. Een index is een hulpmiddel voor een specifiek patroon, geen algemene snelheidsinstelling.
Kies samengestelde indexen op basis van kolomvolgorde
Een samengestelde index bevat meerdere kolommen in een vaste volgorde. Die volgorde bepaalt voor welke filters en sorteringen de index direct bruikbaar is. Bij een index op klant_id en status kan de database doorgaans zoeken op klant_id, of op klant_id in combinatie met status. Alleen op status zoeken is vaak minder effectief, omdat de index eerst is geordend op klant_id. De precieze mogelijkheden verschillen per databasesysteem, maar het voorvoegsel van de index speelt meestal een belangrijke rol.
Begin bij de querypatronen die daadwerkelijk voorkomen. Een kolom waarop vrijwel altijd een gelijkheidsfilter staat, kan een geschikte eerste sleutel zijn; daarna kunnen kolommen volgen die verdere selectie of ordening ondersteunen. Dit is geen universele regel: een kolom met weinig verschillende waarden kan soms toch nuttig zijn in een samengestelde index, afhankelijk van de combinatie en de verdeling van data. Controleer daarom de echte queryplannen en niet alleen de kolomnamen.
Een brede index kan meerdere queryvarianten ondersteunen, maar neemt meer opslagruimte in en maakt wijzigingen aan de sleutelkolommen zwaarder. Twee smalle indexen zijn niet automatisch beter: de optimizer kan ze combineren, maar die combinatie kan extra werk kosten. Kies de indexvorm op basis van representatieve filters, joins en sorteringen, en let op query’s die dezelfde tabel op een andere manier benaderen.
Lees een queryplan van geschatte naar werkelijke kosten
Een queryplan laat zien hoe de database een SQL-instructie wil uitvoeren of heeft uitgevoerd. In een geschat plan baseert de optimizer zich op statistieken en aannames; een werkelijk uitvoeringsplan bevat daarnaast gegevens over wat er tijdens de uitvoering gebeurde. Let op de volgorde van bewerkingen, het aantal verwachte en werkelijk verwerkte rijen, en de hoeveelheid gelezen data. Een groot verschil tussen geschatte en werkelijke aantallen kan erop wijzen dat statistieken verouderd zijn of dat de verdeling van waarden niet goed wordt beschreven.
De visuele weergave verschilt per databaseplatform. Sommige plannen tonen bewerkingen van onder naar boven, andere presenteren de gegevens in een boomstructuur. Leer eerst hoe jouw platform de richting en kosten weergeeft. De procentuele kosten in een plan zijn schattingen binnen die ene uitvoering en geen directe meting van verstreken tijd. Een operator met een hoog percentage is daarom een aanwijzing, geen zelfstandig bewijs van het probleem.
Vergelijk bij een trage query het plan met een snelle uitvoering en controleer of parameters, datavolume en cachecondities overeenkomen. Zoek naar grote verschillen in rijaantallen, herhaalde bewerkingen en onnodige sorteringen. Bekijk ook logische en fysieke leesacties en CPU-tijd wanneer die beschikbaar zijn. Een plan krijgt pas betekenis in combinatie met meetgegevens en kennis van de gebruikte dataset.
Herken wanneer een table scan werkelijk een probleem is
Een table scan leest een groot deel of de volledige tabel. Dat klinkt inefficiënt, maar kan de goedkoopste keuze zijn wanneer de query een groot percentage van de rijen nodig heeft. Een index gebruiken om vervolgens voor bijna iedere gevonden sleutel de tabel te raadplegen, kan meer willekeurige leesacties veroorzaken dan een aaneengesloten scan. Bij kleine tabellen kan een scan bovendien goedkoper zijn dan navigeren door een index. Het enkele voorkomen van een scan in het plan is dus geen reden om een index toe te voegen.
Onderzoek hoeveel rijen de query teruggeeft ten opzichte van de tabelomvang en hoeveel data werkelijk is gelezen. Als een query slechts enkele rijen nodig heeft maar een grote tabel scant, controleer dan of de filterkolommen geïndexeerd zijn en of de filter sargable is. Een functie rond de kolom, een impliciete datatypeconversie of een patroon dat geen bruikbaar bereik oplevert, kan gericht zoeken verhinderen. De precieze invloed is afhankelijk van database en optimizer.
Een scan kan ook ontstaan doordat statistieken de selectiviteit verkeerd inschatten, of omdat de query veel kolommen opvraagt en een index geen voordeel biedt. Pas de query of index pas aan nadat duidelijk is welke oorzaak speelt. Een index toevoegen aan een tabel die voornamelijk sequentieel wordt gelezen, verhoogt mogelijk het onderhoud zonder de dominante leesbewerking te verbeteren.
Beoordeel key lookups, sorteringen en joins
Een key lookup ontstaat wanneer de database via een niet-geclusterde index geschikte sleutels vindt en daarna voor aanvullende kolommen teruggaat naar de tabel. Bij een handvol resultaten is dat vaak efficiënt. Bij duizenden resultaten kan dezelfde terughaalbewerking zich herhalen en een groot deel van de querytijd bepalen. Controleer daarom het werkelijke aantal uitgevoerde lookups en de rijen die de query uiteindelijk nodig heeft. Een covering index, met de vereiste kolommen als sleutel of als opgenomen kolommen, kan dit werk verminderen, maar maakt de index groter.
Een sorteerbewerking kan wijzen op een ontbrekende index die de gewenste volgorde ondersteunt, maar ook op een query die grote hoeveelheden data moet ordenen. Kijk of filters eerst het aantal rijen kunnen beperken en of een samengestelde index zowel filter als sortering kan ondersteunen. Bij aggregaties kan een hash- of sorteerbewerking normaal zijn; het plan moet aantonen of die bewerking buitensporig veel geheugen of tijdelijke opslag gebruikt.
Bij joins is de gekozen methode afhankelijk van omvang en ordening van de invoer. Nested loops passen vaak bij een kleine set met snelle indexzoekacties, terwijl hash joins juist geschikt kunnen zijn voor grotere sets. Een afwijkende raming van rijaantallen kan leiden tot een ongunstige joinkeuze. Onderzoek daarom de invoer van elke join en niet alleen de operatornaam. Extra indexen lossen een foutieve kardinaliteitsschatting niet vanzelf op.
Weeg indexwinst af tegen extra schrijfwerk
Elke index moet worden bijgewerkt wanneer een transactie een relevante rij toevoegt, verwijdert of wijzigt. Een insert schrijft daardoor niet alleen naar de tabel, maar ook naar de indexstructuren. Een update van een geïndexeerde kolom kan een indexsleutel verplaatsen; afhankelijk van de databasestructuur kan ook een update van andere kolommen extra werk veroorzaken. Meer indexen betekenen daarnaast meer opslaggebruik, grotere back-ups en mogelijk meer cacheverbruik. Dat kan de prestaties van zowel schrijf- als leesverkeer beïnvloeden.
De afweging is workloadspecifiek. Een index die een essentiële zoekopdracht sterk verkort, kan de extra schrijfbewerkingen rechtvaardigen. Een index die zelden wordt gebruikt maar veel sleutels bevat, verdient nader onderzoek. Gebruik gebruiksstatistieken over een representatieve periode en houd rekening met onderhoudsvensters, herstarts en zeldzame rapportageprocessen: statistieken kunnen worden gereset of een querypatroon kan slechts periodiek optreden.
Voeg niet meerdere bijna identieke indexen toe zonder te controleren of een bestaande samengestelde index het patroon al ondersteunt. Overlap kan leiden tot dubbel onderhoud en onduidelijkheid bij schemawijzigingen. Bij verwijderen is voorzichtigheid nodig: controleer afhankelijkheden, gebruiksgegevens en de invloed op queryplannen. Een index die op het eerste gezicht overbodig lijkt, kan een specifieke kritieke query ondersteunen die buiten de meetperiode valt.
Controleer statistieken en parametergevoeligheid
De optimizer gebruikt statistieken om te schatten hoeveel rijen een filter oplevert. Wanneer die schatting ver afwijkt van de werkelijkheid, kan het gekozen plan ongeschikt zijn. De database kan bijvoorbeeld een index verwachten die weinig rijen oplevert, terwijl een veel groter deel van de tabel voldoet. Of ze kiest een scan terwijl de query in werkelijkheid zeer selectief is. Controleer daarom de geschatte en werkelijke aantallen op meerdere punten in het plan, niet alleen bij het eindresultaat.
Statistieken kunnen verouderen wanneer data sterk verandert, maar automatisch bijwerken is niet in iedere situatie voldoende. Scheve verdelingen, gecorreleerde kolommen en nieuwe waarden kunnen lastig te schatten zijn. Het bijwerken van statistieken kan een ander plan opleveren, maar kan ook bestaande plannen wijzigen. Leg vast wat er is aangepast en vergelijk de uitvoering daarna onder dezelfde omstandigheden.
Parametergevoeligheid ontstaat wanneer één query met verschillende parameterwaarden sterk uiteenlopende aantallen rijen oplevert. Een plan dat goed werkt voor een zeldzame waarde kan slecht presteren voor een veelvoorkomende waarde, of andersom. Sommige databases bewaren een plan dat op eerdere parameters is gebaseerd; andere hebben mechanismen om varianten te maken. Onderzoek de gebruikte parameterwaarden en planhistorie voordat je de SQL herschrijft of hints inzet. Een hint kan een specifieke uitvoering sturen, maar verbergt soms een probleem dat door data of schattingen wordt veroorzaakt.
Valideer indexwijzigingen met reproduceerbare metingen
Een indexwijziging beoordeel je door voor en na de aanpassing dezelfde query en vergelijkbare data te meten. Leg het uitvoeringsplan, de werkelijke rijaantallen, logische leesacties, CPU-gebruik en verstreken tijd vast. Herhaal de meting met meerdere relevante parameterwaarden en controleer zowel selectieve als minder selectieve gevallen. Eén geslaagde uitvoering bewijst niet dat het nieuwe plan voor de volledige workload beter is. Een verandering in caching, achtergrondtaken of gelijktijdige transacties kan resultaten beïnvloeden.
Test waar mogelijk op een representatieve dataset en met een vergelijkbare databasestructuur. Een ontwikkelomgeving met weinig rijen kan een andere keuze opleveren dan productie. Neem ook schrijfbewerkingen mee: meet de invloed van inserts en updates op tabellen waarop indexen zijn toegevoegd. Bij gelijktijdige belasting kunnen lockgedrag, wachttijden en I/O-concurrentie zichtbaar worden die een geïsoleerde querytest niet laat zien.
Voer wijzigingen beheerst door en bewaar een mogelijkheid om het effect terug te draaien. Registreer de reden voor de index, de querypatronen die ermee worden ondersteund en de metingen waarop de keuze is gebaseerd. Monitor na ingebruikname of het plan en de workload zich gedragen zoals verwacht. Dat is extra relevant wanneer tabellen snel groeien of de verdeling van data verandert. Een index is geen eenmalige optimalisatie los van de applicatie: querygedrag, gegevensvolume en schrijfpatronen blijven bepalen of de structuur passend is.
Veelgestelde vragen
Wanneer moet je een database-index rebuilden of reorganiseren?
Rebuild of reorganiseer een index pas wanneer metingen laten zien dat de indexconditie de prestaties beïnvloedt; fragmentatie alleen is geen automatische reden. De impact hangt onder meer af van het databasesysteem, de opslag en hoe de workload de index gebruikt. Een onderhoudsactie kan bovendien extra I/O, ruimte en belasting veroorzaken.
- Controleer fragmentatie en paginadichtheid met de hulpmiddelen van je database.
- Meet of relevante query’s er daadwerkelijk door worden vertraagd.
- Controleer vooraf de gevolgen voor belasting, transacties en beschikbare opslag.
Hoe test je een nieuwe database-index veilig voordat je die in productie zet?
Test een nieuwe index eerst met representatieve data en query’s, en voer de wijziging daarna gecontroleerd uit met een plan om de impact te volgen en terug te draaien. Een kleine testdataset geeft mogelijk een misleidend beeld, omdat de optimizer op grotere tabellen andere keuzes kan maken.
- Vergelijk uitvoeringsplannen en meetwaarden vóór en na de wijziging.
- Controleer of jouw database online of gelijktijdig indexen kan aanmaken en welke beperkingen gelden.
- Volg na implementatie ook schrijfprestaties en andere belangrijke query’s.
Wat is het verschil tussen een sleutelkolom en een opgenomen kolom in een index?
Een sleutelkolom bepaalt de ordening en de zoekmogelijkheden van de index, terwijl een opgenomen kolom extra gegevens bewaart om een query mogelijk uit de index te beantwoorden. Opgenomen kolommen helpen dus bij het afdekken van een query, maar zijn doorgaans niet bruikbaar om rechtstreeks op te zoeken of te sorteren.
- Gebruik sleutelkolommen voor filters, joins en gewenste sorteringen.
- Neem alleen extra kolommen op die een relevante query nodig heeft.
- Houd rekening met extra opslag en onderhoud: ook opgenomen gegevens maken de index groter.
Waarom gebruikt een database-index geen index bij een LIKE-zoekopdracht?
Een gewone B-tree-index helpt vaak niet bij een LIKE-patroon dat met een jokerteken begint, zoals LIKE '%woord%', omdat de database dan geen startpunt in de geordende index kan bepalen. Een patroon als LIKE 'woord%' kan in veel systemen wel een bruikbaar begin van een bereik opleveren.
- Controleer of het patroon met een jokerteken begint.
- Gebruik voor zoeken naar woorden of tekstfragmenten eventueel full-text search of een gespecialiseerde index.
- Welke oplossing past, hangt af van de database en van de gewenste zoekbetekenis.
Hoe controleer je of een indexwijziging andere query's heeft vertraagd?
Controleer na een indexwijziging de prestaties van de bredere workload, niet alleen van de query waarvoor de index bedoeld was. Een nieuwe index kan bijvoorbeeld schrijfwerk verzwaren of ervoor zorgen dat andere query’s een ander uitvoeringsplan kiezen. Vergelijk met een representatieve periode vóór de wijziging en let op veranderingen die samenvallen met de implementatie.
- Volg responstijden, leesacties en CPU-gebruik van belangrijke query’s.
- Vergelijk planwijzigingen en foutmeldingen met eerdere metingen.
- Houd een terugdraaiplan klaar als de totale workload achteruitgaat.
