Dotaz: mysql rozdelenie zaznamov do tabuliek podla ich stavu

[adrinko]

ahojte,

mam jednu mysql databazovu otazku.

tabulka_1: ID (auto increment), hrac, stav Stale pridavam hracov do tabulky (ktori sa neskor spracuju), takze tabulka bude mat desattisice hracov. Pri prvotnom pridani ma kazdy hrac stav s hodnotou 0. Ked sa hrac spracuje, zmeni sa jeho stav na 1.

A teraz otazka: Je lepsie vsetkych hracov, ktori maju stav=1 hned presunut do tabulky povedzme tab_ok a z povodnej tabulka_1 jeho zaznam vymazat? Zrychli to pri velkom pocte pracu mysql? Alebo je to uplne jedno a mozu zostat hraci s oboma stavmi v tabulka_1? Tak ci tak, ked by som odstranil z zabulka_1 cely riadok hraca, auto increment samozrejme jeho ID uz nikdy znova neprida.

Ide o to, aby to aspon trosku ulahcilo pracu mysql. Ma to teda zmysel? Prinajmensom mi vychadza, ze to ma aspon zmysel z hladiska prehladnosti.

vdaka moc za reakcie

Odpovědi

[kuka]

Klicova otazka zde je, praci s cim by to melo usetrit. Tzn. budou napriklad caste dotazy podle stavu? A co je to zpracovani hrace, je to klicovy okamzik z hlediska aplikace, napriklad jen zpracovany hrac muze hrat apod.? Tedy pak by tam byla treba i vazba na referencni integritu, protoze hrac se stavem 0 by nemohl mit zapasy nebo co ja vim. Pokud nepopises problem, tak ti opravdu nikdo nerekne, jestli je lepsi mit jednu tabulku nebo treba deset.

[adrinko]

vdaka za reakciu. ano, dotazy budu caste podla stavu (a podla dalsich kriterii, ja som spomenul len tie zakladne ako ID, hrac, stav, ale samozrejme tam bude viac stlpcov). Napr kazdy hrac bude patrit do klubu, takze budu dotazy aj na zoradenie podla klubov. Tiez napr tam mozu byt stlpce ako polia oci a na to tiez dotazy.

[kuka]

Pokud je to atribut na urovni jinych atributu, tak urcite nema smysl to podle nej delit do tabulek, to by ses pak musel pokazde dotazovat do vice tabulek, kdyz bys chtel treba vsechny hrace klubu, cimz bys databazi praci spise pridelal, nehlede na neprehlednost. Tabulky by mely zhruba odpovidat vecnym entitam. Rozdeleni do vice tabulek ma tedy smysl, pokud je hrac ve stavu 0 jina entita nez hrac ve stavu 1, napr. muze byt pouze smazan nebo preveden do stavu 1 (napr. po konfirmaci emailu pri online registraci), ale nemuze delat veci, ktere muze delat hrac ve stavu 1, treba hrat zapasy.

[VM]

Určitě bych to nedělil - mít několik podobných tabulek situaci spíše znepřehlední. Je ale potřeba dobře navrhnout indexy, aby pokrývaly všechny dotazy.

[Kit]

Desítky tisíc záznamů jsou pro databázi SQL na hranici rozlišitelnosti, zda mají indexy (jiné než primární) smysl nebo nemají. Takže rozhodně bych se nesnažil indexy pokrýt VŠECHNY dotazy, ale jenom ty, u kterých to má význam. Indexy mohou u kratších tabulek způsobit zbytečné prodloužení doby zpracování.

Nedělit.

[l0gik]

No tak to zas myslím přeháníš - když uděláš join přes deset tabulek a z každý budeš chtít jednu řádku, tak index opravdu poznáš... Přecijenom je to řádově deset operací ku deseti tisícům na jednu tabulku.

[Kit]

Deset krát deset tisíc už je sto tisíc. Psal jsem o desítkách tisíc, ne o statisících. Tak mě nechytej za slovo.

Použití indexu je operace navíc, takže ne deset, ale dvacet. Vyhledání hodnoty v indexu má typickou složitost log(n), takže se dostáváme na stovku operací.

Index (kromě primárního) je vlastně další speciální tabulka v databázi. Při každém update je nutné ji aktualizovat. To je další režie.

Použití jednoho indexovaného záznamu je ukázkově výhodné. Pokud takových vyhovujících záznamů je víc než cca 20%, index začne být zbytečnou zátěží. SQL server ho na takový dotaz stejně vypne a hledá sekvenčně.

