Správny návrh SQL databázy a vyhľadávanie

eMPiko

Zasnem tu nad odpovedami v tejto teme. Riesenie, ktore ponukol sharky hned v prvej odpovedi je jedine, co tu dava zmysel.

*****HERO*****. Riesenie so stringami je pomale, pracne a stracas kopec funkcionality, ktoru ti relacne databazy poskytuju. Toto sa tu uz riesilo - indexovanie, referencovanie, praca s datami cisto v databaze a mnohe ine. Ano, zaciatocnik mozno tieto funkcie nevyuzije, ale aj napriek tomu je dobre sa hned od zaciatku ucit ako sa to ma robit spravne.

To tvoje riesenie, okrem toho ze ma vsetky tie nevyhody, je navyse aj omnoho narocnejsie na implementaciu. Musis si okolo toho naprogramovat cely system, ktory ti bude pri vkladani overovat, ci tam take cislo uz nie je, pri mazani zasa davat pozor na to, aby si nevymazal ciatku z konca ci zaciatku stringu a ani upravovanie nie je take trivialne. Je okolo toho kopec roboty a len takymito rozsireniami do svojho kodu mozes zaviest rozlicne bugy. Namiesto toho, kebyze to urobis spravne a urobis si dobry navrh databazy, tak nic z toho riesis nemusis, lebo vsetko toto riesi za teba prave databaza - chces nieco vymazat? urobis jednoduchy SQL DELETE. chces nieco pridat? urobis jednoduchy SQL INSERT. Cize nielenze je tvoje riesenie menej efektivne, ale este je okolo neho aj viac roboty.

*****HERO*****

eMPiko. narocne na implementaciu? podla mna je to praveze omnoho jednoduchsie na implementaciu. skusim uviest priklady.

chces nieco vymazat? jednoduchy SQL DELETE.

Kód: Vybrať všetko

$kategorie = [1,9,56,18956]
delete from knihy where kategorie LIKE \'%,' . implode(',%\' OR LIKE \'%,', $kategorie) . ',%\';

JOINy? zlozity SQL delete

Kód: Vybrať všetko

$kategorie = [1,9,56,18956]
delete from knihy, knihy_kategoria LEFT JOIN knihy_kategoria ON knihy.id = knihy_kategoria.kniha_id WHERE knihy_kategoria.kategoria_id IN (' . implode(',', $kategorie) . ')

chces nieco pridat? jednoduchy SQL insert

Kód: Vybrať všetko

