LINUXSOFT.cz Přeskoč levou lištu

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 27391×

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:

CREATE TABLE cislovani (id INT AUTO_INCREMENT, PRIMARY KEY (id));

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:

create table cisla (cislo int);

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

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.PrintStream;
import java.util.Random;
public class Rand {
    public static void main(String[] args) {
        Random generator = new Random();
        StringBuffer buffer=new StringBuffer();
        FileOutputStream fout;
        String separator = System.getProperty("line.separator");
        int i;
        for (i=0;i<1000000;i++){
            buffer.append(generator.nextInt());
            buffer.append(separator);
        }
        try
        {
            fout = new FileOutputStream ("cisla");
            new PrintStream(fout).print(buffer.toString());
            fout.close();       
        }
        catch (IOException e){}
    }
}

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

load data infile './cisla' into table cisla;

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,

truncate table cisla;
alter table cisla add index (cislo);

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.


Vložení (bez indexu)
Vložení (s indexem)
Vložení s přebudováním indexu
DROP INDEX
LOAD DATA
ADD INDEX
CELKEM
První milion záznamů
0,64 s
2,84 s
0,01 s
0,64 s
2,52 s
3,17 s
Další milion (celkem 2 mil.)
0,64 s 12,41 s
0,30 s
0,64 s
5,16 s
6,10 s
Další milion (celkem 3 mil.)
0,64 s 25,64 s
0,53 s
0,64 s
8,30 s
9,47 s
Další milion (celkem 4 mil.)
0,66 s 125,23 s
0,78 s
0,64 s
10,66 s
12,08 s
Další milion (celkem 5 mil.)
0,64 s 261,77 s
1,05 s
0,64 s
13,52 s 15,21 s

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:

select * from cisla where cislo > 2000000000;

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

pridej.cz

 

DISKUZE

Super 5.8.2005 07:43 MaReK Olšavský
L Re: Super 5.8.2005 08:09 Petr Zajíc
žlutá ikonka klíče 25.2.2016 11:52 Kvido Vícha




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

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?

20.9.2018 10:04 / Jan Ober
Jaký kurz a software by jste doporučili pro začínajcího kodéra?

Více ...

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