Nejsem proti indexům. Jen jsem reagoval na nutnost důsledně indexovat na všechny typy dotazů. To rozhodně nutné není, zejména u takové miniaturní databáze.

[l0gik]

Hele, nechci se s Tebou hádat. To co píšeš je ve spoustě věcí, pravda, ale namíchané s řekněme ne tak relevatníma věcma.

- Např. i kdybych bral Tvůj výpočet (kterej je trochu pochybnej, protože sekvenční vyhledávání taky něco stojí), tak furt je to 100x míň.

- Že je index třeba aktualizovat je pravda,. ale v drtivé většině aplikací (a databáze uživatelů k nim evidentně patří) se daleko více čte, než zapisuje.

- Použití jednoho záznamu je ukázkově výhodné, to je pravda. Ale kolik se běžně čte záznamů? Buďto jeden, nebo obsah jedné stránky výpisu (tzn. dvacet). A joiny jsou z velké části právě na jeden záznam.

- Ad chytání za slovo: původní dotaz byl na tabulku, ve které je deset tisíc záznamů. Taková tabulka asi nebude v systému sama. Pokud jsi tedy myslel, že záznamů bude souhrnně 10000, byl jsi OT. Navíc JOIN přeci nemusí zvyšovat počet záznamů: klidně můžu (a u stromové struktury se tak dělá běžně) joinovat tabulku dokola. Myslím, že tady to nejsem já, kdo překrucuje.

- Ano, pokud se čte víc než x% tabulky, index se nepoužije. Kolik takovejch dotazů je? Navíc jak todle souvisí s počtem záznamů v tabulce a i s původním dotazem? To se pouze snažíš najít skulinu, ve který bude Tvé tvrzení platit

------------- Ale hlavně, ono nemáš pravdu ani u jedné tabulky s 10000 záznamů, bez jakýchkoli joinů a čehokoli extra. I tam se u běžných dotazů index pozná. Zkus todle

Zkus (kód pro postgresql) todle: to, co trvá s indexem necelý čtyři sekundy, trvá bez indexu přes dvě stě sekund. Načítám průměrně 25 záznamů - v běžný praxi se málokdy načítá více....

Naplnění tabulky:

CREATE TABLE big (a integer);
insert into big values (1,2,3,4,5,6,7,8);
insert into big select a + (select max(a) from big) from big ;
insert into big select a + (select max(a) from big) from big ;
insert into big select a + (select max(a) from big) from big ;
insert into big select a + (select max(a) from big) from big ;
insert into big select a + (select max(a) from big) from big ;
insert into big select a + (select max(a) from big) from big ;
insert into big select a + (select max(a) from big) from big ;
insert into big select a + (select max(a) from big) from big ;
insert into big select a + (select max(a) from big) from big ;
insert into big select a + (select max(a) from big) from big ;

Test:

DO $$
declare r integer;
declare s integer;
declare t integer;
begin
t=0;
for r in 1..100000 loop
	select sum(a) from big where a between (r % 8000) AND (r % 8000) + r % 50 into s;
	t=t+s;
	end loop;
end;
$$;

A vytvoření indexu:

CREATE INDEX biga ON big(a) ;

Ano, není pravda, že pro každý myslitelný dotaz je lepší použít index. Těch dotazů, které se v praxi používají a je na ně lepší sekvenční scan je ale zanedbatelné minimum. A i takovádle "miniaturní databáze" je dost velká na to, aby definování indexů přineslo významný výkonnostní rozdíl.

[adrinko]

skúsim teda názorný dotaz, či som index pochopil správne mám tabulka_1: ID (auto increment, primary), hrac, stav, datumpridania, team v nej je datumpridania ako unix timestamp

teraz chcem v mysql vybrať mená hráčov podľa toho, či boli pridaní po datumpridania 1307400000 a po 1307492665, a tiež pokiaľ patria do určitého klubu:

SELECT hrac
FROM tabulka_1
WHERE (datumpridania > 1307400000 
AND datumpridania < 1307492665)
AND team = 'barcelona';

mal by som teda nadefinovať index takto:

CREATE INDEX ON tabulka_1 (team, datumpridania) 

je ten index správne nadefinovaný?

[l0gik]

Ahoj, index je správně, ale špatně máš strukturu tabulky. Předpokládám, že máš někde tabulku týmů. Team (spíše doporučuju sloupec nazvat team_id) v tabulce hráč by měl odkazovat na ID týmu, nikoli na název. Zaprve je vyhledávání integerů zpravidla rychlejší (i když to znamená někdy o join víc), jednak když změníš název týmu, nebudeš muset přepisovat celou tabulku hráčů.

