9.3 Adatbázis lekérdezések optimalizálása

Áttekintő

Az adatbázis az alkalmazásod leglassabb pontja – nem a CPU, nem a memória, hanem a DB. Ez nem vélemény, hanem tapasztalat. A legtöbb teljesítményproblémát, amit juniorként okozol, nem rossz algoritmusból ered, hanem abból, hogy az adatbázist feleslegesen sokszor, feleslegesen nagy lekérdezéssel, vagy index nélkül kérdezed le.

Ez az alfejezet négy dolgot tanít meg:

Mi az az N+1 query és hogyan ismerd fel
Mit csinál az index és mikor segít valójában
Hogyan olvasd az EXPLAIN-t (minimálisan, de hasznosat)
Mi az eager vs. lazy loading különbség

Nem kell DBA-nak lenned. Kell viszont felismerni, amikor a kódod 1000 helyett 1 lekérdezéssel meg tudná oldani ugyanazt.

Részletes leírás

Az N+1 query probléma

Az N+1 query az egyik leggyakoribb performancia-bűn, amit junior fejlesztők elkövetnek – és a legsúlyosabb, mert észrevétlenül skálázódik a problémával.

A minta:

1. Lekérdezed az összes felhasználót (1 query)
2. Minden felhasználóhoz lekérdezed a rendeléseit (N query)
Összesen: N+1 query

Ha 10 felhasználód van: 11 query. Ha 1000 felhasználód van: 1001 query. Ha 10 000: 10 001. Linearisan nő.

Pszeudokód szemléltetés:

# ROSSZ – N+1 query
users = db.query("SELECT * FROM users")        # 1 query

for user in users:
    orders = db.query(                          # N query (minden userre)
        "SELECT * FROM orders WHERE user_id = ?", 
        user.id
    )
    print(f"{user.name}: {len(orders)} rendelés")

# JÓ – 2 query (vagy 1 JOIN-nal)
users = db.query("SELECT * FROM users")        # 1 query
orders = db.query("SELECT * FROM orders")      # 1 query

# Csoportosítás memóriában (Python dict/map)
orders_by_user = group_by(orders, key="user_id")

for user in users:
    user_orders = orders_by_user.get(user.id, [])
    print(f"{user.name}: {len(user_orders)} rendelés")

Miért nem veszed észre fejlesztés közben?

Lokálisan 5-10 rekordod van. Minden query 1-2ms. A fejlesztői gép gyors, a DB helyben fut. Majd production-ben 50 000 rekord esetén ugyanez a kód 50 001 query-t csinál, és a feature 30 másodpercig tölt.

Ez az, amit a "Measure first" elv megkövetel: néha érdemes logolni, hány query fut egy request alatt.

Index: mire való és mikor segít

Analógia: Egy könyvben keresni fejezet nélkül = az egész könyvet végig kell lapozni. Index = tartalomjegyzék. Az adatbázis index ugyanez: nem kell az egész táblát végigszkennelni, elég az indexet megnézni.

Mikor segít az index:

WHERE feltételben szereplő oszlopokon
JOIN feltételben szereplő oszlopokon (ON users.id = orders.user_id)
ORDER BY esetén (rendezés index nélkül = full scan + sort)

Mikor NEM segít (vagy árt):

Kis táblákon (100-200 sor) – a motor simán full scan-nel gyorsabb
Olyan oszlopokon, ahol szinte minden sor egyedi és ritkán keresünk rá
INSERT/UPDATE/DELETE műveleteknél – minden módosításkor az index is frissül, tehát írásnál lassít

Alapszabály juniorként:

Minden idegenkulcs (foreign key) oszlopra legyen index
Amire WHERE-ben szűrsz rendszeresen, arra legyen index
Ne indexelj mindent „hátha kell" alapon

-- Index létrehozása (szintaxis általánosan):
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_users_email ON users(email);

EXPLAIN / query plan alapszinten

Az EXPLAIN megmutatja, hogy az adatbázis motor hogyan tervezi végrehajtani a lekérdezésedet – mielőtt ténylegesen végrehajtaná.

Amit juniorként figyelj:

Kulcsszó / sor az EXPLAIN-ban	Mit jelent
`Seq Scan` / `ALL`	Full table scan – teljes táblát végigolvassa
`Index Scan` / `ref`	Index alapján keres – általában gyorsabb
`rows=10000`	Becsült sorok száma – ha sok, az gyanús
`cost=0.00..9999.99`	Becsült cost (relatív szám, nem ms)

PostgreSQL példa:

EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- Ha ezt látod: Seq Scan on orders (cost=0.00..1500.00 rows=5000)
-- → nincs index user_id-n, az egész táblát végigolvassa

