Adatbázis optimalizálás

**pestaa** · 2008-04-09, 13:20

Néhány alapvető trükk iránt érdeklődve Titeket is megkérdezlek ebben a témában.
Mire kell odafigyelni egy, elsősorban fórumként használt szoftver adatbázisának megalkotásakor? A MySQL égisze alatt mennyire érdemes a mezők típusára megszorításokat alkalmazni? (Érthetőség kedvéért álljon itt egy példa: a WordPress text mezőket használ a bejegyzések címeinek, holott a legtöbb a legtöbb esetben 255 karakter is elegendő lenne: tinytext.)
Megéri-e bizonyos fokon tárterületet feláldozni a lekérések egyszerűsíthetősége végett? (Redundáns tárolás a kevesebb táblában való szöszmötölésért.)
Minden apró érdemi információ érdekel.

Hasonló témák:

**melon** · 2008-04-09, 14:58

"Mire kell odafigyelni egy, elsősorban fórumként használt szoftver adatbázisának megalkotásakor?"
Szerintem legfőképp arra, hogy a szükséges táblák indexelve legyenek.

**Wolfy** · 2008-04-09, 16:44

pestaa eredeti hozzászólása

Néhány alapvető trükk iránt érdeklődve Titeket is megkérdezlek ebben a témában.
Mire kell odafigyelni egy, elsősorban fórumként használt szoftver adatbázisának megalkotásakor? A MySQL égisze alatt mennyire érdemes a mezők típusára megszorításokat alkalmazni? (Érthetőség kedvéért álljon itt egy példa: a WordPress text mezőket használ a bejegyzések címeinek, holott a legtöbb a legtöbb esetben 255 karakter is elegendő lenne: tinytext.)
Megéri-e bizonyos fokon tárterületet feláldozni a lekérések egyszerűsíthetősége végett? (Redundáns tárolás a kevesebb táblában való szöszmötölésért.)
Minden apró érdemi információ érdekel.

Szia

Én még soha nem csináltam fórumot

De sokat használok mysqlt sok userrel sok adattal. Nem szeretek pazarolni, ha úgy véled, hogy nagyon kevés esetben lesz 255 karakternél hosszabb cím (nemhiszem, hogy ekkora címet akar valaki írni) akkor nem kell. A redundás tárolás szerintem attól függ, hogy hány felhasználóra tervezed. Biztos van egy határ ami után már megéri, én inkább nem tárolnék redundásan (azt tanultam adatbázistervezés előadásokon, hogy amennyire lehet kerülni kell)

**pestaa** · 2008-04-09, 19:32

Tartok tőle, hogy a DbDesigner 4-em így is több indexet is készít, mint amennyire égetően szükségem van.

A redundáns tartalom alatt a következőt értem: a hozzászólások rekordjaiban eltárolom a hozzászóló felhasználónevét is, így nem kell a lekéréshez a tagok táblát is hozzácsatolni. Az IPB és más nagy nevek is hasonlóképpen csinálják. Kevés felhasználónál nem hátrányos, sok felhasználónál pedig inkább előnyös.

Ennél nagyobb mértékű redundanciát már nem tudok elképzelni. :P

**Zaphod** · 2008-04-09, 20:06

pestaa eredeti hozzászólása

Tartok tőle, hogy a DbDesigner 4-em így is több indexet is készít, mint amennyire égetően szükségem van.

A redundáns tartalom alatt a következőt értem: a hozzászólások rekordjaiban eltárolom a hozzászóló felhasználónevét is, így nem kell a lekéréshez a tagok táblát is hozzácsatolni. Az IPB és más nagy nevek is hasonlóképpen csinálják. Kevés felhasználónál nem hátrányos, sok felhasználónál pedig inkább előnyös.

Ennél nagyobb mértékű redundanciát már nem tudok elképzelni. :P

Igen. ez egy összetett gondolat.

Számtalanszor vertem már a fejem a falba, amikor ID alapján 2-3-4(néha több) egymásba ágyazott mysql select után tudtam kiiratni pl. egy kép címét, vagy usernevet. Javaslom tárold a hsz táblában a user nevét is. DE.

Ha jogot adsz a usernek arra, hogy megváltoztassa pl. a nick-jét, akkor ne felejtsd el végigpásztázni a teljes db összes tábláját, hogy hová is rögzítetted még azt a nevet.

Párszor meg ezért vertem már a fejem a falba. úgyhogy én maradtam az id alapú azonosításnál. sokszor bonyolultabb a lekérdezés, de az adatbiztonság sztem fontosabb. A scriptet meg egyszer kell megírni.

A mezők optimalizálásával eleinte sokat szöszöltem. pl. a dátum mindig varchar 10 volt. aztán később már az időpontot is rögzíteni akartam, meg lehet hogy nem számmal hanem kiírva a hónap, stb.. manapság csak az INT tipusú mezőket adom meg nagyjából méretre, a többi varchar meg 255