$nazovKnihy = 'Kniha#1'
$autorKnihy = 'Autor#1'
$rokVydania = '19.5.2018'
$kategorie = [1,9,56,18956]
insert into knihy(nazov,autor,rok,kategorie) VALUES($nazovKnihy, $autorKnihy, $rokVydania, \',' . implode(',', $kategorie) . ',\')

JOINy? 2x insert query (+ potreba si dopredu vyskladat hromadne INSERT query (cize o jedno pouzitie stringu v PHP naviac (a to som este bol dobry, ze som pouzil nativky a netocil to cyklom ..))), vela zbytocne prenesenych dat (DB musi napriklad vratit id posledneho pridaneho riadku knihy ..)

Kód: Vybrať všetko

$nazovKnihy = 'Kniha#1'
$autorKnihy = 'Autor#1'
$rokVydania = '19.5.2018'
$kategorie = [1,9,56,18956]
$idKnihy = insert into knihy(nazov,autor,rok) VALUES($nazovKnihy, $autorKnihy, $rokVydania)

$insertKategorie = implode(',',
    array_map(function ($kategoria) use ($idKnihy) {
        return '(' . $idKnihy . ',' . $kategoria . ')';
    }, $kategorie)
);

insert into knihy_kategoria (kniha_id, kategoria_id) VALUES ' . $insertKategorie . '

Prakticke vyuizitie?

Mam tabulku A so 100 zaznamami, tabulku B s 10 000 zaznamami, tabulku C so 1 000 000 zaznamami.

- Tabulka A ma cudzi kluc na A.stlpec_1 = B.stlpec_3.
- Tabulka B ma cudzi kluc na B.stlpec_2 = C.stlpec_1

Chcem vybrat vsetky riadky z tabulky A, kde C.stlpec_5 = 29

JOINY:

Kód: Vybrať všetko

select * FROM A
LEFT JOIN B ON A.stlpec_1 = B.stlpec_3
LEFT JOIN C ON B.stlpec_2 = C.stlpec_1
WHERE C.stlpec_5 = 29
GROUP BY A.id

Stringy:

Kód: Vybrať všetko

select * FROM A WHERE A.idecka_z_C_tabulky_stlpca_5 LIKE '%,29,%'

Testy sa mi robit nechce, ale mam taky tusak, ze joinove riesenie v tomto pripade je jednak narocnejsie na implementaciu, narocnejsie na zataz DB, SQL je narocnejsie na citanie a pochopenie amaterom.

jorg22 napísal:Pisal som to preto, lebo ak sa niekto uci ako sa navrhuje databaza tak asi ziskava vela informacii z okolia a ked sa mu do toho budu miesat aj tie "zle" informacie tak sa to nauci zle.

Ale ked uz si napisal, ze vies vymenovat vela prikladov kde sa to hodi tak ma to zaujima. Nehovorim, ze to nemoze mat prakticke vyuzitie ale ja si ho neviem predstavit tak budem rad ak mi rozsiris obzory. Tak mi skus popisat nejaky kontext v praxi kde sa to hodi?

Ale ako pisem v 99% pripadoch je toto spravne riesenie a musis najprv nad nim uvazovat pokial chces pouzit nejake nestandardne. Bezne sa robi danormalizacia databazy koli ziskaniu vykonu ale to je uz na extremne velkych databazach, s ktorymi sa zaciatocnik nestretne. No a konkretne tento priklad na vykone ani nepridava.

Ja som pisal o tomto rieseni preto, lebo ten clovek sa venoval SQLku uz pred 10 rokmi, a stale plava na uplnom brehu ... pochybuyjem ze jeho motivaciou je sa nieco naucit ... to by si zobral knihu a k joinom by sa dostal v uvodnych kapitolach .. on len chce mat nieco hotove ..

ropman

Testy sa mi robit nechce, ale mam taky tusak, ze joinove riesenie v tomto pripade je jednak narocnejsie na implementaciu, narocnejsie na zataz DB, SQL je narocnejsie na citanie a pochopenie amaterom.

tak JOINOVANIE na zaklade indexovanych klucov urcite nie je vecsia zataz na DB ako queries typu LIKE '%,29,%' -> ako chces toto indexovat ? napr. v MySQL by si tam mohol dat maximalne tak fulltext index a to by potom to query muselo vyzerat uplne inac (zlozitejsie). Kdezto na normalizovane data su tieto DB systemy optimalizovane by default pretoze je to uplne standardny use case.

V tych prikladoch co si postol si akurat usetril zopar JOINOV na ukor toho ze musis krkolomne buildovat stringy, co tiez nie je o nic jednoduchsie.

Jedine pri inserte/update usetris nejake queries kt. manipuluju s intermediate table, ale to nevidim ako moc velky benefit, pretoze:
a) insert/update je malo kedy performance bottleneck
b) v aspon trochu serioznejsom projekte to budes mat poriesene nejakou abstrakciou/ORM a v uplne malom projekte si tych par insertov mozes spravit aj sam.

A samozrejme nemas tam nijak poriesenu referencnu integritu, ktora sa pri normalizovanom navrhu da jednoducho spravit.

eMPiko

HERO jednym slovom grc. Prosim ta, predtym nez zacnes nejake databazy montovat alebo nebodaj radit ludom, skus sa pozriet co si o datovom modelovani myslia profesionali - napr. v nejakej knihe. Toto co si tu dal je vyslovene skodlivy kod, nieco ako ked ti na chorobu "profesionali" na internete odporucaju vypit bielidlo. Nemam naladu tu dokolecka opakovat zakladne znalosti z tejto oblasti, su to uplne zaklady, ktore sa docitas v prvych kapitolach kazdej normalnej ucebnice alebo ineho materialu.

*****HERO*****

Ja tu vidim zatial len same ucebnicove kecy z tvojej strany, nic konstruktivne. Pisal si o narocnosti na implementaciu, tak som ti na prikladoch ukazal, ze to tak byt nemusi. Cakam teda, ze tie tvoje tvrdenia tiez ukazes na realnom priklade, ktory by shakalovi pomohol pri rieseni jeho problemu.

