db tervezés: sok feltétel, sok adat, lapozás, szűrés és a helyes módszerek
Adott pl. egy http://hasznaltauto.hu mint példa oldal, ahol milliónyi feltétel alapján lehet keresni, szűrni.
Engem olyan db szerkezetre való ajánlások és módszerek érdekelnének (mysql), amellyel egy ilyen komoly kereső is megvalósítható és nem utolsó sorban hatékonyan, gyorsan működtethető.
Például: egy autó adatainak tárolásánál:
pl. egy autóhoz, különböző (több "szintű") bővíthető számú, egyedi tulajdonság lesz definiálva:
fényezés/metálfény,
fényezés/kék szín,
papírok/érvényes forgalmi,
kiegészítők/ablakemelő,
klíma/automatikus/2 zónás
váltó/automatikus/4 sebességes,
állapot/normál/jó állapot
állapot/normál/újszerű állapot
stb.
Kb. ez lenne a db szerkezet ami elsőre beugrik:
"auto" tábla:
auto_id, nev,..
tulajdonsagok tábla:
tul_id, metalfeny..
auto_tulajdonsagai tábla_
auto_id, tul_id...
A keresőnél pedig: az adatbázisból azon autókat kérdezzük le, amelyeknél a tul_id=.. OR
tul_id=.. OR ...
Hogyha nagyon sok feltétellel keresünk (és persze sok táblakapcsolat is van a normálformák miatt), akkor az egy nagyobb adatbázisnál - helyes indexelés mellett is - totál megöli a mysql-t és így az oldalt is.
Pláne ha a találatokat 100-150 db. OR és AND feltétellel tűzdelem tele..
Megaztán ezt minden egyes keresésnél futtassam le? (napi 10.000 látogató nem sok, de ilyen lekérdezésnél már prolbémát okozhat).
És akkor még bonyolítja a dolgot, hogy jó lenne tudni: hogy hány darab találat lesz (pl lapozáshoz).
De ahogy néztem, a fenti oldalt, azt is kiírja - nagyon gyorsan - hogy hány találat várható.
Szóval milyen javaslatok vannak az ilyen összetett keresőt és az adatbázist illetően? (adatbázistervezés szempontból, de ha van egyéb tipp és trükk, az is érdekelne)
Láttam pl. valahol olyat is, hogy az utolsó X lekérdezés részleteit és az eredményét is egy külön táblába ideiglenesen eltárolták.. ez nagyon hekmányolás, vagy ez egy bevett módszer?
Köszi a válaszokat!
■ Engem olyan db szerkezetre való ajánlások és módszerek érdekelnének (mysql), amellyel egy ilyen komoly kereső is megvalósítható és nem utolsó sorban hatékonyan, gyorsan működtethető.
Például: egy autó adatainak tárolásánál:
pl. egy autóhoz, különböző (több "szintű") bővíthető számú, egyedi tulajdonság lesz definiálva:
fényezés/metálfény,
fényezés/kék szín,
papírok/érvényes forgalmi,
kiegészítők/ablakemelő,
klíma/automatikus/2 zónás
váltó/automatikus/4 sebességes,
állapot/normál/jó állapot
állapot/normál/újszerű állapot
stb.
Kb. ez lenne a db szerkezet ami elsőre beugrik:
"auto" tábla:
auto_id, nev,..
tulajdonsagok tábla:
tul_id, metalfeny..
auto_tulajdonsagai tábla_
auto_id, tul_id...
A keresőnél pedig: az adatbázisból azon autókat kérdezzük le, amelyeknél a tul_id=.. OR
tul_id=.. OR ...
Hogyha nagyon sok feltétellel keresünk (és persze sok táblakapcsolat is van a normálformák miatt), akkor az egy nagyobb adatbázisnál - helyes indexelés mellett is - totál megöli a mysql-t és így az oldalt is.
Pláne ha a találatokat 100-150 db. OR és AND feltétellel tűzdelem tele..
Megaztán ezt minden egyes keresésnél futtassam le? (napi 10.000 látogató nem sok, de ilyen lekérdezésnél már prolbémát okozhat).
És akkor még bonyolítja a dolgot, hogy jó lenne tudni: hogy hány darab találat lesz (pl lapozáshoz).
De ahogy néztem, a fenti oldalt, azt is kiírja - nagyon gyorsan - hogy hány találat várható.
Szóval milyen javaslatok vannak az ilyen összetett keresőt és az adatbázist illetően? (adatbázistervezés szempontból, de ha van egyéb tipp és trükk, az is érdekelne)
Láttam pl. valahol olyat is, hogy az utolsó X lekérdezés részleteit és az eredményét is egy külön táblába ideiglenesen eltárolták.. ez nagyon hekmányolás, vagy ez egy bevett módszer?
Köszi a válaszokat!
még eszembe jutott
auto_id,
..,
kepek
ahol a kepek rekord így néz ki:
('kep01.jpg|kep02.jpg|kep03.jpg')
ezzel kiváltva +a join-t.. ez így megoldás lehet, vagy sos felejtsem el? (vagy attól függ? :) )
Megadtad a helyes választ (is):
Ha "olcsón" ki lehet váltani egy-egy join-t, akkor - szerintem - érdemes. Legalábbis erre láttam példát a Drupal adatbázisában (is), és azt sok jóképességű ember fejleszti.
Ahhoz, hogy konkrét javaslatokat tudjunk tenni adatbázisodra, ismernünk kéne pontosan a feladatot, el kéne kezdened megtervezni és leírni ide.
Ezt a képes dolgot én lehet, hogy könyvtárral csinálnám: pl. az auto_kepek mappán belül auto_id nevű mappában vannak egy autó képei. A táblában két oszlop: fo_kep és konyvtar. A főkép ugye az, amelyik a sok autó listázásakor kell (itt teljes URL-t mentenék), a könyvtár pedig csak akkor, amikor egy autó részleteit jelenítem meg. Ekkor "van időm" PHP-val kiolvasni a könyvtárból azt a kb. max. 20 fájlt. Persze itt egy csomó (megjelenési) dolgot én magamtól feltételeztem, meg a hasznaltauto.hu kinézetéből.
Szerk.: Ez a "kep1.jpg|..." tulajdonképpen (majdnem) szerializált adat, ezek közt nehezebb keresni. Akkor (ott) használd, ha nem nagyon kell keresni közte.
Keresés
Érdekes még, hogy milyen táblatípust és mezőtípusokat használsz. A MySql-táblákat még nem ismerem annyira fullosan, de általánosságban igaz, hogy a fix (bájt)méretű mezők (oszlopok) nagyságrendekkel gyorsabban indexelhetők és kereshetők, mint a blob típusú / dinamikus méretezésűek. Tehát nagyjából a VARCHAR, INT, DATE/TIME, stb. gyors; ...TEXT és társai lassú.
Ha komolyabb rekordszám lesz egy-egy táblában (>=10^6..9), akkor alaposan nézz utána a táblatípus-leírásoknak, indexelésükkel együtt! (Extrém esetben egy helytelenül indexelt nagy rekordszámú táblában maga az index lassíthatja, tönkreteheti a keresést.)
Fontos lesz még, hogy milyen memóriakapacitása van a MySql szerverednek.
Az ideiglenes tábláknak nagy szerepük lehet, akár cache-ként felhasználva (napi 10000 látogatónál van rá esély), akár bonyolultabb lekérdezések könnyítésére/gyorsítására.
köszönöm!
Ami furcsa ezekben, hogy anno amikor sql-t tanítottak, akkor a normálformáknál azt hangsúlyozták hogy "ezt mindenképp így kell csinálni, ha törik ha szakad" és nem azt, hogy "arra kell törekedni".
Persze lehet, hogy egy általános órán nem mennek ilyen "mélységekig" éshát - tisztelet a kivételnek - lehet hogy a tanár sem találkozott még éles környezetben futó adatbázissal.
Valahogy mindig olyan ez, mintha "laboratóriumi körülmények közt" lennénk, aztán amikor jön a gyakorlat és egy 2 GByte-nyi "memória-heggyel felvértezett" mysql szerveren kell gazdálkodni 150.000 user folyton változó adataival, akkor hirtelen minden addig kőbevésett dolgot újra át kell gondolni..
Ilyenkor hekmányolás és lapátolás érzésem van, amit nagyon nem szeretek.
Szerintem kevés olyan oktatás vagy könyv van, amit az alap és a tényleges gyakorlati szint közé lehetne helyezni. (Persze lehet, hogy csak én nem találkoztam vele, úh inkább ezt visszavonom és nem általánosítok)
Az egyik ilyen finomság az "sql teljesítménynövelés" könyv, a másik lehetőség pedig ezen fórumokon való kitárgyalás. (Bár utóbbinál nagyon sok "érdekes" megoldás születik)
Iskola - élet
A tanárokat én nem hibáztatom, az ő feladatuk elsősorban az elméleti alapok megtanítása, ebbe nem fér bele az elvileg helytelen, a valóságban viszont szükséges megoldások tömkelege. Nem is lehet mindenre előre felkészülni/-készíteni, ezeket a "hekmányolásokat" az adott helyzet, feladat szabja meg. (Nem is hívnám kókánynak, ha kellően körültekintően jársz el.)
Ezt a könyvet nem olvastam, de a WL szerintem jobb, mert többféle ötletet láthatsz, még egymásnak ellentmondót is. Ha ezt ki tudod válogatni (mért ne tudnád), akkor kevesebb idő alatt jóval több hasznos infód lesz.
Ami furcsa ezekben, hogy anno
Ilyenkor érdemes utánanézni, hogy az oktató adatbázisok tervezésével kereste-e korábban a kenyerét, vagy pl. azzal, hogy okos cikkeket publikált adatbáziselméleti folyóiratokba.
Ha jól emlékszem egyetemi
A másik pedig, hogy ha teljesítménynövelésről van szó, akkor bizony normálformát kell sérteni, pl. ha egy fórumtopikokat listázó aloldalra oda akarod tenni, hogy egyik-másikban mennyi hozzászólás van, akkor nem biztos, hogy az a legjobb, ha minden lekérdezésnél végigszámolod a comment táblát is a topic tábla mellett. Ilyenkor (mivel a hozzászólás posztolása lényegesen ritkább feladat), inkább teszel egy countert a topic táblába, és azt módosítod, amikor a comment tábla ide vonatkozóan változik.
De biztos van még ezer olyan példa, amikor a redundancia elvárás (pl. a webshop rendeléseinél nem hivatkozhatsz meg egy usert egyszerűen user_id-vel, mert mi van, ha már minden adatát megváltoztatta, esetleg kitörölted/módosíottad a terméket, amit rendelt?)
Első?
Lehet, hogy rögtön az elsőt, miszerint "Az egymáshoz kapcsolódó adatok minden halmazához hozzon létre külön táblát."
Röhej, hogy már ez is csak copy-paste-tel megy :(
varchar?
Nem
Ha lesz erőm, megpróbálok utánanézni
* - ez sok esetben jelentős helyspórolással jár, viszont egy a méretét gyakran növelő mező esetében nem igazán előnyös, mert az update-kor át kell helyezni a sort esetleg egy másik adatblokkba, ami eléggé lelassíthatja a rendszert. (de rég volt... :-(((( és hogy élveztem, hogy rajtam kívül nem nagyon ismert ilyen részleteket senki a kollégák közül! :-) )
Annyira én sem
- Olvastam, hogy többnyire az alapértelmezett táblatípusnak a myisam-et szokták beállítani.
- Ez a tábla tudtommal mindenestül egy adat és egy (több?) indexfájl. (És egy adatbázis = egy könyvtár.) Nem vagyok biztos benne, én nem otthon vagyok, a könyvem viszont igen...:)
- De (majdnem) biztos vagyok abban, hogy a ...TEXT mezők ugyanabban a fájlban tárolódnak myisam-ben. Ez amiatt jó, hogy egyszerű fájlmásolással költöztethető a db. Amiatt rossz, hogy nagyon nagyra tud nőni egy tábla.
- Ma már - ha a memória mérete engedi - az adatbázisszerver betölti a teljes táblát indexszel együtt, és "egy ideig" bent is tartja, hátha kell még. Így nem sok felentősége van már - szerintem - a külön tárolt BLOB-nak.
Feladtam.5.5-től InnoDB a
5.5-től InnoDB a default.
Hogy az adatbázis hány fájlban tárolódik, annak a világon semmi köze ahhoz, hogy a TEXT/LOB/stb. adatokat fizikailag a sorban tárolja-e vagy valahol elkülönítve és a sorba csak egy pointert tesz a helyére.
A külön tárolt nagyméretű adatoknak van pl. egy olyan jelentősége is, hogy overheadekkel együtt max. 32K lehet egy sor hossza. Lehet pl. két 30K-s varchar-od, de ha mindkettőt max.-ra kitöltenéd, akkor hibát kapsz, míg ha az egyikben csak néhány karakter van, akkor a másikba bele fog férni a 30K-nyi szöveg. Viszont ha TEXT-et v. LOB-t használsz, akkor a hivatkozásuk elfoglal (ex has) 4 byte-ot és maga az adat lehet akár több megás is (úgy emlékszem, ezekre a típusokra nem vonatkozik a 32K-s limit)
Kb. ennyi, amit majdnem biztosan tudok a témáról.
update:
Innen.
Mondhatni, szomorú vagyok. Csalódtam az InnoDB-ben. :-)
update2: lehet, hogy mégsem az InnoDB-ben csalódtam, hanem magamban? A "trailing spaces" vajon mit jelent ebben az esetben? Ha explicite odaírom a sor végére a szóközöket? Vagy úgy általában, ha rövidebb lenne a mező tartalma a maximumnál, akkor is megtartja a max. hosszúságot?
Lehet, hogy újra el kellene olvasnom azt az írást...
Denormalizálás
Az egyes hirdetesekhez alapvetoen ketszer tarolja az oldal az adatokat, egyreszrol van egy szepen normalizalt tablarendszer (hirdetestipusokkal, azokhoz rendelt propertykkel, property ertekekkel...) es van egy nagy kover "kereso" tabla ahol minden lehetseges tulajdonsagnak van egy oszlopa (legyen az boolean/int/varchar...).
A tabla joreszt csupa NULL -okkal van tele (ertelemszeruen bicikli hirdetesek nemnagyon tartalmazanak motorra vonatkozo adatokat). Ha egyszer megvannak a talalatok akkor a kapott hirdetes azonositokat memcache -be kerulnek az adott kereso kombinaciohoz igy a lapozashoz mar nem kell ujra megepiteni a query -t es lefuttatni (bar elsore is ~200ms jelenlegi adathalmaz mellett is)
Hogy a modszer "helyes" -e mint olyan, nehezen tudnam megmondani, adatbazis tervezest oktato tanaraim bizonyara felhuznak a szemoldokuket egesz a holdig ennek lattan, cserebe elfogadhatoan mukodo megoldas a rendelkezesre allo eszkozokkel.
Mindenesetre ha nem ragaszkodsz mysql -hez akkor ilyen "faceted search" feladatokra erdemes lehet megismerkedni Solr -al.
"Like!"
Ezzel még nem találkoztam, de kifejezetten érdekes.
Mint valaki már említette,
A gyakori kereséseket érdemes cache-elni valami DB-nél gyorsabb helyen (tipikusan memcached); az utolsó X keresést cache-elni kevésbé tűnik praktikusnak.