Další věc je, že pokud je v sloupeci datum, tak bys k němu měl přistupovat jako k datu. Postgresql ani neumí castnout integer na datum, mysql možná jo, v každým případě se Tim připravuješ zaprve o srozumitelnost dotazů, zadruhý o speciální funkce pracující s datem (různou aritmetiku s daty apod.). Např. pokud bys chtěl hráče přidané za posledních třicet dní nezávisle na denní době (prostě dle data, ne času) tak máš s integerem dost problém.

Dobrý zvyk je také dávat indexům jména (abys je moh případně snadno zrušit či pozměnit). Ono se jim teda nějaký jméno vygeneruje, ale bude dost ugly.

A pak ještě detail,

(datumpridania > 1307400000 AND datumpridania < 1307492665)

lze kratšeji napsat takto:

datumpridania BETWEEN1307400000 AND 1307492665

(teda - BETWEEN AND používá neostré nerovnosti, ale to je v tomto případě asi jedno)

[adrinko]

l0gik, som Ti veľmi zaviazaný za tvoje rady. fakt super.

>>pokud je v sloupeci datum, tak bys k němu měl přistupovat jako k datu

pochopil som, ze tam teda bude lepšie, keď použijem datetime namiesto int, t.j. vo formate 0000-00-00 00:00:00. Je toto potom ok(?):

BETWEEN '2011-01-01' AND '2011-05-30'
BETWEEN '2011-01-01 12:54:21' AND '2011-05-30 01:01:05'

[l0gik]

Jojo - jen pozor na rozdíl date a datetime. Pravděpodobně chceš ukládat čas přidání, asi chceš datetime, v tu chvíli je ale tročku problém s toudle podmínkou:

BETWEEN '2011-01-01' AND '2011-05-30'

'2011-05-30' se konvertuje na datetime a to tak, že se tam přidá čas 00:00:00. Takže BETWEEN '2011-05-30' nevrátí žádné záznamy z posledního dne, které byly založeny po půlnoci. Pokud to nevadí, tak ok. Pokud bys to chtěl přesně

Tak buďto musíš konvertovat datetime sloupec na date (pak bys ale na to musel udělat spešl index). Druhá možnost je udávat místo '2011-05-30' přesné datetime, třeba takhle: '2011-05-31' - interval 1 microsecond popř. rovnou (bys to moh dělat programově) '2011-05-30' + interval 1 day - interval 1 microsecond

Anebo v tomdle případě je asi lepší použít neostré nerovnosti: datum >= '2011-01-01' AND datum < '2011-05-30'

[adrinko]

ahaaa, no jasné, na to automatické pridávanie 00:00:00 som pozabudol. v mojom prípade bude úplne stačiť, keď tam dám podmienku v tvare

BETWEEN '2011-01-01 00:00:00' AND '2011-05-30 23:59:59'

alebo

BETWEEN '2011-01-01 00:00:00' AND '2011-05-31 00:00:00'

(čo by malo vrátiť približne rovnaké výsledky v oboch prípadoch - avšak asi nevráti zápis s hodnotou 2011-05-30 23:59:59, vráti len tie čo sú menšie) vďaka za pomoc

[l0gik]

BETWEEN je neostrá nerovnost. Takže první podmínka dělá to, co asi chceš.

Jo, a sorry za double post, blbo mi spojení.

[adrinko]

vďaka, už mi je to jasné. išiel som akurát nadefinovať ten index CREATE INDEX meno_indexu ON tabulka_1 (team, datumpridania) a napadla mi jedna vec. čo by sa stalo, keby som nadefinoval

CREATE INDEX prvy_index ON tabulka_1 (team) 
CREATE INDEX druhy_index ON tabulka_1 (datumpridania) 
namiesto CREATE INDEX meno_indexu ON tabulka_1 (team, datumpridania) ?

v prípade meno_indexu by sa nejako tie dva stĺpce team+datumpridania previazali? narozdiel od tych prvy_index a druhy_index, kedy sa indexy vytvoria solo pre každý stĺpec? pýtam sa tak blbo, lebo nerozumiem, aký je tam rodiel v tých indexoch :/ ďakujem za trpezlivosť

[l0gik]

Při každém dotazu se použije max jeden index. Takže pokud nadefinuješ složený index, tak se použije při vyhledávání dle obou kritérií, popř. dle prvního. Nepoužije se při vyhledávání dle druhého kritéria. Tzn. Tvůj třetí index zastoupí první index, ale nikoli druhý.