Btw nic si z toho nerob, tiez som v programovani trpel obsesiami robit vsetko podla navodu a mat cisty kod, ale cas ukazal, ze prakticke riesenia su v dnesnom svete bohuzial viac cenenne.

eMPiko

Pravdupovediac cloveka co by s tymto prisiel do akejkolvek serioznej firmy by okamzite vyrazili alebo aspon posadili spat na junior poziciu, kde ho niekto starsi este moze zaucit. Toto sa na vysokych skolach uci v prvom/druhom rocniku. Ako som vravel, ja ti tu zaklady datoveho modelovania vysvetlovat nebudem. Dovodov preco to nerobit cez string mas v tejto teme dost. To ze im nerozumies nie je moj problem - google ti pomoze ak mas zaujem sa nieco naucit.

*****HERO*****

Bozechran ma od serioznej firmy, robil som som polroka v esete, co som isteho casu pokladal ako jednu z top pracovnych prilezitosti v BA a jedine co tam bolo seriozne je to, ze sidlia v budove, kde je jedine KFC v BA. Ale dik za tipy, skusim si pogooglit co to je vlastne ten join, ja som doteraz ako databazu len notepad pouzival

awtt · Príspevok od používateľa **awtt** » 21 máj 2018, 7:02

Ta join tabulka je fakt lepsie riesenie, ale ja by som ten json tak cierne ako eMPiko nevidel. V robote bezne serializujeme JSON v tabulkach s milionmi zaznamov a v pohode.
V active record je to asi jeden riadok navyse. (inac to fakt este v dnesnej dobe (okrem analytics) niekto pise raw sql?

)

ropman

btw je rok 2018, uz nemusime na kazdy use case pouzivat relacnu sql db ak nechceme, ale existuje mnoho nosql, document-oriented, schema-less databaz ako napr. mongodb:

https://www.mongodb.com/what-is-mongodb

harrison314

Pri niekolko milionoch zaznaov bude ten JOIN (v skutocnej databaze, ktora vie robit aj daco ine ako loop join) vyrazne richlejsi ako rienie cez LIKE.
A keby taketo rienie prestalo z nejakeho divneho dovodu stacit, tak clanky s kategoriami zaindefujem pomocou nejakeho fulltext enginu a vyhladavam ich cez neho, ale zdroj dat bude v relacnej databaze v normalnej forme.

@ropman: MongoDb je super vec, ale je to dokumentova datbaza a ma specialne pouzitie, ked sa do nej rozhodnes tlacit relacne data vzniknu len problemy, uz som to videl mnohokrat.

//autoeditácia príspevku (21 Máj 2018, 8:00)

awtt napísal:(inac to fakt este v dnesnej dobe (okrem analytics) niekto pise raw sql? )

Ano pise a nie je to tak ojedinele.

eMPiko

NoSQL? Chlapci, vy ma zabijate. Dam si teda tu namahu a skusim rozpisat vsetky nevyhody, ktore ma taky model, kde id-cka pribuznych entit ukladam do nejakeho stringu s ciarkami. Budem teda konkretne pouzivat tento pripad, ale vela z tych vyhrad sa daju zovseobecnit aj na pribuzne formy ukladania tych dat, napr. serializacia v jsonoch a podobne. Vidim, ze vela ludi tu v tom ma gulas, a vobec sa necudujem, lebo tiez som volakedy pisal podobne nezmysly. Urcite odporucam nastudovat si aspon nejake zakladne kapitoly o tejto teme, lebo tu riesime veci, ktore boli vyriesene uz v 70. rokoch a len si zbytocne sposobujeme bolehlavy zlozvykmi.

Najprv si teda podme zadefinovat dve datove schemy, jednu s pouzitim cudzich klucov a tzv. asociacnej tabulky a druhu s pouzitim stringu. Budem tu, aj dalej v prispevku pouzivat PostgreSQL:

A - cudzie kluce

Kód: Vybrať všetko

CREATE TABLE knihy (
  id SERIAL PRIMARY KEY,
  nazov VARCHAR(100)
);

CREATE TABLE kategorie (
  id SERIAL PRIMARY KEY,
  nazov VARCHAR(100)
);

CREATE TABLE kategorie_knihy (
  kniha_id INTEGER REFERENCES knihy(id),
  kategoria_id INTEGER REFERENCES kategorie(id),
  UNIQUE (kniha_id, kategoria_id)
);

