LINUXSOFT.cz Přeskoč levou lištu
Uživatel: Heslo:  
   CZUKPL

> MySQL (31) - Indexy

Co to je, k čemu je to dobré a jak to funguje. Indexy v databázích a jejich tvorba v MySQL.

29.7.2005 07:00 | Petr Zajíc | Články autora | přečteno 38645×

Komerční sdělení: Pořádáme Kurzy MySQL

Původně jsem se v dalším pokračování článku chtěl věnovat něčemu trochu jinému, ale dotazy čtenářů mě přesvědčily, že je čas podívat se na způsob, jakým se v MySQL pracuje s indexy. Protože ale někteří možná neví, k čemu indexy v databází přesně slouží, začněmě nějakou tou teorií.

Co jsou indexy

Suchá definice praví, že index je pomocná datová struktura, která určuje pozici dat v tabulce na základě jejich hodnoty. Je to definice jak se patří, což znamená, že si pod ní většina lidí nepředstaví zhola nic ;-). Pojďme si to přirovnat k situaci z reálného světa. Představte si obrovskou knihovnu, do níž si přijdete půjčit nějakou tu  detektivku. Kdyby knihy v knihovně nebyly nijak srovnány (neboli "indexovány"), nezbylo by vám nic jiného, než vzít do ruky každou knihu a podívat se, zda je to ta, kterou jste si chtěli půjčit. V podstatě ve stejné situaci je databáze, když má vybrat data z tabulky, na níž není definován žádný index - musí zkrátka celou tabulkou projít.

Jiná situace nastane, když budou knížky v knihovně srovnány, seřazeny. To vám potom může knihovnice říct, že "detektivky jsou vzadu vpravo", a vám zůstane na procházení mnohem méně knih. To je právě index - všimněte si, že určuje pozici dat ("vzadu vpravo") na základě jejich hodnoty ("detektivky"). V databázi slouží indexy v podstatě ke stejné věci - k urychlení hledání mezi mnoha záznamy.

Z toho, co bylo řečeno, by se mohlo na první pohled zdát, že indexy by měly být v databázi skoro všude - vždyť je to dobrá věc. Jako ale všechno na světě, i indexy mají svou druhou stranu mince. Jakou? Vraťme se ke znázornění s knihovnou. Když knihovna získá nové výtisky, musí je správně zařadit - detektivky na své místo a tak dále - a to je mnohem pomalejší, než kdyby se knížky "házely na jednu hromadu" nebo by se dávaly do polic tak, jak přijdou. Úplně stejné je to v databázích - pro akční dotazy (INSERT, UPDATE a DELETE) platí, že indexy tyto operace obecně zpomalují.

Vícesloupcové indexy

V databázi není nutné mít index jen na jednom sloupci. Když se opět vrátím ke knihovně, může to být například tak, že "detektivky jsou vzadu vpravo" a zároveň "detektivky zahraničních autorů jsou v polici nahoře". V takovém případě máme vlasně index na dvou hodnotách najednou, na "detektivkách" a "zemi původu". I v databázích se běžně pracuje s indexy, které pokrývají data ve více sloupcích - a říká se jim podle toho vícesloupcové indexy. Samozřejmě, že vícesloupcové indexy mají reálný význam pouze v případě, kdy vyhledáváme data pomocí hodnot v indexovaných sloupcích.

Více indexů na tabulce

Je třeba si rovněž uvědomit, že ve světě databází není problém mít více indexů na jedné tabulce - za předpokladu, že se týkají jiných sloupců. Tady již analogie s knihovnou pokulhává, ale přesto to můžeme znázornit. Knihovnice může mít k dispozici katalog všech detektivek s jejich umístěním (bez ohledu na to, zda pocházejí z pera našich autorů nebo ze zahraničí), a zároveň může mít jiný katalog všech zahraničních knih v knihovně (bez ohledu na to, zda se jedná o detektivku nebo cokoli jiného). Podobně v tabulce můžete mít jeden index na poli příjmení a úplně jiný index na poli rodné číslo.

Indexy a MySQL

Moderní databázové systémy - MySQL nevyjímaje - fungují tak, že index můžete vytvořit při definici tabulky (nebo i později) a databáze se pak o tento index stará. Když říkám "stará", tak to v praxi znamená dvě věci:

  1. Databáze index udržuje platný. Při změně indexovaných dat se změní i příslušný index nebo indexy. Což, pochopitelně, stojí nějaký ten čas.
  2. Databáze při požadavku na vrácení nebo úpravu dat rozhodne, zda je rychlejší použít pro tuto operaci index nebo procházení tabulkou. Pokud dojde k závěru, že je to rychlejší, použije hledání dat pomocí indexů podobně, jako knihovnice, když vám hledá konkrétní publikaci podle nějakého katalogu.