-- Ha ezt látod: Index Scan using idx_orders_user_id (cost=0.10..8.50 rows=12)
-- → index megvan, csak a releváns sorokat olvassa

MySQL/MariaDB:

EXPLAIN SELECT * FROM orders WHERE user_id = 42;
-- Figyeld: `type` oszlop – "ALL" = teljes scan, "ref" = index használat

Mit NEM kell tudnod (junior szinten): query plan részletes értelmezése, hash join vs. nested loop döntések, parallell query tervezés. Ez DBA terep.

Eager loading vs. lazy loading

Ezek ORM (Object-Relational Mapper) fogalmak, de a koncepció DB szinten is értelmes.

Lazy loading: "Akkor kérem le az adatot, amikor szükségem van rá." Ez kényelmes, de N+1 query melegágya.

Eager loading: "Előre lekérem az összes szükséges adatot, mielőtt végigiterálnék."

# Laravel Eloquent pszeudokód

# LAZY LOADING – N+1 query
users = User.all()                          # 1 query
for user in users:
    print(user.orders)                      # query minden userre!

# EAGER LOADING – 2 query
users = User.with("orders").all()           # 1 query: users
                                            # 1 query: összes orders egyszerre
for user in users:
    print(user.orders)                      # memóriából, nincs új query

// Sequelize pszeudokód

// LAZY LOADING
const users = await User.findAll();
for (const user of users) {
    const orders = await user.getOrders();  // N query
}

// EAGER LOADING
const users = await User.findAll({
    include: [{ model: Order }]             // 1 join vagy 2 query, de nem N
});

Főszabály: Ha tudod, hogy szükséged lesz a kapcsolódó adatokra → eager loading. Ha biztosan csak az alap entitás kell (ritka eset) → lazy loading.

Mikor kerüljük a JOIN-t?

A JOIN alapvetően jó. De van, amikor nem érdemes:

JOIN kerülendő, ha:

Nagyon nagy táblák keresztszorzatát hozza létre (pl. CROSS JOIN véletlenül)
Az alkalmazáslogika szintjén olcsóbb összefésülni (pl. már memóriában van az adat)
A join-olt tábla ritkán változik és cache-elhető

JOIN rendben van, ha:

Normalizált adatstruktúra szükséges összefésülése
Az index megvan mindkét oldalon
Nem iteratív (nem loopon belüli) JOIN

-- JÓ JOIN – indexelt kulcsokon
SELECT users.name, orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id  -- index mindkét oldalon
WHERE orders.created_at > '2025-01-01';

-- KERÜLENDŐ – szűrő nélküli, nagy tábla
SELECT *
FROM products
JOIN categories ON products.category_id = categories.id
-- Ha mindkét tábla 100k sor és nincs szűrő → lassú lehet

Életszerű példák

1. példa: Az ismeretlen N+1

Egy junior egy belső admin felületet épít, ami listázza az összes projektet és mellette az hozzárendelt felhasználók számát. Locálisan 8 projekt van – gyors. Production-ben 3200 projekt – az oldal 45 másodpercig tölt, timeout-ol.

A probléma: for project in projects: count = db.count(WHERE project_id = project.id) – 3201 query.

A megoldás:

SELECT project_id, COUNT(*) as user_count
FROM project_users
GROUP BY project_id

Egy query. 45 másodpercből 0.3 másodperc lett.

2. példa: Az elfelejtett index

Egy API endpoint szűri a rendeléseket status = 'pending' alapján. Fejlesztés közben mindig gyors. Egy hónap múlva az ügyfélszolgálat jelenti, hogy lassú. Az orders tábla 800 000 sorba nőtt, a status oszlopon nincs index. Minden kérésre 800k sort olvas végig.

-- Diagnózis
EXPLAIN SELECT * FROM orders WHERE status = 'pending';
-- Seq Scan on orders (rows=800000) → baj

-- Fix
CREATE INDEX idx_orders_status ON orders(status);
-- Ezután: Index Scan (rows=1200) → rendben

3. példa: JOIN vs. alkalmazásszintű összefésülés

Egy fejlesztő minden API válaszhoz joinol egy translations táblát, ami 5 nyelv × N sor. A translation tábla szinte soha nem változik. Egy Redis cache-szel a translations tábla egyszer töltődik be, majd az alkalmazás memóriájában él – a JOIN eltűnik, az API 8x gyorsabb lesz.

Feladatok és tesztek

Példafeladat: N+1 azonosítása és javítása

Az alábbi pszeudokód N+1 query problémát tartalmaz. Azonosítsd a problémát, és írd át úgy, hogy legfeljebb 2 DB query fusson.

