Antwoord

Indexen zijn speciale datastructuren (meestal op basis van B-bomen) die worden gebruikt voor snelle zoekopdrachten naar gegevens op een bepaalde kolom of set kolommen van een tabel. Indexen versnellen het ophalen, sorteren en filteren door het aantal bekeken rijen te verminderen.

Soorten indexen:

• Gewone (B-tree, hash)
• Samengestelde (meerkolomindexen)
• Unieke (zorgt voor unieke waarden)
• Bedekkende (covering)
• Volledige tekst (full-text)

Indexen versnellen:

• WHERE ... = ...
• JOIN op geïndexeerde kolom
• ORDER BY en GROUP BY op geïndexeerde kolom

Kunnen vertragen:

• Invoegen, bijwerken, verwijderen — indexen vereisen extra bewerkingen voor het onderhouden van hun structuur.
• Als aanvragen vaak gebruik maken van kolommen waar geen indexen op zijn, zijn indexen nutteloos, en bij massale invoegen zelfs schadelijk.

Voorbeeld van het maken van een index:

CREATE INDEX idx_user_email ON users (email);

Voorbeeld waar index niet helpt:

SELECT * FROM users WHERE lower(email) = 'test@example.com';
-- als de index is opgebouwd op email, maar de aanvraag de functie lower(email) gebruikt, wordt de index niet gebruikt!

Misleidende vraag

Als je een index toevoegt aan alle kolommen van de tabel, zal de uitvoering van alle SELECT-aanvragen altijd versnellen?

Antwoord:
Nee. Indexen versnellen alleen die aanvragen waarbij filtering of sortering strikt plaatsvindt op de geïndexeerde kolom zonder gebruik te maken van functies of bewerkingen die het gebruik van de index verhinderen. Een overmaat aan indexen vertraagt niet alleen INSERT/UPDATE/DELETE, maar neemt ook veel ruimte in beslag, en sommige complexe aanvragen kunnen helemaal om indexen heen werken (bijvoorbeeld bij het scannen op een bereik met een expressie).

Voorbeeld:

SELECT * FROM orders WHERE year(order_date) = 2023;
-- als er alleen een index op order_date is, werkt de index niet vanwege de functie year()

---

Geschiedenis

In het e-mailsysteem werd er een trigger gemaakt die een index aanmaakte voor elk veelgebruikt veld. Na zes maanden daalde de prestaties van het systeem - voor elke invoeging of wijziging van een rij werd 4-5 keer zoveel tijd besteed. Na een audit werd het aantal indexen verminderd, en het systeem versnelde.

---

Geschiedenis

Op het reclameplatform kwamen vaak SELECT-aanvragen voor met een filter op substring(url, 1, 10). Ondanks de index op url, gebruikte SQL de index niet vanwege de functie substring. De oplossing was het invoeren van een apart veld voor deze selectie en een index daarop.

---

Geschiedenis

In het loyaliteitsprogramma werd er een samengestelde index gemaakt op de velden (customer_id, shop_id). Bij aanvragen alleen op shop_id werd de index niet toegepast, en was er een volledige tabelscan (full scan). Dit leidde tot het verlies van de voordelen van de index tijdens de bonusberekeningen. Optimalisatie hielp: een aparte index op shop_id.