MySQL mindenkinek 7

MySQLAz 5. részben előkerültek az indexek témája, de akkor elfutottunk előlük. Mostanra elég bátorságot szedtünk össze, hogy szembenézzünk velük és rabszolgamunkára kényszerítsük őket. Az indexek igen hasznos segédeszközök gyakorlatilag elvétve akadnak olyan helyzetek amikor ne használnák őket.

Az indexek a keresések lefuttatásakor lesznek hasznosak. Az indexek létrehozásával a lekérdezéseket gyorsíthatjuk meg. Nem akarok itt semmiféle technikai részletekbe bonyolódni, lényeg, hogy ahogyan egy tartalomjegyzék vagy egy szójegyzék segít nekünk egy könyvben megtalálni amit keresünk úgy az indexek az SQL szervernek segítenek megtalálni amit szeretnénk.

Primary key

Valójában már használtunk indexeket, ugyanis a primary key mezők automatikusan indexelésre kerülnek. Az 5. részben az alábbi példánál megígértük, hogy visszatérünk egy kérdésre. Ott azt mondtuk, hogy ha kíváncsiak vagyunk arra, hogy ki jár az 1/A osztályba, akkor azt kétfélekéépen is megtehetjük.

  1. SELECT *
    	FROM diakok, osztalyok, diak_osztaly
    	WHERE diakok.id = diak_osztaly.diakok_id
    	AND osztalyok.id = diak_osztaly.osztalyok_id
    	AND osztalyok.id = 1
    
  2. SELECT *
    	FROM diakok, osztalyok, diak_osztaly
    	WHERE diakok.id = diak_osztaly.diakok_id
    	AND osztalyok.id = diak_osztaly.osztalyok_id
    	AND osztalyok.osztaly = '1/A'
    

A kettő közül az első megoldást javasoltuk, mivel ott a WHERE feltétel utolsó sorában az osztály id-jét keressük, míg a második példában az osztály nevét keressük. Az osztály id-t primary key-nek jelöltük, tehát automatikusan létrejön hozzá az index. Ezért a két lekérdezés közül az első jóval gyorsabban fog lefutni, legalábbis nagyobb rekordszám esetében. Kis rekordszámnál az indexek valójában lassíthatják a keresést. Általánosságban a rekordszám növekedni fog szóval többnyire érdemes lesz indexeket használni.

Az indexek ára

Az indexek létrehozását úgy is elképzelhetjük, hogy az adattáblánk mellett létrejön egy másik, láthatatlan tábla amely a “szójegyzéket” azaz az indexet tartalmazza. Ez azzal jár, hoyg az INSERT, UPDATE és DELETE utasítások lassabban fognak végrehajtódni, mivel ezek befolyásolják sz index táblát is. Cserébe a SELECT utasítások úgy fognak pörögni mint mókus a kerékben.

Mit indexeljünk és mit ne?

Ezért érdemes megfontolni, hogy mit indexeljünk és mit nem. Az olyan táblákat amelyeken sokszor több adatrögzítési feladatot hajtunk végre mint adat lekérésit – azaz több INSERT lesz mint SELECT – nem feltétlenül a legoptimálisabb indexelni. Például egy oldal látogatási statisztika esetében mondjuk az az elvárás, hogy a felhasználók minden mozdulatát rögzítsük és havonta egyszer gyártsunk belőle statisztikákat jobban járunk ha nem indexelünk. Ha a statisztikát 5 percenként le akarjuk kérni új adatokkal, akkor pedig biztosan indexelni fogunk.

Indexelni a táblánk azon mezőit érdemes amelyek ó eséllyel szerepelni fognak a lekérdezések WHERE feltételeiben. (Sejtitek márt a primary key miért indexelődik automatikusan?)

A szintaxis

Indexeket kétféle módon hozhatunk létre. Egyrészt definiálhatjuk őket a táblák létrehozásakor, ekkor az index egyből létrejön és kezdettől fogva él.

CREATE TABLE `diakok` (
  `id` smallint(5) unsigned NOT NULL auto_increment,
  `diak` varchar(45) collate utf8_hungarian_ci NOT NULL default '0',
  `nem` tinyint(1) NOT NULL COMMENT '0:férfi, 1:nő',
  PRIMARY KEY  (`id`),
  KEY `nem` (`nem`)
) ENGINE=MyISAM;

Amint látjuk a szintaxis egyszerű, a KEY kulcsszó, aztán a kulcs neve ami lehet bármi, majd a mező neve amire létre kell hozni az indexet.

A másik változatot már létező, indexeletlen tábla esetében alkalmazzuk.

ALTER TABLE `diakok` ADD INDEX `nem` (`nem`)

Az ALTER TABLE utasítás igazi többfunkciós svájci bicska, a későbbiekben még lesz szó róla. Az index létrehozásáról az ADD INDEX utasítás gondoskodik, majd megadjuk az index nevét és az indexelndő mező nevét. Létezik még egy CREATE INDEX utasítás is, de ez valójában ennek az ALTER TABLE ADD INDEX utasításnak egy álneve.

Ha szeretnénk a meglévő adatbázisunkat feltölteni a nemre vonatkozó adatokkal, akkor ahhoz itt egy update script.

Többes indexek

Egy táblán tetszőleges számú indexet létrehozhatunk. Lehetőség van arra is, hogy az egy indexbe ne egy mezőt, hanem többet vonjunk be.

ALTER TABLE `osztalyok` ADD INDEX `ofo_o` (`osztalyfonok_id`, `osztaly`)

Ezt az indexet akkor fogja az SQL szerver használni ha a SELECT WHERE feltételében ezek a mezők és ebben a sorrendben szerepelnek ha szerepelnek. Azaz a következő esetekben használva lesznek:

...
WHERE osztalyfonok_id = 3
AND osztaly = '3/A'
...
WHERE osztalyfonok_id = 3

Ezzel szemben ha csak az osztály szerepel a feltételben akkor az index nem lesz használható.

2 thoughts on “MySQL mindenkinek 7

    • @wram70 Az index azt mondja meg, hogy először eszerint aztán eszerint. Persze az adatbázis motorkat fejlesztik. Csinálj egy próbát és mérd le az eredményt

Vélemény, hozzászólás?

Az email címet nem tesszük közzé. A kötelező mezőket * karakterrel jelöljük.