MySQL a použití sloupce ID

MySQL a použití sloupce ID
« kdy: 05. 05. 2011, 11:23:33 »
Zdravím,
práve robím jeden návrh databáze a napadla ma taká myšlienka...

Doposiaľ som robil DB vždy tak, že každá tabuľka mala ako primárny kľúč položku INT "id" s nastaveným auto_increment.

Tu je príklad tabuľky pre párovanie produktov v eShope s kategóriami:

  • id INT PRIMARY KEY autoincrement
  • shop_products_id INT
  • shop_categories_id INT

na stĺpce shop_products_id a shop_categories_id boli samozrejme vytvorené INDEXy.

Táto tabuľka sa dá ale spraviť aj úspornejšie takto:

  • shop_categories_id INT
  • shop_products_id INT

Primárny kľúč by bol vytvorený na obidve položky. INDEX by sa potom vytvoril len na shop_products_id, kvoli vyhľadaniu v ktorej kategorii je produkt.

Otázka teda znie, že či je pre MySQL lepšie používať všade stĺpec ID(či mysql vie lepšie pracovať s takýmito záznamami, ktoré majú id) tak ako v hornom príklade, alebo mu je to jedno ?
Ak je lepšie druhé riešenie, ako čo najsprávnejšie nadefinovať INDEXy?

Je v MySQL nejaká možnosť, že samotná tabuľka by bola INDEX - keďže v druhom prípade sa data ukladajú raz do tabuľky a dva-krát do INDEXov....?

Prosím o solídne a technicky podložené odpovede, prečo je tá - ktorá variatna lepšia.
« Poslední změna: 06. 05. 2011, 03:33:11 od Petr Krčmář »


VM

Mít v každé tabulce vlastní ID je šikovné třeba pro mazání nebo úpravy konkrétního řádku - bez něj se musejí vzít nějaké sloupce co dohromady tvoří UNIQUE index (v daném případě ty dva další cizí klíče), a pokud nejsou, tak operaci prakticky nelze provést (tak aby použila jeden konkrétní řádek).

Samotná tabulka indexem být nemůže - indexy jsou vždy nad konkrétní tabulkou. Pokud tabulka slouží jen pro spojování dvou jiných a typ relace není m:n ale 1:n, n:1 nebo 1:1, tak tabulku lze vypustit a ID vložit do jedné ze spojovaných tabulek - myslím že o něco takového v dotazu šlo. Relace m:n vlastní tabulku z pochopitelných důvodů vyžaduje.

Jak správně nadefinovat indexy? Podle operací které se nad databází běžně provádějí. InnoDB a cizí klíče přímo vyžadují příslušný index, a u žádného běžného dotazu by se nemělo stát, že se kvůli absenci indexů bude muset prohledávat celá tabulka (snad krom případů kdy lze garantovat že bude vždy velmi malá, ale osobně bych dal index i tam).

j.

Zkus se podivat, jak vypaji dotazy nad danou tabulkou. Jestli 90% selektu je "select * where products_id=neco", tak tam ID zadnou pridanou hodnotu nema. A jestli to ale propojujes s jinymi tabulkami, zavisi od struktury databazi a dotazu. Ale tohle vypada na tabulku implementujici n:m relaci, tam mne zadne vyuziti ID nenapada.
A indexy obecne by meli byt nad vsemi polickami, ktere se vyskytuji v klauzili "where" v jakemkoli z dotazu co se budou pouzivat (no ne vzdy, ale v eshopu urcite).

pecko

  • ***
  • 105
    • Zobrazit profil
    • E-mail
precitaj si nieco o normalizacii db, mozno ti to pomoze.