Pokud nadefinuješ dva jednoduché indexy, použije se při vyhledávání dle obou kritérií jen jeden z nich (podle statistik databáze, kterej bude vhodnější), druhá podmínka se pak dohledá sekvenčně (porovnáním všech možností odpovídající první podmínce).

PS: V podstatě se dá říct, že pokud se záznamy nemění nějak divoce pod rukama, je vždy lepší nadefinovat index přes více sloupců tak, aby každý záznam byl v indexu jednoznačně identifikován. Moc to nestojí a při vyhledávání to pomůže.

[kuka]

To urcite obecne takto nejlepsi neni, napriklad proto, ze v tom indexu kazdy sloupc zabira misto, coz ma vliv na rychlost jeho cteni, zalohovani atd. Pokud ma index podporovat urcity typ dotazu, ktere vraceji vetsi mnozstvi zaznamu, tak neni duvod, aby kazdy z nich "jednoznacne identifikoval", muze to navic mast nektere statistiky nad indexy apod.

[l0gik]

Samozřejmě, že je blbina dělat index na kombinace, který se nikdy nepoužijou, ale to zas je snad jasný i oslovi , že jsem to nepředpokládal, že to musím explicitně vyslovit. Pokud se "ale pravidelně" používají v systému nějaký dotazy na obě dvě složky indexu (a to zpravidla platí), tak je volba složených indexů ta, která dá největší poměr cena/výkon. Statistiky to u rozumné databáze nezvoře, protože ty si může bez problémů evidovat po složkách. Jediná nevýhoda je větší velikost indexu, ta je ale furt menší, než dělat složenej a jednoduchej index najednou, popř. zpomalení dané větší velikostí indexu zdaleka není takové jako ztráta času, která by nastala při existenci pouze jednoduchého indexu díky sekvenčnímu zpracování druhé podmínky.

---

PS: V minulym postu jsem to trochu zjednodušil, chytrý databáze (např. postgresql) dokonce uměj kombinovat více indexů: udělaj bitmapovou mapu obou podmínek a pak jí mergnou, ale tadle metoda není zdaleka tak efektivní, jako složený index.

[l0gik]

Jojo - jen pozor na rozdíl date a datetime. Pravděpodobně chceš ukládat čas přidání, asi chceš datetime, v tu chvíli je ale tročku problém s toudle podmínkou:

BETWEEN '2011-01-01' AND '2011-05-30'

'2011-05-30' se konvertuje na datetime a to tak, že se tam přidá čas 00:00:00. Takže BETWEEN '2011-05-30' nevrátí žádné záznamy z posledního dne, které byly založeny po půlnoci. Pokud to nevadí, tak ok. Pokud bys to chtěl přesně

Tak buďto musíš konvertovat datetime sloupec na date (pak bys ale na to musel udělat spešl index). Druhá možnost je udávat místo '2011-05-30' přesné datetime, třeba takhle: '2011-05-31' - interval 1 microsecond popř. rovnou (bys to moh dělat programově) '2011-05-30' + interval 1 day - interval 1 microsecond

Anebo v tomdle případě je asi lepší použít neostré nerovnosti: datum >= '2011-01-01' AND datum < '2011-05-31'

[moo]

Ma to teda zmysel?

Nema.

[Šangala]

To mně přijde docela divné to takto dělit - obecně, a při desítkách tisíc záznamů snad jen na stařičkém pentiu se 64MB RAM
Ohledně přehlednosti: „zrobí to pekný chaos“

[l0gik]

Jojo - až bude hráčů deset milionů, tak můžeš začít přemejšlet o prvních optimalizacích. Do tý doby indexy nakrásně stačej. Teda, oni budou stačit i dlouho potom....

[Michal Kubeček]

Tohle by se možná dalo označit za jednu z nejčastějších začátečnických chyb. Rozdělením stejných záznamů do několika tabulek docílíte jen toho, že v lepším případě budete místo jednoduchých dotazů tvořit nepřehledné, nesrozumitelné a neoptimalizovatelné konstrukce založené na union dvou dotazů přes jednotlivé tabulky. Tedy ve vašem případě, kdy jsou ty tabulky dvě; pokud jich je víc, obvykle to končí iterováním přes jednotlivé tabulky.

Desetitisíce záznamů v tabulce nejsou ani zdaleka důvodem k panice, natož k podobným šíleným konstrukcím. Databáze jsou stavěné na to, aby efektivně pracovaly s daty, a nemá smysl jim házet klacky pod nohy tím, že ze strachu budete uměle komplikovat strukturu těch dat.