eddig nem vettem észre hátrányát, pedig van olyan table amiben a recordok száma jóval százezer fölötti.

Nemrég azt mondták rá, hogy a wc-re is table-val megyek. szóval én nem spórolok a db-n belüli táblákkal. Amit kell vagy csak esély van rá, hogy kellhet, azt rögzítem, lehetőség szerin külön táblában. Pár hónap/év múlva a programozókra jellemző roppant ,,beszédes,, mezőnevek miatt egy méretesebb táblánál fogalmad sem lesz, hogy mi micsoda.

**TLoF** · 2008-04-09, 23:37

Optimalizálás:

Jó kérdés. Csináltam az elmúlt években pár nagyon nagy forgalmú ***** site-ot, ott mindent feláldoztunk a sebesség oltárán.

Magyarán: Redundás adat tárolás jó, csak tudi kell fejben kezelni. Amit mondasz, hogy a scriptet egyszer kell megirni ez tökéletesen igaz. Ha szépen szét szeparálod a modell rétegedet az üzleti logikától soha nem kell foglalkoznod később azzal, hogy egy bizonyos adat hogyan került elő.

Az igaz, hogy a text tipusú adatokat a mysql külön kezeli a táblában csak hivatkozásokat helyez el ezekre a mezőkre. Ezáltal ha a where feltételben nem hivatkozol erre a mezőre, akkor csak a result-ban kellő sorok kerülnek felolvasásra, amivel diszk io -t lehet spórolni.

A dátumot unsigned(int) 10 -ben éri meg tárolni, unix_timestamp formában, igy később azt iratsz ki bellőle ami jólesik.

Ha nagyon szétdarabolod az adataidat akkor később a rengeteg join miat rengeteg cpu és memória megy el fölösben minden lekérdezésnél.

Az indexeknél megéri eljátszani a dologgal, ha csak pár jól definiálható query tipus fut a táblára megéri mindegyiknek indexet késziteni, hisz az index segit az olvasásban (ha legalább a találatok felét ki lehet zárni a segitségével) Viszont lassitja az irást.

100ezer sor nem sok. a mysql myisam táblái 3-4 millió sorig probléma mentesen müködnek, még full table scannél sem túl problémás a dolog.

**pestaa** · 2008-04-13, 16:38

Szóval a segítségetekkel összeállt kép a következő: találjam meg az arany utat az olvasás egyszerűsítésére használt redundancia és az írás egyszerűsítésére használt szétdaraboltság között. Mindezt valami jól dokumentált és pofonegyszerű logika mentén.

Továbbá ne legyek szűkmarkú a mezőtípusok kiválasztásánál, mert a sebesség nem ezen fog múlni.

A segítségeteket hálásan köszönöm, maroktartást megelőzendő mindkettőtöknek jár a hírnév.

Minekután a fejlesztői oldalon nem találtam egyértelmű magyarázatot, Hozzátok fordulok ismét: milyen különbség van a char(255), varchar(255) és a tinytext között? Miért másabb az, ha a char/varchar típusokat binárisan tárolom? Amennyit megértettem, az az, hogy a char mezőt a mysql feltölti nullás bájtokkal, így még véletlenül sem tud kevesebb helyet foglalni.
Végül pedig mivel másabb egy full text index egy egyszerű indexnél?

Máris indítok Google-keresést ezekre, de bízom benne, hogy Ti is hasznos információval láttok majd el.

**TLoF** · 2008-04-13, 20:55

A fórumnál azért az irás kevesebb van mint olvasás. Ezért elsősorban olvasásra kellene optimalizálni.

A char fix hosszuságú, index épitésnél előnyös. A varchar változó hosszuságú, helyet spórolsz, mind a diszken, mind a memóirában, tinytext örökölte a text tulajdonságát tehát csak pointer van a táblában a tényleges adat egy külső helyen tárolódik. Előnyös, ha csak kevés alkalommal van szükséged az adatra, viszont sok full table scant csinálsz, nem erre a mezőre szürve, igy spórolhatsz a diszk müveletekkel.

**huncyrus** · 2008-04-14, 13:57

Ahoj!

Nos szerintem érdemes optimailizálni egy egyadatbázist, hiszen rengeteg programozási időt és erőforrást lehet megsprórolni akkor, ha kevés táblával dolgozol és nem kalimpál a cucc fölösleges dolgokért.

Igazábol, ha überprofi a project akkor nagyon-nagyon sokáig csak az adatbázis alapjait tervezik. (vettem már részt olyanban, ahol az adatbázist két hónapon át papiron tervezgettük és mire a kódolás szakaszhoz jutottunk kb 25 verziot néztünk át és minden cucc megvolt papiron igy joformán csak le kellett gépelni, és dolgoztam már online mmo adatbázis tervezésén is, ott az adatbázisra több mint 4 egész hónap ment el... )