su ludia, co si urputne obhajuju pchanie indexov (tym myslim samostatnych primary keys s auto incerement) do kazdej tabulky. ano, je to pohodlnejsie, ale podla mna je to nesystemove a neprogramatorske a nedatabazove:) kombinacia dvoch unikatnych klucov bude vzdy len unikatnym klucom, takze tam je treti kluc uplne zbytocny. zapamataj si, ze kazdy index ti zbytocne zvacsi tabulku. ak budes mat mrte dat, aj jej udrzba ta cosi bude stat. preto som ja osobne zastancom aplikacie normalizacie, premysleneho navrhu datoveho modelu a rozumnych indexov.

a ako tu uz chalani vyssie spomenuli, rozhodni sa podla toho, ci ten index realne a relevantne potrebujes. ak ho nikdy zmysluplne nevyuzijes, nepchaj ho tam. na vsetko trivialne ti staci unique key zlozeny z dvoch cudzich klucov;)

potom sa skus skamosit aj s 'explain'. to ti tez velmi ulahci zivot a pomoze pri vyrabani vykonnych selectov.

VM

A indexy obecne by meli byt nad vsemi polickami, ktere se vyskytuji v klauzili "where" v jakemkoli z dotazu co se budou pouzivat (no ne vzdy, ale v eshopu urcite).
To obecně nestačí - pokud mám např. WHERE na 3 atributy, tak potřebuji jeden index obsahující všechny 3, nikoliv 3 jednotlivé indexy. Navíc musejí být na začátku - index na (c1,c2,c3,c4) umí dotaz na (c1,c2) a (c1,c2,c3), ale ne (c2,c3,c4).


pecko

  • ***
  • 105
    • Zobrazit profil
    • E-mail
To obecně nestačí - pokud mám např. WHERE na 3 atributy, tak potřebuji jeden index obsahující všechny 3, nikoliv 3 jednotlivé indexy. Navíc musejí být na začátku - index na (c1,c2,c3,c4) umí dotaz na (c1,c2) a (c1,c2,c3), ale ne (c2,c3,c4).

to plati len na zlozene indexy. pokial mas 3 samostatne indexy, mozes ich odkazovat v lubovolnom poradi. ak mas zlozeny kluc, plati to, co si napisal, ze sa na ne mozes odkazovat len v poradi od prveho zlava postupne...

VM

to plati len na zlozene indexy. pokial mas 3 samostatne indexy, mozes ich odkazovat v lubovolnom poradi.
Muzes, ale nedela to co by clovek potreboval (poradi za WHERE nehraje roli, psal jsem o poradi v indexu, a to je neco uplne jineho). Napriklad tabulka o 10 milionech radku, sloupce c1 a c2 obsahujici nahodne nuly a jednicky, index na c1, index na c2, dotaz 'SELECT COUNT(*) FROM tbl WHERE c1=0 AND c2=0'. Prvni index vybere korektne radky s c1=0, ale druhy index uz nepomuze, tech nekolik milionu radku s 'c1=0' se bude muset prochazet jedna po druhe. Pokud se mylim tak me opravte..

pecko

  • ***
  • 105
    • Zobrazit profil
    • E-mail
Muzes, ale nedela to co by clovek potreboval (poradi za WHERE nehraje roli, psal jsem o poradi v indexu, a to je neco uplne jineho). Napriklad tabulka o 10 milionech radku, sloupce c1 a c2 obsahujici nahodne nuly a jednicky, index na c1, index na c2, dotaz 'SELECT COUNT(*) FROM tbl WHERE c1=0 AND c2=0'. Prvni index vybere korektne radky s c1=0, ale druhy index uz nepomuze, tech nekolik milionu radku s 'c1=0' se bude muset prochazet jedna po druhe. Pokud se mylim tak me opravte..

tusim sa mylis:) ak mas tie indexy spravene dobre, musi sa ti uplatnit aj ten druhy

VM

