Vysvětlete mi indexování v databázi

Kit

Re:Vysvětlete mi indexování v databázi
« Odpověď #15 kdy: 15. 06. 2018, 11:44:00 »
Index v SQL lze vytvořit i bez požadavku na unikátnost. Proto souhlasím s tazatelem, že pak záznam v takovém indexu (např. btree koncový list) odkazuje na množinu/kolekci záznamů v "hostitelské" tabulce. A příznivě se projeví na rychlosti selectů / joinů apod. nad takto indexovaným sloupcem = neunikátní index je lepší než žádný index. A že není unikátní, to vyplývá z reality = z dat, která ten sloupec uchovává.

Například jméno zákazníka rozhodně není unikátní a přitom se obvykle vyplatí ho indexovat. Dotaz přes takový index pak vrací množinu relací, které dotazu vyhovují, zatímco přes unikátní index vrací množinu s žádnou nebo jednou relací.

Když chci z databáze vytáhnout seznam položek faktury, tak je také netahám po jedné.


Re:Vysvětlete mi indexování v databázi
« Odpověď #16 kdy: 15. 06. 2018, 11:56:50 »
Vy to ale pořád píšete opačně. Index primárně slouží pro vyhledávání (a tudíž není unikátní). A s pomocí toho indexu se dá nastavit omezení na sloupci, aby byl unikátní, pro což se pak používá zkratka „unikátní index“. Samozřejmě, že pro vyhledávání je lepší mít index než ho nemít. A různé typy indexů mohou být pro určitý typ dat lepší než jiné – a unikátní index může být o něco efektivnější, než neunikátní. Což je ale jedno, protože unikátní index se nastavuje především kvůli tomu omezení unikátnosti dat.

Honza

Re:Vysvětlete mi indexování v databázi
« Odpověď #17 kdy: 15. 06. 2018, 12:40:18 »
S tvrzenim ze mit index se vzdy pro select vyplati bych byl velmi opatrny, protoze to neni pravda. Zalezi v prvni rade na datech (resp na jejich kardinalite/selectivite). Pokud totiz budu mit napriklad sloupec se jmeny a budu chtit podle nej vyhledavat tak v pripade ze budu  it 100 hodnot a jmeno honza se bude vyskytovat v 50 z nich pak full scan bude rychlejs protoze hledani pres index jsou jen i/o navici. Pokud ovsem ve stejne tabulce bude jirka jen jednou a budu pro nej vyhledavat zaznam pak hledani dle indexu bude lepsi volbou. Zalezi tedy na datech a jak vyhledavam. Pokud mam unikatni data a chci one row je index jasna volba.

Jeste bych pro uplnost zminil ze nejsou pouze btree index ale pouzivaji se tez bitmap indexy nebu function based (klapu na mobilu nekdo dalsi necht pls vysvetli)

Re:Vysvětlete mi indexování v databázi
« Odpověď #18 kdy: 15. 06. 2018, 13:13:22 »
Vy to ale pořád píšete opačně. Index primárně slouží pro vyhledávání (a tudíž není unikátní). A s pomocí toho indexu se dá nastavit omezení na sloupci, aby byl unikátní, pro což se pak používá zkratka „unikátní index“. Samozřejmě, že pro vyhledávání je lepší mít index než ho nemít. A různé typy indexů mohou být pro určitý typ dat lepší než jiné – a unikátní index může být o něco efektivnější, než neunikátní. Což je ale jedno, protože unikátní index se nastavuje především kvůli tomu omezení unikátnosti dat.

Myslím, že jsem konečně pochopil, o co Vám jde. Děkuji za vysvětlení, že kočár patří za koně :-)

Kit

Re:Vysvětlete mi indexování v databázi
« Odpověď #19 kdy: 15. 06. 2018, 13:18:51 »
Pokud ovsem ve stejne tabulce bude jirka jen jednou a budu pro nej vyhledavat zaznam pak hledani dle indexu bude lepsi volbou. Zalezi tedy na datech a jak vyhledavam. Pokud mam unikatni data a chci one row je index jasna volba.

Ne tak docela. Pro 100 záznamů je index zbytečný, praktičtější je full-scan. Pokud tedy budu mít tabulku se sloupci ID a jméno, tak použiji primární index pro ID a hotovo. Jména mohu prohledávat sekvenčně, protože další index to neurychlí.


pb.

Re:Vysvětlete mi indexování v databázi
« Odpověď #20 kdy: 15. 06. 2018, 13:35:54 »
Indexy navíc obvykle bývají v cache, což přináší další zrychlení.
Nebo v jiném tablespace, což také přináší zrychlení. Data lze sypat na velký, levný HDD, indexy pak na dražší, menší, ale rychlejší SSD...

Re:Vysvětlete mi indexování v databázi
« Odpověď #21 kdy: 15. 06. 2018, 13:43:37 »
Index se prakticky nikdy nenavrhuje pro jeden konkrétní dotaz, ale pro množinu očekávaných dotazů. Jestli se konkrétní index použije si databáze rozhodne sama. Pokud bude mít v nějaké tabulce 50 % záznamů hledanou hodnotu, ve většině případů se opravdu nevyplatí index použít. Ale i v takovém případě může být dotaz na tyto hodnoty, kdy se použití indexu vyplatí – třeba když se budu ptát jenom na počet záznamů, nebo když budu z té tabulky potřebovat jen údaje, které jsou v tom indexu.

