[es] - Problem optimizacije, gotovo optimalnog upita ;-)

BigFoot
Boban Jovanović
Arilje

Član broj: 1098
Poruke: 991
*.dynamic.sbb.co.yu.

+35 Profil

Problem optimizacije, gotovo optimalnog upita ;-)

^{22.01.2008. u 11:53 - pre 210 meseci}

Postoje 2 tabele, korisnici i izveštaji. Korisnici dnevno, nijednom ili više puta šalju svoje izveštaje. Treba mi tabela poslednjih primljenih izveštaja po korisnicima, u zadatom željenom vremenskom intervalu, tipa: Daj mi izveštaje od prvih 100 aktivnih korisnika koji su primljeni danas od 17 do 19h.

Napravio sam upit koji to radi, a radi jako brzo iz tabele izvštaja sa preko milion slogova, medjutim... EXPLAIN daje sledeće:

Code:
id select_type  table     type  possible_keys      key      key_len   ref           rows Extra
1   PRIMARY       L       ref   KorisnikID,Aktivan Aktivan     1     const          144  Using where; Using temporary; Using filesort
1   PRIMARY   <derived2>  ALL   NULL               NULL       NULL   NULL           182
2   DERIVED   <derived3>  ALL   NULL               NULL       NULL   NULL           182
2   DERIVED       S      eq_ref PRIMARY            PRIMARY     4     M.MaxIzvestajID  1
3   DERIVED   izvestaji  range  DatumVreme         DatumVreme  8     NULL           388  Using where; Using temporary; Using filesort

Upit je:

Code:
SELECT L.KorisnikID, L.Mesto, L.Naziv, R.IzvestajID, R.Uplata, R.Isplata, R.DatumVreme
FROM korisnici L LEFT OUTER JOIN
    (SELECT IzvestajID, Uplata, Isplata, DatumVreme, KorisnikID
     FROM izvestaji S INNER JOIN
        (SELECT MAX(IzvestajID) AS MaxIzvestajID
         FROM izvestaji
         WHERE DatumVreme BETWEEN '2008-01-15 07:00' AND '2008-01-15 23:59'
         GROUP BY KorisnikID) M
     ON S.IzvestajID = M.MaxIzvestajID) R
ON L.KorisnikID = R.KorisnikID
WHERE L.KorisnikID BETWEEN 1 AND 100 AND L.Aktivan = 1
ORDER BY L.Mesto, L.Naziv

Problem je što ovaj upit, iako radi i radi brzo, uvećava MySQL Select_full_join vrednost, za koju u opisu piše:

Select_full_join - The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.

Za tabelu korisnici imam indekse po KorisnikID (PK), Mesto, Naziv, Aktivan, a za tabelu izveštaji po IzvestajID (PK), DatumVreme, KorisnikID. Pretpostavljam da se iz istog razloga uvećavaju i Handler_read_rnd i Handler_read_rnd_next, a svi indeksi su sigurno dobri. Šta može biti problem ili problema uopšte i nema?

Two beer or not two beer...

Odgovor na temu

stsung
NS

Član broj: 12899
Poruke: 432
213.244.208.*

+3 Profil

Re: Problem optimizacije, gotovo optimalnog upita ;-)

^{24.01.2008. u 04:34 - pre 210 meseci}

Pozd.

Imajuci u vidu postavku zadatka kako si naveo, ovo shto si napisao je previshe zakomplikovano. Prvo, logika upita je obrnuta - postavka je "daj mi izveshtaje", a ti na tabelu korisnika lepish tabelu izveshtaja. Naravno ovo mozhe ovako, ali generalno se treba pridrzhavati logike kod pisanja upita - upit predstavlja odgovor na postavljen problem, od pochetka do kraja. Pride, ovaj join je OUTER JOIN, chime cesh dobiti korisnike i koji nisu imali nikakav izveshtaj u tom periodu. Malo je nejasna postavka "od prvih 100 aktivnih korisnika" ... da li zhelish korisnike sa ID poljem od 1 do 100 kako si naveo u upitu (shta se deshava ako brishesh korisnike?), ili zhelish od svih korisnika koji su imali izveshtaj u tom periodu, prvih 100.

Kako nisi dao strukturu tabela, odokativno mogu da navedem recimo ovakav upit koji odgovara postavci:

Code:

SELECT k.KorisnikID, k.Mesto, k.Naziv, i.IzvestajID, i.Uplata, i.Isplata, i.DatumVreme
FROM izvestaji i
INNER JOIN korisnici k ON (k.KorisnikID=i.KorisnikID)
WHERE
k.KorisnikID BETWEEN 1 AND 100 AND k.Aktivan=1
and i.IzvestajID=(
    select MAX(IzvestajID) FROM izvestaji i2 WHERE
    i2.DatumVreme BETWEEN '2008-01-15 07:00:00' AND '2008-01-15 23:59:00'
    and i2.KorisnikID=k.KorisnikID
)
ORDER BY k.Mesto, k.Naziv

Ovde se naravno podrazumeva da je IzvestajID auto increment koji konstantno raste, pa time MAX() daje poslednji u tom periodu. Sa odgovarajucim indeksima (prema uslovima upita), mozhesh da izbegnesh kreiranje temp tabele, no zbog ordera mislim da se filesort ne mozhe izbeci.

Svako dobro.

Odgovor na temu

BigFoot
Boban Jovanović
Arilje

Član broj: 1098
Poruke: 991
77.46.200.*

+35 Profil

Re: Problem optimizacije, gotovo optimalnog upita ;-)

^{24.01.2008. u 12:33 - pre 210 meseci}

Citat:

stsung: postavka je "daj mi izveshtaje", a ti na tabelu korisnika lepish tabelu izveshtaja

Da, jer mi treba za svakog korisnika, imao on, ili nemao izveštaj. Zato se na tabelu korisnika lepi tabela izveštaja LEFT OUTER JOIN-om. One koji nisu dostavili izveštaj, npr. prikazujem drugom bojom.

Citat:

stsung: Malo je nejasna postavka "od prvih 100 aktivnih korisnika"

Misli se na opseg ID, npr. od 1 do 100, bez obzira koliko ih je. Ovo iz razloga što su ID preko toga rezervisani.
Iz struktura navedenih tabela izvučena su samo bitnija polja za razumevanje problema, a standardno su definisana, npr. svaki ID je uvek primarni ključ i auto_increment, imam indekse po poljima koja se javljaju u ON, WHERE, ORDER i sl.

Citat:

stsung: Sa odgovarajucim indeksima (prema uslovima upita), mozhesh da izbegnesh kreiranje temp tabele, no zbog ordera mislim da se filesort ne mozhe izbeci.

Indekse već imam, čak i za Naziv i Mesto, koji se koriste za ORDER. Zanimljivo da se potpuno isti rezultat dobija i kad izbacim ORDER. Šta god da sam probao, uvek dobijam 2 DERIVED podskupa sa type ALL...

Two beer or not two beer...

Odgovor na temu

stsung
NS

Član broj: 12899
Poruke: 432
213.244.209.*

+3 Profil

Re: Problem optimizacije, gotovo optimalnog upita ;-)

^{24.01.2008. u 14:21 - pre 210 meseci}

Pozd.

Rekao sam vec da si previshe zakomplikovao taj upit. Kako ti trebaju svi korisnici (ukljuchujuci i one koji nisu imali izveshtaj), samo je potrebno da promenish upit koji sam ti poslao, prema novoj postavci: Izlistaj prvih 100 korisnika sa njihovim najnovijim izveshtajem ako ga imaju u tom i tom periodu. Otprilike ovako:

Code:

SELECT k.KorisnikID, k.Mesto, k.Naziv, i.IzvestajID, i.Uplata, i.Isplata, i.DatumVreme
FROM korisnici k
LEFT OUTER JOIN izvestaji i ON (
         i.KorisnikID=k.KorisnikID
         AND i.IzvestajID=(
                    select MAX(IzvestajID) FROM izvestaji i2 WHERE
                    i2.DatumVreme BETWEEN '2008-01-15 07:00:00' AND '2008-01-15 23:59:00'
                    and i2.KorisnikID=k.KorisnikID)
)
WHERE
k.KorisnikID BETWEEN 1 AND 100 AND k.Aktivan=1
ORDER BY k.Mesto, k.Naziv

U vezi indeksa jedna bitna napomena: indeksi se prave prema tome kakav se upit koristi. Ne mora uopshte da znachi da ce indeks po samo jednom polju da ishta pomogne.

