MySQL teszt indexelés

**Janko** · 2013-11-27, 15:01

Sziasztok!

Csináltam pár mysql index tesztet amiről azt gondolom érdemes lehet megosztani itt a fórumon.
Mivel elég hosszú is, meg az érthetőség miatt is négy bejegyzésben teszem közzé.

MySQL verzió 5.6.13
Tábla InnoDB, 1M sorral (teszt tábla így csak én használom egy szálon, viszont a szerver éles de unatkozik)
Lényegs mezők status tinyint(1) 2-3 eltérő adattal, cimg tinyint(1) 2-3 eltérő adattal, rnd_nr1 int(10) mind egyedi (timestamp), rnd_nr2 int(10) mind egyedi (timestamp)
Az "A" és "B" teszteket PHP-ból egy lapról futtattam...

**Janko** · 2013-11-27, 15:01

A1.,
indexek: status, cimg
lekérés: WHERE status='1' AND cimg='1' AND rnd_nr1<='1385543001' ORDER BY rnd_nr1 DESC LIMIT 0,20
válasz: 2 másodperc körül mozog
eredmény: [select_type] => SIMPLE [type] => ref [possible_keys] => cimg,status [key] => cimg [key_len] => 2 [ref] => const [rows] => 494348 [Extra] => Using where; Using filesort

A2.,
indexek: status, cimg, rndnr1, rndnr2
lekérés: WHERE status='1' AND cimg='1' AND rnd_nr1<='1385543001' ORDER BY rnd_nr1 DESC LIMIT 0,20
válasz: 2 másodperc körül mozog ez is, bár talán picit jobb mint az előző
eredmény: [select_type] => SIMPLE [table] => offer_tub_page_tmp [type] => ref [possible_keys] => cimg,status,rndnr1 [key] => cimg [key_len] => 2 [7] => const [8] => 494348 [rows] => 494348 [Extra] => Using where; Using filesort

A3.,
indexek: status, cimg, rndnr1, rndnr2, status+cimg+rnd_nr1
lekérés: WHERE status='1' AND cimg='1' AND rnd_nr1<='1385543001' ORDER BY rnd_nr1 DESC LIMIT 0,20
válasz: 0.30 másodperc körül mozog! Tehát annak ellenére, hogy elvileg most is 494e sort kerül átnézésre, még is nagyon begyorsult!
eredmény: [select_type] => SIMPLE [table] => offer_tub_page_tmp [type] => range [possible_keys] => cimg,status,rndnr1,status-cimg-rndnr1 [key] => status-cimg-rndnr1 [key_len] => 8 [ref] => [rows] => 494348 [Extra] => Using where; Using index

A4.,
indexek: status, cimg, rndnr2, status+cimg+rnd_nr1
lekérés: WHERE status='1' AND cimg='1' AND rnd_nr1<='1385543001' ORDER BY rnd_nr1 DESC LIMIT 0,20
válasz: 0.30 másodperc körül mozog, tehát látszólag jelentős változás nem történt.
eredmény: [select_type] => SIMPLE [table] => offer_tub_page_tmp [type] => range [possible_keys] => cimg,status,status-cimg-rndnr1 [key] => status-cimg-rndnr1 [key_len] => 8 [ref] => [rows] => 494348 [Extra] => Using where; Using index

**Janko** · 2013-11-27, 15:02

B1.,
indexek: status, cimg
lekérés: WHERE status='1' AND cimg='1' AND rnd_nr1<='1385543001' ORDER BY rnd_nr2 DESC LIMIT 0,20
válasz: 2 másodperc körül mozog
eredmény: [select_type] => SIMPLE [table] => offer_tub_page_tmp [type] => ref [possible_keys] => cimg,status [key] => cimg [key_len] => 2 [ref] => const [rows] => 494348 [Extra] => Using where; Using filesort

