Databaseontwerp en optimalisatie

De meeste trage databases missen de voor de hand liggende index

Wanneer een applicatie trager wordt naarmate hij groeit, is de oorzaak meestal niet de server of de database-engine. Het zijn een handvol queries die hele tabellen scannen omdat geen index past bij hoe ze zoeken. Een query die 20 milliseconden kostte bij 1.000 rijen kan seconden kosten bij een miljoen, en niemand merkt het tot de klanten het merken.

We beginnen bij bewijs: de eigen statistieken van de database over welke queries het vaakst en het langst draaien. Dat wijst meestal op een korte lijst oplossingen, zoals een index op de kolommen die een veelgebruikt filter gebruikt, een query die is herschreven om te voorkomen dat hele tabellen worden geladen, of een ontbrekende koppeling tussen tabellen die herhaalde lookups forceert. We meten elke query voor en na, zodat de verbetering zichtbaar is in plaats van beweerd.

Beperkingen houden slechte data tegen bij de deur

Veel databases vertrouwen erop dat de applicatie de gegevens schoon houdt. De app controleert of een e-mail uniek is, of een bestelling bij een echte klant hoort, of een aantal niet negatief is. Dan slaat een tweede app, een importscript of een haastige handmatige correctie die controles over, en sluipen er duplicaten en verweesde records in. Rapporten gaan van elkaar afwijken en niemand weet zeker welk getal klopt.

Een goed ontworpen schema handhaaft de belangrijke regels zelf: unieke constraints, foreign keys, check constraints en verstandige defaults. Wat ook naar de database schrijft, moet zich eraan houden. Bij een bestaand systeem zoeken we uit waar gegevens al mis zijn gegaan, plannen we de opschoning en voegen we daarna de constraints toe zodat het niet opnieuw gebeurt.

• Unieke constraints op zaken die niet mogen herhalen, zoals e-mails of factuurnummers
• Foreign keys zodat records niet kunnen wijzen naar iets dat niet bestaat
• Beperkingen controleren voor waarden die binnen bereik moeten blijven
• Indexen gekozen op basis van echte querypatronen, niet overal toegevoegd