Πώς βελτιστοποιείς αργές SQL queries;

Question

Accepted Answer

Η βελτιστοποίηση αργών queries ξεκινά με **ανάλυση του execution plan** (`EXPLAIN`) για να βρεις το bottleneck, και στη συνέχεια εφαρμογή στοχευμένων διορθώσεων — πιο συχνά **προσθήκη indexes**, επανεγγραφή της query, και αποφυγή συνηθισμένων anti-patterns. Το μεγαλύτερο κέρδος είναι συνήθως η μετατροπή των full table scans σε index lookups.

## Βήμα 1: ανάλυση με 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` (ή `EXPLAIN ANALYZE`) δείχνει πώς η βάση δεδομένων εκτελεί την query — κρουσιαίως, αν κάνει **full table scan** (αργό) ή χρησιμοποιεί **index** (γρήγορο). Πάντα κάνε profiling πριν βελτιστοποιήσεις.

## Το μεγαλύτερο κέρδος: προσθήκη indexes

```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
```

Η πιο συνηθισμένη και impactful διόρθωση: **προσθήκη indexes** σε columns που χρησιμοποιούνται σε `WHERE`, `JOIN`, και `ORDER BY`. Αυτό μετατρέπει τα full table scans σε γρήγορα lookups.

## Συνηθισμένα anti-patterns για διόρθωση

```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
```

## Άλλες τεχνικές βελτιστοποίησης

```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)
```

## Γιατί έχει σημασία

Η βελτιστοποίηση queries είναι κρίσιμη γνώση για την απόδοση της βάσης δεδομένων — αργές queries είναι ένας από τους κύριους λόγους κακής απόδοσης της εφαρμογής, και η κατανόηση του πώς να τις βρεις και να τις διορθώσεις είναι ένα βασικό senior-level skill για όποιον δουλεύει με βάσεις δεδομένων σε μεγάλη κλίμακα.

Η θεμελιώδης πρακτική είναι η **ανάλυση με `EXPLAIN`** (εξέταση του execution plan για εύρεση του πραγματικού bottleneck — κρουσιαίως αν η query κάνει ένα αργό full table scan ή ένα γρήγορο index lookup) πριν βελτιστοποιήσεις, παρά το να μαντεύεις — αυτή η measurement-driven προσέγγιση είναι αυτή που κάνει την βελτιστοποίηση αποτελεσματική.

Το **απόλυτο μεγαλύτερο και πιο συνηθισμένο κέρδος είναι η προσθήκη indexes** σε filtered, joined, και sorted columns (μετατροπή των full table scans σε γρήγορα lookups — συχνά βελτιώνοντας τον χρόνο της query κατά τάξεις μεγέθους), κάνοντας την γνώση των indexes κεντρική στη βελτιστοποίηση.

Η κατανόηση των **συνηθισμένων anti-patterns** είναι εξίσου σημαντική: functions σε columns και leading-wildcard `LIKE` εμποδίζουν τη χρήση indexes, `SELECT *` φέρνει περιττά δεδομένα, και ειδικά **N+1 queries** (querying σε ένα loop αντί για ένα μόνο JOIN — ένας συχνός, σοβαρός performance killer στον κώδικα της εφαρμογής) — η αναγνώριση και διόρθωση αυτών αντιμετωπίζει τις πιο συνηθισμένες αιτίες των αργών queries.

Άλλες τεχνικές (composite indexes, μείωση των scanned δεδομένων, επανεγγραφή subqueries ως JOINs, ενημέρωση statistics, materialized views για expensive queries) συμπληρώνουν το toolkit.

Επειδή η απόδοση της query επηρεάζει άμεσα την ανταπόκριση της εφαρμογής και την κλιμακωσιμότητα, και επειδή η συστηματική προσέγγιση (ανάλυση με EXPLAIN, και μετά διόρθωση — συνήθως με προσθήκη indexes και εξάλειψη anti-patterns όπως N+1) είναι αυτή που κρατά τις βάσεις δεδομένων γρήγορες υπό φόρτο, η κατακτήση της βελτιστοποίησης queries είναι σημαντική, υψηλής αξίας senior γνώση που διακρίνει developers που μπορούν να κατασκευάσουν performant, scalable database-backed εφαρμογές — μια συχνά-σχετική, κρίσιμη skill και συνηθισμένο θέμα συνέντευξης για database performance.