Pokud mám tabulku, která má jen 100 záznamů, je otázka, jestli vůbec potřebuji databázi.

Ne tak docela. Pro 100 záznamů je index zbytečný, praktičtější je full-scan. Pokud tedy budu mít tabulku se sloupci ID a jméno, tak použiji primární index pro ID a hotovo. Jména mohu prohledávat sekvenčně, protože další index to neurychlí.
Záleží na konkrétním případě, ale obvykle by i v takové situaci index hledání urychlil.

Kit

Re:Vysvětlete mi indexování v databázi
« Odpověď #22 kdy: 15. 06. 2018, 14:09:31 »
Ne tak docela. Pro 100 záznamů je index zbytečný, praktičtější je full-scan. Pokud tedy budu mít tabulku se sloupci ID a jméno, tak použiji primární index pro ID a hotovo. Jména mohu prohledávat sekvenčně, protože další index to neurychlí.
Záleží na konkrétním případě, ale obvykle by i v takové situaci index hledání urychlil.

Jak? Bez indexu se zmíněná data vejdou do jedné alokační jednotky na disku. Když použiji index, je potřeba načíst dvě alokační jednotky, což je pomalejší.

Honza

Re:Vysvětlete mi indexování v databázi
« Odpověď #23 kdy: 15. 06. 2018, 14:11:31 »
heh root nezklamal :) - 100 zaznamu byl samozrejme jen priklad, abych to mel na cem vysvetlit

Kit

Re:Vysvětlete mi indexování v databázi
« Odpověď #24 kdy: 15. 06. 2018, 14:16:38 »
heh root nezklamal :) - 100 zaznamu byl samozrejme jen priklad, abych to mel na cem vysvetlit

Nesváděj to na roota, když jsem se toho chytil já. Už jsem viděl hromadu tabulek s 50 záznamy, ke kterým někdo zbytečně dělal další indexy. Fakt to nemá význam.

Honza

Re:Vysvětlete mi indexování v databázi
« Odpověď #25 kdy: 15. 06. 2018, 14:24:11 »
ehh tak je pravda, ze se v praxi clovek setka clovek s ruznymi zhuverilostmi, ale tak nekdo musi proslapat tu slepou cesticku poznani :)

Re:Vysvětlete mi indexování v databázi
« Odpověď #26 kdy: 15. 06. 2018, 14:27:22 »
Jak? Bez indexu se zmíněná data vejdou do jedné alokační jednotky na disku. Když použiji index, je potřeba načíst dvě alokační jednotky, což je pomalejší.
Jestli budou data tabulky na jedné alokační jednotce disku závisí na tom, jak budou na disku zapsaná. Záznamy mohou být nějak zarovnané, navíc 100 záznamů neznamená, že jsou jeden za druhým – může tam být např. prázdné místo po smazaných záznamech, nebo se prostě záznamy jedné tabulky nezapisují souvisle za sebou.

Když se použije index, je větší pravděpodobnost, že budou data u sebe v jedné alokační jednotce disku. A explicitně jsem psal o případu, kdy jsou požadovaná data v indexu – takže odpadá čtení dat z tabulky, které mlčky předpokládáte.

gnat

Re:Vysvětlete mi indexování v databázi
« Odpověď #27 kdy: 15. 06. 2018, 15:15:30 »
Indexovat tabulku se 100 záznamy nemusí být nesmysl, ale naopak zcela racionální rozhodnutí.
1. spousta optimalizátoru dotazů nesmyslné penalizuje full table scan a u složitějších hodinu pak upřednostňuje málo selektivni scan obrovských indexu na velkých tabulkách před vysoce selektivnim full scanem minitabulky
2. cache vetsinou uprednostnuje indexy před datovými bloky a pokud jdu do tabulky jen pro indexovanou hodnotu, moderní engine ji vrátí přímo z indexu bez načítání datového bloku
3. i nad malou tabulkou můžu mít komplikovaný výraz, který může být výpočetně dráhy a má smysl použít funkcionální index

Kit

Re:Vysvětlete mi indexování v databázi
« Odpověď #28 kdy: 15. 06. 2018, 15:36:33 »
Indexovat tabulku se 100 záznamy nemusí být nesmysl, ale naopak zcela racionální rozhodnutí.
1. spousta optimalizátoru dotazů nesmyslné penalizuje full table scan a u složitějších hodinu pak upřednostňuje málo selektivni scan obrovských indexu na velkých tabulkách před vysoce selektivnim full scanem minitabulky
2. cache vetsinou uprednostnuje indexy před datovými bloky a pokud jdu do tabulky jen pro indexovanou hodnotu, moderní engine ji vrátí přímo z indexu bez načítání datového bloku
3. i nad malou tabulkou můžu mít komplikovaný výraz, který může být výpočetně dráhy a má smysl použít funkcionální index

Souhlasím s těmito výjimkami z uvedeného pravidla, že do 100 relací sekundární indexy v tabulce nepotřebuji.

Lothic

Re:Vysvětlete mi indexování v databázi
« Odpověď #29 kdy: 15. 06. 2018, 18:14:40 »
Ještě bych měl otázku. Jak je to z hlediska práce s pamětí? Respektive, když použiji indexování, tak jak probíhá práce s pamětí? Je v tomhle směru nějaká výhoda vůči práce bez indexace?