Naszóval, sok esetben túlbonyolítják a dolgokat (lásd a fenti példa, cim text-ben tárolása. erre való a varchar(100)

vagy ott a dátum letárolása. arra ott a date, time és a datetime, fölösleges unix timestampet tárolgatni (egyrészt mivel minden időből mindenfelé lehet konvertálni, másrészt meg igy a sima kinyeréséhez is konverziora van szükséged és nem elég egy sima lekérés (na pl ez is lassitja a dolgokat és teljesen fölösleges erőforrás pocsékolás... kivéve ha kimondottan ez a cél))

Szerintem leginkább egy cms-nél és forumnál azt kell megnézni hogy mire van szükség, és milyen adatokat akarsz letárolni és esetlegesen kellenek-e kapcsolat táblák (lásd az IPB felhasználó kezelése, kb 5 különböző táblázatot néz át 1 hozzászolás lekéréséhez minimum). A másik, hogy tudnod kell a valós igényeket, és a rendelkezésre álló erőforrás / látogató arányt. Egy profinak mondott hostingnál (amely akár lehet gagyi is) nem feltétlen érdemes durvára tervezett nagy adatbázisu cuccot pakolni, hiszen laggolni fog egyszercsak... de ez már más kérdés...

index épitéséhez szigoruan javasolt a szám alapu cucc auto_increment-elve, igy nem kapsz vissza marhaságot és az indexelés is igy default illetve javasolt

(sokan más dolgokat adnak meg indexnek, az sem rossz de nem is a legjobb dolog... )

Láttam itt a kérdést, hoyg mely tipus között mi a különbség. Az egyik, talán a legfontosabb, az hogy mit tárolhatsz le benne és milyen hosszan, a másik meg az, hogy a server mekkora erőforrást és memóriát(!) fog hozzá társítani. Egy int(1) -hez lényeegesen kevesebb erőforrás kell értelem szerüen mint egy texthez vagy egy bigint(200) -hoz. Ennek alapvetően nem sok látszati hatása van, de amikor van egyidőben 600 online usered és 15.000 regisztrált felhasználód akkor bizony érezhetővé és láthatóvá válnak az ilyen nem optimalizált hibák...

[off] talán példaként leginkább a warez forumok ipb váltása vbulletinre és smf-re jól mutatja, hogy az ipb-nek és a phpbb-nek annnyira nagyra sikerült az adattáblája (több százra is rúghat lazán... ) hogy már a kisebb hostokon kimondottan bugolt és lassu volt néhány ezer felhasználónál is... természetesen a cégek javitották a dolgokat, de akkor is elég sokan hirtelen (látványosan) váltottak más cucokra, vagy irtak/irattak maguknak egy cél-softtit...

**MKarcsi** · 2008-04-14, 15:14

Hú, de sok okos dolog hangzott el eddig is

tényleg lehet belőle tanulni ezt-azt. Leírom én is a mostani tapasztalataimat, mert elég esedékes most nekem az optimalizálás.

Bár igazából attól függ az egész, hogy milyen szerveren vagy mit és kiknek csinálsz. Mert egy kisebb csoportnak, akik annyira nem terhelik lekérdezésekkel a db-t egy izmosabb gépen annyira nem kell optimalizálni, mint ennek ellenkezőjekor. Persze ilyenkor se árt azért

Mindig csak annyi adatot tárolj el amennyi kell. Ezt értem a mezők típusának meghatározására is. Teljesen felesleges text-et és int-et használni általában, mert nincs kihasználva.
Felhasználók tárolására se intet használj, mert mikor van szükséged 4 294 967 295 adatra? Soha. Bőven elég egy unsigned smallint például.
Textek helyett a varchar(256)-ok is legtöbbszőr megteszik.
Dátumnál én most a timestamp-et használom, mert így lehet sql-lel is szűrni napra órára pontosan az adatokat.
IP-t ha eltárolod bőven elég szerintem még a varchar(15), mert ez egy maximális IPv4-es cím tárálásának határa, IPv6-nál meg átírod nagyobbra ha esedékes lesz.
MD5-ölt jelszónál detto elég a 32-es varchar mivel több az elve miatt nem lehet.
Amit lehet sql lekérdezéssel csinálj ne php-ben szűrd az adatokat.
Ha csak 1-1 adatot akarsz lekérdezni vagy update-elni és esetleg van több tízezer, akkor használd a limit 1-et, hogy miután megtalálta ne nézzen át még párezret.

Amúgy az ilyen alap dolgoktól eltekintve nem tudom mennyire kell optimalizálni. Persze minnél jobban, de pl. egy smallint és medium int között a spórólás milliós adatoknál jön ki akkor is csak pár KB, sebességnél meg sztem nem is érezhető.