V MySQL existují celkem čtyři typy indexů. Jsou to:

  1. Ordinální indexy - to je jiný výraz pro klasický index tak, jak jsem jej popisoval.
  2. Jedinečné indexy - indexy, které kromě své klasické funkce hlídají i to, aby se žádná z hodnot v indexovaném sloupci neopakovala. Nebo, aby se v případě vícesloupcového indexu neopakovala kombinace hodnot v indexovaných sloupcích. Na jedné tabulce smí být definováno více jedinečných indexů.
  3. Primární klíč - primární klíč je jedinečný index na poli, v němž je každá hodnota jedinečná a žádná hodnota není neznámá (NULL). Každá tabulka smí mít nejvýše jeden primární klíč.
  4. Fulltextový index - slouží k prohledávání fulltextu a budeme o něm mluvit později v tomto seriálu.

Definovat index při založení tabulky můžete pomocí následující syntaxe:

create table knihy (nazev varchar (50), zanr varchar(50), index (zanr));

Máte-li existující tabulku, můžete do ní index přidat takto (v příkladu předpokládáme, že existuje tabulka knih ale bez indexu):

alter table knihy add index (zanr);

Tyto příkazy způsobí, že od nynějška začne databáze udržovat index všech hodnot na sloupci "žánr", a to dokud index neodstraníme. Pokud bychom toužili zbavit tabulku břímě indexu, poslouží nám k tomu syntaxe ve smyslu:

alter table knihy drop index zanr;

Tyto operace lze provést bez ohledu na to, zda a jaká data tabulka obsahuje. S existujícími daty se v žádném případě nic nestane.

Pozn.: Pozor na ty závorky. V prvním případě jsou, ve druhém nikoli. Tady se programátoři MySQL moc nevyznamenali, dodnes se mi to plete. Naštěstí se indexy neruší tak často, aby mi to stálo za zapamatování.

Obdobně jako ordinální index lze v tabulce definovat i unikátní (neboli jedinečný) index - pouze je třeba myslet na to, že se definuje klíčovým slovem UNIQUE namísto INDEX. Vytvoření probíhá takto:

create table knihy (nazev varchar (50), zanr varchar(50), unique (zanr));

přidání unikátního indexu do již existující tabulky takhle:

alter table knihy add unique (zanr);

Ale pozor - odstranění unikátního indexu probíhá stejně jako odstranění ordinálního indexu, takže ne DROP UNIQUE, ale DROP INDEX. U unikátních indexů je třeba pamatovat ještě na jednu věc - pokud se pokusíme definovat unikátní index na sloupci, který již obsahuje data, a tento sloupec navíc obsahuje duplicitní údaje, příkaz pro vytvoření unikátního indexu selže. Na prázdné tabulce pochopitelně příkaz uspěje, protože ta neobsahuje žádná data, natož nějaké duplicity.

V dalším díle seriálu se podíváme na primární klíče. Také si porovnáme výkon výběrových a aktualizačních dotazů při použití indexů a bez nich, takže se máte na co těšit.

Verze pro tisk

pridej.cz

 

DISKUZE

A nesmi se to s nima prehanet 29.7.2005 09:25 MaReK Olšavský
L Re: A nesmi se to s nima prehanet 29.7.2005 09:56 Petr Zajíc
  L Re: A nesmi se to s nima prehanet 29.7.2005 10:04 MaReK Olšavský
Závorky u indexů 30.7.2005 12:01 Jarek Šeděnka
L Re: Závorky u indexů 30.7.2005 12:49 Petr Zajíc
Unikátní indexy a NULL 30.7.2005 12:07 Jarek Šeděnka
  L Re: Unikátní indexy a NULL 30.7.2005 12:46 Petr Zajíc




Příspívat do diskuze mohou pouze registrovaní uživatelé.
> Vyhledávání software
> Vyhledávání článků

15.5.2017 23:50 /František Kučera

Máš rád svobodný software a hardware nebo se o nich chceš něco dozvědět? Zajímá tě DIY, CNC, SDR nebo morseovka? Přijď na sraz spolku OpenAlt, který se bude konat ve čtvrtek 18. května od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5).


Přidat komentář