Svako dobro.

Odgovor na temu

BigFoot
Boban Jovanović
Arilje

Član broj: 1098
Poruke: 991
*.dynamic.sbb.co.yu.

+35 Profil

Re: Problem optimizacije, gotovo optimalnog upita ;-)

^{24.01.2008. u 16:10 - pre 210 meseci}

Citat:

stsung: Rekao sam vec da si previshe zakomplikovao taj upit.

Moguće

Ovo jeste jednostavniji upit, izvršava se pravilnije, vraća manje tmp slogova, krajnji rezultat je isti, ali sporije. Rezultati su sledeći:

• "Zakomplikovani" upit
Izvršava se prvi put za 0.016s, a svaki sledeći (zbog keširanja) za 0.0002s. Prema EXPLAIN koristi FULL JOIN što se smatra lošom organizacijom indeksa i povećava Select_full_join vrednost.

• "Nekomplikovani" upit
Izvršava se prvi za 0.96s, a svaki sledeći takodje za 0.96s. Ovo je EXPLAIN:

Code:
id select_type        table  type    possible_keys                key       key_len  ref            rows  Extra
1   PRIMARY             L    ref     KorisnikID,Aktivan        Aktivan        1      const           144  Using where; Using filesort
1   PRIMARY             R    eq_ref  PRIMARY,FK_KorisnikID     PRIMARY        4      func              1
2   DEPENDENT SUBQUERY  S    ref     DatumVreme,FK_KorisnikID  FK_KorisnikID  2      K.KorisnikID    210  Using where

Šta je sad bolje? Pravilnije i sporije, ili (možda) nepravilnije, a primetno brže? Kakav upit može objediniti ove dve stvari?

Citat:

stsung: indeksi se prave prema tome kakav se upit koristi

Naravno, ali u ovom slučaju svaki upit i podupit koristi elementarna polja, i uglavnom primarni ključ.

Two beer or not two beer...

Odgovor na temu

stsung
NS

Član broj: 12899
Poruke: 432
82.117.201.*

+3 Profil

Re: Problem optimizacije, gotovo optimalnog upita ;-)

^{24.01.2008. u 16:38 - pre 210 meseci}

Pozd.

Prvo, dependent subquery ne koristi indeks optimalno. Taj upit koristi relaciju 2 polja, a indeks ne postoji za ovu relaciju (KorisnikID, DatumVreme) vec imash pojedinachne indekse za svako polje.

Drugo, apsolutno mi je nejasan razlog kako kazhesh zashto se ne koristi cache kod drugog upita ? MySQL koristi cache kod identichnih upita, i ako nije bilo promene u tabelama. Ako se za prvi upit koristi cache a za drugi ne, ili nisi dobro obratio pazhnju, ili ti su cache parametri baze pogreshni. Ako postavish strukture obe tabele, mogu i sam pogledati tachno shta se deshava ako oba upita pustim da rade nad tabelama sa milion recorda.

Svako dobro.

P.S. Zaboravio sam dodati : takodje je apsolutno nejasno zashto ti se jedan upit kako kazhesh izvrshi za 0.016s a po explainu ima vishe redova za obradu od drugog - ispada da shto ima manje redova za obradu to upit duzhe traje, shto je nemoguce. Pride, zanimljivo je jako zashto ti upit koristi indeks po polju Aktivan a ne po KorisnikID, kad je ochigledno da u drugom sluchaju mora biti manje od 144 rezultata - za ovo treba uraditi jedan ANALYZE kako bi se kardinalnosti indeksa popravile, a i pride za ovaj takodje upit nemash optimalan indeks, jer koristish relaciju 2 polja a indekse imash pojedinachno za svako polje.

_{[Ovu poruku je menjao stsung dana 24.01.2008. u 17:59 GMT+1]}

Odgovor na temu

BigFoot
Boban Jovanović
Arilje

Član broj: 1098
Poruke: 991
*.dynamic.sbb.co.yu.

+35 Profil

Re: Problem optimizacije, gotovo optimalnog upita ;-)

^{24.01.2008. u 17:28 - pre 210 meseci}

Citat:

stsung: Taj upit koristi relaciju 2 polja, a indeks ne postoji za ovu relaciju (KorisnikID, DatumVreme)

