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.
Bekijk eerst de trage query en wat die precies doet
Je begint met het oplossen van een traag SQL-gedrag door precies te bepalen om welke query het gaat. Dat klinkt logisch, maar daardoor weet je onder welke omstandigheden het probleem ontstaat. Verzamel altijd de precieze querytekst, gebruikte parameters, gemeten responstijden en hoe vaak deze query draait. Een rapportagequery die af en toe piekt, vraagt iets anders dan een SELECT die duizenden keren per minuut de productieomgeving belast. Denk aan de verhouding tussen lezen en schrijven: een index voor snellere rapportages kan bulkimports of veelvoorkomende updates verzwaren.
Bekijk het liefst meerdere gevallen van dezelfde query. Verschillen tussen koude en warme cache verklaren soms waarom je op de ene server niks merkt en op de andere alles vastloopt. Schrijf daarnaast op hoe groot de tabel op dit moment is, en of die nog hard groeit. Een oplossing die prima werkt bij tienduizend rijen kan falen zodra je miljoenen rijen bereikt. Kijk dus ook eens naar de spreiding van databasebelasting, in plaats van alleen naar pieken: een frequente vertraging trekt soms zwaarder aan bronnen dan een incidentele piek.
Zo'n inventarisatie voorkomt dat je lukraak een index toevoegt die misschien niemand helpt. Je geeft jezelf ook houvast bij de interpretatie van het queryplan: de database baseert zich op wat hij nú weet over data en parameters, niet alleen de querytekst.
Voor query-optimalisatie in maatwerksoftware kun je een PHP developer inschakelen.
Dat doet een index eigenlijk met het zoekwerk
Een database slaat tabellen en hun indexen verschillend op. De structuur van een index geeft je snellere toegang tot bepaalde gegevens. Een B-tree is hier een bekend voorbeeld: dat is in feite een hiërarchische boom die zoekwaarden geordend houdt. Als je géén bruikbare index biedt, zoekt de database rijen via een table scan, dus moeten er flink wat rijen doorlopen worden om iets te vinden. Met de juiste index navigeert de database rechtstreeks naar het relevante bereik van sleutelwaarden; ze kan dus precies waar nodig zoeken en lezen.
In een index staan niet altijd alle kolommen. Vaak zijn het de sleutelwaarden met een verwijzing naar de juiste rij, soms (zoals bij een geclusterde index in SQL Server) alles van de rij zelf. Het maakt nogal uit of de opgevraagde gegevens al direct in de index staan. Als dat zo is, is een index-only scan voldoende. Zo niet, haalt de database aanvullende kolommen alsnog uit de tabel. Dit is efficiënt bij weinig resultaten, maar kan chaotisch worden als je juist heel veel verspreid opvraagt.
Gebruik van indexen werkt dus niet vanzelf altijd als je een geïndexeerde kolom gebruikt. De database moet kunnen voorspellen dat de index voor een filter, sorteer- of joinactie zin heeft, en schat of dat goedkoper is dan andere opties. Waardeverdeling in de kolom, het aantal te lezen rijen en benodigde aanvullende tabeltoegang bepalen het effect. Een index is niet bedoeld als universele versneller, maar als hulpmiddel voor specifiek zoekgedrag.
Samengestelde index? Onthoud de kolomvolgorde
Een samengestelde index bestaat uit meerdere kolommen in een vaste volgorde. Die volgorde is niet willekeurig: ze bepaalt voor welke filters en sorteringen je de index direct kunt gebruiken. Stel, je indekseert eerst op klant_id, dan op status. Dan werkt een zoekopdracht op klant_id, en op klant_id plus status, doorgaans prima, maar alleen zoeken op status pakt zelden goed uit omdat dat niet het eerste sorteerpunt is. Regels kunnen verschillen tussen databases, maar het 'voorste' deel van de index is altijd belangrijk.
Kijk hierbij vooral naar voorkomend SQL-gebruik. Staat op één kolom meestal een WHERE gelijkheid of een JOIN-voorwaarde? Zet die dan aan het begin. Daarna volgt een kolom die bijvoorbeeld een ORDER BY ondersteunt. Dit is geen alles-voor-iedereen-regel: ook een kolom met weinig unieke waarden kan nuttig zijn, afhankelijk van het filter- en zoekprofiel. Kijk naar queryplannen in de praktijk, niet alleen naar mooie kolomnamen.
Een brede index ondersteunt meer querycombinaties, maar kost meer opslag en raakt sneller fragmentatie kwijt, want elke wijziging telt. Twee aparte smalle indexen zijn niet automatisch beter, want samenvoegen kost soms onverwacht extra werk. Laat de query's en hun varianten je keuze bepalen, niet alleen het schemaontwerp van de tabel.
Zo lees je een queryplan: van schatting tot echte cijfers
Een queryplan geeft inzicht in de route die de database kiest om jouw SQL uit te voeren. In een geschat plan maakt de optimizer gebruik van statistieken en gokt op basis van wat in het verleden klopt; een werkelijk plan laat extra zien wat er daadwerkelijk gebeurde tijdens het uitvoeren. Let goed op de volgorde waarin bewerkingen plaatsvinden, zie hoeveel rijen verwacht werden en hoeveel er in werkelijkheid verwerkt zijn, en check hoeveel er fysiek gelezen is. Grote verschillen tussen inschatting en werkelijkheid kunnen komen door scheve statistieken of een ongelijke verdeling van data.
Hoe het plan eruitziet, hangt af van je database. De ene database laat bewerkingen van onder naar boven zien, een andere gebruikt een boomstructuur. Het is handig om eerst uit te zoeken hoe jouw systeem richtingen, bewerkingen en kostenrangorde aangeeft. Kostenpercentages geven hier alleen een relatieve inschatting binnen één uitvoering, en zeggen niets rechtstreeks over tijd. Zie een opvallend percentage dus als een clue, niet als direct bewijs dat daar het probleem zit.
Vergelijk trage met vlotte queryruns, controleer op verschillen in parameters, datavolumes en cacheeffecten. Zoek naar afwijkingen in rijgebruik, dubbele acties en onnodige sorteringen. Heb je metrics over fysieke en logische I/O of CPU, neem die zeker mee. Een queryplan wordt pas betekenisvol als je het samenlegt met echte meetgegevens én kennis van je data.
Table scan: gevaar of soms gewoon de beste keuze
Zie je in je plan een table scan? Dat is niet automatisch reden tot paniek. Een scan die veel of alle rijen leest, klinkt misschien alsof je alles verkeerd doet, maar als je toch het merendeel van de tabel opvraagt, is dit vaak de beste optie. Ga je met een index zoeken om daarna alsnog voor bijna elke rij terug te springen naar de tabel, dan leidt dat tot veel losse leesacties, duurder dan één aaneengesloten scan. Voor kleine tabellen is deze aanpak sowieso vaak sneller dan ingewikkeld indexnavigeren. Het zien van een scan is dus op zichzelf geen argument om per se te gaan indexeren.
Zoek uit hoeveel rijen je daadwerkelijke query ontvangt, in relatie tot de grootte van de complete tabel. Scan je een grote tabel voor maar een paar rijen, kijk dan of je WHERE-filterkolommen een index gebruiken, en controleer of de conditie sargable is (dus geschikt voor zoeken met index). Functies op kolommen, impliciete conversies of een onhandig zoekpatroon kunnen de optimizer in de weg zitten. Dit verschilt subtieler per databasesysteem.
Soms krijgt de database foute verwachtingen door achterlopende statistieken, of omdat er veel kolommen tegelijk nodig zijn waarbij de index geen voordeel biedt. Pas pas na onderzoek de query of index aan. Extra indexen toevoegen voor een tabel die zelden anders dan sequentieel gelezen wordt, levert alleen maar onderhoud en geen winst op.
Key lookups, sorteren en joins: let op details
Een key lookup gebeurt als de database via een index geschikte sleutels vindt, maar daarna voor ontbrekende kolommen alsnog naar de tabel moet. Met weinig resultaten is dat prima, maar als duizenden rijen betrokken zijn, kunnen die terughalingen samen de query sterk vertragen. Kijk hier altijd naar echte meetcijfers: hoeveel lookups gebeuren er, hoeveel rijen zijn het uiteindelijk? Een covering index die alle gevraagde kolommen bevat, voorkomt dit extra werk, maar let op: je index bestand wordt daarmee ook groter.
Komt er in het plan een soort sorteerbewerking (bijvoorbeeld SORT of ORDER BY) naar voren, check dan of een index de sortering had kunnen geven. Soms is een sortering onvermijdelijk omdat de query nu eenmaal een hoop data moet herordenen, maar vaak loont het om te kijken of een index filter en sorteer tegelijk ondersteunt. Aggregaties (SUM, COUNT, enzovoort) gebruiken vaak een hash of een andere sorteermethode; dat is niet per se slecht, maar let op als dit overdreven veel geheugen of diskruimte vraagt.
Voor joins gebruikt de database verschillende methoden afhankelijk van tabelgrootte en volgorde. Nested loops werken meestal goed bij kleine datasets en snelle indexzoekacties; hash of merge joins passen weer beter bij grotere datasets of bulkoperaties. Gaat de optimizer uit van foute kardinaliteit, dan pakt hij de verkeerde joinmethode. Check dus niet alleen de operatornaam, maar ook het invoervolume. Los verkeerde inschattingen op voordat je meer indexen toevoegt.
Vergeet niet: indexen maken schrijven zwaarder
Elk keer dat een rij wordt toegevoegd, verwijderd of gewijzigd in een tabel met indexen, moeten die indexen óók worden aangepast. Dus niet alleen INSERT, maar ook UPDATE (zeker als die een geïndexeerde waarde wijzigt) en DELETE zorgen voor extra werk, soms zelfs bij wijzigingen op niet-geïndexeerde kolommen als de structuur dat vereist. Extra indexen nemen bovendien meer opslag in, moeten mee in de back-up, en verbruiken schijfcache. Dat kan de lees- én schrijfsnelheid raken, zeker bij zware bulkoperaties of veel gelijktijdig verkeer.
Wat is het je waard? Een index die een essentiële query enorm versnelt, mag best wat extra schrijfwerk kosten. Maar een index die nauwelijks gebruikt wordt terwijl-ie wel veel sleutels bevat, kun je beter heroverwegen. Kijk naar echte gebruiksstatistieken over een goede periode en neem speciale gevallen zoals rapportages of groot onderhoud mee, statistieken kunnen bijvoorbeeld na een herstart leeg zijn of niet alle patronen tonen.
Plaats niet gedachteloos nieuwe indexvarianten naast bestaande samengestelde indexen die het patroon al vangen. Overlappende indexen zorgen voor dubbel schrijfwerk en verwarring bij later onderhoud. Kijk bij verwijderen zorgvuldig naar afhankelijkheden en queryplannen: een schijnbaar overbodige index kan specifiek rapportageverkeer ondersteunen dat je meetperiode net gemist heeft.
Ook de Suitableshop-case laat zien waarom de technische performance van een webshop ertoe doet.
Statistieken en parameters kunnen alles veranderen
De optimizer schat het aantal resultaten op basis van statistieken die hij kent, dat zijn opnames van de data op bepaalde kolommen. Als die cijfers totaal niet meer passen bij de werkelijkheid, omdat er bijvoorbeeld veel meer data zijn of de verdeling nu heel anders ligt, kiest de database soms een dramatisch slecht plan. Je ziet dat aan verschillen tussen geschatte en werkelijke rijen in het plan, bekijk niet alleen de eindteller, maar ook tussentijdse bewerkingen.
Statistieken worden niet altijd vanzelf bijgehouden. Soms raken ze verouderd als de data snel verandert, of zijn ze niet fijnmazig genoeg voor een gekantelde verdeling of nieuwe waardes die niet goed af te leiden zijn uit bestaande statistieken. Het verversen van statistieken kan de optimizer anders laten plannen, mét mogelijk directe impact op al het SQL-verkeer. Leg veranderingen dus altijd vast, en test onder gelijke omstandigheden of de uitkomst nu wel klopt.
Parametrisatie is tricky: één en dezelfde query kan, afhankelijk van de parameterwaarde, extreem verschillende prestatieresultaten geven. Gaat het plan uit van een zeldzame waarde en krijgt het vervolgens een veelvoorkomende, dan loopt alles uit de pas. Sommige databases hergebruiken plannen op basis van de eerste waarde die langskomt; andere systemen maken aparte 'planvarianten'. Kijk altijd eerst naar werkelijke queries en parameters voordat je de SQL of index aanpast. Hints kunnen dwingen tot bepaald gedrag, maar zijn soms slechts een pleister op een dieperliggend data- of inschattingsprobleem.
Zo meet je het effect van indexwijzigingen
Om te weten of een indexwijziging nut heeft, meet je dezelfde query voor én na aanpassing, met vergelijkbare data. Leg voor beide situaties het queryplan, echte rijaantallen, logische leesacties, CPU en kloktijd vast. Wissel van parameterwaarden, zodat je zowel zeldzame als veelvoorkomende gevallen ziet. Eén goede testrun is niet genoeg om te zeggen dat alles nu optimaal draait voor alle SQL-verkeer. Factoren als caching, achtergrondprocessen of andere transacties kunnen de verschillen vertekenen.
Test daarom bij voorkeur met een dataset en een omgeving die lijken op productie. In een ontwikkelbak met 100 rijen komen vaak heel andere beslissingen uit de optimizer. Kijk niet alleen naar SELECT, maar ook naar INSERT en UPDATE: meet de werkelijke impact op schrijfprestaties van tabellen waarop je nieuwe indexen hebt staan. Gelijktijdige belasting (denk aan veel users of scripts tegelijk) kan extra lock- en I/O-problemen laten zien, die bij losse queries niet opvallen.
Doe indexaanpassingen stapsgewijs en zorg dat je altijd weet hoe je de wijziging kunt terugdraaien. Leg bij elke wijziging vast waarom je deze index toevoegt, welke query’s of patronen je ermee helpt, en wat de meetgegevens waren. Houd de performance na livegang actief in de gaten, vooral bij rap groeiende tabellen of als je data zich anders gaat gedragen. Een index is nooit een definitieve ingreep: veranderend query- en schrijfgedrag blijft invloed houden op het nut van de indexstructuur.
Voor structurele verbeteringen aan een webshop kan webshopontwikkeling verder gaan dan indexen alleen.
Veelgestelde vragen
Wanneer moet je een database-index rebuilden of reorganiseren?
Je voert een rebuild of reorganisatie alleen uit als blijkt dat de conditie van de index de prestaties aantast. Fragmentatie is geen reden op zich, het moet echt effect op het gedrag hebben. Hoe groot de impact is en wat je eraan hebt, verschilt per database, soort opslag en workload: een reorganisatie kan daarnaast ook zorgen voor extra I/O, ruimtegebruik en systeembelasting.
- Gebruik de tools van je database om fragmentatie en paginadichtheid te meten.
- Meet of query’s die deze index gebruiken er door fragmentatie daadwerkelijk last van hebben.
- Check eerst welke gevolgen een onderhoudsactie heeft voor I/O, transacties en beschikbare ruimte.
Hoe test je een nieuwe database-index veilig voordat je die in productie zet?
Test een nieuwe index door eerst op representatieve data en queries te meten, en daarna de wijziging in productie pas door te voeren mét een plan om bij problemen terug te draaien. Een kleine testomgeving geeft soms een verkeerde indruk, omdat voor grotere tabellen of workloads de optimizer heel andere keuzes maakt.
- Leg plannen en meetcijfers vast, vóór en na de aanpassing.
- Kijk of jouw database indexen online of onder belasting kan aanmaken, en welke limieten daarbij gelden.
- Blijf na livegang niet alleen lezen, maar let ook op schrijfprestaties en prestaties van andere belangrijke queries.
Wat is het verschil tussen een sleutelkolom en een opgenomen kolom in een index?
Een sleutelkolom bepaalt in welke volgorde de rijen in de index zitten en waar je op kunt zoeken of sorteren. Een opgenomen kolom (inclusief kolom) slaat extra data op in de index, zodat een query direct genoeg informatie kan vinden zonder terug te moeten naar de tabel. Met opgenomen kolommen kun je queries volledig vanuit de index beantwoorden, maar ze zijn niet geschikt voor zoeken of sorteren binnen de index.
- Gebruik sleutelkolommen als je wilt kunnen filteren, joinen of sorteren.
- Voeg alleen opgenomen kolommen toe die een query echt nodig heeft.
- Weet dat opgenomen kolommen het indexbestand groter maken en meer onderhoud vragen.
Waarom gebruikt een database-index geen index bij een LIKE-zoekopdracht?
Bij een LIKE-zoekopdracht met een wildcard aan het begin (LIKE '%woord%') kan de database geen startpunt bepalen in de index, omdat die gesorteerd is. Het volledige bereik is dan onbekend. Gebruik je een patroon als LIKE 'woord%', dan lukt dat wel: daar kan de database wél gericht zoeken op het begin van de index.
- Kijk of jouw LIKE-patroon begint met een wildcard of niet.
- Bij zoekopdrachten naar woorden of tekstsegmenten kan full-text search of een speciale index een betere oplossing zijn.
- Welke manier werkt, hangt af van het databasesysteem en hoe specifiek je wilt zoeken.
Hoe controleer je of een indexwijziging andere query's heeft vertraagd?
Je ziet of een indexwijziging andere query's vertraagt door de hele workload te monitoren, niet alleen het gedrag van één query. Een nieuwe index kan bijvoorbeeld het schrijfwerk zwaarder maken of andere queries een ander plan laten kiezen, waardoor de prestaties verschuiven. Vergelijk prestaties in een tijdvak vóór en na de wijziging om te zien welk effect echt door de inzet komt.
- Check responstijden, leesacties en CPU-gebruik van belangrijke, verschillende queries.
- Vergelijk de queryplannen en mogelijke foutmeldingen met eerdere metingen.
- Houd een plan klaar om de indexwijziging terug te draaien als blijkt dat het effect negatief uitpakt voor het geheel.
Foto: panumas nikhomkhai