B - stringy

Kód: Vybrať všetko

CREATE TABLE knihy (
  id SERIAL PRIMARY KEY,
  nazov VARCHAR(100),
  kategorie TEXT DEFAULT ','
);

CREATE TABLE kategorie (
  id SERIAL PRIMARY KEY,
  nazov VARCHAR(100)
);

V pripade A mame dve logicke tabulky knihy a kategorie a v tabulke kategorie_knihy (toto sa nazyva vazobna entita alebo asociacna tabulka) zaznamenavame, ktore knihy patria do akych kategorii. V tabulke B mame podobne tabulky knihy a kategorie, ale namiesto vazobnej entity tu mame nejaky string. Aj ked teda definicia A je marginalne narocnejsia (+3 riadky SQL kodu), ja tvrdim, ze je omnoho lepsia ako jej alternativa B. Prvy dovod vidime uz tu pri definovani. Aj ked je ta definicia dlhsia, jej vyhoda je ta, ze uplne opisuje, ake data sa kde v databaze nachadzaju. Mame jasne definovane co kam patri, a ked ku tomuto kodu pride novy clovek, alebo sa k nemu vratim za dva roky, tak pri pohlade na toto hned viem, ako su v tej databaze data ulozene. Inymi slovami ten datovy model je uplny.

Naproti tomu v pripade B mame stlpec kategorie, ktory je nejaky string, ale vobec netusime, co sa v nom ma nachadzat. Ako DEFAULT hodnotu ma nastavenu ciarku, ale to nam stale nepovie, ze ake data v akej forme su v nom ulozene. HERO ma takuto notaciu: ,id1,id2,id3,. Lenze toto nikde tej databaze nedokazeme povedat, ze by mala cakat nieco podobne (resp. vedeli by sme, ale museli by sme si vybudovat nas vlastny regexp constraint, ktory nam to bude kontrolovat zakazdym pri vkladani - toto je ale implementacne netrivialna vec). Nas datovy model, t.j. to, akym sposobom data ukladame tu vobec nie je v tejto definicii opisany. To je samozrejme chyba, lebo namiesto toho, aby sme vyuzili schopnosti SQL databaz modelovat dat, si musime tieto veci pamatat, alebo si to zapisat do nejakej dokumentacie. Toto je navyse problem, ktory sa s nami bude tahat aj po zvysok tohto postu. Dalej uvadzam niektore bezne pripady pouzitia, ktore sa s databazou daju robit a uvadzam aj dovody, preco je sposob B zly.

Vkladanie do databazy

Do takejto jednoduchej databazy mozeme chciet vlozit 3 veci: knihy, kategorie, alebo spojenie medzi nimi. Podme cez ne prejdem.

Vkladanie knihy

Toto je jediny pripad, kedy je system B marginalne jednoduchsi:

Kód: Vybrať všetko

INSERT INTO knihy(nazov, kategorie) VALUES ('moj_nazov', ',1,2,3,')

V systeme A to musim robit na 2 INSERTy:

Kód: Vybrať všetko

INSERT INTO knihy(nazov) VALUES ('moj_nazov') RETURNING id;
INSERT INTO kategorie_knihy(kniha_id, kategoria_id) VALUES (id, 1), (id, 2), (id, 3);

Vkladanie kategorie

Toto je rovnako trivialne v oboch pripadoch:

Kód: Vybrať všetko

INSERT INTO kategorie(nazov) VALUES (nas_nazov);

Vkladanie prepojenia

Tu prvy krat mozeme vidiet, ake vyhody ma definicia A. V nej je vkladanie velmi trivialne, chcem vlozit nieco do databazy, napr. spojenie medzi knihou s id X a kategoriou s id Y, tak semanticky presne to aj urobim

Kód: Vybrať všetko

INSERT INTO kategorie_knihy VALUES (X, Y);

Uplne trivialne, podme sa pozriet na vkladanie cez stringy:

Kód: Vybrať všetko

UPDATE knihy
SET kategorie = kategorie || ',' || Y
WHERE id = X;

