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 43921×
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:
- 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.
- 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:
- Ordinální indexy - to je jiný výraz pro klasický index tak, jak
jsem jej popisoval.
- 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ů.
- 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íč.
- 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
|
Příspívat do diskuze mohou pouze registrovaní uživatelé.
|
|

Vyhledávání software

Vyhledávání článků
28.11.2018 23:56 /František Kučera Prosincový sraz spolku OpenAlt se koná ve středu 5.12.2018 od 16:00 na adrese Zikova 1903/4, Praha 6. Tentokrát navštívíme organizaci CESNET. Na programu jsou dvě přednášky: Distribuované úložiště Ceph (Michal Strnad) a Plně šifrovaný disk na moderním systému (Ondřej Caletka). Následně se přesuneme do některé z nedalekých restaurací, kde budeme pokračovat v diskusi.
Komentářů: 1
12.11.2018 21:28 /Redakce Linuxsoft.cz 22. listopadu 2018 se koná v Praze na Karlově náměstí již pátý ročník konference s tématem Datová centra pro business, která nabídne odpovědi na aktuální a často řešené otázky: Jaké jsou aktuální trendy v oblasti datových center a jak je optimálně využít pro vlastní prospěch? Jak si zajistit odpovídající služby datových center? Podle jakých kritérií vybírat dodavatele služeb? Jak volit vhodné součásti infrastruktury při budování či rozšiřování vlastního datového centra? Jak efektivně datové centrum spravovat? Jak co nejlépe eliminovat možná rizika? apod. Příznivci LinuxSoftu mohou při registraci uplatnit kód LIN350, který jim přinese zvýhodněné vstupné s 50% slevou.
Přidat komentář
6.11.2018 2:04 /František Kučera Říjnový pražský sraz spolku OpenAlt se koná v listopadu – již tento čtvrtek – 8. 11. 2018 od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5). Tentokrát bez oficiální přednášky, ale zato s dobrým jídlem a pivem – volná diskuse na téma umění a technologie, IoT, CNC, svobodný software, hardware a další hračky.
Přidat komentář
4.10.2018 21:30 /Ondřej Čečák LinuxDays 2018 již tento víkend, registrace je otevřená.
Přidat komentář
18.9.2018 23:30 /František Kučera Zářijový pražský sraz spolku OpenAlt se koná již tento čtvrtek – 20. 9. 2018 od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5). Tentokrát bez oficiální přednášky, ale zato s dobrým jídlem a pivem – volná diskuse na téma IoT, CNC, svobodný software, hardware a další hračky.
Přidat komentář
9.9.2018 14:15 /Redakce Linuxsoft.cz 20.9.2018 proběhne v pražském Kongresovém centru Vavruška konference Mobilní řešení pro business.
Návštěvníci si vyslechnou mimo jiné přednášky na témata: Nejdůležitější aktuální trendy v oblasti mobilních technologií, správa a zabezpečení mobilních zařízení ve firmách, jak mobilně přistupovat k informačnímu systému firmy, kdy se vyplatí používat odolná mobilní zařízení nebo jak zabezpečit mobilní komunikaci.
Přidat komentář
12.8.2018 16:58 /František Kučera Srpnový pražský sraz spolku OpenAlt se koná ve čtvrtek – 16. 8. 2018 od 19:00 v Kavárně Ideál (Sázavská 30, Praha), kde máme rezervovaný salonek. Tentokrát jsou tématem srazu databáze prezentaci svého projektu si pro nás připravil Standa Dzik. Dále bude prostor, abychom probrali nápady na využití IoT a sítě The Things Network, případně další témata.
Přidat komentář
16.7.2018 1:05 /František Kučera Červencový pražský sraz spolku OpenAlt se koná již tento čtvrtek – 19. 7. 2018 od 18:00 v Kavárně Ideál (Sázavská 30, Praha), kde máme rezervovaný salonek. Tentokrát bude přednáška na téma: automatizační nástroj Ansible, kterou si připravil Martin Vicián.
Přidat komentář
Více ...
Přidat zprávičku
 Poslední diskuze
31.7.2023 14:13 /
Linda Graham iPhone Services
30.11.2022 9:32 /
Kyle McDermott Hosting download unavailable
13.12.2018 10:57 /
Jan Mareš Re: zavináč
2.12.2018 23:56 /
František Kučera Sraz
5.10.2018 17:12 /
Jakub Kuljovsky Re: Jaký kurz a software by jste doporučili pro začínajcího kodéra?
Více ...
|