Hogyan optimalizálsz lassú SQL lekérdezéseket?

Question

Accepted Answer

A lassú lekérdezések optimalizálása az **végrehajtási terv elemzésével** (`EXPLAIN`) kezdődik, hogy megtaláljuk a szűk keresztmetszetet, majd célzott javításokat alkalmazunk — legelőször az **indexek hozzáadása**, a lekérdezés átírása és a gyakori anti-minták elkerülése. A legnagyobb nyereség általában a teljes táblascanok indexlookupokká való átváltása.

## 1. lépés: elemzés EXPLAIN-nel

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

Az `EXPLAIN` (vagy `EXPLAIN ANALYZE`) azt mutatja meg, hogyan hajtja végre az adatbázis a lekérdezést — legfontosabban azt, hogy **teljes táblascan** (lassú) vagy **index** (gyors) használ-e. Mindig profilezzuk az optimalizálás előtt.

## A legnagyobb nyereség: indexek hozzáadása

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

A leggyakoribb és leghatásosabb megoldás: **indexeket hozzáadni** a `WHERE`, `JOIN` és `ORDER BY` clause-okban használt oszlopokhoz. Ez a teljes táblascanokat gyors lookupokká alakítja.

## Gyakori anti-minták javítása

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

## Egyéb optimalizálási technikák

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

## Miért számít ez

A lekérdezés optimalizálása kritikus fontosságú az adatbázis teljesítménye szempontjából — a lassú lekérdezések a gyenge alkalmazásteljesítmény fő oka, és az azok megtalálása és javítása elengedhetetlen senior szintű készség mindenkinek, aki nagyobb léptékű adatbázisokkal dolgozik.

Az alapvető disciplína az **`EXPLAIN` segítségével történő elemzés** (a végrehajtási terv megvizsgálása a tényleges szűk keresztmetszet megtalálásához — kulcsfontosságú, hogy a lekérdezés lassú teljes táblascanot vagy gyors indexlookupot hajt-e végre) a találgatás helyett — ez a méréseken alapuló megközelítés az, ami az optimalizálást hatékonyv teszi.

Az **egyetlen legnagyobb és leggyakoribb nyereség az indexek hozzáadása** szűrt, összekapcsolt és rendezett oszlopokon (a teljes táblascanokat gyors lookupokká alakítva — gyakran a lekérdezési időt nagyságrendekkel javítva), így az indexek ismerete központi az optimalizálásban.

Az **gyakori anti-minták megértése** ugyanolyan fontos: az oszlopokon alkalmazott függvények és a vezetõ helyettesítõ karakteres `LIKE` meggátolják az indexhasználatot, a `SELECT *` szükségtelen adatokat kér le, és különösen az **N+1 lekérdezések** (lekérdezés cikluson belül egy helyett egyetlen JOIN — a gyakori, súlyos teljesítményprobléma az alkalmazáskódban) — ezek felismerése és javítása a lassú lekérdezések leggyakoribb okait kezeli.

Az egyéb technikák (összetett indexek, a letapogatott adatok csökkentése, alkérdezések átírása JOIN-okra, statisztikák naprakészen tartása, drága lekérdezésekhez materializált nézetek) kiegészítik az eszközkészletet.

Mivel a lekérdezés teljesítménye közvetlenül befolyásolja az alkalmazás válaszidejét és skálázhatóságát, és mivel a szisztematikus megközelítés (elemzés EXPLAIN-nel, majd javítás — általában indexek hozzáadásával és az N+1-hez hasonló anti-minták kiküszöbölésével) az, ami az adatbázisokat gyorsnak tartja terhelés alatt, a lekérdezés optimalizálásának elsajátítása fontos, magas értékű senior tudás, amely megkülönbözteti azokat a fejlesztőket, akik képesek teljesítményes, skálázható adatbázis-vezérelt alkalmazásokat felépíteni — egy gyakran releváns, kritikus készség és gyakori interjúkérdés az adatbázis teljesítményéhez.