Tento prikaz je podla mna omnoho menej intuitivny, chcem vlozit do tabulky vztah, ale namiesto toho musim luskat nejaky UPDATE cez stringy a vobes ovladat stringove operacie. To same osebe taky problem nie je. Omnoho vacsi problem je to, ze v pripade A mi databaza, kedze je dobre nastavena, sama od seba skontroluje, ci data, ktore do nej vkladam, maju zmysel. V definicii tabulky kategorie_knihy mam jasne napisane tri dolezite obmedzenia, ktore si databaza zobrala ku srdcu a pri kazdom vkladani do databazy ich kontroluje:

1) obe id-cka su typu INTEGER
2) obe su cudzie kluce, ktore odkazuju na existujuce zaznamy v tabulke knihy alebo kategorie
3) kazda dvojica kniha_id, kategoria_id musi byt unikatna

T.j. Namozem tam vlozit namiesto cisla nejaky string. V pripade B mozem. Kebyze v implementacii urobim chybu a namiesto id tak davam napr. meno, tak ta dabaza si tam bude veselo ukladat ,meno1,meno2,meno3, a ani nepipne. Preco je toto zle? Lebo ludia pri implementacii spravidla chyby robia, najma zaciatocnici. To ani nehovorim o pripadoch, kedy s jednou databazou pracuje viacero programatorov. Vtedy je absolutne nevyhnutne, aby sme taketo pravidla databaze povedali a ona sa nimi potom moze riadit a ked tam chce niekto vlozit nejaku koninu, tak mu to hned vypise chybu.

Podobne argumenty sa daju pouzit aj na (2) a (3). V pripade B mozem napr. pokojne mat takyto retazec ,1,1,1, . T.j. jedna kniha bude mat tu istu kategoriu zapisnu 3x. Mozem napriklad urobit nejaku chybu pri implementacii a omylom spustit nejaky prikaz viac krat. Pripad A taketo nieco nedopusti a hned vypise chybu a viete, ze ste nieco urobili zle. Taktiez v pripade B mozete mat pripad, kde do stringu vlozite ,1000, ale kategoria s takym id sa v databaze nenachadza. Toto opat pripad A nedopusti. Mozete sa pytat, ale preco by ma toto malo zaujimat, no tak tam mam nejaku kategoriu viac krat, alebo je tam nejake id, ktore neplati, a preco je toto akoze zle?

Dovodov je niekolko, ale hlavny sa nazyva konzistencia dat alebo inymi slovami datova integrita. To je akysi ciel, ktory by sme mali pri navrhu databaz mat vzdy na pamati. Chceme, aby tie data, co mame v databaze davali vzdy v kazdom momente zmysel. Pripad A toto zabezpecuje (id-cka su kluce, neopakuju sa a odkazuju na existujuce kategorie), zatial co pripad B toto zabezpecit vobec nevie. Naopak, toto, co vam v A poskytuje databaza, kontroluje vas a dava pozor, aby ste robili korektne veci, v B si toto vsetko musite spravovat sami. Pri kazdej operacii nad databazou B hrozi, ze urobite nejaku chybu, pokazite si data (v tomto pripade tie stringy) a uz sa k nim nikdy nedostanete.

Ja som napriklad zamerne v kode hore (ten UPDATE), dal na zlu stranu ciarku. Kolki z vas si to vsimli? Takato jednoducha chyba z nepozornosti vam moze riadne zneprijemnit zivot, lebo ta databaza sa bude tvarit, ze vsetko funguje ako ma, ale v skutocnosti data v nej budu chybne. Napr. ked sa pozrieme na pripad, ze mame v tom stringu id-cka, ktore sa opakuju ,1,1,. Ja takyto string z tej databazy v dobre viete vytiahnem, lebo neviem ze je chybny a dam si vypisat kategorie danej knihy. Kedze su tam dva take iste id, tak mi vypise 2x za sebou tu istu kategoriu. A teraz ja mozem len tapat, ze do prcic, preco mi ten kod vypisuje takuto koninu a kym pridem na to, ze je to zlymi datami, tak mi to moze hodnu chvilu trvat. Navyse, ked na takuto chybu pridete, vobec nemusi byt lahke ju opravit. Mozete napriklad pri nejakej operacii zabudnut dat na koniec stringu ciarku ,1,2,3 a ked potom pridate dalsiu kategoriu -- 4, vzikne vam taketo cosi ,1,2,34,. 3 a 4 sa spoja a vznikne vam kategoria 34 a nemate sancu uz zistit, ze kde sa stala chyba.

