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

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

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

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ů

12.7.2016 13:14 /František Kučera
Spolek OpenAlt zve na 130. distribuovaný sraz příznivců svobodného softwaru a otevřených technologií (hardware, 3D tisk, SDR, DIY, makers…), který se bude konat ve čtvrtek 21. července od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5).
Přidat komentář

11.7.2016 16:53 /Redakce Linuxsoft.cz
Konference LinuxDays hledá přednášející. Přihlášky poběží do konce prázdnin, v září bude hlasování a program. Více na https://www.linuxdays.cz/2016/cfp/.
Přidat komentář

8.5.2016 17:19 /Redakce Linuxsoft.cz
PR: Dne 26.5.2016 proběhne v Praze konference Cloud computing v praxi. Tématy bude např. nejnovější trendy v oblasti cloudu a cloudových řešení, cloudové služby, infrastruktura cloudu, efektivní využití cloudu, možné nástrahy cloudů a jak se jim vyhnout
Přidat komentář

21.4.2016 8:01 /František Kučera
Spolek OpenAlt zve na 127. distribuovaný sraz příznivců svobodného softwaru a otevřených technologií (hardware, 3D tisk, SDR, DIY, makers…), který se bude konat ve čtvrtek 28. dubna od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5).
Přidat komentář

2.3.2016 22:41 /Ondřej Čečák
Letošní ročník konference InstallFest již tento víkend!
Přidat komentář

14.2.2016 16:39 /Redakce Linuxsoft.cz
O víkendu 5. a 6. března 2016 proběhne na pražském Strahově 8. ročník tradiční konference InstallFest. Celkem za dva dny uvidíte ​30 přednášek​ a ​6 workshopů.
Přidat komentář

5.2.2016 17:38 /Petr Ježek
Utilitka z XFce "xfce4-power-manager" nejen umožňuje nastavení lhůty pro uspání či hybernaci, ale i zapínání a vypínání prezentačního módu pro nerušené sledování videí. Stačí ji nastavit v každém vybavenějším panelu a v jakémkoli nontiled WM/DE.
Přidat komentář

10.1.2016 11:32 /Pavel `Goldenfish' Kysilka
LinuxMarket změnil provozovatele. Nově jej provozuje Marek Pszczolka. Více info a detaily #1 a #2.
Přidat komentář

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

> Poslední diskuze

10.6.2016 21:10 / pavel riha
FreeBSD 10.3 a virtualizace

8.6.2016 21:56 / Milan Gallas
Nevalidní prefix m

7.5.2016 14:58 / Teodor Komárek
Soubory

20.4.2016 0:07 / Jakub Cleing
Sázkový panel PHP FUSION

9.4.2016 9:43 / jiwopene@gmail.com
Re: problém s dpkg a nemožností instalovat

Více ...

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