Fórum Root.cz
Hlavní témata => Vývoj => Téma založeno: Michal Zahradníček 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.
-
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).
-
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).
-
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.
-
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).
-
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...
-
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..
-
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
-
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.
-
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.
-
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šší.
-
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ě?
-
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).
-
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.
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.
zaujimave, ze mne vzdy druhy zaberal a explain mi to potvrdil.
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.
to je prave ten najvacsi problem 98% webovych aplikacii. ze kaslete na normalizaciu a riadite sa len sedliackym rozumom:) a potom databazy vyzeraju, ako vyzeraju a vsetci kydaju na mysql, ako keby ono mohlo za to, ze pouzivate iba sedliacky rozum namiesto pravidiel tvorby datoveho modelu. mysql mozno neni mega db. mozno neni vobec db, to nech si riesi ini inde. urcite to ale je nastroj, ktory si vie poradit datami a pokial dodrziavas iste pravidla (napr. normalizacia), vie to byt aj vykonny nastroj. ja by som sa teda v pripade databaz stranil sedliackeho rozumu, lebo to je najvacsi a najcastejsi zabijak databaz a je to najsignifikantjesi markant toho, ze autorom db je neskuseny amater, ktory je len mudry vo forach:)
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šší.
ak dojde k tomuto, podla mna bol zle navrhnuty datovy model a aplikacia pred vyvojom nepresla dostatocnou analyzou. su sice hranicne okolnosti, kedy k podobnemu javu moze dojst, ale tie sa daju minimalizovat preciznou pripravou
-
zaujimave, ze mne vzdy druhy zaberal a explain mi to potvrdil.
Takovy explain bych chtel videt. Typuju ze ukazoval USING WHERE, coz je chyba pokud chces selektovat pomoci indexu. Zalezi ale jake dotazy jsi s tim provadel, no :)
to je prave ten najvacsi problem 98% webovych aplikacii. ze kaslete na normalizaciu a riadite sa len sedliackym rozumom:) a potom databazy vyzeraju, ako vyzeraju a vsetci kydaju na mysql, ako keby ono mohlo za to, ze pouzivate iba sedliacky rozum namiesto pravidiel tvorby datoveho modelu. mysql mozno neni mega db. mozno neni vobec db, to nech si riesi ini inde. urcite to ale je nastroj, ktory si vie poradit datami a pokial dodrziavas iste pravidla (napr. normalizacia), vie to byt aj vykonny nastroj. ja by som sa teda v pripade databaz stranil sedliackeho rozumu, lebo to je najvacsi a najcastejsi zabijak databaz a je to najsignifikantjesi markant toho, ze autorom db je neskuseny amater, ktory je len mudry vo forach:)
Nevim jestli ted myslis me osobne, v kazdem pripade ja jsem zastance MySQL a mam stejny nazor jako ty - pokud nekdo spatne navrhne databazi a pak mu to nefunguje poradne a nadava na MySQL, tak je to spatne.
Tim selskym rozumem jsem mel na mysli predevsim to, pochopit jak databazovy server funguje a podle toho psat SQL dotazy a delat navrh db, a ne jen vzit nejakou teoretickou prirucku o 'normalizaci' (coz ja ani poradne nevim co je) a ridit se podle nejake prirucky.
A pokud mi je sloupec s auto increment ID k nicemu a v aplikaci ho nepouziju a mam moznost jednoznacne identifikovat radek tabulky pomoci jineho klice (treba slozeneho z vice sloupcu), tak se muzu na ID vykaslat a MySQL s tim NEMA nejmensi problem. A pokud nekdo tvrdi ze ma, tak at laskave odkaze na nejakou analyzu.
-
Takovy explain bych chtel videt. Typuju ze ukazoval USING WHERE, coz je chyba pokud chces selektovat pomoci indexu. Zalezi ale jake dotazy jsi s tim provadel, no :)
zial, uz si ho nepamatam, ale viem, ze to fungovalo spolahlivo... s tym using where si nie som isty
Nevim jestli ted myslis me osobne, v kazdem pripade ja jsem zastance MySQL a mam stejny nazor jako ty - pokud nekdo spatne navrhne databazi a pak mu to nefunguje poradne a nadava na MySQL, tak je to spatne.
Tim selskym rozumem jsem mel na mysli predevsim to, pochopit jak databazovy server funguje a podle toho psat SQL dotazy a delat navrh db, a ne jen vzit nejakou teoretickou prirucku o 'normalizaci' (coz ja ani poradne nevim co je) a ridit se podle nejake prirucky.
A pokud mi je sloupec s auto increment ID k nicemu a v aplikaci ho nepouziju a mam moznost jednoznacne identifikovat radek tabulky pomoci jineho klice (treba slozeneho z vice sloupcu), tak se muzu na ID vykaslat a MySQL s tim NEMA nejmensi problem. A pokud nekdo tvrdi ze ma, tak at laskave odkaze na nejakou analyzu.
myslel som teba a kazdeho, kto pracuje s databazami bez akychkolvek predchadzajucich teoretickych znalosti len na zaklade ziskanych praktickych skusenosti (= sedliacky rozum ;) ). do teoretickych znalosti neratam navody na spravnu pracu s mysql ako takym. ak to citas a riadis sa podla toho, je to fajn. ale ono k tomu treba vediet aj malinko viac. netvrdim, ze ja som guru, ale viem, ze precitat si cosi okolo normalizacie mi len pomohlo. zistis zase o cosi viac, ako pristupovat k tvorbe db. nestaci len chapat, ako funguje mysql server. treba pochopit aj zasady datoveho modelovania a aspon trosku treba poznat zasady normalizacie.
-
zial, uz si ho nepamatam, ale viem, ze to fungovalo spolahlivo... s tym using where si nie som isty
Nechcem ani vediet, co si predstavujes pod pojmom "fungovalo to spolahlivo". MySQL vie od verzie 5.0 pouzit v specialnych pripadoch aj dva indexy s tym, ze ich merguje, ale pochybujem, ze to bol tvoj pripad... Urcite je lepsie spravit jeden index na oba stlpce v prepojovacich tabulkach v takom poradi, aby to databaza mohla vytiahnut z indexu a nemusela sahat do dat. V pripade MyISAM su data kesovane len na urovni systemu, takze sa nevyhnes systemovym volaniam, narozdiel od pristupu do indexu.
-
ani vediet, co si predstavujes pod pojmom "fungovalo to spolahlivo". MySQL vie od verzie 5.0 pouzit v specialnych pripadoch aj dva indexy s tym, ze ich merguje, ale pochybujem, ze to bol tvoj pripad... Urcite je lepsie spravit jeden index na oba stlpce v prepojovacich tabulkach v takom poradi, aby to databaza mohla vytiahnut z indexu a nemusela sahat do dat. V pripade MyISAM su data kesovane len na urovni systemu, takze sa nevyhnes systemovym volaniam, narozdiel od pristupu do indexu.
ak to nechces vediet, nepoviem. ale ak by si chcel, povedal by som, ze som na vlastne oci videl, ako mi aj druhy index vyberal priamo data podla indexu a neprechadzal vsetky zaznamy vybrane prvym indexom. ale keby si precitas poriadne celu debatu, vedel by si, ze myslim a predstavujem si presne toto:)
-
pecko: On je index něco jako trpaslík? Ehm? Že jsi ho viděl v akci?
Zajímalo by mě, jak se to provádí. Mám dva indexy, jeden z nich použiju a vyselektnim nějaký řádky. Pak mám možnost: buďto všechny vyselektěný řádky porovnám na druhou podmínku: to má složitost n*s, kde n je počet záznamů odpovídají první podmínce a s je složitost zjištění druhé podmínky.
Anebo vyselektím záznamy pomocí druhého indexu, ty pak ale ještě musím setřídit podle prvního indexu a mergnout. Složitost je tedy
m*(log m) + m + n
kde m je velikost složky druhého indexu. Pokud má db dobře statistiky, tak je navíc m > n. I při ideálním případě (m=n) se to ale vyplatí jen v případě, kdy složitost porovnání druhé podmínky je řádově větší než log(m)+2, spíše ani tak ne, protože to vyžaduje více paměti na zpracování a tedy to víc zatíží paměťový subsystém.
tomas:
Lidi nenadávaj na mysql kvůli tomu, že dělaj blbej návrh (i když někdo asi taky), ale proto, že veškerý složitější věci jsou v ní problém. Namátkou
Triggery: jsou. Ale nedaj se použít, protože se spustěj jen někdy. Navíc nemůžou bejt dva.
Fulltex: je. Ale nedá se použít, protože je jen nad netransakčníma tabulkama.
CTE: nejsou
Transakce: jsou. Ale implementace vyšších stupňů izolace je přinejmenším podivná (pro jistotu se všechno zamkne)
atd....
Mysql je dobrá jako jednoduché úložiště dat. Pro složitější věci se ale prostě nehodí...
-
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..
Tak som to vyskusal:
Najprv som vytvoril DB tabulku typu MyISAM:
CREATE TABLE `tbl_test` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`c1` TINYINT(3) UNSIGNED NOT NULL,
`c2` TINYINT(3) UNSIGNED NOT NULL,
PRIMARY KEY (`id`),
INDEX `ind_c1` (`c1`),
INDEX `ind_c2` (`c2`)
);
Tie indexy boli vytvorene az dodatocne po vlozeni 10 000 000 zaznamov.
ALTER TABLE `tbl_test` ADD INDEX `ind_c1` (`c1`), ADD INDEX `ind_c2` (`c2`);
SELECT COUNT(*) FROM tbl_test;
9999999
Ops, takze nieje ich presne 10 000 000 ale o jeden menej. ;)
mysql> EXPLAIN SELECT COUNT(*) FROM tbl_test WHERE c1=0 AND c2=0;
+----+-------------+----------+------+---------------+--------+---------+-------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+--------+---------+-------+---------+-------------+
| 1 | SIMPLE | tbl_test | ref | ind_c1,ind_c2 | ind_c2 | 1 | const | 4692634 | Using where |
+----+-------------+----------+------+---------------+--------+---------+-------+---------+-------------+
1 row in set (0.00 sec)
Niesom navrhar db tak mi mozete vysvetlil kto mal teda pravdu? Nechce sa mi citat manual k explain...
-
Tak som si ten vystup EXPLAIN-u nastudoval a pravdu ma VM. Pouzije sa iba jeden index (v tomto pripade ind_c2) a mysql musi potom este prebehnut 4692634 zaznamov.
Vyskusal som aj tu query spustit:
mysql> SELECT COUNT(*) FROM tbl_test WHERE c1=0 AND c2=0;
+----------+
| COUNT(*) |
+----------+
| 2501665 |
+----------+
1 row in set (8.56 sec)
A fakto to trvalo dlho (8.56 sec), ak by sa pouzili obidva indexy tak by vysledok mala vratit "hned".
-
Myslím že jsem vyhrál.
K tomu explainu (návod je na http://dev.mysql.com/doc/refman/5.1/en/explain-output.html ):
possible_keys ind_c1,ind_c2 říká, že si optimalizátor vybíral z těchto dvou indexů
key ind_c2 říká, že se použil tento index
rows 4692634 znamená, že se během dotazu musel prohledat tento počet řádků (tj. výsledky výběru indexem ind_c2)
Čas přes 8 sekund potvrzuje, že se musela prohledat půlka tabulky, pro indexový přístup by to bylo ve zlomcích sekundy.
Pro potvrzení vyrobte index na (c1,c2), a vložte sem výsledky toho EXPLAINu i čas SELECTu - mělo by se to dramaticky lišit.
-
Vytvorim zlozeny index ind_c1c2 nad stlpacim c1 a c2:
ALTER TABLE `tbl_test` ADD INDEX `ind_c1c2` (`c1`, `c2`);
mysql> EXPLAIN SELECT COUNT(*) FROM tbl_test WHERE c1=0 AND c2=0;
+----+-------------+----------+------+------------------------+----------+---------+-------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+------------------------+----------+---------+-------------+---------+-------------+
| 1 | SIMPLE | tbl_test | ref | ind_c1,ind_c2,ind_c1c2 | ind_c1c2 | 2 | const,const | 2374873 | Using index |
+----+-------------+----------+------+------------------------+----------+---------+-------------+---------+-------------+
1 row in set (0.03 sec)
a samotny SELECt je potom velmi rychly:
mysql> SELECT COUNT(*) FROM tbl_test WHERE c1=0 AND c2=0;
+----------+
| COUNT(*) |
+----------+
| 2501665 |
+----------+
1 row in set (0.77 sec)
-
Developer, diky za namahu, me se nechtelo :)
USING WHERE v tom explainu znamena, ze se pouzil jeden z indexu na vytvoreni podmnoziny dat, a zbytek filtru se vyhledal hrubou silou v te podmnozine. Tudiz optimalizace neni nulova, bude to rychlejsi nez kdyby index nebyl zadny, ale nikdy to nebude tak dobre jako slozeny index (nejlepe UNIQUE) na obou sloupcich (paklize filtr v selectu hodnoti oba sloupce).
Pecko, ja nezpochybnuju uzitecnost studovani teoretickych znalosti, ale veskera normalizace je k nicemu kdyz nevis, ze select na 2 samostane indexy je neoptimalni v porovnani se selectem na 1 slozeny index, a troufam si tvrdit, ze v tomto pripade NEZALEZI na databazi. Argument "urcite to fungovalo" je smesny.
-
Význam těch dvou indexů je čistě jen takový, že si optimalizátor podle statistik může vybrat,, kterej použije. Jinak je to nämlich to samý, jako by tam byl jen jeden index.
Jestli je index UNIQUE nebo ne v optimalizaci příliš nehraje roli, v podstatě jediný význam je ten, že optimalizátor ví, že když zvolí tendle index, tak získá pouze jednu řádku. To ale s rozumně aktuálnáíma statistikama bude vědět taky.
Btw., ono když už mám index, tak je (skoro) vždycky rozumný ho udělat přes víc sloupců, každá rozumná databáze umí použít z vícesloupcového indexu začátek a rozdíl ve velikosti indexů není takový.
-
Ještě je jedn důvod, proč použít primární klíč. Většina databázových engine, innodb nevyjímaje, používá fyzické řazení záznamů dle primárního klíče. Pokud se tedy tabulka hodně mění, tak umělý primární klíč (furt rostoucí do nekonečna) zajistí, že se při inzetru nebudou tak často muset štěpit stránky. Zápis na konec je vždy jednodušší, než doprostřed.
Výhoda složeného klíče by byla při stabilní tabulce, ale protože většina enginů umí použít přímo data z klíče, nebude muset datovou oblast číst vůbec, takže tato výhoda padá.
-
Ještě je jedn důvod, proč použít primární klíč. Většina databázových engine, innodb nevyjímaje, používá fyzické řazení záznamů dle primárního klíče.
To je IMHO docela odvážné tvrzení. Byl by nějaký odkaz?
Něco jiného by bylo u indexů. Tam se většinou používají B-stromy a u nich se vzrůstající index ukládá mnohem rychleji, zejména kvůli využití cache.
-
No, s většinou jsem možná přestřelil, ale Innodb to tak má
http://me.in-berlin.de/doc/mysql-doc/manual_InnoDB.html
(hledej clustered index)
MS SQLServer taky s opt-outem
http://msdn.microsoft.com/en-us/library/aa933131%28v=sql.80%29.aspx
a Oracle s opt-inem. Postgresql co vím nikoli.
-
V postgreSQL je podobný mechanismus,
http://www.postgresonline.com/journal/archives/10-How-does-CLUSTER-ON-improve-index-performance.html
ale musí se to dělat částečně manuálně.
-
No, s většinou jsem možná přestřelil, ale Innodb to tak má
http://me.in-berlin.de/doc/mysql-doc/manual_InnoDB.html
(hledej clustered index)
Podle tohoto zdroje je primární index vytvářen vždy. Tedy i v případě, že není explicitně definován. Data jsou součástí B-stromu, s velkou pravděpodobností je tedy snížen počet čtení diskových bloků. Fyzické řazení dat na disku však nemusí být úplně sekvenční ani při postupném zvyšování (např. syntetického) primárního klíče. Efektivita uložení (a následného vyhledávání) dat s použitím syntetického nebo monotónního klíče je však vyšší.
Ohledně obsazení paměti je tedy jedno, jestli deklaruji nebo nedeklaruji primární klíč. Pokud ho deklaruji, mám k němu z aplikace přístup. Je tedy výhodnější ho deklarovat.
-
RowId je innodb vždy, i když je definován primární klíč, akorát neviditelně. Pokud je definován unique klíč a ne primární klíč, je clusterován ten unique klíč - ale tady jsme se bavili o rozdílu složený versus jednoduchý pkey. Deklarace tedy zabere více místa na disku (a tedy i v paměti).
Co se týče vyhledání dat, tak ta se budou vždy vyhledávat podle indexu a zajímat nás budou pouze data z indexu, takže je vyhledávání stejně rychlé s existencí i bez existence autoinkrementního políčka (v Mysql).
Co je výhodnější pro updaty je ale vlastně otázka, protože sice update clusterovaného indexu je jednodušší s jednoduchým klíčem, ale zas tam je o index navíc...
-
A pokud mi je sloupec s auto increment ID k nicemu a v aplikaci ho nepouziju a mam moznost jednoznacne identifikovat radek tabulky pomoci jineho klice (treba slozeneho z vice sloupcu), tak se muzu na ID vykaslat a MySQL s tim NEMA nejmensi problem. A pokud nekdo tvrdi ze ma, tak at laskave odkaze na nejakou analyzu.
On se generovanej klic muze leckdy hodit. Treba kdyz mam tabulku kde PK by mohl byt nejaky sloupec - treba VARCHAR(100) - tak kdyz budu se budu na tuto tabulku v dalsich x tabulkach odkazovat, tak je sakra rozdil jestli to bude pres VARCHAR(100) nebo INT. Krome velikosti dat v tabulkach mi take narostou indexy, a vsechno zabere vic pameti a bude pomalejsi.