1. Melyik a jó plan? Ha használ vagy ha nem használ indexet a DB.

Egy egy összetettebb lekérdezés esetén a keletkezett plan lekérdezésekor felmerül a kérdés: vajon miért nem használta az adatbázis valamelyik indexet, amelynek használatát mi logikusnak, sebességgyorsítónak érezzük.

De hát a DB motor nem biztos, hogy a mi gondolatmenetünket követi, néhol természetesen azért, mert kevesebbet tud, mint az SQL parancsokat osztogató ember. De gyakran azért, mert a DB motort létrehozó programozók komoly munkabefektetéssel többet tudnak a kérdésről és ennek a tudásnak egy részét sikeresen beépítik az adatbázis motorba.


A kérdés megválaszolásához alapvető dolog tudni néhány alapinformációt.

Az adatbázisok hatékonyságának a leggyengébb pontja a merevlemez olvasása/írása. Itt is két fontos adat van, az adatátvitel sebessége, ami egymás melletti szektorok (tipikusan egy sáv) memóriába olvasásának időigénye illetve a fej pozicionálás, vagyis a merevlemez szektorai közötti váltás időigénye. Egyrészt a szükséges sávra kell lépni, azon belül pedig kivárni amíg a lemez odafordul, hogy kiolvashassuk a sáv adott szektorát.

A leggyengébb láncszem, mint látható a lemezpozicionálás.


Mit tesz az adatbáziskezelő egy tábla teljes végigolvasásakor és index mentén keresés esetén?

Egy tábla végigolvasásakor nem érdekes a rekord sorrend betartása, így a tábla rekordjain fizikai sorrendben lépked végig, vagyis a fejmozgatás közeli, jó esetben egymás melletti sávokat érint és gyakran a sávok teljes beolvasott adata hasznos információ, a táblához kapcsolódó rekordokat tartalmaz. Kevés a fejmozgatás és a felesleges I/O.

Index melletti olvasás esetén a fej ugrál az indextábla szektorjai és a tárolt adatrekordok fizikai szektorai között. Még az indextábla is (tipikusan fa szerkezet) széttöredezett, több részlet közötti ugrálással olvasható, de a hivatkozott rekordok (index sorrend) gyakorlatilag a teljes adatterületen szétszórva, tipikusan egymástól messze, sokszor egy-egy lemezsávon csak egy-egy szektort érintve helyezkednek el. Az index sorrend szerinti adatelérés tehát nem túl hatékony. Bár nehéz megtippelni (mert sok és esetenként egyedi, eltérő paraméterek alapján kalkulálható, és inkább csak valószínűsíthető az eredmény is), de általánosságban pl. a rekordok 5-10%-ánál többet érintő adatelérés esetén nem célszerű az indexek használata még akkor sem, ha a lekérdezés eredménye valamely index mellett szűrhető vagy olyan sorrendben kell az eredmény.



  1. Használjuk-e a plan-t SQL utasításban?


Hát részemről nem ajánlom!

Tapasztalataim szerint a plan inkább csak az SQL parancshoz képzett adatelérési mód lekérdezésére való, vagyis az optimalizáló ellenőrzésére. Mert igaz a régi mondás, miszerint a számítógép nem azt csinálja amit szeretnénk tőle, hanem azt, amire utasítottuk. Hiába szeretnénk, hogy az optimalizáló 'okosabb' legyen és kitalálja gondolatainkat, ez nem megy.

A számítógép már csak olyan, hogy a programozónak kell alkalmazkodnia a lehetőségekhez és a lehetséges mozgástérben a legjobb eredményt előállítani. Az optimalizáló meg olyan, hogy csak a beleprogramozott emberi intelligenciát képes visszaadni, vagyis dőreség többet elvárni, mint amire képes.


Ezek szerint mégiscsak a plan az egyetlen lehetőség ahhoz, hogy az SQL parancs olyan módon olvassa az adatainkat, ahogy a rendezettségek alapján tervezzük?

Nem. Jól kell megfogalmazni az SQL parancsot. Ha segítünk az SQL parsernek és optimalizálónak, azzal hogy úgy írjuk meg a parancsunkat, hogy abból könnyen értelmezhető legyen az adatelérési tervünk is, akkor az Interbase/Firebird képes lesz megérteni a szándékunkat;)

Erre egy példa. Ha van egy személyzeti adatbázisunk és mondjuk a TAJ szám a személy azonosító kulcsa és erre van indexünk is, akkor a lekérdezés where tagjának helyes megválasztásával segíthetünk az optimalizálónak.


Helytelen parancs:

> select TAJ,NEV,NEME from SZEMELYZET where NEME = 'F' and '1234567' = TAJ

Helyes parancs:

> select TAJ,NEV,NEME from SZEMELYZET where TAJ = '1234567' and NEME = 'F'


Természetesen a fenti egy egyszerűsített esetre példa, vagyis az optimalizáló nagy valószínűséggel a helytelennek minősített parancsból is kihámozza, hogy a TAJ indexet kell használni a gyorsabb lekérdezéshez, de az elv a fontos. Ha összetett a where, akkor a megcélzott rendezettség mezőjének használata legyen az első a szűrési sorrendben, sőt, összetett indexek használata esetén a szűrésben a mezők sorrendje is legyen azonos a rendezési kulcsban felsorolt sorrenddel.

Természetesen számos (sőt elképzelhetetlenül sok esetet) lehetne végiggondolni, de a cél nem a mechanikus gondolkodás, hanem hogy programozók lévén képzeljük bele magunkat az optimalizálót író programozó lehetőségeibe és a parancs megadásakor gondoljuk végig, vajon mit csinálhat az adatbázis ennek hatására, vagyis empátia és tolerancia kell (mint ahogy a világ számos más területén ezekből kellene jóval több) valamint körültekintő tervezés, átgondolni a feladatot. Én fejben lemodellezem, hogy én hogyan gyűjteném ki a szükséges adatokat (milyen algoritmusokat, milyen ideiglenes belső táblákat használnák) egy adott lekérdezéshez és ha jó az adatbázis kezelő tulajdonságairól az elképzelésem, jó modellt állítottam össze, akkor a lekérdezés működőképes és a parancs hatékony lehet.


Akkor most miért nem jobb a plan megadása?

A plan használatától azért idegenkedek, mert hibalehetőséget visz be a programba. Ha elrontjuk a plan parancsot, akkor az egész SQL futtatását utasítja vissza az adatbázis szerver, tehát egy hatékonyság probléma helyett egy nem működő rendszert kapunk. Ez szerintem túl nagy ár!

Másrészt egy jól megírt és belőtt rendszer alatt is előfordulhat, hogy egy-egy indexet (még ha csak technikai okokból is, pl. karbantartásnál) kikapcsolunk vagy eldobunk, akkor a plan érvénytelen lesz és a rendszer válik üzemképtelenné (ismét csak némi hatékonyság romlás helyett).


(c) eMeL * utolsó módosítás: 2003.10.04