tusim sa mylis:) ak mas tie indexy spravene dobre, musi sa ti uplatnit aj ten druhy
Druhy moc nepomuze - prvni umi vyhledat vsechny radky co maji c1=0, a ty pak nezbyde nez jeden po druhem prochazet a filtrovat podle c2 - bud tabulkou nebo druhym indexem (ale to uz vyjde skoro nastejno, v obou pripadech to bude hodne tisic pristupu na disk). Kdezto index na (c1,c2) by udelal intervalovy dotaz a vratil primo radky vyhovujici _obema_ podminkam najednou, v pripade dotazu COUNT(*) by mohl rovnou vratit cislo.

Logik

  • *****
  • 863
    • Zobrazit profil
    • E-mail
jojo, použije se ten nejrestriktivnější.
Další použít nejde, to by se museli udělat dva datasety, pak setřídit a mergnout - a to už je levnější projít seqvenčně výsledky z toho jednoho indexu. (Ledaže by byl index na hodně složitej výraz, ale to je jinej případ.)

tusim sa mylis:) ak mas tie indexy spravene dobre, musi sa ti uplatnit aj ten druhy
Tusis absolutne spatne.

Staci si nechat ukazat EXPLAIN a uvidis, ze pokud mas 2 indexy tak je to neco uplne jineho nez kdyz je 1 index na obou sloupcich.

Co se tyce ID cloupecku na kazde tabulce, neexistuje nic co by me (nebo autora dotazu) prinutilo cpat id tam kde to neni potreba. A v dany moment nemusi nikoho zajimat 'normalizace' nebo co, zdravy selsky rozum rika, ze pokud auto increment id nebudu pouzivat, tak ho tam prece nedavam.

BTW je zcestne definovat ID jako AUTO INCREMENT INT, protoze tam vzdy budou ulozene jen kladne hodnoty, takze UNSIGNED INT je optimalnejsi; pro rozsahle tabulky ale nemusi stacit a je lepsi treba BIGINT. U mensich tabulek zas bigint je zbytecne velky. Zalezi kolik radku v tabulce kdo ocekava.

Logik

  • *****
  • 863
    • Zobrazit profil
    • E-mail
To, že je jednoduchý primární klíč úplně na nic není tak úplně pravda.
- některé ORM systémy ho potřebují
- vývojem aplikace může dojít k tomu, že daný vztah bude nějak zobecněn např. typem vztahu. Tím se z 1:1 relace stane n:m relace. Pokud bude předem existovat primární klíč, bude úprava aplikace jednodušší.

Sten

Re: My v MySQL používat sloupec ID?
« Odpověď #13 kdy: 05. 05. 2011, 22:52:42 »
Z vlastní zkušenosti: MyISAM nemá rádo, když nemá primární klíč jeden sloupec, který není auto_increment. Zvládá to dobře, ale některé operace (třeba INSERT) začnou zdržovat a jiné nezvládá úplně dobře (transakce, ale to se netýká jenom primárních klíčů). Zkrátka MyISAM není pořádná databáze, ale jen takové chytřejší úložiště. Naproti tomu InnoDB je to jedno a tam bych preferoval ten složený klíč, protože InnoDB podle primárního klíče řadí celou tabulku, takže vyhledávání tam potom není rychlejší jenom kvůli indexu, ale kvůli tomu, že celá tabulka je tak seřazená.

Takže otázka zní: potřebujete transakční databázi nebo jednoduché úložiště?

Logik

  • *****
  • 863
    • Zobrazit profil
    • E-mail
Re: My v MySQL používat sloupec ID?
« Odpověď #14 kdy: 06. 05. 2011, 03:14:31 »
Jenže pokud je odpověď na otázku: potřebuji pořádnou transakční databázi, tak správná rada nezní, nepoužívejte ID, ale nepoužívejte Mysql. Sice se hodně zlepšila, ale pořád jsou některé její implementace dosti zoufalé i v innoDb (nepoužitelnost triggerů spolu s referenční integritou či pohledy, nepodpora CTE rekurentních dotazů aj.).
V tom případě, pokud to je jen trochu možné, tak přejděte na jinou databázi (postgresql je asi nejběžnější z těch dobrých, popř. firebird).