function getArticlesWithAuthors():
    articles = db.query("SELECT * FROM articles")   # 1 query
    
    result = []
    for article in articles:
        author = db.query(                           # N query
            "SELECT * FROM authors WHERE id = ?", 
            article.author_id
        )
        result.append({
            "title": article.title,
            "author": author.name
        })
    
    return result

Megoldás iránya: Kérd le az összes author-t egy query-vel, majd memóriában fésüld össze. Vagy használj JOIN-t.

Kvíz kérdések

1. Mi az N+1 query probléma lényege?

a) Az adatbázis csak N+1 kapcsolatot enged egyszerre
b) Egy lista lekérdezése után minden elemhez külön query fut
c) A JOIN operátor N+1-szer fut le
d) Az index N+1 sort olvas be

Helyes válasz: b

2. Melyik esetben NEM segít az index?

a) WHERE email = 'xyz@mail.com' lekérdezésnél
b) JOIN users ON orders.user_id = users.id esetén
c) 50 soros, ritkán lekérdezett táblán
d) ORDER BY created_at DESC esetén

Helyes válasz: c

3. Mit jelent az EXPLAIN output-ban a Seq Scan?

a) A lekérdezés párhuzamosan fut
b) Az adatbázis index nélkül végigolvassa az egész táblát
c) A lekérdezés sorrendben (sequence) visszaadja az adatot
d) A query plan optimalizált

Helyes válasz: b

4. Mi az eager loading előnye a lazy loadinghoz képest?

a) Mindig kevesebb memóriát használ
b) Előre betölti a kapcsolódó adatokat, elkerülve az N+1 query-t
c) Gyorsabb INSERT műveleteknél
d) Nem igényel index-et a foreign key oszlopokon

Helyes válasz: b

5. Melyik lekérdezési stratégia okozza leggyakrabban az N+1 problémát?

a) Eager loading
b) JOIN
c) Lazy loading
d) Indexelt keresés

Helyes válasz: c

6. Rangsorozd hatékonyság szerint (legjobbtól legrosszabbig) az alábbi megközelítéseket ugyanarra a feladatra: "Listázd az összes user nevét és rendeléseik számát"

A) SELECT users.name, COUNT(orders.id) FROM users LEFT JOIN orders ON users.id = orders.user_id GROUP BY users.id
B) SELECT * FROM users + minden userre: SELECT COUNT(*) FROM orders WHERE user_id = ?
C) SELECT * FROM users + SELECT * FROM orders + számolás memóriában

Helyes sorrend: A → C → B
(A: 1 optimalizált query | C: 2 query, memóriában összefésülés | B: N+1 query)

7. Az alábbi két lekérdezés közül melyik a hatékonyabb, és miért?

-- A verzió
SELECT * FROM products WHERE category_id = 5 AND price < 10000;

-- B verzió  
SELECT * FROM products WHERE price < 10000 AND category_id = 5;

a) A verzió, mert a szűrő sorrendje számít
b) B verzió, mert az olcsóbb feltétel hamarabb kizár sorokat
c) Ugyanolyan hatékonyak, az SQL motor optimalizálja a sorrendet
d) Attól függ, melyik oszlopon van index

Helyes válasz: c (Az SQL motor maga optimalizálja – a WHERE feltételek sorrendje nem befolyásolja a végrehajtási tervet)

8. Mikor érdemes KERÜLNI a JOIN használatát?

a) Ha mindkét táblán van index a join oszlopokon
b) Ha a join-olt tábla szinte soha nem változik és cache-elhető
c) Ha normalizált adatstruktúrát kérdezünk le
d) Ha az egyik tábla 1000 sorból áll

Helyes válasz: b

9. Mi történik, ha indexet adsz egy foreign key oszlophoz, amelyre rendszeresen JOIN-olsz?

a) A JOIN lassabb lesz, mert az index frissítése overhead-et okoz
b) Nincs változás, a JOIN nem használja az indexet
c) A JOIN gyorsabb lesz, mert az index alapján közvetlenül megtalálja az összetartozó sorokat
d) Az index csak WHERE feltételnél használható, JOIN-nál nem

Helyes válasz: c

10. Szituáció: Egy API endpoint 500ms helyett 8 másodpercig fut. Milyen sorrendben vizsgálod a problémát?

a) Azonnal hozzáadsz indexeket minden oszlophoz → újratesztelsz
b) EXPLAIN-nel megnézed a query plan-t → azonosítod a lassú pontot → célzott optimalizálás
c) Átírod a lekérdezést JOIN nélkülire → újratesztelsz
d) Cache-eled az egész endpoint-ot → kész

Helyes válasz: b (Mérj először, optimalizálj utána – lásd 9.1 alfejezet alapelve)