B2.,
indexek: status, cimg, rndnr1, rndnr2
lekérés: WHERE status='1' AND cimg='1' AND rnd_nr1<='1385543001' ORDER BY rnd_nr2 DESC LIMIT 0,20
válasz: 0.00 másodperc (valalhol 0.005 körül), mondhatni iszonyatosan begyorsult
eredmény: [select_type] => SIMPLE [table] => offer_tub_page_tmp [type] => index [possible_keys] => cimg,status,rndnr1 [key] => rndnr2 [key_len] => 4 [ref] => [rows] => 40 [Extra] => Using where

B3.,
indexek: status, cimg, rndnr1, rndnr2, status+cimg+rnd_nr1
lekérés: WHERE status='1' AND cimg='1' AND rnd_nr1<='1385543001' ORDER BY rnd_nr2 DESC LIMIT 0,20
válasz: 0.00 másodperc (valalhol 0.001 körül), tehát még az előzőhöz képest is begyorsult
eredmény: [select_type] => SIMPLE [table] => offer_tub_page_tmp [type] => index [possible_keys] => cimg,status,rndnr1,status-cimg-rndnr1 [key] => rndnr2 [key_len] => 4 [ref] => [rows] => 40 [Extra] => Using where

B4.,
indexek: status, cimg, rndnr2, status+cimg+rnd_nr1
lekérés: WHERE status='1' AND cimg='1' AND rnd_nr1<='1385543001' ORDER BY rnd_nr2 DESC LIMIT 0,20
válasz: 0.00 másodperc hasonlóan mint az előző teszt esetén.
eredmény: [select_type] => SIMPLE [table] => offer_tub_page_tmp [type] => index [possible_keys] => cimg,status,status-cimg-rndnr1 [key] => rndnr2 [key_len] => 4 [ref] => [rows] => 40 [Extra] => Using where

**Janko** · 2013-11-27, 15:03

Persze hozzá kell tennem, hogy nem laboratóriumi körülmények között történt a mérés, bár ennek ellenére jól láthatóak az eredmények.
A másik amire figyelni kell, hogy ha a táblába sok írás, felülírás történik akkor ezeknél a műveleteknél az indexek lassulást okozhatnak.
De az igazi lényeg, hogy érdemes időt szánni a helyes indexelés kiválasztására és tesztelésére mielőtt vasat cserélnél...

Egyébként meg ha egyik táblából másik tábla időszakonként sok adatot kell átmásolni, és persze lehetséges is, akkor nagyon érdemes a 'LOAD DATA LOCAL INFILE' lehetőséget választani, mert néhány órás írási időt néhány percre lehet csökkenteni. Különösen MyIsam tábla esetén a zárolás miatt...

**Geri** · 2013-11-27, 15:39

Kód:

WHERE status='1' AND cimg='1' AND rnd_nr1<='1385543001' ORDER BY rnd_nr2 DESC LIMIT 0,20

Ha jol gondolom akkor felesleges az idezojel, mert nem string tipusu adatokrol van szo.
Egyebkent kicsit osszevissza amit irsz, de ha jol latom A2 es B2 kozott a fo kulonbseg, hogy A2 nel ugyanazon a mezon van feltetel is meg rendezes is. Azt meg nem tudjuk hogy rnd_nr2 az mit tartalmaz, lehet sokkal rovidebb adatot mint rnd_nr1 ezert gyorsabb az azon torteno rendezes.
Azzal pedig hogy az indexek fontosak nem sok ujat irtal

.

**Janko** · 2013-11-27, 16:13

Geri, nem olvastad el az elejét...
Az elején kiderül, hogy az rnd_nr2...

Nem azt írtam, hogy fontosak vagy sem az indexek...
"De az igazi lényeg, hogy érdemes időt szánni a helyes indexelés kiválasztására és tesztelésére mielőtt vasat cserélnél..."

Egyébként meg az A teszt önmagában, vagy az A és B teszt együtt mond sokat, külön a B teszt nem is lehet igaz ezért írtam, hogy a kettőt egymás után egy lapon futtattan!

**Geri** · 2013-11-27, 16:20

Janko eredeti hozzászólása

Geri, nem olvastad el az elejét...
Az elején kiderül, hogy az rnd_nr2...