Upravo sam probao i sa indeksom po 2 polja, i to za korisnike (KorisnikID, Aktivan), a za izveštaje (KorisnikID, DatumVreme) i opet je isto.

Citat:

stsung: Drugo, apsolutno mi je nejasan razlog kako kazhesh zashto se ne koristi cache kod drugog upita?

I meni je to čudno. Ne verujem da su podešavanja baze loša jer ovaj upit koristi manje slogova i lakše ga je keširati. Jedino vidim da je vreme izvršenja svaki put isto i po tome cenim da nije keširano. Dok kod originalnog upita, prvi put potraje 0.016s, ali svaki sledeći trenutno, a prikazuje 0.0002s.

Citat:

stsung: Ako postavish strukture obe tabele, mogu i sam pogledati tachno shta se deshava ako oba upita pustim da rade nad tabelama sa milion recorda.

Code:
CREATE TABLE `korisnici` (
  `KorisnikID` int unsigned NOT NULL auto_increment,
  `Naziv` varchar(32) collate ascii_bin NOT NULL,
  `Mesto` varchar(32) collate ascii_bin default NULL,
  `Aktivan` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`KorisnikID`),
  KEY `Mesto` (`Mesto`),
  KEY `Naziv` (`Naziv`),
  KEY `Aktivan` (`Aktivan`)
) ENGINE=InnoDB  DEFAULT CHARSET=ascii COLLATE=ascii_bin COMMENT='Podaci o korisnicima';

CREATE TABLE `izvestaji` (
  `IzvestajID` int unsigned NOT NULL auto_increment,
  `KorisnikID` int unsigned NOT NULL,
  `DatumVreme` datetime default NULL,
  `Uplata` double default NULL,
  `Isplata` double default NULL,
  PRIMARY KEY  (`IzvestajID`),
  KEY `DatumVreme` (`DatumVreme`),
  KEY `KorisnikID` (`KorisnikID`)
) ENGINE=InnoDB  DEFAULT CHARSET=ascii COLLATE=ascii_bin;

ALTER TABLE `izvestaji`
  ADD CONSTRAINT `FK_KorisnikID` FOREIGN KEY (`KorisnikID`) REFERENCES `korisinci` (`KorisnikID`) ON DELETE CASCADE ON UPDATE CASCADE;

U u principu funkcioniše zadovoljavajuće, ali me zainteresovalo zašto ne radi kako očekujem.

Citat:

stsung: po explainu ima vishe redova za obradu od drugog - ispada da shto ima manje redova za obradu to upit duzhe traje

Ne utiče na vreme rezultujući i broj medjuslogova, već i vreme za koje se do njih dodje. Pravilnom selekcijom ćeš pre dobiti 100.000 slogova, nego nepravilnom 1000.

Citat:

stsung: zanimljivo je jako zashto ti upit koristi indeks po polju Aktivan a ne po KorisnikID

Ovo je i meni posebno zanimljivo! Probao sam i eksplicitno da mu navedem USE INDEX (KorisnikID), ali su rezultati još gori. Onda dobijem 3 podupita sa type ALL. Radjen je ANALYZE, OPTIMIZE i sve deluje ok.

Two beer or not two beer...

Odgovor na temu

stsung
NS

Član broj: 12899
Poruke: 432
82.117.201.*

+3 Profil

Re: Problem optimizacije, gotovo optimalnog upita ;-)

^{24.01.2008. u 19:06 - pre 210 meseci}

Pozd.

Izgenerisao sam random koju stotinu hiljada korisnika i neshto vishe od milion izveshtaja.

EXPLAIN za tvoj upit:

Code:

1    PRIMARY    L                 range       PRIMARY,Aktivan   PRIMARY    4                                75    Using where; Using temporary; Using filesort
1    PRIMARY    <derived2>    ALL                                                                                 36796
2    DERIVED    <derived3>    ALL                                                                                  36796
2    DERIVED    S                 eq_ref   PRIMARY              PRIMARY         4  M.MaxIzvestajID   1
3    DERIVED    izvestaji        range    DatumVreme         DatumVreme    9                           65204 Using where; Using temporary; Using filesort

Jedva sam ovo izvukao, trajalo je jako dugo, shto je razumljivo.

EXPLAIN drugog upita:

Code:

