MySQL a použití sloupce ID

[pecko]

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

[Reklama]

[Tomas Matejicek]

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.

[pecko]

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.

[pribinacik]

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.

[pecko]

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:)

[Reklama]

[Logik]

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í...

[Developer]

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. 

Kód: [Vybrat]

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...

[Developer]

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:

Kód: [Vybrat]

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".

[VM]

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.

[Developer]

Vytvorim zlozeny index ind_c1c2 nad stlpacim c1 a c2:
ALTER TABLE `tbl_test`  ADD INDEX `ind_c1c2` (`c1`, `c2`);

Kód: [Vybrat]

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:

Kód: [Vybrat]

mysql> SELECT COUNT(*) FROM tbl_test WHERE c1=0 AND c2=0;
+----------+
| COUNT(*) |
+----------+
|  2501665 |
+----------+
1 row in set (0.77 sec)

[Tomas Matejicek]

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.

[Logik]

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ý.

[Logik]

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á.

[Kit]

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.

[Logik]

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.