Modulelessen (1/4)
Indexen en B-bomen
Hoe kan een database in fracties van een seconde één record vinden tussen miljoenen rijen?
Wanneer we SELECT * FROM customers WHERE city = 'Milano' vragen, moet Postgres standaard een sequentiële scan uitvoeren: het leest fysiek de hele tabel rij voor rij van schijf, waarbij elke stad wordt vergeleken met "Milaan". Wanneer een tabel miljoenen records bevat, verlamt dit proces de prestaties.
Het belangrijkste structurele antwoord op deze knelpunten zijn Indexen.
Indexen (B-bomen)
Indexen werken precies zoals de analytische index achterin een groot boek. In plaats van het hele boek te lezen om te zien waar het over "Milaan" gaat, zoeken we de achterpagina's op waar het woord "Milaan" ons doorverwijst naar [p. 45, 114, 230].
Databases bouwen achter de schermen deze afzonderlijke, zeer compacte datastructuur (vaak een "B-Tree"), geordend op de kolom of kolommen in kwestie, met directe verwijzingen naar de fysieke opslag van de overeenkomende rijen.
-- Creare un indice semplice
CREATE INDEX idx_customers_city ON customers(city);Deze enkele handeling activeert de Postgres-engine, die alle city waarden op de achtergrond zal extraheren en indexeren. Eenmaal voltooid, zal elke volgende SELECT ... WHERE city = '...' vliegen!
Primaire sleutels en uniciteit
Wist u dat de primaire sleutel (PRIMARY KEY) en UNIQUE sleutelregels in het geheim indexen gebruiken? Omdat de DB ervoor moet zorgen dat een e-mailadres of ID nooit wordt gedupliceerd bij het invoegen of wijzigen van rijen, creëert PostgreSQL er automatisch een impliciete B-Tree-index bovenop: op deze manier duren de uniciteitscontroles microseconden.
Daarom presteren joins in het veld ID ongelooflijk goed.
Een index verwijderen
Als u zich realiseert dat zware zoekopdrachten zijn veranderd en een index nooit is geraadpleegd, is het verstandig deze te vernietigen en vitale ruimte terug te winnen:
DROP INDEX idx_customers_city;Jouw beurt
In onze e-commerce merken we via dashboards dat we klanten heel vaak filteren op nationaliteit (de landkolom bij klanten). \nMaak een nieuwe basisindex voor de landkolom en noem deze 'idx_customers_country'.
Toon hint
Gebruik CREATE INDEX indexnaam ON tabelnaam (kolomnaam);
Oplossing beschikbaar na 3 pogingen
Secundaire unieke indexen verkennen
Een B-Tree-index kan ook worden gebruikt om een UNIEKe beperking te simuleren tijdens het maken en het opzoeken te versnellen, terwijl de logische bedoeling expliciet en afgedwongen wordt.\n\nMaak een nieuwe index waarin u het UNIEKE sleutelwoord declareert. Definieer het zo dat ongewenste dubbele aanmeldingen van andere gebruikers worden geblokkeerd, waardoor uniciteit wordt afgedwongen in de kolom 'e-mail' van 'klanten', en noem het 'idx_unique_email_customer'.
Toon hint
Schrijf CREATE UNIQUE INDEX index_name ON tabel (kolom);
Oplossing beschikbaar na 3 pogingen