Toto vsetko su problemy, ktore su sposobene nedostatocnou definiciou dat, kedy namiesto toho, aby sme naozaj vyuzili moznosti kontroly, ktore nam databaza ponuka, skusame implementovat nejaky vlastny krkolomny system. System A je ako vas uprimny kamarat, ktory vam hned z fleku dokaze dat konstruktivnu kritiku. System B vsak naopak ubezpecuje ze vsetko je v poriadku a pokojne tam spracuje cokolvek mu podhodite, ale zrazu zistite ze vam nefunguje aplikacia a netusite preco a musite rucne prechadzat vase zaznamy v databaze a opravovat ich.

Mazanie z databazy

Tu, podobne ako pri vytvarani, mame 3 pripady pouzitia: chcem vymazat knihu, kategoriu, alebo ich prepojenie. Tu si este dovolim doplnit definiciu A o par klucovych slov:

Kód: Vybrať všetko

  kniha_id INTEGER REFERENCES knihy(id) ON DELETE CASCADE,
  kategoria_id INTEGER REFERENCES kategorie(id) ON DELETE CASCADE,

Mazanie knihy

V oboch pripadoch trivialne:

Kód: Vybrať všetko

DELETE FROM knihy WHERE id = X;

Mazanie prepojenia

Chcem vymazat prepojenie medzi knihou X a kategoriou Y. V pripade A je to zasa uplne pochopitelny a logicky kod:

Kód: Vybrať všetko

DELETE FROM kategorie_knihy WHERE kniha_id = X AND kategoria_id = Y;

V pripade B je to taketo nieco:

Kód: Vybrať všetko

UPDATE knihy
SET kategorie = REPLACE(kategorie, ',' || Y || ',', ',')
WHERE kniha_id = X;

Opat teda namiesto toho, aby som napisal co chcem urobit - vymazat nejaky zaznam - musim srotit nejake SQL string prikazy, ktore su na prvy pohlad absolutne nepochopitelne. To ale nie je vsetko, pred chvilou sme si povedali, ze pri vkladani do takejto databazy sa nam pokojne moze stat, ze sa nam tam jedno id odcitne viac krat, napr. ,1,2,2,. Tu sme omylom vlozili kategoriu 2 dva krat za seba. Co sa ale stane, ked nad takymto stringom spustim ten UPDATE?

Kód: Vybrať všetko

SELECT REPLACE(',1,2,2,', ',2,', ',');

Vysledok je takyto:

Kód: Vybrať všetko

,1,2,

Kvoli tomu, ako je implementovane rozponavanie stringov vam databaza zaregistruje len tu prvu dvojku, tu nahradi ciarkou a tu druhu necha tak. Aj ked chcem teda vymazat vztah, databaza to nedokaze urobit. Riesenim je urobit to mimo databazy alebo nejakou custom string operaciou - cize implementacie pomerne narocne scenare. Mazanie prepojenia je teda v stringoch nespolahliva operacia

Mazanie kategorie

Tu sa uz dostavame ku zaujimavejsim veciam. Doteraz to bolo najma o datovej integrite a tom, ze definicia B vam dovoli zapisat do databazy hociaku chobotinu a vy na to pridete az ked vam padne aplikacia. Teraz uz sa dotkneme trochu aj efektivity. Ako sa teda v systeme A da zmazat kategoria Z? Jednoduchu:

Kód: Vybrať všetko

DELETE FROM kategorie WHERE id = X;

Opat velmi intuitivne, chcem zmazat kategoriu, tak presne to napisem databaze. A kedze ju mam dobre nasetupovanu, ona je taka laskava, ze mi zmaze nie len tu kategoriu, ale aj vsetky prepojenia medzi touto kategoriou a knihami - toto maju na starosti tie klucove slova ON DELETE CASCADE. Teda je to vsetko z databazy prec. Ked tento isty prikaz spustim v B, tak mi to sice riadok v kategorii zmaze tiez, ale v tabulke knihy zostanu v tom nestastnom stringu vsetky odkazy na tuto kategoriou, napr.: ,1,2,X,. Opat sa mozete pytat, a je toto problem? Ano, je, lebo zasa stracate datovu integritu a nemozete uz svojej databaze verit, ze tie data, ktore v nej su, su aktualne. Napr. mat tam takyto zombie id, ktorym nikam neukazuje, je celkom pruser. Zakazdym ked s tymto stringom budete niekedy v buducnosti chciet nieco urobit, budete si musiet pamatat, ze v nom moze byt takyto problem a budete musiet takyto extremny stav osetrovat. Zakazdym.

