tutorials.hu

Kezdőlap Blogok Fórum Kapcsolat

Belépés

E-mail cím:

Jelszó:



Regisztráció
Elfelejtett jelszó

Új tutorial feltöltése ebbe a ketegóriába!

Tutorial feltöltése ebbe a kategóriába

SQL tutorial


MySQL lekérdezések optimalizálása

Beküdte: aty | 2009-02-17 16:51:35 | hozzászólások (0)
Megtekintve 87526 alkalommal
Értékelés: | értékelje ezt a tutorialt

MySQL adatbázisok optimalizálása.


Az optimális teljesítmény elérése több összetevő együttes meglétéhez kötött.
Az adatbázis motor megfelelő konfigurálása, az adott operációs rendszer, és összeállított hardware tulajdonságainak figyelembevételével
Az adattáblák struktúrájának megfelelő szervezésével (mezők, kapcsolatok, összefüggések, indexek)
Az SQL lekérdezések legoptimálisabb kialakításával.

A fentiek közül az első témára egy későbbi MySQL tutorialban fogok visszatérni

Miket vegyünk figyelembe az indexek, illetve a táblák összeállításánál?
Fontos tudni, hogy milyen műveletek lesznek túlsúlyban? Lekérdezések, új sorok beszúrása.

Néhány fontos ismeret, mielőtt rátérnék a részletekre. Az adatbázis motorok az adatokat tárolhatják egy, vagy táblánként több adatfájlban. Az adatok tárolása általában fizikálisan rendezetlenül történik. Az adatok gyors elérése értekében úgynevezett indexeket használnak, melyek gyakorlatilag keresőfák, (B-TREE), menyek blokkokban már logikailag rendezetten tartják nyilván adattáblánk mezőit, esetleg általunk definiált kifejezések alapján is tárolhatnak adatokat.
Amikor a táblánkba egy új sort szúrunk be, akkor a sor beszúrásakor, de a sor törlésekor is az adatbázismotor karbantartja az indexeket. Eltávolítja, beszúrja az index blokkokba a megfelelő bejegyzéseket, szükség esetén szétosztja, összevonja a blokkokat.
Egy későbbi leírásban, amennyiben érdeklődésre tart számot, majd ismertetem az indexelés (B-TREE) menetét.
Amit most tudni kell, hogy az inzertálások, és sorok törlése annál hosszabb ideig fog tartani, minél több indexet kell karbantartani, viszont a szükséges indexek hiánya pedig lassíthatja a kereséseket, eredmények kinyerését az adatbázisból.
Tehát az első fontos tényező, hogy mindig a szükséges indexeket hozzuk csak létre, feleslegeseket ne, de ügyeljünk a szükséges indexek jelenlétére is.
Az adatbázismotor, amikor egy lekérdező (SQL) parancsot kap, először értelmezi azt, eldönti, hogy milyen módon, mely indexek használatával fogja kinyerni az adatokat.
Ilyenkor fontos, hogy mennyi rekord nyerhető ki index segítségével, és mennyi az amelyet szekvenciálisan –sorról sorra kell átnéznie a motornak.
Az adatbázis motorok az ORDER BY parancs kiadása esetén sorba rendezést is végeznek. Ez esetben, ha hiányzik a megfelelő index, ez azt eredményezi, hogy a motor ideiglenes adattáblát hoz létre, melyet sorba rendez. Ez jelentős idő igénybevétel, és szerverterhelés, amennyiben a kinyert rekordok száma sok. Viszont amennyiben az eredménytábla egy pár rekord, elképzelhető, hogy jobb, ha nem használunk indexet az adott sorrend eléréséhez.

Nézzünk példákat:

