ARCHIV

MySQL (32) - ještě k indexům

Jak je to v MySQL s primárními klíči? A jak rychlé či pomalé je pracovat s tabulkami, na nichž je nějaký ten index? O tom je řeč v dnešním díle seriálu.

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

V dnešním díle dokončíme rozbor toho, co pro MySQL znamenají indexy a podíváme se na slíbené testy rychlosti. Uvidíte, že použití indexů v praxi může výrazně zrychlit některé operace, jiné však může naopak výrazně zpomalit.

Primární klíče

Z indexů nám zbývá podívat se na primární klíče. Připomenu z definice, že primární klíč je jedinečný klíč na sloupci, jež nesmí obsahovat hodnoty null a jež smí být v každé tabulce maximálně jeden. V praxi se téměř vždy k vytvoření primárního klíče používá sloupec s automatickým číslováním, protože tam jsou neprázdné hodnoty zajištěny již z principu. Takové pole vytvoříte pomocí příkazu ve stylu:

Tato technika již byla v seriálu rozebrána, a to v díle Tipy ke tvrobě tabulek. Je ale třeba si uvědomit, že zatímco pole typu AUTO_INCREMENT musí být v MySQL definováno jako primární klíč, primárním klíčem NEMUSÍ být vždy automaticky číslované pole. Jestliže jste schopni zajistit unikátnost ve sloupci jinými prostředky než pomocí automatického číslování, můžete to udělat.

Pozn.: Praxe je ovšem taková, že sloupce zahrnuté v primárním klíči vetšinou pouze odlišují řádky, a nenesou žádnou jinou informaci, která se váže k datům. Proto většinou kombinace primárního klíče a automaticky číslovaného pole bohatě postačí a není třeba vymýšlet nic jiného.

Rychlost operací s indexy

Abyste měli alespoň nějakou představu o tom, jak indexy zrychlují nebo zpomalují určité databázové operace, a aby seriál nebyl jen samá teorie, rozhodl jsem se udělat takový primitivní rychlostní test. Test spočívá v tom, že jsem si připravil soubor s určitým kvantem informací a ten se pokusím dostat do databáze. Budu přitom měřit, jak dlouho bude taková operace trvat - a na základě toho uvidíte, jak a co vlastě všechno indexy ovlivňují.

Aby byl test jednoduchý, připravil jsem si tabulku jen s jedním jediným sloupcem - bude do něj ukládáno celé číslo. Pokud si něco takového chcete vyzkoušet, zadejte příkaz ve smyslu:

Následně jsem si připravil soubor s náhodnými čísly. Abych co nejméně ovlivnil výsledky měření, nepoužil jsem k tomu MySQL, ale nechal jsem si vygenerovat soubor obsahující jeden milión náhodných čísel pomocí kraťoučkého programu v jazyce Java

Pozn.: K tvorbě souboru s náhodnými čísly můžete samozřejmě použít jakýkoli programovací jazyk, který zvládá použití generátoru náhodných čísel a zápis do souboru. Původně jsem plánoval, že bych pro testovací účely svůj soubor s daty zveřejnil, ale byl by moc veliký. I přesto, že jsem jej zkoušel komprimovat.

Hromadné vkládání záznamů

MySQL obsahuje příkaz, pomocí něhož lze nahrát data z textového souboru do databáze. Protože jsme jej ještě v seriálu nerozebírali, zmíním se jen letmo o tom, že

nahraje obsah souboru cisla do naší právě vytvořené tabulky. To za předpokladu, že soubor je umístěn v adresáři, v němž má MySQL data pro právě používanou databázi a že jsme právě přepnuti v databázi, jež obsahuje tabulku cisla. Tento příkaz jsem změřil, výsledky jsou v tabulce níže. Postupoval jsem přitom tak, že jsem vytvořil pět souborů s náhodnými daty a vkládal je postupně jeden za druhým. Jestliže tabulku cisla vyprázdníme a k poli cislo přidáme index,

a zkusíme příkaz pro vložení záznamů opakovat, zjistíme, že trvá déle, mnohem déle. Opět jsem celou akci opakoval pětkrát s tím, že jsem vložil první až pátou sadu s milionem náhodných čísel. Výsledky (které opět máte v tabulce) poukazují na fakt, že přebudování indexů pochopitelně něco stojí.

Co kdybychom ale šli na celou věci jinak? Co kdybychom

1. Existující index zrušili,
   
2. Hromadně vložili data,
3. A následně znovu přidali k tabulce index?

Vložení dat v takovém případě bude trvat v podstatě konstatní dobu. Jak ale vidíme, zrušení a znovuvytvoření indexu je až řádově rychlejší než jeho udržování během operace hromadného vkládání záznamů - a ten rozdíl je tím markantnější, čím více dat je v podkladové tabulce.

Výběr záznamů

Když už máme tak pěknou sadu dat, můžeme si ještě ukázat, jak indexy plní svůj původní význam - tedy dohledávat data podle jejich hodnoty. Pokud máte na tabulce index, zrušte jej a zkuste si spustit následující dotaz:

Na mém stroji tento dotaz trvá asi 1,45 s a to proto, že databáze musí tuto tabulku projít (data v tabulce totiž nejsou nikterak seřazena - vždyť jsou to náhodná čísla). Stejný dotaz s použitím indexu však trvá pouhých 0,85 s. protože databáze díky indexu ví, kde má hodnoty větší než dvě miliardy ve výsledné sadě záznamů hledat.

Závěr

Tyto rychlostní testy samozřejmě je třeba brát s jistou rezervou. Především proto, že neodrážejí skutečné operace, s nimiž se budete při práci s databází setkávat v praxi. Dále proto, že jsme měřili pouze rychlost provedení dotazu, a ne již takové věci, jako je zatížení procesoru, spotřeba paměti a tak dále. Konečně, měření probíhalo na mé pracovní stanici, kde bylo zároveň spuštěno několik dalších programů, takže výsledky budou zcela jistě nepřesné.

Nicméně, mohli jste si udělat představu o tom, jak indexy urychlují (či zpomalují) jednotlivé databázové operace.

Verze pro tisk

DISKUZE Super 5.8.2005 07:43 MaReK Olšavský Re: Super 5.8.2005 08:09 Petr Zajíc žlutá ikonka klíče 25.2.2016 11:52 Kvido Vícha Zobrazit označené Zobrazit vše Příspívat do diskuze mohou pouze registrovaní uživatelé.