Okej, ale vsak stale mozem vsetky tie prepojenia zmazat rucne:

Kód: Vybrať všetko

UPDATE knihy
SET kategorie = REPLACE(kategorie, ',' || X || ',', ',');

Ten isty UPDATE ako predtym, len teraz som nedal na koniec WHERE. Tento UPDATE teda zbehne na kazdom jednom stringu v stlpci kategorie, ktory mam v databaze. Toto je vlastne mazanie prepojenia, ktore som uz rozoberal a stale platia vsetky problemy, ktore to malo. Tu ale pribudol jeden novy: casova efektivnost. Tato vec mozno databazoveho zaciatocnika trapit az tak nemusi, ale je dobre na to mysliet, kebyze sa chce posunut niekam dalej.

Predstavme si, ze mame taketo pocty zaznamov v tabulkach: 100.000 knih, 1000 kategorii a kazda kniha patri v priemere do 10 kategorii, cize mame 1.000.000 prepojeni medzi knihami a kategoriami. Nazvyme tieto cisla takto: pocet knih - j, pocet prepojeni - k, pocet kategorii - l. Aka je teda vypoctova zlozitost zmazania jednej kategorie v systeme A? Kedze mame nad nasimi datami vytvorene indexy, zlozitost najdenia jedneho zaznamu v tabulke kategorie je log_2(l) a zlozitost najdenia prisluchajucich zaznamov v tabulke prepojeni je log_2(k). Kedze l je malicke cislo, povieme ze celkova zlozitost je O(log_2(k)). Je to logaritmicka zlozitost, co to znamena v praxi je, ze system A dokaze zmazat jednu kategoriu na log_2(1.000.000) ~= 20 elementarnych krokov. V praxi by som cakal odozvu ~30ms.

System B ma zmazanie kategorie rovnako narocne log_2(l). Zmazanie prepojeni ale vyzaduje prechod cez kazdy jeden string zo stlpca kategorie v tabulke knihy. Kazdy jeden. Vieme ze v tychto stringoch je zakodovanych k milion vztahov, cize jeden prechod cez tieto stringy trva minimalne k elementarnych krokov. Hovorime o linearnej vypoctovej zlozitosti O(k). V tomto pripade mame teda pripad B 1.000.000 elementarnych krokov vs pripad A 20 elementarnych krokov. To je 50.000x pomalsie. V praxi by som cakal odozvu niekolko sekund, cize uz nieco celkom pomale.

Na milionoch zaznamoch to este take zufale nie je, ale kebyze tam tych prepojeni je 10.000.000 tak system A ani okom nemihne a stale bude schopny urobit to radovo v milisekundach. Naproti tomu system B, ktoremu vypoctova zlozitost rastie linearne a teda aj cas vypoctu vzrastie 10-nasobne a namiesto sekund je to uz zrazu minuta. A minutu cakat na premazanie nejakych chorych stringov asi nikto cakat nechce.

Pri mazani vo vseobecnosti teda nadalej mame problemy so zlou konzistenciou dat, ale uz sa nam tu pridali aj problemy s vykonom a problemy so spracovanim stringov, ktore si musime robit sami.

Dopyty do databazy

Napokon sa este velmi rychlo dotknem dopytov do databazy. Tu je problem s casovou narocnostou podobny ako pri mazani. Ak potrebujeme v kazdom dopyte prejst vsetky stringy co mame v databaze, casova zlozitost tohto riesenia je linearna, zatial co casova zlozitost JOIN-ov je logaritmicka. Toto opat zacne byt zaujimave az pri vacsich tabulkach, kde stringy zacnu velmi rychlo naberat sekundove ci minutove doby bezania, zatial co vykon systemu A je skalovatelny az kamsi do miliard, kde uz nie je problem cas, ale pamatovy priestor.

Chcem najst vsetky knihy z danej kategorie

V systeme A je to trivialna uloha:

Kód: Vybrať všetko

SELECT nazov FROM knihy kn
JOIN kategorie_knihy kk ON kk.kniha_id = kn.id
WHERE kategoria_id = X;