12.5.2017 16:42 /Honza Javorek
PyCon CZ, česká konference o programovacím jazyce Python, se po dvou úspěšných ročnících v Brně bude letos konat v Praze, a to 8. až 10. června. Na konferenci letos zavítá např. i Armin Ronacher, známý především jako autor frameworku Flask, šablon Jinja2/Twig, a dalších projektů. Těšit se můžete na přednášky o datové analytice, tvorbě webu, testování, tvorbě API, učení a mentorování programování, přednášky o rozvoji komunity, o použití Pythonu ve vědě nebo k ovládání nejrůznějších zařízení (MicroPython). Na vlastní prsty si můžete na workshopech vyzkoušet postavit Pythonem ovládaného robota, naučit se učit šestileté děti programovat, efektivně testovat nebo si v Pythonu pohrát s kartografickým materiálem. Kupujte lístky, dokud jsou.
Přidat komentář

2.5.2017 9:20 /Eva Rázgová
Putovní konference československé Drupal komunity "DrupalCamp Československo" se tentokrát koná 27. 5.2017 na VUT FIT v Brně. Můžete načerpat a vyměnit si zkušenosti z oblasti Drupalu 7 a 8, UX, SEO, managementu týmového vývoje, využití Dockeru pro Drupal a dalších. Vítáni jsou nováčci i experti. Akci pořádají Slovenská Drupal Asociácia a česká Asociace pro Drupal. Registrace na webu .
Přidat komentář

1.5.2017 20:31 /Pavel `Goldenfish' Kysilka
PR: 25.5.2017 proběhne v Praze konference na téma Firemní informační systémy. Hlavními tématy jsou: Informační systémy s vlastní inteligencí, efektivní práce s dokumenty, mobilní přístup k datům nebo využívání cloudu.
Přidat komentář

15.4.2017 15:20 /František Kučera
Máš rád svobodný software a hardware nebo se o nich chceš něco dozvědět? Zajímá tě IoT a radiokomunikace? Přijď na sraz spolku OpenAlt, který se bude konat ve středu 19. dubna od 18:30 v Šenkovně (Sokolská 60, Praha 2).
Přidat komentář

5.3.2017 19:12 /Redakce Linuxsoft.cz
PR: 23. března proběhne v Praze konferenci na téma Cloud computing v praxi. Hlavními tématy jsou: Nejžhavější trendy v oblasti cloudu a cloudových řešení, Moderní cloudové služby, Infrastruktura současných cloudů, Efektivní využití cloudu, Nástrahy cloudových řešení a jak se jim vyhnout.
Přidat komentář

27.2.2017 22:12 /František Kučera
Pozvánka na 137. sraz OpenAlt – Praha: Tentokrát jsme si pro vás připravili neobvyklou akci. Ve středu 1.3. v 17:30 nás přivítá sdružení CZ.NIC ve svých prostorách v Milešovské ulici číslo 5 na Praze 3, kde si pro nás připravili krátkou prezentaci jejich činnosti. Následně navštívíme jejich datacentrum pod Žižkovskou věží. Provedou nás prostory, které jsou běžnému smrtelníkovi nedostupné!
Po ukončení prohlídky se všchni odebereme do hostince U vodoucha, Jagelonská 21, Praha 3 pochutnat si na některém z vybraných piv či dát si něco na zub. Rezervaci máme od 19:30, heslo je OpenAlt.
Ale pozor! Do prostor datového centra máme omezený přístup, dostane se tam pouze 10 lidí! Takže kdo přijde dříve, ten má přednost, a občanky s sebou! Kdo nebude chtít na prohlídku datového centra, může se pomalu přesunout do hostince U vodoucha a u nepřeberné nabídky piv počkat na ostatní.
Přidat komentář

18.1.2017 0:49 /František Kučera
Členové a příznivci spolku OpenAlt se pravidelně schází v Praze a Brně. Fotky z pražských srazů za uplynulý rok si můžete prohlédnout na stránkách spolku. Příští sraz se koná už 19. ledna – tentokrát je tématem ergonomie ovládání počítače – tzn. klávesnice, myši a další zařízení. Také budete mít příležitost si prohlédnout pražský hackerspace Brmlab.
Přidat komentář

   Více ...   Přidat zprávičku

> Poslední diskuze

11.5.2017 23:32 / lelo
Re: Problém se správcem balíčků

11.5.2017 5:45 / davd mašek
Re: Problém se správcem balíčků

10.5.2017 22:54 / lelo
Re: Problém se správcem balíčků

10.5.2017 22:19 / davd mašek
Problém se správcem balíčků

17.4.2017 19:15 / Jakub shoop
chyba

Více ...

ISSN 1801-3805 | Provozovatel: Pavel Kysilka, IČ: 72868490 (2003-2017) | mail at linuxsoft dot cz | Design: www.megadesign.cz | Textová verze