Fő tartalom
Tantárgy/kurzus: Programozás > 3. témakör
3. lecke: Relációs lekérdezések SQL-ben- Adatok felosztása kapcsolódó táblákra
- Kapcsolódó táblák összekapcsolása JOIN-nal
- Feladat: Bobby hobbijai
- Kapcsolódó táblák összekapcsolása baloldali külső összekapcsolással (LEFT OUTER JOIN)
- Feladat: Ügyfelek megrendelései
- Táblák önmagukkal való összekapcsolása „self-join”-nal
- Feladat: Filmek SQL-ben
- Többszörös összekapcsolások kombinálása
- Feladat: BarátKönyv
- Projekt: Híres emberek
- Hatékonyabb SQL, lekérdezések tervezésével és optimalizálásával
© 2024 Khan AcademyFelhasználási feltételekAdatkezelési tájékoztatóSüti figyelmeztetés
Hatékonyabb SQL, lekérdezések tervezésével és optimalizálásával
Most, hogy már sokféle módon le tudod kérdezni az adataidat, és elkezdted használni a
SELECT-et több táblára vonatkozóan is, itt az idő, hogy szót ejtsünk az SQL lekérdezések hatékonyságáról – arról, hogy milyen gyorsan futnak le, és hogyan futhatnának még gyorsabban.Az SQL egy deklaratív nyelv – minden lekérdezés deklarálja, hogy mit hajtson végre az SQL motor, de nem mondja meg, hogy hogyan hajtsa ezt végre. Viszont a hogyan – a „terv” – az, ami befolyásolja a lekérdezések hatékonyságát, ezért ez elég fontos.
Miért szükséges az SQL lekérdezéseket megtervezni?
Tekintsük példaként ezt az egyszerű lekérdezést:
SELECT * FROM books WHERE author = "J K Rowling";Eszerint a lekérdezés szerint 2 különböző módon adhatja meg az SQL a lekérdezés eredményét:
- „Teljes tábla átvizsgálása”: a tábla minden egyes sorát megnézi, és visszatér az illeszkedő sorokkal.
- „Index” készítése: egy másolatot készít a tábláról a szerzők (author) szerint rendezve, és egy bináris keresés segítségével megkeresi azokat a sorokat, ahol a szerző "J K Rowling". Ezután megkeresi az ennek megfelelő azonosítókat (ID), majd egy másik bináris keresés segítségével visszaadja az eredeti táblából a megfelelő sorokat, amik az adott azonosítóhoz tartoznak.
Vajon melyik gyorsabb? Ez függ az adattól is és attól is, hogy a lekérdezés milyen gyakran hajtódik végre. Ha a táblának csak 10 sora van, akkor a teljes átvizsgálás csak 10 sor átnézését jelenti. Ilyenkor az első változat is hatékony.
Ha a táblában 10 millió sor van, akkor a teljes tábla átvizsgálása azt jelenti, hogy mind a 10 millió sort át kell nézni. Ez sokkal gyorsabban végrehajtható bináris kereséssel a sorbarendezett táblán - csak 23 keresési lépésre van szükségünk, hogy megtaláljuk a megfelelő értéket a 10 millió sorból. Viszont maga a rendezett tábla elkészítése is időbe telik (~230 millió művelet az SQL motortól függően). Ha a lekérdezésünket sokszor meghívjuk (23-nál többször), vagy ha már elkészítettük ezt a táblát, akkor a második változat lesz a gyorsabb.
Hogyan dönti el az SQL motor, hogy melyik változatot hajtsa végre? Erről még nem beszéltünk, pedig elég fontos kérdés, viszont eddig a lekérdezések szintaxisára összpontosítottunk, nem az implementációikra. Ahogyan egyre profibbá válsz az SQL használatában nagy adatbázisokon, a tervezési lépés egyre fontosabbá válik.
Az SQL lekérdezés folyamata
Elképzelhetjük úgy, hogy az SQL motor az alábbi lépéseket járja végig minden egyes lekérdezésnél, amit kiadunk:
- A lekérdezés-feldolgozó megbizonyosodik róla, hogy a lekérdezés szintaktikailag megfelelő (pl. vesszők nincsenek rossz helyen), szemantikailag korrekt (pl. a táblák léteznek), és hibával tér vissza, ha nem. Ha megfelelő, akkor algebrai kifejezéssé alakítja, és átadja a következő lépésnek.
- A lekérdezés-tervező és optimalizáló felelős a kemény, fejtörő munkáért. Elsőként minden egyértelmű optimalizálást elvégez (olyan javításokat, amik mindig jobb teljesítményt eredményeznek, például egyszerűsíti az 5*10-et 50-re). Ezután különböző „lekérdezés-terveket” vesz figyelembe, amik különbözhetnek optimalizálás szempontjából, megbecsüli mindegyik terv költségét (CPU és idő) az érintett táblák sorainak száma alapján, majd kiválasztja az optimális tervet és átadja a következő lépésnek.
- A lekérdezés-végrehajtó fogja a tervet és lefordítja adatbázis műveletekké, majd visszaadja az eredményt, ha van.
Hol kerülünk képbe mi, emberek?
Leélheted az egész életedet SQL lekérdezések kiadásával úgy, hogy észre sem veszed, hogy a lekérdezés-tervezés és optimalizálás minden egyes lekérdezéskor megtörténik. Viszont amint elkezdesz nagyobb adatbázisokkal dolgozni, elkezdesz majd foglalkozni a lekérdezéseid gyorsaságával is, és lehet, hogy érdekelni fog, tudod-e valahogy növelni a lekérdezéseid hatékonyságát.
Sok esetben – főleg összetett lekérdezéseknél – valóban magad is tudod optimalizálni a lekérdezést, ezt hívjuk „lekérdezés (finom)hangolás”-nak.
Első lépésként válaszd ki, melyik lekérdezést érdemes finomhangolni. SQL profiler segítségével például meg fogod találni azt a lekérdezést, ami a leghosszabb ideig fut, vagy a legtöbb erőforrást használja. Olykor magad fedezel fel egy rosszul teljesítő lekérdezést, miután annak futása annyi ideig tart, hogy az egész adatbázisod leáll. Remélhetőleg azért még a leállás előtt megtalálod.
A következő lépés, hogy megpróbálod megérteni, hogy az SQL motor hogyan hajt végre egy lekérdezést. Minden SQL rendszernek van egy módja arra, hogyan szólítja meg a motort. SQLite-ban még SQL kódolás megkezdése előtt támaszkodhatsz az
EXPLAIN QUERY PLAN oldalra, ami megmutatja, hogy mi zajlik a háttérben. Ha ezt használod, készülj fel, hogy mélységében meg kell ismerned az EXPLAIN QUERY PLAN referenciát, mert a „magyarázat” eléggé részletes és implementációfüggő is. Ha más SQL motort keresel, keress rá arra, hogyan kaphatod meg a végrehajtási tervet X-ben, angolul „how do I get an execution plan in X”.És most jön a nehéz rész: manuális optimalizálás a végrehajtási terv feljavításának érdekében. Ez a rész sokszor függ az általad használt SQL motor és a saját adataid sajátosságaitól.
Például emlékszel a lekérdezésre, amit fent használtunk? Ha korábban tudtuk volna, hogy több száz lekérdezést szeretnék
WHERE megszorításokkal az author oszlopra, akkor expliciten készíthettünk volna indexet a CREATE INDEX segítségével. Így az SQL motor képes lett volna hatékonyan használni ezt az indexet, hogy megtalálja a megfelelő sorokat. Olvasd el az SQLite query planning útmutatót, amiben megtudhatod, hogyan segíthetnek az indexek.Az indexek létrehozása gyakran hatékonyabbá teszi az ismétlődő lekérdezéseket. De van számos más megközelítés is. Többet tudhatsz meg az SQLite-ről a query planner overview oldalon, ahol érdemes alaposan megnézni a „manual” fejezeteket.
Lehetetlen a lekérdezés optimalizálás minden komplexitását és finomhangolását lefednünk itt, ezért azt javaslom, hogy amikor szükséged lesz rá, nézz utána a részleteknek.
(Itt találsz részleteket különböző SQL lekérdezés-tervezőkről, amik szerintem érdekesek: SQL Server Query Optimizer, Oracle SQL Tuning, MSSQL Execution Plan Basics)
Szeretnél részt venni a beszélgetésben?
Még nincs hozzászólás.