Jogos. Igy mar viszont tenyled erdemes lehagyni az idezojeleket, mert megsporolod a typecast-ot.

Janko eredeti hozzászólása

Nem azt írtam, hogy fontosak vagy sem az indexek...
"De az igazi lényeg, hogy érdemes időt szánni a helyes indexelés kiválasztására és tesztelésére mielőtt vasat cserélnél..."

Egyébként meg az A teszt önmagában, vagy az A és B teszt együtt mond sokat, külön a B teszt nem is lehet igaz ezért írtam, hogy a kettőt egymás után egy lapon futtattan!

Szerintem ha nem a lekerdezesnek megfelelo az indexeles akkor egyebkent is feleslegesen valtanal vasat, mert az nem biztos hogy segiteni fog. Nekem peldaul volt amikor a tobbmagos laptopomon bitang lassan futott egy lekerdezes egy hianyzo index miatt.

**Janko** · 2013-11-27, 17:40

Igazad van mert nem string...
Abban is igazad van, hogy ha nem a lekérdezésnek megfelelő az indexelés akkor tök mind1, hogy mit csinálsz. Viszont sokan vannak akik rádobnak mindenre indexet aztán búcsút intenek...

De amit írtam remélem abból érthető a kezdőknek is, hogy nagyon hálás tud lenni a rendszer, ha az indexek megfelelőek de ezt akkor lehet tudni, ha bizony a tesztelésre is ráfordítunk egy kis időt. Arról nem is beszélve, hogy sokszor csak az összevont indexelés ad normális eredményt, stb.

Egyébként azért merült fel bennem, hogy leírjam a teszt eredményeket mert a múltkor kerestem valamit aztán ide oda tévedve sok borzalmat olvastam a témáról, így arra gondoltam itt levezetve sokaknak kirajzolódik a megoldás. Jó, aki ma kezdte annak sokat nem mond...

**huntR** · 2013-11-27, 21:37

Nagy mennyiségű adat mozgatása esetén egyik táblából a másikba érdemes a történetet tranzakcióba tenni. A tranzakciókat egyébként is érdemes lehet használni, ahol kényes adatokkal dolgozunk. Nyújt adatvédelmet is, pl hiba esetén a tranzakció visszavonható, így nem történik meg, hogy 2-3 tábla összesen 20-30 rekordjából csak fele jön létre, és nem kell utólag adatbázist tisztogatni, illetve az adatírást gyorsítja, mert tranzakció közben blokkonként történik az írás, a hagyományos rekordonkénti helyett. Ha valaki erre akarja kihegyezni az adatbázisát, akkor kicsit belemélyedve a beállításokba a blokkok mérete optimálisan összehangolható a megengedett memória mérettel, így még nyerve néhány % teljesítményt.

Illetve ha több táblából gyakran kérdezünk azonos adatszerkezettel, akkor érdemes lehet nézettáblát (view) létrehozni erre a szerkezetre, és az érintett táblákat erre indexelni.

Másik trükk még a triggerek használata lehet karöltve un temp táblákkal, bár ez a beszúrást lassítja, de az adatelérést gyorsíthatja. Pl insertre teszünk egy triggert, ami fizikai adattáblába ugyancsak elhelyezi a gyakran használt mezők tartalmát (lényegében 1 insert helyett 2 történik), és a temp táblát indexeljük és használjuk lekérdezésre. Az még jobb, ha a bonyolult kapcsolatú lekérdezéseket tároljuk így, bár akkor már kiváltható a trigger és használhatunk eleve nagyobb mezőszámú táblákat. Minél kevesebb kapcsolat van egy lekérdezésben, annál gyorsabb lehet. Pl egy számlázó rendszer számla adattáblájába nem csak a vevő egyedi azonosítóját tároljuk, hanem minden adatát ami amúgy is meg kell jelenjen a számlán (név, cím, adósz, stb..), így egy kapcsolt lekérdezés helyett egy egyenes query-vel megkapjuk egy számla adatait.