Az alábbi tábla egy hírlevél feliratkozásait tartalmazza:
CREATE TABLE `hirlevel` (
`ID` int(11) NOT NULL auto_increment,
`ENEV` varchar(35) collate latin2_hungarian_ci default NULL,
`UNEV` varchar(35) collate latin2_hungarian_ci default NULL,
`EMAIL` varchar(60) collate latin2_hungarian_ci default NULL,
`regdate` timestamp NOT NULL default CURRENT_TIMESTAMP,
`rnr` int(11) default NULL,
`active` char(1) collate latin2_hungarian_ci default NULL,
PRIMARY KEY (`ID`),
KEY `EMAIL` (`EMAIL`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2 COLLATE=latin2_hungarian_ci

A táblában található egy elsődleges kulcs (ID)
Indexelve van az EMAIL cím, mivel a leiratkozáskor az email cím megadásával kezdeményezi az ügyfél a törlést. Ez esetben, hogy a motornak ne kelljen sorról sorra végignézni a rekordokat, amíg a keresett email címhez nem ér, indexeltük a táblát.

Az alábbi SQL kifejezés megadja a keresett ügyfél egyedi azonosítóját:
select ID from hirlevel where email="attila@domainem.hu"

A lekérdezést tudjuk elemezni a MySQL Explain parancs segítségével:
EXPLAIN
select ID from hirlevel where email="attila@domain.hu"


Ez esetben kapunk egy elemzést, hogy a MySQL motor hogyan értelmezi a parancsunkat, milyen módon, milyen index használatával kapunk eredményt.
Ennek az elemzésével , és szükség esetén a struktúra, és indexek változtatásával be tudjuk állítani, hogy a motor a lehető legkevesebb erőfeszítés árán adja vissza a kívánt eredményt.
Tovább szintén fontos megjegyezni, hogy az erőforrásokkal való takarékosság érdekében célszerű csak azokat a mezőket lekérdezni , melyeket valóban fel akarunk használni.

A fenti EXPLAIN parancs eredménye:

Select Type: SIMPLE
Table: hirlevel
Type: ref
Possible Keys: EMAIL
Keys: EMAIL
Key Len: 63
Ref: Const
Rows 1
Extra Using where


Ezek közül a legfontosabbak:
Possible Keys (A lehetséges indexek)
Keys (Az indexek (vagy csak 1) amit valójában felhasznál
KeyLen (Az index hossza)
Rows (Sorok száma a lekérdezésben)
Extra (Extra információk: Ez nagyon fontos lehet)

Nézzük meg, hogy a motor azokat az indexeket használja e, melyeket terveztünk a lekérdezés számára!
Ellenőrizzük, hogy a (Rows) mennyi sorra hivatkozik. Ha itt túl nagy szám található, az azt jelenti, hogy a motornak sok sort kell átvizsgálnia az eredmény eléréséhez, és ezt feltételezhetően nem megfelelő index, vagy hiányzó index okozza)
Az Extra tartalmazza, hogy készült e ideiglenes tábla a lekérdezés folyamán (Using temporary), vagy esetleg a sorba rendezést indexek figyelembe vétele nélkül végezte (Using fileshort)

A fenti két üzenet abban az esetben, ha a rows értéke nagy, mindenképp odafigyelést igényel.

Továbbá komplex lekérdezések esetén, amikor több feltétel szerint is szűrünk, lehetséges kombinált indexeket készíteni a gyorsabb elérés céljából.
Pl: van egy táblánk, ahol az iskola diákjai vannak nyilvántartva.
Van egy OSZTALY, (osztály), és egy NYELV (az osztály több csoportban tanulja az idegen nyelvet), mező.
Szeretnénk lekérdezni, hogy a 5a osztály angol nyelvű csoportjában kik járnak:
Select * from suli where OSZTALY=”5a” and NYELV=”Angol”

Az alábbi index a legcélravezetőbb, mivel pontosan illeszkedik a lekérdezéshez

KEY `MYKEY` (`OSZTALY`,`NYELV`)

Így a lekérdezés nem fog szekvenciális vizsgálatot végrehajtani, és egy nagyobb adathalmazból sorról sorra kiválogatni a megfelelő rekordokat. Az index gyakorlatilag egy blokkban tartalmazza az adatokat.

Persze a fenti példa csak illusztráció, mert lehetséges, hogy pár 10 hallgató lehet 1-1 nyelvi csoportban, amely nem teszi szükségessé a kombinált indexek alkalmazását, hiszen a MySQL EXPLAIN parancsánál a Row érték elenyésző rekord mennyiséget mutat. Ez a módszer elsősorban nagy adatmennyiség esetén célravezető. A kombinált indexek viszont nagyobb mértékben növelik az insert, illetve delete műveletek végrahajtási idejét, mint a hagyományos indexek.

Ez a tutorial alapokban ismertette az adatbázisok indexeinek optimalizálását. Még rengeteg „trükk” van, és maguknak a tábláknak a felépítése, és azok közötti relációs kapcsolatok elemzése is sokat nyom a latban.
Hozzászólások:



Szólj te is hozzá:
Beceneved:


Hozzászólásod:


Megjegyzés

A beküldött tutorialok taralmáért felelősséget az oldal szerkesztője nem vállal



Hirdetések


Photoshop, Flash, CSS, JavaScript, HTML, Delphi, C++, SQL, Egyéb segédletek gyűjteménye
Copyright © O-Soft Bt. Készítette, és az oldalt fenntartja O-Soft Bt.