1    PRIMARY    k    range    PRIMARY,Aktivan    PRIMARY    4        75    Using where; Using filesort
1    PRIMARY    i    eq_ref    PRIMARY,KorisnikID    PRIMARY    4    func    1
2    DEPENDENT SUBQUERY    i2    ref    DatumVreme,KorisnikID    KorisnikID    4    test.k.KorisnikID    7    Using where

Rezultat ovoga je bio u trenutku. Sad naravno, podaci su random, nisam imao predstavu otprilike kakav ti je raspored tj odnos izmedju aktivnih/neaktivnih korisnika, kao i uchestalost izveshtaja.

Kao shto vidish, u drugom upitu koristio je ipak primaran kljuch, jer je zakljuchio da je odnos broja podataka korishcenjem tog kljucha pogodniji nego da koristi kljuch po Aktivan. U svakom sluchaju, i dalje apsolutno ne vidim kako prvi query mozhe brzhe da se odvija od drugog - probaj da stavish malo veci vremenski interval pa testiraj tako.

Svako dobro.

Odgovor na temu

BigFoot
Boban Jovanović
Arilje

Član broj: 1098
Poruke: 991
*.dynamic.sbb.co.yu.

+35 Profil

Re: Problem optimizacije, gotovo optimalnog upita ;-)

^{24.01.2008. u 19:28 - pre 210 meseci}

Proširenjem opsega datuma, iako to za ovu primenu nije uobičajeno, već se izveštaji traže u nekom kraćem vremenskom periodu, a najviše jedan radni dan, dobio sam da je originalni upit sporiji (0.38s) jer jedan podupit vraća 118145 slogova, u odnosu na novi koji se sad izvršava za (0.26s). Novi upit, gotovo ne zavisi od opsega datuma, jer koristi indekse tako da je bolje rešenje. U mojem slučaju, još uvek se koristi indeks po Aktivan, ali to nije problem. Hvala na pomoći.

_{[Ovu poruku je menjao BigFoot dana 24.01.2008. u 20:45 GMT+1]}

Two beer or not two beer...

Odgovor na temu

BigFoot
Boban Jovanović
Arilje

Član broj: 1098
Poruke: 991
*.dynamic.sbb.co.yu.

+35 Profil

Re: Problem optimizacije, gotovo optimalnog upita ;-)

^{28.01.2008. u 13:35 - pre 209 meseci}

Ipak je originalni upit ostao kao bolje rešenje. Novi upit se najčešće izvršava 5-8s, a originalni nikad preko 0.1s, iako po EXPLAIN-u vraća više medjuslogova. Mislim da je problem u onom

Code:
...i.IzvestajID=(
                    select MAX(IzvestajID) FROM izvestaji i2 WHERE
                    i2.DatumVreme BETWEEN '2008-01-15 07:00:00' AND '2008-01-15 23:59:00'
                    and i2.KorisnikID=k.KorisnikID)

Ovde on troši puno vremena da za svaki i.IzvestajID uradi po jedan select MAX(), dok kod originalnog upita zbog INNER JOIN-a, odjednom izvuče podskup svih MAX(), grupisano po KorisnikID, iako veći od potrebnog, uspostavi relacije i zato brže pronadje potrebne slogove.
Ovo je primer kad teorijski bolje rešenje ne mora da bude i praktično bolje. Zanimljivo je jedino zašto?

Two beer or not two beer...

Odgovor na temu

stsung
NS

Član broj: 12899
Poruke: 432
82.117.201.*

+3 Profil

Re: Problem optimizacije, gotovo optimalnog upita ;-)

^{28.01.2008. u 20:01 - pre 209 meseci}

Pozd.

Odgovor na to pitanje treba potrazhiti u underline arhitekturi samog MySQL-a. Mozhesh eksperimentisati pa malo menjati ovo, tako shto cesh max() izvuci napolje, no poenta je da upit treba prilagoditi takodje i vrsti podataka koja se trazhi od njega. Kako ti je podskup podataka dovoljno mali u JOIN, MySQL uspeva brzhe da odradi posao nego u drugom upitu, no i sam si video kada bi se taj broj podataka povecao (odnosno kada bi povecao interval), vreme potrebno za izvrshavanje drastichno raste. Konkretno ja kada sam testirao vec sam hteo da ga nasilno prekinem jer je radio skoro 10 minuta, ali nad random podacima.

Svako dobro.

Odgovor na temu