Vypoctova zlozitost tohto riesenia je opat logaritmicka. V pripade systemu B musim urobit taketo nieco:

Kód: Vybrať všetko

SELECT nazov FROM knihy
WHERE kategorie LIKE ',' || X || ',';

Kedze presne ako v pripade DELETE, tu musim pri kazdom SELECTe precitat kazdy jeden znak zo vsetkych stringov, co mam v databaze, ta vypoctova zlozitost je linearna. Presne ako v tom DELETE je teda aj toto ~50.000x pomalsie pri takych velkych tabulkach, ake som navrhol.

Chcem najst pre kazdu knihu vsetky jej kategorie

Kód: Vybrať všetko

SELECT kn.nazov, ka.nazov FROM knihy kn
JOIN kategore_knihy kk ON
JOIN kategorie ka ON;

V systeme A je to par JOINov. V systeme B mame zasa linearnu zlozitost, teda musime kazdy jeden string rucne prehrabat:

Kód: Vybrať všetko

SELECT ka.nazov, t.nazov FROM kategorie ka
JOIN (
  SELECT UNNEST(string_to_array(kategorie, ',')) AS kategoria_id, nazov
  FROM knihy
) AS t ON t.kategoria_id = ka.id;

Opat teda totalne neintuitivna implementacia, ktora nie je vobec robustna, voci tym problemom co tie stringy maju a ktore som uz spominal. Okrem toho je to stale omnoho casovo narocnejsie.

Aby som to teda zhrnul:

1) B ma velmi slabu integritu dat a ked raz urobite pri zadavani do databazy chybu, nikdy sa z nej nevyhrabete. A ma uplnu integritu dat, vsetky data v systeme maju zmysel a mozete sa na ne spolahnut.
2) B ma pri beznych ulohach linearnu casovu zlozitost, zatial co A ju ma logaritmicku. Kebyze mame tebulku s 1M zaznamami, tak system A je ~50.000x rychlejsi (aspon radovo by to sedelo). Kedze system A je kompatibilny s relacnym modelom, dokaze pri nom databaza naplno vyuzivat query planning a optimalizovat spustanie prikazov. System B toto nedokaze vobec.
3) V systeme B si kopec veci musite programovat rucne a neustale si musite pamata na rozlicne nezvycajne stavy, ktore v nom mozu nastat. Databaza v systeme A ma kopec veci na starosti sama a nemusite na to vobec dbat.
4) System A je v sulade so sucasnymi postupmi. Mozete ho pouzivat s ORM, kniznicami a inym softverom, ktory presne taketo tabulky v databaze ocakava. Za system B vas deti na pieskovisku budu sikanovat.
5) Jedina nevyhoda systemu A je, ze si musite nastudovat odhadom tak prve 3 kapitoly o datovom modelovani, co vam zaberie tak hodinu a pol. Naproti tomu ale usetrite desiatky hodin casu tym, ze nebudete musiet nahanat hlupe bugy vzniknute v systeme B.

Na zaver este kratko ku NoSQL. Niekto tu pisal, ze v roku 2018 nemusi byt kazda databaza relacna. Ja by som to prave obratil, je rok 2018 a ten hype okolo NoSQL databaz uz uticha a mozeme sa zamysliet nad tym, co ziskame / co stratime ich pouzivanim. Pokial mas data s jasne definovanymi entitami, ktore medzi sebou maju jasne definovane vztahy (napr. knihy a kategorie), relacne databazy su stale ta spravna cesta. Jedinou vynimkou je, ked potrebujes optimalizovat vykon kvoli specifickym narocnym dopytom. Vtedy ale stracas datovu integritu, atomickost operacii a ine chutovky relacnej databazy.

jorg22

eMPiko to prave zhrnul velmi vystizne a obdivujem ta, ze si s tym dal tolko namahy.

A ty co tu hypujete NoSQL databazy do vsetkych rieseni, alebo ste robili v ESETe a uz vsetko viete tak to co napisal eMPiko citajte stale dokola az dokym tomu nepochopite a neprestanete radit ludom hacky, ktore nemali nikdy uzriet svetlo sveta.

harrison314

@eMPiko:

Zhrnute absolutne vsetko.

Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie

Re: Správny návrh SQL databázy a vyhľadávanie