Eredmény: 1 - 9 (9) összesen

Téma: MySQL teszt indexelés

  1. #1
    Bölcs
    Csatlakozott
    11-12-21
    Hozzászólás
    699
    Begyűjtött 107 köszönetet
    94 hozzászólásával

    Alapbeállítás MySQL teszt indexelés

    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...



  2. #2
    Bölcs
    Csatlakozott
    11-12-21
    Hozzászólás
    699
    Begyűjtött 107 köszönetet
    94 hozzászólásával

    Alapbeállítás re: MySQL teszt indexelés

    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



  3. #3
    Bölcs
    Csatlakozott
    11-12-21
    Hozzászólás
    699
    Begyűjtött 107 köszönetet
    94 hozzászólásával

    Alapbeállítás re: MySQL teszt indexelés

    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



  4. #4
    Bölcs
    Csatlakozott
    11-12-21
    Hozzászólás
    699
    Begyűjtött 107 köszönetet
    94 hozzászólásával

    Alapbeállítás re: MySQL teszt indexelés

    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...



  5. #5
    Rubyist Geri logója
    Csatlakozott
    07-12-15
    Hely
    \x90
    Hozzászólás
    5.442
    Begyűjtött 1.107 köszönetet
    678 hozzászólásával

    Alapbeállítás re: MySQL teszt indexelés

    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 .


    If debugging is the process of removing software bugs, then programming must be the process of putting them in.
    Github | Ruby tutorial
    Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
    Respect all, fear none

  6. #6
    Bölcs
    Csatlakozott
    11-12-21
    Hozzászólás
    699
    Begyűjtött 107 köszönetet
    94 hozzászólásával

    Alapbeállítás re: MySQL teszt indexelés

    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!



  7. #7
    Rubyist Geri logója
    Csatlakozott
    07-12-15
    Hely
    \x90
    Hozzászólás
    5.442
    Begyűjtött 1.107 köszönetet
    678 hozzászólásával

    Alapbeállítás re: MySQL teszt indexelés

    Idézet Janko eredeti hozzászólása Hozzászólás megtekintése
    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.

    Idézet Janko eredeti hozzászólása Hozzászólás megtekintése
    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.



  8. #8
    Bölcs
    Csatlakozott
    11-12-21
    Hozzászólás
    699
    Begyűjtött 107 köszönetet
    94 hozzászólásával

    Alapbeállítás re: MySQL teszt indexelés

    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...



  9. Az alábbi felhasználók hálásak a válaszért:

    Geri (2013-11-27)

  10. #9
    Tag
    Csatlakozott
    12-10-03
    Hozzászólás
    98
    Begyűjtött 40 köszönetet
    34 hozzászólásával

    Alapbeállítás re: MySQL teszt indexelés

    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.



Könyvjelzők

Hozzászólás szabályai

  • Új témákat nem hozhatsz létre
  • Válaszokat nem küldhetsz
  • Fájlokat nem csatolhatsz
  • A hozzászólásaidat nem módosíthatod
  •