Hoe optimaliseer je trage SQL-queries?

Question

Accepted Answer

Het optimaliseren van trage queries begint met **het analyseren van het uitvoeringsplan** (`EXPLAIN`) om het knelpunt te vinden, gevolgd door gerichte oplossingen — meestal **indexen toevoegen**, de query herschrijven en veelvoorkomende anti-patterns vermijden. De grootste verbetering is meestal het omzetten van volledige tabelscannings naar index-lookups.

## Stap 1: analyseer met EXPLAIN

```sql
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 5;
-- shows the EXECUTION PLAN: scan type (Seq Scan = full scan, Index Scan = using an index),
-- estimated/actual rows, cost, and timing → reveals WHERE the time goes
```

`EXPLAIN` (of `EXPLAIN ANALYZE`) toont hoe de database de query uitvoert — cruciaal is of het een **volledige tabelscan** (traag) of een **index** (snel) gebruikt. Profile altijd voordat je optimaliseert.

## De grootste winst: voeg indexen toe

```sql
-- if EXPLAIN shows a Seq Scan on a filtered/joined column → add an index
CREATE INDEX idx_orders_customer ON orders(customer_id);
-- turns a full scan into a fast index lookup
```

De meest voorkomende en impactvolle oplossing: **voeg indexen toe** op kolommen die in `WHERE`, `JOIN` en `ORDER BY` worden gebruikt. Dit zet volledige tabelscannings om in snelle lookups.

## Veelvoorkomende anti-patterns om op te lossen

```sql
-- ❌ functions on a column prevent index use
WHERE UPPER(email) = 'X@Y.COM'        -- can't use an index on email
WHERE email = 'x@y.com'               -- ✅ can use the index

-- ❌ leading wildcard LIKE can't use an index
WHERE name LIKE '%smith'              -- ✅ 'smith%' (prefix) can use an index

-- ❌ SELECT * fetches unneeded columns
SELECT * → SELECT only_needed_columns  -- less data, may use a covering index

-- ❌ N+1 queries (querying in a loop) → use a single JOIN instead
```

## Andere optimalisatietechnieken

```text
✓ Fix N+1 queries (one query per row) → use JOINs / batch into one query
✓ Add appropriate indexes (including COMPOSITE for multi-column filters)
✓ Reduce data scanned — filter early, select only needed columns, paginate
✓ Rewrite subqueries as JOINs where more efficient
✓ Use EXISTS instead of IN for existence checks on large subqueries
✓ Keep statistics updated (ANALYZE) so the planner makes good choices
✓ Consider denormalization / materialized views for expensive repeated queries
✓ Watch for missing indexes on FOREIGN KEYS (joins scan without them)
```

## Waarom het belangrijk is

Query-optimalisatie is essentiële kennis voor databaseprestaties — trage queries zijn een van de belangrijkste oorzaken van slechte applicatieprestaties, en het begrijpen hoe je deze kunt vinden en oplossen is een essentiële senior-level vaardigheid voor iedereen die met databases op schaal werkt.

De fundamentele discipline is **analyseren met `EXPLAIN`** (het onderzoeken van het uitvoeringsplan om het werkelijke knelpunt te vinden — cruciaal of de query een trage volledige tabelscan of een snelle index-lookup uitvoert) voordat je optimaliseert, in plaats van gissen — deze metinggestuurde aanpak is wat optimalisatie effectief maakt.

De **enkele grootste en meest voorkomende winst is het toevoegen van indexen** op gefilterde, samengevoegde en gesorteerde kolommen (het omzetten van volledige tabelscannings naar snelle lookups — vaak het verbeteren van querytijd met ordes van grootte), waardoor indexkennis centraal staat in optimalisatie.

Het begrijpen van **veelvoorkomende anti-patterns** is even belangrijk: functies op kolommen en leading-wildcard `LIKE` voorkomen index-gebruik, `SELECT *` haalt onnodige gegevens op, en vooral **N+1 queries** (queries in een lus in plaats van een enkele JOIN — een veel voorkomende, ernstige performancekiller in applicatiecode) — het herkennen en oplossen van deze zaken behandelt de meest voorkomende oorzaken van trage queries.

Andere technieken (samengestelde indexen, het verminderen van gescande gegevens, het herschrijven van subqueries als JOINs, het bijhouden van statistieken, gerealiseerde weergaven voor dure queries) completeren de gereedschapskist.

Omdat queryprestaties rechtstreeks van invloed zijn op toepassingsresponsiviteit en schaalbaarheid, en omdat de systematische aanpak (analyseer met EXPLAIN, daarna oplossen — meestal door indexen toe te voegen en anti-patterns zoals N+1 te elimineren) is wat databases snel houdt onder belasting, is het beheersen van query-optimalisatie belangrijk, waardevol senior-kennis die ontwikkelaars onderscheidt die performante, schaalbare op databases gebaseerde applicaties kunnen bouwen — een veelvuldig relevant, kritieke vaardigheid en veelvoorkomend interviewonderwerp voor databaseprestaties.